Java Apache DBUtil
当你的能力还驾驭不了你的目标的时候，你就应该沉下心来历练
https://commons.apache.org/proper/commons-dbutils/
| 名称 | 说明 |
|---|---|
| DBUtils | 辅助（打开&关闭连接.....） |
| QueryRunner | 增删查改 |
| ResultSetHandler | 如果是 “查” 操作，则需要ResultSetHandler来处理结果集 |
| ArrayHandler | 返回结果集中的第一行数据，用Object[ ] 数组接收。 |
| BeanHandler | 返回结果集中的第一行数据，并封装到一个对应的JavaBean对象实例中。 |
| BeanListHandler | 返回结果集中的多行数据，并封装存放到List里 |
| MapHandler | 将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。//重点 |
| MapListHandler | 将结果集中的每一行数据都封装到一个Map里，然后再存放到List |
| ColumnListHandler | ColumnListHandler |
| ScalarHandler | 将结果集第一行的某一列放到某个对象中 |
MySQLDBUtil
package com.cmz.util;
import java.sql.*;
/**
* @author summer
* @create 2020-02-16 13:32
*/
public class MySQLDBUtil {
public static final String URL = "jdbc:mysql://localhost:3306/test";
public static final String USERNAME = "root";
public static final String PASSWORD = "root";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/***
* 获取数据连接
*/
public static Connection getConnection(){
try {
return DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/*
* 关闭连接
* */
public static void closeConnection(Connection connection,Statement statement){
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com.cmz.apacheDBUtil;
import com.cmz.entity.Emp;
import com.cmz.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;
/**
* @author summer
* @create 2020-02-16 18:42
*/
public class DBUtilTest {
public static Connection connection;
public static void testQuery() throws SQLException {
connection = MySQLDBUtil.getConnection();
String sql = "select * from emp where empno=?";
QueryRunner runner = new QueryRunner();
Emp query = runner.query(connection, sql, new BeanHandler<Emp>(Emp.class),7369);
System.out.println(query);
connection.close();
}
public static void testList() throws SQLException {
connection = MySQLDBUtil.getConnection();
String sql = "select * from emp";
QueryRunner runner = new QueryRunner();
List<Emp> query = runner.query(connection, sql, new BeanListHandler<Emp>(Emp.class));
for (Emp emp : query) {
System.out.println(emp);
}
connection.close();
}
public static void testArray() throws SQLException {
connection = MySQLDBUtil.getConnection();
String sql = "select * from emp";
QueryRunner runner = new QueryRunner();
Object[] object = runner.query(connection, sql, new ArrayHandler());
for (Object o : object) {
System.out.println(o);
}
connection.close();
}
public static void testArrayList() throws SQLException {
connection = MySQLDBUtil.getConnection();
String sql = "select * from emp";
QueryRunner runner = new QueryRunner();
List<Object[]> query = runner.query(connection, sql, new ArrayListHandler());
for (Object[] objects : query) {
System.out.println(objects[0]+"---"+objects[1]);
}
connection.close();
}
public static void testMap() throws SQLException {
connection = MySQLDBUtil.getConnection();
String sql = "select * from emp";
QueryRunner runner = new QueryRunner();
Map<String, Object> query = runner.query(connection, sql, new MapHandler());
Set<Map.Entry<String, Object>> entries = query.entrySet();
for (Map.Entry<String, Object> entry : entries) {
System.out.println(entry.getKey()+"----"+entry.getValue());
}
connection.close();
}
public static void testSaclarHander() throws SQLException {
connection = MySQLDBUtil.getConnection();
String sql = "select count(1) from emp";
QueryRunner runner = new QueryRunner();
Object query = runner.query(connection, sql, new ScalarHandler<>());
System.out.println(query);
connection.close();
}
//自定义
public static void testMyHander() throws SQLException {
connection = MySQLDBUtil.getConnection();
String sql ="select * from emp where empno = ?";
QueryRunner runner = new QueryRunner();
Emp emp = runner.query(connection, sql, new ResultSetHandler<Emp>() {
@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,"cmz");
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,"leco",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();
// testList();
// testArray();
// testArrayList();
// testMap();
// testSaclarHander();
// testMyHander();
// insert();
// update();
delete();
}
}