java--使用dbuntils下的通用增删查改操作

一、先dbuntils的jar包，导入到项目工程：DbUtils – Download Apache Commons DbUtils

二、操作使用

连接数据库的工具类

package com.ruqi.basedao;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class ConnectUtil {

    public static Connection getConnection() throws Exception {
        // druid 连接方式
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.propries");
        Properties pros = new Properties();
        pros.load(is);
        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();
        return conn;
    }

        public static void closeConnection1(Connection conn, PreparedStatement ps, ResultSet rs) {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }

}

1、增删改

package com.ruqi.druidtest;
import com.ruqi.basedao.ConnectUtil;
import com.ruqi.basedao.Scores;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DButilsTest {

    @Test
    public void update() throws Exception {
        QueryRunner runner = new QueryRunner();
        Connection conn = ConnectUtil.getConnection();
        String sql = "update Scores set score=100 where id = ?";
        int updateCounts = runner.update(conn,sql,5);
        System.out.println(updateCounts);
        ConnectUtil.closeConnection(conn,null,null);
    }

    
}

2、查询操作

package com.ruqi.druidtest;
import com.ruqi.basedao.ConnectUtil;
import com.ruqi.basedao.Scores;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DButilsTest {

    @Test
    public void selectOneOrMany() throws Exception {
        QueryRunner runner = new QueryRunner();
        Connection conn = ConnectUtil.getConnection();
        String sql = "select score,date from Scores where id = ?";
        //  BeanHandler返回一条数据的对象
        BeanHandler<Scores> handler = new BeanHandler<>(Scores.class);
        Scores restult = runner.query(conn, sql, handler, 5);
        System.out.println(restult);

        //  BeanHandler返回多条数据的对象
        sql = "select score,date from Scores where id < ?";
        BeanListHandler<Scores> handler1 = new BeanListHandler<>(Scores.class);
        List<Scores> list = runner.query(conn, sql, handler1, 5);
        list.forEach(System.out::println);
        ConnectUtil.closeConnection(conn,null,null);
    }

    @Test
    public void selectMap() throws Exception {
        QueryRunner runner = new QueryRunner();
        Connection conn = ConnectUtil.getConnection();
        String sql = "select score,date from Scores where id = ?";
        //  MapHandler返回一条数据，以map形式返回
        MapHandler handler = new MapHandler();
        Map<String, Object> restult = runner.query(conn, sql, handler, 5);
        System.out.println(restult);
        //  MapListHandler返回多条数据，以list包装map形式返回
        sql = "select score,date from Scores where id < ?";
        MapListHandler handler1 = new MapListHandler();
        List<Map<String, Object>> list = runner.query(conn, sql, handler1, 5);
        System.out.println(list);
        ConnectUtil.closeConnection(conn,null,null);

    }

    @Test
    public void specialData() throws Exception {
        QueryRunner runner = new QueryRunner();
        Connection conn = ConnectUtil.getConnection();
        String sql = "select count(*) from Scores;";
        //  ScalarHandler查询特殊值
        ScalarHandler handler = new ScalarHandler();
        Long counts = (Long) runner.query(conn, sql, handler);
        System.out.println(counts);
        sql = "select MAX(score) from Scores;";
        int maxscore = (int) runner.query(conn, sql, handler);
        System.out.println(maxscore);
        ConnectUtil.closeConnection(conn,null,null);

    }
}

3、自定义handler

package com.ruqi.druidtest;
import com.ruqi.basedao.ConnectUtil;
import com.ruqi.basedao.Scores;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DButilsTest {

    @Test
    public void defineHandler() throws Exception {
        QueryRunner runner = new QueryRunner();
        Connection conn = ConnectUtil.getConnection();
        String sql = "select id from Scores where id = ?;";
        //  自定义handler
        ResultSetHandler<Scores> handler = new ResultSetHandler<Scores>(){
            //重写接口方法
            @Override
            public Scores handle(ResultSet resultSet) throws SQLException {
                if (resultSet.next()){
                    int id = (int) resultSet.getObject(1);
                    return new Scores(id,-1,null);
                }
                return null;
            }
        };
        Scores result = runner.query(conn, sql, handler,3);
        System.out.println(result);
        ConnectUtil.closeConnection(conn,null,null);
    }
}