JDBC 實現(xiàn)通用的增刪改查基礎(chǔ)類方法

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;
/**
 * 通用的增刪改查實現(xiàn)類
 * @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];
		}
	}
	/**
	 * 獲取對應(yīng)類的數(shù)據(jù)庫中的表名
	 */
	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);
			//記錄數(shù)據(jù)列
			List<String> filedList = new ArrayList<String>();
			//獲取sql語句
			String sql = getsavesql(tableName,prykey,filedList);
			//執(zhí)行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);
		//記錄數(shù)據(jù)列
		List<String> filedList = new ArrayList<String>();
		//獲取sql語句
		String sql = getmodifysql(tableName,prykey,filedList);
		//添加主鍵到集合
		filedList.add(prykey);
		//執(zhí)行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);
		}	
	}
	/**
	 * 查詢?nèi)糠椒?	 */
	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());
		//執(zhí)行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+" = ?";
				//執(zhí)行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;
	}
	
	/**
	 * 獲取總條數(shù)
	 * @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;
	}
	/**
	 * 保存方法執(zhí)行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();
	}
  /**
   * 執(zhí)行查詢?nèi)縎QL
   * @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);
			//封裝查詢結(jié)果
 			pageUtil.setList(list);
			//封裝總條數(shù)
			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)) {
				//根據(jù)規(guī)則獲取方法名稱
				String methodName = "get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
				try {
					//根據(jù)方法名稱獲取方法對象
					Method method = cls.getMethod(methodName);
					//執(zhí)行方法并獲取返回值
					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;
  }
  /**
   * 封裝查詢結(jié)果
   * @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