Let's understand the application of JDBC in java through several JDBC cases
Before starting to study the case, we should have a general understanding of the use process and specification of JDBC
1. Basic application
JDBC usage rules:
JDBC call process:
1. Register the Driver interface implementation class provided by the MySQL server manufacturer with the JVM
2. Establish a [connection channel] between Java project and MySQL server through DriverManager in JDBC specification
3. Establish a vehicle [PrepareStatement] through the Connection interface implementation class provided by MySQL server manufacturer
4. Push the SQL command from the Java project to the MySQL server for execution through the vehicle [PrepareStatement] and bring back the execution result
5. Destroy all resource objects in the process of this transaction
Introduction to interfaces under JDBC specification:
1. Location: exists in JDK_ 1.8. Java.jar SQL package
2. Classification: (1) java sql. Drivermanager class: this class exists in JDK_1.8. Be responsible for registering the Driver interface provided by the database manufacturer and establishing a connection channel between java and MySQL
(2)java.sql.Connection interface:
Manage the connection channel between java project and database server
(3)java.sql.PreparedStatement interface:
Be responsible for the management of vehicles for round-trip transportation on the connecting channel
(4)java.sql.ResultSet interface:
Manage the temporary tables returned by the database server
After understanding these, we can strengthen our understanding of JDBC usage process through the following cases
Case 1: push the add data command to MySQL server through JDBC
public class Push add command {
public static void main(String[] args) throws SQLException {
Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);//This sentence can be omitted
//1. Register the driver interface implementation class in the jar package provided by MySQL server to the JVM
String url = "jdbc:mysql://localhost:3306/name";
//ucr format: "jdbc:mysql / / IP address of the computer where the server is located: service port number / database"
Connection connection = DriverManager.getConnection(url, "root", "233");//root and 233 here refer to the account and password of the database
//2. Create a channel between java and MySQL through DriverManager and hand it over to connection management
PreparedStatement ps = connection.prepareStatement("");//3. Build a vehicle on the passage for transportation orders and submit it to ps management
//Note that the SQL statement should be put here, but we only define it, so we can make it empty, which is equivalent to an "empty car"
int result = ps.executeUpdate("insert into dept (deptno,dname,loc) values(88,'bookstore','CN')");
//4. Push the SQL command to the MySQL server through the vehicle ps for execution and bring back the processing results
//Returns the number of added data. If the added data already exists, an exception will be caused
//5. Destruction of relevant resources
if(ps!=null)//Before destruction, confirm whether the ps still exists, so as to prevent the ps from being destroyed due to program abnormalities
{
ps.close();
}
if(connection!=null){
connection.close();
}
System.out.println("In this transaction, a total of"+result+"Data");
}
}
Case 2: push the update data command to MySQL server through JDBC
public class Push update command {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");//The second way is to register the Driver implementation class with the JVM
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/name", "root", "233");
PreparedStatement ps = connection.prepareStatement("");
int result = ps.executeUpdate("update dept set loc = 'Guizhou' where deptno <>90 ");
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
System.out.println("This transaction has been updated"+result+"Data bar");
}
}
Case 3: push query command to MySQL server through JDBC
public class Push query command {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String sql ="select dname from dept where dname like '_c%'";//Writing query statements
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/name", "root", "cjysql233");
PreparedStatement ps = connection.prepareStatement("");
ResultSet rs = ps.executeQuery(sql);
//Use while to traverse the ResultSet to get the query result
while (rs.next()){
String dname = rs.getString("dname");
System.out.println(dname);
}
//Note that the ResultSet is built on the PrepareStatement, so you should finish the operation on the ResultSet before closing the Statement
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
}
}
Through the above three cases, we can probably understand the use process of JDBC in java, but we will also find that these three simple cases have many problems, the most prominent of which are
1.SQL statement writing is cumbersome and error prone
2. Every time executeupdate() is executed, Java - > MySQL - > java will be executed, resulting in a lot of waste of time and space
3. There is no transaction function applied to MySQL. In practical application, most operations should be handled by transactions
So how can we solve these problems
You can experience it through the next two cases
2. Transaction management and batch processing
Case 4: transaction management
/*
Rules:
In a requirement, as long as any SQL command cannot be executed, all SQL commands in the requirement shall be judged as execution failure
*/
public class transaction management {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/name","root","233");
//Push the "start transaction" command through the connection channel to start a transaction
connection.setAutoCommit(false);
//If the connection is in auto Commit mode, all its SQL statements will be run and committed as a single transaction. Otherwise, its SQL statement will act as a transaction group until the Commit method or rollback method is called. By default, new connections are in auto submit mode.
//When the value passed in setAutoCommit is true, the automatic submission mode will be enabled. At this time, the SQL statement will be managed by the MySQL server. When false is passed in, the automatic submission will be turned off and a transaction will be started. At this time, the SQL statement will be managed by the transaction
PreparedStatement ps = connection.prepareStatement("");
//Note: only when the storage engine of the table is innodb can there be backup and transaction rollback can be used. You can use the alter table name engine = innodb statement to set the storage engine of the table to innodb and view the storage engine of the current table through the show create table table name
try{//Through try catch.. To catch exceptions to ensure that when any SQL statement has problems, the transaction can be rolled back, the backup of all files in the operation will be overwritten with the table file, and the operation will be cancelled
ps.executeUpdate("delete from emp where deptno = 20");
ps.executeUpdate("delete from dept where deptno= 20");
//If you can get here, it proves that the pushed SQL statements can run normally. Therefore, you can submit data, close transactions, and notify the MySQL server to delete the backup files
connection.commit();//Push "commit" to MySQL server Transaction shutdown
}catch (SQLException sqlException){
connection.rollback();//Push "rollback" to MySQL server Roll back
}finally {
//Destroy
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();
}
}
}
}
Case 5: batch processing attempt
public class Batch attempt {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
//Precompiled SQL statement
String sql ="insert into dept (deptNo,dname,loc) values (?,?,?)";
//"?" Is a placeholder, and a question mark represents a value
//Precompiled SQL is equivalent to a mold. In subsequent development, you only need to fill the data into the placeholder to get a new SQL
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/name", "root", "cjysql233");
//When we build a vehicle, we need to register the precompiled SQL statement in the PrepareStatement
PreparedStatement ps = connection.prepareStatement(sql);
for(int i = 0;i<=50;i++){
//Generate new SQL commands by populating precompiled SQL statements with data
ps.setInt(1,i);// 1 refers to ps the first placeholder "?" in the loaded precompiled SQL statement, The following i is the data to fill in the placeholder
ps.setString(2,"dept"+i);
ps.setString(3,"CN");
//In order to realize that the SQL statements can be transported in batches instead of completing the round-trip tasks one at a time, we need to fill the written SQL statements into the "magazine" like bullets and wait for them to be launched together
ps.addBatch();//Add SQL statement to the clip
}
int[] ints = ps.executeBatch();//[one time] push all SQL statements to MySQL server, that is, "pull the trigger". Batch is actually a list
//executeBatch() returns an integer array. The value in the array is the number of lines affected by each statement in its "magazine". If - 2 is returned, it means that the command is processed successfully, but the number of lines affected is unknown. If - 3 is returned, it means that the statement cannot be executed, and the driver will skip it and continue to process downward
//If one of the commands in the batch update fails to execute correctly, this method raises a BatchUpdateException, and the JDBC driver may or may not continue to process the remaining commands. However, the driver's behavior is bound to a specific DBMS. It either always continues to process commands or never continues to process commands. If the driver continues processing, the method returns EXECUTE_FAILED(-3).
for (int i = 0; i < ints.length; i++) {
System.out.println("sentence"+i+"Successful execution,Carried out"+ints[i]+"Operations");
}
if(ps!=null){
ps.close();
}
if (connection != null) {
connection.close();
}
}
}
This will solve the problems that have arisen before
These cases mainly come from the courses of Lao Yang, the power node. Interested students can have a look Station B power node java web