DBUtils工具类实现增删改查

admin • • 研发管理

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 " +
                "tableKeyn" +
                "from INFORMATION_SCHEMA.TABLES as tn" +
                "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kn" +
                "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 " +
                "tableKeyn" +
                "from INFORMATION_SCHEMA.TABLES as tn" +
                "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kn" +
                "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 " +
                "tableKeyn" +
                "from INFORMATION_SCHEMA.TABLES as tn" +
                "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kn" +
                "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);
    }

}

在这里插入图片描述

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>