JDBC,
1. What is JDBC?
JDBC is Java DataBase Connectivity, a standard specification for Accessing databases in Java. In essence, it is a set of interfaces developed by SUN Company, which is located in the JAVa.sql package of JDK.
The specific implementation classes of JDBC interface are implemented by various database vendors, and these implementation classes are called database drivers. In practice, it is necessary to import the corresponding driver JAR package according to the DBMS used in the project. For example, if the MySQL database is used in the project, you need to import the MySQL driver JAR package.
1.1 Benefits of USING JDBC
Interface oriented programming, the programmer only needs to be able to call methods in the JDBC interface, not the implementation of the class.
1.2 Why did SUN develop a JDBC interface?
Because the underlying implementation principle of each database is different.
2, JDBCSome preparatory work before development
2.1. Use text compiler for development
Download the corresponding driver JAR package from the database’s official website and configure it into the environment variable classpath.
For example, if the MySQL database is used in the project, download the MySQL driver from the official website and configure environment variables as follows:
classpath=.; D: \ course 06 - JDBC \ resources \ \ MySql Connector Java 5.1.23 \ MySql Connector - Java - 5.1.23 - bin. The jarCopy the code2.2 Development using IDEA
You do not need to configure the above environment variables when using IDEA.
3. A six-step introduction to JDBC programming
Step 1: Register the driver (for this purpose: to tell the Java program which brand of database to connect to)
Step 2: Get the connection (indicates that the channel between the JVM process and the database process is open, this is a process communication, heavyweight, must be closed after use channel.)
Step 3: Get the database action object (the object that specifically executes SQL statements)
Step 4: Execute SQL statement (DQL DML….)
Step 5: Process the query result set (this step is only available if step 4 executes a SELECT statement.)
Step 6: Release resources (Always close resources when you are done using them. Java and database are interprocess communication, and must be turned off once enabled.
4, JDBC programming six steps in detail
4.1 register the driver
4.1.1. The first method
Driver driver = new com.mysql.jdbc.Driver();// Polymorphic, parent type reference points to child type object (MySQL driver)
//Driver driver = new oracle.jdbc.driver.OracleDriver(); / / Oracle driver
DriverManager.registerDriver(driver);
// Two lines of code can be merged into:
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Copy the codeWhere Driver is a JDBC interface in the JAVa. SQL package in the JDK. The com.mysql.jdbc.Driver class (mysql Driver) implements the Driver interface. You can download the corresponding JAR package from the mysql official website and configure it into the IDEA module.
4.1.2 The second way (common)
Class.forName("com.mysql.jdbc.Driver");// Use reflection mechanism
Copy the codeSo why is the second method more common?
We can from com.mysql.jdbc.driver source code analysis, its source code is as follows:
package com.mysql.jdbc;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver(a) throws SQLException {}static { Class. ForName (" full Class name ") completes the Class loading action
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!"); }}}Copy the codeAs you can see, the action to register the driver is in a static code block, so only its classloading timing is needed. In addition, the class.forname () method takes a string that can be written to the properties property configuration file, and we don’t need the return value of the method. In this way, the driver can be registered by initializing static code blocks using classloading actions.
4.2. Obtaining a connection
Obtaining a connection requires a URL, database username, and password.
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/db"."root"."123456");
Copy the code2, URL
A URL is a uniform resource locator (the absolute path to a resource on a network). For example: www.baidu.com/ this is a URL.
The URL consists of four parts: protocol, IP, port number, and resource name
4.2.2 Database user name and password
The database user name and password can be set in the corresponding DBMS.
4.3. Obtain database operation objects
A database action object, Statement, is an object that specifically executes SQL statements.
You can obtain the database action object Statement through the database connection object.
stmt = conn.createStatement();
Copy the code4.4. Execute SQL statements
4.4.1. Execute the DQL statement
String sql = "select empno as a,ename,sal from emp";
rs = stmt.executeQuery(sql);
Copy the codeThe executeQuery(select) method in Statement is the same as the executeQuery(select) method in Statement
ResultSet executeQuery(String sql) throws SQLException;
Copy the codeWhere ResultSet is a JDBC interface under the java.sql package in the JDK.
4.4.2 Execute DML statements
Execute DML statements using the executeUpdate method of Statement.
String sql = "Insert into dept(deptno, loc) values(50,' deptno ',' guangzhou ')";
int count = stmt.executeUpdate(sql);
Copy the codeNote that the executeUpdate(insert/delete/update) method in Statement is:
int executeUpdate(String sql) throws SQLException;
Copy the codeThe return value of this method is “affect the number of records in the database”
4.5. Process the query result set
The query result set needs to be processed only if a DQL statement is executed in step 4.
There are four main methods: subscript fetching, column name fetching, subscript fetching of specified type, column name fetching
while(rs.next()){
// The first type, subscript fetch
/* String empno = rs.getString(1); // All subscripts in JDBC start at 1, not 0. String ename = rs.getString(2); String sal = rs.getString(3); System.out.println(empno + "," + ename + "," + sal); * /,// The second way is to get the column name instead of the index of the column
/* String empno = rs.getString("a"); String ename = rs.getString("ename"); String sal = rs.getString("sal"); System.out.println(empno + "," + ename + "," + sal); * /
// The third type can be retrieved as a specific type in addition to the String type
/* int empno = rs.getInt(1); String ename = rs.getString(2); double sal = rs.getDouble(3); System.out.println(empno + "," + ename + "," + (sal + 100)); * /
/ / the fourth
int empno = rs.getInt("a");
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(empno + "," + ename + "," + (sal + 200));
}
Copy the code4.6. Release resources
Be sure to close resources when you are done using them. Java and database are interprocess communication, which must be turned off once enabled.
To ensure that the resource is always released, close the resource ina finally block, and follow a sequence of finally statements, each with a try.. The catch.
4.6.1 How to release resources when EXECUTING DML statements?
When executing DML statements, close the Statement database operation object first and then the Connection database Connection object.
finally {
if(stmt ! =null) {try {
stmt.close();
} catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}}Copy the code4.6.2 How to Release Resources when EXECUTING DQL Statements?
When a DQL Statement is executed, close the ResultSet query object, the Statement database operation object, and the Connection database Connection object.
finally {
//6. Release resources
if(rs ! =null) {try {
rs.close();
} catch(SQLException e) { e.printStackTrace(); }}if(stmt ! =null) {try {
stmt.close();
} catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {try {
conn.close();
} catch(SQLException e) { e.printStackTrace(); }}}Copy the code5. What is SQL injection?
For example, on the login page of a website, enter the following user name and password:
Username: Feng Password: Feng' or '1'='1
Copy the codeIf a successful login is displayed at this point, SQL injection has occurred
5.1 Root cause of SQL injection
The information entered by users contains keywords of SQL statements, and these keywords are involved in the compilation process of SQL statements. As a result, the original intention of SQL statements is distorted, thus achieving SQL injection. (This can be seen through the debug program)
5.2. How to solve SQL injection
SQL injection can be resolved using preparedStatements.
// get the precompiled database operation object
// Block the SQL statement. Among them? Represents a placeholder, a? To receive a value
String sql = "select * from t_user where loginName = ? and loginPwd = ?";
// At this point, the program sends the SQL block to the DBMS, which then precompiles the SQL statement.
ps = conn.prepareStatement(sql);
// Give placeholder? Pass values (the first question mark subscript is 1, the second question mark subscript is 2, and all subscripts in JDBC start at 1.)
ps.setString(1,loginName);
ps.setString(2,loginPwd);
Copy the code5.3. Principle of PreparedStatement
The SQL injection problem can be solved as long as the user-supplied information does not participate in the compilation process of the SQL statement. Or if the information provided by the user contains the keyword of the SQL statement, but the user did not participate in the compilation, the keyword does not take effect, and THE SQL injection can be resolved. To the user information is not involved in the SQL Statement compilation, then you have to use Java.. SQL PreparedStatement, PreparedStatement interface inherits the Java.. SQL Statement, A PreparedStatement is a database operation object that belongs to a pre-compiled database. The principle of a PreparedStatement is to compile the SQL statement framework in advance, and then pass values to the SQL statement.
5.4. Differences between Statement and PreparedStatement
1. PreparedStatement resolves the SQL injection problem.
Statement is compiled and executed once. PreparedStatement is compiled once and can be executed N times. PreparedStatement is more efficient.
PreparedStatement will perform type security checks during compilation.
To sum up, preparedStatements are often used, and are only needed in rare cases. If SQL injection is required and SQL Statement concatenation is required, Statement is used.
6. JDBC transactions
The default is automatic submission. Automatic commit: Commits automatically as long as any DML statement is executed.
However, in the actual business, N DML statements are usually combined to complete the task, and they must be guaranteed to succeed or fail simultaneously in the same transaction. In this case, you need to change the JDBC transaction to manual commit.
6.1. Change JDBC transactions to manual commit
You can change this to manual commit using conn.setautoCommit (false).
Such as:
public class JDBCTest11 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1. Register driver
Class.forName("com.mysql.jdbc.Driver");
// get the connection
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db"."root"."123456");
// Change the automatic commit mechanism to manual commit
conn.setAutoCommit(false);// Start the transaction
// get the precompiled database operation object
String sql = "update t_act set balance = ? where actno = ?";
ps = conn.prepareStatement(sql);
/ / to? The value of
ps.setDouble(1.10000);
ps.setInt(2.111);
int count = ps.executeUpdate();Execute the first UPDATE statement
System.out.println(count);
String s = null;
s.toString();
/ / to? The value of
ps.setDouble(1.10000);
ps.setInt(2.222);
count += ps.executeUpdate();
System.out.println(count == 2 ? "Transfer successful" : "Transfer failed");
// The program can go to this point, indicating that there is no exception above program, transaction end, manual commit data
conn.commit();// Commit the transaction
} catch (Exception e) {
// Rollback the transaction
if(conn ! =null) {try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
e.printStackTrace();
}finally{
//6. Release resources
if(ps ! =null) {try {
ps.close();
} catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
Copy the code7, fuzzy query
// The correct way to write (fuzzy query)
String sql = "select ename from emp where ename like ?";
ps = conn.prepareStatement(sql);
ps.setString(1."_A%");
rs = ps.executeQuery();
while(rs.next()){
System.out.println(rs.getString("ename"));
}
Copy the code8. Pessimistic lock and optimistic lock
8.1 Pessimistic Locking (Row-level Locking)
Transactions must be queued to execute. Data is locked and concurrency is not allowed.
Use method: add for update after select
8.2. Optimistic Locking
Concurrency is supported and transactions do not need to be queued, only a version number is required.