0. Overview

This article uses postgreSQL to connect through JDBC data base Examples of common database operations and JDBC API, JDBC's general workflow and JDBC transactions are introduced.

1. Preparations

A. Download and install the postgreSQL database.

B. Build a new one Java Project and import the JDBC driver jar package of postgreSQL.

2. Java connection postgreSQL code example.

1. package vertxTest;  
2.   
3. import java.sql.Connection;  
4. import java.sql.DriverManager;  
5. import java.sql.Statement;  
6.   
7. public class PostgreSQLJDBC {  
8.     public static void main(String args[]) {  
9.         Connection c = null;  
10.         Statement stmt = null;  
11.         try {  
12.             Class.forName("org.postgresql.Driver");  
13.             c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/pgsqltest", "postgres", "2016");  
14.             c.setAutoCommit(false); //Automatically submit  
15.             System.out.println("Opened database successfully");  
16.   
17.             stmt = c.createStatement();   
18.             String sql = "CREATE TABLE STUDENTS " +   
19.                          "(ID TEXT PRIMARY KEY     NOT NULL ," +   
20.                          " NAME            TEXT    NOT NULL, " +   
21.                          " SEX             TEXT    NOT NULL, " +   
22.                          " AGE             TEXT    NOT NULL)";   
23.             stmt.executeUpdate(sql);   
24.             System.out.println("Table created successfully");  
25.               
26.             stmt.close();  
27.             c.commit();  
28.             c.close();  
29.         } catch (Exception e) {  
30.             System.err.println(e.getClass().getName() + ": " + e.getMessage());  
31.             System.exit(0);  
32.         }  
33.     }  
34. }  

3. Common addition, deletion and alteration checking operations.

A. Classes defining records (optional)

1. package vertxTest;  
2.   
3. public class Student {  
4.     private String Id;  
5.     private String Name;  
6.     private String Sex;  
7.     private String Age;  
8.   
9.     Student(String Id,String Name, String Sex, String Age) {  
10.         this.Id = Id; //default  
11.         this.Name = Name;  
12.         this.Sex = Sex;  
13.         this.Age = Age;  
14.     }  
15.   
16.     public String getId() {  
17.         return Id;  
18.     }  
19.   
20.     public void setId(String Id) {  
21.         this.Id = Id;  
22.     }  
23.   
24.     public String getName() {  
25.         return Name;  
26.     }  
27.   
28.     public void setName(String Name) {  
29.         this.Name = Name;  
30.     }  
31.   
32.     public String getSex() {  
33.         return Sex;  
34.     }  
35.   
36.     public void setSex(String Sex) {  
37.         this.Sex = Sex;  
38.     }  
39.   
40.     public String getAge() {  
41.         return Age;  
42.     }  
43.   
44.     public void setage(String Age) {  
45.         this.Age = Age;  
46.     }  
47. }  

B, JDBC table data operation method

1. package vertxTest;  
2.   
3. import java.sql.Connection;  
4. import java.sql.DriverManager;  
5. import java.sql.PreparedStatement;  
6. import java.sql.ResultSet;  
7. import java.sql.SQLException;  
8.   
9. public class JDBCOperation {  
10.       
11.     /** 
12.      * @method getConn() Get the connection to the database 
13.      * @return Connection 
14.      */  
15.     public Connection getConn() {  
16.         String driver = "org.postgresql.Driver";  
17.         String url = "jdbc:postgresql://localhost:5432/pgsqltest";  
18.         String username = "postgres";  
19.         String password = "2016";  
20.         Connection conn = null;  
21.         try {  
22.             Class.forName(driver); //classLoader, load corresponding driver  
23.             conn = (Connection) DriverManager.getConnection(url, username, password);  
24.         } catch (ClassNotFoundException e) {  
25.             e.printStackTrace();  
26.         } catch (SQLException e) {  
27.             e.printStackTrace();  
28.         }  
29.         return conn;  
30.     }  
31.       
32.     /** 
33.      * @method insert(Student student) Insert data into tables 
34.      * @return int Number of successfully inserted data bars 
35.      */  
36.     public int insert(Student student) {  
37.         Connection conn = getConn();  
38.         int i = 0;  
39.         String sql = "insert into students (id,Name,Sex,Age) values(?,?,?,?)";  
40.         PreparedStatement pstmt;  
41.         try {  
42.             pstmt = (PreparedStatement) conn.prepareStatement(sql);  
43.             pstmt.setString(1, student.getId());  
44.             pstmt.setString(2, student.getName());  
45.             pstmt.setString(3, student.getSex());  
46.             pstmt.setString(4, student.getAge());  
47.             i = pstmt.executeUpdate();  
48.             pstmt.close();  
49.             conn.close();  
50.         } catch (SQLException e) {  
51.             e.printStackTrace();  
52.         }  
53.         return i;  
54.     }  
55.       
56.     /** 
57.      * @method delete(Student student) Delete data from tables 
58.      * @return int Successfully delete the number of data bars in the table 
59.      */  
60.     public int delete(String name) {  
61.         Connection conn = getConn();  
62.         int i = 0;  
63.         String sql = "delete from students where Name='" + name + "'";  
64.         PreparedStatement pstmt;  
65.         try {  
66.             pstmt = (PreparedStatement) conn.prepareStatement(sql);  
67.             i = pstmt.executeUpdate();  
68.             System.out.println("resutl: " + i);  
69.             pstmt.close();  
70.             conn.close();  
71.         } catch (SQLException e) {  
72.             e.printStackTrace();  
73.         }  
74.         return i;  
75.     }  
76.       
77.     /** 
78.      * @method update(Student student) Change the data in the table 
79.      * @return int Successfully changing the number of data bars in the table 
80.      */  
81.     public int update(Student student) {  
82.         Connection conn = getConn();  
83.         int i = 0;  
84.         String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";  
85.         PreparedStatement pstmt;  
86.         try {  
87.             pstmt = (PreparedStatement) conn.prepareStatement(sql);  
88.             i = pstmt.executeUpdate();  
89.             System.out.println("resutl: " + i);  
90.             pstmt.close();  
91.             conn.close();  
92.         } catch (SQLException e) {  
93.             e.printStackTrace();  
94.         }  
95.         return i;  
96.     }  
97.       
98.     /** 
99.      * @method Integer getAll() Query and print the data in the table 
100.      * @return Integer Query and print the data in the table 
101.      */  
102.     public Integer getAll() {  
103.         Connection conn = getConn();  
104.         String sql = "select * from students";  
105.         PreparedStatement pstmt;  
106.         try {  
107.             pstmt = (PreparedStatement)conn.prepareStatement(sql);  
108.             ResultSet rs = pstmt.executeQuery();  
109.             int col = rs.getMetaData().getColumnCount();  
110.             System.out.println("============================");  
111.             while (rs.next()) {  
112.                 for (int i = 1; i <= col; i++) {  
113.                     System.out.print(rs.getString(i) + "\t");  
114.                     if ((i == 2) && (rs.getString(i).length() < 8)) {  
115.                         System.out.print("\t");  
116.                     }  
117.                  }  
118.                 System.out.println("");  
119.             }  
120.                 System.out.println("============================");  
121.         } catch (SQLException e) {  
122.             e.printStackTrace();  
123.         }  
124.         return null;  
125.     }  
126. }  

C,test class

1. package vertxTest;  
2.   
3. public class JDBCTest {  
4.     public static void main(String args[]) {  
5.         JDBCOperation op = new JDBCOperation();  
6.         op.getAll();  
7.         op.insert(new Student("001","Achilles", "Male", "14"));  
8.         op.insert(new Student("002","Bean", "Fmale", "15"));  
9.         op.getAll();  
10.         op.update(new Student("002","Bean", "", "7"));  
11.         op.delete("Achilles");  
12.         op.getAll();  
13.     }  
14. }  

C. Output results

============================ ============================ ============================ 001 Achilles   Male   14  002 Bean       Fmale  15  ============================ resutl: 1 resutl: 1 ============================ 002 Bean       Fmale  7   ============================

4. Code Analysis

In the process of adding, deleting and modifying the database mentioned above, we can find its common part, that is, the general process:

 

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

 

(2) Pass in the SQL query command to the Connection object and get the PreparedStatement object.

 

(3) ExcuteUpdate () or executeQurey() is executed on the PreparedStatement object to get the result.

 

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

 

As you can see, when using JDBC, the two most frequently interacted classes are Connection, PreparedStatement, and ResultSet in select.

5. JDBC API

API	Explain
java.sql.Connection	Connection to a specific database (session). The information provided by the database, the supported SQL grammar, stored procedures and the functions of this connection can be obtained through the getMetaData method. Represents the database. -
java.sql.Driver	Each driver class must implement the interface, at the same time, each database driver should provide a class to implement the Driver interface. -
java.sql.DriverManager (Class)	Manage a set of basic services for JDBC drivers. As part of the initialization, this interface attempts to load the driver referenced in the "jdbc.drivers" system property. It's just an auxiliary class. It's a tool. -
java.sql.Statement	Objects used to execute static SQL statements and return their generated results. -
java.sql.PreparedStatement	The Statement interface is inherited to represent the object of the precompiled SQL statement, which is precompiled and stored in the PreparedStatement object. This object can then be used to execute the statement multiple times efficiently. -
java.sql.CallableStatement	Used to access stored procedures in the database. It provides some ways to specify the input/output parameters used by the statement. -
java.sql.PreparedStatement	The Statement interface is inherited to represent the object of the precompiled SQL statement, which is precompiled and stored in the PreparedStatement object. This object can then be used to execute the statement multiple times efficiently. -
java.sql.CallableStatement	Used to access stored procedures in the database. It provides some ways to specify the input/output parameters used by the statement. -

6. General workflow of JDBC

(1) Load Driver

Class.forName("org.postgresql.Driver"); The JAVA specification specifies that all drivers must register drivers in the static initialization code block into the driver manager.

(2) Connecting

conn=DriverManager.getConnection("jdbc:postgresql://localhost:5432/pgsqltest","postgres", "2016");

Connection connection is obtained by the static method getConnection(....) of DriverManager, which essentially passes parameters to the connect() method in the actual Driver to get the database connection.

The format of postgreSQL URL:

jdbc:postgresql:(protocol)@XXX.XXX.X.XXX:X X XXXX (IP address and port number): XXXXX (library name used)

MySQL Writing of URL s: jdbc:mysql://192.168.8.21:3306/test

(3) Obtain the Statement object

Statement stmt = conn.createStatement();

(4) Executing sql statements

stmt.executeQuery(String sql); // Returns a query result set.

stmt.executeUpdate(String sql); the // return value is int, indicating the number of entries affecting the record.

sql statements are sent to the database through the connection for execution in order to achieve the operation of the database.

(5) Processing result sets

Using the Connection object to get a Statement, the executeQuery(Stringsql) method in the Statement can use the select statement to query and return a result set. ResultSet, by traversing the result set, you can get the search result of the select statement. The next() method of ResultSet will operate a cursor to read from the front of the first record until the last record.

The executeUpdate(String sql) method is used to execute DDL and DML statements, such as update and delete operations.

Only when the select statement is executed can the result set be returned.

Statement str=con.createStatement(); // Create Statement

String sql="insert into test(id,name)values(1,"+"'"+"test"+"'"+")";

str. executeUpdate(sql);//Execute Sql statement

String sql="select * from test";

ResultSets rs = str. executeQuery (String sql); // Execute Sql statement with result set after execute select statement

// Traversal processing result set information

while(rs.next()){

System.out.println(rs.getInt("id"));

System.out.println(rs.getString("name"))

}

(6) Close database connection

rs.close();

stmt.close();

con.close();

ResultSet Statement Connection is dependent in turn.

Note: Close the resource in the order of ResultSet result set, Statement, and Connection. Because Statement and ResultSet are available only when they need to be connected, it is possible that other Statements need to be connected after the end of use, so Connection cannot be closed now.

7. JDBC transactions

Four characteristics of transactions

(1) Atomicity

The atomicity of transaction refers to that the program contained in the transaction acts as the logical unit of work of the database, and the data modification operations it does are either all or none of them.

Atomic operations, which are inseparable operations, must succeed together and fail together.

(2) Consistency

Transaction consistency means that the database must be in a consistent state before and after a transaction is executed. This feature is called transaction consistency. If the state of the database satisfies all integrity constraints, it is said that the database is consistent.

(3) Separation

Separation means that concurrent transactions are isolated from each other. That is to say, the operation inside a transaction and the data being operated must be blocked and not seen by other transactions that attempt to modify it.

(4) Persistence

Persistence means ensuring that updates to committed transactions are not lost when a system or medium fails. That is, once a transaction is committed, DBMS ensures that its changes to the data in the database should be permanent and can withstand any system failures. Persistence is guaranteed by database backup and recovery.