Alas, I haven't written anything for a long time recently. Due to work reasons, I came into contact with the underlying orm framework developed by the company. I accidentally found that when calling jdbc operations, the framework refers to SimpleJdbcTemplate in hibernate. Here I thought of a simple jdbc encapsulation I used when I was in college. Now I will post the code and share it with you:
Config class: read the database connection configuration file under the same package, so that it is better for generalization considerations.
package com.tly.dbutil;import java.io.IOException;import java.util.Properties;public class Config { private static Properties prop = new Properties(); static{ try { //Load dbconfig.properties configuration file prop.load(Config.class.getResourceAsStream("dbconfig.properties")); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //Set constant public static final String CLASS_NAME = prop.getProperty("CLASS_NAME"); public static final String DATABASE_URL = prop.getProperty("DATABASE_URL"); public static final String SERVER_IP = prop.getProperty("SERVER_IP"); public static final String SERVER_PORT = prop.getProperty("SERVER_PORT"); public static final String DATABASE_SID = prop.getProperty("DATABASE_SID"); public static final String USERNAME = prop.getProperty("USERNAME"); public static final String PASSWORD = prop.getProperty("PASSWORD"); }dbconfig.properties: database configuration file, you can also use xml format, etc., pay attention to the call location of the file in the Config class
CLASS_NAME=com.mysql.jdbc.DriverDATABASE_URL=jdbc:mysqlSERVER_IP=localhostSERVER_PORT=3306DATABASE_SID=employeesUSERNAME=rootPASSWORD=1
Next is the database connection auxiliary class DBConn
package com.employees.dbutil;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DBConn { //Three properties and four methods//Three core interfaces private Connection conn = null; private PreparedStatement pstmt = null; private ResultSet rs = null; //Four methods//Method1: Create a connection to the database public Connection getConntion(){ try { //1: Load the connection driver, Java reflection principle Class.forName(Config.CLASS_NAME); //2: Create a Connection interface object to obtain the connection object of the MySQL database. Three parameters: url connection string account password String url = Config.DATABASE_URL+"://"+Config.SERVER_IP+":"+Config.SERVER_PORT+"/"+Config.DATABASE_SID; conn = DriverManager.getConnection(url,Config.USERNAME,Config.PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } //method2: Method to close the database public void closeConn(){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(pstmt!=null){ try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } //method3: Method specifically used to send add, delete and modify statements public int execOther(PreparedStatement pstmt){ try { //1. Use the Statement object to send SQL statement int affectedRows = pstmt.executeUpdate(); //2. Return the result returned affectedRows; } catch (SQLException e) { e.printStackTrace(); return -1; } } //method4: Specifically used to send query statements public ResultSet execQuery(PreparedStatement pstmt){ try { //1. Use the Statement object to send SQL statement rs = pstmt.executeQuery(); //2. Return the result return rs; } catch (SQLException e) { e.printStackTrace(); return null; } }}Usually, using the above code can solve some simple CRUD applications, but there are many restrictions. For example, every time the program takes a connection, it will increase the burden on the system, there are no transactions, no dataSource, etc. Today I saw a friend in the park writing an article in the garden using reflection to solve CRUD directly in object parameters. I have written this before, but I haven't finished it. I mainly want to write a general DButil. Finally, I studied it and found that it is getting closer to the simpleJdbcTemplate in hibernate, so I went to see the source code of hibernate. In addition, there were some things during that time, and I didn't have time, so I left this matter idle. Now I make up for this thing and review it for myself.
BaseDao class
package com.employees.dao;import java.io.InputStream;import java.lang.reflect.Method;import java.lang.reflect.ParameterizedType;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import com.employees.dbutil.DBConn;public class BaseDAO<T> { DBConn conn = new DBConn(); private Connection connection = null; @SuppressWarnings("unused") private Class<T> persistentClass; @SuppressWarnings("unchecked") public BaseDAO() { initConnection(); //Get parameterized type ParameterizedType type = (ParameterizedType)getClass().getGenericSuperclass(); persistentClass = (Class<T>)type.getActualTypeArguments()[0]; } /** * Get database connection*/ public void initConnection() { connection = conn.getConntion(); } /** * Save */ public void save(T entity) throws Exception{ //SQL statement,insert into table name ( String sql = "insert into " + entity.getClass().getSimpleName().toLowerCase() + "("; //Get the object of all methods with string get List<Method> list = this.matchPojoMethods(entity,"get"); Iterator<Method> iter = list.iterator(); //Split field order insert into table name(id,name,email, while(iter.hasNext()) { Method method = iter.next(); sql += method.getName().substring(3).toLowerCase() + ","; } //Remove the last one, symbol insert insert into table name(id,name,email) values( sql = sql.substring(0, sql.lastIndexOf(",")) + ") values("; //Stand up precompiled SQL statement insert into table name(id,name,email) values(?,?,?, for(int j = 0; j < list.size(); j++) { sql += "?,"; } //Remove the last SQL statement, symbol insert insert into table name(id,name,email) values(?,?,?); sql = sql.substring(0, sql.lastIndexOf(",")) + ")"; //To this SQL statement splicing is completed, print the SQL statement System.out.println(sql); //Get the reference to the precompiled object PreparedStatement statement = connection.prepareStatement(sql); int i = 0; //Move the pointer to the last line of the iterator to the first line. iter = list.iterator(); while(iter.hasNext()) { Method method = iter.next(); //This initially determines the type of return value, because some field value formats need to be changed when stored in the database. For example, String, SQL statement is '"+abc+"' if(method.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1){ statement.setDate(++i, this.getDate(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("InputStream") != -1) { statement.setAsciiStream(++i, this.getBlob(method, entity),1440); } else { statement.setInt(++i, this.getInt(method, entity)); } } //Execute conn.execOther(statement); //CloseConn(); } /** * Modify */ public void update(T entity) throws Exception{ String sql = "update " + entity.getClass().getSimpleName().toLowerCase() + " set "; //Get all get method objects collections of this class List<Method> list = this.matchPojoMethods(entity,"get"); //Temporary Method object, responsible for iterating the fashion method object. Method tempMethod = null; //Because the ID is not necessary to be modified when modifying, adding parameters in order should move the Id to the end. Method idMethod = null; Iterator<Method> iter = list.iterator(); while(iter.hasNext()) { tempMethod = iter.next(); //If the method name contains an ID string and the length is 2, it is considered an ID. if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { //Save the object of the ID field into a variable and delete it in the collection. idMethod = tempMethod; iter.remove(); //If the method name is removed and the set/get string is incompatible with pojo + "id" (case insensitive), it is considered an ID } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))) { idMethod = tempMethod; iter.remove(); } } //Move the iterative pointer to the first position iter = list.iterator(); while(iter.hasNext()) { tempMethod = iter.next(); sql += tempMethod.getName().substring(3).toLowerCase() + "= ?,"; } //Remove the last one, symbol sql = sql.substring(0,sql.lastIndexOf(",")); //Add condition sql += " where " + idMethod.getName().substring(3).toLowerCase() + " = ?"; //SQL splicing is completed, print SQL statement System.out.println(sql); PreparedStatement statement = this.connection.prepareStatement(sql); int i = 0; iter = list.iterator(); while(iter.hasNext()) { Method method = iter.next(); //This initially determines the type of return value, because some field value formats need to be changed when stored in the database, for example, String, SQL statement is '"+abc+"' if(method.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1) { statement.setString(++i, this.getString(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1) { statement.setDate(++i, this.getDate(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("InputStream") != -1) { statement.setAsciiStream(++i, this.getBlob(method, entity),1440); } else { statement.setInt(++i, this.getInt(method, entity)); } } //Add value to the Id field if(idMethod.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(idMethod, entity)); } else { statement.setInt(++i, this.getInt(idMethod, entity)); } //Execute SQL statement statement.executeUpdate(); //Close the precompiled object statement.close(); //Close the connection.close(); } /** * Delete */ public void delete(T entity) throws Exception{ String sql = "delete from " + entity.getClass().getSimpleName().toLowerCase() + " where "; //Storing field object with string "id" Method idMethod = null; //Getting field object with string "id" List<Method> list = this.matchPojoMethods(entity, "get"); Iterator<Method> iter = list.iterator(); while(iter.hasNext()) { Method tempMethod = iter.next(); //If the method name contains the ID string and the length is 2, it is considered an ID. if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { //Save the object of the ID field in a variable and delete it in the set. idMethod = tempMethod; iter.remove(); //If the method name is removed and the set/get string is incompatible with pojo + "id" (case insensitive), it is considered an ID } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3)))) { idMethod = tempMethod; iter.remove(); } } sql += idMethod.getName().substring(3).toLowerCase() + " = ?"; PreparedStatement statement = this.connection.prepareStatement(sql); //Add value to the Id field int i = 0; if(idMethod.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(idMethod, entity)); } else { statement.setInt(++i, this.getInt(idMethod, entity)); } //Execute conn.execOther(statement); //CloseConn(); } /** * Query by ID*/ public T findById(Object object) throws Exception{ String sql = "select * from " + persistentClass.getSimpleName().toLowerCase() + " where "; //Use the constructor of the subclass to obtain the specific type of the parameterized type. For example, BaseDAO<T>, that is, obtain the specific type of T entity = persistentClass.newInstance(); //Storage the method object that stores the primary key of Pojo (or the table being operated) Method idMethod = null; List<Method> list = this.matchPojoMethods(entity, "set"); Iterator<Method> iter = list.iterator(); //Filter to obtain the Method object while(iter.hasNext()) { Method tempMethod = iter.next(); if(tempMethod.getName().indexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { idMethod = tempMethod; } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))){ idMethod = tempMethod; } } //The first letter is converted to lowercase sql += idMethod.getName().substring(3,4).toLowerCase()+idMethod.getName().substring(4) + " = ?"; //After the encapsulation statement, print the sql statement System.out.println(sql); //Get the connection PreparedStatement statement = this.connection.prepareStatement(sql); //Judge the type of id if(object instanceof Integer) { statement.setInt(1, (Integer)object); } else if(object instanceof String){ statement.setString(1, (String)object); } //Execute sql to get the query result set. ResultSet rs = conn.execQuery(statement); //Register, record loops to the number of fields int i = 0; //Point the pointer to the first line of the iterator iter = list.iterator(); //Encapsulate while(rs.next()) { while(iter.hasNext()) { Method method = iter.next(); if(method.getParameterTypes()[0].getSimpleName().indexOf("String") != -1) { //Because in the list set, the order of the method object to be retrieved is inconsistent with the order of the database field (for example: the first method of list is setDate, and the database takes the "123" value in order) //The database fields are selected in the corresponding name. this.setString(method, entity, rs.getString(method.getName().substring(3).toLowerCase())); } else if(method.getParameterTypes()[0].getSimpleName().indexOf("Date") != -1){ this.setDate(method, entity, rs.getDate(method.getName().substring(3).toLowerCase())); } else if(method.getParameterTypes()[0].getSimpleName().indexOf("InputStream") != -1) { this.setBlob(method, entity, rs.getBlob(method.getName().substring(3).toLowerCase()).getBinaryStream()); } else { this.setInt(method, entity, rs.getInt(method.getName().substring(3).toLowerCase())); } } } //Close the result set rs.close(); //Close the precompiled object statement.close(); return entity; } /** * Filter all Method objects with incoming strings in the current Pojo class and return the List collection. */ private List<Method> matchPojoMethods(T entity,String methodName) { //Get all current Pojo method objects Method[] methods = entity.getClass().getDeclaredMethods(); //List container stores all Method objects with get string List<Method> list = new ArrayList<Method>(); //Filter all Method objects with get strings in the current Pojo class and store them in the List container for(int index = 0; index < methods.length; index++) { if(methods[index].getName().indexOf(methodName) != -1) { list.add(methods[index]); } } return list; } /** * The method returns the SQL statement value when the type is int or Integer. Corresponding to get */ public Integer getInt(Method method, T entity) throws Exception{ return (Integer)method.invoke(entity, new Object[]{}); } /** * The method returns the SQL statement assembly value when the type is String. For example, 'abc', corresponding to get */ public String getString(Method method, T entity) throws Exception{ return (String)method.invoke(entity, new Object[]{}); } /** * The method returns the SQL statement assembled value when the type of Blob. Corresponding to get */ public InputStream getBlob(Method method, T entity) throws Exception{ return (InputStream)method.invoke(entity, new Object[]{}); } /** * The method returns the SQL statement assembled value when the type of Date, corresponding to get */ public Date getDate(Method method, T entity) throws Exception{ return (Date)method.invoke(entity, new Object[]{}); } /** * When the parameter type is Integer or int, set parameters for the entity field, corresponding to set */ public Integer setInt(Method method, T entity, Integer arg) throws Exception{ return (Integer)method.invoke(entity, new Object[]{arg}); } /** * When the parameter type is String, set parameters for the entity field, corresponding to set */ public String setString(Method method, T entity, String arg) throws Exception{ return (String)method.invoke(entity, new Object[]{arg}); } /** * When the parameter type is InputStream, set parameters for the entity field, corresponding to set */ public InputStream setBlob(Method method, T entity, InputStream arg) throws Exception{ return (InputStream)method.invoke(entity, new Object[]{arg}); } /** * When the parameter type is Date, set parameters for the entity field, corresponding to set */ public Date setDate(Method method, T entity, Date arg) throws Exception{ return (Date)method.invoke(entity, new Object[]{arg}); }}EmployeesDao inherits BaseDAO, and can directly use the parent class method, adding code reuse
package com.employees.dao;import java.util.ArrayList;import java.util.List;import com.employees.po.Employees;public class EmployeesDao extends BaseDAO<Employees> { // Operation of adding employee information public boolean addEmployees(final Employees employees) throws Exception { save(employees); return true; } // Add employee information to the table public List<Employees> addEmployees(int id) throws Exception { List<Employees> lstEmployees = new ArrayList<Employees>(); Employees employees = findById(id); // Load the currently enclosed data into the object lstEmployees.add(employees); return lstEmployees; } public void deleteEmp(final Employees entity) throws Exception { this.delete(entity); } public void updateEmp(final Employees entity) throws Exception { this.update(entity); }}I won't post the code of the po layer, now use junit4 to do a test
package com.employees.dao;import org.junit.Test;import com.employees.po.Employees;public class EmployeesDaoTest { @Test public void testAdd() throws Exception { Employees emp = new Employees(); emp.setPname("tly"); emp.setPsex("male"); emp.setPbeliefs("xxxxx"); emp.setPaddr("Tianhe"); emp.setPhobby("play basketball"); emp.setPsubject("Computer"); emp.setPtel("123456"); EmployeesDao dao = new EmployeesDao(); dao.addEmployees(emp); } @Test public void testUpdate() throws Exception { EmployeesDao dao = new EmployeesDao(); Employees emp = dao.findById(14); emp.setPtel("999999"); dao.updateEmp(emp); } @Test public void testdelete() throws Exception { EmployeesDao dao = new EmployeesDao(); Employees emp = dao.findById(15); dao.deleteEmp(emp); }}After testing, these three methods can run normally, time is rushing. Some codes are used by other buddies. Some places may not be considered very comprehensive or some codes will be redundant. The general crud operation in BaseDAO has not been written in full. If any friend is interested, you can write it again, such as query, batch operation, etc. If the test passes, remember to send me a copy, haha
The above simple and general JDBC auxiliary packaging (example) is all the content I share with you. I hope you can give you a reference and I hope you can support Wulin.com more.