1 background
1.1 data persistence
Data persistence is to save data to a power down storage device for later use. In most cases, especially for enterprise applications, data persistence means saving the data in memory to the hard disk for "solidification", and the implementation process of persistence is mostly completed through various relational databases.
The main application of persistence is to store the data in memory in relational database. Of course, it can also be stored in disk file and XML data file.
2 Foundation
2.1 concept
The full name of JDBC is Java Database Connectivity, which means the connection between Java and database.
JDBC is a set of interface specification for operating database provided by SUN company. It defines a standard Java class library for accessing database. Using this class library, you can access database resources more conveniently.
Programmers need to install database drivers when using databases. Different database drivers are also different. Therefore, for the convenience of programmer development, SUN company provides a set of interfaces for database manufacturers to implement these interfaces. Programmers only need to use this interface to operate different databases without paying attention to the installation of underlying database drivers, which greatly simplifies and speeds up the development process.
2.2 architecture
JDBC interface includes two levels:
JDBC API: that is, application-oriented API. It is an abstract interface for application developers to use and provides the connection between the program and JDBC manager.
JDBC Driver API: that is, an API for database driver, which requires developers to implement this interface and provides a connection between JDBC manager and database driver.
2.3 specifications
There are four main core objects:
DriverManager class (java.sql.DriverManager): used to register drivers and create connection objects.
Connection interface (java.sql.Connection): represents the connection created with the database.
Statement interface (java.sql.Statement): an object that executes database SQL statements and returns corresponding results.
ResultSet interface (java.sql.ResultSet): a result set or a virtual table, an object used to store table data.
The Statement interface also has two sub interfaces:
PreparedStatement interface (java.sql.PreparedStatement): a precompiled object, which is a sub interface of the Statement interface, is used to solve sql injection problems.
CallableStatement interface (java.sql.CallableStatement): supports SQL operations with parameters and calls to stored procedures. It is a sub interface of PreparedStatement interface.
3 connect to the database
The following instructions take connecting to a MySQL database as an example.
3.1 preparation
You have a MySQL database that can be accessed normally, and you can log in with your user name and password.
Create a project ready to connect to the database.
3.2 importing jar packages
Import the jar package used by Java to connect to MySQL. This jar package is usually provided by the database manufacturer. Here, download the package "mysql-connector-java-5.1.32.jar".
Take Eclipse as an example, right-click the project and click Properties:
Then find the "Java Build Path" directory on the left side of the pop-up page and enter it. Select the "Libraries" tab and find the "Add External JARs..." button on the right side of the tab:
Find the downloaded jar package and click to open it:
You can see that the jar package was successfully imported into the project:
Click OK to complete the import.
3.3 loading drive
The method to load the database driver is to call the static method forName of Class class. The syntax format is as follows:
public static Class<?> forName(String className)
The passed in parameter className is a driver name provided by each database vendor.
For different databases, the driver names are as follows:
MySQL Drive: com.mysql.jdbc.Drive Oracle Drive: oracle.jdbc.driver.OracleDriver SQLServer Drive: com.microsoft.sqlserver.jdbc.SQLServerDriver PostgreSQL Drive: org.postgresql.Driver DB2 Drive: com.ibm.db2.jdbc.net.DB2Driver Sybase Drive: com.sybase.jdbc.SybDriver
3.4 establishing connections
Use the static method getConnection of the DriverManager class to establish a connection to the specified database. The syntax format is as follows:
public static Connection getConnection(String url, String user, String password)
url is a protocol between SUN company and database manufacturer. User is the user name connecting to the database, and password is the password corresponding to the user name.
For different databases, the format of url protocol is as follows:
MySQL Format: jdbc:mysql://Address or host name: port number / database name Oracle Format: jdbc:oracle:thin:@Address or hostname:Port number:Database name SQLServer Format: jdbc:sqlserver://Address or host name: port number; databaseName = database name PostgreSQL Format: jdbc:postgresql://Address or host name: port number / database name DB2 Format: jdbc:db2:Address or hostname:Port number/Database name Sybase Format: jdbc:sybase:Tds:Address or hostname:Port number/Database name
If it is local and the default port number is used, the address and port number can be omitted:
jdbc:mysql:///Database name
It is recommended that the file code, database connection code and database code in the url be consistent. When adding data to the database, the connection parameters should preferably contain Unicode character support, so that the added characters can be recognized by the database and displayed normally:
jdbc:mysql://Address or host name: port number / database name? useUnicode=true&characterEncoding=UTF-8
Note: if the connection attribute is configured in the xml file, you need to escape the connector "&" and change it to "& amp;".
At this point, the Connection object connecting to the MySQL database is successfully obtained.
3.5 execution statement
After obtaining the Connection, use the createStatement method of the Connection interface to obtain the Statement object. The syntax format is as follows:
Statement createStatement()
After obtaining the Statement object, use the executeQuery method to execute the query Statement to obtain the result set of ResultSet type, and use the executeUpdate method to execute the add, delete and modify statements to obtain the number of records of int type.
3.6 release connection
The database Connection is a very rare resource and must be released immediately after it is used up. If the Connection cannot be closed in time and correctly, it will lead to system downtime. The use principle of Connection is to create as late as possible and release as early as possible.
The released method is by calling the close method of Connection. The syntax format is as follows:
void close()
A complete database operation process includes loading drivers, establishing connections, executing statements and releasing connections. Among them, the loading driver can be executed only once. After the established connection is used up, it must be released immediately.
4. Connect to database instance
4.1 configure connection information in the program
The complete code is as follows:
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
try {
String url = "jdbc:mysql://192.168.35.128:3306/demo";
String user = "root";
String password = "123456";
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}4.2 configure the connection information in the configuration file
The configuration file "jdbc.properties" is in the "src" directory and is at the same level as the root directory "package jdbc;" of the current source file. The contents are as follows:
driverClass=com.mysql.jdbc.Driver url="jdbc:mysql://192.168.35.128:3306/demo" user=root password=123456
The complete code is as follows:
public static void main(String[] args) {
Properties pros = new Properties();
try {
pros.load(TestConnection.class.getClassLoader().getResourceAsStream("jdbc.properties"));
} catch (IOException e) {
e.printStackTrace();
}
try {
Class.forName(pros.getProperty("driverClass"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
try {
String url = pros.getProperty("url");
String user = pros.getProperty("user");
String password = pros.getProperty("password");
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}5. Addition, deletion, modification and query
5.1 query
Usage example:
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
String url = "jdbc:mysql://192.168.35.128:3306/demo";
String user = "root";
String password = "123456";
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from student");
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString("address"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}5.2 add, modify and delete
Usage example:
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
Statement stmt = null;
try {
String url = "jdbc:mysql://192.168.35.128:3306/demo";
String user = "root";
String password = "123456";
conn = DriverManager.getConnection(url, user, password);
stmt = conn.createStatement();
int count = stmt.executeUpdate("delete from student where id = 903");
System.out.println("Number of rows affected:" + count);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}5.3 Statement interface
The Statement interface is obtained through the createStatement method of Connection. It is used to execute SQL statements and return corresponding results.
After using, you need to close the Statement object manually.
5.4 PreparedStatement interface
The Preparedstatement interface is obtained through the Preparedstatement method of Connection. It is a sub interface of the Statement and represents a precompiled SQL Statement.
The parameters in the incoming SQL statement are represented by a question mark "?. call the setXxx method of the PreparedStatement object to set these parameters. The setXxx method has two parameters. The first parameter is the parameter index to be set (starting from 1) and the second is the parameter value to be set.
Using a Statement may lead to SQL injection attack by people because of string splicing, but using PreparedStatement does not need to splice strings, but uses placeholder method, which effectively avoids the problem of SQL injection attack.
Use examples:
pstmt = conn.prepareStatement("select * from student where id = ?");
pstmt.setInt(1, 904);
rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString("address"));
}5.5 ResultSet interface
Use the executeQuery method of the Statement interface to execute the incoming query Statement and get the result set of ResultSet type.
The ResultSet interface is implemented by the database manufacturer and encapsulates the result set of database operation in the form of logical table.
The ResultSet object maintains a cursor pointing to the current data row. The cursor starts from 1 by default and can be moved to the next row through the next method.
After use, you need to manually close the ResultSet object.
Use examples:
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from student");
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString("address"));
}5.6 ResultSetMetaData interface
The ResultSetMetaData object can be used to obtain the type and property information of columns in the ResultSet object.
Use examples:
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from student");
while (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getColumnName(1) + "=" + rs.getInt("id") + "\t" + rsmd.getColumnName(2) + "=" + rs.getString(2));
}