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.