An in-depth explanation of the operation of JDBC in JAVA development

Sun company develops a set of standard APIs. They are just interfaces and do not provide implementation classes. The database vendors provide implementation classes, that is, drivers

jdbc operation process: 1.jar Package import; 2. Define the record class (such as Student class); 3. Obtain the connection; 4. Execution of sql.

// sql的执行 
// insert
private static int insert(Student student) {
    Connection conn = getConn();
    int i = 0;
    String sql = "insert into students (Name,Sex,Age) values(?,?,?)";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        pstmt.setString(1, student.getName());
        pstmt.setString(2, student.getSex());
        pstmt.setString(3, student.getAge());
        i = pstmt.executeUpdate();
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return i;
}

// update
private static int update(Student student) {
    Connection conn = getConn();
    int i = 0;
    String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        i = pstmt.executeUpdate();
        System.out.println("resutl: " + i);
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return i;
}
// select xx from tableName
private static Integer getAll() {
    Connection conn = getConn();
    String sql = "select * from students";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        int col = rs.getMetaData().getColumnCount();
        System.out.println("============================");
        while (rs.next()) {
            for (int i = 1; i <= col; i++) {
                System.out.print(rs.getString(i) + "\t");
                if ((i == 2) && (rs.getString(i).length() < 8)) {
                    System.out.print("\t");
                }
             }
            System.out.println("");
        }
            System.out.println("============================");
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

// delete
private static int delete(String name) {
    Connection conn = getConn();
    int i = 0;
    String sql = "delete from students where Name=&#39;" + name + "&#39;";
    PreparedStatement pstmt;
    try {
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        i = pstmt.executeUpdate();
        System.out.println("resutl: " + i);
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return i;
}

Test:

// 测试
public static void main(String args[]) {
    JDBCOperation.getAll();
    JDBCOperation.insert(new Student("Achilles", "Male", "14"));
    JDBCOperation.getAll();
    JDBCOperation.update(new Student("Bean", "", "7"));
    JDBCOperation.delete("Achilles");
    JDBCOperation.getAll();
}

Output result:

============================
1    Ender        male    8    
2    Bean        male    6    
3    Petra        fema    9    
4    Peter        male    9    
5    _Graff        male    40    
6    GOD        fema    255    
============================
============================
1    Ender        male    8    
2    Bean        male    6    
3    Petra        fema    9    
4    Peter        male    9    
5    _Graff        male    40    
6    GOD        fema    255    
7    Achilles    Male    14    
============================
resutl: 1
resutl: 1
============================
1    Ender        male    8    
2    Bean        male    7    
3    Petra        fema    9    
4    Peter        male    9    
5    _Graff        male    40    
6    GOD        fema    255    
============================

Code analysis:

During the above process of adding, deleting, modifying and checking the database, we can find that The common part, that is, the general process:

　(1) Create a Connection object and SQL query command string;

　(2) Pass the SQL query command to the Connection object and obtain the PreparedStatement object;

　(3) Execute executeUpdate() or executeQurey() on the PreparedStatement object to obtain the result;

　(4) Close the PreparedStatement object and the Connection object successively.

It can be seen that when using JDBC, the two classes that are most commonly dealt with are Connection and PreparedStatement, as well as the ResultSet class in select.

Thinking Questions

1. Every SQL operation needs to establish and close a connection, which will inevitably consume a lot of resource overhead. How to avoid it?

Analysis: You can use a connection pool to maintain the connection uniformly without having to establish and close it every time. In fact, this is used by many tools that encapsulate JDBC.

2. What should I do if the incoming data format in Java code is different from the database definition? For example, assign the Java String object to the tinyint property of the database.

Analysis: When executing a SQL statement, the database will try to perform conversion. According to my experiments, if a String object with pure letters is used to pass in the age attribute of tinyint, it will be converted to 0. The specific conversion rules should be related to the database.

The above is the detailed content of An in-depth explanation of the operation of JDBC in JAVA development. For more information, please follow other related articles on the PHP Chinese website!