Druid和QueryRunner类实现对数据库表中数据的增删改查

 

可选的配置

spring:
  datasource:
    username: root
    password: root
    url: jdbc:gbase://192.168.170.128:8181/text:GBASEDBTSERVER=gbase01;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;IFX_LOCK_MODE_WAIT=30
    driver-class-name: com.gbase.jdbc.Driver
    type: com.gbase.jdbc.jdbc2.optional.GBaseDataSource
#    factory: com.alibaba.druid.pool.DruidDataSourceFactory
    #Spring Boot 默认是不注入这些属性值的，需要自己绑定
    #druid 数据源专有配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 打开PSCache
    poolPreparedStatements: true
    #配置监控统计拦截的filters，stat:监控统计、log4j：日志记录、wall：防御sql注入
    #如果运行时报错  java.lang.ClassNotFoundException: org.apache.log4j.Priority
    #则导入 log4j 依赖即可，Maven 地址： https://mvnrepository.com/artifact/log4j/log4j
    filters: stat
    #指定每个连接上PSCache的大小
    maxPoolPreparedStatementPerConnectionSize: 20
    #合并多个DruidDataSource的监控数据
    useGlobalDataSourceStat: true
    #通过connectProperties属性来打开mergeSql功能；慢SQL记录
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

 

 

 <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
</dependency>
<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.7</version>
</dependency>

package com.example.demo.config;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBSUtl {
    private static DruidDataSource dataSource;
    //静态代码块
    static{
        dataSource = new DruidDataSource();
        //获取url
        dataSource.setUrl("jdbc:mysql://localhost:3306/supermarket_sys?useUnicode=true&characterEncoding=utf-8");
        //获取用户名
        dataSource.setUsername("root");
        //获取密码
        dataSource.setPassword("root");

    }
    public static DataSource getDBDS(){
        //返回dds
        return dataSource;
    }
//    public static Connection getConn(){
//        try {
//            return dataSource.getConnection();
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        return null;
//    }

    /**
     * 获取连接池对象
     * @return
     */
    public static DataSource getDataSource(){
        return DBSUtl.dataSource;
    }


    /**
     * 获取连接对象
     * @return
     */
    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }


    /**
     * 关闭结果集
     * @param res
     */
    public static void closeRes(ResultSet res){
        try {
            res.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭预处理对象
     * @param stmt
     */
    public static void closeStmt(PreparedStatement stmt){
        try {
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭连接资源
     * @param connection
     */
    public static void closeCon(Connection connection){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}

package com.example.demo.config;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class SysAdmDao {
    /*
     *在某个类中的方法
     */
    private QueryRunner runner = new QueryRunner(DBSUtl.getDataSource());

    public List<User> findAll(){//一个findAll方法，将返回一个以User为对象的集合list
//1、准备sql语句
        String sql = "select * from User order by User_id asc limit 10";

        //2、执行sql
        List<User> list = null;
        try{
            //将查询到的结果，注意此时返回的是将结果集中的每一行数据都封装到一个对应的JavaBean实例中，
            // 存放到List里。意思就是如果你有多条数据就用BeanListHandler，
            // 如果你只有一条数据就用BeanHandler。后面的User.class就是一个规范，某某类.class
            list = runner.query(sql,new BeanListHandler<>(User.class));

        }catch (SQLException e){
            e.printStackTrace();
        }
        return list;
    }
}

package com.example.demo.config;

public class User {
    private int id;
    private String uname;
    private String email;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUname() {
        return uname;
    }

    public void setUname(String uname) {
        this.uname = uname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}