JDBC programming -- JDBC principle and basic programming

JDBC

WHAT IS JDBC

Java Database Connectivity: a solution for Java to access databases.
JDBC is a milestone solution for Java applications to access databases. Java developers hope to access different databases in the same way to realize the Java operation interface independent of the specific database.
JDBC defines a set of standard interfaces, that is, the general API for accessing the database. Different database manufacturers implement these interfaces according to the characteristics of their respective databases.

JDBC interface and database vendor implementation

Some interfaces are defined in JDBC:
1. Drive management:
DriverManager
2. Connection interface
Connection
DatabasemetaData
3. Statement object interface
Statement
PreparedStatement
CallableStatement
4. Result set interface
ResultSet
ResultSetMetaData

How JDBC works

JDBC only defines interfaces, and each database manufacturer is responsible for the specific implementation.
When programmers use it, they only need to call the interface. What they actually call is the implementation part of the underlying database manufacturer.

Working process of JDBC accessing database:
Load the driver and establish the connection
Create statement object
Execute SQL statement
Processing result set
Close connection

Driver interface and driver class loading

To use the JDBC interface, you need to load the implementation part (driver) of the corresponding database.
Driver class loading method (Oracle):

Class.forName("oracle.jdbc.driver.OracleDriver");

The meaning of this statement is: load the driver class, and the driver class realizes "automatic registration" in DriverManager through static block.

Connection interface

The Connection interface is responsible for the Connection of the application program to the database. After loading the driver, it uses three parameters: url, username and password to create a Connection to the specific database.

Class.forName("oracle.jdbc.OracleDriver")
//According to the url connection parameters, find the matching Driver object and call its method to obtain the connection
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.0.26:1521:tarena",
"openlab","open123");

It should be noted that Connection is only an interface, and the real implementation is completed by the driver package provided by the database manufacturer.

Statement interface

The Statement interface is used to process the SQL Statement object sent to the database, which is created through the Connection object. There are three common methods:

Statement stmt=conn.createStatement();
//1. The execute method returns true if the executed sql is a query statement and has a result set, and false if it is a non query statement or has no result set
boolean flag = stmt.execute(sql);
//2. Execute the query statement and return the result set
ResultSetrs = stmt.executeQuery(sql);
//3. Execute DML statement and return the number of records affected
int flag = stmt.executeUpdate(sql);

ResultSet interface

The result set returned after executing the query SQL statement is received by the ResultSet interface.
Common processing methods: traverse / judge whether there are results (login).

String sql = "select * from emp";
ResultSetrs = stmt.executeQuery(sql);
while (rs.next()) {
    System.out.println(rs.getInt("empno")+","
       +rs.getString("ename") );
}

The result of the query is stored in a series of lines of the ResultSet object. The initial position of the pointer is at the beginning of the line. The next() method is used to move between lines, and the getXXX() method is used to obtain the content of the field.

Processing result set ResultSet

ResultSet represents the DQL query result, which is a two-dimensional result It internally maintains a cursor for reading data. By default, the cursor is before the first row of data. When calling the next() method, the cursor will move down and return whether the result set contains data. If it contains data, it will return true The result set also provides a good getXXX method to get the result set cursor pointing to the current row of data

Principle:

Case:

/**
 * Execute DQL statement 
 */
public class Demo03 {
    public static void main(String[] args) 
        throws Exception{
        //Register driver
        String driver="oracle.jdbc.OracleDriver";;
        Class.forName(driver);
        //Connect database
        String url="jdbc:oracle:thin:@192.168.201.227:1521:orcl";
        String user="openlab";
        String pwd="open123";
        Connection conn=DriverManager.getConnection(
                url, user, pwd);
        //Create Statement
        Statement st=conn.createStatement();
        //Execute SQL(dql)
        String sql="select id, name "
                + "from robin_demo ";
        ResultSet rs=st.executeQuery(sql);
        //Processing results
        //The rs result set contains a cursor, which is in the result set by default
        //Before the first line of
        //rs.next(): move the result set cursor to the next row
        //Check whether there is data. If yes, return true, otherwise false
        while(rs.next()){
            //Getxxx (column name): returns the current row of the result set
            // Specifies the data for the column name
            int id = rs.getInt("id");
            String name=rs.getString("name");
            //Output query results
            System.out.println(id+","+name);
        }
        //Close connection
        conn.close();
    }
}

Read configuration file using Properties

Properties is an API in Java that is specifically used to read configuration files

  1. Its bottom layer is text file IO
  2. The Properties itself implements the Map interface, with hash tables inside
  3. Properties defines that both key and Value are String types

