JDBC learning notes

Posted by saraadmin on Fri, 25 Feb 2022 07:44:41 +0100

There are five ways to obtain database connection:

 

 

Method 5 [final version] only this one can be practiced (doge):

Put the basic information into a configuration file.

 

Benefits of mode 5:


1. Realize the separation of data and code, and realize decoupling

2. If you need to modify the configuration file information, you can avoid the repackaging of the program [meaning that you can avoid modifying our code, but directly modify the configuration file]

PreparedStatement and Statement:

PreparedStatement is the sub interface of Statement, but we use PreparedStatement;

PreparedStatement can precompile in advance

Therefore, PreparedStatement solves the problem of sql injection, which is simply adding an additional placeholder

However, we usually use placeholders at the filter conditions to represent uncertainty

As shown in the figure: just like the writing method of Statement, write a number directly without placeholder

PreparedStatement can operate data in batch more efficiently, because it can precompile placeholders. After precompiling once, we don't need to check again, so as to realize batch operation

PreparedStatement implements the code related to the addition, deletion, modification and query of table data

Tools:

package JDBC_Utils;

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

public class jdbc_Util {
    //Establish database connection
    public static Connection getConnection() throws Exception{
        InputStream jdbc_pro = jdbc_Util.class.getClassLoader().getResourceAsStream("JDBC_pro");
        Properties properties = new Properties();
        properties.load(jdbc_pro);
        String user=properties.getProperty("user") ;
        String password=properties.getProperty("password") ;
        String url=properties.getProperty("url") ;
        String driverClass=properties.getProperty("driverClass") ;
        Class.forName(driverClass) ;
        Connection connection= DriverManager.getConnection(user,password,url) ;
        return connection ;
    }
    //Close connection and Statement
    public static void closeResoure(Connection connection, Statement s) {
        if (connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (s!=null) {
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(Connection connection, Statement s, ResultSet r) {
        try {
            if (connection!=null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        try {
            if (s!=null) {
                    s.close();
                }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        try {
            if (r!=null)
                {
                    r.close();
                }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
    }
}

Implementation class:

package JDBC;

import JDBC_Utils.jdbc_Util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

public class PreparedStatementUpdateTest {
    // Add a record to the customers table
    public void testInsert() {
        Connection connection= null;
        PreparedStatement ps= null;
        try {
            //1. Load the four information in the file
            InputStream jdbc_pro = ClassLoader.getSystemClassLoader().getResourceAsStream("JDBC_pro");
            //Get four information in the file
            Properties properties = new Properties();
            properties.load(jdbc_pro);
            String user=properties.getProperty("user") ;
            String password=properties.getProperty("password") ;
            String url=properties.getProperty("url") ;
            String driverClass=properties.getProperty("driverClass") ;
            //Load driver
            Class.forName(driverClass) ;
            //Get connection
            connection = DriverManager.getConnection(driverClass);
            //Precompile the sql statement and return an instance of PreparedStatement
            String sql
                    ="insert into customers(name,email,birth) values(?,?,?)" ;//? Represents a placeholder
            ps = connection.prepareStatement(sql);
            //Fill placeholder
            ps.setString(1,"nezha");
            ps.setString(2,"nezha@gmail.com") ;
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd") ;
            Date date=sdf.parse("1000-01-01") ;
            ps.setDate(3, (java.sql.Date) new Date(date.getTime()));
            //6. Perform the operation
            ps.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //close
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //Modify a record in the customers table
    public void testUpdate() throws Exception{
        Connection connection= jdbc_Util.getConnection();
        String sql="update customers set name=? where id = ";
        PreparedStatement ps=connection.prepareStatement(sql);
        ps.setObject(1,"Mozart");
        ps.setObject(2,18);
        ps.execute();
        jdbc_Util.closeResoure(connection,ps);
    }
    //General addition, deletion, modification and query operations
    public void update(String sql,Object ...args) throws Exception{
        //The number of placeholders in sql is the same as the length of variable parameters
        //1. Get the connection to the database
        Connection connection=jdbc_Util.getConnection();
        //2. Precompile the sql statement and return the instance of PreparedStatement
        PreparedStatement ps=connection.prepareStatement(sql) ;
        //3. Fill placeholder
        for (int i=0;i< args.length;i++) {
            ps.setObject(i+1,args[i]);
        }
        //4. Implementation
        ps.execute() ;
        //5. Resource shutdown
        jdbc_Util.closeResoure(connection,ps);
    }
}

PreparedStatement is a query operation for a table

Situation 1: as like as two peas in our table, the attribute name is identical.

Main category:

package JDBC;

import JDBC_Bean.Customer;
import JDBC_Utils.jdbc_Util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Date;

/**
 *  Query operation for Customers table
 */
public class CustomerForQuery {
    /**
     * General operation of table query
     * @throws Exception
     */
    public Customer queryForCustomers(String sql,Object...args) {
        Connection connection= null;
        PreparedStatement ps= null;
        ResultSet rs= null;
        Customer cust= null;
        try {
            connection = jdbc_Util.getConnection();
            ps = connection.prepareStatement(sql);
            //Fill placeholder
            for (int i=0;i< args.length;i++) {
                ps.setObject(i+1,args[i]);
            }
            //Return result set
            rs = ps.executeQuery();
            //Get metadata in result set
            ResultSetMetaData rsmd= rs.getMetaData();
            //Get the number of columns in the result set through ResultSetMetaData
            int columnCount=rsmd.getColumnCount() ;
            cust = null;
            if(rs.next()) {
                 cust = new Customer();
                //The result of processing each row in a data set
                for (int i=0;i<columnCount ;i++) {
                    //Get column values, such as the value size of name, student number, and so on
                    //The value size of the column is still in the result set, but the corresponding column name is at the metadata level
                    Object columValue=rs.getObject(i+1) ;
                    //Get the column name of each column, that is, name or student number, etc
                    //String columnName=rsmd.getColumnName(i+1) ;
                    //The reason for using getColumnLabel() method instead of getColumnName(i+1) is:
                    //It is possible that the column name in the data table we want to query is different from the property name in our JavaBean class, so we need to use the alias of the column
                    //Using the getColumnLabel() method, we first return the column alias. If there is no column alias, we return the column name
                    String columnLabel=rsmd.getColumnLabel(i+1) ;
                    //The columnLabel attribute specified to cust object is assigned columnValue;
                    // Through reflection, we get the attribute object
                    Field field=Customer.class.getDeclaredField(columnLabel);
                    //It is possible that the property is private, so we use blasting here
                    field.setAccessible(true);
                    //Here, we assign the columnLabel attribute corresponding to cust object to columnvalue
                    //Here we use reflection. Generally, we use object reference Set (attribute object, the value to be assigned to the attribute)
                    //But among our reflections: attribute objects Set (object reference, value to be given to attribute)
                    field.set(cust,columValue);
                }
            }
            return cust ;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.close(connection,ps,rs);
        }
       return null ;
    }
    public void testQuery01() throws Exception{
        Connection connection=jdbc_Util.getConnection();
        String sql="select id,name,email,birth from customers where id = ?" ;
        PreparedStatement ps=connection.prepareStatement(sql);
        //The first parameter represents our operation to query the position of the first row
        //The second parameter means that we fill in only one placeholder, that is, the value of the first parameter, but it is uncertain
        ps.setObject(1,1);
        //Execute, return result set
        ResultSet resultSet=ps.executeQuery();
        //Processing result set
        if (resultSet.next()) {
            //Judge whether the next item of the result set has data. If so, return true and the pointer points to the next item

            //Get the field values of the current data
            int id=resultSet.getInt(1) ;
            String name=resultSet.getString(2) ;
            String email=resultSet.getString(3) ;
            Date birth=resultSet.getDate(4);
            //Encapsulate data into an object
            Customer customer=new Customer(id,name,email,birth) ;
        }
    }
}

Customer JavaBean package class

package JDBC_Bean;

import java.util.Date;

/**
 * ORM Programming idea:
 * A data table corresponds to a java class
 * A record in the table corresponds to an object of the java class
 * A field in the table corresponds to an attribute of the java class
 */
public class Customer {
    private int id ;
    private String name ;
    private String email ;
    private Date birth ;

    public Customer() {
    }

    public Customer(int id, String name, String email, Date birth) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }
}

Case 2: the attribute name is different from the column name in the table

 

At this time, we need to use the alias of the column to solve this problem. Then we need to use:

Note that the alias of the column we take should be consistent with the attribute name

Order class under JavaBean package

package JDBC_Bean;

import java.util.Date;

public class Order {
    private int orderId;
    private String orderName;
    private Date orderDate;

    public Order() {
    }

    public Order(int orderId, String orderName, Date orderDate) {
        this.orderId = orderId;
        this.orderName = orderName;
        this.orderDate = orderDate;
    }

    public int getOrderId() {
        return orderId;
    }

    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }

    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    public Date getOrderDate() {
        return orderDate;
    }

    public void setOrderDate(Date orderDate) {
        this.orderDate = orderDate;
    }

    @Override
    public String toString() {
        return "Order{" +
                "orderId=" + orderId +
                ", orderName='" + orderName + '\'' +
                ", orderDate=" + orderDate +
                '}';
    }
}

Class for query operation:

package JDBC;

import JDBC_Bean.Order;
import JDBC_Utils.jdbc_Util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Date;

public class OrderForQuery {
    public static void main(String[] args) throws Exception{
        //Test
        String sql = "select order_id orderId,order_name orderName,order_date orderDate from order where orderId = ?" ;
        Order order=OrderForQuery(sql,1) ;
        System.out.println(order);
    }
    public static Order OrderForQuery(String sql,Object...args) throws Exception{
        Connection connection=jdbc_Util.getConnection() ;
        PreparedStatement ps=connection.prepareStatement(sql) ;
        //Identify placeholders
        for (int i=0;i<args.length;i++) {
            ps.setObject(i+1,args[i]);
        }
        ResultSet rs= ps.executeQuery();
        ResultSetMetaData rsd= rs.getMetaData();
        int columnCount= rsd.getColumnCount();
        if(rs.next()) {
            Order order=new Order();
            for (int i=0;i<columnCount;i++) {
                //The value returned is the value of the column, which may be of many types
                Object columnValue=rs.getObject(i+1);
                //Find out the column name and use the getColumnLabel method. If there is an alias of the column, the alias of the column will be returned
                //If none, the column name is returned
                String columnLabel =rsd.getColumnLabel(i+1) ;
                //Assignment using reflection
                Field declaredField = Order.class.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);//Blasting is to access private properties
                //Assign values to each column name
                declaredField.set(order,columnValue);
            }
            return order ;
        }
        return null ;
    }
    public void testQuery1() {
        Connection connection=null ;
        PreparedStatement ps = null ;
        ResultSet rs = null;
        try {
            connection=jdbc_Util.getConnection();
            String sql="select order_id,order_name,order_date from 'order' where order_id = ?" ;
            ps=connection.prepareStatement(sql) ;
            //The first parameter indicates that we want to query the first data, and the second parameter indicates to fill in a placeholder
            ps.setObject(1,1);
            //Get result set
            rs=ps.executeQuery() ;
            if (rs.next()) {
                int id = (int) rs.getObject(1) ;
                String name = (String) rs.getObject(2) ;
                Date date=(Date) rs.getObject(3) ;
                Order order=new Order(id,name,date) ;
                System.out.println(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.close(connection,ps,rs) ;
        }
    }
}

Illustration - the flow of the PreparedStatement query data table

When we want to concatenate a variable in a string, we want to concatenate it

 

Splice the double quotation marks

PreparedStatement refers to queries for different tables

package JDBC;

import JDBC_Utils.jdbc_Util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * Realize the query of different tables
 */
public class PreparedStatementQueryTes {
    //Realize the query of multiple tables
    public <T> List<T> getForList(Class<T> clazz,String sql,Object...args) throws Exception{
        Connection connection=jdbc_Util.getConnection();
        PreparedStatement ps=connection.prepareStatement(sql) ;
        for (int i=0;i<args.length;i++) {
            ps.setObject(1,i+1);
        }
        ResultSet rs=ps.executeQuery();
        ResultSetMetaData rsd=rs.getMetaData();
        int columnCount=rsd.getColumnCount();
        ArrayList<T> arrayList=new ArrayList<>();
        while (rs.next()) {
            T t=clazz.newInstance();
            for (int i=0;i<columnCount;i++) {
                Object columnValue = rs.getObject(i + 1);
                String columnLabel=rsd.getColumnLabel(i+1) ;
                Field field = clazz.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t,columnValue);
            }
            arrayList.add(t);
        }
        jdbc_Util.close(connection,ps,rs);
        return arrayList;
    }
    //The first parameter passed is actually equal to the class name class .  Is a class object. For example: class < T > clazz = class name class
    public <T> T getInstance(Class<T> clazz,String sql,Object...args) throws Exception{
        //1. Establish connection first
        Connection connection=jdbc_Util.getConnection();
        //2. Get the Prepartment object
        PreparedStatement ps= connection.prepareStatement(sql);
        //3. Given placeholder
        for (int i=0;i< args.length;i++) {
            ps.setObject(1,i+1);
        }
        //4. Get ResultSet object
        ResultSet rs=ps.executeQuery() ;
        //5. Get metadata object
        ResultSetMetaData rsd=rs.getMetaData();
        //Get the number of columns
        int columnCount= rsd.getColumnCount();
        if (rs.next()) {
            //6. The instance object of Class corresponding to the data table obtained each time to prepare for subsequent reflection.
            T t=clazz.newInstance();
            for (int i=0;i<columnCount;i++) {
                //Get column value
                Object columnValue = rs.getObject(i + 1);
                //Get column name
                String columnLabel = rsd.getColumnLabel(i + 1);
                //Reflect to get attribute object
                Field field = clazz.getDeclaredField(columnLabel);
                field.setAccessible(true);
                //The reflection format is quite the opposite of our daily format: instance object reference attribute
                //In reflection are attribute objects For instance object reference, it's good to compare the set method here.
                field.set(t,columnValue);
            }
            return t;
        }
        jdbc_Util.close(connection,ps,rs);
        return null ;
    }
}

practice:

1.

package JDBC_test;

import JDBC_Utils.jdbc_Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class test01 {
    public void testInsert() throws Exception{
        Scanner scanner = new Scanner(System.in);
        System.out.print("Please enter user name:");
        String name = scanner.next();
        System.out.print("Please enter email address:");
        String email = scanner.next();
        System.out.print("Please enter birthday:");
        String birthday = scanner.next();//'1992-09-08'
        String sql="insert into customers(name,email,birth) values(?,?,?)";
        int insertCount=update(sql,name,email,birthday);
        if(insertCount>0) {
            System.out.println("Added successfully");
        } else {
            System.out.println("Add failed");
        }
    }
    public int update(String sql,Object...args) throws Exception{
        Connection connection=jdbc_Util.getConnection();
        PreparedStatement ps=connection.prepareStatement(sql);
        for(int i=0;i<args.length;i++) {
            ps.setObject(1,args[i]);
        }
        jdbc_Util.closeResoure(connection,ps);
        return ps.executeUpdate();
    }
}

2.

package JDBC_test;

import JDBC_Utils.jdbc_Util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;

public class test02 {
    /**
     * Question 1: add a record to the examstudent table
     * @throws Exception
     */
    public void insertStuInformation() throws Exception{
        Scanner scanner = new Scanner(System.in);
        System.out.print("Level 4/Level 6:");
        int type = scanner.nextInt();
        System.out.print("ID number:");
        String IDCard = scanner.next();
        System.out.print("Admission No.:");
        String examCard = scanner.next();
        System.out.print("Student Name:");
        String studentName = scanner.next();
        System.out.print("City:");
        String location = scanner.next();
        System.out.print("Examination results:");
        int grade = scanner.nextInt();
        String sql="insert into examstudent(type,IDCard,examCard,studentName,location,grade) values(?,?,?)";
        int count=update(sql,type,IDCard,examCard,studentName,location,grade);
        if(count>0) {
            System.out.println("Information entered successfully");
        } else {
            System.out.println("Information entry failed");
        }
    }
    public int update(String sql,Object...args) {
        Connection connection=null;
        PreparedStatement ps=null;
        try {
            connection=jdbc_Util.getConnection();
            ps=connection.prepareStatement(sql) ;
            for (int i=0;i< args.length;i++) {
                ps.setObject(1,args[i]);
            }
            //Returns a value when the operation is executed and executed successfully
            return ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(connection,ps);
        }
        return 0;
    }

    /**
     * Question 2: query student achievement information according to ID number or ticket number.
     * @throws Exception
     */
    public void FindStu() throws Exception{
        System.out.println("Please enter your choice:");
        System.out.println("a:Means to select id card to query student information");
        System.out.println("b:Indicates to select the admission ticket number to query student information");
        Scanner scanner = new Scanner(System.in);
        String choice=scanner.next();
        if ("a".equalsIgnoreCase(choice)){
            System.out.println("Please input your ID number.");
            String IDCard=scanner.next();
            String sql="select type,IDCard,examCard,studentName,location,grade " +
                    "from examstudent where IDCard = ?";
            Student student=find(Student.class,sql,IDCard);
            if (student!=null){
                System.out.println(student);
            } else {
                System.out.println("Your ID card is entered incorrectly");
            }
        } else if ("b".equalsIgnoreCase(choice)) {
            System.out.println("Please enter your admission number");
            String examCard=scanner.next();
            String sql="select type,IDCard,examCard,studentName,location,grade from " +
                    "examstudent where examCard = ? ";
            Student student=find(Student.class,sql,examCard);
            if (student!=null){
                System.out.println(student);
            } else {
                System.out.println("Your admission card number is entered incorrectly");
            }
        } else {
            System.out.println("Your input is incorrect");
        }
    }
    public <T> T find(Class<T> clazz,String sql,Object...args) throws Exception{
        Connection connection=jdbc_Util.getConnection();
        PreparedStatement ps=connection.prepareStatement(sql);
        for (int i=0;i< args.length;i++) {
            ps.setObject(1,args[i]);
        }
        ResultSet rs= ps.getResultSet();
        ResultSetMetaData rsd= rs.getMetaData();
        int columnCount= rsd.getColumnCount();
        if (rs.next()) {
            T t=clazz.newInstance();
            for (int i = 0;i<columnCount;i++) {
                Object columnValue=rs.getObject(i+1) ;
                String columnLabel=rsd.getColumnLabel(i+1) ;
                Field field=clazz.getDeclaredField(columnLabel);
                field.setAccessible(true);
                field.set(t,columnValue);
            }
            return t;
        }
        return null;
    }
    /**
     * Question 3: delete the specified student information
     */
    public void deleteStuInformation() throws Exception{
        while (true) {
            System.out.println("Please enter the student's test number");
            Scanner scanner = new Scanner(System.in);
            String examCard=scanner.next();
            String sql="select examCard from examstudent where examCard = ?" ;
            int update = update(sql, 1);
            if (update>0) {
                String sql2="delete from examstudent where examCard = ?";
                int update1 = update(sql2, 2);
                if (update1>0) {
                    System.out.println("Deleted successfully");
                    break;
                } else {
                    System.out.println("Delete failed");
                }
            } else {
                System.out.println("Input error");
            }
        }
    }
    //optimization
    public void WelltestDelete() {
        while (true) {
            System.out.println("Please enter the student's test number");
            Scanner scanner = new Scanner(System.in);
            String examCard = scanner.next();
            String sql = "delete from examstudent where examCard = ?";
            int deleteCount = update(sql, 2);
            if (deleteCount > 0) {
                System.out.println("Deleted successfully");
                break;
            } else {
                System.out.println("Delete failed,Please re-enter");
            }
        }
    }
}

How to view the type of table data?

Add a picture in the form of stream

Insert and query Blob type fields into the data table

package JDBC;

import JDBC_Bean.Customer;
import JDBC_Utils.jdbc_Util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class BlobTest {
    /**
     * Insert field of Blob type
     * @throws Exception
     */
        public void testInsert() throws Exception {
        Connection connection=jdbc_Util.getConnection();
        String sql="Insert into customers(name,email,birth,photo) values(?,?,?,?)";
        PreparedStatement ps=connection.prepareStatement(sql);

        ps.setObject(1,"Yuan Hao");
        ps.setObject(2, "yuan@qq.com");
        ps.setObject(3,"1992-09-08");
        FileInputStream is = new FileInputStream(new File("girl.jpg"));
        ps.setBlob(4, is);
        ps.execute();
       jdbc_Util.closeResoure(connection,ps);
    }
    /**
     * Query the Blob type field in the data table customers
     */
    public void Find() throws Exception{
        Connection connection=jdbc_Util.getConnection();
        String sql="select id,name,email,birth,photo from customers where id = ? " ;
        PreparedStatement ps=connection.prepareStatement(sql);
        ps.setInt(1,2);
        ResultSet rs= ps.getResultSet();
        if (rs.next()) {
            int id=rs.getInt("id");
            String name=rs.getString("name");
            String email=rs.getString("email");
            Date birth=rs.getDate("birth");
            Customer customer = new Customer(id,name, email, birth);
            //Download the Blob type fields and save them locally as files
            Blob photo=rs.getBlob("photo");
            InputStream binaryStream = photo.getBinaryStream();
            FileOutputStream fileOutputStream = new FileOutputStream("xxx.jpg");
            byte[] buffer=new byte[1024];
            int len ;
            while ((len=binaryStream.read(buffer))!=-1) {
                fileOutputStream.write(buffer,0,len);
            }
        }
    }
}

Using PreparedStatement to realize the operation of batch data

In order to realize batch operation, we need to use the jar package of 1.37,

1. Delete the jar package of 1.7 first

 

 2. Then add the jar package of version 1.7 to Bath

 

package JDBC;

import JDBC_Utils.jdbc_Util;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertTest {
    //Batch insertion mode 2
    public void testInsert1() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //Get start time
            long start = System.currentTimeMillis();
            //Get connection
            connection = jdbc_Util.getConnection();
            //Figure out the sql statement
            String sql =
                    "insert into goods(name) values(?)";
            ps = connection.prepareStatement(sql);
            for (int i = 0; i <= 20000; i++) {
                ps.setObject(1, "name_" + i);
                ps.execute();
            }
            long end = System.currentTimeMillis();
            System.out.println("The time taken is:" + (end - start));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            jdbc_Util.closeResoure(connection, ps);
        }
    }
        /*
         * Batch insertion method 3:
         * 1.addBatch(),executeBatch(),clearBatch()
         * 2.mysql The server turns off batch processing by default. We need a parameter to let mysql turn on batch processing support.
         * 		 ?rewriteBatchedStatements=true Write it after the url of the configuration file
         * 3.Use the updated MySQL driver: mysql-connector-java-5.1.37-bin jar
         */
        public void testInsert03 () throws Exception {
            long start = System.currentTimeMillis();
            Connection connection = jdbc_Util.getConnection();
            String sql = "insert into goods(name) values(?)";
            PreparedStatement ps = connection.prepareStatement(sql);
            for (int i = 0; i < 1000000; i++) {
                ps.setObject(1, "name_" + i);
                //1. Save sql
                ps.addBatch();
                if (i % 500 == 0) {
                    //2. Execute Batch
                    ps.executeBatch();
                    //3. Empty Batch
                    ps.clearBatch();
                }
            }
            jdbc_Util.closeResoure(connection, ps);
        }
    //Method 4 of batch insertion: set the connection not to allow automatic submission of data
    public void test04() throws Exception{
            long start = System.currentTimeMillis();
            Connection connection=jdbc_Util.getConnection();
            //Setting does not allow automatic submission of data
        connection.setAutoCommit(false);
        String sql ="insert into goods(name) values(?)" ;
        PreparedStatement ps= connection.prepareStatement(sql);
        for (int i =0;i<100000;i++) {
            ps.setObject(1,"name_"+i);
            ps.addBatch();
            if (i%500==0) {
                ps.executeBatch();
                ps.clearBatch();
            }
        }
        //Submit
        connection.commit();
        long end = System.currentTimeMillis();
        System.out.println(end-start);
        jdbc_Util.closeResoure(connection,ps);
        }
}

Topics: Database JDBC SQL