JDBC learning notes

Posted by chrille112 on Tue, 22 Feb 2022 13:57:36 +0100

1, What is JDBC
Java database connectivity.
Essence of JDBC: JDBC is a set of interface s formulated by SUN company.
Interfaces have callers and implementers.
Interface oriented calling and interface oriented writing implementation classes belong to interface oriented programming.
Polymorphic mechanism is very typical: Abstract oriented programming. (do not program specifically).

   Animal a = new Cat();
   Animal a = new Dog();
public void feed (Animal a){//Parent oriented programming

Not recommended:

Dog d = new Dog();
Cat c =new Cat();

The reason for formulating a set of JDBC interfaces: because the underlying implementation principle of each database is different.

2, JDBC programming steps
1. Register the driver (function: tell the java program which brand of database to connect to)
2. Get the connection (it means that the channel between the JVM process and the database process is open, which belongs to the communication between processes. Be sure to close the channel after use)
3. Obtain the database operation object (the object specially executing sql statements)
4. Execute SQL statement (DQL, DML)
5. Process the query result set (step 5 is available only when the select statement is executed in step 4)
6. Release resources (after using resources, be sure to close resources. Java and database belong to processes between processes, and be sure to close them after opening)

3, Demonstration

import java.sql.Statement;
public class JDBC01{
      public static void main (String [] args){
     // 1. Register driver
    //DriverManager.registerDriver(new com.mysql.jdbc.Driver()); The first writing method of registration drive
    Class.forName("com.mysql.jdbc.Driver");//The second writing method of registration driver is commonly used.

    //2. Get connection
   String url ="jdbc:mysql://ip:port/name";//url: uniform resource locator. Including: protocol, IP, port name, port
   String user = "root";
   String password = " xxx";
   Connection conn = DriverManager.getConnection(url,uesr,password);
    System.out.printIn("Database connection object=" +conn);

      //3. Get database operation object
   Statement stmt = conn.createStatement();

      //4. Execute SQL statement
     String sql = "select empno,ename,sal from emp";
    rs = stmt.executeQuert(sql);//A method dedicated to executing DQL statements.

   //5. Processing query result set
 boolean flag1 = rs.next();
    //The line pointed to by the cursor has data
    //Fetch data
    //The characteristic of getString() method is that no matter what the data type of the database is, it is retrieved in the form of String.
     String empno = rs.getString(1);
     String ename = rs.getString(2);
     String sal = rs.getString(3);
     System.out.printIn(empno+"," +ename +"," +sal);

   }catch (SQLExeption e){
   //6. Release resources
  //In order to ensure that the resources will be released, close the resources in the finally statement block, and close them from small to large, and align try catch
           if(rs != null){
}catch(SQLExeption e){
     if(conn != null){
}catch(SQLExeption e){

4, Additional knowledge

Similarities and differences, advantages and disadvantages of Statement and PreparedStatement
Same: both are used to execute SQL statements
Different: PreparedStatement needs to be created according to SQL Statement. It can set parameters and specify corresponding values, instead of using string splicing like Statement.
Advantages of PreparedStatement:
1. Its use parameter setting has good readability and is not easy to remember wrong. The use of string splicing in statement has poor readability and maintainability.
2. It has precompiled mechanism and its performance is faster than statement.
3. It can effectively prevent SQL injection attacks.
Principle of PreparedStatement: compile the framework of SQL statement in advance, and then pass "value" to SQL statement. Is a precompiled database operation object.
The difference between execute and executeUpdate
Similarities: both can perform operations such as adding, deleting and modifying.
1. Execute can execute the query statement, and then get the result through getResult. executeUpdate cannot execute a query statement.
2. execute returns the Boolean type. true indicates that the query statement is executed, and false indicates that the insert, delete, update, etc. are executed. The return value of executeUpdate is int, which indicates how many pieces of data have been affected.

Topics: Java Database JDBC