package com.wjl.test;
import java.io.*;
import java.sql.*;
import java.util.*;
public class SQLHelper {
private static String url = null;
private static String username = null;
private static String password = null;
private static String driver = null;
private static FileInputStream fis = null;
private static Properties prop = null;
private static Connection conn = null;
private static PreparedStatement pstmt = null;
private static ResultSet rs = null;
private static CallableStatement cstmt = null;
//初始化静态代码块
static {
try {
try {
fis = new FileInputStream("DBInfo.properties");
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
prop = new Properties();
prop.load(fis);
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
driver = prop.getProperty("driver");
} catch (IOException e) {
e.printStackTrace();
}
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
fis = null;
}
}
}
//取得连接的方法
public static Connection getConnection() {
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//执行单条SQL语句的DML方法(update、delete、insert)
public static void executeUpdate(String sql, String[] parameters) {
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if (parameters != null) {
for (int i = 0;i < parameters.length;i ++) {
pstmt.setString(i + 1, parameters[i]);
}
}
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());//若pstmt更新错误了,可发出通知,但可以不处理异常。
} finally {
close(rs, pstmt, conn);
}
}
//执行多条SQL语句的DML方法(update、delete、insert)
public static void executeBatch(String[] sql, String[][] parameters) {
try {
conn = getConnection();
conn.setAutoCommit(false);
for (int i = 0;i < sql.length; i ++) {
if (parameters[i] != null) {
pstmt = conn.prepareStatement(sql[i]);
for (int j = 0;j < parameters[i].length;j ++) {
pstmt.setString(j + 1, parameters[i][j]);
}
pstmt.executeUpdate();
}
}
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new RuntimeException(e.getMessage());//若pstmt更新错误了,可发出通知,但可以不处理异常。
} finally {
close(rs, pstmt, conn);
}
}
//统一的select方法
public static ResultSet executeQuery(String sql,String[] parameters) {
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if (parameters != null) {
for (int i = 0;i < parameters.length;i ++) {
pstmt.setString(i + 1, parameters[i]);
}
}
rs = pstmt.executeQuery();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());//若pstmt更新错误了,可发出通知,但可以不处理异常。
} finally {
}
return rs;
}
//调用没有返回值的存储过程的方法
public static void executeCall(String sql, String[] parameters) {
try {
conn = getConnection();
cstmt = conn.prepareCall(sql);
if (parameters != null) {
for (int i = 0;i < parameters.length;i ++) {
cstmt.setObject(i + 1, parameters[i]);
}
}
cstmt.execute();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());//若pstmt更新错误了,可发出通知,但可以不处理异常。
} finally {
close(rs, cstmt, conn);
}
}
//调用有返回值的存储过程的方法
public static CallableStatement executeCall(String sql, String[] inParameters, Integer[] outParameters) {
try {
conn = getConnection();
cstmt = conn.prepareCall(sql);
if (inParameters != null) {
for (int i = 0;i < inParameters.length;i ++) {
cstmt.setObject(i + 1, inParameters[i]);
}
}
if (outParameters != null) {
for (int i = 0;i < outParameters.length;i ++) {
cstmt.registerOutParameter(inParameters.length + i + 1, outParameters[i]);
}//inParameters.length + i + 1,因为前面有inParameters.length个?
}
cstmt.execute();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());//若pstmt更新错误了,可发出通知,但可以不处理异常。
} finally {
}
return cstmt;
}
public static CallableStatement getCstmt() {
return cstmt;
}
public static Connection getConn() {
return conn;
}
public static PreparedStatement getPstmt() {
return pstmt;
}
public static ResultSet getRs() {
return rs;
}
//统一关闭资源的方法
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null; //待GC回收
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null; //待GC回收
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
我的SqlHelper类【雏形】
最新推荐文章于 2022-04-29 18:38:17 发布