DbUtils

2017-09-10 本文已影响0人常威爆打来福

一简介
Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。
下载组件，引入jar文件：commons-dbutils-1.6.jar
maven配置：

<dependency>
      <groupId>commons-dbutils</groupId>
      <artifactId>commons-dbutils</artifactId>
      <version>1.6</version>
    </dependency>

二核心类QueryRunner

带有Connection的
- Int update(Connection conn, String sql, Object param);执行更新带一个占位符的sql
- Int update(Connection conn, String sql, Object… param);执行更新带多个占位符的sql
- Int[] batch(Connection conn, String sql, Object[][] params) 批处理
- T query(Connection conn ,String sql, ResultSetHandler<T> rsh, Object... params) 查询方法
不带有Connection的
- Int update( String sql, Object param); 执行更新带一个占位符的sql
- Int update( String sql, Object… param);执行更新带多个占位符的sql
- Int[] batch( String sql, Object[][] params); 批处理

注意：如果调用DbUtils组件的操作数据库方法，没有传入连接对象，那么在实例化QueryRunner对象的时候需要传入数据源对象：

QueryRunner qr = new QueryRunner(DataSource ds);

三更新操作（包括delete 、insert、 update）
1 删除

package Dao;

import org.apache.commons.dbutils.QueryRunner;
import utils.jdbcUtil;

import java.sql.*;

/**
 * Created by pc on 2017/9/9.
 */
public class DbUtils {
    public static void main(String [] args){
            try {
                String sql = " delete from student where id =? ";
                Connection conn = jdbcUtil.getConnection();
                QueryRunner queryRunner= new QueryRunner();
                queryRunner.update(conn,sql,7);
                jdbcUtil.close(conn,null,null);
            }catch (Exception e){
                throw new RuntimeException(e);
            }

        }
    }

7已删除

2 插入
方法一

package Dao;

import org.apache.commons.dbutils.QueryRunner;
import utils.jdbcUtil;

import java.sql.*;

public class DbUtils {
    public static void main(String [] args){
            try {
                String sql = "INSERT INTO student (NAME,PASSWORD) VALUE (?,?)";
                Connection conn = jdbcUtil.getConnection();
                QueryRunner queryRunner= new QueryRunner();
                queryRunner.update(conn,sql,"丹丹",123456);
                jdbcUtil.close(conn,null,null);
            }catch (Exception e){
                throw new RuntimeException(e);
            }
        }
    }

结果显示

方法二

package Dao;

import org.apache.commons.dbutils.QueryRunner;
import utils.jdbcUtil;

import java.sql.*;

/**
 * Created by pc on 2017/9/9.
 */
public class DbUtils {
    public static void main(String [] args){
            try {
                String sql = "INSERT INTO student (NAME,PASSWORD) VALUE (?,?)";
                Connection conn = jdbcUtil.getConnection();
                QueryRunner queryRunner= new QueryRunner();
                queryRunner.batch(conn,sql,new Object[][]{{"李彦斌",666666},{"胡歌",888888}});
                jdbcUtil.close(conn,null,null);
            }catch (Exception e){
                throw new RuntimeException(e);
            }
        }
    }

结果显示

四查询操作
DbUtils提供的封装结果的一些对象：

BeanHandler: 查询返回单个对象
BeanListHandler: 查询返回list集合，集合元素是指定的对象
ArrayHandler, 查询返回结果记录的第一行，封装对对象数组, 即返回：Object[]
ArrayListHandler, 把查询的每一行都封装为对象数组，再添加到list集合中
ScalarHandler 查询返回结果记录的第一行的第一列 (在聚合函数统计的时候用)
MapHandler 查询返回结果的第一条记录封装为map
MapListHandler 查询返回结果封装为List<map>
1 自定义结果集封装数据

package Dao;

import entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import utils.jdbcUtil;

import java.sql.*;

/**
 * Created by pc on 2017/9/9.
 */
public class DbUtils {
    public static void main(String [] args) throws Exception{
                final String sql = "select * from student where id = ?";
                Connection conn =jdbcUtil.getConnection();
                QueryRunner qr = new QueryRunner();
                //查询
                final Student student = qr.query(conn, sql, new ResultSetHandler<Student>() {
                    //自定义 封装一个Bean对象
                    public Student handle(ResultSet resultSet) throws SQLException {
                        if (resultSet.next()){
                        Student student1 = new Student();
                        student1.setId(resultSet.getInt("id"));
                        student1.setName(resultSet.getString("name"));
                        student1.setPassword(resultSet.getInt("password"));
                        return student1;
                        }
                        return  null;
                    }
                },1);
                System.out.println(student.getId()+student.getName()+student.getPassword());
            }
        }

结果显示

2 查询使用组件提供的BeanHandler结果集对象封装数据

package Dao;

import entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import utils.jdbcUtil;

import java.sql.*;

/**
 * Created by pc on 2017/9/9.
 */
public class DbUtils {
    public static void main(String [] args) throws Exception{
                final String sql = "select * from student where id = ?";
                Connection conn =jdbcUtil.getConnection();
                QueryRunner qr = new QueryRunner();
                Student student = qr.query(conn,sql,new BeanHandler<Student>(Student.class),2);
                System.out.println(student.getId()+student.getName()+student.getPassword());
                conn.close();
            }
        }

结果显示

3 查询使用组件提供的BeanListHandler结果集对象封装数据

package Dao;

import entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.jdbcUtil;

import java.sql.*;
import java.util.List;

public class DbUtils {
    public static void main(String [] args) throws Exception{
                final String sql = "select * from student where id = ?";
                Connection conn =jdbcUtil.getConnection();
                QueryRunner qr = new QueryRunner();
                List<Student> student = qr.query(conn,sql,new BeanListHandler<Student>(Student.class),3);
                System.out.println(student.get(0).getId()+student.get(0).getName()+student.get(0).getPassword());
                conn.close();
            }
        }

运行结果

4 查询使用组件提供的MapHandler 结果集对象封装数据

package Dao;

import entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import utils.jdbcUtil;

import java.sql.*;
import java.util.List;
import java.util.Map;

public class DbUtils {
    public static void main(String [] args) throws Exception{
                final String sql = "select * from student";
                Connection conn =jdbcUtil.getConnection();
                QueryRunner qr = new QueryRunner();
                Map<String,Object> stuent = qr.query(conn,sql,new MapHandler());
                System.out.println(stuent);
                conn.close();
            }
        }

运行结果

5 查询使用组件提供的ScalarHandler 结果集对象封装数据(在聚合函数统计时候用)

package Dao;

import entity.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.jdbcUtil;

import java.sql.*;
import java.util.List;
import java.util.Map;

public class DbUtils {
    public static void main(String [] args) throws Exception{
                final String sql = "select count(*) from student";
                Connection conn =jdbcUtil.getConnection();
                QueryRunner qr = new QueryRunner();
                Long stuent = qr.query(conn,sql,new ScalarHandler<Long>());
                System.out.println(stuent);
                conn.close();
            }
        }

运行结果

DbUtils

猜你喜欢

热点阅读