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语句,以及实现自己的数据表封装类即可