catalogue
Blob type field
Overview: in MySQL, Blob is a large binary object. It is a container that can store a large amount of data. It can accommodate data of different sizes.
Note: PreparedStatement must be used to insert Blob type data, because Blob type data cannot be spliced with string.
Introduction: there are four Blob types of MySQL (they are the same except for the maximum amount of information stored). In actual use, different Blob types are defined according to the size of the data to be stored.
be careful:
- If the stored file is too large, the performance of the database will degrade
- If the error xxx too large is reported after specifying the relevant Blob type, find the my.ini file in the mysql installation directory and add the following configuration parameters: max_allowed_packet=16M. At the same time, note: after modifying the my.ini file, you need to restart the mysql service
Code implementation: insert and query Blob type data
//Insert a Blob type field into the data table customers
@Test
public void testInsert() throws Exception {
//Get connection
Connection conn = JDBCUtils.getConnection();
String sql = "insert into customers(name,email,birth,photo)values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
//Fill placeholder
ps.setObject(1, "Joker Xue");
ps.setObject(2, "xuezq@qq.com");
ps.setObject(3, "1983-7-17");
//Manipulate variables of Blob type
FileInputStream is = new FileInputStream(new File("x.jpg"));
ps.setBlob(4, is);
//implement
ps.execute();
JDBCUtils.closeResource(conn, ps);
}
//Query the Blob type field in the data table customers
@Test
public void testQuery() {
Connection conn = null;
PreparedStatement ps = null;
InputStream is = null;
FileOutputStream fos = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select id,name,email,birth,photo from customers where id=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 22);
//implement
rs = ps.executeQuery();
if (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer cust = new Customer(id, name, email, birth);
System.out.println(cust);
//Download Blob type fields and save them locally as files
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("xzq.jpg");
byte[] buffer = new byte[1024];
int len;
while ((len = is.read(buffer)) != -1) {
fos.write(buffer, 0, len);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (is != null)
is.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
if (fos != null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
JDBCUtils.closeResource(conn, ps, rs);
}
}Batch insert
Overview: when you need to insert or update records in batches, you can use the batch update mechanism of Java, which allows multiple statements to be submitted to the database for batch processing at one time.
JDBC batch processing statements include three methods:
- addBatch(String): add SQL statements or parameters that need batch processing
- executeBatch(): executes batch processing statements
- clearBatch(): clear cached data
There are usually two cases of batch execution of SQL statements:
- Batch processing of multiple SQL statements
- Batch parameter transfer of an SQL statement
Code implementation: insert 20000 pieces of data into the data table
#Provide a goods table in the database. Create the following: CREATE TABLE goods( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) );
@Test
public void testInsert4() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
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);
for (int i = 1; i <= 20000; i++) {
ps.setObject(1, "name_" + i);
//1. Save sql
ps.addBatch();
if (i % 500 == 0) {
//2. Implementation
ps.executeBatch();
//3. Empty the batch
ps.clearBatch();
}
}
//Submit data
conn.commit();
long end = System.currentTimeMillis();
System.out.println("Execution time:" + (end - start)); //Execution time: 703
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}Database transaction
Note: only the transaction processing in JDBC is introduced here. For the basic introduction of database transactions, please refer to the previous articles of the blogger.
Overview: a group of logical operation units that transform data from one state to another. (a group of logical operation units, one or more DML operations)
characteristic:
- Once the data is submitted, it cannot be rolled back
- When will the data be submitted?
- Once the DDL operation is executed, it will be submitted automatically. set autocommit = false will invalidate the DDL operation
- By default, once the DML is executed, it will be submitted automatically. set sutocommit = false to cancel the automatic submission of DML operations
- Close the database connection and the data will be submitted automatically
- When a connection object is created, the transaction is automatically committed by default
In the JDBC program, multiple SQL statements are executed as one transaction:
- Call setAutoCommit(false) of the Connection object to cancel the auto commit transaction
- After all SQL statements have been successfully executed, call commit() to commit the transaction.
- When an exception occurs, the rollback() method is called to roll back the transaction
Code implementation: user AA transfers 100 to user BB
//**********Transfer operation considering database transactions****************
@Test
public void testUpdateWithTx() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
System.out.println(conn.getAutoCommit()); //true
//1. Cancel automatic submission of data
conn.setAutoCommit(false);
String sql1 = "update user_table set balance = balance - 100 where user = ?";
update(conn,sql1,"AA");
//Analog network exception
//System.out.println(10/0);
String sql2 = "update user_table set balance = balance + 100 where user = ?";
update(conn,sql2,"BB");
System.out.println("Transfer succeeded!");
//2. Submission of data
conn.commit();
}catch (Exception e){
e.printStackTrace();
//3. Rollback data
try {
conn.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
finally {
//Modify it to automatically submit data, mainly for the use of database connection pool
try {
conn.setAutoCommit(true);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
JDBCUtils.closeResource(conn,null);
}
}
//General addition, deletion and modification operations - version 2.0 (considering previous transactions)
public int update(Connection conn,String sql,Object ...args) {
PreparedStatement ps = null;
try {
//1. Precompile the sql statement and return the PreparedStatement instance
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){
e.printStackTrace();
}finally {
//4. Resource shutdown
JDBCUtils.closeResource(null, ps);
}
return 0;
}