package com.yucheng.dao;
import java.lang.reflect.InvocationTargetException;
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.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
/**
* DAO: database access object
* 访问数据信息的类
* 包含了对数据的增加,删除和修改
*
* @author 石丽丽
*/
public class DAO {
/**
* 对数据的增加、删除和修改
* @param sql
* @param args
*/
public void update(String sql,Object ... args){
Connection conn=null;
PreparedStatement preStatement=null;
try {
conn=JDBCTools.getConnecition();
preStatement=conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preStatement.setObject(i+1, args[i]);
}
preStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, null);
}
}
/**
*通过结果集,得到ColumnLables的集合
* @param resultSet
* @return
* @throws Exception
*/
private List<String> getColumnLables(ResultSet resultSet) throws Exception{
ResultSetMetaData rsmd=resultSet.getMetaData();
List<String> columnLables=new ArrayList<String>();
for(int i=0;i<rsmd.getColumnCount();i++){
String columnLable=rsmd.getColumnLabel(i+1);
columnLables.add(columnLable);
}
return columnLables;
}
/**
* 查询一条记录,返回对应的对象
* @param clazz
* @param sql
* @param obj
* @return
*/
public <T> T get(Class<T> clazz,String sql,Object ... args){
List<T> result=getForList(clazz, sql, args);
if(result.size()>0){
return result.get(0);
}
return null;
}
/**
* 查询多条记录记录,返回对应的对象的集合
* @param clazz
* @param sql
* @param args
* @return
*/
public <T> List<T> getForList(Class<T> clazz,String sql,Object ... args){
List<T> list =new ArrayList<T>();
Connection conn =null;
PreparedStatement preStatement=null;
ResultSet resultSet=null;
try {
//1,得到结果街
conn=JDBCTools.getConnecition();
preStatement=conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preStatement.setObject(i+1, args[i]);
}
resultSet =preStatement.executeQuery();
//2,处理结果集,得到MapList
List<Map<String, Object>> values = handleResultSetToMapList(resultSet);
//3,将MapList转换为BeanList
list=transfterMapListToBeanList(clazz,values);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, resultSet);
}
return list;
}
private <T> List<T> transfterMapListToBeanList(Class<T> clazz,
List<Map<String, Object>> values) throws Exception {
List<T> list=new ArrayList<T>();
T bean=null;
if(values.size()>0){
for(Map<String, Object> value:values){
bean=clazz.newInstance();
for(Map.Entry<String, Object> entry:value.entrySet()){
String fieldName=entry.getKey();
Object fieldValue=entry.getValue();
BeanUtils.setProperty(bean,fieldName, fieldValue);
}
list.add(bean);
}
}
return list;
}
/**
* 处理结果集,得到MapList对象
* @param resultSet
* @return
* @throws Exception
* @throws SQLException
*/
private List<Map<String, Object>> handleResultSetToMapList(
ResultSet resultSet) throws Exception, SQLException {
Map<String, Object> map=null;
List<Map<String,Object>> values=new ArrayList<Map<String,Object>>();
List<String> columnLables=getColumnLables(resultSet);
while(resultSet.next()){
map=new HashMap<String, Object>();
for(String columnLable :columnLables){
Object fieldValue=resultSet.getObject(columnLable);
map.put(columnLable, fieldValue);
}
values.add(map);
}
return values;
}
/**
* 返回莫条记录的某个值或者一个统计的值
* @param sql
* @param args
* @return
*/
public<E> E getForValues(String sql,Object ... args){
//1,得到结果集
Connection conn=null;
PreparedStatement preStatement =null;
ResultSet resultSet=null;
try {
conn=JDBCTools.getConnecition();
preStatement=conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preStatement.setObject(i+1, args[i]);
}
resultSet=preStatement.executeQuery();
if(resultSet.next()){
return (E) resultSet.getObject(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, resultSet);
}
return null;
}
}
import java.lang.reflect.InvocationTargetException;
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.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
/**
* DAO: database access object
* 访问数据信息的类
* 包含了对数据的增加,删除和修改
*
* @author 石丽丽
*/
public class DAO {
/**
* 对数据的增加、删除和修改
* @param sql
* @param args
*/
public void update(String sql,Object ... args){
Connection conn=null;
PreparedStatement preStatement=null;
try {
conn=JDBCTools.getConnecition();
preStatement=conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preStatement.setObject(i+1, args[i]);
}
preStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, null);
}
}
/**
*通过结果集,得到ColumnLables的集合
* @param resultSet
* @return
* @throws Exception
*/
private List<String> getColumnLables(ResultSet resultSet) throws Exception{
ResultSetMetaData rsmd=resultSet.getMetaData();
List<String> columnLables=new ArrayList<String>();
for(int i=0;i<rsmd.getColumnCount();i++){
String columnLable=rsmd.getColumnLabel(i+1);
columnLables.add(columnLable);
}
return columnLables;
}
/**
* 查询一条记录,返回对应的对象
* @param clazz
* @param sql
* @param obj
* @return
*/
public <T> T get(Class<T> clazz,String sql,Object ... args){
List<T> result=getForList(clazz, sql, args);
if(result.size()>0){
return result.get(0);
}
return null;
}
/**
* 查询多条记录记录,返回对应的对象的集合
* @param clazz
* @param sql
* @param args
* @return
*/
public <T> List<T> getForList(Class<T> clazz,String sql,Object ... args){
List<T> list =new ArrayList<T>();
Connection conn =null;
PreparedStatement preStatement=null;
ResultSet resultSet=null;
try {
//1,得到结果街
conn=JDBCTools.getConnecition();
preStatement=conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preStatement.setObject(i+1, args[i]);
}
resultSet =preStatement.executeQuery();
//2,处理结果集,得到MapList
List<Map<String, Object>> values = handleResultSetToMapList(resultSet);
//3,将MapList转换为BeanList
list=transfterMapListToBeanList(clazz,values);
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, resultSet);
}
return list;
}
private <T> List<T> transfterMapListToBeanList(Class<T> clazz,
List<Map<String, Object>> values) throws Exception {
List<T> list=new ArrayList<T>();
T bean=null;
if(values.size()>0){
for(Map<String, Object> value:values){
bean=clazz.newInstance();
for(Map.Entry<String, Object> entry:value.entrySet()){
String fieldName=entry.getKey();
Object fieldValue=entry.getValue();
BeanUtils.setProperty(bean,fieldName, fieldValue);
}
list.add(bean);
}
}
return list;
}
/**
* 处理结果集,得到MapList对象
* @param resultSet
* @return
* @throws Exception
* @throws SQLException
*/
private List<Map<String, Object>> handleResultSetToMapList(
ResultSet resultSet) throws Exception, SQLException {
Map<String, Object> map=null;
List<Map<String,Object>> values=new ArrayList<Map<String,Object>>();
List<String> columnLables=getColumnLables(resultSet);
while(resultSet.next()){
map=new HashMap<String, Object>();
for(String columnLable :columnLables){
Object fieldValue=resultSet.getObject(columnLable);
map.put(columnLable, fieldValue);
}
values.add(map);
}
return values;
}
/**
* 返回莫条记录的某个值或者一个统计的值
* @param sql
* @param args
* @return
*/
public<E> E getForValues(String sql,Object ... args){
//1,得到结果集
Connection conn=null;
PreparedStatement preStatement =null;
ResultSet resultSet=null;
try {
conn=JDBCTools.getConnecition();
preStatement=conn.prepareStatement(sql);
for(int i=0;i<args.length;i++){
preStatement.setObject(i+1, args[i]);
}
resultSet=preStatement.executeQuery();
if(resultSet.next()){
return (E) resultSet.getObject(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCTools.release(preStatement, conn, resultSet);
}
return null;
}
}