JDBC——JAVA获取MySQL数据库连接及会出现的问题

一、手动编写url获取连接
1.* java.sql.SQLException: The server time zone value ‘�й���׼ʱ��’
* is unrecognized or represents more than one time zone.
* You must configure either the server or JDBC driver
* (via the serverTimezone configuration property)
* to use a more specifc time zone value if you want to utilize time zone support.
使用的数据库是MySQL，这是由于数据库和系统时区差异所造成的，需要设置时区：
在url后加上：serverTimezone=GMT或者serverTimezone=UTC即可

2.jar包与mysql数据库的字符集不一致，
需在url后添加：useUnicode=true&characterEncoding=utf-8

    String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT";

最终版：

    //获取Driver实现类对象
    Class.forName("com.mysql.jdbc.Driver");
    //加载com.mysql.jdbc.Driver驱动时，实现类会执行静态代码块，
    //代码块中会执行注册驱动步骤：java.sql.DriverManager.registerDriver(new Driver())
    String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT";
    String user = "root";
    String password = "980828";

    //获取连接
    Connection connection = DriverManager.getConnection(url, user, password);

二、编写properties配置文件获取数据库连接
//将数据库连接需要的基本信息声明在配置文件中，通过读取配置文件的方式获取连接

    //1.读取配置文件中的四个基本信息
    InputStream resourceAsStream = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    Properties properties = new Properties();
    properties.load(resourceAsStream);
    String user = properties.getProperty("user");
    String password = properties.getProperty("password");
    String url = properties.getProperty("url");
    String driverClass = properties.getProperty("driverClass");

    //2.加载驱动
    Class.forName(driverClass);

    //3.获取连接
    Connection connection = DriverManager.getConnection(url, user, password);

properties配置文件：
user=root
password=980828
url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=UTC&rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver

三、数据库连接池获取连接
1.C3P0

    //方式一：获取C3P0数据库连接池
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" ); //loads the jdbc driver
    //由于使用8.0的MySQL,需要导入8.0以上的jar包，
    //且driverClass由com.mysql.jdbc.Driver改为com.mysql.cj.jdbc.Driver
    cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=UTC" );
    //url后缀也需要加上?characterEncoding=utf-8&serverTimezone=UTC
    cpds.setUser("root");
    cpds.setPassword("980828");
    //连接池设置
    cpds.setInitialPoolSize(100); //初始数据库连接池中的连接数

    Connection connection = cpds.getConnection();
    System.out.println(connection);

    //销毁连接池（不推荐使用）
    DataSources.destroy(cpds);

  
   //方式二：获通过xml文件配置获取C3P0数据库连接池
    ComboPooledDataSource cpds = new ComboPooledDataSource("c3p0");
    Connection connection = cpds.getConnection();
    System.out.println(connection);

xml文件配置信息：

<?xml version="1.0" encoding="UTF-8" ?> <c3p0-config> <!-- This app is massive! -->
    <named-config name="c3p0">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">980828</property>

        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxPoolSize">1000</property>

        <!-- intergalactoApp adopts a different approach to configuring statement caching -->
        <property name="maxStatements">0</property>
        <property name="maxStatementsPerConnection">5</property>

    </named-config>
</c3p0-config>

2.DBCP

    public void GetConnection1() throws SQLException {
    //创建DBCP连接池
    BasicDataSource source = new BasicDataSource();
    //设置基本信息
    source.setDriverClassName("com.mysql.cj.jdbc.Driver");
    source.setUrl("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=UTC");
    source.setUsername("root");
    source.setPassword("980828");

    //设置其他涉及数据库连接池管理的相关属性
    source.setMaxActive(10);
    source.setInitialSize(10);

    Connection connection = source.getConnection();
}


//方式二：使用配置文件
public void GetConnection2() throws Exception {
    //方式二：使用配置文件Properties
    Properties properties = new Properties();
    InputStream is = ClassLoader.getSystemResourceAsStream("dbcp.properties");
    properties.load(is);

    DataSource source = BasicDataSourceFactory.createDataSource(properties);
    Connection connection = source.getConnection();
    System.out.println(connection);
}

3.Druid（推荐使用）

    try {
        //使用配置文件
        Properties properties = new Properties();
        InputStream is = ClassLoader.getSystemResourceAsStream("druid.properties");
        properties.load(is);
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        Connection connection = dataSource.getConnection();
    } catch (Exception e) {
        e.printStackTrace();
    }

properties配置文件：
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=UTC
username=root
password=980828
MaxActive=10
initialSize=8