JDBC 实现通用的增删改查基础类方法

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);			//执行sql			flag = 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);		//执行sql		excuteSQL(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);		//获取sql		String sql = getquerySQL(paramMap,tableName);		sql += " limit ?,?";		paramMap.put("pageSize", (pageUtil.getPageSize() - 1)*pageUtil.getPageNum());		paramMap.put("pageNum", pageUtil.getPageNum());		//执行SQL		excutQuery(pageUtil,sql,paramMap,tableName);			}		/**	 * 单个查询方法	 */	public E queryById(Object id) {		//获取表名				String tableName = configUtil.getVal(cls.getName());				//获取主键				String prykey = getPrimKey(tableName);				//获取sql				String sql = "select * from "+tableName+" where 1 = 1 and "+prykey+" = ?";				//执行SQL				Connection 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;	}}

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

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

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