最精简实用的jdbc工具类
java对JDBC的封装，操作起来更方便IResultSetCall.javaimport java.sql.ResultSet;
import java.sql.SQLException;
public interface IResultSetCall<T> {
public T invoke(ResultSet rs) throws SQLException;
}DBUtil.javaimport java.io.IOException;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import oracle.jdbc.OracleTypes;
import util.db.test.DBUtilTest;
/**
*
* DBUtil，数据库访问工具类<br/>
* 对应测试类： {@link DBUtilTest}
* @preserve all
*/
public class DBUtil {
private static Connection con = null;
public static Connection openConnection() throws SQLException, ClassNotFoundException, IOException {
if (null == con || con.isClosed()) {
Properties p = new Properties();
p.load(DBUtil.class.getResourceAsStream("/config-db.properties"));
Class.forName(p.getProperty("db_driver"));
con = DriverManager.getConnection(p.getProperty("db_url"), p.getProperty("db_username"),
p.getProperty("db_password"));
}
return con;
}
public static void closeConnection() throws SQLException {
try {
if (null != con)
con.close();
} finally {
con = null;
System.gc();
}
}
public static List<Map<String, Object>> queryMapList(Connection con, String sql) throws SQLException,
InstantiationException, IllegalAccessException {
List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
Statement preStmt = null;
ResultSet rs = null;
try {
preStmt = con.createStatement();
rs = preStmt.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (null != rs && rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < columnCount; i++) {
String name = rsmd.getColumnName(i + 1);
Object value = rs.getObject(name);
map.put(name, value);
}
lists.add(map);
}
} finally {
if (null != rs)
rs.close();
if (null != preStmt)
preStmt.close();
}
return lists;
}
public static List<Map<String, Object>> queryMapList(Connection con, String sql, Object... params)
throws SQLException, InstantiationException, IllegalAccessException {
List<Map<String, Object>> lists = new ArrayList<Map<String, Object>>();
PreparedStatement preStmt = null;
ResultSet rs = null;
try {
preStmt = con.prepareStatement(sql);
for (int i = 0; i < params.length; i++)
preStmt.setObject(i + 1, params);// 下标从1开始
rs = preStmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (null != rs && rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < columnCount; i++) {
String name = rsmd.getColumnName(i + 1);
Object value = rs.getObject(name);
map.put(name, value);
}
lists.add(map);
}
} finally {
if (null != rs)
rs.close();
if (null != preStmt)
preStmt.close();
}
return lists;
}
public static <T> List<T> queryBeanList(Connection con, String sql, Class<T> beanClass) throws SQLException,
InstantiationException, IllegalAccessException {
List<T> lists = new ArrayList<T>();
Statement stmt = null;
ResultSet rs = null;
Field[] fields = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
fields = beanClass.getDeclaredFields();
for (Field f : fields)
f.setAccessible(true);
while (null != rs && rs.next()) {
T t = beanClass.newInstance();
for (Field f : fields) {
String name = f.getName();
try {
Object value = rs.getObject(name);
setValue(t, f, value);
} catch (Exception e) {
}
}
lists.add(t);
}
} finally {
if (null != rs)
rs.close();
if (null != stmt)
stmt.close();
}
return lists;
}
public static <T> List<T> queryBeanList(Connection con, String sql, Class<T> beanClass, Object... params)
throws SQLException, InstantiationException, IllegalAccessException {
List<T> lists = new ArrayList<T>();
PreparedStatement preStmt = null;
ResultSet rs = null;
Field[] fields = null;
try {
preStmt = con.prepareStatement(sql);
for (int i = 0; i < params.length; i++)
preStmt.setObject(i + 1, params);// 下标从1开始
rs = preStmt.executeQuery();
fields = beanClass.getDeclaredFields();
for (Field f : fields)
f.setAccessible(true);
while (null != rs && rs.next()) {
T t = beanClass.newInstance();
for (Field f : fields) {
String name = f.getName();
try {
Object value = rs.getObject(name);
setValue(t, f, value);
} catch (Exception e) {
}
}
lists.add(t);
}
} finally {
if (null != rs)
rs.close();
if (null != preStmt)
preStmt.close();
}
return lists;
}
public static <T> List<T> queryBeanList(Connection con, String sql, IResultSetCall<T> qdi) throws SQLException {
List<T> lists = new ArrayList<T>();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
while (null != rs && rs.next())
lists.add(qdi.invoke(rs));
} finally {
if (null != rs)
rs.close();
if (null != stmt)
stmt.close();
}
return lists;
}
public static <T> List<T> queryBeanList(Connection con, String sql, IResultSetCall<T> qdi, Object... params)
throws SQLException {
List<T> lists = new ArrayList<T>();
PreparedStatement preStmt = null;
ResultSet rs = null;
try {
preStmt = con.prepareStatement(sql);
for (int i = 0; i < params.length; i++)
preStmt.setObject(i + 1, params);
rs = preStmt.executeQuery();
while (null != rs && rs.next())
lists.add(qdi.invoke(rs));
} finally {
if (null != rs)
rs.close();
if (null != preStmt)
preStmt.close();
}
return lists;
}
public static <T> T queryBean(Connection con, String sql, Class<T> beanClass) throws SQLException,
InstantiationException, IllegalAccessException {
List<T> lists = queryBeanList(con, sql, beanClass);
if (lists.size() != 1)
throw new SQLException("SqlError：期待一行返回值，却返回了太多行！");
return lists.get(0);
}
public static <T> T queryBean(Connection con, String sql, Class<T> beanClass, Object... params)
throws SQLException, InstantiationException, IllegalAccessException {
List<T> lists = queryBeanList(con, sql, beanClass, params);
if (lists.size() != 1)
throw new SQLException("SqlError：期待一行返回值，却返回了太多行！");
return lists.get(0);
}
public static <T> List<T> queryObjectList(Connection con, String sql, Class<T> objClass) throws SQLException,
InstantiationException, IllegalAccessException {
List<T> lists = new ArrayList<T>();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
label: while (null != rs && rs.next()) {
Constructor<?>[] constor = objClass.getConstructors();
for (Constructor<?> c : constor) {
Object value = rs.getObject(1);
try {
lists.add((T) c.newInstance(value));
continue label;
} catch (Exception e) {
}
}
}
} finally {
if (null != rs)
rs.close();
if (null != stmt)
stmt.close();
}
return lists;
}
public static <T> List<T> queryObjectList(Connection con, String sql, Class<T> objClass, Object... params)
throws SQLException, InstantiationException, IllegalAccessException {
List<T> lists = new ArrayList<T>();
PreparedStatement preStmt = null;
ResultSet rs = null;
try {
preStmt = con.prepareStatement(sql);
for (int i = 0; i < params.length; i++)
preStmt.setObject(i + 1, params);
rs = preStmt.executeQuery();
label: while (null != rs && rs.next()) {
Constructor<?>[] constor = objClass.getConstructors();
for (Constructor<?> c : constor) {
String value = rs.getObject(1).toString();
try {
T t = (T) c.newInstance(value);
lists.add(t);
continue label;
} catch (Exception e) {
}
}
}
} finally {
if (null != rs)
rs.close();
if (null != preStmt)
preStmt.close();
}
return lists;
}
public static <T> T queryObject(Connection con, String sql, Class<T> objClass) throws SQLException,
InstantiationException, IllegalAccessException {
List<T> lists = queryObjectList(con, sql, objClass);
if (lists.size() != 1)
throw new SQLException("SqlError：期待一行返回值，却返回了太多行！");
return lists.get(0);
}
public static <T> T queryObject(Connection con, String sql, Class<T> objClass, Object... params)
throws SQLException, InstantiationException, IllegalAccessException {
List<T> lists = queryObjectList(con, sql, objClass, params);
if (lists.size() != 1)
throw new SQLException("SqlError：期待一行返回值，却返回了太多行！");
return lists.get(0);
}
public static int execute(Connection con, String sql) throws SQLException {
Statement stmt = null;
try {
stmt = con.createStatement();
return stmt.executeUpdate(sql);
} finally {
if (null != stmt)
stmt.close();
}
}
public static int execute(Connection con, String sql, Object... params) throws SQLException {
PreparedStatement preStmt = null;
try {
preStmt = con.prepareStatement(sql);
for (int i = 0; i < params.length; i++)
preStmt.setObject(i + 1, params);// 下标从1开始
return preStmt.executeUpdate();
} finally {
if (null != preStmt)
preStmt.close();
}
}
public static int[] executeAsBatch(Connection con, List<String> sqlList) throws SQLException {
return executeAsBatch(con, sqlList.toArray(new String[] {}));
}
public static int[] executeAsBatch(Connection con, String[] sqlArray) throws SQLException {
Statement stmt = null;
try {
stmt = con.createStatement();
for (String sql : sqlArray) {
stmt.addBatch(sql);
}
return stmt.executeBatch();
} finally {
if (null != stmt) {
stmt.close();
}
}
}
public static int[] executeAsBatch(Connection con, String sql, Object[][] params) throws SQLException {
PreparedStatement preStmt = null;
try {
preStmt = con.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
Object[] rowParams = params;
for (int k = 0; k < rowParams.length; k++) {
Object obj = rowParams;
preStmt.setObject(k + 1, obj);
}
preStmt.addBatch();
}
return preStmt.executeBatch();
} finally {
if (null != preStmt) {
preStmt.close();
}
}
}
private static <T> void setValue(T t, Field f, Object value) throws IllegalAccessException {
// TODO 以数据库类型为准绳，还是以java数据类型为准绳？还是混合两种方式？
if (null == value)
return;
String v = value.toString();
String n = f.getType().getName();
if ("java.lang.Byte".equals(n) || "byte".equals(n)) {
f.set(t, Byte.parseByte(v));
} else if ("java.lang.Short".equals(n) || "short".equals(n)) {
f.set(t, Short.parseShort(v));
} else if ("java.lang.Integer".equals(n) || "int".equals(n)) {
f.set(t, Integer.parseInt(v));
} else if ("java.lang.Long".equals(n) || "long".equals(n)) {
f.set(t, Long.parseLong(v));
} else if ("java.lang.Float".equals(n) || "float".equals(n)) {
f.set(t, Float.parseFloat(v));
} else if ("java.lang.Double".equals(n) || "double".equals(n)) {
f.set(t, Double.parseDouble(v));
} else if ("java.lang.String".equals(n)) {
f.set(t, value.toString());
} else if ("java.lang.Character".equals(n) || "char".equals(n)) {
f.set(t, (Character) value);
} else if ("java.lang.Date".equals(n)) {
f.set(t, new Date(((java.sql.Date) value).getTime()));
} else if ("java.lang.Timer".equals(n)) {
f.set(t, new Time(((java.sql.Time) value).getTime()));
} else if ("java.sql.Timestamp".equals(n)) {
f.set(t, (java.sql.Timestamp) value);
} else {
System.out.println("SqlError：暂时不支持此数据类型，请使用其他类型代替此类型！");
}
}
public static void executeProcedure(Connection con, String procedureName, Object... params) throws SQLException {
CallableStatement proc = null;
try {
proc = con.prepareCall(procedureName);
for (int i = 0; i < params.length; i++) {
proc.setObject(i + 1, params);
}
proc.execute();
} finally {
if (null != proc)
proc.close();
}
}
public static boolean executeProcedureReturnErrorMsg(Connection con, String procedureName, StringBuffer errorMsg,
Object... params) throws SQLException {
CallableStatement proc = null;
try {
proc = con.prepareCall(procedureName);
proc.registerOutParameter(1, OracleTypes.VARCHAR);
for (int i = 0; i < params.length; i++) {
proc.setObject(i + 2, params);
}
boolean b = proc.execute();
errorMsg.append(proc.getString(1));
return b;
} finally {
if (null != proc)
proc.close();
}
}
public static <T> List<T> executeProcedureReturnCursor(Connection con, String procedureName, Class<T> beanClass,
Object... params) throws SQLException, InstantiationException, IllegalAccessException {
List<T> lists = new ArrayList<T>();
CallableStatement proc = null;
ResultSet rs = null;
try {
proc = con.prepareCall(procedureName);
proc.registerOutParameter(1, OracleTypes.CURSOR);
for (int i = 0; i < params.length; i++) {
proc.setObject(i + 2, params);
}
boolean b = proc.execute();
if (b) {
rs = (ResultSet) proc.getObject(1);
while (null != rs && rs.next()) {
T t = beanClass.newInstance();
Field[] fields = beanClass.getDeclaredFields();
for (Field f : fields) {
f.setAccessible(true);
String name = f.getName();
try {
Object value = rs.getObject(name);
setValue(t, f, value);
} catch (Exception e) {
}
}
lists.add(t);
}
}
} finally {
if (null != rs)
rs.close();
if (null != proc)
proc.close();
}
return lists;
}
public static <T> List<List<T>> listLimit(List<T> lists, int pageSize) {
List<List<T>> llists = new ArrayList<List<T>>();
for (int i = 0; i < lists.size(); i = i + pageSize) {
try {
List<T> list = lists.subList(i, i + pageSize);
llists.add(list);
} catch (IndexOutOfBoundsException e) {
List<T> list = lists.subList(i, i + (lists.size() % pageSize));
llists.add(list);
}
}
return llists;
}
}config-db.propertiesdb_url=jdbc:oracle:thin:@localhost:1521:orcl
db_driver=oracle.jdbc.driver.OracleDriver
db_username=hr
db_password=DBUtilTest.javaimport static org.junit.Assert.fail;
import static util.db.DBUtil.executeAsBatch;
import static util.db.DBUtil.executeProcedure;
import static util.db.DBUtil.openConnection;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import util.db.DBUtil;
import util.db.IResultSetCall;
/**
* 注意： 可以替换Employess1为Employess2,看看查询结果有什么区别。。
*/
public class DBUtilTest {
private Connection con = null;
@Before
public void setUp() throws Exception {
try {
con = DBUtil.openConnection();
} catch (SQLException e) {
fail(e.getMessage());
}
}
@After
public void tearDown() throws Exception {
try {
DBUtil.closeConnection();
} catch (SQLException e) {
fail(e.getMessage());
}
}
@Test
public void testQueryBeanListConnectionStringClassOfT() {
String sql = "SELECT * FROM employees";
try {
List<Employees1> emList = DBUtil.queryBeanList(con, sql, Employees1.class);
print(emList);
} catch (Exception e) {
fail(e.getMessage());
}
}
@Test
public void testQueryBeanListConnectionStringClassOfTObjectArray() {
String sql = "SELECT * FROM employees t WHERE t.salary > ? and T.JOB_ID = ?";
try {
List<Employees1> emList = DBUtil.queryBeanList(con, sql, Employees1.class, 5000, "ST_MAN");
print(emList);
} catch (Exception e) {
fail(e.getMessage());
}
}
@Test
public void testQueryBeanListConnectionStringIResultSetCallOfTObjectArray() {
String sql = "SELECT first_name, last_name, salary FROM employees t WHERE t.salary > ? and T.JOB_ID = ?";
try {
List<Employees1> emList = DBUtil.queryBeanList(con, sql, new IResultSetCall<Employees1>() {
public Employees1 invoke(ResultSet rs) throws SQLException {
Employees1 e = new Employees1();
e.setFirst_name(rs.getString("first_name"));
e.setLast_name(rs.getString("last_name"));
e.setSalary(rs.getDouble("salary"));
return e;
}
}, 5000, "ST_MAN");
print(emList);
} catch (Exception e) {
e.printStackTrace();
fail(e.getMessage());
}
}
@Test
public void testQueryObjectListConnectionStringClassOfT() {
String sql = "SELECT email FROM employees t";
try {
List<String> lists = DBUtil.queryObjectList(con, sql, String.class);
print(lists);
} catch (Exception e) {
fail(e.getMessage());
}
}
@Test
public void testQueryObjectListConnectionStringClassOfTObjectArray() {
String sql = "SELECT salary FROM employees t WHERE t.salary > ? and T.JOB_ID = ?";
try {
List<Double> lists = DBUtil.queryObjectList(con, sql, Double.class, 2000, "ST_MAN");
print(lists);
} catch (Exception e) {
fail(e.getMessage());
e.printStackTrace();
}
}
@Test
public void testQueryBeanConnectionStringClassOfT() {
String sql = "SELECT * FROM employees t WHERE t.employee_id in (120)";
try {
Employees1 emp = DBUtil.queryBean(con, sql, Employees1.class);
print(emp);
} catch (Exception e) {
fail(e.getMessage());
}
}
@Test
public void testQueryBeanConnectionStringClassOfTObjectArray() {
String sql = "SELECT * FROM employees t WHERE t.employee_id = ?";
try {
Employees1 emp = DBUtil.queryBean(con, sql, Employees1.class, 120);
print(emp);
} catch (Exception e) {
fail(e.getMessage());
}
}
@Test
public void testQueryObjectConnectionStringClassOfT() {
String sql = "SELECT email FROM employees t WHERE t.employee_id in (120)";
try {
String s = DBUtil.queryObject(con, sql, String.class);
print(s);
} catch (Exception e) {
fail(e.getMessage());
}
}
@Test
public void testQueryObjectConnectionStringClassOfTObjectArray() {
String sql = "SELECT salary FROM employees t WHERE t.employee_id in (?)";
try {
Double d = DBUtil.queryObject(con, sql, Double.class, 12);
print(d);
} catch (Exception e) {
fail(e.getMessage());
e.printStackTrace();
}
}
@Test
public void testExecuteConnectionStringObjectArray() {
String sql = "UPDATE employees t SET t.salary =? WHERE t.employee_id =?";
try {
con.setAutoCommit(false);
int count = DBUtil.execute(con, sql, 20000, 120);
Assert.assertTrue(count == 1);
sql = "SELECT t.salary FROM employees t WHERE t.employee_id =?";
Double d = DBUtil.queryObject(con, sql, Double.class, 120);
Assert.assertTrue(d - 20000 == 0);
} catch (Exception e) {
e.printStackTrace();
fail(e.getMessage());
} finally {
try {
con.rollback();
con.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void testQueryMapList() {
String sql = "SELECT first_name, last_name, salary FROM employees t WHERE t.salary > ? and T.JOB_ID = ?";
try {
List<Map<String, Object>> lists = DBUtil.queryMapList(con, sql, 3000, "ST_MAN");
print(lists);
} catch (Exception e) {
e.printStackTrace();
fail(e.getMessage());
}
}
@Test
public void testExecuteProcedure() {
try {
executeProcedure(openConnection(), "{CALL prc_updatedata_for_daochong(?,?,?,?)}", "3000000993447731",
"060000019213", "50", "0010701848");
System.out.println("执行存储过程更新采购订单表上的数据成功");
} catch (Exception e) {
e.printStackTrace();
fail(e.getMessage());
}
}
@Test
public void testExecuteAsBatch() {
try {
List<String> sqlList = new ArrayList<String>();
sqlList.add("UPDATE sm_user t SET t.password = 'ok' WHERE t.row_id = '232s43' ");
sqlList.add("UPDATE sm_user t SET t.password = 'ok' WHERE t.row_id = '232f42' ");
sqlList.add("UPDATE sm_user t SET t.password = 'ok' WHERE t.row_id = '23g2423' ");
sqlList.add("UPDATE sm_user t SET t.password = 'ok' WHERE t.row_id = '232434s' ");
executeAsBatch(openConnection(), sqlList);
} catch (Exception e) {
e.printStackTrace();
fail(e.getMessage());
}
}
@Test
public void testExecuteAsBatchForPre() {
try {
executeAsBatch(con, "UPDATE employees t SET t.first_name = ? WHERE t.last_name = ? ", new Object[][] {
{ "ok", "235jklsd" }, { "no", "jg4ti324" }, { "no1", "111" }, { "no2", "32423" } });
} catch (Exception e) {
e.printStackTrace();
fail(e.getMessage());
}
}
private void print(Object obj) {
if (obj instanceof List) {
List list = (List) obj;
for (Object o : list) {
if (o instanceof Map) {
Map<String, Object> map = (Map<String, Object>) o;
Set<String> set = map.keySet();
for (String key : set) {
Object value = map.get(key);
System.out.print(key + ":" + value + "\t");
}
System.out.println();
} else {
System.out.println(o);
}
}
System.out.println("总共查询出数据数量是：" + list.size());
} else {
System.out.println(obj);
}
}
}
页:
[1]