catalogue
2, Working principle and advantages of JDBC
2. Create operation command Statement
3. Use the operation command to execute SQL
4, JDBC common interfaces and classes
1, What is JDBC
- JDBC, Java Database Connectivity
- Is a Java API for executing SQL statements. It is a database connection specification in Java
- This API consists of Java sql.*, javax. sql.* Package consists of some classes and interfaces
- API provides a standard API for Java developers to operate databases, which can provide unified access to a variety of relational databases
2, Working principle and advantages of JDBC
JDBC provides a unified access mode for a variety of relational databases. As a high-level abstraction of database access API for specific manufacturers, it mainly contains some general interface classes
JDBC advantages:
- The operation of accessing database in Java language is completely oriented to abstract interface programming
- The development of database applications is not limited to the API of specific database manufacturers
- The portability of the program is greatly enhanced
3, JDBC Usage Summary
1. Create database Connection
Connection connection = DriverManager.getConnection("URL");
MySQL Data connected URL The parameter format is as follows:
jdbc:mysql://Server address: port / database name? Parameter name = parameter value2. Create operation command Statement
Statement statement = connection.createStatement();
3. Use the operation command to execute SQL
ResultSet resultSet= statement.executeQuery
("select id, sn, name, qq_mail, classes_id from student");4. Process ResultSet
while (r.next()) {
int id = r.getInt("id");
int sn = r.getInt("sn");
String name = r.getString("name");
String qqMail = r.getString("qq_mail");
int classesId = r.getInt("classes_id");
System.out.printf("id=%s,sn=%s,name=%s,qqMail=%s,classesId=%s\n",
id, sn, name, qqMail, classesId);
}5. Release resources
if(r != null){
r.close();
}
if(s != null){
s.close();
}
if(conn != null){
conn.close();
}Specific implementation code:
package com.bit.jdbc;
import java.sql.*;
public class QueryTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = null;
Statement s = null;
ResultSet r = null;
try {
//Load a class by reflection (class loading: execute static variables and static code blocks)
//The database driver package can perform the corresponding initialization (driver) under this operation
Class.forName("com.mysql.jdbc.Driver");
//Get database Connection: the Connection interface needs to be in jdbc, not in mysql
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java43?user=root&password=123456&useUnicode=true&characterEncoding=UTF-8&useSSL=false");
System.out.println(conn);
//Create the operation command object Statement (using the in jdbc) through the connection object, which is an abstract object for operating sql
s = conn.createStatement();
//Query:
// (1) Call the executeQuery(sql) of the Statement operation command object
// (2) Returns a ResultSet result set object (the result set of query sql execution)
r = s.executeQuery("select id,sn,name,qq_mail,classes_id from student where id>3");
//Processing result set: the result set may be multiple rows of data, which needs to be traversed to obtain
// Call next to move to the next row. If you return true, it means that the row has data, and if you return false, it means that the row has no data
while (r.next()) {//Go all the way to the end
//Enter the loop, representing a row of data traversed by the operation
int id = r.getInt("id");
String sn = r.getString("sn");
String name = r.getString("name");
String qqMail = r.getString("qq_mail");
int classesId = r.getInt("classes_id");
System.out.printf("id=%s, sn=%s, name=%s, qqMail=%s, classesId=%s\n",
id, sn, name, qqMail, classesId);
}
}finally {//In any case, release resources
//Free resources:
//(1) In any case (abnormal)
//(2) Release in reverse order of creation (result set object, operation command object, database connection object)
if(r != null)//When an exception occurs, the object may not have been assigned (initialized), and a null pointer exception will occur when calling close
r.close();
if(s != null)
s.close();
if(conn != null)
conn.close();
}
}
}4, JDBC common interfaces and classes
1.JDBC API
In Java JDBC programming, database operations are handled in a unified manner using the JDK's own API, which is usually completely decoupled from the driver class of a specific database. Therefore, you can master Java database programming by mastering Java JDBC API (under java.sql package)
2. Database Connection
The Connection interface implementation class is provided by the database. There are usually two ways to obtain the Connection
- One is obtained through the static method of DriverManager (driver management class):
// Load JDBC Driver
Class.forName("com.mysql.jdbc.Driver");
// Create database connection
Connection connection = DriverManager.getConnection(url);- One is obtained through the DataSource object. The DataSource object will be used in practical application
DataSource ds = new MysqlDataSource();
((MysqlDataSource) ds).setURL("jdbc:mysql://localhost:3306/ttl");
((MysqlDataSource) ds).setUser("root");
((MysqlDataSource) ds).setPassword("111111");
((MysqlDataSource) ds).setUseUnicode(true);
((MysqlDataSource) ds).setCharacterEncoding("UTF-8");
((MysqlDataSource) ds).setUseSSL(false);Differences between the above two methods:
- The Connection obtained by the DriverManager class cannot be reused. Each time you release resources after use, you can use Connection Close() closes the physical Connection.
- DataSource provides support for connection pooling. When the connection pool is initialized, a certain number of database connections will be created. These connections can be reused. After using the database connection each time, release the resources and call connection Close() is to recycle the connection object of the connection
3.Statement object
Three types:
- Statement simple operation command object
- PrepareStatement command object for precompile operation
- Operation command of CallableStatement stored procedure (that is, write a piece of code with variables, judgments and loops)
Advantages of precompiled commands over the original simple operation commands (advantages):
- Prevent SQL injection, that is, when the string replaces the placeholder, the single quotation mark will be escaped (the single quotation mark in the replaced string will be regarded as a part of the string)
- Precompiled, high efficiency (except for placeholders, all other places are the same. Precompiled in advance can improve efficiency)
4.ResultSet object
- ResultSet object, which is called result set, represents all rows that meet the conditions of SQL statement, and provides access to the data in these rows through a set of getXXX methods
- The data in the ResultSet is arranged row by row. Each row has multiple fields and a record pointer. The data row referred to by the pointer is called the current data row. We can only operate the current data row. If we want to get a record, we should use the next() method of the ResultSet. If we want to get all the records in the ResultSet, we should use the while loop
5. Code example
package com.bit.jdbc;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.*;
public class AdvanceQueryTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Connection conn = null;
Statement s = null;
ResultSet r = null;
try {
//First create a database connection pool, and then obtain the database connection object through the connection pool
DataSource ds = new MysqlDataSource();
//Create database connection pool: during initialization, a certain number of database connections will be created. These connection objects can be reused with higher efficiency
//The whole url with parameters can only use setURL method, or set the parameters by calling method
((MysqlDataSource) ds).setURL("jdbc:mysql://localhost:3306/java43");
((MysqlDataSource) ds).setUser("root");
((MysqlDataSource) ds).setPassword("123456");
((MysqlDataSource) ds).setUseUnicode(true);
((MysqlDataSource) ds).setCharacterEncoding("UTF-8");
((MysqlDataSource) ds).setUseSSL(false);
conn = ds.getConnection();
//To solve the above security problems, you need to adjust the above operation command object to PreparedStatement
String queryName = "skdjsj' or '1'='1";
int queryId = 6;
//Prepare a belt? Placeholder sql
String sql = "select id,sn,name,qq_mail,classes_id from student where name=? or id=?";
PreparedStatement ps = conn.prepareStatement(sql);//Create precompiled action command objects
//Replace placeholders: call setXXX method. The first parameter represents the placeholders (starting from 1) and the second parameter represents the value to be replaced
ps.setString(1, queryName);//The setXXX method is called for the type of value to be replaced
ps.setInt(2, queryId);
//To execute sql, you need to use a parameterless method
r = ps.executeQuery();
//Processing result set: the result set may be multiple rows of data, which needs to be traversed to obtain
// Call next to move to the next row. If you return true, it means that the row has data, and if you return false, it means that the row has no data
while (r.next()) {//Go all the way to the end
//Enter the loop, representing a row of data traversed by the operation
int id = r.getInt("id");
String sn = r.getString("sn");
String name = r.getString("name");
String qqMail = r.getString("qq_mail");
int classesId = r.getInt("classes_id");
System.out.printf("id=%s, sn=%s, name=%s, qqMail=%s, classesId=%s\n",
id, sn, name, qqMail, classesId);
}
}finally {//In any case, release resources
//Free resources:
//(1) In any case (abnormal)
//(2) Release in reverse order of creation (result set object, operation command object, database connection object)
if(r != null)//When an exception occurs, the object may not have been assigned (initialized), and a null pointer exception will occur when calling close
r.close();
if(s != null)
s.close();
if(conn != null)
conn.close();
}
}
}