Transfer case of JDBC control transaction

Transfer case of JDBC control transaction


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;
				username VARCHAR(32),
				balance DOUBLE

			INSERT INTO account VALUES(NULL,'zhangsan','123',1000);
			INSERT INTO account VALUES(NULL,'lisi','234',1000);

3. Writing

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


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 configuration file
    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
        //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("");
        String path = resource.getPath();


        //2. Load Files
        pro.load(new FileReader(path));

        //3. Get data, assign values
        user = pro.getProperty("user");
        password = pro.getProperty("password");
        driver = pro.getProperty("driver");

        //4. Registration Drivers
        } catch (IOException e) {
        } catch (ClassNotFoundException e) {

     Get Connections
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user, password);

    Release Resources
    public static void close(Statement stmt, Connection conn){
            try {
            } catch (SQLException e) {
        if (conn != null) {
            try {
            } catch (SQLException e) {

5. Write JDBCDemo1

1. The essence of knowledge

🥑 (1) Common methods in Statement interface

Method declarationFunctional 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

  1. Transaction: A business operation that contains multiple steps. If the business operation is managed by a transaction, the steps either succeed or fail simultaneously.
  2. Operation:
    Open Transaction
    Submit Transaction
    Rollback transaction
  3. 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

            //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
            // 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;
            //Submit Transaction
        } catch (Exception e) {
            try {
                if (conn != null) {
            } catch (SQLException e1) {
        } finally {
            JDBCUtils.close(pstmt1, conn);
            JDBCUtils.close(pstmt2, null);

🥑 Result:

Keywords: Database

Added by veenasv on Tue, 26 Oct 2021 20:04:06 +0300