JDBC

What is JDBC

JDBC (Java database connectivity) is a Java API used to execute SQL statements. It provides unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language

Connect database

Connect before mysql8

Database driver name: com mysql. jdbc. Driver

url connection: jdbc:mysql://one hundred and twenty-seven.0.0.1:three thousand three hundred and six/mydb

public class test{
    public static void main(String[] args){
    	Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://127.0.0.1:3306/mydb";
        String user="root";
        String password="root";
        Connection conn = DriverManager.getConnection(url,user,password);
        
	}
}

Connect after mysql8

Database driver name: com mysql. cj. jdbc. Driver

url connection: JDBC: mysql://127.0.0.1:3306/mydbseSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai

public class test{
    public static void main(String[] args){
         * url Same resource locator
            *   agreement          jdbc:mysql:
            *   ip address        127.0.0.1/localhost
            *   Port number        3306
            *   Specific resource path mydb
            *   useSSL=false             Not used SSL Encryption mechanism
            *   &useUnicode=true         use unicode character set
            *   &characterEncoding=utf8  use utf8 As communication character set
            *   &serverTimezone=Asia/Shanghai  Determine the time zone as Asia/Shanghai
            * 	user     account number root
            *	password password root
            * */
 		Class.forName("com.mysql.cj.jdbc.Driver");
         String url = "jdbc:mysql://127.0.0.1:3306/mydb?seSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
         String user = "root";
         String pwd = "root";
        Connection conn = DriverManager.getConnection(url,user,password);   
	}
}

Common anomalies

java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver2

jar package not added or path error

SQLException: No suitable driver found for jdbcs

Protocol error

CommunicationsException: Communications link failure

Communication failure

SQLException: Access denied for user 'root'@'localhost' (using password: YES)

Access denied password error

SQLNonTransientConnectionException: Public Key Retrieval is not allowed

Not allowed to retrieve public key user name error

SQLIntegrityConstraintViolationException: Duplicate entry '50' for key 'PRIMARY'

Duplicate primary key entry

What is the difference between executeQuert() and executeUpdate()

Execute query command: executeQuert()

Execute the add, delete and modify command: executeUpdate()

Interface result

The ResultSet object receives the return value of executeQuert()

JDBC Statement interface

It is used to send SQL statements to the database or to execute SQL commands

Add data (insert)

public class TestInsert {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. Load drive
        Class.forName("com.mysql.cj.jdbc.Driver2");
        //2. Create database connection
        String url = "jdbc:mysql://127.0.0.1:3306/mydb?		useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String pwd = "root";
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //3. Create SQL command sender
        Statement st = conn.createStatement();
        String sql = "insert into dept values(50,'central section','Beijing')";
        //4. Send SQL command through Statement and get the result
        int rows = st.executeUpdate(sql);
        System.out.println("After execution, the data" + rows + "Impact on row data");
        //5. Close resources
        st.close();
        conn.close();
    }
}

Modify data (update)

public class TestUpdate {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. Load drive
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. Create database connection
        String url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String pwd = "root";
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //3. Create SQL command sender
        Statement st = conn.createStatement();
        String sql = "update  dept set dname='Sales Department'where deptno=50 ";
        //4. Send SQL command through Statement and get the result
        int rows = st.executeUpdate(sql);
        System.out.println("After execution, the data" + rows + "Impact on row data");
        //5. Close resources
        st.close();
        conn.close();
    }
}

Delete data

public class TestDelete {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. Load drive
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. Create database connection
        String url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String pwd = "root";
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //3. Create SQL command sender
        Statement st = conn.createStatement();
        String sql = "delete from dept where deptno=50";
        //4. Send SQL command through Statement and get the result
        int rows = st.executeUpdate(sql);
        System.out.println("After execution, the data" + rows + "Impact on row data");
        //5. Close resources
        st.close();
        conn.close();
    }
}

Query data (select)

public class TestExecuteQuery {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
      
