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;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;}  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;}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();}  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;}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;}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;}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();}  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);}}  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;}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();}  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;  }  @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;}}