Properties common API methods:

  • Load reads a configuration file
  • String getProperty(key) reads a property value

Use steps:

  1. Create Properties object
  2. Use the load method to read the configuration file
  3. Using getProperty to query the content of the property file

Case, read the configuration file:

Add the configuration file dB. In the resource folder properties:

# db.properties
jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.201.227:1521:orcl
jdbc.username=openlab
jdbc.password=open123

Use Properties to read the contents of the configuration file:

public class Demo05 {
    public static void main(String[] args)
        throws IOException{
        // Properties is to read
        // *API designed for. properties file
        // Its bottom layer is text file IO
        // The Properties themselves implement the Map interface
        // The internal part is a hash table, which limits that both key and Value are
        // String type

        //Method: load reads the file as a hash table
        //String getProperty(key) query value

        //Use steps
        //1 create Properties object
        Properties cfg = new Properties();
        System.out.println(cfg);
        System.out.println(cfg.size());
        System.out.println(cfg.isEmpty());
        //2\.  Use the load method to read the file
        InputStream in=
            Demo05.class.getClassLoader()
            .getResourceAsStream("db.properties");
        //After execution, the contents of the file are read into the hash table
        cfg.load(in); 
        System.out.println(cfg);
        System.out.println(cfg.size());

        //3\.  To find the file content is to read the file content
        String s=
            cfg.getProperty("jdbc.driver");
        System.out.println(s); 
    }
}

The parameters in the program can be saved to the configuration file by using the configuration file. To modify the program parameters, you only need to modify the configuration file

Manage database connections

In software, database connection is used very frequently. If a connection is created every time, it will cause a lot of redundancy of code. The conventional practice is to establish database connection tool class, encapsulate the database connection process, unify the database connection process, and simplify the code when in use

Implementation steps:

  1. Create database connection parameter file dB properties
  2. Create dbutils Java encapsulated database connection method

    • Use Properties to read the database connection parameters in the configuration folder
    • Create a method getConnection to encapsulate the database connection process
  3. Using the getConnection method

Create profile dB properties

# db.properties
jdbc.driver=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.201.227:1521:orcl
jdbc.username=openlab
jdbc.password=open123

Create dbutils java

public class DbUtils {
    static String driver;
    static String url;
    static String username;
    static String password;

    //Read the database connection parameters in the file
    static{
        //Initialize static properties
        //1\.  Read configuration file with Properties
        //2\.  Find the corresponding parameter value from the configuration file
        try{
            Properties cfg=new Properties();
            InputStream in=
                DbUtils.class.getClassLoader()
                .getResourceAsStream("db.properties");
            cfg.load(in);
            System.out.println(cfg); 
            //Initialize connection parameters 
            driver=cfg.getProperty("jdbc.driver");
            url=cfg.getProperty("jdbc.url");
            username=cfg.getProperty("jdbc.username");
            password=cfg.getProperty("jdbc.password");
            in.close();
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    /**
     * Encapsulates the process of creating a database connection
     * Simplify database connections
     */
    public static Connection getConnection(){
        try{
            Class.forName(driver);
            Connection conn=
                DriverManager.getConnection(
                url, username, password);
            return conn;
        }catch(Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    //DbUtils.java
    /*
     * The connection method of closing the database encapsulates the complex closing process
     */
    public static void close(Connection conn){
        if(conn!=null){
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace(); 
            }
        }
    }
}

explain:

  1. driver url username password are four database connection parameters. Since only one is required, it is defined as a static variable
  2. The purpose of static code block is to read the values of four database connection parameters from the configuration file
  3. The getConnection method encapsulates the database connection process
  4. The close method encapsulates the process of closing the database connection

Use of DbUtils:

public class Demo06 {
    public static void main(String[] args) {
        Connection conn=null;
        try{
            conn=DbUtils.getConnection();
            Statement st=conn.createStatement();
            String sql="select * from robin_demo";
            ResultSet rs=st.executeQuery(sql);
            while(rs.next()){
                int id=rs.getInt("id");
                String name=rs.getString("name");
                System.out.println(id+","+name);
            }
            rs.close();//Release query results
            st.close();//Release statement object
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            DbUtils.close(conn);
        }
    }
}

Obviously: using DbUtils can simplify the writing of JDBC code
This code closes the database connection in finally. Its advantage is to close the connection reliably

Finally, if you are interested in these contents, you can continue to pay attention to me and update them every day~
Of course, if you need previous content, you can also ask me to read it. I have integrated it here for your convenience. Click for details here!!!

Tags: Java Programming JDBC interface

Posted by giba on Wed, 04 May 2022 06:42:29 +0300