DBUtil
package com.lbf.util;
import java.io.InputStream;
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.Properties;
public class DBUtil {
private static Connection ct=null;//连接
private static ResultSet rs=null;//结果
private static PreparedStatement ps=null;
//连接数据库参数
private static String url = "";
private static String drivername = "";
private static String username = "";
private static String password = "" ;
//加载驱动
static{
try
{
Properties properties=new Properties();
InputStream is=DBUtil.class.getClassLoader().getResourceAsStream("com/lbf/util/dbinfo.properties");
properties.load(is);
//属性文件读取信息
drivername=properties.getProperty("driver");
username=properties.getProperty("username");
password=properties.getProperty("password");
url=properties.getProperty("url");
} catch (Exception e) {
e.printStackTrace();
System.exit(-1);
}
}
public static Connection getCon()
{
try {
Class.forName(drivername);
ct= DriverManager.getConnection(url,username,password);//注意配置文件
} catch (Exception e) {
e.printStackTrace();
}
return ct;//谁调用谁拿到Connection
}
public static void close(ResultSet rs,Statement ps,Connection ct)
{
if(rs!=null)
{
try
{
rs.close();
}catch(Exception e)
{
}
rs=null;//使用垃圾回收
}
if(ps!=null)
{
try
{
ps.close();
}catch(SQLException e)
{
e.printStackTrace();
}
ps=null;
}
if(ct!=null)
{
try
{
ct.close();
}catch(SQLException e)
{
e.printStackTrace();
}
ct=null;
}
}
}
SqlHelper
package com.lbf.util;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class SqlHelper {
private static Connection ct = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
private static CallableStatement cs = null;
public static ArrayList executeQuery(String sql,String []paras)
{
ArrayList al=new ArrayList();
try {
ct=DBUtil.getCon();
ps=ct.prepareStatement(sql);
//给sql问号赋值
for (int i = 0; i < paras.length; i++)
{
ps.setString(i+1, paras[i]);
}
rs=ps.executeQuery();
//非常有用
ResultSetMetaData rsmd=rs.getMetaData();
//用法rs可以的到有多少列
int columnNum=rsmd.getColumnCount();
//循环从a1中取出数据封装到ArrayList
while(rs.next())
{
Object []objects=new Object[columnNum];
for(int i=0;i<objects.length;i++)
{
objects[i]=rs.getObject(i+1); //返回对象数组
}
al.add(objects);
}
return al;
} catch (Exception e)
{
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}finally
{
DBUtil.close(rs,ps,ct);
}
}
public static ResultSet executeQuery(String sqlstr)
{
Statement stmt = null;
try
{
//得到连接
ct=DBUtil.getCon();
//ps=ct.prepareStatement(sqlstr);
stmt = ct.createStatement();
//创建结果集
rs = stmt.executeQuery(sqlstr);
//将结果集返回
return rs;
}
catch(SQLException e)
{
System.out.print("错误");
}
return null;
}
//*************callPro1存储过程函数1*************
public static CallableStatement callPro1(String sql,String[] parameters)
{
try{
ct=DBUtil.getCon(); ;
cs = ct.prepareCall(sql);
if(parameters!=null){
for(int i=0;i<parameters.length;i++){
cs.setObject(i+1,parameters[i]);
}
}
cs.execute();
}
catch(Exception e) { e.printStackTrace(); throw new RuntimeException(e.getMessage());}
finally
{ DBUtil.close(rs,cs,ct);}
return cs;
}
//*******************callpro2存储过程2************************
public static CallableStatement callPro2(String sql,String[] inparameters,
Integer[] outparameters)
{
try
{
ct = DBUtil.getCon();
cs = ct.prepareCall(sql);
if(inparameters!=null)
{
for(int i=0;i<inparameters.length;i++)
{
cs.setObject(i+1,inparameters[i]);
}
}
//cs.registerOutparameter(2,oracle.jdbc.OracleTypes.CURSOR);
if(outparameters!=null)
{
for(int i=0;i<outparameters.length;i++)
{
cs.registerOutParameter(inparameters.length+1+i,outparameters[i]);
}
}
cs.execute();
}
catch(Exception e) {
e.printStackTrace(); throw new RuntimeException(e.getMessage());
}
finally
{
}
return cs;
}
public static void executeUpdate2(String[] sql,String[][] parameters)
{
try
{
ct = DBUtil.getCon();
ct.setAutoCommit(false);
for(int i=0;i<sql.length;i++)
{
if(null!=parameters[i])
{
ps = ct.prepareStatement(sql[i]);
for(int j=0;j<parameters[i].length;j++)
{
ps.setString(j+1,parameters[i][j]);
}
ps.executeUpdate();
}
}
ct.commit();
}catch (Exception e)
{
e.printStackTrace();
try
{
ct.rollback();
}
catch (SQLException e1)
{
e1.printStackTrace();
}
throw new RuntimeException(e.getMessage());
}finally
{
DBUtil.close(rs,ps,ct);
}
}
//先写一个update、delete、insert
//sql格式:update 表名 set 字段名 =?where 字段=?
//parameters应该是(”abc“,23)
public static void executeUpdate(String sql,String[] parameters)
{
try
{
ct=DBUtil.getCon();
ps = ct.prepareStatement(sql);
if(parameters!=null)
{
for(int i=0;i<parameters.length;i++)
{
ps.setString(i+1,parameters[i]);
}
}
ps.executeUpdate();
}
catch(Exception e)
{
e.printStackTrace();//开发阶段
//抛出异常
//可以处理,也可以不处理
throw new RuntimeException(e.getMessage());
}
finally
{
DBUtil.close(rs,ps,ct);
}
}
/* public static void close(ResultSet rs,Statement ps,Connection ct)
{
//关闭资源(先开后关)
if(rs!=null)
{
try{
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
rs=null;
}
if(ps!=null)
{
try
{
ps.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
ps=null;
}
if(null!=ct)
{
try{
ct.close();
}
catch(SQLException e) {
e.printStackTrace();
}
ct=null;
}
}*/
public static CallableStatement getCs()
{
return cs;
}
public static Connection getCt()
{
return ct;
}
public static PreparedStatement getPs()
{
return ps;
}
public static ResultSet getRs()
{
return rs;
}
}