Knowledge of JDBC

1. 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

2. Steps: (there are improvements later)

        1. Import driver jar package

        2. Register driver

        3. Get database Connection object Connection

        4. Define sql

        5. Gets the object Statement that executes the sql Statement

        6. Execute sql and receive the returned object

        7. Processing results

        8. Release resources

Implementation code:

 //1. Import the driver jar package
            //2. Register driver
                Class.forName("com.mysql.jdbc.Driver");
            //3. Get database connection object
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1","root","root");
            //4. Define sql statements
            String sql = "update account set balance = 1500 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();

3. Explain each object in detail:

        1.DriverManager: drive management object

Function:

                        1.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.jdbc.Driver");

By looking at the source code, I found that: on COM mysql. jdbc. Static code block in driver 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.

                        1.2. Get database connection:

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

Parameters:

url: specify the connection path

Syntax: jdbc:mysql://ip Address (domain name): port number / database name

Details: if the local mysql server is connected and the default port number of the mysql server is 3306, the url can be abbreviated as: jdbc:mysql: / / / database name

User: user name

Password: password

                    2.Connection: database connection object

                        2.1. Get the object executing sql

                                Statement createStatement()

                                Preparedstatement   preparedstatement(String sql)

                        2.2. Management services:

Start transaction: setAutoCommit(boolean autoCommit): call this method and set the parameter to false, that is, start the transaction

Commit transaction: commit()

Rollback transaction: rollback()

                          3.Statement: object to execute sql

                                        3.1. Execute sql

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

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

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

                                                3.ResultSet executeQuery(String sql): executes DQL(select) statements

Exercise: adding a message to the database

/**
 * jdbc Exercise to add information to the database
 */
