import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.bjsxt.wuye.util.DBUtil;
import com.bjsxt.wuye.util.GetNameUtil;
import com.bjsxt.wuye.util.PageUtil;
@SuppressWarnings("unchecked")
public class BaseDao{
/**
* 拼接oracle 分页sql语句
* @param sql
* @return
*/
public String getOraclePageSql(String sql){
StringBuffer sqlBuffer = new StringBuffer("select * from (select a_tab_01.*,rownum a_row_num_r from (");
sqlBuffer.append(sql);
sqlBuffer.append(" ) a_tab_01 where rownum<=?) b_tab_02 where a_row_num_r>=?");
return sqlBuffer.toString();
}
/**
* 单表 等值查询 分页
* @param objVo
* @param pageNumStr
* @param pageSize
* @return
*/
public PageUtil queryListByPage(Object objVo,String pageNumStr,String pageSize)
throws ClassNotFoundException, SQLException, SecurityException,
IllegalArgumentException, InstantiationException,
IllegalAccessException, NoSuchFieldException,
NoSuchMethodException, InvocationTargetException{
List sqlAndParamList = getSqlAndParam(objVo, "");
if(sqlAndParamList != null&&sqlAndParamList.size()>0){
StringBuffer sql = (StringBuffer) sqlAndParamList.get(0);
List paramList = (List) sqlAndParamList.get(1);
return queryListByPage(sql.toString(),objVo.getClass(),pageNumStr,pageSize,paramList.toArray());
}
return null;
}
/**分页查询
* @param sql
* @param c
* @param pageNumStr
* @param pageSize
* @param param
* @return
*/
public PageUtil queryListByPage(String sql,Class c,String pageNumStr,String pageSize,Object...param)
throws ClassNotFoundException, SQLException, SecurityException,
IllegalArgumentException, InstantiationException,
IllegalAccessException, NoSuchFieldException,
NoSuchMethodException, InvocationTargetException{
if(c!=null){
String totalSql = "select count(*) from "+sql.split("from")[1];//获取统计记录总数的sql语句
int totalCount = getTotalCount(totalSql,param);
//创建 PageUtil对象
PageUtil pageUtil = new PageUtil(pageSize,pageNumStr,totalCount);
//获取连接
Connection conn = DBUtil.getConn();
//获取Oracle 分页的查询语句
sql = getOraclePageSql(sql);
//预处理sql语句
PreparedStatement ps = conn.prepareStatement(sql);
//填坑
setPsValue(ps, param);
//把分页条件 从第几条记录到第几条记录数据补充到PreparedStatement对象
ps.setObject(param==null?1:param.length+1, pageUtil.getRowEnd());
ps.setObject(param==null?2:param.length+2, pageUtil.getRowStart());
//获取结果集
ResultSet rs = ps.executeQuery();
//将结果集放入vo,再放入list容器
List resultList = addList(rs, c);
//关闭对象
DBUtil.close(conn, ps, rs);
pageUtil.setList(resultList);
return pageUtil;
}else{
System.out.println("反射对象不能为null");
return null;
}
}
/**
* 查询并返回对象结果集
* @param sql 输入的sql语句
* @param c 相对应的类
* @param param 准备填坑的参数
* @return 返回查询到的对象结果集List
*/
public List queryList(String sql,Class c,Object...param) throws ClassNotFoundException,
SQLException, InstantiationException, IllegalAccessException,
SecurityException, NoSuchFieldException, NoSuchMethodException,
IllegalArgumentException, InvocationTargetException {
if(c != null){
//第一步: 获取连接
Connection conn = DBUtil.getConn();
//第二步: sql 语句 挖坑 预处理sql语句
PreparedStatement ps = conn.prepareStatement(sql);
//第三步: 给sql语句填坑
setPsValue(ps,param);
//第四步: 执行查询
ResultSet rs = ps.executeQuery();
//根据输入的sql语句和对应的填坑参数返回查询到的对象List
List resultList = addList(rs, c);
DBUtil.close(conn, ps, rs); //关闭与数据库的链接
return resultList;
}else{
System.out.println("反射对象不能为null");
return null;
}
}
/**
* 填坑
* @param ps 预处理对象
* @param param 准备填坑的参数
*/
private void setPsValue(PreparedStatement ps,Object...param) throws SQLException{
if(param !=null&¶m.length>0){
for(int i = 0;i<param.length;i++){
ps.setObject(i+1,param[i]);
}
}
}
/**
* 查询并返回对象结果集
* @param rs 查询到的结果集
* @param c 结果集要赋值给的相关类
* @return 返回查询到的对象List
*/
public List addList(ResultSet rs,Class c) throws SQLException, InstantiationException,
IllegalAccessException, SecurityException, NoSuchFieldException, NoSuchMethodException, IllegalArgumentException, InvocationTargetException{
List resultList = new ArrayList();
//遍历rs中的结果集
while(rs.next()){
//把rs结果集赋给对应的类对象,然后获取相对应的类对象
Object obj = setObjectFieldValue(rs,c);
resultList.add(obj);
}
return resultList;
}
/**
* 给vo对象属性赋值
* @param rs 查询到的结果集
* @param c 结果集要插入的对应类
* @return 获取对应的类对象
*/
private Object setObjectFieldValue(ResultSet rs,Class c) throws SQLException, SecurityException, NoSuchFieldException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException, InstantiationException{
ResultSetMetaData rsmd = rs.getMetaData(); //获取rs中的元数据
int colCount = rsmd.getColumnCount(); //获取rs中有多少列
Object obj = c.newInstance(); //创建vo对象
for(int i = 1;i<=colCount;i++){
String colName = rsmd.getColumnName(i); //获取sql语句中字段名
if(!colName.toLowerCase().equals("a_row_num_r")){
String fieldName = GetNameUtil.getFieldNameByColName(colName); //根据sql语句中字段名获取属性名
String setter = GetNameUtil.getSetter(fieldName); //通过属性名获取设定器名
Field field = c.getDeclaredField(fieldName); //获取VO类中的属性名
//获取设定器对应的映射方法
Method m = c.getDeclaredMethod(setter, field.getType());
Object value = rs.getObject(i); //获取rs中查询到和属性名对应的值
//调用设定器
invokeSetter(value,field,m,obj);
}
}
return obj;
}
/**
* 不同类型下调用设定器
* @param value 属性名对应的值
* @param field 属性名
* @param m 和属性吗对应的方法
* @param obj 和属性名对应的类对象
*/
private void invokeSetter(Object value,Field field,Method m,Object obj) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException{
if(value !=null){
if(value.getClass()==BigDecimal.class){//判断 字段结果类型是否是BigDecimal
BigDecimal bd = (BigDecimal)value; //强制类型转换
if(field.getType()==Integer.class||field.getType()==int.class){//属性类型是Integer
m.invoke(obj, bd.intValue());
}else if(field.getType()==Double.class||field.getType()==double.class){
m.invoke(obj, bd.doubleValue());
}else if(field.getType()==Float.class||field.getType()==float.class){
m.invoke(obj, bd.floatValue());
}else if(field.getType()==Byte.class||field.getType()==byte.class){
m.invoke(obj, bd.byteValue());
}else if(field.getType()==long.class||field.getType()==Long.class){
m.invoke(obj, bd.longValue());
}else if(field.getType()==Short.class||field.getType()==short.class){
m.invoke(obj, bd.shortValue());
}
}else{
m.invoke(obj,value);
}
}
}
/**
* 根据条件进行单表查询
* @param objVo 类对象
* @return 得到查询到的对象结果集
*/
public List queryList(Object objVo) throws SecurityException, NoSuchMethodException,
IllegalArgumentException, IllegalAccessException,
InvocationTargetException, ClassNotFoundException,
SQLException, InstantiationException, NoSuchFieldException{
//获取sql语句和参数聚合集
List sqlAndParamList = getSqlAndParam(objVo,"");
if(sqlAndParamList != null&&sqlAndParamList.size()>0){
String sql = sqlAndParamList.get(0).toString();
List paramList = (List) sqlAndParamList.get(1);
//调用 查询结果集的方法
return queryList(sql,objVo.getClass(),paramList.toArray());
}
return null;
}
/**
* 获取属性值
* @param field 属性名
* @param objVo 属性名对应类对象
* @return 得到获取的属性值
*/
private Object getFieldValue(Field field,Object objVo) throws SecurityException,
NoSuchMethodException, IllegalArgumentException,
IllegalAccessException, InvocationTargetException{
String getter = GetNameUtil.getGetter(field); //获取取值器名
Method m = objVo.getClass().getDeclaredMethod(getter); //获取取值器
Object value = m.invoke(objVo);
return value;
}
/**
* 获取sql语句和参数聚合集
* @param objVo sql语句对应的类对象
* @return 得到sql语句和参数聚合集
*/
private List getSqlAndParam(Object objVo,String remark) throws SecurityException,
NoSuchMethodException, IllegalArgumentException,
IllegalAccessException, InvocationTargetException{
List resultList = new ArrayList();
List paramList = new ArrayList();
Class c = objVo.getClass(); //根据对象名获取类名
String tabName = GetNameUtil.getTabOrColName(c.getSimpleName()); //根据类名获取表明
String sqlPre = remark.equals("totalCount")?"select count(*) from ":"select * from ";
StringBuffer sql = new StringBuffer(sqlPre+" "+tabName+" t where 1=1");
//获取 参数对象属性名数组
Field[] fieldArray = c.getDeclaredFields();
//遍历属性名数组获取每个属性值
for(int i = 0;i<fieldArray.length;i++){
Object value = getFieldValue(fieldArray[i],objVo); //获取属性值
if(value!=null){
if(value.getClass()!=String.class){
sql.append(" and t."+GetNameUtil.getTabOrColName(fieldArray[i].getName())+"=?"); //获取列明
paramList.add(value);
}else{
if(!((String)value).trim().equals("")){
sql.append(" and t."+GetNameUtil.getTabOrColName(fieldArray[i].getName())+"=?"); //获取列明
paramList.add(value); //把参数置入参数聚合集
}
}
}
}
resultList.add(sql); //得到sql语句
resultList.add(paramList); //将参数聚合集放入结果聚合集中
return resultList;
}
/**
* 获取记录总数
* @param sql 输入的sql语句
* @param param 参数集
* @return 得到记录总数
*/
private int getTotalCount(String sql,Object...param) throws ClassNotFoundException, SQLException{
//获取连接
Connection conn = DBUtil.getConn();
//预处理sql语句
PreparedStatement ps = conn.prepareStatement(sql);
//填坑
setPsValue(ps, param);
//获取rs结果集
ResultSet rs = ps.executeQuery();
//获取记录总数
int count = rs.next()?rs.getInt(1):0;
DBUtil.close(conn, ps, rs);
return count;
}
/**
* 获取记录总数
* @param objVo 类对象
* @return 得到查询记录数
*/
public int getTotalCount(Object objVo) throws SecurityException, IllegalArgumentException,
NoSuchMethodException, IllegalAccessException,
InvocationTargetException, ClassNotFoundException, SQLException{
List sqlAndParamList = getSqlAndParam(objVo,"totalCount"); //获取sql语句和参数聚合集
if(sqlAndParamList != null&&sqlAndParamList.size()>0){
String sql = sqlAndParamList.get(0).toString();
List paramList = (List) sqlAndParamList.get(1);
//调用 查询结果集的方法
return getTotalCount(sql,paramList.toArray());
}
return 0;
}
/**
* 根据Id获取单条记录
* @param objVo 对象
* @return 得到查询到的对象
*/
public Object queryObjectById(Object objVo) throws SecurityException, IllegalArgumentException,
NoSuchMethodException, IllegalAccessException,
InvocationTargetException, ClassNotFoundException,
SQLException, InstantiationException,
NoSuchFieldException{
List list = queryList(objVo); //查询到对应的对象结果集
if(list!=null&&list.size()>0){
return list.get(0);
}
return null;
}
/**
* 向数据库中插入数据
* @param objVo 对应 插入数据的类对象
* @param preKeyField sql语句中的对应相关表的主键
*/
public void addVo(Object objVo,String preKeyField) throws SecurityException,
NoSuchMethodException, IllegalArgumentException, IllegalAccessException,
InvocationTargetException, ClassNotFoundException, SQLException{
//创建装参数的集合
List paramList = new ArrayList();
//获取反射对象
Class c = objVo.getClass();
String tabName = GetNameUtil.getTabOrColName(c.getSimpleName()); //获取表名
StringBuffer sql = new StringBuffer("insert into "+tabName+" values(");
//获取类对象中的属性名数组
Field[] fieldArray = c.getDeclaredFields();
//遍历属性名
for(int i = 0;i<fieldArray.length;i++){
String fieldName = fieldArray[i].getName();
if(!fieldName.equals(preKeyField)){
sql.append("?,");
Object value = getFieldValue(fieldArray[i], objVo); //获取属性值
paramList.add(value); //把属性值装入参数聚合集
} else{
sql.append("seq_"+tabName+".nextval,"); //获取sql语句中的递增值
}
}
sql.deleteCharAt(sql.length()-1); //去掉sql语句中最后一个逗号
sql.append(")");
//添加数据
modifyVo(sql.toString(),paramList.toArray());
}
/**
* 更新数据库相关记录
* @param objVo 要修改的vo对象
* @param preKeyField 必写 主键对应的属性名
*/
public void updateVo(Object objVo,String... preKeyFields) throws SecurityException,
IllegalArgumentException, NoSuchMethodException,
IllegalAccessException, InvocationTargetException, NoSuchFieldException,
ClassNotFoundException, SQLException{
List paramList = new ArrayList(); //创建装参数的集合
Class c = objVo.getClass(); //获取反射对象
String tabName = GetNameUtil.getTabOrColName(c.getSimpleName()); //获取表名
//拼接sql
StringBuffer sql = new StringBuffer("update "+tabName+" t set ");
Field[] fieldArray = c.getDeclaredFields(); //获取属性名
//遍历属性名数组
for(int i = 0;i<fieldArray.length;i++){
String fieldName = fieldArray[i].getName();
int j =0 ;
for (j = 0; j < preKeyFields.length; j++) {
if(fieldName.equals(preKeyFields[j])){
break;
}
}
Object value = getFieldValue(fieldArray[i], objVo); //获取属性值
if(j>=preKeyFields.length && null != value && !"".equals(value)){
sql.append("t."+GetNameUtil.getTabOrColName(fieldName)+"=? ,");
paramList.add(value); //将得到的属性值装入参数聚合集
}
}
sql.deleteCharAt(sql.length()-1);
sql.append(" where t."+GetNameUtil.getTabOrColName(preKeyFields[0])+"=? ");
for(int i=1; i<preKeyFields.length;i++){
sql.append(" and "+GetNameUtil.getTabOrColName(preKeyFields[i])+"=? ");
}
System.out.println(sql);
//根据传进来的主键名字 获取主键对应的属性名
for(String preKeyField:preKeyFields){
Field preKeyFieldObj = c.getDeclaredField(preKeyField);
//获取主键对应的属性值
Object keyValue = getFieldValue(preKeyFieldObj, objVo);
//将主键的值装入参数聚合
paramList.add(keyValue);
//修改信息
}
modifyVo(sql.toString(),paramList.toArray());
}
/**
* 删除数据库单条记录
* @param objVo 装有主键参数值的对象
* @param preKeyField 主键对应的名字
*/
public void deleteVo(Object objVo,String preKeyField) throws SecurityException, NoSuchFieldException,
IllegalArgumentException, NoSuchMethodException,
IllegalAccessException, InvocationTargetException, ClassNotFoundException, SQLException{
//获取反射对象
Class c = objVo.getClass();
String tabName = GetNameUtil.getTabOrColName(c.getSimpleName());//获取表名
//拼接sql
StringBuffer sql = new StringBuffer("delete from "+tabName+" t where t. "+GetNameUtil.getTabOrColName(preKeyField)+"=?");
//根据传进来的主键名字 获取主键对应的对像中的属性名
Field preKeyFieldObj = c.getDeclaredField(preKeyField);
//获取主键对应的属性值
Object keyValue = getFieldValue(preKeyFieldObj, objVo);
//删除单条记录
modifyVo(sql.toString(),keyValue);
}
/**
* 增删改公用的方法
* @param sql 输入的sql语句
* @param param 参数聚合集
*/
private void modifyVo(String sql,Object...param) throws ClassNotFoundException, SQLException{
//获取连接
Connection conn = DBUtil.getConn();
//预处理sql语句
PreparedStatement ps = conn.prepareStatement(sql);
//填坑
setPsValue(ps, param);
//执行
ps.executeUpdate();
//关闭
DBUtil.close(conn, ps, null);
}
}