Java 使用apacheDBUtil简化JDBC的数据表与数据封装对象的相互转化

Oracle的Apache软件基金会提供了commons.dbutils-1.7.jar等不同版本的jar包，封装了数据库的的存取操作，方便了我们对数据库数据和类对象的相互转换，在项目中导入该jar包即可简化数据库的增删改查操作

获取连接的封装类

package com.mypackage.util;

import java.sql.*;

public class MySQLDBUtil {

    public static final String URL = "jdbc:mysql://localhost:3306/demo";//注意改成自己的数据库名称
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * @return 返回连接对象
     */
    public static Connection getConnection(){
        try {
            return DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}

---

DBUtil测试类

package com.mypackage.apacheDBUtil;

import com.mypackage.entity.Emp;
import com.mypackage.util.MySQLDBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class DBUtilTest {

    public static Connection connection ;

    public static void testQuery() throws SQLException {
        connection = MySQLDBUtil.getConnection();
        //apacheDBUtil中用于执行sql的对象
        QueryRunner runner = new QueryRunner();
        String sql = null;"select * from emp where empno=?";
        
        //Emp为数据库数据的封装类
        
        //1.BeanHandler查询一条数据并转换为数据封装类对象
        sql = "select * from emp where empno=?";
        Emp query = runner.query(connection, sql, new BeanHandler<Emp>(Emp.class), 7369);
        System.out.println(query);
        
        //2.BeanListHandler查询一组数据转换为一组数据封装类对象，用List存储
        sql ="select * from emp";
       	List<Emp> query = runner.query(connection, sql, new BeanListHandler<Emp>(Emp.class));
        for (Emp emp : query) {
            System.out.println(emp);
        }
        
        //3.ArrayHandler查询一条数据转换为Object数组，每列存在Object数组的每个元素中
		sql ="select * from emp";
        Object[] object = runner.query(connection, sql, new ArrayHandler());
        for (Object o : object) {
            System.out.println(o);
        }
		
		//4.ArrayListHandler查询一组数据转换为Object数组，每列存在Object数组的每个元素中，并用List存储
		sql ="select * from emp";
        List<Object[]> query = runner.query(connection, sql, new ArrayListHandler());
        for (Object[] objects : query) {
            System.out.println(objects[0]+"--"+objects[1]);
        }
		
		//5.MapHandler查询一条数据，列名作为key，列的值作为value存于map中
		sql ="select * from emp where empno = ?";
        Map<String, Object> query = runner.query(connection, sql, new MapHandler(), 7369);
        Set<Map.Entry<String, Object>> entries = query.entrySet();
        for (Map.Entry<String, Object> entry : entries) {
            System.out.println(entry.getKey()+"---"+entry.getValue());
        }

		//6.ScalarHandler执行组函数sql，返回一个Object类型
		sql ="select count(*) from emp";
        Object query = runner.query(connection, sql, new ScalarHandler<>());
        System.out.println(query);

		//7.自定义Handler
		String sql ="select * from emp where empno = ?";
        Emp emp = runner.query(connection, sql, new ResultSetHandler<Emp>() {
        	//匿名内部类，实现ResultSetHandler接口
            @Override
            public Emp handle(ResultSet resultSet) throws SQLException {
                if(resultSet.next()){
                    Emp e = new Emp();
                    e.setEmpno(resultSet.getInt("empno"));
                    e.setEname(resultSet.getString("ename"));
                    return e;
                }
                return null;
            }
        },7369);
        System.out.println(emp);
		
        connection.close();
    }

    public static void insert() throws SQLException {
        String sql = "insert into emp(empno,ename) values(?,?)";
        connection = MySQLDBUtil.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,1234,"zsf");
        connection.close();
    }

    public static void update() throws SQLException {
        String sql = "update emp set ename=? where empno = ?";
        connection = MySQLDBUtil.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,"lisi",1234);
        connection.close();
    }

    public static void delete() throws SQLException {
        String sql = "delete from emp where empno=?";
        connection = MySQLDBUtil.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        queryRunner.update(connection,sql,1234);
        connection.close();
    }
    public static void main(String[] args) throws SQLException {
        testQuery();
        insert();
        update();
        delete();
    }
}

在测试上述java文件时注意改成自己的数据库、数据库名、数据库表、sql语句，以及实现自己的数据表封装类即可