07-day5 dark horse javaweb notes JDBC

07-day5 dark horse javaweb notes JDBC

Basic concepts of JDBC

Concept: Java database connectivity, Java database connection, Java language operation database

JDBC essence: it is actually a set of rules defined by the official (sun company) to operate all relational databases, that is, interfaces. Each database manufacturer implements this interface and provides database driver jar package. We can use this set of interfaces (JDBC) to program, and the real executed code is to drive the implementation classes in the jar package.

quick get start

Steps:

  1. Import the driver jar package mysql-connector-java-5.1.37-bin.jar
  2. Copy mysql-connector-java-5.1.37-bin.jar to the libs directory of the project
  3. Right click – > Add as library

  1. Register driver
  2. Get database Connection object Connection
  3. Define sql
  4. Gets the Statement object that executes the sql Statement
  5. Execute sql and accept the returned results
  6. Processing results
  7. Release resources
Class.forName("com.mysql.jdbc.Driver");
//3. Get database connection object
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//4. Define sql statements
String sql = "update account set balance = 500 where id = 1";
//5. Get the object Statement executing sql
Statement stmt = conn.createStatement();
//6. Execute sql
int count = stmt.executeUpdate(sql);
//7. Treatment results
System.out.println(count);
//8. Release resources
stmt.close();
conn.close();

Explain each object in detail:

1.DriverManager: (drive management object)

Function:

Register driver: tell the program which database driver jar to use

static void registerDriver(Driver driver) :Register with the given driver DriverManager .  
Write code using:  Class.forName("com.mysql.jdbc.Driver");
By viewing the source code, it is found that: com.mysql.jdbc.Driver Static code block in class
static {
		try {
				java.sql.DriverManager.registerDriver(new Driver());
				} catch (SQLException E) {
				throw new RuntimeException("Can't register driver!");
				}
}

Note: the driver jar package after mysql5 can omit the steps of registering the driver.

Get database connection:

Method: static Connection getConnection(String url, String user, String password)

Parameters:

url: Specify the path of the connection
					* Syntax: jdbc:mysql://ip address (domain name): port number / database name
					* example: jdbc:mysql://localhost:3306/db3
					* Details: if the connection is local mysql Server, and mysql The default service port is 3306, then url It can be abbreviated as: jdbc:mysql:///Database name
					* user: user name
					* password: password 
2.Connection: (database connection object)
Function:

1. Get the object executing sql

Statement createStatement()
PreparedStatement prepareStatement(String sql)  

2. Management services:

  • Start transaction: setAutoCommit(boolean autoCommit): call this method to set the parameter to false, that is, start the transaction
  • Submit: commit()
  • Rollback transaction: rollback()
3.Statement: (object executing sql) static sql

boolean execute(String sql): arbitrary sql can be executed

int executeUpdate(String sql): execute DML (insert, update, delete) statements and DDL(create, alter, drop) statements

Return value: the number of affected rows. You can judge whether the DML statement is executed successfully by the number of affected rows. If the return value > 0, the execution is successful; otherwise, it fails.

ResultSet executeQuery(String sql): execute DQL (select) statements

  1. Add a record to the account table
  2. account table modification record
  3. Delete a record from the account table
Statement stmt = null;
Connection conn = null;
try {
		            //1. Register driver
		            Class.forName("com.mysql.jdbc.Driver");
		            //2. Define sql
		            String sql = "insert into account values(null,'Wang Wu',3000)";
		            //3. Get the Connection object
		            conn = DriverManager.getConnection("jdbc:mysql:///db3", "root", "root");
		            //4. Get the object Statement executing sql
		            stmt = conn.createStatement();
		            //5. Execute sql
		            int count = stmt.executeUpdate(sql);//Number of rows affected
		            //6. Treatment results
		            System.out.println(count);
		            if(count > 0){
		                System.out.println("Successfully added!");
		            }else{
		                System.out.println("Failed to add!");
}
		
		        } catch (ClassNotFoundException e) {
		            e.printStackTrace();
		        } catch (SQLException e) {
		            e.printStackTrace();
		        }finally {
		            //stmt.close();
		            //7. Release resources
		            //Avoid null pointer exceptions
		            if(stmt != null){
		                try {
		                    stmt.close();
		                } catch (SQLException e) {
		                    e.printStackTrace();
		                }
		            }
		
		            if(conn != null){
		                try {
		                    conn.close();
		                } catch (SQLException e) {
		                    e.printStackTrace();
		                }
		            }
		        }
