簡略通用JDBC幫助類封裝(實例)。本站提示廣大學習愛好者：（簡略通用JDBC幫助類封裝(實例)）文章只能為提供參考，不一定能成為您想要的結果。以下是簡略通用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.Driver DATABASE_URL=jdbc:mysql SERVER_IP=localhost SERVER_PORT=3306 DATABASE_SID=employees USERNAME=root PASSWORD=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幫助類封裝(實例)就是小編分享給年夜家的全體內容了，願望能給年夜家一個參考，也願望年夜家多多支撐。
