1. Method of adding, deleting, modifying and querying JDBC
1, Statement: an object used to execute a static SQL statement and return the results generated by it (if there are disadvantages, generally do not use it, just understand it). The disadvantages do not completely represent errors, but are a little troublesome or strange
Disadvantage 1: need to spell sql statement
Drawback 2: there is a problem with SQL injection (this is a real error! There is a problem, is it a vulnerability?)
SQL injection is a method of using the SQL Engine of the system to complete malicious behavior by injecting illegal SQL statement segments or commands (such as: SELECT user,password FROM user_table WHERE user = 'a' OR1='AND password = 'or't' = '1') into the user input data without sufficient inspection of the user input data.
user and passwrod are variables
String sql = "SELECT user,password FROM user_table WHERE user = '"+ user +"' AND password = '"+ password +"';
2, Prepatedstatement: the SQL statement is precompiled and stored in this object, which can be used to execute the statement multiple times efficiently.
Customize the general connection data method and close resource method
package JDBCUtils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
*
* @Description Tool class for operating database
* @author shkstart Email:2462920991@qq.com
* @version
* @date 2021 April 10, 2014 4:44:35 PM
*
*/
public class JDBCUtils {
public static Connection getConnection() throws Exception {
//Read 4 basic information of configuration file
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//2. Load drive
Class.forName(driverClass);
//3. Get connection
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
*
* @Description Close connection and Statement
* @author shkstart
* @date 2021 April 10, 2014 4:50:17 PM
* @param conn
* @param ps
*/
public static void closeResource(Connection conn,Statement ps) {
try {
if(ps != null) {
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
1, General addition, deletion and modification of database
Delete operation
//Delete operation
@Test
public void testCommonUpdate() {
String sql = "delete from customers where id = ?";
update(sql,3);
}
//General addition, deletion and modification operations
@Test
public void update(String sql,Object ...args){//The number of placeholders in sql is the same as the length of variable parameters!
//1. Get the connection to the database
Connection conn = null;
//2. Precompile the sql statement and return the instance of PreparedStatement
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
//3. Fill placeholder
for(int i = 0;i < args.length;i++) {
ps.setObject(1 + 1, args[i]);//Beware of parameter declaration error!
}
//4. Implementation
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//5. Close resources
JDBCUtils.closeResource(conn, ps);
}
}
Modify operation
@Test
public void testCommonUpdate2() {
String sql = "update `order` set order_name = ? where order_id = ?";
update(sql,"DD","2");
}
Query method
package preparedstatement Add, delete, modify and check;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
import JDBCUtils.JDBCUtils;
import List.Customer;
import List.Order;
public class PrepareStatementQueryTest {
@Test
public void testGetForList() throws Exception {
String sql = "select id,name,email from customers where id < ?";
List<Customer> list = getForList(Customer.class,sql,12);
list.forEach(System.out::println);
System.out.println("=============================================");
String sql1 = "select order_id orderId,order_name orderName from `order` where order_id < ?";
List<Order> orderList = getForList(Order.class, sql1, 5);
orderList.forEach(System.out::println);
}
/**
*
* @Description General query method for different tables
* @author shkstart
* @date 2021 April 11, 2013 3:12:51 PM
* @throws Exception
*/
public <T> List<T> getForList(Class<T> clazz,String sql,Object...args){
Connection conn = null;
PreparedStatement ps = null;
//Execute, get result set
ResultSet rs = null;
//Create collection object
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//Get metadata of result set
ResultSetMetaData rsmd = rs.getMetaData();
//Get the number of columns
int columnCount = rsmd.getColumnCount();
ArrayList<T> list = new ArrayList<T>();
while(rs.next()) {
T t = clazz.newInstance();
//Process each column in a row of data in the result set: assign a value to the attribute specified by the t object
for(int i = 0;i < columnCount;i++) {
//Get the value of the column by ResultSet
Object columValue = rs.getObject(i + 1);
//Get column name
// String columnName = rsmd.getColumnName(i + 1);
//Get alias of column getColumnLabel()
String columnLabel = rsmd.getColumnLabel(i + 1);
//Through reflection, assign the attribute named culumName to the t object with the specified value columnValue
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
@Test
public void testGetInstance() throws Exception {
String sql = "select id,name,email from customers where id = ?";
Customer customer = getInstance(Customer.class, sql, 12);
System.out.println(customer);
}
/**
*
* @Description The general query operation for different tables returns a record in the table
* @author shkstart
* @date 2021 April 11, 2013 3:12:10 PM
* @param <T>
* @param clazz
* @param sql
* @param args
* @return
* @throws Exception
*/
public <T> T getInstance(Class<T> clazz,String sql,Object...args) throws Exception{
Connection conn = JDBCUtils.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
//Execute, get result set
ResultSet rs = ps.executeQuery();
//Get metadata of result set
ResultSetMetaData rsmd = rs.getMetaData();
//Get the number of columns
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
T t = clazz.newInstance();
for(int i = 0;i < columnCount;i++) {
//Get the column value of the column through ResultSet
Object columValue = rs.getObject(i + 1);
//Get column name
// String columnName = rsmd.getColumnName(i + 1);
//Get alias of column getColumnLabel()
String columnLabel = rsmd.getColumnLabel(i + 1);
//Through reflection, assign the attribute named culumName to the t object with the specified value columnValue
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
JDBCUtils.closeResource(conn, ps, rs);
return null;
}
}
I think we only need to remember the above two general query methods
The above codes need to be wrapped with try catch. In order to facilitate viewing and understanding, you can directly use throws
2. MySQL Bolb type and its operation
Because the default storage file size of Blob is 1M, if we need to insert more than 1M pictures, we need to modify the configuration
Batch insert data operation
package insert Blob Type data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import JDBCUtils.JDBCUtils;
/*
* Using PreparedStatement to realize batch data operation
*
* update,delete It has the effect of batch operation
* Batch operation at this time mainly refers to batch insertion. How to use PreparedStatement to achieve more efficient batch insertion?
*
* Title: insert 200 day data into the goods table
* CREATE TABLE goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(25)
);
*
*/
public class InsertTest {
@Test
public void testInsert1() throws Exception {
Connection conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i = 1;i <= 20000;i++) {
ps.setObject(1, "name_" + i);
ps.execute();
}
long end = System.currentTimeMillis();
System.out.println("Time spent:" + (end - start));//13634ms
JDBCUtils.closeResource(conn, ps);
}
/*
* Batch insertion method 3:
1.addBatch(),executeBatch( ).clearBatch()
2.mysql The server turns off batch processing by default. We need a parameter to let mysql turn on batch processing support.
?rewriteBatchedStatements=true Write it after the url of the configuration file
3.Use the updated MySQL driver: mysql-connector-java-5.1.37-bin jarl
*/
@Test
public void testInsert2() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i = 1;i <= 20000;i++) {
ps.setObject(1, "name_" + i);
//1. "Save" sql
ps.addBatch();
if(i % 500 == 0) {
//2. Execute batch
ps.executeBatch();
//3. Empty batch
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("Time spent:" + (end - start));//13634ms
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, ps);
}
}
//Method 4 of batch insertion: set the connection not to allow automatic submission of data
@Test
public void testInsert3() throws Exception {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
//Setting does not allow automatic submission of data
conn.setAutoCommit(false);
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
long start = System.currentTimeMillis();
for(int i = 1;i <= 2000000;i++) {
ps.setObject(1, "name_" + i);
//1. "Save" sql
ps.addBatch();
if(i % 500 == 0) {
//2. Execute batch
ps.executeBatch();
//3. Empty batch
ps.clearBatch();
}
}
//Submit data
conn.commit();
long end = System.currentTimeMillis();
System.out.println("Time spent:" + (end - start));//13634ms
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, ps);
}
}
}
2.1 differences between Preparedstatement and Statement
1. Code readability and maintainability. PreparedStatement is a sub interface of Statement, which is defined by sun, not a third-party api.
2.PreparedStatement can maximize performance:
3.DBServer will provide performance optimization for precompiled statements. Because the precompiled statement may be called repeatedly, the execution code of the statement compiled by the DBServer compiler is cached. As long as it is the same precompiled statement in the next call, it does not need to be compiled. As long as the parameters are directly passed into the compiled statement execution code, it will be executed.
4. In the statement statement statement, even if it is the same operation, because the data content is different, the whole statement itself cannot match, and there is no meaning of caching the statement The fact is that no database caches the compiled execution code of ordinary statements. In this way, the incoming statement should be compiled every time it is executed.
5. (syntax check, semantic check, translation into binary commands, cache) PreparedStatement can prevent SQL injection
JDBC transaction processing
1. Once the data is submitted, it cannot be rolled back·
2. When does data mean submission?
2.1 When a connection object is created, the transaction is automatically committed by default: each time an SQL statement is executed, if the execution is successful, it will be automatically committed to the database without rollback.
2.2 close the database connection and the data will be submitted automatically. If there are multiple operations, and each operation uses its own separate connection, the transaction cannot be guaranteed. That is, multiple operations of the same transaction must be under the same connection.
In order to execute SQL statements as one or more JDBC programs:
3.1 call setAutoCommit(false) of Connection object; To cancel the auto commit transaction.
3.2 after all SQL statements have been successfully executed, call commit(); Method commit transaction
3.3 when an exception occurs, call rollback(); Method rolls back the transaction
If the Connection is not closed at this time and may be reused, you need to restore its automatic submission state
setAutoCommit(true). Especially when using database connection pool technology, it is recommended to restore the automatic submission state before executing the close() method.
package Database transaction;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.junit.Test;
import JDBCUtils.JDBCUtils;
/*
* 1.What is database transaction?
Transaction: a group of logical operation units that transform data from one state to another.
>—Group logical operation unit: one or more DML operations.
2.Principle of transaction processing: ensure that all transactions are executed as a work unit. Even if there is a failure, this execution mode cannot be changed.
When multiple operations are performed in a transaction, or all transactions are committed, the changes are permanently saved
Come down; Or the database management system will abandon all modifications made and roll back the whole transaction to the original state.
3.Once the data is submitted, it cannot be rolled back
4.What actions will lead to automatic submission of data?
>DDL Once the operation is executed, it will be submitted automatically.
>set autocommit = false Failure of DDL operation
>DML By default, once executed, it will be submitted automatically.
>We can cancel the automatic submission of DML operations by setting autocommit = false.
>By default, data will be submitted automatically when the connection is closed
*/
public class TransactionTest {
@Test
public void testUpdateWithTx() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
System.out.println(conn.getAutoCommit());
//Cancel automatic submission of data
conn.setAutoCommit(false);
String sql1 = "update user_table set balance = balance - 100 where user = 'AA'";
update(conn,sql1,"AA");
//Analog network exception
System.out.println(10 / 0);
String sql2 = "update user_table set balance = balance + 100 where user = 'BB'";
update(conn, sql2, "BB");
System.out.println("Transfer succeeded");
//Submit data
conn.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
//Undo Data
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
JDBCUtils.closeResource(conn, null);
}
//General addition, deletion and modification operations (considering transactions)
public int update(Connection conn,String sql,Object...args) throws Exception {
PreparedStatement ps = null;
try {
//1. Precompile the sql statement and return the instance of PreparedStatement
ps = conn.prepareStatement(sql);
//2. Fill placeholder
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
//3. Implementation
return ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//4. Resource shutdown
JDBCUtils.closeResource(null, ps);
}
return 0;
}
}
6.2 ACID attribute of transaction
1. Atomicity atomicity refers to that a transaction is an indivisible work unit, and the operations in the transaction either occur or do not occur.
2. Consistency transaction must change the database from one consistency state to another.
3. Isolation: transaction isolation means that the execution of a transaction cannot be disturbed by other transactions, that is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.
4. Durability: once a transaction is committed, its changes to the data in the database are permanent, and other subsequent operations and database failures should not have any impact on it.
6.3.1 concurrency of database
1. For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, various concurrency problems will be caused:
1.1 dirty read: for two transactions T1 and T2, T1 reads the fields that have been updated by T2 but have not been committed. Then, if T2 rolls back, the content read by T1 is temporary and invalid.
1.2 non repeatable reading: for two transactions T1 and T2, T1 reads a field, and then T2 updates the field. After that, T1 reads the same field again, and the value is different.
1.3 phantom reading: for two transactions T1 and T2, T1 reads a field from a table, and then T2 inserts some new rows into the table. After that, if T1 reads the same table again, several more rows will appear.
2. Isolation of database transactions: the database system must have the ability to isolate and run various transactions concurrently, so that they will not affect each other and avoid various concurrency problems.
3. The degree to which a transaction is isolated from other transactions is called isolation level. The database specifies a variety of transaction isolation levels. Different isolation levels correspond to different interference levels. The higher the isolation level, the better the data consistency, but the weaker the concurrency.
6.3.2 four isolation levels
Four transaction isolation levels provided by the database:
Generally, in writing code, a setAutoCommit(false) is given before the sql statement is executed; Cancel the automatic commit transaction, and then give a commit() after the sql statement is executed; Method to submit data, so that when an exception occurs, you can call rollback(); Method to roll back the transaction.
General method of BaseDAO
package baseDAO;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import JDBCUtils.JDBCUtils;
/*
* Encapsulates common operations for data tables
*/
public abstract class BaseDAO {
// General addition, deletion and modification operations (considering transactions)
public int update(Connection conn, String sql, Object... args){
PreparedStatement ps = null;
try {
// 1. Precompile the sql statement and return the instance of PreparedStatement
ps = conn.prepareStatement(sql);
// 2. Fill placeholder
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 3. Implementation
return ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 4. Resource shutdown
JDBCUtils.closeResource(null, ps);
}
return 0;
}
// An operation that returns a generic query object
public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {
PreparedStatement ps = null;
// Execute, get result set
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// Get metadata of result set
ResultSetMetaData rsmd = rs.getMetaData();
// Get the number of columns
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// Get the column value of the column through the ResultSet
Object columValue = rs.getObject(i + 1);
// Get column name
// String columnName = rsmd.getColumnName(i + 1);
// Get alias of column getColumnLabel()
String columnLabel = rsmd.getColumnLabel(i + 1);
// Through reflection, assign the attribute named culumName to the t object with the specified value columnValue
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
//Returns a generic query for multiple objects
public <T> List<T> getForList(Connection conn,Class<T> clazz, String sql, Object... args) {
PreparedStatement ps = null;
// Execute, get result set
ResultSet rs = null;
// Create collection object
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// Get metadata of result set
ResultSetMetaData rsmd = rs.getMetaData();
// Get the number of columns
int columnCount = rsmd.getColumnCount();
ArrayList<T> list = new ArrayList<T>();
while (rs.next()) {
T t = clazz.newInstance();
// Process each column in a row of data in the result set: assign a value to the attribute specified by the t object
for (int i = 0; i < columnCount; i++) {
// Get the column value of the column through the ResultSet
Object columValue = rs.getObject(i + 1);
// Get column name
// String columnName = rsmd.getColumnName(i + 1);
// Get alias of column getColumnLabel()
String columnLabel = rsmd.getColumnLabel(i + 1);
// Through reflection, assign the attribute named culumName to the t object with the specified value columnValue
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
//Used to query special values
public <E> E getValue(Connection conn,String sql,Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
if(rs.next()) {
return (E) rs.getObject(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
}
Database connection pool
The existence of database connection pool is to solve the problem of database connection in traditional development
Traditional data connectivity issues:
1. Ordinary JDBC database connections are obtained using DriverManager. Each time a Connection is established to the database, the Connection must be loaded into memory, and then the user name and password must be verified (it takes 0.05s~1s). When you need a database Connection, ask for one from the database and disconnect it after execution. This way will consume the resources and time of the big star. The Connection resources of the database have not been well reused. If hundreds or even thousands of people are online at the same time, frequent database Connection operation will occupy a lot of system resources, and even cause server crash.
2. For each database connection, it must be disconnected after use. Otherwise, if the program fails to close due to exceptions, it will lead to memory leakage in the database system and eventually restart the database. (recall: what is Java's memory leak?)
3. This development cannot control the number of connection objects created, and system resources will be allocated without consideration. If there are too many connections, it may also lead to memory leakage and server crash.
Database connection pool technology
1. In order to solve the problem of database connection in traditional development, database connection pool technology can be used.
2. The basic idea of database connection pool is to establish a buffer pool for database connections. Put a certain number of connections in the buffer pool in advance. When you need to establish a database connection, just take one out of the buffer pool and put it back after use.
3. Database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re establishing one.
4. During initialization, the database connection pool will create a certain number of database connections into the connection pool. The number of these database connections is set by the minimum number of database connections. No matter whether these database connections are used or not, the connection pool will always ensure that there are at least so many connections. The maximum number of database connections in the connection pool limits the maximum number of connections that can be occupied by the connection pool. When the number of connections requested by the application from the connection pool exceeds the maximum connection efficiency, these requests will be added to the waiting queue.
There are several types of database connection pools:
1. dbcp is a database connection pool provided by Apache. The tomcat server has its own dbcp database connection pool. The speed is relatively c3p0 fast, but due to its own BUG,
Hibernate 3 has provided support.
2. C3P0 is a database connection pool provided by an open source organization, which is relatively slow and stable. hibernate official recommendation 3. Proxool
It is an open source project database connection pool under sourceforge. It has the function of monitoring the status of the connection pool, and its stability is a little worse than c3p0
4. BoneCP is a database connection pool provided by an open source organization with high speed
5. Druid is a database connection pool provided by Alibaba. It is said to be a database connection pool integrating the advantages of DBCP, C3P0 and Proxool. However, it is uncertain whether it is faster than BoneCP
Now the mainstream of development is Druid, so we don't enumerate the code one by one, just write the code of Druid
Druid's properites file configuration
url=jdbc:mysql:///test username=root password=123 driverClassName=com.mysql.jdbc.Driver initialSize=10 maxActive=10
/**
* Using Druid database connection pool technology
*/
private static DataSource source1;
static {
try {
Properties pros = new Properties();
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
pros.load(is);
source1 = DruidDataSourceFactory.createDataSource(pros);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection3() throws Exception {
Connection conn = source1.getConnection();
return conn;
}
OK, write here first and update later if necessary!
Learning from station B Shangsi Valley