4.ResultSet: (result set object, encapsulating query results)
  • boolean next(): move the cursor down one line to determine whether the current line is the end of the last line (whether there is data). If yes, it returns false. If not, it returns true
  • Getxxx (parameter): get data
    • Xxx: represents the data type, such as int getint(), string getstring()
    • Parameters:
      • int: represents the number of the column, starting from 1, such as getString(1)
      • String: represents the name of the column. For example: getDouble("balance")

be careful:

Use steps:

  1. Move cursor down one line
  2. Determine whether there is data
  3. get data
//Loop to determine whether the cursor is at the end of the last line.
	            while(rs.next()){
	                //get data
	                //6.2 data acquisition
	                int id = rs.getInt(1);
	                String name = rs.getString("name");
	                double balance = rs.getDouble(3);
	
	                System.out.println(id + "---" + name + "---" + balance);
	            }
package JDBCpractice.test;

import java.sql.*;

public class JDBCDemo1 {
    public static void main(String[] args) {

        Connection conn = null;
        Statement statement = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql:///tmp", "root", "root");
            statement = conn.createStatement();
            String sql = "select ename from emp";
            ResultSet resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString(1));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            if(conn != null){
                try {
                    conn.close();
                }catch (SQLException e){
                    System.out.println(e);
                }
            }

            if(statement != null){
                try {
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }





    }
}

5.PreparedStatement: object for executing sql (dynamic sql, preprocessing)

1.SQL injection problem: when splicing sql, some special keywords of sql participate in string splicing, which will cause security problems

1. Enter the user name and password: a' or 'a' = 'a
2. sql: select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a' 
  1. Solve the sql injection problem: use the PreparedStatement object to solve it
  2. Precompiled SQL: use? As placeholder for parameter
  3. Steps:
    1. Import the driver jar package mysql-connector-java-5.1.37-bin.jar
    2. Register driver
    3. Get database Connection object Connection
    4. Define sql
      1. Note: sql parameters use "? As placeholders. For example: select * from user where username =? And password =?;
    5. Gets the object PreparedStatement Connection.prepareStatement(String sql) that executes the sql statement
    6. Assign value to?:
      1. Method: setXXX (parameter 1, parameter 2)
        1. Parameter 1: the position number of "? Starts from 1
        2. Parameter 2:? Value
    7. Execute sql and accept the returned results without passing sql statements
    8. Processing results
    9. Release resources
  4. Note: PreparedStatement will be used later to complete all operations of adding, deleting, modifying and querying
    1. Can prevent SQL injection
    2. More efficient
package JDBCpractice.test;

import java.sql.*;

public class JDBCDemo1 {
    public static void main(String[] args) {

        Connection conn = null;
        Statement statement = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql:///tmp", "root", "root");
            String sql = "select ename from emp where id = ?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1,1001);
            ResultSet resultSet = preparedStatement.executeQuery();


            while(resultSet.next()){
                System.out.println(resultSet.getString(1));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            if(conn != null){
                try {
                    conn.close();
                }catch (SQLException e){
                    System.out.println(e);
                }
            }

            if(statement != null){
                try {
                    statement.close();
                }catch (SQLException e){
                    e.printStackTrace();
                }
            }
        }
    }
}

Extract JDBC tool class: JDBC utils

Objective: to simplify writing

analysis:

  1. Registered drivers are also extracted

  2. Extract a method to get the connection object

  3. Requirements: do not want to pass parameters (trouble), but also ensure the universality of tool classes.

  4. Solution: configuration document

  - ```
    url=jdbc:mysql:///tmp
    user=root
    password=root
    driver=com.mysql.jdbc.Driver
    ```
  1. Extract a method to release resources
