仿照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时引发空指针异常 } 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 block e.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 block e.printStackTrace(); } return resultSet; } }
configuration.properties配置文件
jdbc.url=jdbc\:mysql\://localhost\:3306/order_manager
jdbc.user=root
jdbc.password=admin
jdbc.driver=com.mysql.jdbc.Driver