JDBC工具类

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.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class DBUtil{ 
//定义静态属性     driver url uname upwd
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/video";
private static String uname = "root";
private static String upwd = "root";
//连接数据库
public static Connection getConn() throws ClassNotFoundException, SQLException{
Class.forName(driver);
return DriverManager.getConnection(url, uname, upwd);
}//getConn()
//增删改方法  exeUpdate(String sql, Object...params)
public static void exeUpdate(String sql, Object...params) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
System.out.println("\n-->DBUtil....连接数据库");
conn = getConn();
pstmt = conn.prepareStatement(sql);
if(null != params && params.length > 0){
for(int i = 0;i < params.length;i++){
pstmt.setObject(i + 1, params[i]);
}
pstmt.executeUpdate();
}
System.out.println("-->DBUtil....执行成功");
} catch (Exception e) {
System.out.println("-->DBUtil....执行失败");
e.printStackTrace();
throw new RuntimeException(e.getMessage(), e.getCause());
}finally{
//关闭数据库
System.out.println("-->DBUtil....关闭数据库");
close(conn, pstmt, null);
}
}//exeUpdate()
//查询方法  exeQuery(String sql, Class cls, Object...params)
public static List exeQuery(String sql, Class cls, Object...params){
List<Object> lstResult = new ArrayList<Object>(); //结果集列表objResultSet
List<Method> setMethod = new ArrayList<Method>(); //set方法列表
List<Method> getMethod = new ArrayList<Method>(); //set方法列表
for(Method mtd : cls.getMethods()){
if(mtd.getName().startsWith("set")){
setMethod.add(mtd);
}
}//for //获取并添加set方法到方法列表
for(Method mtd : cls.getMethods()){
if(mtd.getName().startsWith("get")){
getMethod.add(mtd);
}
}//for //获取并添加set方法到方法列表
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
System.out.println("\n-->DBUtil....连接数据库");
conn = getConn();
pstmt = conn.prepareStatement(sql);
if(null != params && params.length > 0){
for(int i = 0;i < params.length;i++){
pstmt.setObject(i + 1, params[i]);
}//for()
}//if()
rs = pstmt.executeQuery();
//循环添加查询的结果到结果集列表objResult
System.out.println("-->DBUtil....循环添加查询的结果到结果集列表objResult");
while(rs.next()){
Object objClass = cls.newInstance();
for(Method mtd : setMethod){
Object objRsValue = getResultValue(mtd, rs);
mtd.invoke(objClass, objRsValue);
}
lstResult.add(objClass);
}//while()
System.out.println("-->DBUtil....exeQuery()查询的结果：");
for(Object obj : lstResult){
for(Method mtd : getMethod){
System.out.print("\t    " + mtd.invoke(obj));
}
System.out.println();
}//控制台输出查询结果
System.out.println();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage(),e.getCause());
}finally{
System.out.println("-->DBUtil....关闭数据库");
close(conn, pstmt, rs);
}
return lstResult;
}//exeQuery()
//exeQuereyUnique()
public static Object exeQuereyUnique(String sql,Object...params){
Object obj = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
System.out.println("\n-->DBUtil....连接数据库");
conn = getConn();
pstmt = conn.prepareStatement(sql);
if(null != params && params.length > 0){
for(int i = 0;i < params.length;i++){
pstmt.setObject(i + 1, params[i]);
}//for()
}//if()
rs = pstmt.executeQuery();
//循环添加查询的结果到结果集列表objResult
if(rs.next()){
obj = rs.getObject(1);
}//while()
System.out.println("-->DBUtil....exeQuereyUnique()查询的结果：");
System.out.println(obj);
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage(),e.getCause());
}finally{
System.out.println("-->DBUtil....关闭数据库");
close(conn, pstmt, rs);
}
return obj;
}//exeQuereyUnique()
//获取结果的某一个属性
private static Object getResultValue(Method mtd, ResultSet rs) throws SQLException{
String column = mtd.getName().substring(3);
Class paramType = mtd.getParameterTypes()[0];
if(paramType == int.class) return rs.getInt(column);
if(paramType == String.class) return rs.getString(column);
if(paramType == Object.class) return rs.getObject(column);
if(paramType == Date.class) return rs.getDate(column);
return null;
}//getResultValue(Method m, ResultSet rs)
//关闭数据库
public static void close(Connection conn, Statement pstmt, ResultSet rs){
if(null != rs){
try {rs.close();} catch (SQLException e) {e.printStackTrace();}finally{rs = null;}
}//关闭结果集
if(null != pstmt){
try {pstmt.close();} catch (SQLException e) {e.printStackTrace();}finally{pstmt = null;}
}//关闭 Statement 对象
if(null != conn){try {conn.close();} catch (SQLException e) {e.printStackTrace();}finally{conn = null;}
}//关闭 Connection 对象
}//close()
}//DBUtil类