簡單通用JDBC輔助類封裝(實例)

哎，最近很好久沒寫點東西了，由於工作的原因，接觸公司自己研發的底層orm框架，偶然發現該框架在調用jdbc操作的時候參考的是hibernate 裡面的SimpleJdbcTemplate,這裡我想到了在大學的時候自己用過的一個簡單的jdbc封裝，現在我將代碼貼出來，和大家一起分享：

Config類：讀取同一包下的數據庫連接配置文件，這樣是為了更好的通用性考慮

package com.tly.dbutil;import java.io.IOException;import java.util.Properties;public class Config { private static Properties prop = new Properties(); static{ try { //加載dbconfig.properties配置文件prop.load(Config.class.getResourceAsStream("dbconfig.properties")); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //設置常量public static final String CLASS_NAME = prop.getProperty("CLASS_NAME"); public static final String DATABASE_URL = prop.getProperty("DATABASE_URL"); public static final String SERVER_IP = prop.getProperty("SERVER_IP"); public static final String SERVER_PORT = prop.getProperty("SERVER_PORT"); public static final String DATABASE_SID = prop.getProperty("DATABASE_SID"); public static final String USERNAME = prop.getProperty("USERNAME"); public static final String PASSWORD = prop.getProperty("PASSWORD"); }

dbconfig.properties：數據庫配置文件，你也可以用xml格式等，注意Config類裡面該文件的調用位置

CLASS_NAME=com.mysql.jdbc.DriverDATABASE_URL=jdbc:mysqlSERVER_IP=localhostSERVER_PORT=3306DATABASE_SID=employeesUSERNAME=rootPASSWORD=1

接下來就是數據庫連接輔助類DBConn了

package com.employees.dbutil;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DBConn { //三屬性、四方法//三大核心接口private Connection conn = null; private PreparedStatement pstmt = null; private ResultSet rs = null; //四個方法//method1: 創建數據庫的連接public Connection getConntion(){ try { //1: 加載連接驅動，Java反射原理Class.forName(Config.CLASS_NAME); //2:創建Connection接口對象，用於獲取MySQL數據庫的連接對象。三個參數：url連接字符串賬號密碼String url = Config.DATABASE_URL+"://"+Config.SERVER_IP+":"+Config.SERVER_PORT+"/"+Config.DATABASE_SID; conn = DriverManager.getConnection(url,Config.USERNAME,Config.PASSWORD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } //method2：關閉數據庫的方法public void closeConn(){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(pstmt!=null){ try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } //method3: 專門用於發送增刪改語句的方法public int execOther(PreparedStatement pstmt){ try { //1、使用Statement對象發送SQL語句int affectedRows = pstmt.executeUpdate(); //2、返回結果return affectedRows; } catch (SQLException e) { e.printStackTrace(); return -1; } } //method4: 專門用於發送查詢語句public ResultSet execQuery(PreparedStatement pstmt){ try { //1、使用Statement對象發送SQL語句rs = pstmt.executeQuery(); //2、返回結果return rs; } catch (SQLException e) { e.printStackTrace(); return null; } }}

平時的用上面的代碼能夠解決一些簡單的CRUD的應用了，但是還有很多限制，比如每次程序拿連接都要new,這樣就給系統加大了負擔，沒有事務，沒有dataSource等等，今天看見一哥們在園裡面寫的一篇用反射解決直接以對象參數的方式CRUD,這個我以前也寫過，沒寫完，主要是自己想寫一個通用的DButil,最後研究來研究去，發現越來越和hibernate裡面的simpleJdbcTemplate接近了，所以就直接去看hibernate的源碼了，加上那段時間有些事，沒有時間，就將這件事閒置起來了，現在把這個東西補上，也給自己回顧一下下

BaseDao類

package com.employees.dao;import java.io.InputStream;import java.lang.reflect.Method;import java.lang.reflect.ParameterizedType;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import com.employees.dbutil.DBConn;public class BaseDAO<T> { DBConn conn = new DBConn(); private Connection connection = null; @SuppressWarnings("unused") private Class<T> persistentClass; @SuppressWarnings("unchecked") public BaseDAO() { initConnection(); //獲得參數化類型ParameterizedType type = (ParameterizedType)getClass().getGenericSuperclass(); persistentClass = (Class<T>)type.getActualTypeArguments()[0]; } /** * 獲得數據庫連接*/ public void initConnection() { connection = conn.getConntion(); } /** * 保存*/ public void save(T entity) throws Exception{ //SQL語句,insert into table name ( String sql = "insert into " + entity.getClass().getSimpleName().toLowerCase() + "("; //獲得帶有字符串get的所有方法的對象List<Method> list = this.matchPojoMethods(entity,"get"); Iterator<Method> iter = list.iterator(); //拼接字段順序insert into table name(id,name,email, while(iter.hasNext()) { Method method = iter.next(); sql += method.getName().substring(3).toLowerCase() + ","; } //去掉最後一個,符號insert insert into table name(id,name,email) values( sql = sql.substring(0, sql.lastIndexOf(",")) + ") values("; //拼裝預編譯SQL語句insert insert into table name(id,name,email) values(?,?,?, for(int j = 0; j < list.size(); j++) { sql += "?,"; } //去掉SQL語句最後一個,符號insert insert into table name(id,name,email) values(?,?,?); sql = sql.substring(0, sql.lastIndexOf(",")) + ")"; //到此SQL語句拼接完成,打印SQL語句System.out.println(sql); //獲得預編譯對象的引用PreparedStatement statement = connection.prepareStatement(sql); int i = 0; //把指向迭代器最後一行的指針移到第一行. iter = list.iterator(); while(iter.hasNext()) { Method method = iter.next(); //此初判斷返回值的類型,因為存入數據庫時有的字段值格式需要改變,比如String,SQL語句是'"+abc+"' if(method.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1){ statement.setDate(++i, this.getDate(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("InputStream") != -1) { statement.setAsciiStream(++i, this.getBlob(method, entity),1440); } else { statement.setInt(++i, this.getInt(method, entity)); } } //執行conn.execOther(statement); //關閉連接conn.closeConn(); } /** * 修改*/ public void update(T entity) throws Exception{ String sql = "update " + entity.getClass().getSimpleName().toLowerCase() + " set "; //獲得該類所有get方法對象集合List<Method> list = this.matchPojoMethods(entity,"get"); //臨時Method對象,負責迭代時裝method對象. Method tempMethod = null; //由於修改時不需要修改ID,所以按順序加參數則應該把Id移到最後. Method idMethod = null; Iterator<Method> iter = list.iterator(); while(iter.hasNext()) { tempMethod = iter.next(); //如果方法名中帶有ID字符串並且長度為2,則視為ID. if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { //把ID字段的對象存放到一個變量中,然後在集合中刪掉. idMethod = tempMethod; iter.remove(); //如果方法名去掉set/get字符串以後與pojo + "id"想符合(大小寫不敏感),則視為ID } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))) { idMethod = tempMethod; iter.remove(); } } //把迭代指針移到第一位iter = list.iterator(); while(iter.hasNext()) { tempMethod = iter.next(); sql += tempMethod.getName().substring(3).toLowerCase() + "= ?,"; } //去掉最後一個,符號sql = sql.substring(0,sql.lastIndexOf(",")); //添加條件sql += " where " + idMethod.getName().substring(3).toLowerCase() + " = ?"; //SQL拼接完成,打印SQL語句System.out.println(sql); PreparedStatement statement = this.connection.prepareStatement(sql); int i = 0; iter = list.iterator(); while(iter.hasNext()) { Method method = iter.next(); //此初判斷返回值的類型,因為存入數據庫時有的字段值格式需要改變,比如String,SQL語句是'"+abc+"' if(method.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("Date") != -1){ statement.setDate(++i, this.getDate(method, entity)); } else if(method.getReturnType().getSimpleName().indexOf("InputStream") != -1) { statement.setAsciiStream(++i, this.getBlob(method, entity),1440); } else { statement.setInt(++i, this.getInt(method, entity)); } } //為Id字段添加值if(idMethod.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(idMethod, entity)); } else { statement.setInt(++i, this.getInt(idMethod, entity)); } //執行SQL語句statement.executeUpdate(); //關閉預編譯對象statement.close(); //關閉連接connection.close(); } /** * 刪除*/ public void delete(T entity) throws Exception{ String sql = "delete from " + entity.getClass().getSimpleName().toLowerCase() + " where "; //存放字符串為"id"的字段對象Method idMethod = null; //取得字符串為"id"的字段對象List<Method> list = this.matchPojoMethods(entity, "get"); Iterator<Method> iter = list.iterator(); while(iter.hasNext()) { Method tempMethod = iter.next(); //如果方法名中帶有ID字符串並且長度為2,則視為ID. if(tempMethod.getName().lastIndexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { //把ID字段的對象存放到一個變量中,然後在集合中刪掉. idMethod = tempMethod; iter.remove(); //如果方法名去掉set/get字符串以後與pojo + "id"想符合(大小寫不敏感),則視為ID } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))) { idMethod = tempMethod; iter.remove(); } } sql += idMethod.getName().substring(3).toLowerCase() + " = ?"; PreparedStatement statement = this.connection.prepareStatement(sql); //為Id字段添加值int i = 0; if(idMethod.getReturnType().getSimpleName().indexOf("String") != -1) { statement.setString(++i, this.getString(idMethod, entity)); } else { statement.setInt(++i, this.getInt(idMethod, entity)); } //執行conn.execOther(statement); //關閉連接conn.closeConn(); } /** * 通過ID查詢*/ public T findById(Object object) throws Exception{ String sql = "select * from " + persistentClass.getSimpleName().toLowerCase() + " where "; //通過子類的構造函數,獲得參數化類型的具體類型.比如BaseDAO<T>也就是獲得T的具體類型T entity = persistentClass.newInstance(); //存放Pojo(或被操作表)主鍵的方法對象Method idMethod = null; List<Method> list = this.matchPojoMethods(entity, "set"); Iterator<Method> iter = list.iterator(); //過濾取得Method對象while(iter.hasNext()) { Method tempMethod = iter.next(); if(tempMethod.getName().indexOf("Id") != -1 && tempMethod.getName().substring(3).length() == 2) { idMethod = tempMethod; } else if((entity.getClass().getSimpleName() + "Id").equalsIgnoreCase(tempMethod.getName().substring(3))){ idMethod = tempMethod; } } //第一個字母轉為小寫sql += idMethod.getName().substring(3,4).toLowerCase()+idMethod.getName().substring(4) + " = ?"; //封裝語句完畢,打印sql語句System.out.println(sql); //獲得連接PreparedStatement statement = this.connection.prepareStatement(sql); //判斷id的類型if(object instanceof Integer) { statement.setInt(1, (Integer)object); } else if(object instanceof String){ statement.setString(1, (String)object); } //執行sql,取得查詢結果集. ResultSet rs = conn.execQuery(statement); //記數器,記錄循環到第幾個字段int i = 0; //把指針指向迭代器第一行iter = list.iterator(); //封裝while(rs.next()) { while(iter.hasNext()) { Method method = iter.next(); if(method.getParameterTypes()[0].getSimpleName().indexOf("String") != -1) { //由於list集合中,method對象取出的方法順序與數據庫字段順序不一致(比如:list的第一個方法是setDate,而數據庫按順序取的是"123"值) //所以數據庫字段採用名字對應的方式取. this.setString(method, entity, rs.getString(method.getName().substring(3).toLowerCase())); } else if(method.getParameterTypes()[0].getSimpleName().indexOf("Date") != -1){ this.setDate(method, entity, rs.getDate(method.getName().substring(3).toLowerCase())); } else if(method.getParameterTypes()[0].getSimpleName().indexOf("InputStream") != -1) { this.setBlob(method, entity, rs.getBlob(method.getName().substring(3).toLowerCase()).getBinaryStream()); } else { this.setInt(method, entity, rs.getInt(method.getName().substring(3).toLowerCase())); } } } //關閉結果集rs.close(); //關閉預編譯對象statement.close(); return entity; } /** * 過濾當前Pojo類所有帶傳入字符串的Method對象,返回List集合. */ private List<Method> matchPojoMethods(T entity,String methodName) { //獲得當前Pojo所有方法對象Method[] methods = entity.getClass().getDeclaredMethods(); //List容器存放所有帶get字符串的Method對象List<Method> list = new ArrayList<Method>(); //過濾當前Pojo類所有帶get字符串的Method對象,存入List容器for(int index = 0; index < methods.length; index++) { if(methods[index].getName().indexOf(methodName) != -1) { list.add(methods[index]); } } return list; } /** * 方法返回類型為int或Integer類型時,返回的SQL語句值.對應get */ public Integer getInt(Method method, T entity) throws Exception{ return (Integer)method.invoke(entity, new Object[]{}); } /** * 方法返回類型為String時,返回的SQL語句拼裝值.比如'abc',對應get */ public String getString(Method method, T entity) throws Exception{ return (String)method.invoke(entity, new Object[]{}); } /** * 方法返回類型為Blob時,返回的SQL語句拼裝值.對應get */ public InputStream getBlob(Method method, T entity) throws Exception{ return (InputStream)method.invoke(entity, new Object[]{}); } /** * 方法返回類型為Date時,返回的SQL語句拼裝值,對應get */ public Date getDate(Method method, T entity) throws Exception{ return (Date)method.invoke(entity, new Object[]{}); } /** * 參數類型為Integer或int時,為entity字段設置參數,對應set */ public Integer setInt(Method method, T entity, Integer arg) throws Exception{ return (Integer)method.invoke(entity, new Object[]{arg}); } /** * 參數類型為String時,為entity字段設置參數,對應set */ public String setString(Method method, T entity, String arg) throws Exception{ return (String)method.invoke(entity, new Object[]{arg}); } /** * 參數類型為InputStream時,為entity字段設置參數,對應set */ public InputStream setBlob(Method method, T entity, InputStream arg) throws Exception{ return (InputStream)method.invoke(entity, new Object[]{arg}); } /** * 參數類型為Date時,為entity字段設置參數,對應set */ public Date setDate(Method method, T entity, Date arg) throws Exception{ return (Date)method.invoke(entity, new Object[]{arg}); }}

EmployeesDao繼承BaseDAO,可以直接使用父類的方法，增加了代碼的複用

package com.employees.dao;import java.util.ArrayList;import java.util.List;import com.employees.po.Employees;public class EmployeesDao extends BaseDAO<Employees> { // 添加員工信息的操作public boolean addEmployees(final Employees employees) throws Exception { save(employees); return true; } // 將員工信息添加到表格中public List<Employees> addEmployees(int id) throws Exception { List<Employees> lstEmployees = new ArrayList<Employees>(); Employees employees = findById(id); // 將當前封轉好的數據裝入對像中lstEmployees.add(employees); return lstEmployees; } public void deleteEmp(final Employees entity) throws Exception { this.delete(entity); } public void updateEmp(final Employees entity) throws Exception { this.update(entity); }}

po層的代碼就不貼了，現在用junit4做一下測試

package com.employees.dao;import org.junit.Test;import com.employees.po.Employees;public class EmployeesDaoTest { @Test public void testAdd() throws Exception { Employees emp = new Employees(); emp.setPname("tly"); emp.setPsex("男"); emp.setPbeliefs("xxxxx"); emp.setPaddr("天河"); emp.setPhobby("打籃球"); emp.setPsubject("計算機"); emp.setPtel("123456"); EmployeesDao dao = new EmployeesDao(); dao.addEmployees(emp); } @Test public void testUpdate() throws Exception { EmployeesDao dao = new EmployeesDao(); Employees emp = dao.findById(14); emp.setPtel("999999"); dao.updateEmp(emp); } @Test public void testdelete() throws Exception { EmployeesDao dao = new EmployeesDao(); Employees emp = dao.findById(15); dao.deleteEmp(emp); }}

經過測試，這三個方法都能正常運行，時間倉促，有些代碼是參考其他哥們的，有些地方可能考慮的不是很全面或者有些代碼會有冗餘，BaseDAO中做通用crud操作沒有寫全，要是哪位小伙伴有興趣，可以接下去寫寫，比如查詢，批量化操作等等，如果測試通過的話，記得給我發一份啊，呵呵

以上這篇簡單通用JDBC輔助類封裝(實例)就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持武林網。