MySQL database - detailed explanation of five objects (Java)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCDemo {
    public static void main(String[] args) throws Exception{
        //Register driver
//        Class. forName("com.mysql.jdbc.Driver");               // After JDBC 5, you don't need to register the driver
        //Get database connection object
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1","root","root");
        //Define sql statement
        String sql = "select * from db1.emp";
        //Get the Statement object executing sql
        Statement stmt = conn.createStatement();
        //Execute sql
        ResultSet resultSet = stmt.executeQuery(sql);
        //Processing results
        while(resultSet.next()) {                                     //Move the cursor to the next row. true indicates that there is a data row and you can move to the next row
            int id = resultSet.getInt(1);                    //Get id
            String name = resultSet.getString("ename");             //Get name
            System.out.println("id:" + id + " name:" + name);
        }
        //Release resources
        stmt.close();
        conn.close();
    }
}

1, DriverManager: drive management object

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

static void registerDriver(Driver driver): registered 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. There are static code blocks in the driver class,

static {
       try {
           java.sql.DriverManager.registerDriver(new Driver());
            } catch (SQLException E) {
                  throw new RuntimeException("Can't register driver!");
            }
}

So you can write it directly as follows:

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

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

2. Get database connection:

Method: static Connection getConnection(String 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/db3
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: database connection object

1. Get the object executing sql

* Statement createStatement()

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1","root","root");
Statement stmt = conn.createStatement();

* PreparedStatement prepareStatement(String sql)

V. PreparedStatement object

2. Management services:

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()

Rolling back transactions in catch

catch (Exception e) {
      try {
            if(conn != null)
                conn.rollback();          //If an exception occurs, the transaction is rolled back
           }

3, Statement: object to execute sql

1,boolean execute(String sql) :

Can execute arbitrary sql (understand)

2,int executeUpdate(String sql) :

Execute DML (insert, update, delete) statements and DDL(create, alter, drop) statements

* return value: the number of affected lines. You can judge whether the DML statement is executed successfully by the number of affected lines. If the return value > 0, the execution is successful, otherwise, it fails, as shown in the following code segment:

Statement stmt = conn.createStatement();
int count = stmt.executeUpdate("update db1.account set balance = 1500 where id = 2");
if (count > 0)
    System.out.println("Transfer succeeded");
else
    System.out.println("Transfer failed");

3,ResultSet executeQuery(String sql) :

Execute DQL (select) statement: usage

4, ResultSet: result set object that encapsulates query results

boolean next():

The cursor moves 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. 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("balance")
* use steps:
1. Move cursor down one line
2. Judge whether there is data
3. Get data

Usage:

//Processing results
ResultSet rs = stmt.executeQuery("update db1.account set balance = 1500 where id = 2");              
//Loop to determine whether the cursor is at the end of the last line.
while(rs.next()) {              //Move the cursor to the next row. true indicates that there is a data row and you can move to the next row
    int id = rs.getInt(1);                    //Get id
    String name = rs.getString("name");             //Get name
    double balance = rs.getDouble(3);              //Get salary
    System.out.println("id:" + id + " full name:" + name + " Salary:" + balance);
}

5, PreparedStatement: the object that executes sql

1. SQL injection attack:

When splicing sql, some special keywords of sql participate in string splicing. It will cause security problems. For example, enter the user's password casually: a 'or' a '='a( Universal password ), such as:
sql: select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a'

2. Solve the sql injection problem:

Use the PreparedStatement object to solve the problem

3. Precompiled SQL:

Parameter usage? As placeholder

4. Steps:

1. Import the driver jar package mysql-connector-java-5.1.37-bin jar
2. Register driver
3. Get the 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 PreparedStatement connection prepareStatement(String sql)
6. Here you are? Assignment:
* method: setXXX (parameter 1, parameter 2)
* parameter 1:? The position number of starts from 1
* parameter 2:? Value of
7. Execute sql and accept the returned results without passing sql statements
8. Treatment results
9. Release resources

5. Usage:

public boolean login(String username,String password) {
        if (username == null || password == null)
            return false;
        Connection conn = null;
        PreparedStatement pstmt = null;                //Prevent sql injection problems
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();         //Use your own JDBC utils to connect to the database
            String sql = "select * from db1.user where username = ? and password = ?";
            pstmt = conn.prepareStatement(sql);                            //Get the object of sql
            pstmt.setString(1,username);          //Here? assignment
            pstmt.setString(2,password);
            rs = pstmt.executeQuery();                          //Execute query
            return rs.next();                          //Returns true if there is a next line
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);               //Release resources with JDBCUtils written by yourself
        }
        return false;
}

Note: PreparedStatement will be used later to complete all operations of adding, deleting, modifying and querying
1. Can prevent SQL injection
2. More efficient

Topics: Java Database MySQL JDBC

Programmer Think