JDBC implements general basic methods of adding, deleting, modifying and searching

There are many frameworks for data management in Java, such as hibernate, mybatis, etc., but what I learned at the beginning was JDBC. I think JDBC is still very good. It allows me to understand data operations more deeply. Today I will write the basic JDBC class I wrote again! Deepen the memory! ! !

First post the common incremental implementation class BaseDAO

 package com.shude.DAO;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.lang.reflect.ParameterizedType;import java.lang.reflect.Type;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import java.util.Map.Entry;import com.shude.DAO.im.IBaseDAO;import com.shude.util.ConfigUtil;import com.shude.util.JdbcUtil;import com.shude.util.PageUtil;/** * General addition, deletion, modification and search implementation class* @author Administrator * * @param <E> */public class BaseDAO<E> implements IBaseDAO<E> {protected static ConfigUtil configUtil;protected Class<?> cls;/** * Get the package name and class name*/public BaseDAO(){Class<?> clsTemp = this.getClass();Type type = clsTemp.getGenericSuperclass();if (type instanceof ParameterizedType) {Type[] types = ((ParameterizedType) type).getActualTypeArguments();cls = (Class<?>) types[0];}}/** * Get the table name in the database of the corresponding class*/static{configUtil = ConfigUtil.newInstance("/tabORM.properties");}/** * Save method*/public boolean saveInfo(E e) {boolean flag = true;try {Class<?> cls = e.getClass();//Get the table name String tableName = configUtil.getVal(cls.getName());//Get the primary key String prykey = getPrimKey(tableName);//Record the data column List<String> filedList = new ArrayList<String>();//Get the sql statement String sql = getsavesql(tableName,prykey,fileedList);//Execute sqlflag = exclusiveSQL(sql,e,fileedList);} catch (Exception e1) {flag = false;e1.printStackTrace();}return flag;}/** * Modify method*/public void modifyInfo(E e) {Class<?> cls = e.getClass();//Get the table name String tableName = configUtil.getVal(cls.getName());//Get the primary key String prykey = getPrimKey(tableName);//Record the data column List<String> filedList = new ArrayList<String>();//Get the sql statement String sql = getmodifysql(tableName,prykey,filedList);//Add the primary key to the set filedList.add(prykey);//Execute sqlexcuteSQL(sql,e,filedList);} /** * Delete method*/public void deleteInfo(Object id) {//Get the table name String tableName = configUtil.getVal(cls.getName());//Get the primary key String prykey = getPrimKey(tableName);//Get the sql statement String sql = "update "+tableName+" set status='1' where "+prykey+"=?";Connection conn = null;PreparedStatement pstm = null;try {conn = JdbcUtil.getConn();pstm = conn.prepareStatement(sql);pstm.setObject(1, id);pstm.execute();} catch (Exception e) {e.printStackTrace();} finally {JdbcUtil.closeConn(conn);}}/** * Query all methods*/public void queryinfo(PageUtil<E> pageUtil) {E e = pageUtil.getEntity();//Get the table name String tableName = configUtil.getVal(cls.getName());//Get query conditions Map<String,Object> paramMap = getParamMap(e);//Get sqlString sql = getquerySQL(paramMap,tableName); sql += " limit ?,?";paramMap.put("pageSize", (pageUtil.getPageSize() - 1)*pageUtil.getPageNum());paramMap.put("pageNum", pageUtil.getPageNum());//Execute SQLexcutQuery(pageUtil,sql,paramMap,tableName);}/** * Single query method*/public E queryById(Object id) {//Get the table name String tableName = configUtil.getVal(cls.getName());//Get the primary key String prykey = getPrimKey(tableName);//Get sqlString sql = "select * from "+tableName+" where 1 = 1 and "+prykey+" = ?";//Execute SQLConnection conn = null;PreparedStatement pstm = null;ResultSet rs = null;E e = null;try {conn = JdbcUtil.getConn();pstm = conn.prepareStatement(sql);pstm.setObject(1, id);rs = pstm.executeQuery();List<E> list = getEntityList(rs);e = list.get(0);} catch (Exception ex) {ex.printStackTrace();} finally{JdbcUtil.closeConn(conn);}return e;}/** * Get the total number of digits* @param paramMap * @param tableName * @return */ private Integer getPagenumss(Map<String, Object> paramMap, String tableName) { paramMap.remove("pageSize");paramMap.remove("pageNum");String sql = getquerySQL(paramMap,tableName);sql = "select count(*) from ("+sql+") tempTab";Connection conn = null;PreparedStatement pstm = null;ResultSet rs = null;Integer pagenumss = 0;try {conn = JdbcUtil.getConn();pstm = conn.prepareStatement(sql);int i = 1;for (Entry<String,Object> entry : paramMap.entrySet()) {Object val = entry.getValue();if(val instanceof java.lang.String){pstm.setString(i, "%"+val.toString()+"%");}else if(val instanceof java.lang.Integer){pstm.setInt(i, Integer.parseInt(val.toString()));}i++;}rs = pstm.executeQuery(); while(rs.next()){pagenumsss = rs.getInt(1);}} catch (Exception e) {e.printStackTrace();} finally{JdbcUtil.closeConn(conn);}return pagenumsss;}/** * Get query SQL * @param paramMap * @param tableName * @return */private String getquerySQL(Map<String, Object> paramMap, String tableName) {StringBuffer sql = new StringBuffer();sql.append("select * from ").append(tableName).append(" where 1 = 1 and status='0' ");List<String> columnlist = getTableColumns(tableName);for (Entry<String,Object> entry : paramMap.entrySet()) {String columnName = entry.getKey();for (String colnName : columnlist) {if(colnName.equalsIgnoreCase(columName)){if(entry.getValue() instanceof java.lang.String){sql.append(" and ").append(columName).append(" like ?");}else{sql.append(" and ").append(columName).append("=?");}break;}}} return sql.toString();} /** * Get query conditions* @param e * @return */private Map<String, Object> getParamMap(E e) {Map<String,Object> paramMap = new LinkedHashMap<String,Object>();Field[] fields = e.getClass().getDeclaredFields();for (Field field : fields) {try {field.setAccessible(true);Object val = field.get(e);if(val != null && !"".equals(val.toString())){paramMap.put(field.getName(), val);}} catch (Exception e1) {e1.printStackTrace();}} return paramMap;}/** * Get the primary key* @param tableName * @return */private String getPrimKey(String tableName) {Connection conn = null;DatabaseMetaData metaData = null;ResultSet rs = null;String primKeyName = null;try {conn = JdbcUtil.getConn();metaData = conn.getMetaData();rs = metaData.getPrimaryKeys(conn.getCatalog(), null, tableName.toUpperCase()); while (rs.next()) {primKeyName = rs.getString("COLUMN_NAME");}} catch (SQLException e) {e.printStackTrace();} finally{JdbcUtil.closeConn(conn);}return primKeyName;}/** * Save method execution SQL * @param sql * @param e * @param filedList * @return */private boolean exhaustSQL(String sql, E entity, List<String> filedList) {boolean flag = true;Connection conn = null;PreparedStatement pstm = null;try {conn = JdbcUtil.getConn();pstm = conn.prepareStatement(sql);//Assign int i = 1;for (String columnName : filedList) {Object val = getFieldValue(entity,columName);pstm.setObject(i, val);i++;}pstm.execute();} catch (SQLException e1) {e1.printStackTrace();flag = false;} finally{JdbcUtil.closeConn(conn);} return flag;}/** * Get the SQL for modifying the method * @param tableName * @param prykey * @param filedList * @return */private String getmodifysql(String tableName, String prykey, List<String> filedList) {StringBuffer sql = new StringBuffer();sql.append("update ").append(tableName).append("set ");List<String> columnList = getTableColumns(tableName);for (String columnName : columnList) {if (!columnName.equalsIgnoreCase(prykey)) {filedList.add(columnName);sql.append(columnName).append("=?,");}}if (sql.toString().endsWith(",")) {sql = new StringBuffer(sql.substring(0,sql.length()-1));}sql.append(" where ").append(prykey).append("=?");return sql.toString();} /** * Execute all SQL * @param pageUtil * @param sql * @param paramMap * @param tableName */private void excutQuery(PageUtil<E> pageUtil, String sql, Map<String, Object> paramMap, String tableName) {Connection conn = null;PreparedStatement pstm = null;ResultSet rs = null;try {conn = JdbcUtil.getConn();pstm = conn.prepareStatement(sql);int i = 1;for (Entry<String,Object> entry : paramMap.entrySet()) {Object val = entry.getValue();if(val instanceof java.lang.String){pstm.setString(i, "%"+val.toString()+"%");}else if(val instanceof java.lang.Integer){pstm.setInt(i, Integer.parseInt(val.toString()));}i++;}rs = pstm.executeQuery();List<E> list = getEntityList(rs);//Encapsulate query results pageUtil.setList(list);//Encapsulate total number of digits pageUtil.setPageNumSum(getPagenumsss(paramMap,tableName));} catch (Exception e) {e.printStackTrace();} finally{JdbcUtil.closeConn(conn);}}/** * Get attributes in the table* @param entity * @param columnName * @return */ private Object getFieldValue(E entity, String columnName) { Class<?> cls = entity.getClass();Object value = null;//Get all member attributes in the class Field[] fields = cls.getDeclaredFields();for (Field field: fields) {//Get the attribute name String fieldName = field.getName();//Term whether the attribute name is the same as the column name if (fieldName.equalsIgnoreCase(columName)) {//Get the method name according to the rules String methodName = "get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);try {//Get the method object according to the method name Method method = cls.getMethod(methodName);//Execute the method and get the return value value = method.invoke(entity);} catch (Exception e) {e.printStackTrace();}break;}}return value;}/** * Save the method to get SQL * @param tableName * @param prykey * @param filedList * @return */private String getsavesql(String tableName, String prykey, List<String> filedList) {StringBuffer sql = new StringBuffer();sql.append("insert into ").append(tableName).append(" (");List<String> columnList = getTableColumns(tableName);for (String string : columnList) {if (!string.equalsIgnoreCase(prykey)) {sql.append(string).append(",");filedList.add(string);}}if (sql.toString().endsWith(",")) {sql = new StringBuffer(sql.substring(0,sql.length()-1));}sql.append(") value (");for (int i = 0; i <filedList.size(); i++) {sql.append("?,");}if (sql.toString().endsWith(",")) {sql = new StringBuffer(sql.substring(0,sql.length()-1));}sql.append(")");return sql.toString();} /** * Get table column* @param tableName * @return */private List<String> getTableColumns(String tableName) {List<String> columnList = new ArrayList<String>();Connection conn = null;DatabaseMetaData metaData = null;ResultSet rs = null;conn = JdbcUtil.getConn();try {metaData = conn.getMetaData();rs = metaData.getColumns(conn.getCatalog(),null,tableName.toUpperCase(),null); while (rs.next()) {String clumnName = rs.getString("COLUMN_NAME");columnList.add(clumnName);}}catch (SQLException e) {e.printStackTrace();} finally{JdbcUtil.closeConn(conn);}return columnList; } /** * Encapsulate query results* @param rs * @return * @throws Exception */@SuppressWarnings("unchecked")private List<E> getEntityList(ResultSet rs) throws Exception {List<E> list = new ArrayList<E>();Field[] fields = cls.getDeclaredFields();while(rs.next()){E e = (E)cls.newInstance();for (Field field : fields) {try {field.setAccessible(true);String columnName = field.getName();String fieldType = field.getType().getSimpleName();if("String".equals(fieldType)){field.set(e, rs.getString(columName));}else if("Integer".equals(fieldType)){field.set(e, rs.getInt(columName));}} catch (Exception e1) {e1.printStackTrace();}}list.add(e);}return list;}}}

MySQL related configuration file config.properties content

 driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/dbsda10?characterEncoding=UTF-8 username=root password=rootroot temfile=C://image=C://images

Configuration file reading class ConfigUtil

 package com.shude.util;import java.io.IOException;import java.util.Properties;/** * Read configuration file* @author Administrator * */public class ConfigUtil { private static ConfigUtil configUtil; private static final String DEFALT_FILE_PATH="/config.properties"; private static String name; private Properties pop; private ConfigUtil(){ init(); } private void init() { pop=new Properties(); try { if(name!=null) pop.load(ConfigUtil.class.getResourceAsStream(name)); pop.load(ConfigUtil.class.getResourceAsStream(DEFALT_FILE_PATH)); } catch (IOException e) { e.printStackTrace(); } } public static ConfigUtil newInstance(String name){ ConfigUtil.name=name; if(configUtil==null) configUtil=new ConfigUtil(); return configUtil; } /** * Get the value on the right in the configuration file * @param key * @return */ public String getVal(String key){ return pop.getProperty(key); }}

The condition before this is that the field name of the database must correspond to and be the same as the name in the entity class, and there are related data table names and configuration files related to the entity class name. The configuration files are as follows:

tabORM.properties

 com.shude.entity.UserInfo=user_info com.shude.entity.RoleInfo=role_info com.shude.entity.FabricInfo=fabric_info com.shude.entity.ProductInfo=product_info com.shude.entity.MateInfo=mate_info com.shude.entity.ProgramInfo=program_info

The above article JDBC implements general basic methods for adding, deleting, modifying and checking 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.