java_JDBC，连接数据库方式，RestSet结果集，Statement，PreparedStatement，事务，批处理，数据库连接池（c3p0和Druid）、ApacheDBUtils、

//properties配置文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test_db?useSSL=false&characterEncoding=utf8&serverTimezOne=UTC
user=root
password=123456
//连接数据库的相关操作
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class jdbc02 {
    public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
        //创建一个properties对象
        Properties properties = new Properties();
        //加载和获取创建的properties的相关数据
        properties.load(new FileInputStream("src\\db.properties"));
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        String sql = "insert into actor values (null,'王五','男','1975-11-23','110')";

        //driver驱动，也可以不加，但是加上更容易读懂流程
        Class.forName(driver);
        //连接和执行操作
        Connection cOnnection= DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        statement.executeUpdate(sql);
        //关闭连接
        System.out.println("连接"+connection);
        statement.close();
        connection.close();


    }
}

import java.io.*;
import java.sql.*;
import java.util.Properties;

public class jdbc02 {
    public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
        //创建一个properties对象
        Properties properties = new Properties();
        //加载和获取创建的properties的相关数据
        properties.load(new FileInputStream("src\\db.properties"));
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        String sql = "insert into actor values (null,'三毛','男','1975-11-23','110')";
        String sql1 = "select id,name from actor";

        //driver驱动，也可以不加，但是加上更容易读懂流程
        Class.forName(driver);
        //连接和执行操作
        Connection cOnnection= DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
//        statement.executeUpdate(sql);

//        String sql1 = "select id,name from actor";
        ResultSet resultSet = statement.executeQuery(sql1);
        while (resultSet.next()) {
            int i = resultSet.getInt(1);
            String string1 = resultSet.getString(2);
            System.out.println(i+"=="+string1);
        }
        //关闭连接
        resultSet.close();
        System.out.println("连接"+connection);
        statement.close();
        connection.close();


    }
}

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class PrepareStatement {
    public static void main(String[] args) throws Exception {

        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\db.properties"));
        String drive = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        //更新操作
//        String sql = "update actor set phOne= ? where name = ?";
        //删除操作
//        String sql = "delete from actor where name = ?";
//        添加操作
//        String sql = "insert into actor values(null,?,?,?,?)";
//查询操作
        String sql = "select * from actor";

        Class.forName(drive);

        Connection connection = DriverManager.getConnection(url, user, password);

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //更改操作
//        preparedStatement.setString(1, "123456");
//        preparedStatement.setString(2, "三毛");
//删除操作
//        preparedStatement.setString(1, "二狗");
//添加操作
//        preparedStatement.setString(1, "张三");
//        preparedStatement.setString(2, "男");
//        preparedStatement.setString(3, "1975-11-23 00:00:00");
//        preparedStatement.setString(4,"456789");
//        int i = preparedStatement.executeUpdate();
//        System.out.println(i >0 ? "成功" : "失败");

//查询操作
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            System.out.println(resultSet.getString(2));
        }

        preparedStatement.close();
        connection.close();


    }
}

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class ConDatasource {
    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\db.properties"));
        String driver = properties.getProperty("driver");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");

        //建立comboPooledDataSource管理连接池
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        //设置初始化连接数和最大连接数
        comboPooledDataSource.setInitialPoolSize(10);
        comboPooledDataSource.setMaxPoolSize(50);
        //这个方法就是从DataSource接口实现的
        Connection cOnnection= comboPooledDataSource.getConnection();
        System.out.println("1连接成功");
        connection.close();

    }

    @Test
    public  void ConDatasource02() throws SQLException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("test_C3P0");

        long start = System.currentTimeMillis();
        System.out.println("测试连接5000次需要的时间");
        for (int i = 0; i <5000; i++) {
        Connection connection = comboPooledDataSource.getConnection();
//        System.out.println("2连接成功");
        connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("花费时长："+(end-start));
    }
}
//第二种方式的xml文件


  "test_C3P0">

  "driverClass">com.mysql.cj.jdbc.Driver
  
      "jdbcUrl">jdbc:mysql://localhost:3306/test_db?useSSL=false&characterEncoding=utf8&serverTimezOne=UTC
  
          "user">root
          
      "password">123456
      
    "acquireIncrement">5
    
    "initialPoolSize">10
    
    "minPoolSize">5
   
    "maxPoolSize">10

    
    "maxStatements">5 
    
    
    "maxStatementsPerConnection">2
  

package com.zjl.jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.util.Properties;

public class Druidsource {
    public static void main(String[] args) throws Exception {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));//如下定义的配置文件

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        long start = System.currentTimeMillis();
        for (int i = 0;i <500000;i++ ) {
            Connection connection = dataSource.getConnection();
            connection.close();
        }
        long end = System.currentTimeMillis();
        System.out.println("Druid连接5000 次需要的时间：" + (end - start));//Druid连接500000 次需要的时间：625
    }
}
//连接中需要的一个配置文件

druid.properties：放在src目录下

#key=value
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test_db?useSSL=false&characterEncoding=utf8&serverTimezOne=UTC
#url=jdbc:mysql://localhost:3306/girls
username=root
password=123456
#initial connection Size初始连接数
initialSize=10
#min idle connecton size最小等待连接数
minIdle=5
#max active connection size最大连接数
maxActive=20
#max wait time (5000 mil seconds)
# 最大等待时间，时间超过就结束这次连接等待下一次的连接
maxWait=5000

package com.zjl.jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtilsByDruid {
    static DataSource ds;
    static {
        Properties properties = new Properties();

        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
        //编写获取Connection的方法
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            //将编译异常转成运行异常抛出
            throw new RuntimeException(e);
        }
    }
}

package com.zjl.jdbc;

import com.zjl.bean.Actor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

public class TestDBUtils {
    public static void main(String[] args) throws Exception {
        String sql = "select * from `actor`";
        //从Druid中获取一个连接
        Connection cOnnection= JDBCUtilsByDruid.getConnection();
        //引入DBUtils的jar包，并获得一个QueryRunner对象
        QueryRunner queryRunner = new QueryRunner();

        //利用QueryRunner对象就可以直接查询数据库,执行SQl语句，返回结果封装到ArrayList集合中
        // connection是一个连接，sql是一个执行语句
        //new BeanListHandler<>(Actor.class):在将resultSet中的结果以Actor对象方式封装到ArrayList
        //底层使用反射机制，获取Actor类的属性，然后进行封装
        //1就是给sql 语句中的?赋值，可以有多个值，因为是可变参数0bject... params
//        List list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        //如果要返回单个查询数据就使用BeanHandler<>(Actor.class)，返回结果就会是单个对象或者为空
        List list = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class));
        //在底层会得到resultSet并在query中关闭，也会在底层使用preparedStatement并关闭
//        JDBCUtilsByDruid.close(null, null, connection);
        Iterator iterator = list.iterator();
        while (iterator.hasNext()) {
            Actor next = iterator.next();
            System.out.println(next);
        }
        //进行更新操作
        String updateSql = "update `actor` set name = ? where id = ?";
        int update = queryRunner.update(connection, updateSql, "王无无", 8);
        System.out.println((update > 0) ? "更新成功" : "更新失败");
        //进行删除操作
        String delSql = "delete from `actor` where id = ?";
        int update1 = queryRunner.update(connection, delSql, 10);
        System.out.println((update > 0) ? "删除成功" : "删除失败");

        //关闭资源
        JDBCUtilsByDruid.close(null, null, connection);
    }

}