SqlHelper工具类封装
SQLHelper.java
import java.io.FileInputStream; import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
//防止 sql注入漏洞
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties; public class SQLHelper {
// 定义需要的变量
private static Connection ct = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
private static Properties prop = null;
private static CallableStatement cs = null;
private static InputStream in = null;
public static Connection getCt() {
return ct;
}
public static PreparedStatement getPs() {
return ps;
}
// 加载驱动，只需要一次
static {
prop = new Properties();
try {
// 配置信息放在src/properties下，根目录是工程目录
// in = new FileInputStream(
// "src/properties/dbinfo.properties");
// 如果打jar包，根目录是src
in = SQLHelper.class
.getResourceAsStream("/properties/dbinfo.properties");
prop.load(in);
driver = prop.getProperty("driver");
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
Class.forName(driver);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
in = null;
}
}
}
// 得到链接
private static Connection getConnection() {
try {
ct = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return ct;
}
// update/delete/insert 单个增删改操作，不需要考虑从事务
public static void executeUpdate(String sql, String params[]) {
try {
ct = getConnection();
ps = ct.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setString(i + 1, params[i]);
}
}
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
close(rs, ps, ct);
}
}
// 多个 update / delete / insert 语句，考虑事务
public static void executeUpdate(String sql[], String params[][]) {
try {
ct = getConnection();
// 因为用户传入的可能是多个sql语句,设置不自动提交
ct.setAutoCommit(false);
for (int i = 0; i < sql.length; i++) {
ps = ct.prepareStatement(sql[i]);
if (params != null) {
if (params[i] != null) {
for (int j = 0; j < params[i].length; j++) {
ps.setString(j + 1, params[i][j]);
}
}
}
ps.executeUpdate();
}
ct.commit();
} catch (Exception e) {
e.printStackTrace();
try {
ct.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
// 查询数据库
public static ResultSet executeQuery(String sql, String params[]) {
// 根据实际情况我们队sql语句 ？进行赋值
try {
ct = getConnection();
ps = ct.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setString(i + 1, params[i]);
}
}
rs = ps.executeQuery();
return rs;
} catch (SQLException e) {
throw new RuntimeException("查询失败");
}
}
// 对查询语句进行升级
public ArrayList<Object[]> executeQuery2(String sql, String params[]) {
PreparedStatement preparedStatement = null;
Connection connection = null;
ResultSet resultSet = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setString(i + 1, params[i]);
}
}
resultSet = preparedStatement.executeQuery();
ArrayList<Object[]> list = new ArrayList<Object[]>();
ResultSetMetaData rsmd = resultSet.getMetaData();
// 返回此 ResultSet 对象中的列数。
int column = rsmd.getColumnCount();
while (resultSet.next()) {
Object obj[] = new Object[column];
for (int i = 1; i <= column; i++) {
obj[i - 1] = resultSet.getObject(i);
}
list.add(obj);
}
return list;
} catch (Exception e) {
throw new RuntimeException("查询失败" + e.getMessage());
} finally {
close(resultSet, preparedStatement, connection);
}
}
// 调用存储过程 无返回值
// sql {call 过程(?,?,?)}
public static void calPro1(String sql, String params[]) {
try {
ct = getConnection();
cs = ct.prepareCall(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
cs.setString(i + 1, params[i]);
}
}
cs.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, cs, ct);
}
}
// 调用存储过程 有返回值
public static CallableStatement callPro2(String sql, String[] inparameters,
Integer[] outparameters) {
try {
ct = getConnection();
cs = ct.prepareCall(sql);
if (inparameters != null) {
for (int i = 0; i < inparameters.length; i++) {
cs.setObject(i + 1, inparameters[i]);
}
}
// 给 out参数赋值
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 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;
}
}
}
配置文件 dbiofo.properties
#这是我的 oracle 配置
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
username=scott
password=tiger
#mysql 配置...
应用：
String sql1="insert into emp (empno,ename) values (1256,'康吉')";
String sql2="insert into emp (empno,ename) values (7933,'康吉')";
String sql[]={sql1,sql2};
SQLHelper.executeUpdate2(sql, null);
