java中关于数据的管理有很多的框架，如hibernate、mybatis等，但我最开始学习的就是JDBC，我觉得JDBC还是很不错的，它让我更深层次的了解了数据的操作，今天我将我写过的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
*/
public class BaseDAO implements IBaseDAO {
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 filedList = new ArrayList();
//获取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 filedList = new ArrayList();
//获取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 pageUtil) {
E e = pageUtil.getEntity();
//获取表名
String tableName = configUtil.getVal(cls.getName());
//获取查询条件
Map 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 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 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 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 paramMap, String tableName) {
StringBuffer sql = new StringBuffer();
sql.append("select * from ")
.append(tableName)
.append(" where 1 = 1 and status='0' ");
List columlist = getTableColumns(tableName);
for (Entry 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 getParamMap(E e) {
Map paramMap = new LinkedHashMap();
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 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 filedList) {
StringBuffer sql = new StringBuffer();
sql.append("update ").append(tableName).append(" set ");
List 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 pageUtil, String sql, Map 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 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 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 filedList) {
StringBuffer sql = new StringBuffer();
sql.append("insert into ").append(tableName).append(" (");
List 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 getTableColumns(String tableName) {
List columnList = new ArrayList();
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 getEntityList(ResultSet rs) throws Exception {
List list = new ArrayList();
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;
}
}
