JDBC Java datebase connectivity

JDBC Java datebase connectivity

  1. Concept: Java language operation database

    • JDBC essence: it is 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 classes implemented in the jar package
  2. Quick start:

    • Steps:

      1. Import the driver jar package mysql-connector-java-8.0.26.jar
        1. Copy mysql-connector-java-8.0.26.jar to the libs directory of the project
        2. Right click – > Add as library
      2. Register driver
      3. Get database Connection object Connection
      4. Define sql
      5. Gets the Statement object that executes the sql Statement
      6. Execute sql and accept the returned results
      7. Processing results
      8. Release resources
      //1. Import the driver jar package
              //2. Register driver
              Class.forName("com.mysql.cj.jdbc.Driver");
              //3. Get database connection object
              Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1", "root", "qin");
              //4. Define sql statements
              String sql="update emp set salary = 1 where dept_id=1";
              //5. Get the object Statement executing sql
              Statement stmt =con.createStatement();
              //6. Execute sql
              int c=stmt.executeUpdate(sql);
              //7. Implementation results
              System.out.println(c);
              //8. Release resources
              stmt.close();
              con.close();
      
  3. Explain each object in detail

    1. DriverManager object: drive management object

      • Function:

        1. 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.cj.jdbc.Driver"); ctrl+n: directly jump to the corresponding class

          By looking at the source code, it is found that there are static code blocks in the com.mysql.cj.jdbc.Driver class

          static {
              try {
                  DriverManager.registerDriver(new Driver());//Register driver
              } catch (SQLException var1) {
                  throw new RuntimeException("Can't register driver!");
              }
          }
          

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

        2. Get database connection:

          • Method: static Connection getConnection(Strng url, String user, String password);
          • Parameters:
            • url: Specifies the path of the connection
              • Syntax: jdbc:mysql://ip Address (domain name): port number / database name
              • Example: jdbc:mysql://localhost:3306/db1
              • Details: if the local mysql server is connected and the default port of mysql service is 3306, the url can be abbreviated as jdbc:mysql: / / / database name
            • User: user name
            • Password: password
    2. Connection object: database connection object

      • Function:
        1. Get the object executing mysql
          • Statement createStatement()
          • PreparedStatement prepareStatement(String sql)
        2. Management services:
          • Start transaction: void setAutoCommit(boolean autoCommit): call this method to set the parameter to false, that is, start the transaction
          • Commit transaction: commit()
          • Rollback transaction: rollback()
    3. Statement object: the object that executes sql

      1. Execute sql

        1. boolean execute(String sql): arbitrary sql can be executed
        2. int executeUpdate(String sql): generally, DML (insert, update, delete) statements and DDL (create, alter, drop) statements are executed (rarely used)
          • 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
        3. ResultSet executeQuery(String sql): execute DQL (select) statements
      2. practice:

        1. Add a record to the table
        2. Table modification record
        3. Table delete a record
        Statement stm=null;
                Connection con =null;
                try {
                    //1. Register driver
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    //2. Define sql
                    String sql="insert into emp values(7,'Wang Wu','male',9999,'2001-08-08',2)";
                    //3. Get the Connection object
                    con = DriverManager.getConnection("jdbc:mysql:///db1", "root", "qin");
                    //4. Get the object Statement executing sql
                    stm = con.createStatement();
                    //5. Execute sql
                    int account=stm.executeUpdate(sql);//Number of rows affected
                    //6. Treatment results
                    System.out.println(account);
                    if (account>0){
                        System.out.println("Successfully added!");
                    }else{
                        System.out.println("Failed to add!");
                    }
        
                } catch (ClassNotFoundException | SQLException e) {
                    e.printStackTrace();
                }finally {
                    //7. Release resources
                    //Avoid null pointer exceptions
                    if (stm != null){
                        try {
                            stm.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                    if (con != null){
                        try {
                            con.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
        
                }
        
    4. ResultSet object: result set object, which encapsulates query results

      • boolean next(): move the cursor down one line and return boolean. Judge whether the current line is the end of the last line (whether there is data). If there is no data, return false. Otherwise, return true if there is data

      • Getxxx (parameter): get data

        • Xxx: represents the data type, such as int getInt();
        • Parameters:
          1. int: represents the number of the column, starting from 1, such as getString(1)
          2. 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
          // Cyclic acquisition
          while(res.next()) {
              //2. Obtain data
              int id = res.getInt(1);
              String name = res.getString("name1");
              double salary = res.getDouble("salary");
              System.out.println(id + "---" + name + "---" + salary);
          }
          
          
      • practice:

        • Query the data of emp table, encapsulate it as an object, then load the collection and return.
          1. Class defining Emp
          2. Define method public list < EMP > findall() {}
          3. Implementation method select * from emp;
    5. PreparedStatement object: the object that executes sql

      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's password: a 'or' a '=' a '
        2. sql: select * from user where username = 'failjdd' and password = 'a' or 'a'='a';
      2. Solve the sql injection problem: use the PreparedStatement object to solve it
      3. Precompiled sql: parameter usage? As placeholder
      4. Steps:
        1. Import driver jar package
        2. Register driver
        3. Get the database connection object connection
        4. Define sql
          • 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?:
          • Method: setXXX (parameter 1 (question mark position number starts from 1), parameter 2 (question mark value));
        7. Execute sql and receive the returned results without passing sql statements
        8. Processing results
        9. Release resources
      5. Note: PreparedStatement will be used later to complete all operations of adding, deleting, modifying and querying
        1. Can prevent SQL injection
        2. efficient

example:

package cn.itcast.jdbc;

import cn.itcast.util.JDBCUtils;

import java.sql.*;
import java.util.Scanner;

/**
 * Requirements:
 * 1. Enter the user name and password through the keyboard
 * 2. Judge whether the user logs in successfully
 */
public class JBDCDemo9 {

    public static void main(String[] args) {
        //1. Enter the user name and password with the keyboard
        Scanner r = new Scanner(System.in);
        System.out.println("Please enter user name:");
        String username = r.nextLine();
        System.out.println("Please input a password:");
        String password=r.nextLine();
        //2. Call method
        boolean f = new JBDCDemo9().login2(username, password);
        //3. Judge the result and output different statements
        System.out.println(username);
        if (f){
            //success
            System.out.println("Login succeeded!");
        }else{
            System.out.println("Login failed!");
        }
    }

    /**
     * Login method
     * @return Return success or failure
     */
    /*
    public boolean login(String username,String password){
        if (username==null||password==null){
            return false;
        }
        //Connect to the database and judge whether the login is successful
        Connection con=null;
        Statement stmt=null;
        ResultSet resultSet=null;
        //1. Get connection
        try {
            con= JDBCUtils.getConnection();
            //2. Define sql
            String sql="select * from user where username = '"+username+"' and password = '"+password+"'";
            //3.Get the object executing sql
            stmt=con.createStatement();
            //4. Execute query
            resultSet = stmt.executeQuery(sql);
            //5. Judge whether there is data in the result set
//            if (resultSet.next()){//If there is a next line
//                return true;
//            }else{
//                return false;
//            }
            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(resultSet,stmt,con);
        }


        return true;
    }
*/
    /**
     * The login method is implemented using PreparedStatement
     * @return Return success or failure
     */
    public boolean login2(String username,String password){
        if (username==null||password==null){
            return false;
        }
        //Connect to the database and judge whether the login is successful
        Connection con=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        //1. Get connection
        try {
            con= JDBCUtils.getConnection();
            //2. Define sql
            String sql="select * from user where username = ? and password = ?";
            //3. Get the object executing sql
            preparedStatement = con.prepareStatement(sql);
            //Assign a value to the question mark
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            //4. Execute the query without passing sql
            resultSet = preparedStatement.executeQuery();
            //5. Judge whether there is data in the result set
//            if (resultSet.next()) {/ / if there is a next line
//                return true;
//            }else{
//                return false;
//            }
            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(resultSet,preparedStatement,con);
        }


        return true;
    }
}

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

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

      • Solution: configuration file (the configuration file must be under src, not a folder!!! Blood flow lesson)

        jdbc.properties

        url=

        user=

        password=

    3. Extract a method to release resources

  • Code implementation:

package cn.itcast.util;

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

/**
 * JDBC Tool class
 */
public class JDBCUtils {

    private static String url;
    private static String user;
    private static String password;
    private static String driver1;

    static{
        //Read the resource file and get the value
          try {
            //1. Create Properties collection class
            Properties pro = new Properties();

            //How to get the file under src path -- > classloader classloader
            ClassLoader classLoader=JDBCUtils.class.getClassLoader();
            URL re = classLoader.getResource("jdbc.properties");
            String path = re.getPath();
            System.out.println(path);

            //2. Load file
            pro.load(new FileInputStream(path));
//            pro.load(JDBCUtils.class.getResourceAsStream("/jdbc.properties"));
            //3. Obtain data
            url=pro.getProperty("url");
            user=pro.getProperty("user");
            password=pro.getProperty("password");
            driver1 = pro.getProperty("driver");
            //4. Register driver
            Class.forName(driver1);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }


    }


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

    /**
     * Release resources
     */
    public static void close(ResultSet res,Statement stmt, Connection con){
        if (stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

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

    }


    public static void close(Statement stmt, Connection con){
        if (stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.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 the sql has query results, it will succeed, otherwise it will fail
    • Steps:

      1. Create database table user

        CREATE TABLE USER(
        	id INT PRIMARY KEY AUTO_INCREMENT,
        	username VARCHAR(32),
        	PASSWORD VARCHAR(32)
        );
        
        SELECT * FROM USER;
        
        INSERT INTO USER VALUES(NULL,'zhnagsan','123');
        INSERT INTO USER VALUES(NULL,'lisi','234');
        
        
        

JDBC control transaction

  1. Transaction: a business operation with multiple steps. If the business operation is managed by a transaction, the multiple steps will either succeed or fail at the same time.
  2. operation
    1. Open transaction
    2. Commit transaction
    3. Rollback transaction
  3. Use the conjunction 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

Tags: Java MySQL Big Data SQL

Posted on Tue, 28 Sep 2021 13:57:36 -0400 by localhost1