JDBC 實現通用的增刪改查基礎類方法

java中關於數據的管理有很多的框架，如hibernate、mybatis等，但我最開始學習的就是JDBC，我覺得JDBC還是很不錯的，它讓我更深層次的了解了數據的操作，今天我將我寫過的JDBC基礎類再寫一遍！加深下記憶！ ！ ！

先將通用的增查實現類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;/** * 通用的增刪改查實現類* @author Administrator * * @param <E> */public class BaseDAO<E> implements IBaseDAO<E> {protected static ConfigUtil configUtil;protected Class<?> cls;/** * 獲取包名、類名*/public BaseDAO(){Class<?> clsTemp = this.getClass();Type type = clsTemp.getGenericSuperclass();if (type instanceof ParameterizedType) {Type[] types = ((ParameterizedType) type).getActualTypeArguments();cls = (Class<?>) types[0];}}/** * 獲取對應類的數據庫中的表名*/static{configUtil = ConfigUtil.newInstance("/tabORM.properties");}/** * 保存方法*/public boolean saveInfo(E e) {boolean flag = true;try {Class<?> cls = e.getClass();//獲取表名String tableName = configUtil.getVal(cls.getName());//獲取主鍵String prykey = getPrimKey(tableName);//記錄數據列List<String> filedList = new ArrayList<String>();//獲取sql語句String sql = getsavesql(tableName,prykey,filedList);//執行sqlflag = excuteSQL(sql,e,filedList);} catch (Exception e1) {flag = false;e1.printStackTrace();}return flag;}/** * 修改方法*/public void modifyInfo(E e) {Class<?> cls = e.getClass();//獲取表名String tableName = configUtil.getVal(cls.getName());//獲取主鍵String prykey = getPrimKey(tableName);//記錄數據列List<String> filedList = new ArrayList<String>();//獲取sql語句String sql = getmodifysql(tableName,prykey,filedList);//添加主鍵到集合filedList.add(prykey);//執行sqlexcuteSQL(sql,e,filedList);} /** * 刪除方法*/public void deleteInfo(Object id) {//獲取表名String tableName = configUtil.getVal(cls.getName());//獲取主鍵String prykey = getPrimKey(tableName);//獲取sql語句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);}}/** * 查詢全部方法*/public void queryinfo(PageUtil<E> pageUtil) {E e = pageUtil.getEntity();//獲取表名String tableName = configUtil.getVal(cls.getName());//獲取查詢條件Map<String,Object> paramMap = getParamMap(e);//獲取sqlString sql = getquerySQL(paramMap,tableName);sql += " limit ?,?";paramMap.put("pageSize", (pageUtil.getPageSize() - 1)*pageUtil.getPageNum());paramMap.put("pageNum", pageUtil.getPageNum());//執行SQLexcutQuery(pageUtil,sql,paramMap,tableName);}/** * 單個查詢方法*/public E queryById(Object id) {//獲取表名String tableName = configUtil.getVal(cls.getName());//獲取主鍵String prykey = getPrimKey(tableName);//獲取sqlString sql = "select * from "+tableName+" where 1 = 1 and "+prykey+" = ?";//執行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;}/** * 獲取總條數* @param paramMap * @param tableName * @return */ private Integer getPagenumsss(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 pagenumsss = 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;}/** * 獲取查詢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> columlist = getTableColumns(tableName);for (Entry<String,Object> entry : paramMap.entrySet()) {String columName = entry.getKey();for (String colnName : columlist) {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();} /** * 獲取查詢條件* @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;}/** * 獲取主鍵* @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;}/** * 保存方法執行SQL * @param sql * @param e * @param filedList * @return */private boolean excuteSQL(String sql, E entity, List<String> filedList) {boolean flag = true;Connection conn = null;PreparedStatement pstm = null;try {conn = JdbcUtil.getConn();pstm = conn.prepareStatement(sql);//賦值int i = 1;for (String columName : 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;}/** * 獲取修改方法的SQL * @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();} /** * 執行查詢全部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);//封裝查詢結果pageUtil.setList(list);//封裝總條數pageUtil.setPageNumSum(getPagenumsss(paramMap,tableName));} catch (Exception e) {e.printStackTrace();}finally{JdbcUtil.closeConn(conn);}}/** * 獲取表中屬性* @param entity * @param columName * @return */ private Object getFieldValue(E entity, String columName) { Class<?> cls = entity.getClass();Object value = null;//獲取類中的所有成員屬性Field[] fields = cls.getDeclaredFields();for (Field field : fields) {//獲取屬性名稱String fieldName = field.getName();//判斷屬性名稱是否與列名相同if (fieldName.equalsIgnoreCase(columName)) {//根據規則獲取方法名稱String methodName = "get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);try {//根據方法名稱獲取方法對象Method method = cls.getMethod(methodName);//執行方法並獲取返回值value = method.invoke(entity);} catch (Exception e) {e.printStackTrace();}break;}}return value;}/** * 保存方法獲取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();} /** * 獲取表列* @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; } /** * 封裝查詢結果* @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 columName = 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相關配置文件config.properties內容

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

配置文件讀取類ConfigUtil

 package com.shude.util;import java.io.IOException;import java.util.Properties;/** * 讀取配置文件* @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; } /** * 獲取配置文件中右邊的值* @param key * @return */ public String getVal(String key){ return pop.getProperty(key); }}

在此之前的條件是數據庫的字段名稱要與實體類中的名稱對應並且相同，有相關的數據表名稱及實體類名稱所相關的配置文件，配置文件如下：

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

以上這篇JDBC 實現通用的增刪改查基礎類方法就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持武林網。