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);
}
}