public class JdbcDemo2 {
    public static void main(String[] args) {
        Connection conn =null;
        Statement stat = null;
        int num = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "insert into account value(4,1000)";

            conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");

            stat = conn.createStatement();

            num = stat.executeUpdate(sql);

            System.out.println(num);
            if(num>0){
                System.out.println("Added successfully");
            }else{
                System.out.println("Add failed");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

Exercise: querying data in a table

/**
 * jdbc Exercise, query the information in the table
 */
public class JdbcDemo3 {
    public static void main(String[] args) {
        Connection conn =null;
        Statement stat = null;
        ResultSet set = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String sql = "select * from account";

            conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");

            stat = conn.createStatement();

            set = stat.executeQuery(sql);

            while(set.next()){
                int id = set.getInt(1);

                String balance = set.getString("balance");

                System.out.println("Employee number:"+id+"  "+"The salary of employees is:" + balance);
            }
            if(set !=null){
                System.out.println("Search succeeded");
            }else{
                System.out.println("Search failed");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

                          4.ResultSet: result set object} encapsulates query results

next(): cursor moves down one line

Getxxx (parameter): get data

* Xxx: represents the data type. For example: int getInt(), String getString()

* parameters:

                                                1.int: represents the number of the column, starting from 1. For example: getString(1)

                                                2.String: represents the name of the column. For example: getDouble("field name")

Note:

* use steps: 1 Move the cursor down one line {2 Judge whether there is data # 3 get data

                               5. Preparedstatement: the object that executes sql

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

* enter the user's password: a 'or' a '=' a '

                                        2. Solve the problem of sql injection: 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 database Connection object Connection

                                                4. Define sql

* Note: the sql parameters are used in the following way:? As a placeholder.

For example: select * from user where username =? and password = ?;

                                                5. Get the object perforedstatement connection. That executes the SQL statement prepareStatement(String sql)

                                                6. Here? Assignment:

Method: setXXX (parameter 1, parameter 2)

Parameter 1:? The position number of starts from 1

Parameter 2:? Value of

                                                7. Execute sql and receive the returned results without passing sql statements

                                                8. Processing results

                                                9. Release resources

                                

4. Extract JDBC tool class

Objective: to simplify writing

Analysis:

                                        1. Registered drivers are also extracted

                                        2. Extract a method to obtain the connection object

* requirements: do not want to pass parameters, but also ensure the universality of tool classes

* solution: Profile

                                                        jdbc.properties

                                                                 url=        user=         password=

                                        3. Extract a method to release resources

Exercise: use JDBC tools to simplify the query function

        1. Create a file. The data in the file is as follows:

url=jdbc:mysql:///db1
user=root
password=root
driver=com.mysql.jdbc.driver

         2. Create JDBC tool class

package cn.itcast.jdbc.utils;

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 driver;
    /**
     * The file can be read only once, using static code blocks
     */
    static{

        try {
            //Read the resource file and get the value

            //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();
            URL resource = classLoader.getResource("jdbc.properties");
            String path = resource.getPath();
            System.out.println(path);
            //2. Load file
            pro.load(new FileReader(path));

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


    }

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

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

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

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



}

3. Improved class code

package cn.itcast.jdbc;

import cn.itcast.jdbc.utils.JDBCUtils;

import java.sql.*;

/**
 * jdbc Exercise to demonstrate JDBC tool class
 */
public class JdbcDemo4 {
    public static void main(String[] args) {
        Connection conn =null;
        Statement stat = null;
        ResultSet set = null;
        try {
            //Class.forName("com.mysql.jdbc.Driver");
            String sql = "select * from account";

           // conn = DriverManager.getConnection("jdbc:mysql:///db1", "root", "root");

            conn = JDBCUtils.getConnection();
            stat = conn.createStatement();

            set = stat.executeQuery(sql);

            while(set.next()){
                int id = set.getInt(1);

                String balance = set.getString("balance");

                System.out.println("Employee number:"+id+"  "+"The salary of employees is:" + balance);
            }
            if(set !=null){
                System.out.println("Search succeeded");
            }else{
                System.out.println("Search failed");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
//            if (stat != null) {
//                try {
//                    stat.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
//            }
//            if (conn != null) {
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    e.printStackTrace();
//                }
            JDBCUtils.close(set,stat,conn);
            }
        }
    }


Exercise: implement a simple login function

Requirements: enter the user name and password through the keyboard, and {judge whether the user logs in successfully


/**
 *  Requirements: enter the user name and password through the keyboard to judge whether the user has successfully logged in
 *  Problem: there is a SQL injection problem
 */
public class JdbcDemo5 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("Please enter your name:");
        String name = scanner.nextLine();
        System.out.println("Please input a password:");
        String password = scanner.nextLine();
        boolean flag = new JdbcDemo5().login(name, password);
        if(flag ==true){
            System.out.println("Login successful");
        }else{
            System.out.println("Login failed");
        }
    }

    public boolean login(String name,String password){
        ResultSet res = null;
        Connection conn = null;
        Statement stat = null;
            if(name == null || password ==null)
                 return false;
        try {
             conn = JDBCUtils.getConnection();
            String sql = "select * from user where name='"+name+"' and password='"+password+"'";
            stat = conn.createStatement();
            res = stat.executeQuery(sql);
            return res.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(res,stat,conn);
        }
        return false;
    }


}

 


/**
 *  Requirements: enter the user name and password through the keyboard to judge whether the user has successfully logged in
 *  Note: solve the SQL injection problem
 */
public class JdbcDemo6 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("Please enter your name:");
        String name = scanner.nextLine();
        System.out.println("Please input a password:");
        String password = scanner.nextLine();
        boolean flag = new JdbcDemo6().login(name, password);
        if(flag ==true){
            System.out.println("Login successful");
        }else{
            System.out.println("Login failed");
        }
    }

    public boolean login(String name,String password){
        ResultSet res = null;
        Connection conn = null;
        PreparedStatement pstat = null;
            if(name == null || password ==null)
                 return false;
        try {
             conn = JDBCUtils.getConnection();
            String sql = "select * from user where name= ? and password= ?";
            pstat = conn.prepareStatement(sql);
            pstat.setString(1,name);
            pstat.setString(2,password);
            res = pstat.executeQuery();
            return res.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(res,pstat,conn);
        }
        return false;
    }
}

5.JDBC 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 Connection object to manage transactions

* start transaction: setAutoCommit(boolean autoCommit): call this method and set the parameter to false, that is, start the transaction

* * start transaction before executing sql

* commit transaction: commit()

* * when all sql has completed the commit transaction

* rollback transaction: rollback()

* * rollback transaction in catch

Exercise: do a simple transfer function


/**
 * jdbc Using transactions, a simple transfer
 */
public class jdbcDemo7 {
    public static void main(String[] args) {
        PreparedStatement pstat1 = null;
        PreparedStatement pstat2 = null;
        Connection conn = null;
        try {
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);//Open transaction
            String sql1="update  accounts set balance = balance - ? where id = ?";

            String sql2="update  accounts set balance = balance + ? where id = ?";

            pstat1 = conn.prepareStatement(sql1);
            pstat2 = conn.prepareStatement(sql2);

            pstat1.setInt(1,500);
            pstat1.setInt(2,1);

            pstat2.setInt(1,500);
            pstat2.setInt(2,2);


            pstat1.executeUpdate();

            //Manufacturing error
            int i = 3/0;

            pstat2.executeUpdate();
            conn.commit();//Commit transaction
        } catch (Exception e) {
            try {
                conn.rollback();//Rollback transaction
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBCUtils.close(conn,pstat1);
            JDBCUtils.close(null,pstat2);
        }


    }
}

Keywords: Java Database MySQL jar

Added by Pehape on Mon, 21 Feb 2022 06:58:21 +0200