Transfer case of JDBC control transaction
Preface
This exercise is from a black horse programmer class case
1. Title requirements
- Zhangngsan and lisi each have 1,000 yuan. Please transfer 500 yuan from zhangsan to lisi
- And try if you add an error code, will the transfer change? (The answer appears in the code as a comment)
🥑 Quick Start: - Steps:
1. Import the driver jar package mysql-connector-java-5.1.37-bin.jar
1. Copy mysql-connector-java-5.1.37-bin.jar to the libs directory of the project
2. Right-click ->Add As Library
2. Registration Drivers
3. Get the database connection object Connection
4. Define sql
5. Get the object PreparedStatement that executes the sql statement
6. Execute sql and accept the returned results
7. Processing results
8. Release resources
2. Create data table account
create database db1; user db1; CREATE TABLE account( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(32), PASSWORD VARCHAR(32), balance DOUBLE ); INSERT INTO account VALUES(NULL,'zhangsan','123',1000); INSERT INTO account VALUES(NULL,'lisi','234',1000);
3. Writing jdbc.properties
1. The essence of knowledge
🥑 About url=jdbc:mysql://localhost:3366/jdb1
Syntax: jdbc:mysql://ip Address (domain name): port number/database name
- jdbc:mysql://refers to how JDBC is connected
- localhost is your local address
- Port number for 3306 SQL database (default)
- db1 is the address of the database you want to connect to
2. Code
url=jdbc:mysql://localhost:3366/db1 user=root password=111 driver=com.mysql.jdbc.Driver
4. Extract JDBC Tool Class: JDBCUtils
1. The essence of knowledge
🥑 (1) static action
Static: Static modifiers, variables and methods that are modified by static are similar to global variables and methods, and can be called when an object is not created or, of course, after it is created. Common tool methods that can be used in tool classes are moderate. For example, most of the methods in Math classes are static methods, and they play a role in tool methods.
🥑 (2) About JDBCUtils
Purpose: To simplify writing
- Analysis:
1. Registration drivers also extract
2. Extract a method to get connected objects
*Requirement: Do not want to pass parameters (cumbersome), but also ensure the universality of the tool class.
*Resolution: Profile
Write jdbc.properties configuration file
url=
user=
password=
3. Extract a method to release resources
2. Code
public class JDBCUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
/*
Read the file once to get these values, using a static block of code
*/
static{
//Read the resource file to get the value
try {
//1. Create Properties Collection Class
Properties pro=new Properties();
//Get the file style under src path--->ClassLoader class loader
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL resource = classLoader.getResource("jdbc.properties");
String path = resource.getPath();
System.out.println(path);
//2. Load Files
pro.load(new FileReader(path));
//3. Get data, assign values
url=pro.getProperty("url");
user = pro.getProperty("user");
password = pro.getProperty("password");
driver = pro.getProperty("driver");
//4. Registration Drivers
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/*
Get Connections
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user, password);
}
/*
Release Resources
*/
public static void close(Statement stmt, Connection conn){
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5. Write JDBCDemo1
1. The essence of knowledge
🥑 (1) Common methods in Statement interface
| Method declaration | Functional Description |
|---|---|
| int executeUpdate() | Execute a SQL statement that must be either a DML statement or a SQL statement with no return content, such as a DDL statement |
| ResultSet executeQuery() | Used to execute select statements in SQL |
🥑 (2) JDBC controls transactions that matter
- Transaction: A business operation that contains multiple steps. If the business operation is managed by a transaction, the steps either succeed or fail simultaneously.
- Operation:
Open Transaction
Submit Transaction
Rollback transaction - Use Connection Objects to Manage Transactions
- Open transaction: setAutoCommit(boolean autoCommit): Call this method to set the parameter to false, that is, open the transaction
- Open transaction before executing sql
- Commit transaction: commit()
When all SQLs have finished committing transactions - Rollback transaction: rollback()
Rollback Transaction in catch
2. Code
public class JDBCDemo1 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try {
//1. Get connected
conn = JDBCUtils.getConnection();
//Open Transaction
conn.setAutoCommit(false);
//2. Define sql
//2.1 sheets 3-500
String sql1 = "update account set balance = balance - ? where id = ?";
//2.2 Li Si+500
String sql2 = "update account set balance = balance + ? where id = ?";
//3. Get the execution sql object
pstmt1 = conn.prepareStatement(sql1);
pstmt2 = conn.prepareStatement(sql2);
//4. Setting parameters
pstmt1.setDouble(1, 500);
pstmt1.setInt(2, 1);
pstmt2.setDouble(1, 500);
pstmt2.setInt(2, 2);
//5. Execute sql
pstmt1.executeUpdate();
// Manually create exceptions
//The whole code must be wrong, and the transaction rolls back to its original state, so the amount of money for zhangsan and lisi hasn't changed
int i = 3/0;
pstmt2.executeUpdate();
//Submit Transaction
conn.commit();
} catch (Exception e) {
//rollback
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtils.close(pstmt1, conn);
JDBCUtils.close(pstmt2, null);
}
}
}
🥑 Result:
