Java利用反射封装DBUtil，mysql万能增删改查工具类，附源码

Java利用反射封装DBUtil，mysql万能增删改查工具类，附源码

等有时间再慢慢写代码注释吧，先把源码放出来。文章最后有整个项目的压缩包。
ps:拓展
Java 原生MySQL JDBC 插入后返回自增长ID，基于PreparedStatement executeUpdate

导览

1.仅关注DBUtil
看test + util + vo + myPro.properties
2.关注DBUtil怎么用在DAO中
看test + util + vo + myPro.properties + dao + daoImpl
2.仅关注DBUtil怎么用在Service中
看test + util + vo + myPro.properties + dao + daoImpl + service

DBUtil

util DBConnection 连接数据库

public class DBConnection {
    private static Connection conn;

    static {
        try {
            Properties properties = new Properties();
            //适用于任何情况下查找properties文件
            String propertiesPath = Objects.requireNonNull(DBConnection.class.getClassLoader().getResource("myPro.properties")).getPath().replace("%20"," ");
            properties.load(new FileInputStream(propertiesPath));

            //Java项目简单粗暴查找properties文件
            //String propertiesPath = "src/com/laner/myPro.properties";
            //properties.load(new FileInputStream(propertiesPath));

            //Java和Web项目查找properties文件,但不适用于文件路径中有空格
            //Properties properties  = new Properties();
            //properties.load(DBUtils.class.getClassLoader().getResourceAsStream("db.properties"));

            String dbClassName = (String)properties.get("DBClassName");
            String dbUrl = (String)properties.get("DBURL");
            String dbUsername = (String)properties.get("DBUsername");
            String dbPassword = (String)properties.get("DBPassword");

            Class.forName(dbClassName);
            conn = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn() {
        return conn;
    }
}

util DBExecute 操作数据库

public class DBExecute {

    //万能查询方法，返回Map<Integer, Object>
    // Integer只是计数，Object为数据
    public static Map<Integer, Object> executeQuery(String sql, Object... args){
        Map<Integer, Object> map = new HashMap();
        int key = 0;
        try(PreparedStatement ps = DBConnection.getConn().prepareStatement(sql)) {
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ResultSet rs = ps.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()){
                for (int i = 1; i <= columnCount; i++) {
                    map.put(key++, rs.getObject(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    //查询方法，返回相应对象集合，受限很多，但是新手阶段的很常用，对于理解反射和数据库封装非常有帮助
    // 适用于任何表: select * from / select u_id,u_name,... from
    // 不适用于返回值与类属性无关的sql: 例如：select count(*)/sun(*)/min(*)等
    // 不适用于多表查询的sql: 例如：select id, name from a, b等
    public static <T> List<T> executeQuery(Class<T> tClass, String sql, Object... args){
        List<T> list = new ArrayList<>();

        try(PreparedStatement ps = DBConnection.getConn().prepareStatement(sql)) {
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            Constructor<T> constructor = tClass.getConstructor();
            while (rs.next()){
                T t = constructor.newInstance();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    String columnName = rsmd.getColumnName(i);

                    Field field = tClass.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, rs.getObject(columnName));
                }
                list.add(t);
            }
        } catch (SQLException | NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }

        return list;
    }

    //万能修改方法，返回受影响行数int
    public static int executeUpdate(String sql, Object... args){
        try(PreparedStatement ps = DBConnection.getConn().prepareStatement(sql)) {
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }
}

Propertise

myPro.propertise 存放数据库的信息

DBClassName = com.mysql.cj.jdbc.Driver
DBURL = jdbc:mysql://localhost:3306/mydatabase
DBUsername = root
DBPassword = root

Test

testDBExecute() 测试

public class Test {
    public static void main(String[] args) {
        testDBExecute();
        testDaoImpl();
        testService();
    }

    //就测测DBUtil
    public static void testDBExecute(){
        System.out.println("===========================================================================");
        System.out.println("+++++++Map<Integer, Object> executeQuery(String sql, Object... args)+++++++");
        Map<Integer, Object> map1 = DBExecute.executeQuery("select count(*) from address where a_id like '__'");
        Map<Integer, Object> map2 = DBExecute.executeQuery("select a_id, a_name from address where a_id like '__'");
        map1.forEach(new BiConsumer<Integer, Object>() {
            @Override
            public void accept(Integer integer, Object o) {
                System.out.println("总数: " + o);
            }
        });
        map2.forEach(new BiConsumer<Integer, Object>() {
            @Override
            public void accept(Integer integer, Object o) {
                System.out.println(o);
            }
        });



        System.out.println("===========================================================================");
        System.out.println("+++++List<T> executeQuery(Class<T> tClass, String sql, Object... args)+++++");
        List<Address> addresses1 = DBExecute.executeQuery(Address.class, "select * from address where a_id like concat(?,'__')", 21);
        for (Address address : addresses1) {
            System.out.println(address);
        }
        List<Address> addresses2 = DBExecute.executeQuery(Address.class, "select a_name, a_view_order from address where a_id like concat(?,'__')", 21);
        for (Address address : addresses2) {
            System.out.println(address);
        }



        System.out.println("===========================================================================");
        System.out.println("++++++++++++++++++++++++++DBExecute.executeUpdate++++++++++++++++++++++++++");
        DBExecute.executeUpdate("insert into address values(null, ?, ?, ?)",  "佛山", 34, 12);
        DBExecute.executeUpdate("delete from address where a_view_order = ? and a_name = ?", 13, "佛山");
        DBExecute.executeUpdate("update address set a_id = ? where a_name = ?", 80, "佛山");
    }

    //想看看DBUtil怎么用在DAO中
    public static void testDaoImpl(){
        AddressDao addressDao = new AddressDaoImpl();
        List<Address> allBooks = addressDao.getAllBooks();
        for (Address book : allBooks) {
            System.out.println(book);
        }
    }

    //想看看DBUtil怎么用在Service中
    public static void testService(){
        AddressService addressService = new AddressService();
        List<Address> allBooks = addressService.getAllBooks();
        for (Address book : allBooks) {
            System.out.println(book);
        }
    }
}

总结

优化了一点点
歇了～～有空再写注释
进我主页，免费下载源码（不知道怎么把资源链接放上来啊～～）