DBUtils工具类实现增删改查
- 一、数据库连接池Druid工具类
- 二、DBUtils实现增删改
- 三、DBUtils实现查询单条数据
- 四、DBUtils实现查询批量数据
- 五、DBUtils实现按键值对查询数据
- 六、查询单个数据
- QueryRunner提供对sql语句操作的API
- ResultSetHandler接口，用于定义select操作后，怎样封装结果集
结果集处理类 | 描述 |
ArrayHandler | 将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值 |
ArrayListHandler | 将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中 |
BeanHandler | 将结果集中第一条记录封装到一个制定的javaBean中 |
BeanListHandler | 将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中 |
ColumnListHandler | 将结果集中指定的列的字段值，封装到一个List集合中 |
KeyedHandler | 将结果集中每一条记录封装到Map<String,Object>，在将这个map集合做为另一个Map的value，另一个Map集合的key是指定的字段的值 |
MapHandler | 将结果集中第一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值 |
MapListHandler | 将结果集中每一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值，在将这些Map封装到List集合中 |
ScalarHandler | 它是用于单个数据。例如select count(*) from 表操作 |
一、数据库连接池Druid工具类
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.beanutils.PropertyUtils;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public class JdbcUtils {
private static DataSource ds = null;
static {
try {
Properties props = new Properties();
props.load(JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
ds = DruidDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new RuntimeException("读取配置文件异常", e);
}
}
//Druid连接池
public static Connection getDruidConnection() throws Exception {
Connection conn = null;
conn = ds.getConnection();
return conn;
}
public static void release(Connection conn) throws SQLException {
if(conn != null) conn.close();
}
public static void release(Connection conn, PreparedStatement ps) throws SQLException {
if(ps!=null) ps.close();
if(conn!=null) conn.close();
}
public static void release(Connection conn, PreparedStatement ps, java.sql.ResultSet rs) throws SQLException {
if(rs!=null) rs.close();
if(ps!=null) ps.close();
if(conn!=null) conn.close();
}
}二、DBUtils实现增删改
import com.bigdata.plus.Entity.DatabaseInfo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class DBUtilsTest {
private QueryRunner qr = new QueryRunner();
@Test
public void test1() throws Exception {
Connection conn = JdbcUtils.getDruidConnection();
String sql = "insert into dw.stuinfo(stunum,name,age,hobby,create_time) values(?,?,?,?,?)";
Object[] params = {10012, "杀生丸", 18, "打妖怪",new java.sql.Date(new Date().getTime())};
int row = qr.update(conn, sql, params);
System.out.println("已影响" + row + "行");
JdbcUtils.release(conn,null,null);
}
}成功插入数据：
三、DBUtils实现查询单条数据
import com.bigdata.plus.Entity.DatabaseInfo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class DBUtilsTest {
private QueryRunner qr = new QueryRunner();
@Test
public void test2() throws Exception {
Connection conn = JdbcUtils.getDruidConnection();
String sql = "select t.TABLE_SCHEMA as databaseName,CONCAT_WS('.',t.TABLE_SCHEMA,t" +
".TABLE_NAME)" +
" as " +
"tableName,t.TABLE_COMMENT as tableComment,t.TABLE_ROWS as tableRows,k.COLUMN_NAME as " +
"tableKey\n" +
"from INFORMATION_SCHEMA.TABLES as t\n" +
"inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k\n" +
"on k.TABLE_NAME=t.TABLE_NAME and CONSTRAINT_NAME = 'PRIMARY' and t.TABLE_SCHEMA=?";
Object[] params = {"dw"};
BeanHandler<DatabaseInfo> rsh = new BeanHandler<>(DatabaseInfo.class);
DatabaseInfo databaseInfo = qr.query(conn, sql, rsh, params);
System.out.println(databaseInfo);
JdbcUtils.release(conn,null,null);
}
}
四、DBUtils实现查询批量数据
package com.bigdata.plus;
import com.bigdata.plus.Entity.DatabaseInfo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class DBUtilsTest {
private QueryRunner qr = new QueryRunner();
@Test
public void test3() throws Exception {
Connection conn = JdbcUtils.getDruidConnection();
String sql = "select t.TABLE_SCHEMA as databaseName,CONCAT_WS('.',t.TABLE_SCHEMA,t" +
".TABLE_NAME)" +
" as " +
"tableName,t.TABLE_COMMENT as tableComment,t.TABLE_ROWS as tableRows,k.COLUMN_NAME as " +
"tableKey\n" +
"from INFORMATION_SCHEMA.TABLES as t\n" +
"inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k\n" +
"on k.TABLE_NAME=t.TABLE_NAME and CONSTRAINT_NAME = 'PRIMARY' and t.TABLE_SCHEMA=?";
Object[] params = {"dw"};
BeanListHandler<DatabaseInfo> rsh = new BeanListHandler<>(DatabaseInfo.class);
List<DatabaseInfo> databaseInfos = qr.query(conn, sql, rsh, params);
for(DatabaseInfo databaseInfo : databaseInfos ){
System.out.println(databaseInfo);
}
JdbcUtils.release(conn,null,null);
}
}
五、DBUtils实现按键值对查询数据
import com.bigdata.plus.Entity.DatabaseInfo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class DBUtilsTest {
private QueryRunner qr = new QueryRunner();
@Test
public void test4() throws Exception {
Connection conn = JdbcUtils.getDruidConnection();
String sql = "select t.TABLE_SCHEMA as databaseName,CONCAT_WS('.',t.TABLE_SCHEMA,t" +
".TABLE_NAME)" +
" as " +
"tableName,t.TABLE_COMMENT as tableComment,t.TABLE_ROWS as tableRows,k.COLUMN_NAME as " +
"tableKey\n" +
"from INFORMATION_SCHEMA.TABLES as t\n" +
"inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k\n" +
"on k.TABLE_NAME=t.TABLE_NAME and CONSTRAINT_NAME = 'PRIMARY' and t.TABLE_SCHEMA=?";
Object[] params = {"dw"};
MapListHandler mapListHandler = new MapListHandler();
List<Map<String, Object>> query = qr.query(conn, sql, mapListHandler, params);
for(Map<String,Object> map : query){
Set<Map.Entry<String, Object>> entries = map.entrySet();
for(Map.Entry<String,Object> entry : entries){
String key = entry.getKey();
Object value = entry.getValue();
System.out.println(key + " ======== " + value);
}
System.out.println("-----------------------------------");
}
JdbcUtils.release(conn,null,null);
}
}
六、查询单个数据
import com.bigdata.plus.Entity.DatabaseInfo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class DBUtilsTest {
private QueryRunner qr = new QueryRunner();
@Test
public void test5() throws Exception {
Connection conn = JdbcUtils.getDruidConnection();
String sql = "select count(1) from dw.stuinfo";
ScalarHandler<Object> objectScalarHandler = new ScalarHandler<>();
Object query = qr.query(conn, sql, objectScalarHandler);
System.out.println(query);
JdbcUtils.release(conn,null,null);
}
}