批量操作、事务、连接池

一、批量操作

public class Test {

      public void batch1() throws SQLException{

            Connection connection = JDBC.getConn();//获取连接，自定义的

            PreparedStatement preparedStatement = connection.prepareStatement("update mytable set a= ?");

            

            for(int i=0;i<10000;i++){

                  preparedStatement.setString(1, "aaaaa"+i);                                           

                  preparedStatement.addBatch();

                  if(i%100==0){

                        preparedStatement.executeBatch();

                        preparedStatement.clearBatch();

                  }

            }

            preparedStatement.executeBatch();

      }

      

      public void batch2() throws SQLException{

            Connection connection = JDBC.getConn();//获取连接，自定义的

            Statement statement = connection.createStatement();

            

            statement.addBatch("insert...");

            statement.addBatch("update...");

            statement.addBatch("insert...");

            int[] i=statement.executeBatch();

            for (int j : i) {

                  System.out.println("执行结果"+j);

            }

      }

}

二、事务

    特征：原子性（Atomicity），一致性（Consistency），隔离性（isolation），持久性（durability）

public static void main(String[] args) throws SQLException {      

            Connection connection = JDBC.getConn();

            connection.setAutoCommit(false);

            PreparedStatement preparedStatement = connection.prepareStatement("insert into student (name) values (?) ");

            

            preparedStatement.setString(1, "小笨蛋一号");

            preparedStatement.addBatch();

            preparedStatement.setString(1, "小笨蛋二号");

            preparedStatement.addBatch();

            preparedStatement.executeBatch();

            

            //System.out.println(5/0);

            

            preparedStatement.setString(1, "小笨蛋三号");

            preparedStatement.addBatch();

            preparedStatement.executeBatch();

            connection.commit();

      }

三、连接池

package 连接池;

import java.sql.Connection;

public interface DataSource {

      Connection getConnection();

}

//=============================================

package 连接池;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.LinkedList;

public class MyDataSource implements DataSource {

      LinkedList<Connection> pools = new LinkedList<>();

      int initSize = 10;

      static String driverClass = "com.mysql.jdbc.Driver";

      static String url = "jdbc:mysql://127.0.0.1:3306/ac";

      static String username = "root";

      static String password = "root";

      int activeSize;

      int idealSize;// 闲置连接数

      public MyDataSource() {

            try {

                  addConnection();

            } catch (SQLException e) {

                  e.printStackTrace();

            }

      }

      static {

            try {

                  Class.forName(driverClass);

            } catch (ClassNotFoundException e) {

                  e.printStackTrace();

            }

      }

      private void addConnection() throws SQLException {

            Connection connection;

            for (int i = 0; i < initSize; i++) {

                  connection = DriverManager.getConnection(url, username, password);

                  this.idealSize++;

                  pools.add(connection);

            }

      }

      

      @Override

      public Connection getConnection() {

            try {

                  if (pools.size() <= 0) {

                        addConnection();

                  }

                  Connection connection = pools.removeFirst();

                  if (connection != null && !connection.isClosed()) {

                        this.idealSize--;

                        this.activeSize++;

                        return connection;

                  }

            } catch (SQLException e) {

                  e.printStackTrace();

            }

            return null;

      }

      

      public void release(Connection connection){

            try {

                  if(connection!=null && !connection.isClosed()){

                        pools.add(connection);

                        this.activeSize--;

                        this.idealSize++;

                  }

            } catch (SQLException e) {

                  e.printStackTrace();

            }

      }

      public static void main(String[] args) {

            MyDataSource myDataSource = new MyDataSource();

            Connection connection1 = myDataSource.getConnection();

            Connection connection2 = myDataSource.getConnection();

            Connection connection3 = myDataSource.getConnection();

            System.out.println(connection1);

            System.out.println(connection2);

            System.out.println(connection3);

            

            System.out.println("激活的连接"+myDataSource.activeSize);

            System.out.println("闲置的连接"+myDataSource.idealSize);

            

      }

}

四、连接池

市面上常见连接池：DBCP_apache（Database Connection Pool），C3P0（跟随hibernate框架），Procool

稳定性：C3P0优于DBCP，当连接数超过最大值，c3p0会断开当前连接，dpcp会断开所有连接

效率：dbcp优于c3p0,c3p0能自动回收空闲连接，dbcp不会自动回收连接

package DBCP_TEST;

//动态代理（设计模式）面向切面

import java.sql.Connection;

import java.sql.SQLException;

import org.apache.commons.dbcp2.BasicDataSource;

public class DBCPdemo {

      private static BasicDataSource basicDataSource;

      static{

            init();

      }

      

      private static void init(){

            // 创建一个数据源对象

            basicDataSource = new BasicDataSource();

            // 设置驱动类路径

            basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");

            //设置url,用户名，密码

            basicDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/ac");

            basicDataSource.setUsername("root");

            basicDataSource.setPassword("root");

            //设置连接数（初始，最大）(可选)

            basicDataSource.setInitialSize(10);

            basicDataSource.setMaxTotal(100);

            //设置最大闲置连接数(80,10)（可选)

            basicDataSource.setMaxIdle(80);

            basicDataSource.setMinIdle(10);

            //设置获取连接等待最大时间（可选）

            basicDataSource.setMaxWaitMillis(10000);

      }

      

      //获取连接

      public static Connection getConn() {

            try {

                  if(basicDataSource ==null||basicDataSource.isClosed()){

                        init();

                  }

                  return basicDataSource.getConnection();

            } catch (SQLException e) {

                  e.printStackTrace();

            }

            return null;

      }

      

      public static void main(String[] args) throws SQLException {

            Connection connection1=getConn();//获取连接

            Connection connection2=getConn();

            Connection connection3=getConn();

            Connection connection4=getConn();

            System.out.println(connection1);

            System.out.println(connection2);

            System.out.println(connection3);

            System.out.println(connection4);

            System.out.println("当前连接数"+basicDataSource.getNumActive());//4

            System.out.println("闲置连接数"+basicDataSource.getNumIdle());//6

            connection1.close();//动态代理

            System.out.println("当前连接数"+basicDataSource.getNumActive());//3

            System.out.println("闲置连接数"+basicDataSource.getNumIdle());//7           

      }

}