preface
JDBC is not only the specification of Java operating database, but also the core foundation. The principle of the future framework also encapsulates JDBC and uses reflection, annotation and design patterns to complete simplification.
1, JDBC architecture
- JDBC interface (API) includes two levels:
- Application oriented API: Java API, abstract interface, for application developers to use (connect to the database, execute SQL statements, and obtain results).
- Database oriented API: Java Driver API for developers to develop database drivers.
JDBC is a set of interfaces provided by sun company for database operation. java programmers only need to program for this set of interfaces.
Different database manufacturers need to provide different implementations for this set of interfaces. The collection of different implementations is the driver of different databases———— Interface oriented programming
How Java operates the database is as follows:
2, JDBC programming steps
3, JDBC connection
package com.xhu.java.jdbc;
import com.mysql.jdbc.Driver;
import org.junit.Test;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* Please turn on the service
*/
public class TestConnection {
@Test
public void testConn1() {
//Set the properties file to be logged in
Properties pro = new Properties();
pro.setProperty("user", "root");
pro.setProperty("password", "lls");
//Get connection
try (Connection conn = new Driver().connect("jdbc:mysql://localhost:3306/test", pro);) {
System.out.println(conn);
if (conn != null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void testConn2() throws Exception {
//Shielding specific third-party implementation, using reflection
//1. Get class object
Class driverC = Class.forName("com.mysql.jdbc.Driver");
//2. Set the attribute file to be logged in
Properties pro = new Properties();
pro.setProperty("user", "root");
pro.setProperty("password", "lls");
//3. Get the constructor through reflection
Constructor dc = driverC.getDeclaredConstructor();
//4. Instance the object through the obtained constructor
java.sql.Driver driver = (Driver) dc.newInstance();
//5. Obtain the connection according to the object of the instance
Connection conn = driver.connect("jdbc:mysql://localhost:3306/test", pro);
System.out.println(conn);
if (conn != null)
conn.close();
}
@Test
public void testConn3() throws Exception {
//Using DriverManager
//1. Get class object
Class driverC = Class.forName("com.mysql.jdbc.Driver");
//2. Set the attribute file to be logged in
Properties pro = new Properties();
pro.setProperty("user", "root");
pro.setProperty("password", "lls");
//3. Get the constructor through reflection
Constructor dc = driverC.getDeclaredConstructor();
//4. Instance the object through the obtained constructor
java.sql.Driver driver = (Driver) dc.newInstance();
//5. Register driver
DriverManager.registerDriver(driver);
//6. Use the registered manager to obtain the connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", pro);
System.out.println(conn);
conn.close();
}
@Test
public void testConn4() throws Exception {
//Using DriverManager, MySQL provides static code blocks to register drivers
//1. Load the class. At this time, the JVM loads the class, links and initializes. During initialization, the static code block and static assignment statement are combined into the clinit() method and executed.
/*static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}*/
Class.forName("com.mysql.jdbc.Driver");
//2. Set the attribute file to be logged in
Properties pro = new Properties();
pro.setProperty("user", "root");
pro.setProperty("password", "lls");
//3. Use the registered manager to get the connection
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", pro);
System.out.println(conn);
conn.close();
}
@Test
public void testConn5() throws Exception {
//Instead of writing code, let the code read the configuration file
//1) In this way, you don't have to recompile the Java file, just change the configuration file. 2) Code and data are separated, and the coupling is low (non hard coding).
//1. Create a node stream for the target file
InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
//2. Use Properties to read in the file
Properties pro = new Properties();
pro.load(is);
is.close();
//3. Get value in properties
String driverClass = pro.getProperty("driverClass");
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
//4. Get the Class object for reflection operation.
Class driverC = Class.forName(driverClass);
//5. Use the registered manager to get the connection
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
conn.close();
}
}
4, PreparedStatement implements CRUD operations
1. Operating and accessing databases
-
The database connection is used to send commands and SQL statements to the database server and accept the results returned by the database server. In fact, a database connection is a Socket connection.
-
In the java.sql package, there are three interfaces that define different ways to call the database:
- Statement: an object used to execute a static SQL statement and return the results generated by it. SQL injection problems will occur.
- PrepatedStatement: the SQL statement is precompiled and stored in this object, which can be used multiple times to execute the statement efficiently.
- CallableStatement: used to execute SQL stored procedures
2,CRUD
package com.xhu.java.jdbc;
import org.junit.Test;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;
public class TestCRUD {
@Test
public void test_JDBCUtils() {
//The alias must correspond to the property name in the POJO class before you can inject data with reflection.
String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order`";
List<Order> orders = JDBCUtils.getMultiInstance(Order.class, sql);
orders.forEach(System.out::println);
}
@Test
public void test_getMultiInstance() throws Exception {
String sql = "select order_id orderId,order_name orderName,order_date orderDate from `order`";
List<Order> orders = getMultiInstance(Order.class, sql);
System.out.println(Arrays.toString(orders.toArray()));
orders.forEach(System.out::println);
}
/**
* @param c
* @param sql
* @param args
* @param <T>
* @return
* @throws Exception
* @function Get multiple records
*/
public <T> List<T> getMultiInstance(Class<T> c, String sql, Object... args) throws Exception {
//1. Get connection
Connection conn = getConn();
//2. Get preprocessed SQL object
PreparedStatement ps = conn.prepareStatement(sql);
//3. Placeholder assignment
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//4. Execute SQL
ResultSet res = ps.executeQuery();
//5. Processing result set
/**
* @Reflect
* res Encapsulate the field data of the queried data, i.e. metadata information, in meta data
* Get the metadata, and then get the number of columns and field names of the query
* Get the corresponding value of the field through res
* The corresponding value is dynamically injected through reflection + field name
*/
List<T> resList = new ArrayList<>();
while (res.next()) {
ResultSetMetaData metaData = res.getMetaData();
int cCount = metaData.getColumnCount();
T t = c.getConstructor(null).newInstance();
//class objects required for reflection injection
Class cusC = t.getClass();
for (int i = 0; i < cCount; i++) {
//Get field name
String columnName = metaData.getColumnLabel(i + 1);//Get the POJO class corresponding to the alias. If there is no alias, it is the original name
Object columnValue = res.getObject(i + 1);
//Reflection enables dynamic injection of data
//Get fields with arbitrary permissions
Field field = cusC.getDeclaredField(columnName);
//Get private permission
field.setAccessible(true);
//Set attribute value
field.set(t, columnValue);
}
resList.add(t);
}
//6. Close resources
closeQuery(ps, conn, res);
//7. Return query results
return resList;
}
@Test
public void test_getInstance() throws Exception {
String sql = "select id,name,email,birth from customers where id = ?";
System.out.println(getInstance(Customer.class, sql, 1));
}
/**
* @param c
* @param sql
* @param args
* @param <T>
* @return T
* @throws Exception
* @function General query of various classes
*/
public <T> T getInstance(Class<T> c, String sql, Object... args) throws Exception {
//1. Get connection
Connection conn = getConn();
//2. Get preprocessed SQL object
PreparedStatement ps = conn.prepareStatement(sql);
//3. Placeholder assignment
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//4. Execute SQL
ResultSet res = ps.executeQuery();
//5. Processing result set
/**
* @Reflect
* res Encapsulate the field data of the queried data, i.e. metadata information, in meta data
* Get the metadata, and then get the number of columns and field names of the query
* Get the corresponding value of the field through res
* The corresponding value is dynamically injected through reflection + field name
*/
T t = null;
if (res.next()) {
ResultSetMetaData metaData = res.getMetaData();
int cCount = metaData.getColumnCount();
t = c.getConstructor(null).newInstance();
//class objects required for reflection injection
Class cusC = t.getClass();
for (int i = 0; i < cCount; i++) {
//Get field name
String columnName = metaData.getColumnLabel(i + 1);//Get the POJO class corresponding to the alias. If there is no alias, it is the original name
Object columnValue = res.getObject(i + 1);
//Reflection enables dynamic injection of data
String setMethodName = "set" + (char) (columnName.charAt(0) - 32) + columnName.substring(1);
String getMethodName = "get" + (char) (columnName.charAt(0) - 32) + columnName.substring(1);
Method method = cusC.getDeclaredMethod(setMethodName, cusC.getDeclaredMethod(getMethodName, null).getReturnType());
method.invoke(t, columnValue);
/**
* Mode II
* //Get fields with arbitrary permissions
* Field field = cusC.getDeclaredField(columnName);
* //Get private permission
* field.setAccessible(true);
* //Set attribute value
* field.set(cus, columnName);
*/
}
}
//6. Close resources
closeQuery(ps, conn, res);
//7. Return query results
return t;
}
@Test
public void selectBySQL() throws Exception {
//Check by id
String sql = "select id,name,email,birth from customers where id = ?";
Customer customer = selectCommon(sql, 1);
System.out.println(customer);
//Check by name
String sql2 = "select id,name,email,birth from customers where name = ?";
customer = selectCommon(sql2, "Jay Chou");
System.out.println(customer);
}
/**
* @param sql
* @param args
* @return Customer class
* @throws Exception
* @function General operation of a class
*/
public Customer selectCommon(String sql, Object... args) throws Exception {
//1. Get connection
Connection conn = getConn();
//2. Get preprocessed SQL object
PreparedStatement ps = conn.prepareStatement(sql);
//3. Placeholder assignment
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//4. Execute SQL
ResultSet res = ps.executeQuery();
//5. Processing result set
/**
* @Reflect
* res Encapsulate the field data of the queried data, i.e. metadata information, in meta data
* Get the metadata, and then get the number of columns and field names of the query
* Get the corresponding value of the field through res
* The corresponding value is dynamically injected through reflection + field name
*/
Customer cus = null;
if (res.next()) {
System.out.println("There are results");
ResultSetMetaData metaData = res.getMetaData();
int cCount = metaData.getColumnCount();
cus = new Customer();
//class objects required for reflection injection
Class cusC = cus.getClass();
for (int i = 0; i < cCount; i++) {
//Get field name
String columnName = metaData.getColumnLabel(i + 1);//Get the POJO class corresponding to the alias. If there is no alias, it is the original name
Object columnValue = res.getObject(i + 1);
//Reflection enables dynamic injection of data
String setMethodName = "set" + (char) (columnName.charAt(0) - 32) + columnName.substring(1);
String getMethodName = "get" + (char) (columnName.charAt(0) - 32) + columnName.substring(1);
Method method = cusC.getDeclaredMethod(setMethodName, cusC.getDeclaredMethod(getMethodName, null).getReturnType());
method.invoke(cus, columnValue);
/**
* Mode II
* //Get fields with arbitrary permissions
* Field field = cusC.getDeclaredField(columnName);
* //Get private permission
* field.setAccessible(true);
* //Set attribute value
* field.set(cus, columnName);
*/
}
}
//6. Close resources
closeQuery(ps, conn, res);
//7. Return query results
return cus;
}
@Test
public void select() throws Exception {
//1. Get connection
Connection conn = getConn();
//2. Get the object of precompiled SQL
String sql = "select * from customers where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
//3. Assignment
ps.setObject(1, 1);
//4. Query results obtained by execution
ResultSet res = ps.executeQuery();
//5. Treatment results
/**
* ResultSet The next method of the object is used to judge whether there is a next value. If so, move the pointer down and return true. Otherwise, return false.
* Iterator The function of the hasNext method of the object is to simply judge whether there is a next one. If there is, it returns true; otherwise, it returns false. The next method moves the pointer down and gets the element.
*/
Customer cus = null;
if (res.next()) {
int id = res.getInt(1);
String name = res.getString(2);
String email = res.getString(3);
Date birth = res.getDate(4);
cus = new Customer(id, name, email, birth);
}
System.out.println(cus);
//6. Close resources
closeQuery(ps, conn, res);
}
/**
* @param ps
* @param conn
* @param res
* @function Close the connection, preprocess the SQL, and obtain the three stream resources from the result set
*/
private void closeQuery(PreparedStatement ps, Connection conn, ResultSet res) {
close(ps, conn);
try {
if (res != null) res.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void insert() {
//1. Get connection
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConn();
//2. Get a precompiled operation SQL object PreparedStatement by connecting + SQL
String sql = "insert into customers(name,email,birth) values(?,?,?)";//? As placeholder
ps = conn.prepareStatement(sql);
//3. Set values for placeholders in precompiled SQL
ps.setString(1, "lls");
ps.setString(2, "lls@qq.com");
ps.setDate(3, new Date(new SimpleDateFormat("yyyy-MM-dd").parse("1998-8-8").getTime()));
//4. Execute SQL
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5. Close the connection
close(ps, conn);
}
}
@Test
public void update() throws Exception {
//1. Get database connection
Connection conn = getConn();
//2. Get the object of SQL operation
String sql = "update customers set name=? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
//3. Assign values to placeholders
ps.setString(1, "LLS");
ps.setInt(2, 19);
//4. Execute SQL
ps.execute();
//5. Close resources
close(ps, conn);
}
private Connection getConn() throws Exception {
//Instead of writing code, let the code read the configuration file
//1) In this way, you don't have to recompile the Java file, just change the configuration file. 2) Code and data are separated, and the coupling is low (non hard coding).
//1. Create a node stream for the target file
InputStream is = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
//2. Use Properties to read in the file
Properties pro = new Properties();
pro.load(is);
is.close();
//3. Get value in properties
String driverClass = pro.getProperty("driverClass");
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
//4. Get the Class object for reflection operation.
Class driverC = Class.forName(driverClass);
//5. Use the registered manager to get the connection
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
private void close(PreparedStatement ps, Connection conn) {
try {
if (ps != null) ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null) conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
@Test
public void testUpdate() {
Object[] args = new Object[]{"lls", 19};
String sql = "update customers set name=? where id = ?";
JDBCUtils.update(sql, args);
}
}
3,JDBCUtils
package com.xhu.java.jdbc;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
* @author lls
* @version 1.0
* @describle Database operation tool class
* @date 2021-11-27
* @method connection, insert, ...
*/
/**
* What are the benefits of using PreparedStatement?
* 1.SQL Injection problem: when the object is obtained, the incoming SQL statement is logically precompiled, and the logic will not be changed when injecting data, so as to prevent SQL injection problem.
* 2.The efficiency of batch inserting data. When batch inserting data, the object has performed syntax verification on the SQL during precompiling, and then the object will not be verified if it executes the equivalent SQL. However, if an ordinary Statement does not execute an SQL, it will have to recompile and verify the syntax once.
* 3.Write binary problem to the database. The object can write binary files by assigning IO streams to placeholders.
*/
/*
* ORM object relational mapping
* A data table corresponds to a java class
* A record in the table corresponds to an object of the java class
* A field in the table corresponds to an attribute of the java class
*
*/
public class JDBCUtils {
/**
* @param c
* @param sql
* @param args
* @param <T>
* @return
* @throws Exception
* @function Get multiple records, common to all types
*/
public static <T> List<T> getMultiInstance(Class<T> c, String sql, Object... args) {
//1. Get connection
Connection conn = null;
PreparedStatement ps = null;
ResultSet res = null;
//Return results
List<T> resList = new ArrayList<>();
try {
conn = getConn();
//2. Get preprocessed SQL object
ps = conn.prepareStatement(sql);
//3. Placeholder assignment
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//4. Execute SQL
res = ps.executeQuery();
//5. Processing result set
/**
* @Reflect
* res Encapsulate the field data of the queried data, i.e. metadata information, in meta data
* Get the metadata, and then get the number of columns and field names of the query
* Get the corresponding value of the field through res
* The corresponding value is dynamically injected through reflection + field name
*/
while (res.next()) {
ResultSetMetaData metaData = res.getMetaData();
int cCount = metaData.getColumnCount();
T t = c.getConstructor(null).newInstance();
//class objects required for reflection injection
Class cusC = t.getClass();
for (int i = 0; i < cCount; i++) {
//Get field name
String columnName = metaData.getColumnLabel(i + 1);//Get the POJO class corresponding to the alias. If there is no alias, it is the original name
Object columnValue = res.getObject(i + 1);
//Reflection enables dynamic injection of data
//Get fields with arbitrary permissions
Field field = cusC.getDeclaredField(columnName);
//Get private permission
field.setAccessible(true);
//Set attribute value
field.set(t, columnValue);
}
resList.add(t);
}
} catch (Exception e) {
e.printStackTrace();
}
//6. Close resources
closeQuery(ps, conn, res);
//7. Return query results
return resList;
}
/**
* @param sql SQL statement to execute
* @param args SQL Parameters to populate in
* @function Unified operation of addition, deletion and modification
*/
public static void update(String sql, Object... args) {
//1. Get connection
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConn();
//2. Get a precompiled operation SQL object PreparedStatement by connecting + SQL
ps = conn.prepareStatement(sql);
//3. Set values for placeholders in precompiled SQL
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//4. Execute SQL
ps.execute();
} catch (Exception e) {
System.out.println("Insert exception");
e.printStackTrace();
} finally {
//5. Close the connection
close(ps, conn);
}
}
/**
* @param c An object instance corresponds to a record in the database
* @function insert data
*/
public static void insert(Customer c) {
//1. Get connection
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConn();
//2. Get a precompiled operation SQL object PreparedStatement by connecting + SQL
String sql = "insert into customers (`name`,email,birth) values(?,?,?)";//? As placeholder
ps = conn.prepareStatement(sql);
//3. Set values for placeholders in precompiled SQL
ps.setString(1, c.getName());
ps.setString(2, c.getEmail());
ps.setDate(3, c.getBirth());
//4. Execute SQL
ps.execute();
} catch (Exception e) {
System.out.println("Insert exception");
e.printStackTrace();
} finally {
//5. Close the connection
close(ps, conn);
}
}
/**
* @return Database connection
* @throws Exception
* @function Get database connection
*/
private static Connection getConn() throws Exception {
//Instead of writing code, let the code read the configuration file
//1) In this way, you don't have to recompile the Java file, just change the configuration file. 2) Code and data are separated, and the coupling is low (non hard coding).
//1. Create a node stream for the target file
InputStream is = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
//2. Use Properties to read in the file
Properties pro = new Properties();
pro.load(is);
is.close();
//3. Get value in properties
String driverClass = pro.getProperty("driverClass");
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
//4. Get the Class object for reflection operation.
Class driverC = Class.forName(driverClass);
//5. Use the registered manager to get the connection
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* @param ps
* @param conn
*/
private static void close(PreparedStatement ps, Connection conn) {
try {
if (ps != null) ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null) conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
/**
* @param ps
* @param conn
* @param res
* @function Close the connection, preprocess the SQL, and obtain the three stream resources from the result set
*/
private static void closeQuery(PreparedStatement ps, Connection conn, ResultSet res) {
close(ps, conn);
try {
if (res != null) res.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
4. Data type conversion table corresponding to Java and SQL
| Java type | SQL type |
|---|---|
| boolean | BIT |
| byte | TINYINT |
| short | SMALLINT |
| int | INTEGER |
| long | BIGINT |
| String | CHAR,VARCHAR,LONGVARCHAR |
| byte array | BINARY , VAR BINARY |
| java.sql.Date | DATE |
| java.sql.Time | TIME |
| java.sql.Timestamp | TIMESTAMP |
5. JDBC API summary
-
Two thoughts
-
The idea of interface oriented programming
-
ORM (object relational mapping)
- A data table corresponds to a java class
- A record in the table corresponds to an object of the java class
- A field in the table corresponds to an attribute of the java class
sql needs to be written in combination with column names and table attribute names. Note the alias.
-
-
Two technologies
- Metadata of JDBC result set: ResultSetMetaData
- Get the number of columns: getColumnCount()
- Get alias of column: getColumnLabel()
- Through reflection, create the object of the specified class, obtain the specified attribute and assign a value
- Metadata of JDBC result set: ResultSetMetaData
summary
1) JDBC introduction and connection.
2) Universal CRUD for JDBC.