        String url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String pwd = "root";
        //Load driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        //Establish level training
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //Create sql command sender
        Statement st = conn.createStatement();
        String sql = "select * from emp";
        //Send sql command and return data
        ResultSet rs = st.executeQuery(sql);
    	//ResultSet usage 2
        while (rs.next()) {
            //Get row data
            int enmno = rs.getInt(1);
            String eName=rs.getString(2);
            String job=rs.getString(3);
            int mgr = rs.getInt(4);
            Date hireDate= rs.getDate(5);
            double sal=  rs.getDouble(6);
            double comm=  rs.getDouble(7);
            int deptNo =rs.getInt(8);
            StringBuilder str = new StringBuilder();
            str.append(enmno+"  ").append(eName+"  ").append(job+"  ").append(mgr+"  ").append(hireDate+"  ").append(sal+"  ").append(comm+"  ").append(deptNo+"  ");
            System.out.println(str.toString());
        }
        rs.close();
        st.close();
        conn.close();
         //ResultSet usage 2
        /*while (rs.next()) {
            int enmno = rs.getInt("EMPNO");
            String eName=rs.getString("ENAME");
            String job=rs.getString("JOB");
            int mgr = rs.getInt("MGR");
            Date hireDate= rs.getDate("HIREDATE");
            double sal=  rs.getDouble("SAL");
            double comm=  rs.getDouble("COMM");
            int deptNo =rs.getInt("DEPTNO");
            StringBuilder str = new StringBuilder();
            str.append(enmno+"  ").append(eName+"  ").append(job+"  ").append(mgr+"  ").append(hireDate+"  ").append(sal+"  ").append(comm+"  ").append(deptNo+"  ");
            System.out.println(str.toString());
        }
*/
    }
}

Store multiple pieces of data in the database into the entity class and traverse the data

public class TestExecuteQuery2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        List<Emp> list = getAllEmp();
        showEmp(list);
    }
    public static List<Emp> getAllEmp() {
        String url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String pwd = "root";
        ResultSet rs = null;
        Connection conn = null;
        Statement st = null;
        List<Emp> list = new ArrayList<>();
        try {
            //Load driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            //Establish connection
            conn = DriverManager.getConnection(url, user, pwd);
            //Create sql command sender
            st = conn.createStatement();
            String sql = "select * from emp";
            //Send sql command to return data
            rs = st.executeQuery(sql);
            while (rs.next()) {
                //Get row data
                int enmno = rs.getInt("EMPNO");
                String eName = rs.getString("ENAME");
                String job = rs.getString("JOB");
                int mgr = rs.getInt("MGR");
                Date hireDate = rs.getDate("HIREDATE");
                double sal = rs.getDouble("SAL");
                double comm = rs.getDouble("COMM");
                int deptNo = rs.getInt("DEPTNO");
                Emp emp = new Emp(enmno,eName,job,mgr,hireDate,sal,comm,deptNo);
                list.add(emp);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //close resource
            if (null != rs) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != st) {
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    /**
     * Traversal set
     * */
    public static void showEmp(List<Emp> list){
        for(Emp e:list){
            System.out.println(e);
        }
    }

}

sql injection risk

The spliced content in the SQL statement destroys the original judgment logic of the SQL statement

select * from user where username='lisi' and password='ll' or '1'='1'

Using Statement

public static User login(String username,String password) {
         User users = null;
         Connection conn=null;
         int n=0;
         try {
             Class.forName("com.mysql.cj.jdbc.Driver");
             String url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
             String user = "root";
             String pwd = "root";
             conn = DriverManager.getConnection(url, user, pwd);

             Statement st = conn.createStatement();
             String sql = "select * from user where username='"+username+"' and password='"+password+"'";
             System.out.println(sql);
             ResultSet rs =  st.executeQuery(sql);
             if(rs.next()){
                 int id = rs.getInt("id");
                 String userName= rs.getString("username");
                 String pwd2 =rs.getString("password");
                 users= new User(id,userName,pwd2);
             }

         } catch (ClassNotFoundException e) {
             e.printStackTrace();
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             if (null != conn){
                 try {
                     conn.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
         }
         return users;
     }

Solution to sql injection risk: use PreparedStatement

public static User login2(String username,String password) {
        User users = null;
        Connection conn=null;
        int n=0;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url, user, pwd);

            String sql = "select * from user where username='"+username+"' and password='"+password+"'";

            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs =  ps.executeQuery();
            if(rs.next()){
                int id = rs.getInt("id");
                String userName= rs.getString("username");
                String pwd2 =rs.getString("password");
                users= new User(id,userName,pwd2);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (null != conn){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return users;
    }

JDBC PreparedStatement interface

Difference and relation between Statement and PreparedStatement

Contact: PreparedStatement extends Statement

Difference: 1 Preparedstatement has high security and can avoid SQL injection

​ 2. Preparedstatement is simple and not cumbersome, and there is no need for string splicing

​ 3. Preparedstatement has high performance and is used when performing DML operations on multiple identical databases

Add data (insert)

public class TestPreparedStatement {
  public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
  private static final String URL = "jdbc:mysql://127.0.0.1:3306/mydb?	    useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
  private static final String USER = "root";
  private static final String PWD = "root";
  public static void main(String[] args)throws Exception {
      insert(66,"asdfaf","jiadjfa");
  }
  /**
    * Function Description: add
    * @param:
    * @return:
    */
   public static void insert(int deptno,String dname,String loc)  throws Exception{
       Class.forName(DRIVER);
       Connection conn = DriverManager.getConnection(URL, USER, PWD);
       String sql = "insert into dept values(?,?,?) ";
       PreparedStatement ps = conn.prepareStatement(sql);
       ps.setInt(1,deptno);
       ps.setString(2,dname);
       ps.setString(3,loc);
       int n = ps.executeUpdate();
       ps.close();
       conn.close();
    }  
}

Modify data (update)

public class TestPreparedStatement {
  public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
  private static final String URL = "jdbc:mysql://127.0.0.1:3306/mydb?	    useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
  private static final String USER = "root";
  private static final String PWD = "root";
  public static void main(String[] args)throws Exception {
      insert(66,"asdfaf","jiadjfa");
  }
   /**
     * Function Description: modify
     * @param:
     * @return:
     */
  public static void update(int deptno,String dname)  throws Exception{
      Class.forName(DRIVER);
      Connection conn = DriverManager.getConnection(URL, USER, PWD);
      String sql = "update dept set dname=? where deptno=? ";
      PreparedStatement ps = conn.prepareStatement(sql);
      ps.setString(1,dname);
      ps.setInt(2,deptno);
      int n = ps.executeUpdate();
      ps.close();
      conn.close();
  }  
}

Delete data

public class TestPreparedStatement {
  public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
  private static final String URL = "jdbc:mysql://127.0.0.1:3306/mydb?	    useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
  private static final String USER = "root";
  private static final String PWD = "root";
  public static void main(String[] args)throws Exception {
      insert(66,"asdfaf","jiadjfa");
  }
  /**
    * Function Description: delete
    * @param:
    * @return:
    */
  public static void delete(int deptno)  throws Exception{
     Class.forName(DRIVER);
     Connection conn = DriverManager.getConnection(URL, USER, PWD);
     String sql = "delete from dept where deptno=? ";
     PreparedStatement ps = conn.prepareStatement(sql);
     ps.setInt(1,deptno);
     int n = ps.executeUpdate();
     ps.close();
     conn.close();
  }
}

Query data (select)

public class TestPreparedStatement {
  public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
  private static final String URL = "jdbc:mysql://127.0.0.1:3306/mydb?	    useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
  private static final String USER = "root";
  private static final String PWD = "root";
  public static void main(String[] args)throws Exception {
      insert(66,"asdfaf","jiadjfa");
  }
 /**
   * Function Description: fuzzy query
   * @param:
   * @return:
   */
 public static void find(String n) throws Exception {
      Class.forName(DRIVER);
      Connection conn = DriverManager.getConnection(URL, USER, PWD);
      String sql = "select * from emp where ename like ? ";
      PreparedStatement ps = conn.prepareStatement(sql);
      ps.setString(1,"%"+n+"%");
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
          int enmno = rs.getInt("EMPNO");
          String eName=rs.getString("ENAME");
          String job=rs.getString("JOB");
          int mgr = rs.getInt("MGR");
          Date hireDate= rs.getDate("HIREDATE");
          double sal=  rs.getDouble("SAL");
          double comm=  rs.getDouble("COMM");
          int deptNo =rs.getInt("DEPTNO");
          StringBuilder str = new StringBuilder();
          str.append(enmno+"  ").append(eName+"  ").append(job+"  ").append(mgr+"  ").append(hireDate+"  ").append(sal+"  ").append(comm+"  ").append(deptNo+"  ");
            System.out.println(str.toString());
     }
        rs.close();
        ps.close();
        conn.close();
  }
}

JDBC transaction

Prohibit automatic transaction commit (setAutoCommit)

​ setAutoCommit(false);

Manually commit a transaction

​ conn.commit()

rollback

​ conn.rollback()

public class TestTransaction {
    public static void main(String[] args) {
        //1. Load drive
        PreparedStatement pst = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
            String user = "root";
            String pwd = "root";
            //2. Create database connection
            conn = DriverManager.getConnection(url, user, pwd);
            //Prohibit automatic transaction submission
            conn.setAutoCommit(false);
            String sql = "update account set money= money-100 where username=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, "Zhang San");
            //Send SQL command
            pst.executeUpdate();
            sql = "update account set money=money+100 where username=?";
            pst = conn.prepareStatement(sql);
            pst.setString(1, "Li Si");
            //Send SQL command
            pst.executeUpdate();
        } catch (ClassNotFoundException e) {
            try {
                //RollBACK 
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (null != conn) {
                try {
                    //Commit transaction
                    conn.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            //close resource
            if (null != pst) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (null != conn) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

JDBC API summary

Connection interface method

Represents a database connection

DriverManager class method

Basic services that manage a set of JDBC drivers

Statement interface method

Used to send sql statements to the database or to execute sql statements

ResultSet interface method

The data in the ResultSet is arranged row by row. Each row has multiple fields and a record pointer. The data row indicated by the pointer is called the current data row. We can only operate the current data row. If we want to get a record, we should use the next() method of ResultSet. If we want to get all the records in ResultSet, we should use the while loop

The ResultSet object automatically maintains a cursor pointing to the current data row. Each time the next() method is called, the cursor moves down one line.

Properties class use

Main function: read the contents from the file into the Properties

Write content to file

  **relationship**: 	Properties extends  Hashtable<Object,Object>

Features: both key and value are strings

The Properties class reads the contents of the file

​

public class TestProperties {
    public static void main(String[] args) {
        Properties pro = new Properties();   
        try {
            pro.load(new FileInputStream("Z:\\IdeaProjects\\20.empmgr\\src\\test.txt"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println(pro);
    }
}

## File configuration
driver=com.mysql.cj.jdbc.Driverurl=jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
user=rootpassword=root
initSize=5
maxSize=10

public class TestProperties {
    public static void main(String[] args) {
        Properties pro = new Properties();   
       InputStream is = ConnectionPool.class.getResourceAsStream("/jdbc.properties");
        try {
            properties.load(is);
             // Read content
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            pasword = properties.getProperty("password");
            initSize = Integer.parseInt(properties.getProperty("initSize"));
            maxSize = Integer.parseInt(properties.getProperty("maxSize"));
            
            System.out.println("driver="+driver);
            System.out.println("url="+url);
            System.out.println("user="+user);
            System.out.println("password="+pasword);
         } catch (IOException e) {
            e.printStackTrace();
        } 
    }
}

The Properties class writes content to the file

public class TestProperties {
    public static void main(String[] args) throws IOException {
        Properties prop = new Properties();
        prop.put("cn", "China");
        prop.put("jp", "Japan");
        prop.put("us", "the United States");
        prop.put("us", "America");
        prop.setProperty("uk", "the United Kingdom");
        //Write key value pair to file
        OutputStream os = new FileOutputStream("e:/countries.proerties");
        prop.store(os, "countries name map");
    }
}

JDBC code optimization to reduce code reuse

Simplify database connection operations

public static Connection getConnection() {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://127.0.0.1:3306/" +
                "mytest?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

JDBC DML (database operation language) code simplification

 /**
   * Simplify DML operation 1
   * */
    public static int getExecuteUpdate(String sql, Object[] params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;//Default add failed
        try {
            //2. Establish connection with database
            conn = DBUtil.getConnection();
            //3. Create SQL named sender
            //4. Prepare an SQL command, send it with the SQL sender, and return the result
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            //5. Treatment results
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(1111);
            e.printStackTrace();
            logger.error("implement DML Operation failed:" + e);
        } finally {
            //6. Close resources
            DBUtil.closeAll(rs, pstmt, conn);
        }
        return n;
    }
/**
  * Simplify DML operation 2
  * */
public static int getExecuteUpdate(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;//Default add failed
        try {
            //2. Establish connection with database
            conn = DBUtil.getConnection();
            //3. Create SQL named sender
            //4. Prepare an SQL command, send it with the SQL sender, and return the result
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            //5. Treatment results
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(1111);
            e.printStackTrace();
            logger.error("implement DML Operation failed:" + e);
        } finally {
            //6. Close resources
            DBUtil.closeAll(rs, pstmt, conn);
        }
        return n;
    }

JDBC DQL (database query language) code simplification

 /**
     * Get database information dynamically using reflection 1
     * */
    public static<T> List <T> executeQuery(String sql, Object[] params,Class clazz) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<T> list =  new ArrayList<T>();
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            //4. Prepare an SQL command, send it with the SQL sender, and return the result
            for (int i = 0; i <params.length ; i++) {
                pstmt.setObject(i+1,params[i]);
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            //5. Treatment results
            while (rs.next()) {
                 T entiy = (T)clazz.newInstance();
                for (int i = 0; i <count ; i++) {
                    //Gets the name of the current column
                    String columnName = rsmd.getColumnName(i+1);
                    //Gets the value of the current column
                    Object value = rs.getObject(columnName);
                    //Get method name through reflection call
                    String methodName = "set"+columnName.substring(0,1).toUpperCase()+
                            columnName.substring(1).toLowerCase();
                    //Get parameter type through reflection
                    Class parameterType = Class.forName(rsmd.getColumnClassName(i+1));
                    Method method = clazz.getMethod(methodName,parameterType);
                    //Assigns the value of the current column to the entity object
                    method.invoke(entiy,value);
                }
                list.add(entiy);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            //6. Close resources
            DBUtil.closeAll(rs, pstmt, conn);
        }
        return list;
    }
    /**
     * Get database information dynamically using reflection 1
     * */
    public static<T> List <T> executeQuery(String sql, Object[] params,String classname) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<T> list =  new ArrayList<T>();
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            //4. Prepare an SQL command, send it with the SQL sender, and return the result
            for (int i = 0; i <params.length ; i++) {
                pstmt.setObject(i+1,params[i]);
            }
            rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            //5. Treatment results
            while (rs.next()) {
                Class clazz = Class.forName(classname);
                T entiy = (T)clazz.newInstance();
                for (int i = 0; i <count ; i++) {
                    //Gets the name of the current column
                    String columnName = rsmd.getColumnName(i+1);
                    //Gets the value of the current column
                    Object value = rs.getObject(columnName);
                    //Get method name through reflection call
                    String methodName = "set"+columnName.substring(0,1).toUpperCase()+
                            columnName.substring(1).toLowerCase();
                    //Get parameter type through reflection
                    Class parameterType = Class.forName(rsmd.getColumnClassName(i+1));
                    System.out.println(parameterType);
                    Method method = clazz.getMethod(methodName,parameterType);
                    //Assigns the value of the current column to the entity object
                    method.invoke(entiy,value);
                }
                list.add(entiy);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            //6. Close resources
            DBUtil.closeAll(rs, pstmt, conn);
        }
        return list;
    }

Connection Pool technology (Pool)

Putting multiple connections into a container called a connection pool

The connection pool solution is to establish multiple database connection objects in advance when the application starts, and then save the connection objects to the connection pool.

When a customer request arrives, a connection object is taken out of the pool to serve the customer.

When the request is completed, the client calls the close() method to put the connection object back into the pool

Queue for requests with more connections than the number of connections in the connection pool.

Applications can also dynamically increase or decrease the number of connections in the pool according to the utilization of connections in the pool

Advantages of connection pool technology:

​ 1. When multiple threads execute concurrently, it can avoid creating a large number of linked objects at one time in a short time, reduce the computing pressure of the computer per unit time, and improve the response speed of the program

​ 2. The repeated use of connection objects can greatly reduce the creation times of link objects and reduce the consumption of resources

Traditional connection:

Existing problems: the connection object must be created every time the DML and DQL are executed. After the DML and DQL are executed, the connection object will be destroyed

Create a new one each time The creation and destruction of this object consume system resources

public final class ConnectionPool {
    private static final Properties properties;
    private static String DRIVER;
    private static String URL;
    private static String USER;
    private static String PASSWORD;
    private static  int initSize=5;
    private static  int maxSize=10;
    private static LinkedList<Connection> pool = new LinkedList<Connection>();
    static {
        properties = new Properties();
        InputStream is = ConnectionPool.class.getResourceAsStream("/jdbc.properties");
        try {
            properties.load(is);
            DRIVER = properties.getProperty("driver");
            URL = properties.getProperty("url");
            USER = properties.getProperty("user");
            PASSWORD = properties.getProperty("password");
            initSize = Integer.parseInt(properties.getProperty("initSize"));
            maxSize = Integer.parseInt(properties.getProperty("maxSize"));
            Class.forName(DRIVER);
            for (int i = 0; i < initSize; i++) {
                Connection conn = createConncetion();
                pool.add(conn);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    /**
    * Function Description: create thread
    * @param:
    * @return:
    */
    private static Connection createConncetion() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    /**
    * Function Description: get thread
    * @param:
    * @return:
    */
    public static Connection getConnection(){
        Connection conn = null;
        if(pool.size()>0){
            conn =pool.removeFirst();
            System.out.println("There are still connections in the connection pool. Take them"+conn.hashCode());

        }else{
            conn = createConncetion();
            System.out.println("There is no connection in the connection pool. Create a connection"+conn.hashCode());
        }
        return conn;
    }
    /**
    * Function Description: put back the thread
    * @param:
    * @return:
    */
    public static void returnConnection(Connection conn){
        if(pool.size()<maxSize){
            pool.addLast(conn);
            System.out.println("There is spare space in the connection pool,Put in connection"+conn.hashCode());
        }else{
            if(null!=conn){
                try {
                    System.out.println("The connection pool is full. Close the connection"+conn.hashCode());
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}