package Dao;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class BaseDao {
static MysqlDataSource ds;
static{
ds = new MysqlDataSource();
ds.setServerName("localhost");
ds.setDatabaseName("company");
ds.setUser("root");
ds.setPassword("123456");
ds.setPort(3306);
}
protected Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/**
* 关闭查询连接
* @param rs
* @param st
* @param c
*/
protected void closeResource(ResultSet rs) {
try {
Statement st = rs.getStatement();
Connection c = st.getConnection();
rs.close();
st.close();
c.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 关闭增删改连接
* @param rs
* @param st
* @param c
*/
protected void closeResource(ResultSet rs,Statement st,Connection c) {
try {
if (rs!=null){rs.close();}
if (st!=null){st.close();}
if (c!=null){c.close();}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 执行增删改
* */
public int executeUpdate(String sql,Object...params){
Connection c = null;
PreparedStatement pst = null;
int ret = 0;
try {
c = getConnection();
pst = c.prepareStatement(sql);
if (params!=null && params.length>0){
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1, params[i]);
}
}
ret = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
closeResource(null,pst,c);
}
return ret;
}
/**
* 执行查询
* @param sql
* @param params
* @return
*/
public ResultSet executeQuery(String sql,Object...params){
Connection c = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
c = getConnection();
pst = c.prepareStatement(sql);
if (params!=null && params.length>0){
for (int i = 0; i < params.length; i++) {
pst.setObject(i+1, params[i]);
}
}
rs = pst.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
/**
* @param entityClass实体类 (类.class)
* @param sqlSQL语句 (String)
* @param params替换参数
* @return
*/
public List<Object> executeQuery(Class entityClass,String sql,Object...params){
ResultSet rs = null;
List<Object> list = null;
try {
rs = executeQuery(sql,params);
//通过反射封装对象
if (rs!=null){
list = new ArrayList<Object>();
while(rs.next()){
Object entity = entityClass.newInstance();//执行该方法默认会调用类的无参构造方法
/*Field应该是字段*/
for(Field f: entityClass.getDeclaredFields()){
f.setAccessible(true);/*所有字段包括私有的*/
for(int i=1; i<=rs.getMetaData().getColumnCount();i++){/*循环查询得到的数据,得到列名*/
/* System.out.println("/r/t------------------"+rs.getMetaData().getColumnName(i).toLowerCase()+"/r/t------------------");
*/if (f.getName().toLowerCase().equals(rs.getMetaData().getColumnName(i).toLowerCase())){
/*列名如果与实体的属性相同,就赋值*/
f.set(entity, rs.getObject(i));
/*System.out.print(rs.getObject(i));*/
}
}
}
list.add(entity); /*添加实体到集合*/
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeResource(rs);
}
return list;
}
}