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);
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/386552.html
標籤：其他