仿照hibernate封裝的一個對資料庫操作的jdbc工具類

package project02_Order_management.util;import java.io.IOException;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Properties;/** * 封裝對資料庫進行--串連/增/刪/改/查/ *  * @author MartinDong *  * @param <T> *            要操作的資料對象 */public class BaseDAOUtil<T> {/** * 聲明資料庫連接對象 */private static Connection connection;/** * 聲明預先處理對象 */private static PreparedStatement preparedStatement;/** * 聲明sql語句返回結果集對象 */private static ResultSet resultSet;/** * 載入預設的configuration.properties資源檔 *  * @return */public static Properties getProperties() {return getProperties(null);}/** * 載入資源檔 *  * @param propertyName *            傳入要載入的資源檔名稱; * @return properties 返回一個屬性設定物件 */public static Properties getProperties(String propertyName) {/** * 設定配置資源檔的預設檔案名稱 */if (propertyName == null) {propertyName = "configuration.properties";}/** * 聲明屬性檔案類,讀取配置使用 */Properties properties = new Properties();try {/** * currentThread()是Thread的一個靜態方法，返回的是當前的進程對象 */properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream(propertyName));} catch (IOException e) {System.out.println(propertyName + "檔案載入出現錯誤!");e.printStackTrace();}return properties;}/** * 擷取預設的資料庫連接對象 *  * @return */public static Connection getConnection() {return getConnection(getProperties());}/** * 擷取資料庫連接對象 *  * @param properties *            傳入已經配置好的屬性設定物件; * @return <b>connection</b> 資料庫連接對象 */public static Connection getConnection(Properties properties) {if (connection == null) {/** * 載入資料庫驅動檔案 */try {Class.forName(properties.getProperty("jdbc.driver"));/** * 建立資料庫連接對象 */try {connection = DriverManager.getConnection(properties.getProperty("jdbc.url"),properties.getProperty("jdbc.user"),properties.getProperty("jdbc.password"));System.out.println("資料庫連接成功>>>>>>>>>>>");} catch (SQLException e) {System.out.println("資料庫連接參數錯誤!");e.printStackTrace();}} catch (ClassNotFoundException e) {System.out.println("缺少資料庫驅動檔案:"+ properties.getProperty("jdbc.driver") + "!");e.printStackTrace();}}return connection;}/** * 釋放資源的方法;<br> *  * @param releaseSet * @param preparedStatement * @param connection */public static void release(ResultSet releaseSet,PreparedStatement preparedStatement, Connection connection) {if (releaseSet != null) {try {releaseSet.close();} catch (SQLException e) {e.printStackTrace();}}if (preparedStatement != null) {try {preparedStatement.close();} catch (SQLException e) {e.printStackTrace();}}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}// /////////////////////////////////CRUD基礎業務//////////////////////////////** * 採用預設的串連,並且資料庫表名與實體類名一致[不區分大小寫] *  * @param entity * @throws SQLException * @throws InvocationTargetException * @throws IllegalArgumentException * @throws IllegalAccessException */public void save(T entity) throws IllegalAccessException,IllegalArgumentException, InvocationTargetException, SQLException {save(entity, getConnection());}/** * 採用資料庫表名與實體類名一致[不區分大小寫]外部傳入資料庫連接; *  * @param entity * @param connection * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public void save(T entity, Connection connection)throws IllegalAccessException, IllegalArgumentException,InvocationTargetException, SQLException {save(entity, connection, null);}/** * 將實體存入資料庫 *  * @param entity *            要操作的資料對象 * @param connection *            傳資料庫連接 * @param tableName *            要操作的表的名稱,如果傳入null,則對傳入的對象名稱一致的表進行操作 * @throws SQLException * @throws InvocationTargetException * @throws IllegalArgumentException * @throws IllegalAccessException */public void save(T entity, Connection connection, String tableName)throws SQLException, IllegalAccessException,IllegalArgumentException, InvocationTargetException {/** * 擷取操作實體的類型 */Class<? extends Object> clazz = entity.getClass();/** * 擷取傳入實體的所有公開的方法; */Method[] methods = clazz.getDeclaredMethods();/** * 擷取傳入實體中的所有公開的的屬性 */Field[] fields = clazz.getDeclaredFields();/** * 如果沒有輸入指定的資料表名酒採用類名進行操作 */if (tableName == null) {tableName = clazz.getSimpleName().toLowerCase();}/** * 拼接類中的屬性欄位,即資料表中的欄位名 */String fieldsName = "";/** * 預留位置的設定 */String placeholder = "";for (int i = 0; i < fields.length; i++) {fieldsName = fieldsName + fields[i].getName() + ",";placeholder = placeholder + "?" + ",";}/** * 去除多餘的標點 */fieldsName = fieldsName.substring(0, fieldsName.length() - 1);placeholder = placeholder.substring(0, placeholder.length() - 1);/** * 拼接sql語句 */String sql = "insert into " + tableName + "(" + fieldsName + ")"+ " values " + "(" + placeholder + ")";System.out.println(sql);/** * 先行編譯sql語句 */PreparedStatement pst = connection.prepareStatement(sql);/** * 給先行編譯語句賦值 */int index = 1;for (int j = 0; j < fields.length; j++) {String str = "get" + fields[j].getName();/** * 迴圈方法名比對 */for (int k = 0; k < methods.length; k++) {/** * 如果當前的屬性拼出的get方法名,與方法明集合中的有一樣的執行 */if (str.equalsIgnoreCase(methods[k].getName())) {/** * 接收指定的方法執行後的資料 */Object propertyObj = methods[k].invoke(entity);/** * 為指定的預留位置進行賦值 */pst.setObject(index++, propertyObj);}}}/** * 執行已經載入的sql語句 */pst.executeUpdate();}/** * 使用預設的資料庫連接進行刪除,傳入的對象 *  * @param entity * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public void delete(T entity) throws IllegalAccessException,IllegalArgumentException, InvocationTargetException, SQLException {deleteById(entity, getConnection());}/** * 使用傳入的資料庫連接,刪除指定的對象. *  * @param entity * @param connection * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public void deleteById(T entity, Connection connection)throws IllegalAccessException, IllegalArgumentException,InvocationTargetException, SQLException {delete(entity, connection, null);}/** *  * @param entity *            傳入操作的對象實體 * @param connection *            傳入資料庫連接對象 * @param id *            要刪除資料的id * @param tableName *            要操作的表的名稱,如果傳入null,則對傳入的對象名稱一致的表進行操作 * @throws SQLException * @throws InvocationTargetException * @throws IllegalArgumentException * @throws IllegalAccessException *  */public void delete(T entity, Connection connection, String tableName)throws SQLException, IllegalAccessException,IllegalArgumentException, InvocationTargetException {Class<? extends Object> clazz = entity.getClass();Method[] methods = clazz.getDeclaredMethods();Field[] fields = clazz.getDeclaredFields();if (tableName == null) {tableName = clazz.getSimpleName().toLowerCase();}String sql = "delete from " + tableName + " where id=?";System.out.println(sql);PreparedStatement pst = connection.prepareStatement(sql);Object id = null;for (int i = 0; i < fields.length; i++) {for (int j = 0; j < methods.length; j++) {if ("getId".equalsIgnoreCase(methods[j].getName())) {id = methods[j].invoke(entity);}}}pst.setObject(1, id);pst.executeUpdate();}/** * 使用預設的資料庫連接修改傳入的對象. *  * @param entity * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public void update(T entity) throws IllegalAccessException,IllegalArgumentException, InvocationTargetException, SQLException {update(entity, getConnection());}/** * 使用傳入的資料庫連接進行資料庫修改; *  * @param entity * @param connection * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public void update(T entity, Connection connection)throws IllegalAccessException, IllegalArgumentException,InvocationTargetException, SQLException {update(entity, connection, null);}/** *  * @param entity *            傳入操作的對象實體 * @param connection *            傳入資料庫連接對象 * @param tableName *            要操作的表的名稱,如果傳入null,則對傳入的對象名稱一致的表進行操作 * @throws InvocationTargetException * @throws IllegalArgumentException * @throws IllegalAccessException * @throws SQLException */public void update(T entity, Connection connection, String tableName)throws IllegalAccessException, IllegalArgumentException,InvocationTargetException, SQLException {Class<? extends Object> clazz = entity.getClass();Method[] methods = clazz.getDeclaredMethods();Field[] fields = clazz.getDeclaredFields();if (tableName == null) {tableName = clazz.getSimpleName().toLowerCase();}String fieldsName = "";// 建立id欄位的預設資料Object id = 1;// 迴圈遍曆以擷取的公開的屬性for (int i = 0; i < fields.length; i++) {// 嵌套迴圈,比較公開屬性名稱經過拼接後和公開的方法名進行匹配for (int j = 0; j < methods.length; j++) {// 使用屬性名稱+get進行拼接String getFieldName = "get" + fields[i].getName();if (getFieldName.equalsIgnoreCase(methods[j].getName())) {// 拼接更行sql語句中的set欄位,並用預留位置fieldsName = fieldsName + fields[i].getName() + "=?,";}// 擷取id欄位的值if ("getId".equalsIgnoreCase(methods[j].getName())) {id = methods[j].invoke(entity);}}}fieldsName = fieldsName.substring(0, fieldsName.length() - 1);String sql = "update " + tableName + " set " + fieldsName+ " where id=?";System.out.println(sql);PreparedStatement pst = connection.prepareStatement(sql);int index = 1;for (int j = 0; j < fields.length; j++) {String str = "get" + fields[j].getName();// 迴圈方法名比對for (int k = 0; k < methods.length; k++) {// 如果當前的屬性拼出的get方法名,與方法明集合中的有一樣的執行if (str.equalsIgnoreCase(methods[k].getName())) {// 接收指定的方法執行後的資料Object propertyObj = methods[k].invoke(entity);// 為指定的預留位置進行賦值pst.setObject(index++, propertyObj);}}}pst.setObject(index++, id);pst.execute();}/** * 使用預設的資料庫連接查詢指定的id資料 *  * @param entity * @param id * @return * @throws InstantiationException * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public T findById(T entity, Integer id) throws InstantiationException,IllegalAccessException, IllegalArgumentException,InvocationTargetException, SQLException {return findById(entity, null, id);}/** *  * 使用傳入的資料庫連接以及傳入的id查詢資料; *  * @param entity * @param connection * @param id * @return * @throws InstantiationException * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public T findById(T entity, Connection connection, Integer id)throws InstantiationException, IllegalAccessException,IllegalArgumentException, InvocationTargetException, SQLException {return findById(entity, connection, id, null);}/** *  * 根據id查詢資料 *  * @param entity *            查詢的實體物件 * @param connection *            資料庫連接對象 * @param id *            查詢的id * @param tableName *            操作的資料庫表名 * @return 返回一個查詢結果對象 * @throws SQLException * @throws InstantiationException * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException */public T findById(T entity, Connection connection, Integer id,String tableName) throws SQLException, InstantiationException,IllegalAccessException, IllegalArgumentException,InvocationTargetException {Class<? extends Object> clazz = entity.getClass();Method[] methods = clazz.getDeclaredMethods();Field[] fields = clazz.getDeclaredFields();// 聲明查詢的結果對象T resultObject = null;if (tableName == null) {tableName = clazz.getSimpleName().toLowerCase();}String sql = "select * from " + tableName + " where id=?";System.out.println(sql);PreparedStatement pst = connection.prepareStatement(sql);pst.setObject(1, id);ResultSet resultSet = pst.executeQuery();if (resultSet.next()) {resultObject = (T) clazz.newInstance();for (int i = 0; i < fields.length; i++) {String fieldName = fields[i].getName();Object fieldObject = resultSet.getObject(i + 1);if (fieldObject == null) {fieldObject = "null";// 防止資料為null時引發null 指標異常}for (int j = 0; j < methods.length; j++) {if (("set" + fieldName).equalsIgnoreCase(methods[j].getName())) {methods[j].invoke(resultObject,resultSet.getObject(fieldName));}}}}return resultObject;}/** * 使用預設的資料庫連接進行資料查詢 *  * @param entity * @return * @throws InstantiationException * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public List<T> findAll(T entity) throws InstantiationException,IllegalAccessException, IllegalArgumentException,InvocationTargetException, SQLException {return findAll(entity, getConnection());}/** * 使用傳入的資料庫連接進行資料查詢 *  * @param entity * @param connection * @return * @throws InstantiationException * @throws IllegalAccessException * @throws IllegalArgumentException * @throws InvocationTargetException * @throws SQLException */public List<T> findAll(T entity, Connection connection)throws InstantiationException, IllegalAccessException,IllegalArgumentException, InvocationTargetException, SQLException {return findAll(entity, connection, null);}/** * 查詢資料表所有的資料 *  * @param entity *            查詢的實體物件 * @param connection *            資料庫連接對象 * @param tableName *            操作的資料庫表名 * @return * @throws SQLException * @throws IllegalAccessException * @throws InstantiationException * @throws InvocationTargetException * @throws IllegalArgumentException */public List<T> findAll(T entity, Connection connection, String tableName)throws SQLException, InstantiationException,IllegalAccessException, IllegalArgumentException,InvocationTargetException {Class<? extends Object> clazz = entity.getClass();Method[] methods = clazz.getDeclaredMethods();Field[] fields = clazz.getDeclaredFields();// 聲明查詢的結果對象List<T> resultObjects = new ArrayList<T>();if (tableName == null) {tableName = clazz.getSimpleName().toLowerCase();}String sql = "select * from " + tableName;System.out.println(sql);PreparedStatement pst = connection.prepareStatement(sql);ResultSet resultSet = pst.executeQuery();while (resultSet.next()) {T resultObject = (T) clazz.newInstance();for (int i = 0; i < fields.length; i++) {String fieldName = fields[i].getName();Object fieldObject = resultSet.getObject(i + 1);if (fieldObject == null) {fieldObject = "null";}for (int j = 0; j < methods.length; j++) {if (("set" + fieldName).equalsIgnoreCase(methods[j].getName())) {methods[j].invoke(resultObject,resultSet.getObject(fieldName));}}}resultObjects.add(resultObject);}return resultObjects;}public List<T> query(T entity, Connection connection, String tableName,String sql) {return null;}/** * 一個需要使用者手動輸入sql和參數語句的:增/刪/改/的操作 *  * @param sql * @param args * @return */public static int upDate(String sql, Object[] args) {try {preparedStatement = getConnection().prepareStatement(sql);for (int i = 1; i <= args.length; i++) {preparedStatement.setObject(i, args[i - 1]);}return preparedStatement.executeUpdate();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return 0;}/** * 傳入自訂的sql語句和參數進行查詢; *  * @param sql *            sql語句 * @param args *            傳入的參數條件 * @return 返回一個set集合 */public static ResultSet getObject(String sql, Object[] args) {System.out.println(sql);try {preparedStatement = JDBCUtil.getConnection().prepareStatement(sql);if (args != null) {for (int i = 1; i <= args.length; i++) {preparedStatement.setObject(i, args[i - 1]);}}resultSet = preparedStatement.executeQuery();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return resultSet;}}

configuration.properties設定檔

jdbc.url=jdbc\:mysql\://localhost\:3306/order_managerjdbc.user=rootjdbc.password=adminjdbc.driver=com.mysql.jdbc.Driver