import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public final class BaseDAO{
/**
* ------------- Oracle ---------------
* DB_DRIVER ="oracle.jdbc.driver.OracleDriver";
* DB_URL ="jdbc:oracle:thin:@localhost:1521:admin";
* DB_USERNAME ="root";
* DB_PASSWORD ="root";
* ------------------------------------
*/
/**
* ------------- Sql Server ---------------
* DB_DRIVER ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
* DB_URL ="jdbc:sqlserver://localhost:1433;DatabaseName=bookinfo";
* DB_USERNAME ="root";
* DB_PASSWORD ="root";
* ----------------------------------------
*/
private static final String DB_DRIVER ="com.mysql.jdbc.Driver";
private static final String DB_URL ="jdbc:mysql://localhost:3306/student";
private static final String DB_USERNAME ="root";
private static final String DB_PASSWORD ="root";
private static Connection getConnection() throws SQLException{
try {
Class.forName(DB_DRIVER);
return DriverManager.getConnection(DB_URL, DB_USERNAME,
DB_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
throw new RuntimeException("无法找到对应的数据库驱动类");
}
}
//增删改
public static void executeUpdate(String sql,Object[] args){
Connection con=null;
PreparedStatement ps=null;
System.out.println("准备执行："+sql);
try {
con=getConnection();
ps= con.prepareStatement(sql);
if(args!=null&&args.length>0){
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
}
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("SQL语句执行失败:"+sql);
}finally{
closeAll(con,ps,null);
}
}
//查询
public static <T> List<T> executeQuery(Class<T> entityClass,String sql,Object[] args){
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
System.out.println("准备执行："+sql);
try {
con=getConnection();
ps= con.prepareStatement(sql);
if(args!=null&&args.length>0){
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
}
rs=ps.executeQuery();
List<T> list=new ArrayList<T>();
while(rs.next()){
T obj=fillDataFromResultSetToEntity(entityClass,rs);
list.add(obj);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("SQL语句执行失败:"+sql);
}finally{
closeAll(con,ps,rs);
}
}
private static<T> T fillDataFromResultSetToEntity(Class<T> entityClass, ResultSet rs)throws SQLException {
try {
//实例化对应实体类
T entity = entityClass.newInstance();
//获得该实体类的所有set方法
Method[] methods=getAllSetMethods(entityClass);
for(Method m:methods){
try{
//通过set方法名推算出属性名
String fieldName=m.getName().substring(3,4).toLowerCase()+m.getName().substring(4);
//获得要调用的ResultSet对象的方法名
String rsMethodName = getResultSetMethod(m.getParameterTypes()[0]);
//获得要调用的ResultSet对象的方法对象
Method rsMethod=rs.getClass().getMethod(rsMethodName,String.class);
//调用rs.getXXX方法获得对应列的数据
Object fieldValue=rsMethod.invoke(rs,fieldName);
//调用实体对象的set方法设值
m.invoke(entity,fieldValue);
}catch (SecurityException e) {
System.out.println("[警告]:"+entityClass.getName()+"."+m.getName()+"无法访问，跳过..");
continue;
} catch (IllegalArgumentException e) {
System.out.println("[警告]:"+entityClass.getName()+"."+m.getName()+"("+m.getParameterTypes()[0].getName()+")参数错误");
continue;
} catch (NoSuchMethodException e) {
System.out.println("[警告]:找不到方法"+entityClass.getName()+"."+m.getName()+"("+m.getParameterTypes()[0].getName()+")");
continue;
} catch (InvocationTargetException e) {
System.out.println("[警告]:调用"+entityClass.getName()+"."+m.getName()+"("+m.getParameterTypes()[0].getName()+")方法产生内部异常");
continue;
}
}
return entity;
} catch (InstantiationException e) {
e.printStackTrace();
throw new RuntimeException("无法实例化" + entityClass.getName());
} catch (IllegalAccessException e) {
e.printStackTrace();
throw new RuntimeException("没有或无法访问["+entityClass.getName()+"]的无参构造函数");
}
}
private static Method[] getAllSetMethods(Class<?> entityClass){
Method[] methods=entityClass.getMethods();
List<Method> list=new ArrayList<Method>();
for(Method m:methods){
if(m.getName().indexOf("set")==0){
list.add(m);
}
}
return list.toArray(new Method[list.size()]);
}
private static String getResultSetMethod(Class<?> fieldType) {
String rsMethodName = null;
if (String.class.equals(fieldType)) {
rsMethodName = "getString";
}
if (Long.class.equals(fieldType) || "long".equals(fieldType.getName())) {
rsMethodName = "getLong";
}
if (Date.class.equals(fieldType)) {
rsMethodName = "getDate";
}
if (Integer.class.equals(fieldType)
|| "int".equals(fieldType.getName())) {
rsMethodName = "getInt";
}
if (Double.class.equals(fieldType)
|| "double".equals(fieldType.getName())) {
rsMethodName = "getDouble";
}
if (Float.class.equals(fieldType)
|| "float".equals(fieldType.getName())) {
rsMethodName = "getFloat";
}
if (rsMethodName == null) {
throw new RuntimeException("无法识别的属性类型:" + fieldType.getName()
+ "目前支持的属性类型有[String,Integer,Long,Double,Float,Date]");
}
return rsMethodName;
}
private static void closeAll(Connection con, PreparedStatement ps,ResultSet rs) {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("关闭数据集错误");
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("关闭数据访问对象错误");
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException("关闭数据库连接错误");
}
}
}
}