First of all, the novice java, newly learned jdbc, feels so detailed that every time you forget it, you need to check it. Write a piece of your own to record it for your own convenience. The novice is brave to see the official if there are any mistakes.

Introduction to 1.1jdbc

Introduction to JDBC (Java DateBase connectivity) Java Database Technology

Specifically, it is the technology to connect to the database through Java and add, delete, alter and check the data in the database.

1.2jdbc related API s

JDBC operates on databases through Java code, which defines interfaces and classes to operate on databases. Driver driver interfaces define how Java can acquire connections to databases

The DriverManager Tool Class provides managerial-driven convenience for accessing connections to databases

The Connection connection interface represents the connection between Java and the database

Tool interface for PrepareStatement (statement) Sending SQL statements Objects of this type are used to send a SQL statement to a database

ResultSet ResultSet Interface Objects of this type represent the result of a query against SQL

1.3 jdbc is specification not implementation

Fundamentally, JDBC is a specification that provides a complete set of interfaces that allow portable access to the underlying database, making it easy to use

Why does JDBC only have interfaces and not provide implementations? The underlying technologies of different databases are different. Many databases are closed source and the source code is not public. Sun Company is unable to provide a specific implementation for all databases. Only the interface can be provided and the specific implementation can be provided by the database manufacturer. Sun Company has only formulated the JDBC standard, and each database manufacturer provides specific implementation according to the standard.

JDBC Writes programs for various databases

 

Programming of 2.0 jdbc

Programming steps for 2.1 jdbc

First, the jdbc database and Navicat opera dramas work essentially the same, and the steps to operate the database are similar, so compare the two.

Specific steps:

1. Load Driver

2. Get connected

3. Prepare a tool to send sql

4. Send SQL

5. Processing result sets

6. Release resources

Dead work

1. Prepare for development (set up development environment)

A database-driven jar package needs to be introduced into the project (jar package: compressed format for class files contains multiple class files with packages, similar to zip, rar after the file has been packaged)

idea

Add mysql-connector-java-8.0.23.jar to the project

1. Right-click on the item, create a new lib folder, copy the jar package into lib 2. Select the jar package, add as

Be similar to

 

Code:

public class JDBCTest01 {
public static void main(String[] args) throws Exception {

//1. Load driver Class.forName("com.mysql.cj.jdbc.Driver");
//2. Get connected
String username="root"; String password="root";
//The url parameter is used to determine the database information for the connection
/*
*	Database Machine IP
*	Port number:port
*	Database name db_name
*	Connected parameter codec set, time zone
*	url Format: jdbc:mysql://ip:port//db_name?k=v Parameters...
* */
String url="jdbc:mysql://localhost:3306/baizhi? userUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=Asia/Shanghai";

Connection conn = DriverManager.getConnection(url, username, password);
//3. Tools to prepare to send SQL
String sql="INSERT INTO t_person VALUES(null,'King Five',19,'male','199999999','University City')";
PreparedStatement pstm = conn.prepareStatement(sql);

//4. Send Execute SQL
//One return value, update, tells us how many rows of data in the table have changed
int update = pstm.executeUpdate(); System.out.println("Number of affected bars"+update);
//5. Processing result sets (if any)

//6. Release resources
//Release later used resources first
pstm.close();
conn.close();
}
}

Similar corresponding additions and deletions can be imitated, but note that the execution of additions and deletions and queries is different

Add, delete, change: executeUpdate() is used to send the SQL tool

Get an int return value

Query: Use executeQuery() to send the SQL tool

What you get is

Result set processing:

 

 

Note: There are two data processing methods for result sets, serial number acquisition and column name acquisition. Column name acquisition is recommended.

Case demo:

public class JDBCTest03 {
public static void main(String[] args) throws Exception {

//1. Load driver Class.forName("com.mysql.cj.jdbc.Driver");
//2. Create connections to databases
String username="root"; String password="root";
String url="jdbc:mysql://localhost:3306/baizhi?userUnicode=true&characterEncoding=UTF- 8&userSSL=false&serverTimezone=Asia/Shanghai";
Connection conn = DriverManager.getConnection(url, username, password);

//3. Prepare a tool to send SQL
String sql="select * from t_person"; PreparedStatement pstm = conn.prepareStatement(sql);

//4. Send and execute SQL
ResultSet rs = pstm.executeQuery();
//5. Processing result sets
/*
* getXxx("Column Name ": Gets the value Xxx of the specified column as a data type
* getXxx(Sequence number: Gets the value of the column in the specified order
*
* */
while (rs.next()){
int personId = rs.getInt("person_id");
String personName = rs.getString("person_name"); int age = rs.getInt("age");
String sex = rs.getString(4); String mobile = rs.getString(5);
String address = rs.getString(6);

System.out.println("personId="+personId+",personName="+personName+",age="+age+",sex="+sex+",mob
ile="+mobile+",address="+address);
}
//6. Release resources rs.close();
pstm.close();
conn.close();
}
}

3. Data binding

Data binding: Bind user input data to SQL statements

The data in the SQL executed by JDBC changes according to the user's input, such as the query SQL behind the login function to perform different conditions depending on the user name, which requires binding the data entered by the user to the SQL statement.

There are two ways to bind data:

String splicing, placeholder binding

3.1 String Stitching

Essentially, correct SQL statements are constructed using Java string splicing syntax

1. Replace with variable names where data is needed
2. Add'+after variable name'before variable name
3. If you stitch a string, you need to add'after +'before'+'

Code examples:

static int loginSys(Connection conn) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.print("enter one user name:");
        String adminName = scanner.nextLine();
        System.out.print("Please input a password:");
        String password = scanner.nextLine();
        String sql = "select admin_name, password from admin where admin_name = '"+adminName+"'and password = '"+password+"'";
        PreparedStatement pstm = conn.prepareStatement(sql);

        ResultSet rs = pstm.executeQuery();
        int flag = 0;
        while (rs.next()){
            flag = 1;
            String AdminName = rs.getString("admin_name");
            String PassWord = rs.getString("password");

            System.out.println("User name:" + AdminName + "Password:" + PassWord);
        }
        System.out.println(rs.next());
        if (flag == 1){
            return 1;
        }
        else{ return 0;}
    }

3.2 Placeholder Stitching

? Placeholders are a special syntax in JDBC that is specifically used for parameter binding steps:

1. Where data is needed, use? Instead of (placeholder)
2. Assign values to? Via the pstm. setXxx method before sending SQL

Code example:

public Student findStudent(String name) {

        Student stu = new Student();
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;

        conn = JDBCUtils.getConnection();
        String sql = "select student_id, student_name, sex, class_id from t_student where student_name = ?";
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,name);
            rs = pstm.executeQuery();
            if ( rs != null){
                while (rs.next()){
                    stu.setStudentId(rs.getString("student_id"));
                    stu.setStudentName(rs.getString("student_name"));
                    stu.setSex(rs.getString("sex"));
                    stu.setClassId("class_id");
                    stu.toString();
                }
            }else{
                stu = null;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            if (null != conn || pstm != null || rs != null){
                JDBCUtils.free(rs,null,conn,pstm);
            }
        }
        return stu;

    }

Differences between 3.3 string stitching and placeholder stitching