1. Introduction to JDBC
-
jdbc(Java DataBase Connectivity)
Developed on the basis of Odbc of C language, it is the earliest ORM (Object Relation Mapping) tool, which can use object-oriented Java code
Operate on relational database.
With the continuous development of ORM tools, many frameworks have been derived. Most of them are developed on the basis of JDBC, which is the basis of almost all frameworks and the most efficient ORM tool
JDBC is the first-party technology, and all interfaces are placed in Java SQL packageFull manual JDBC
Semi automatic myBatis
Fully automatic Hibernate1) Create relationship table
2) Create entity class
A: Generally placed on COM etoak. Po package or bean package
B: Try to use wrapper classes to encapsulate fields, and the field names correspond to each other
C: The empty parameter construction method must be written, and the parameter construction method must be covered as appropriate
D: toString()
3) Create factory class
A: Factory classes are generally placed on COM etoak. In the factory package, this class links to the database
B: To load the driver, you only need to load it once and place it in the static initialization block
C: Encapsulate a method return link
4) Writing dao layer
A: All Dao layers are placed on COM etoak. Dao package
B: Interface is used to set the methods to be implemented
C: The implementation class implements the methods in the interface
Create data table
DROP table if EXISTS person; CREATE table person ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10) NOT NULL, pass VARCHAR(10) NOT NULL, age INT, salary DOUBLE, birth DATE ); insert INTO person VALUES (null,'elena','12345',20,5000.00,'1990-06-03'); insert INTO person VALUES (null,'penny','123465',30,6000.00,'1993-06-13'); insert INTO person VALUES (null,'aleric','1234545',40,7000.00,'1992-06-05'); insert INTO person VALUES (null,'bonnie','1432345',20,3000.00,'1996-06-03'); insert INTO person VALUES (null,'matt','123345',24,15000.00,'1997-06-04'); insert INTO person VALUES (null,'damon','123445',23,25000.00,'1992-06-04'); insert INTO person VALUES (null,'user name','1232245',29,5000.00,'1998-07-01');
1. JavaBean entity class
If a Java class only encapsulates attributes and has no other methods, it is called a POJO class or entity class. It seems to be also called a JavaBean, which is usually placed in com xxx. PO or com, XXX Bean package
The attributes encapsulated in this class must be matched with the fields in the database, and must override the null parameter construction method, the full parameter construction method and the toString() method as appropriate
public class Person{
//To encapsulate a field with a wrapper class, it must correspond to the field in the table
private Integer id;
private String name;
private String pass;
private Integer age;
private Double salary;
private Date birth;
/*
* The constructor of the null parameter must be overridden
* After overriding the null parameter construction method, you can create an object with a new null parameter
* Person per = new Person();
* */
public Person() {
}
/*
* Override the full parameter construction method as appropriate, so that we can directly new an object with six parameters
* Person per = new Person(XX,XX,XX,XX,XX,XX);
* */
public Person(Integer id, String name, String pass, Integer age, Double salary, Date birth) {
this.id = id;
this.name = name;
this.pass = pass;
this.age = age;
this.salary = salary;
this.birth = birth;
}
//get and set methods of each attribute
//See if the requirement covers the toString() method
}
2. Factory
- Generally placed on * * com xxx. Factory package**
1. Load drive
static{
try{
Class.forName("com.mysql.jdbc.Driver");
}catch(Exception e){
e.printStackTrace();
}
}
2. Get links
public static Connection getConn(){
try{
//2. Load link
/*
* Full version:
* jdbc:mysql://localhost:Port number / database
* jdbc:mysql://Remote ip address: port number / database
* If the database is local, you can use the simplified version:
* jbdc:mysql:///database
* user:user name
* password: User password
* */
return DriverManager.getConnection("jdbc:mysql:///et1912","root","dream");
}catch(Exception e){
e.printStackTrace();
return null;
}
}
3. Release resources
public static void close(ResultSet rs, Statement st,Connection con){
try {
if(rs!=null)
rs.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if(st!=null)
st.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if(con!=null)
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
}
code
package com.etoak.factory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Factory {
/*
* A:Load driver
* Different database brand manufacturers provide different drivers, which can correctly identify the database brands of various manufacturers
* Syntax, etc. after loading the driver, you can use the same java code on the Java side to operate databases with different brands and different syntax
Here, the static initialization block can be loaded only once
* */
static{
try {
//Load the driver using reflection class loading
//The drivers of different brands are different. You need to import the jar package provided by the database manufacturer
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception ex) {
ex.printStackTrace();
}
}
/*
* B:Get connection
* Connect to the database. Provide the connection address, user name and password through the driver manager to link to the database
* */
public static Connection getCon(){
try {
/*
* Note that the three parameters here are
* url:Link address
* Full format
* jdbc:mysql://localhost:8080/Database name
* jdbc:mysql://Remote ip address: 8080 / database name
* If the database is local, you can use the simplified version
* jdbc:mysql:///Database name
* user:user name
* password:password
* */
return DriverManager.getConnection(
"jdbc:mysql:///et1912","root","etoaketoak");
} catch (Exception ex) {
ex.printStackTrace();
return null;
}
}
/*
* C:Release resources
* The sequence of releasing resources is as follows:
* First, close the result set, close the actuator, and finally close the connection
* */
public static void close(ResultSet rs, Statement st,Connection con){
try {
if(rs!=null)
rs.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if(st!=null)
st.close();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if(con!=null)
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
}
}
3.dao persistence layer
- dao(Data Access Object)
- Usually placed on * * com xxx. Bao Xia**
1. Interface
/*
* Dao:Data Access Object Data access object
* The interface here is generally implemented by the project manager or some specification makers
* How to implement does not care, only provides a specification
* DaoIf Is short for DaoInterface
* */
public interface PersonDaoIf {
//Add a user
public boolean addPerson(Person per);
//Delete a user by id
public boolean delPersonById(Integer id);
//Take all users
public List<Person> queryAll();
//Query by user name
public boolean queryName(String name);
//Query by user name and password
public Person queryPerson(String name,String pass);
//Modify user data
public boolean updatePerson(Person per);
}
public interface PersonDaoIf2 {
//1 add user
public boolean addPerson(Person per);
//2 delete by id
public boolean delPersonById(Integer id);
//3 delete by name
public boolean delPersonByName(String name);
//4 take all
public List<Person> queryAll();
//5 name duplicate check
public boolean queryName(String name);
//6 login query
public Person queryPerson(String name,String pass);
//7 take the total number of records
public Integer queryCount();
//8 paging query
public List<Person> queryPage(Integer index,Integer max);
//9 name fuzzy paging query
public List<Person> queryNameLikePage(String args,Integer index,Integer max);
//10 conditional paging query
public List<Person> queryLikePage(Person per,Integer index,Integer max);
//11 batch deletion
public boolean multiDel(String[] args);
//12 modification
public boolean updatePerson(Person per);
}
2. Interface implementation class [Statement]
- Create the implementation class PersonDaoImpl of the PersonDaoIf interface
- Right click the blank space * * (or alt + insert) * * select Implements Method to implement the abstract method in the interface
public class PersonDaoImpl implements PersonDaoIf{
//setup connection
Connection conn;
//Set actuator
Statement st;
//Result set
ResultSet rs;
@Override
public boolean addPerson(Person per) {
try {
//Writing sql statements
/*
* java.util.Date Cannot be spliced directly with string into sql statement through +
* There are two ways
* 1)Add Java util. Convert date to Java sql. Date, which can be spliced directly
* 2)Add Java util. Convert date to string
*
*
* */
String sql =
"insert into person values (null,'"+per.getName()+"','"+per.getPass()+"',"+per.getAge()+","
+per.getSalary()+",'"+new SimpleDateFormat("yyyy-MM-dd").format(per.getBirth()) +"')";
//Get connection
con = Factory.getCon();
//Take the actuator
st = con.createStatement();
//The executor calls the method according to the sql statement
return st.executeUpdate(sql)==1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
} finally {
//Release resources regardless of whether the operation is normal or not
Factory.close(null,st,con);
}
}
}
Potential safety hazards
- When an identical conditional expression is passed in, the statement is equivalent to looking up the whole table data
- SQL can be injected
3. Interface implementation class [PreparedStatement]
- Create a class PersonDaoImpl2 to implement the PersonDaoIf2 interface
- PreparedStatement is a subclass of Statement
- When using the PreparedStatement actuator, use? Carry out station first, and then fill in
- When filling in date types, you need to use * * new Java sql. Date(xxx.getDate(). getTime())**
package com.etoak.dao;
import com.etoak.factory.Factory;
import com.etoak.po.Person;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class PersonDaoImpl2 implements PersonDaoIf2 {
Connection con;
Statement st;
/*
* Use the executor PreparedStatement, which is a subclass of the Statement
* */
PreparedStatement pst;
ResultSet rs;
@Override
public boolean addPerson(Person per) {
try {
/*
* When using Statement, we need to splice sql statements, but it is very inconvenient,
* And the security is low, which is prone to sql injection security risks
* If you use PreparedStatement, you can use? To assemble sql statements as placeholders, we are no longer required
* Complex sql splicing
*
* Pay attention to use? Placeholders must be filled in because there are placeholders in sql statements when they can no longer be executed?
* */
String sql = "insert into person values (null,?,?,?,?,?)";
con = Factory.getCon();
//While obtaining the actuator, load the with? Placeholder sql statement
pst = con.prepareStatement(sql);
//Placeholders must be filled in because they exist
/*
* pst.set Data type (index, value to be filled)
* index:From left to right? In order, note that it starts from 1
* */
pst.setString(1,per.getName());
pst.setString(2,per.getPass());
pst.setInt(3,per.getAge());
pst.setDouble(4,per.getSalary());
/*
* java.util.Date And Java sql. The difference between date
*
* 1)java.util.Date Is SQL Parent class of date
* 2)util.Date Generally, you can set year, month, day, hour, minute and second
* And SQL Date can only be accurate to mm / DD / yy
* 3)util.Date Cannot be directly spliced with string into sql statement
* And SQL Date is OK
* 4)Conversion through getTime()
* */
pst.setDate(5,new java.sql.Date(per.getBirth().getTime()));
return pst.executeUpdate()==1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
} finally {
//Long press ctrl or command to trace the source
Factory.close(null,pst,con);
}
}
@Override
public boolean delPersonById(Integer id) {
try {
String sql = "delete from person where id = ?";
con = Factory.getCon();
//Load sql statement while getting the executor
pst = con.prepareStatement(sql);
pst.setInt(1,id);
return pst.executeUpdate()==1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
} finally {
Factory.close(null,pst,con);
}
}
@Override
public boolean delPersonByName(String name) {
try {
String sql = "delete from person where name = ?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,name);
return pst.executeUpdate()>=1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
} finally {
Factory.close(null,pst,con);
}
}
@Override
public List<Person> queryAll() {
try {
String sql = "select * from person";
con = Factory.getCon();
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
List<Person> list = new ArrayList<>();
while(rs.next()){
list.add(new Person(rs.getInt(1),
rs.getString(2),rs.getString(3),rs.getInt(4)
,rs.getDouble(5),rs.getDate(6)));
}
return list;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
Factory.close(rs,pst,con);
}
}
@Override
public boolean queryName(String name) {
try {
String sql = "select * from person where name = ?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,name);
return pst.executeQuery().next();
} catch (Exception ex) {
ex.printStackTrace();
return false;
} finally {
Factory.close(rs,pst,con);
}
}
@Override
public Person queryPerson(String name, String pass) {
try {
String sql = "select * from person where name = ? and pass = ?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,name);
pst.setString(2,pass);
rs = pst.executeQuery();
if(rs.next()){
return new Person(rs.getInt(1),
rs.getString(2),rs.getString(3)
,rs.getInt(4),rs.getDouble(5),rs.getDate(6));
}
return null;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
Factory.close(rs,pst,con);
}
}
@Override
public Integer queryCount() {
try {
String sql = "select count(*) from person";
con = Factory.getCon();
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
/*
* rs here returns a table with two rows and one column
* ------------
* | |
* | count |
* ------------
* | |
* | Number of records|
* ------------
*
* The pointer points to the header and moves it down one line
*/
rs.next();
return rs.getInt(1);
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
Factory.close(rs,pst,con);
}
}
@Override
public List<Person> queryPage(Integer index, Integer max) {
try {
/*
* select Field from table limit x,y;
* x:Indicates the starting index value
* y:Indicates that several records are displayed
* */
String sql = "select * from person limit ?,?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setInt(1,index);
pst.setInt(2,max);
rs = pst.executeQuery();
List<Person> list = new ArrayList<>();
while(rs.next()){
list.add(new Person(rs.getInt(1),
rs.getString(2),
rs.getString(3),
rs.getInt(4),
rs.getDouble(5),
rs.getDate(6)));
}
return list;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
Factory.close(rs,pst,con);
}
}
//Fuzzy query by name and pagination
@Override
public List<Person> queryNameLikePage(String args, Integer index, Integer max) {
try {
String sql = "select * from person where name like ? limit ?,?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
//Stitching left blur and right blur
pst.setString(1,"%"+args+"%");
pst.setInt(2,index);
pst.setInt(3,max);
rs = pst.executeQuery();
List<Person> list = new ArrayList<>();
while(rs.next()){
list.add(new Person(rs.getInt("id")
,rs.getString("name")
,rs.getString("pass")
,rs.getInt("age")
,rs.getDouble("salary")
,rs.getDate("birth")));
}
return list;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
Factory.close(rs,pst,con);
}
}
@Override
public List<Person> queryLikePage(Person per, Integer index, Integer max) {
return null;
}
/*
* What is passed in here is an array of string type, which encapsulates all IDS ins id e
* Delete in batches according to these IDs. Note that the authenticity of IDS does not need to be cons id ered
*
* */
@Override
public boolean multiDel(String[] args) {
try {
String sql = "delete from person where id in (";
String sum = "";
for(int i = 0;i<args.length;i++){
sum = args[i]+","+sum;
}
sum = sum.substring(0,sum.length()-1);
sql += sum+")";
con = Factory.getCon();
st = con.createStatement();
/*
* pst = con.preparedStatement(sql);
* pst.executeUpdate()>=1
* */
return st.executeUpdate(sql)>=1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
} finally {
Factory.close(null,pst,con);
}
}
@Override
public boolean updatePerson(Person per) {
try {
String sql =
"update person set name = ?,pass = ?,age = ?,salary = ?,birth = ? where id = ?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,per.getName());
pst.setString(2,per.getPass());
pst.setInt(3,per.getAge());
pst.setDouble(4,per.getSalary());
pst.setDate(5,new java.sql.Date(per.getBirth().getTime()));
pst.setInt(6,per.getId());
return pst.executeUpdate()==1;
} catch (Exception ex) {
ex.printStackTrace();
return false;
} finally {
Factory.close(null,pst,con);
}
}
}
1. Notes for writing routine
- Statement actuator
//In the implementation method, the first step is to obtain the connection
conn = Factory.getConn();
//The second step is to obtain the actuator
st = conn.createStatement();
//Step three. Write the SQL statement. Here, you can write the SQL statement completely and then add double quotes according to whether it is a character type
String sql = "select * from person";
//If no result set is returned, the execution result is returned,
//This method returns the number of affected rows, and the return value is int
return st.executeUpdate(sql) > 0;
//If the result set is returned, step 4: obtain the result set
rs = st.executeQuery(sql);
//The fifth step is to create a new empty collection. Given the generic type, ensure that only objects of this type can be stored
List<Person> list = new ArrayList<>();
//Step 6: add the results in the result set to the empty set in turn
while(rs.next()){
list.add(new Person(rs.getInt(1),...));
}
//Step 7: close the connection
Factory.closeConn(rs,st,conn);
- PreparedStatement actuator
//1 get connection conn = Factory.getConn(); //2. When writing sql statements, use placeholders first? To fill String sql = "select * from person where name like ? limit ?,?"; //3. Get the executor d and pass in the sql statement at the same time pst = conn.preparedStatement(sql); //4. Pass in the corresponding value to the placeholder. The first parameter represents the question marks in the sql statement, and the second parameter represents the passed in value //4.1 splicing strings in fuzzy query pst.setString(1,"%" + per.getName() + "%"); //4.2 pass in other parameters pst.setInt(2,per.getAge()); //4.3 Date type, because util Date cannot be spliced with sql statements, so it should be converted into sql Date pst.setDate(3,new java.sql.Date(per.getBirth().getTime())); //5. Implementation pst.executeQuery();
2. Date precautions
- When splicing Date with sql statement
- java.util.Date and string cannot be directly spliced into sql statements through +
There are two ways
1) add Java util. Convert date to Java sql. Date, which can be spliced directly
2) add Java util. Convert date to string
String sql = "insert into person values('"+ new SimpleDateFormat("yyyy-MM-dd").format(Date) +"')"
- When creating an object to convert String to Date type
Person per = new Person(new SimpleDateFormat("yyyy-MM-dd").parse("1999-01-01"));
- PreparedStatement passed in Date
pst = conn.preparedStatement(sql); pst.setDate(1,new java.sql.Date(per.getBirth().getTime()));
4. Test
package com.etoak.test;
import com.etoak.factory.Factory;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test {
public static void main(String[] args) {
try {
//Call the factory to get the connection and link the database
Connection con = Factory.getCon();
/*
* If can not found class com mysql. jdbc. The driver has no guide bag
* Or drive writing errors
* */
if(con==null){
System.out.println("Link failed!!");
return;
}
System.out.println("Link succeeded!!");
/*
* After obtaining the connection, you can obtain the executor, which is used to execute SQL statements
* There are mainly the following two kinds of actuators
* Statement
* PreparedStatement
* */
Statement st = con.createStatement();
String dml1 = "insert into person values (null,'Test 1','12345',30,2000.00,'2000-03-03')";
String dql1 = "select * from person";
/*
* After obtaining the executor, you can call the following methods according to different types of SQL statements
*
* 1) boolean execute()
* Returns true if the DQL statement is executed
* If the DML statement is executed, false is returned, but it can still be executed
* This method is less used
*
* 2) int executeUpdate()
* If the DQL statement is executed, an error will be reported immediately
* If the DML statement is executed, the number of records changed is returned
*
* 3) ResultSet executeQuery()
* If the DML statement is executed, an error will be reported immediately
* Execute DQL statement to return a result set ResultSet. By parsing the result set, you can get the value encapsulated in it
*
*
* 4) int[] executeBatch()
* Execute batch processing and execute multiple SQL statements at a time
* */
//int count = st.executeUpdate(dml1);
//System.out.println(count);
ResultSet rs = st.executeQuery(sql1);
/*
* Before parsing, first judge whether there is valid data. If there is no valid data, no parsing is required
Do not judge whether there is valid data according to whether the ResultSet is null, because the ResultSet is similar to a table
Header exists, never null
There is a boolean next() result set, which is similar to a table. The default pointer points to the header of the first row. When we call
.next()If the pointer can move down, return true. If there is no valid data, the pointer cannot move down one line and return false
So we can according to Whether the next() method returns true to judge whether there is valid data
* */
while(rs.next()){
/*
* Fetch data:
* get Data type (number of columns) or get data type (column name)
* */
/*System.out.println("ID:"+rs.getInt(1)+"\t Name: "+ rs.getString(2)
+"\t Password: "+ rs.getString(3)+"\t age: "+ rs.getInt(4)+"\t salary:“
+rs.getDouble(5)+"\t Birthday: "+ rs.getDate(6));*/
System.out.println("ID:"+rs.getInt("id")+"\t full name:"
+rs.getString("name")+"\t password:"+rs.getString("pass")
+"\t Age:"+rs.getInt("age")+"\t salary:"+rs.getDouble("salary")
+"\t birthday:"+rs.getDate("birth"));
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
1. Precautions
- Create actuator
- There are usually two kinds of statements, Preparedstatement
| Return type | Actuator method | explain |
|---|---|---|
| boolean | execute() | Returns true if the DML statement is executed If the DML statement is executed, false is returned, but it can still be executed This method is less used |
| int | executeUpdate() | If the DQL statement is executed, an error will be reported immediately If the DML statement is executed, the number of records affected is returned |
| ResultSet | executeQuery() | If the DML statement is executed, an error will be reported immediately Execute DQL statement to return a result set ResultSet. By parsing the result set, you can get the value encapsulated in it |
| int[] | executeBatch() | Execute batch processing and execute multiple SQL statements at a time |
| language | Full name | Include content |
|---|---|---|
| SQL | Structured Query Language | DDL,DML,DQL,DCL,TCL |
| DDL | Data definition language | Create, alter, drop, truncate |
| DML | Data Manipulation Language | insert, update, delete |
| DQL | Data Query Language | Select (query) |
| DCL | Data Control Language | Grant, revoke |
| TCL | Translation Control Language | Commit, rollback and savepoint will only affect DML operations. |
- Get valid data
Before parsing, first judge whether there is valid data. If there is no valid data, no parsing is required
Do not judge whether there is valid data according to whether the ResultSet is null, because the ResultSet is similar to a table, there is a header, and it will never be null. There is a boolean next() result set, which is similar to a table,
The default pointer points to the first row header when we call rs When next(), if the pointer can move down, it returns true. If there is no valid data, the pointer cannot move down one line and returns false. Therefore, we can Whether the next () method returns true to judge whether there is valid data
rs = st.executeQuery(sql);
while(rs.next()){
//TODO
}
- Fetch data
- It can be retrieved through get data type (number of columns) or get data type (column name)
Integer id = rs.getInt(1);
String name = rs,getString("name");
2. executeBatch() method of actuator
- First, close the automatic commit transaction of mysql, conn.setAutoCommit(false); Will not be submitted when an error occurs
- Use the addBatch(sql) method to put the SQL statements to be executed into the executor one by one
- Execute method and submit
- Open automatic submission of MySQL
public class TestBatch {
public static void main(String[] args) {
try {
//Get connection
Connection con = Factory.getCon();
//Turn off mysql auto commit transaction
con.setAutoCommit(false);
//Get actuator
Statement st = con.createStatement();
//Set the sql to batch
//Note that batch processing cannot execute dql statements
String dml1 = "insert into person values (null,'elena','12345',30,5000.00,'1990-03-01')";
String dml2 = "insert into person values (null,'damon','33345',20,6000.00,'1993-03-01')";
String dml3 = "insert into person values (null,'stefan','53345',17,4000.00,'1992-03-01')";
String dml4 = "delete from person where id = 1";
//Add the sql statement to be executed to the cache
//Each sql statement here is similar to a bullet, and the clip is pressed here
st.addBatch(dml1);
st.addBatch(dml2);
st.addBatch(dml3);
st.addBatch(dml4);
//Perform batch operation
/*
* The returned is an array of type int, which encapsulates the number of changed records
*
* */
int[] count = st.executeBatch();
//Commit transaction
con.commit();
//Resume auto commit
con.setAutoCommit(true);
for(int i:count){
System.out.println("The number of records changed is~~~~>"+i);
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
five 🐴 Difference between Statement and PreparedStatement
- Same point
Both are interfaces
public interface Statement implements Wrapper
public interface PreparedStatement implements Statement PreparedStatement is a subclass of Statement
- difference
① : Statem can only execute static statements; PreparedStatement can also include sql statements with IN parameters. The so-called IN parameters mean that sql statements can change fields and other data
Is not a fixed statement.
② PreparedStatement has a powerful cache, which can be used for sql statements
Precompiled. When executing the same sql statement, the PreparedStatement
The statement is loaded into the cache and compiled only once. When the statement is executed the second time
There is no need to compile again, that is, the same sql statement executes multiple sql statements
Only compile once, and PreparedStatement only modifies the changed data
Instead of compiling;
Statement must be recompiled whenever the statement changes
③ : PreparedStatement supports sql statements? Placeholder to
The modification of field parameters in sql statement reduces the difficulty of development and fundamentally eliminates the problem
sql injection security risks
④ If the sql statement does not need to be executed multiple times, or? Too much, the efficiency may be low
Statement low