package JDBCpractice.util;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;


    /**
     * The file can be read only once to get these values. Use static code blocks
     */
    static{
        //Read the resource file and get the value.

        try {
            //1. Create the Properties collection class.
            Properties pro = new Properties();

            //How to get the file under src path -- > classloader classloader
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            InputStream is = classLoader.getResourceAsStream("jdbc.properties");
            pro.load(is);

            //3. Obtain data and assign values
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            //4. Register driver
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }



    /**
     * Get connection
     * @return Connection object
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }


    /**
     * Release resources
     * @param stmt
     * @param conn
     */
    public static void close(Statement stmt,Connection conn){
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    /**
     * Release resource method overload
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn){

        if (rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    public static void close(ResultSet rs,  PreparedStatement preparedStatement, Connection conn){

        if (rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }


}

practice:

Requirements:

1. Enter the user name and password through the keyboard
 2. Judge whether the user logs in successfully
    * select * from user where username = "" and password = "";
    * If this sql If there are query results, it will succeed; otherwise, it will fail

Create database table user

CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32),
PASSWORD VARCHAR(32)
);

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

Code implementation:

package JDBCpractice.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class UserLoginTest {

    public static void main(String[] args) {
        //1. Keyboard entry, accept user name and password
        Scanner sc = new Scanner(System.in);

        System.out.println("Please enter the account number:");
        String user = sc.next();

        System.out.println("Please input a password");
        String password = sc.next();

        //2. Call method
        boolean b = new UserLoginTest().userLogin(user, password);

        if(b){
            System.out.println("Login successful");
        }else {
            System.out.println("Login failed");
        }
    }

    public boolean userLogin(String user,String password){
        //Judge whether the user password is empty
        if (user == null || password == null){
            return false;
        }

        //Connect to the database to determine whether the login is successful

        Connection conn = null;
        PreparedStatement preparedStatement =  null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            String sql = "select * from user where username = ? and password = ?";
            preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1,user);
            preparedStatement.setString(2,password);
            rs= preparedStatement.executeQuery();

            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JDBCUtils.close(rs,preparedStatement,conn);
        }
        return false;
    }

}

JDBC control transaction:

Transaction: a business operation that contains multiple steps. If the business operation is managed by a transaction, the multiple steps will either succeed or fail at the same time.

Operation:

  1. Open transaction
  2. Commit transaction
  3. Rollback transaction

Use the Connection object to manage transactions

  • Start transaction: setAutoCommit(boolean autoCommit): call this method to set the parameter to false, that is, start the transaction
    • Start the transaction before executing sql
  • Commit transaction: commit()
    • When all sql has completed the commit transaction
  • Rollback transaction: rollback()
    • Rollback transaction in catch

code:

public class JDBCDemo10 {

	    public static void main(String[] args) {
	        Connection conn = null;
	        PreparedStatement pstmt1 = null;
	        PreparedStatement pstmt2 = null;
	
	        try {
	            //1. Get connection
	            conn = JDBCUtils.getConnection();
	            //Open transaction
	            conn.setAutoCommit(false);
	
	            //2. Define sql
	            //2.1 sheet 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. Set parameters
	            pstmt1.setDouble(1,500);
	            pstmt1.setInt(2,1);
	
	            pstmt2.setDouble(1,500);
	            pstmt2.setInt(2,2);
	            //5. Execute sql
	            pstmt1.executeUpdate();
	            // Manual manufacturing exception
	            int i = 3/0;
	
	            pstmt2.executeUpdate();
	            //Commit transaction
	            conn.commit();
	        } catch (Exception e) {
	            //Transaction rollback
	            try {
	                if(conn != null) {
	                    conn.rollback();
	                }
	            } catch (SQLException e1) {
	                e1.printStackTrace();
	            }
	            e.printStackTrace();
	        }finally {
	            JDBCUtils.close(pstmt1,conn);
	            JDBCUtils.close(pstmt2,null);
	        }
	         }
	         
	       	}

Tags: Java Database MySQL

Posted on Fri, 10 Sep 2021 14:54:59 -0400 by fareedreg