package cn.jdbcoracle;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class SqlHelper {
private static Connection con=null;
private static PreparedStatement ps=null;
private static ResultSet rs=null;
//连接数据库用的属性
private static String driver="";
private static String db="";
private static String user="";
private static String pass="";
//加载数据库配置文件用
private static FileInputStream fis=null;
private static Properties pp=null;
//使用类就生产一个驱动
static{
try {
fis=new FileInputStream("dbinfo.properties");
pp=new Properties();
pp.load(fis);
driver=pp.getProperty("driver");
db=pp.getProperty("db");
user=pp.getProperty("user");
pass=pp.getProperty("pass");
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//连接数据库
public static void getConnection(){
try {
con=DriverManager.getConnection(db,user,pass);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void executeUpdate(String sql,String[] parameters){
getConnection();
try {
ps=con.prepareStatement(sql);
if(parameters!=null){
for(int i=0;i<parameters.length;i++){
ps.setObject(i+1,parameters[i]);
}
}
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ps=null;
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
con=null;
}
}
}
//dml事务
public static void executeUpdateTransaction(String[] sql,String[][] parameters){
getConnection();
try {
con.setAutoCommit(false);
for(int i=0;i<sql.length;i++){
ps=con.prepareStatement(sql[i]);//执行第i句sql
for(int j=0;j<parameters[i].length;j++){//获取第i句语句的参数
ps.setObject(j+1, parameters[i][j]);
}
ps.executeUpdate();
//int a=9/0;//发生异常测试
}
con.commit();
} catch (Exception e) {
try {
con.rollback();//事务异常则恢复初始状态，不对数据库作任何修改
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
ps=null;
}
if(con!=null){
try {
con.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
con=null;
}
e.printStackTrace();
}
}
//搜索
public static ResultSet exeSelect(String sql,String[] parameters){
getConnection();
try {
ps=con.prepareStatement(sql);
if(parameters!=null){
for(int i=0;i<parameters.length;i++){
ps.setObject(i+1, parameters[i]);
}
}
rs=ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static void main(String[] args) {
SqlHelper sh=new SqlHelper();
}
}
测试Sqlhelper.java
package cn.jdbcoracle;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.net.aso.r;
public class Test {
@org.junit.Test
public void testcon(){
String sql="insert into emp(empno,ename) values(?,?)";
String[] parameters={"2","关羽"};
SqlHelper.executeUpdate(sql, parameters);
System.out.println("OK");
}
@org.junit.Test
public void testSelect(){
String sql="select * from emp where empno=?";
String parameters[]={"7878"};
ResultSet rs=SqlHelper.exeSelect(sql, parameters);
try {
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@org.junit.Test
public void testTransaction(){
String sql1="update emp set sal=nvl(sal,0)+100 where empno=?";
String sql2="update emp set sal=nvl(sal,0)+200 where empno=?";
String[] sql={sql1,sql2};
String[][] parameters={{"2"},{"3"}};
SqlHelper.executeUpdateTransaction(sql, parameters);
}
}