Interface oriented programming
Programming steps
Get connection
Before that, to obtain the mysql driver, you need to put the corresponding jar package under the project:
Mode 1
import org.junit.Test;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionTest {
@Test
public void test1() throws SQLException {
Driver driver = new com.mysql.jdbc.Driver();
String url = "jdbc:mysql://localhost:3306/test";
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "root");
Connection connection = driver.connect(url, info);
System.out.println(connection);
}
}
com.mysql.jdbc.JDBC4Connection@21213b92
Mode 2 (based on mode 1)
There are third parties in the above way:
- Driver driver = new com.mysql.jdbc.Driver();
For better portability, we can use reflection to dynamically create objects in different databases:
// Iteration of mode 2 to mode 1
@Test
public void test2() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
String url = "jdbc:mysql://localhost:3306/test";
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "root");
Connection connection = driver.connect(url, info);
System.out.println(connection);
}
Method 3: use DriverManager
//Method 3 using DriverManager
@Test
public void test3() throws Exception {
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "root";
DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
Mode 4 (based on mode 3)
//Mode 4 is based on mode 3
@Test
public void test4() throws Exception {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "root";
Class.forName("com.mysql.jdbc.Driver");
// Driver driver = (Driver) clazz.newInstance();
// DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
Why is it possible to comment out those two lines? See the following figure:
Because when the reflection loads the Driver class into memory, it will automatically execute the static code block in the class and automatically help us new the Driver object.
Mode 5 (do not write parameters in hard coding)
Write the configuration information (url,user, etc.) required to connect to the database into the file and connect by reading the file.
@Test
public void test5() throws Exception {
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
Operating and accessing databases
statement
This method has the following disadvantages:
- Splice string required
- sql injection problem
https://www.bilibili.com/video/BV1eJ411c7rf?p=12
PreparedStatement
increase
package preparestatement.java;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Properties;
public class PrepareStatementTest {
@Test
public void test1() throws Exception {
InputStream is = PrepareStatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
// Read parameters
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
// Get connection
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
// Precompile the sql statement and return the instance of prepareStatement
String sql = "insert into customers(name,email,birth)value(?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
// Fill in the placeholder. Note that the index here starts with 1
ps.setString(1, "Qin LAN");
ps.setString(2, "qinlan@gmail.com");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = simpleDateFormat.parse("1979-7-17");
ps.setDate(3, new Date(date.getTime()));
// Execute sql operation
ps.execute();
// close resource
ps.close();
connection.close();
}
}
We can encapsulate the code of creating connection / closing connection into a tool class so that other classes can call:
package utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtils {
public static Connection getConnection() throws Exception{
// InputStream stream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
InputStream stream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(stream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, password, user);
return connection;
}
public static void closeResource(Connection conn, PreparedStatement ps) {
try {
if (conn!=null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
change
@Test
public void test2() {
Connection connection = null;
PreparedStatement ps = null;
try {
// Get database connection
connection = JDBCUtils.getConnection();
// Precompiled statement, returned instance
String sql = "update customers set name = ? where id = ?";
ps = connection.prepareStatement(sql);
// Fill placeholder
ps.setString(1, "Bach");
ps.setInt(2, 18);
// implement
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
// close resource
JDBCUtils.closeResource(connection, ps);
}
}
Abstract the update function
We can encapsulate addition, deletion and modification into one method:
public void update(String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
System.out.println(args[i]);
ps.setObject(i+1, args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps);
}
}
Test:
@Test
public void test3() {
// String sql = "delete from customers where id = ?";
// update(sql, "12");
String sql2 = "update `order` set order_name = ? where order_id = ?";
update(sql2, "QQ", "4");
}
It should be noted here that the order table is a keyword, so writing order directly may report an error. If you want the program to know that you want to update the order table, you should add a backquote like the above code
check
Different from addition, deletion and modification, the query returns results. Therefore, the steps of obtaining connections and sql statements are the same as those of addition, deletion and modification, but the results should be processed.
We create a class to save the found data (only the attributes are written here, and the methods are omitted):
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
}
@Test
public void test1() {
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ps = connection.prepareStatement(sql);
ps.setObject(1, 20);
resultSet = ps.executeQuery();
System.out.println(resultSet);
if (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, resultSet);
}
}
The function of next is to see if there are any elements behind it. If so, the pointer will move down without returning the result.
Note that resultSet also needs to close resources, so add an overloaded method in JDBC utils:
public static void closeResource(Connection conn, PreparedStatement ps, ResultSet result) {
try {
if (conn!=null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
result.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
Make the query operation universal
The above code can only check fixed attributes. Can we write a general method to check several attributes if we want?
// General writing method of query
public Customer query(String sql, Object... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// Acquiring Metadata
ResultSetMetaData metaData = rs.getMetaData();
// Get the number of columns of query results according to metadata
int columnCount = metaData.getColumnCount();
if (rs.next()) {
Customer c = new Customer(); // It's best to write here. If it's written outside if, the object will still be created when rs is empty
for (int i = 0; i < columnCount; i++) {
// Gets the value of each column
Object columnValue = rs.getObject(i + 1);
// Gets the column name of each column
String columnName = metaData.getColumnName(i + 1);
// Assign a value to the column name attribute of the object by using the column name attribute
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true); // Property is private. Don't forget to set permissions
field.set(c, columnValue);
}
return c;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
explain:
- Because we want to check a variable number of attributes, the number of attributes in sql is uncertain
- Let's get the connection first, set the placeholder, and then execute the query
- How do you know how many columns are in the query result? Therefore, it is necessary to obtain the number of columns of the result set (through metadata (that is, the data describing the result information), which has been indicated in the code)
- With the number of columns, we can get the value of each column of the current result through circulation. Because we want to save the query result to the object, we also need to know the attribute name of the current column, which is also obtained through metadata
- Now that you know the column name and value, you need to assign a value to the object. How to assign a value? Through reflection! (marked)
Call:
@Test
public void test2() {
// String sql = "select id, name, birth, email from customers where id = ?";
String sql = "select id, name, email from customers where id = ?";
Customer customer = query(sql, "4");
System.out.println(customer);
}
result:
Customer{id=4, name='Tang Wei', email='tangw@sina.com', birth=null}
When the attribute name in the Java class does not match the attribute name in the database
The above code shows that the attribute names in the Java class and the field names in the database are exactly the same, but sometimes they are not exactly the same. For example, the following table:
We may name the corresponding class as:
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
}
If you still query as before, an error will be reported:
// The attribute name of the Java class is different from that in the database, such as the Order class
public Order orderQuery(String sql, Object... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[0]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
if (rs.next()) {
Order order = new Order();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i+1);
String columnName = metaData.getColumnName(i + 1);
Field field = Order.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
@Test
public void orderTest() {
String sql = "select order_id, order_name, order_date from `order` where order_id = ?";
Order order = orderQuery(sql, 2);
System.out.println(order);
}
result:
This is because the properties in the Java class do not match the field names in the database.
The solution is to use an alias. You can use an alias during sql query to make the alias the same as the attributes in the Java class:
@Test
public void orderTest() {
String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
Order order = orderQuery(sql, 2);
System.out.println(order);
}
However, an error is still reported at this time because metadata Getcolumnname gets the column name instead of the alias. To use getColumnLabel:
The getColumnLabel returns the alias when you give it an alias, and the column name when you don't give it a name.
Summary
Query operation of different tables
The above code can query several attributes of a table. Now I want to query multiple attributes of different tables.
// Query of multiple tables with different attributes
public <T> T queryMulti(Class<T> clazz, String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLable = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
@Test
public void multiTable() {
String sql = "select name, birth, email from customers where id = ?";
Customer customer = queryMulti(Customer.class, sql, 3);
System.out.println(customer);
String sql2 = "select order_id orderId, order_name orderName from `order` where order_id = ?";
Order order = queryMulti(Order.class, sql2, 4);
System.out.println(order);
}
result:
Customer{id=0, name='Lin Zhiling', email='linzl@gmail.com', birth=1984-06-12}
Order{orderId=4, orderName='QQ', orderDate=null}
explain:
- The main change here is to use generics to specify the table name to be queried when calling the query method
- Note the declaration of generic methods: public t querymoulti (class clazz, string SQL, object... args)
Query multiple records
Now we want to include multiple records in the query results.
Just use the collection:
// The query result contains multiple records
public <T> List<T> queryMultiItems(Class<T> clazz, String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLable = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
@Test
public void multiItems() {
String sql = "select email, id, name from customers where id < ?";
List<Customer> customerList = queryMultiItems(Customer.class, sql, 10);
customerList.forEach(s -> System.out.println(s));
}
Customer{id=1, name='Wang Feng', email='wf@126.com', birth=null}
Customer{id=2, name='Faye Wong', email='wangf@163.com', birth=null}
Customer{id=3, name='Lin Zhiling', email='linzl@gmail.com', birth=null}
Customer{id=4, name='Tang Wei', email='tangw@sina.com', birth=null}
Customer{id=5, name='Jackie Chan', email='Jackey@gmai.com', birth=null}
Customer{id=6, name='Delireba', email='reba@163.com', birth=null}
Customer{id=7, name='Liu Yifei', email='liuyifei@qq.com', birth=null}
Customer{id=8, name='Chen Daoming', email='bdf@126.com', birth=null}
prepareStatement and statement
- prepareStatement does not need to splice sql statements
- prepareStatement can solve the problem of sql injection
- prepareStatement can operate pictures, audio and other data
- prepareStatement can realize batch operation more efficiently
https://www.bilibili.com/video/BV1eJ411c7rf?p=23
Summary
Several exercises
1
public class AddItem {
public static void main(String[] args) {
System.out.println("***");
Scanner scanner = new Scanner(System.in);
System.out.print("input user name: ");
String name = scanner.next();
String email = scanner.next();
String birth = scanner.next();
String sql = "insert into customers(name, email, birth) values(?,?,?)";
int i = new AddItem().update(sql, name, email, birth);
if (i > 0) {
System.out.println("update success!!");
}else {
System.out.println("update false!");
}
}
public int update(String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
System.out.println(args[i]);
ps.setObject(i+1, args[i]);
}
// ps.execute();
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps);
}
return 0;
}
}
Note that before, execute was used to execute directly, but it has a return value. If it is a query operation, it will return true, and if it is an update operation, it will return false. The executeUpdate() method is for the update operation. Returning 0 means no item is affected, and returning a number greater than 0 means the number of affected rows
2
The first question is very similar to the previous one. Here, do the second and third questions (only search according to the admission card, similar to the ID card):
package practice;
import bean.java.Student;
import utils.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;
public class task2 {
public static void main(String[] args) {
System.out.println("Please select query method:");
System.out.println("a Admission number");
System.out.println("b ID number");
Scanner scanner = new Scanner(System.in);
String s = scanner.next();
if ("a".equalsIgnoreCase(s)) {
System.out.println("Please enter the admission number");
String examCard = scanner.next();
String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName name, " +
"Location location, Grade grade from examstudent where examCard=?";
Student student = new task2().queryMulti(Student.class, sql, examCard);
System.out.println(student);
}else if ("b".equalsIgnoreCase(s)) {
}
}
public <T> T queryMulti(Class<T> clazz, String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLable = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
}
Third question:
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("input the examId: ");
String s = scanner.next();
String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName name, " +
"Location location, Grade grade from examstudent where examCard=?";
Student student = new task2().queryMulti(Student.class, sql, s);
if (student!=null) {
String sql1 = "delete from examstudent where examCard = ?";
int i = new task2().update(sql1, s);
if (i > 0) {
System.out.println("success!");
}
}
}
Operate on Blob type data
Insert Blob data
public class InsertBlob {
@Test
public void test() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into customers(name, email, birth, photo) values(?,?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1, "Monkey D Luffy");
ps.setObject(2, "lf@gmail.com");
ps.setObject(3, "1995-4-13");
ps.setBlob(4, new FileInputStream(new File("1.jpg")));
ps.execute();
}
}
Read Blob data
// Read Blob data from database
@Test
public void test2() throws Exception {
InputStream is = null;
FileOutputStream os = null;
Connection connection = JDBCUtils.getConnection();
String sql = "select id, name, birth, email, photo from customers where id = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1, 22);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
os = new FileOutputStream("2.jpg");
byte[] bytes = new byte[1024];
int len;
while ((len = is.read(bytes)) != -1) {
os.write(bytes, 0, len);
}
}
JDBCUtils.closeResource(connection, ps, rs);
is.close();
os.close();
}
prepareStatement enables efficient batch operations
prepareStatement is inserted normally
try is not used here. It just means:
@Test
public void test() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into insert_test(name) values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
long l1 = System.currentTimeMillis();
for (int i = 0; i < 20000; i++) {
ps.setObject(1, "name" + i);
ps.execute();
}
long l2 = System.currentTimeMillis();
System.out.println(l2-l1);
JDBCUtils.closeResource(connection, ps);
}
result:
26479
This method is executed every time the placeholder is filled, which leads to low efficiency due to more interactions with the disk. Therefore, the following methods can be used:
Using Batch
// Insert using batch
@Test
public void test2() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into insert_test(name) values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
long l1 = System.currentTimeMillis();
for (int i = 0; i < 20000; i++) {
ps.setObject(1, "name" + i);
// Save first, don't execute
ps.addBatch();
if (i % 500 == 0) {
// Save 500 entries before executing
ps.executeBatch();
// Empty batch
ps.clearBatch();
}
}
long l2 = System.currentTimeMillis();
System.out.println(l2-l1);
JDBCUtils.closeResource(connection, ps);
}
result:
496
However, there are several configurations to be made before using Batch:
Set auto submit
// Set auto submit
@Test
public void test3() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into insert_test(name) values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
long l1 = System.currentTimeMillis();
// Turn off auto submit
connection.setAutoCommit(false);
for (int i = 0; i < 20000; i++) {
ps.setObject(1, "name" + i);
// Save first, don't execute
ps.addBatch();
if (i % 500 == 0) {
// Save 500 entries before executing
ps.executeBatch();
// Empty batch
ps.clearBatch();
}
}
// Unified submission after caching
connection.commit();
long l2 = System.currentTimeMillis();
System.out.println(l2-l1);
JDBCUtils.closeResource(connection, ps);
}
summary
Comparison between PrepareStatement and Statement (interview)
https://www.bilibili.com/video/BV1eJ411c7rf?p=34
They all carry sql statements to the database to perform operations.
The latter has disadvantages: concatenation and sql injection.
The former can also realize the operation of Blob field.
The former is more efficient when bulk inserting data: precompiling
Many advantages of the former are derived from precompiling:
