使用DBUtiles简化JDBC操作

jar包地址
代码地址
1：增加，修改，删除操作

package cn.itheima.jdbc.test;

import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import cn.itheima.jdbc.utils.C3P0Utils;

/**
 * 测试DBUtils的增删改方法
 * @author Administrator
 *
 */
public class TestDBUtils {

    /**
     * 添加用户方法
     */
    @Test
    public void testAddUser(){
        try {
            //1.创建核心类QueryRunner
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            //2.编写sql语句
            String sql = "insert into tbl_user values(null,?,?)";
            //3.为占位符设置值
            Object[] params = {"耿耿","余淮"};
            //4.执行添加操作
            int rows = qr.update(sql, params);
            if(rows>0){
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 根据id修改用户方法
     */
    @Test
    public void updateById(){
        try {
            //1.创建核心类QueryRunner
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            //2.编写sql语句
            String sql = "update tbl_user set upassword=? where uid=?";
            //3.为占位符设置值
            Object[] params = {"xxx",14};
            //4.执行添加操作
            int rows = qr.update(sql, params);
            if(rows>0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 根据id删除用户方法
     */
    @Test
    public void upDeleteById(){
        try {
            //1.创建核心类QueryRunner
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            //2.编写sql语句
            String sql = "delete from tbl_user where uid=?";
            //3.为占位符设置值
            Object[] params = {14};
            //4.执行添加操作
            int rows = qr.update(sql, params);
            if(rows>0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

2：查询方法

package cn.itheima.jdbc.test;

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

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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import cn.itheima.domain.User;
import cn.itheima.jdbc.utils.C3P0Utils;

/**
 * 测试dbutils查询操作
 * @author Administrator
 *
 */
public class TestDBUtils2 {

    /**
     * 查询所有
     */
    @Test
    public void testQueryAll(){
        try {
            //1.获取核心类queryRunner
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            //2.编写sql语句
            String sql = "select * from tbl_user";
            //3.执行查询操作
            List<User> users = qr.query(sql, new BeanListHandler<User>(User.class));
            //4.对结果集集合进行遍历
            for (User user : users) {
                System.out.println(user.getUname()+":"+user.getUpassword());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * 根据id查询
     */
    @Test
    public void testQueryUserById(){
        try {
            //1.获取核心类queryRunner
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            //2.编写sql语句
            String sql = "select * from tbl_user where uid=?";
            //3.为占位符设置值
            Object[] params = {15};
            //3.执行查询操作
            User user = qr.query(sql, new BeanHandler<User>(User.class), params);
            //4.对结果集集合进行遍历
            System.out.println(user.getUname()+":"+user.getUpassword());

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 查询所有用户的总个数
     */
    @Test
    public void getCount(){
        try {
            //1.获取核心类queryRunner
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            //2.编写sql语句
            String sql = "select count(*) from tbl_user";
            //3.执行查询操作
            Long count = (Long) qr.query(sql, new ScalarHandler());
            //4.对结果集集合进行遍历
            System.out.println(count);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    @Test
    public void testQueryAll1(){
        try {
            //1.获取核心类queryRunner
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            //2.编写sql语句
            String sql = "select * from tbl_user";
            //3.执行查询操作
            List<Map<String, Object>> users = qr.query(sql, new MapListHandler());
            //4.对结果集集合进行遍历
            for (Map<String, Object> map : users) {
                System.out.println(map);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }


    @Test
    public void testQueryAll2(){
        try {
            //1.获取核心类queryRunner
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            //2.编写sql语句
            String sql = "select * from tbl_user";
            //3.执行查询操作
            List<Object> query = qr.query(sql, new ColumnListHandler("uname"));
            //4.对结果集集合进行遍历
            for (Object object : query) {
                System.out.println(object);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}