使用druid连接数据库

咱们紧接着上一篇博文

https://blog.csdn.net/liuying1802028915/article/details/90082684

因为我要实现在servlet中查询数据库。

一般查询数据库的方式有很多种，最简单的就是使用jdbc直连数据库，我刚开始也是这么做的，但是在项目中发现，操作几次数据库之后，就获取不到连接了，就会有问题，那么我想到了连接池，使用连接池也有很多种，dbcp,c3p0等等，我使用dbcp时，发现报一些log4j的一些错误，可能与本身的工程有关系，没有深入去解决，因为涉及到公司中的工程就头疼，不敢轻易改动项目中的配置信息，之后了解到阿里巴巴的druid功能很强大，所以学着使用druid去连接数据库。

我之前是参照了另外一个人的博客实现的该功能，但是那篇博文找不到了，这里感谢一下那位作者。下面我将写的一个druid的小demo贴出来，自己留个记录，也方便别人。

新建一个类DBPoolConnection

package com.taoj.druid.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.pool.DruidPooledConnection;

import javax.annotation.processing.FilerException;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.Properties;

public class DBPoolConnection {

    private static DBPoolConnection dbPoolConnection = null;
    private static DruidDataSource druidDataSource;

    static{
        try {
            Properties properties = loadPropertiesFile("classes\\config1.properties");
            druidDataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e){
            e.printStackTrace();
        }

    }

    public static synchronized DBPoolConnection getInstance(){
        if(dbPoolConnection == null){
            dbPoolConnection = new DBPoolConnection();
        }
        return dbPoolConnection;
    }

    public DruidPooledConnection getConnection() throws SQLException {
        return druidDataSource.getConnection();
    }

    private static Properties loadPropertiesFile(String fullFile) throws FilerException {
        String webRootPath = null;
        if (fullFile == null || "".equals(fullFile)) {
            throw new IllegalArgumentException("Properties file path can not be null"+fullFile);
        }
        webRootPath = DBPoolConnection.class.getClassLoader().getResource("").getPath();
        System.out.println("webRootPath:"+webRootPath);
        webRootPath = new File(webRootPath).getParent();
        InputStream inputStream = null;
        Properties p = null;
        try {
            inputStream = new FileInputStream(new File(webRootPath + File.separator + fullFile));
            p = new Properties();
            p.load(inputStream);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return p;
    }
}

下面是pom文件，需要引入druid和mysql的驱动

<?xml version="1.0" encoding="UTF-8"?>

<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>com.taoj</groupId>
  <artifactId>druidDemo</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>druidDemo Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.0.29</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.0.8</version>
    </dependency>
  </dependencies>

  <build>
    <finalName>druidDemo</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>
  </build>
</project>

这里会有一个小插曲，我引入mysql驱动的时候，引得是最新的驱动（6.0.6），执行代码的时候会抱一个错：

他说我应该用com.mysql.cj.jdbc.Driver ，故我将 mysql的驱动版本降到了5.0.8 之后就没有这个错了

 

下面是一个测试类：

package com.taoj.druid.util;

import com.alibaba.druid.pool.DruidPooledConnection;

import java.sql.ResultSet;
import java.sql.Statement;

public class DruidTest {

    public static void main(String[] args) {
        DBPoolConnection dbp = DBPoolConnection.getInstance();
        DruidPooledConnection conn = null;

        String name = "nb";
        String sql = "";
        sql += " SELECT TYPE ";
        sql += " FROM NL_U_ROLE ";
        sql += " WHERE id=( ";
        sql += "    SELECT b.`roleid` ";
        sql += "    FROM NL_U_USER a, NL_U_USER_ROLE b ";
        sql += "    WHERE a.`id`=b.`userid` AND a.`code`='"+name+"' ";
        sql += " )";
        String type = "-1";

        try {
            conn = dbp.getConnection();
            Statement state = conn.createStatement();
            ResultSet rs = state.executeQuery(sql);
            while(rs.next()){
                type = rs.getString(1);
            }
            System.out.println("type:"+type);
            conn.close();
        } catch (Exception e){
            e.printStackTrace();
        }

    }

}

对了这里要放入配置文件，我将数据库的用户名密码模糊了：

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.24.198:3306/server_conf?useUnicode=true&characterEncoding=utf8
username=username
password=password

filters=stat
initialSize=2
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
poolPreparedStatements=false
maxPoolPreparedStatementPerConnectionSize=200

测试类直接可以运行，不过要修改数据库的用户名和密码，请注意

不积跬步，无以至千里

不积小流，无以成江海