import java.lang.reflect.*;import java.sql.*;import java.util.*;public classSqlHelper {//SQL Server
/*** JDBC驱动名称*/
public static final String CLASS_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";/*** 数据库连库字符串*/
public static final String URL = "jdbc:sqlserver://192.168.63.109;databaseName=HTTPCollection";/*** 用户名*/
public static final String UID = "sa";/*** 密码*/
public static final String PWD = "jxjxjx123";/*** JDBC驱动类型*/
public static Class CLS = null;//Oracle//public static final String CLASS_NAME =//"oracle.jdbc.driver.OracleDriver";//public static final String URL =//"jdbc:oracle:thin:@localhost:1522:accp11g";//public static final String UID = "system";//public static final String PWD = "manager";
/*** 获取数据库连接对象
*
*@return*@throwsClassNotFoundException
*@throwsSQLException*/
public static Connection getConnection() throwsClassNotFoundException,
SQLException {if (CLS == null) {
CLS=Class.forName(CLASS_NAME);
}returnDriverManager.getConnection(URL, UID, PWD);
}/*** 执行SQL语句不返回查询的操作,返回受影响的行数
*
*@paramsql
* SQL语句
*@return受影响的行数
*@throwsClassNotFoundException
*@throwsSQLException*/
public static intexecuteNonQuery(String sql) {int result = -1;
Connection con= null;
PreparedStatement ps= null;try{
con=getConnection();
ps=con.prepareStatement(sql);
result=ps.executeUpdate();
}catch(Exception e) {
e.printStackTrace();
}finally{
close(con, ps,null);
}returnresult;
}/*** 执行Insert语句,返回Insert成功之后标识列的值
*
*@paramsql
*@return*@throwsClassNotFoundException
*@throwsSQLException*/
public static intexecuteIdentity(String sql) {int identity = -1;
Connection con= null;
Statement ps= null;
ResultSet rs= null;try{
con=getConnection();
ps=con.createStatement();
ps.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
rs=ps.getGeneratedKeys();if(rs.next()) {//identity = rs.getInt("GENERATED_KEYS");
identity = rs.getInt(1);
}
}catch(Exception e) {
e.printStackTrace();
}finally{
close(con, ps,null);
}returnidentity;
}/*** 执行不返回结果集的存储过程
*
*@paramsql
* 存储过程名称
*@paramparams
* 存储过程参数
*@throwsClassNotFoundException
*@throwsSQLException*/
public static voidexecuteNonQuery(String sql, SqlParameter... params) {
Connection con= null;
CallableStatement cs= null;try{
con=getConnection();
cs=con.prepareCall(sql);
setSqlParameter(cs, params);
cs.executeUpdate();
getSqlParameter(cs, params);
}catch(Exception e) {
e.printStackTrace();
}finally{
close(con, cs,null);
}
}/*** 执行返回聚合函数的操作
*
*@paramsql
* 含有聚合函数的SQL语句
*@return聚合函数的执行结果
*@throwsSQLException
*@throwsClassNotFoundException*/
public static intexecuteScalar(String sql) {int result = -1;
Connection con= null;
PreparedStatement ps= null;
ResultSet rs= null;try{
con=getConnection();
ps=con.prepareStatement(sql);
rs=ps.executeQuery();if(rs.next()) {
result= rs.getInt(1);
}
}catch(Exception e) {
e.printStackTrace();
}finally{
close(con, ps, rs);
}returnresult;
}/*** 执行返回泛型集合的SQL语句
*
*@paramcls
* 泛型类型
*@paramsql
* 查询SQL语句
*@return泛型集合
*@throwsClassNotFoundException
*@throwsSQLException
*@throwsInstantiationException
*@throwsIllegalAccessException*/
public static List executeList(Classcls, String sql) {
List list = new ArrayList();
Connection con= null;
PreparedStatement ps= null;
ResultSet rs= null;try{
con=getConnection();
ps=con.prepareStatement(sql);
rs=ps.executeQuery();while(rs.next()) {
T obj=executeResultSet(cls, rs);
list.add(obj);
}
}catch(Exception e) {
e.printStackTrace();
}finally{
close(con, ps, rs);
}returnlist;
}/*** 执行返回泛型集合的存储过程
*
*@paramcls
* 泛型类型
*@paramsql
* 存储过程名称
*@paramparams
* 存储过程参数
*@return泛型集合
*@throwsClassNotFoundException
*@throwsSQLException
*@throwsInstantiationException
*@throwsIllegalAccessException*/
public static List executeList(Classcls, String sql,
SqlParameter... params) {
List list = new ArrayList();
Connection con= null;
CallableStatement cs= null;
ResultSet rs= null;try{
con=getConnection();
cs=con.prepareCall(sql);
setSqlParameter(cs, params);
rs=cs.executeQuery();while(rs.next()) {
T obj=executeResultSet(cls, rs);
list.add(obj);
}
}catch(Exception e) {
e.printStackTrace();
}finally{
close(con, cs, rs);
}returnlist;
}/*** 执行返回泛型类型对象的SQL语句
*
*@paramcls
* 泛型类型
*@paramsql
* SQL语句
*@return泛型类型对象
*@throwsSQLException
*@throwsClassNotFoundException
*@throwsInstantiationException
*@throwsIllegalAccessException*/
public static T executeEntity(Classcls, String sql) {
T obj= null;
Connection con= null;
PreparedStatement ps= null;
ResultSet rs= null;try{
con=getConnection();
ps=con.prepareStatement(sql);
rs=ps.executeQuery();while(rs.next()) {
obj=executeResultSet(cls, rs);break;
}
}catch(Exception e) {
e.printStackTrace();
}finally{
close(con, ps, rs);
}returnobj;
}/*** 执行返回泛型类型对象的存储过程
*
*@paramcls
* 泛型类型
*@paramsql
* SQL语句
*@paramparams
* 存储过程参数
*@return泛型类型对象
*@throwsSQLException
*@throwsClassNotFoundException
*@throwsInstantiationException
*@throwsIllegalAccessException*/
public static T executeEntity(Classcls, String sql,
SqlParameter... params) {
T obj= null;
Connection con= null;
CallableStatement cs= null;
ResultSet rs= null;try{
con=getConnection();
cs=con.prepareCall(sql);
setSqlParameter(cs, params);
rs=cs.executeQuery();while(rs.next()) {
obj=executeResultSet(cls, rs);break;
}
}catch(Exception e) {
e.printStackTrace();
}finally{
close(con, cs, rs);
}returnobj;
}/*** 将一条记录转成一个对象
*
*@paramcls
* 泛型类型
*@paramrs
* ResultSet对象
*@return泛型类型对象
*@throwsInstantiationException
*@throwsIllegalAccessException
*@throwsSQLException*/
private static T executeResultSet(Classcls, ResultSet rs)throwsInstantiationException, IllegalAccessException, SQLException {
T obj=cls.newInstance();
ResultSetMetaData rsm=rs.getMetaData();int columnCount =rsm.getColumnCount();//Field[] fields = cls.getFields();
Field[] fields =cls.getDeclaredFields();for (int i = 0; i < fields.length; i++) {
Field field=fields[i];
String fieldName=field.getName();for (int j = 1; j <= columnCount; j++) {
String columnName=rsm.getColumnName(j);if(fieldName.equalsIgnoreCase(columnName)) {
Object value=rs.getObject(j);
field.setAccessible(true);
field.set(obj, value);break;
}
}
}returnobj;
}/*** 设置存储过程参数名称,参数值,参数方向
*
*@paramcs
*@paramparams
*@throwsSQLException*/
private static voidsetSqlParameter(CallableStatement cs,
SqlParameter... params)throwsSQLException {if (params != null) {for(SqlParameter param : params) {if(param.OutPut) {
String paramName=param.Name;if (paramName == null || paramName.equals("")) {
cs.registerOutParameter(1, param.Type);//设置返回类型参数
} else{
cs.registerOutParameter(paramName, param.Type);//设置输出类型参数
}
}else{
cs.setObject(param.Name, param.Value);//设置输入类型参数
}
}
}
}/*** 得到存储过程参数执行结果
*
*@paramcs
*@paramparams
*@throwsSQLException*/
private static voidgetSqlParameter(CallableStatement cs,
SqlParameter... params)throwsSQLException {for(SqlParameter param : params) {if(param.OutPut) {
String paramName=param.Name;if (paramName == null || paramName.equals("")) {
param.Value= cs.getObject(1);//返回类型参数值
} else{
param.Value= cs.getObject(paramName);//输出类型参数值
}
}
}
}/*** 关闭JDBC对象,释放资源。
*
*@paramcon
* 连接对象
*@paramps
* 命令对象
*@paramrs
* 结果集对象
*@throwsSQLException*/
private static voidclose(Connection con, Statement ps, ResultSet rs) {try{//rs.close();//jiangxiang删除改行,当多次掉用该语句时会报空指针异常,因为rs已关闭。
if (rs != null) {
rs= null;
}if (ps != null) {
ps.close();
ps= null;
}if (con != null) {
con.close();
con= null;
}
}catch(SQLException e) {//TODO Auto-generated catch block
e.printStackTrace();
}
}
}