JavaWeb 使用 Druid 连接池查询数据库

wsuo

发布于 2020-07-31 15:37:17

9330

发布于 2020-07-31 15:37:17

文章被收录于专栏：技术进阶之路技术进阶之路技术进阶之路

Maven 配置

本 Demo 使用 Maven 创建，如果你没有使用该工具也无伤大雅，自己下载对应的 jar 包放到 lib目录下就可以了。

这里是 pom.xml：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>top.wsuo</groupId>
    <artifactId>jdbc-demo</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>war</packaging>

    <name>jdbc-demo Maven Webapp</name>


    <dependencies>
        <!--Junit 单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.11</version>
            <scope>test</scope>
        </dependency>

        <!--servlet-->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>

        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.26</version>
            <scope>compile</scope>
        </dependency>

        <!--Druid 数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>
    </dependencies>

    <build>
        <finalName>jdbc-demo</finalName>
        <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
            <plugins>
                <plugin>
                    <artifactId>maven-clean-plugin</artifactId>
                    <version>3.1.0</version>
                </plugin>
                <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
                <plugin>
                    <artifactId>maven-resources-plugin</artifactId>
                    <version>3.0.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.8.0</version>
                </plugin>
                <plugin>
                    <artifactId>maven-surefire-plugin</artifactId>
                    <version>2.22.1</version>
                </plugin>
                <plugin>
                    <artifactId>maven-war-plugin</artifactId>
                    <version>3.2.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-install-plugin</artifactId>
                    <version>2.5.2</version>
                </plugin>
                <plugin>
                    <artifactId>maven-deploy-plugin</artifactId>
                    <version>2.8.2</version>
                </plugin>
            </plugins>
        </pluginManagement>
        <!--maven插件-->
        <plugins>
            <!--jdk编译插件-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>utf-8</encoding>
                </configuration>
            </plugin>
            <!--tomcat插件-->
            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <!-- tomcat7的插件， 不同tomcat版本这个也不一样 -->
                <artifactId>tomcat7-maven-plugin</artifactId>
                <version>2.1</version>
                <configuration>
                    <!-- 通过maven tomcat7:run运行项目时，访问项目的端口号 -->
                    <port>8080</port>
                    <!-- 项目访问路径  本例：localhost:9090,  如果配置的aa， 则访问路径为localhost:9090/aa-->
                    <path>/</path>
                </configuration>
            </plugin>

        </plugins>
    </build>
</project>

创建工具类

创建数据库连接池的工具类，负责创建连接对象。

public class JdbcUtil {

    private static DataSource dataSource;

    // 注册驱动
    static {
        try {
            InputStream resource = Druid
                    .class
                    .getClassLoader()
                    .getResourceAsStream("druid.properties");
            Properties properties = new Properties();
            properties.load(resource);
            dataSource = DruidDataSourceFactory
                    .createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnect() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void close(Connection conn,
                             PreparedStatement statement) {
        assert conn != null;
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        assert statement != null;
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Connection conn,
                             PreparedStatement statement,
                             ResultSet resultSet) {

        close(conn, statement);
        assert resultSet != null;
        try {
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

配置文件

Druid 是通过读取配置文件的方式加载类，所以我们必须提供配置文件，默认要放在 classpath 路径下，在我们的 web' 项目中就是 resource 目录，前提你要把该目录标记为 resource 资源文件夹：

标记方法：

右键文件夹，选择 mark：

配置文件内容如下：

driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql:///school?characterEncoding=utf-8
username = root
password = root
initialSize=5
maxActive=10
maxWait=3000

CRUD

实体类：

public class Student {
    private int id;
    private String name;
    private int age;
    private String qq;

    public Student(int id) {
        this.id = id;
    }

    public Student(String name, int age, String qq) {
        this.name = name;
        this.age = age;
        this.qq = qq;
    }

    public Student(int id, String name, int age, String qq) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.qq = qq;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getQq() {
        return qq;
    }

    public void setQq(String qq) {
        this.qq = qq;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", qq='" + qq + '\'' +
                '}';
    }
}

CRUD 类：

public static List<Student> queryAll(Connection connection) throws SQLException {
        String sql = "select * from school.student;";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();
        List<Student> list = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String qq = resultSet.getString("qq");
            list.add(new Student(id, name, age, qq));
        }
        return list;
    }

    public static int insert(Connection connection, Student student) throws SQLException {
        String sql = "insert into school.student(name, age, qq) values(?, ?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, student.getName());
        statement.setInt(2, student.getAge());
        statement.setString(3, student.getQq());
        int i = statement.executeUpdate();
        Druid.close(connection, statement);
        return i;
    }

    public static int update(Connection connection, Student student) throws SQLException {
        String sql = "UPDATE school.student SET NAME = ? WHERE id = ?";
        assert connection != null;
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, student.getName());
        statement.setInt(2, student.getId());
        return statement.executeUpdate();
    }

    public static int delete(Connection connection, int id) throws SQLException {
        String sql = "delete from school.student WHERE id = ?";
        assert connection != null;
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        return statement.executeUpdate();
    }

// 测试方法
	public static void main(String[] args) throws SQLException {
        Connection connection = JdbcUtil .getConnect();
        assert connection != null;
        int n1 = delete(connection, 2);
        System.out.println(n1);
        int n2 = insert(connection,
                new Student("刘能", 23, "34123413"));
        System.out.println(n2);
        List<Student> list = queryAll(connection);
        for (Student student : list) {
            System.out.println(student);
        }
	}

其中 Student 是我们封装的实体类。

Web.xml 配置 Druid 监控

如果我们想监控数据库怎么办呢 ?

也很简单，Druid 已经为我们封装好了一个类，我们可以直接请求，只需要在配置文件中配置一下访问路径和初始化参数…

来看 web.xml

	<!-- 配置 Druid 监控信息显示页面 -->
    <servlet>
        <servlet-name>DruidStatView</servlet-name>
        <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
        <init-param>
            <!-- 允许清空统计数据 -->
            <param-name>resetEnable</param-name>
            <param-value>true</param-value>
        </init-param>
        <init-param>
            <!-- 用户名 -->
            <param-name>loginUsername</param-name>
            <param-value>root</param-value>
        </init-param>
        <init-param>
            <!-- 密码 -->
            <param-name>loginPassword</param-name>
            <param-value>root</param-value>
        </init-param>
    </servlet>
    <servlet-mapping>
        <servlet-name>DruidStatView</servlet-name>
        <url-pattern>/druid/*</url-pattern>
    </servlet-mapping>

然后访问 http://localhost:8080/druid/login.html就可以看到这样一个页面: