javaweb开发（十三）：数据库连接池和Apache DBUtils操作数据库

• 构建web项目
  

• 配置tomcat
  

• 导入依赖
  

commons-dbutils-1.7.jar是数据库工具包
commons-dbcp2-2.7.0.jar是数据库连接池包

• src目录下编写database.properties

driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false
username = root
password = 123456
initialSize=10              # 连接池建立时创建的连接的数量
maxActive=30            # 连接池同一时间内最多能够分配的活动连接的数量

• 编写DataSourceUtil

public class DataSourceUtil {
 
    private static DataSource dataSource;
 
    static {
        try{
            InputStream in = DataSourceUtil.class.getClassLoader().getResourceAsStream("database.properties");
            Properties p = new Properties();
            p.load(in);
            dataSource = BasicDataSourceFactory.createDataSource(p);
        }catch (Exception e){
            e.printStackTrace();
            throw new ExceptionInInitializerError("初始化DBPC失败");
        }
    }
 
    /**
     * 获取连接池
     * @return
     */
    public static DataSource getDataSource(){
        return dataSource;
    }
 
}

• Apache dbutils⼯具包简介

# QueryRunner
查询执⾏器，提供对sql语句操作的API
update(String sql,Object...params) 可执⾏ 增-INSERT、删-DELETE、改-UPDATE
query(String sql,ResultSetHandler rsh,Object...params) 可执⾏ 查询-SELECT
 
# ResultSetHandler
结果集处理类，执⾏处理⼀个结果集对象，将数据转变并处理为任何⼀种形式
BeanHandler 结果集中的第⼀⾏数据封装到⼀个对应的JavaBean实例
BeanListHandler 结果集中的每⼀⾏数据都封装到⼀个对应的JavaBean实例中，
存放到List⾥
MapHandler 结果集中的第⼀⾏数据封装到⼀个Map⾥，key是列名，value就是
对应的值
MapListHandler 结果集中的每⼀⾏数据都封装到⼀个Map⾥，然后再存放到List
ScalarHandler 结果集中第⼀⾏数据指定列的值,常⽤来进⾏单值查询

• 删除database.properties中的中文注释

• 导入如下依赖
  

• 编写实体类

public class User {
 
    private int id;
 
    private String phone;
 
    private String pwd;
 
    private int sex;
 
    private String img;
 
    private Date createTime;
 
    private int role;
 
    private String username;
 
    private String wechat;
 
 
    public int getId() {
        return id;
    }
 
    public void setId(int id) {
        this.id = id;
    }
 
    public String getPhone() {
        return phone;
    }
 
    public void setPhone(String phone) {
        this.phone = phone;
    }
 
    public String getPwd() {
        return pwd;
    }
 
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
 
    public int getSex() {
        return sex;
    }
 
    public void setSex(int sex) {
        this.sex = sex;
    }
 
    public String getImg() {
        return img;
    }
 
    public void setImg(String img) {
        this.img = img;
    }
 
    public Date getCreateTime() {
        return createTime;
    }
 
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
 
    public int getRole() {
        return role;
    }
 
    public void setRole(int role) {
        this.role = role;
    }
 
    public String getUsername() {
        return username;
    }
 
    public void setUsername(String username) {
        this.username = username;
    }
 
    public String getWechat() {
        return wechat;
    }
 
    public void setWechat(String wechat) {
        this.wechat = wechat;
    }
 
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", phone='" + phone + '\'' +
                ", pwd='" + pwd + '\'' +
                ", sex=" + sex +
                ", img='" + img + '\'' +
                ", createTime=" + createTime +
                ", role=" + role +
                ", username='" + username + '\'' +
                ", wechat='" + wechat + '\'' +
                '}';
    }
 
}

• 编写UserDao

public class UserDao {
 
    private QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());
    //开启驼峰映射
    private BeanProcessor bean = new GenerousBeanProcessor();
    private RowProcessor processor = new BasicRowProcessor(bean);
 
    /**
     * 根据id找用户
     * @param id
     * @return
     */
    public User findById(int id){
        String sql = "select * from user where id=?";
        User user = null;
        try {
            user  =  queryRunner.query(sql,new BeanHandler<>(User.class,processor),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }
 
    /**
     * 查询所有
     * @return
     */
    public List<User> list(){
        String sql = "select * from user";
        List<User> list = null;
        try {
            list  =  queryRunner.query(sql,new BeanListHandler<>(User.class,processor));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
 
    /**
     * 保存
     * @param user
     * @return
     * @throws Exception
     */
    public int save(User user) throws Exception {
        String sql = "insert into user (phone,pwd,sex,img,create_time,role,username,wechat) values(?,?,?,?,?,?,?,?)";
        Object [] params = {
                user.getPhone(),
                user.getPwd(),
                user.getSex(),
                user.getImg(),
                user.getCreateTime(),
                user.getRole(),
                user.getUsername(),
                user.getWechat()
        };
        int i = 0;
        try{
            i = queryRunner.update(sql,params);
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception();
        }
        return i;
    }
 
    /**
     * 根据id查询
     * @param id
     * @return
     */
    public Map<String,Object> findByIdWithMap(int id){
        String sql = "select * from user where id=?";
        Map<String,Object> map = null;
        try {
            map  =  queryRunner.query(sql,new MapHandler(),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }
 
    /**
     * 查找全部用户 MapListHandler
     * @return
     */
    public List<Map<String,Object>> listWithMap(){
        String sql = "select * from user";
        List<Map<String,Object>> list = null;
        try {
            list  =  queryRunner.query(sql,new MapListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
 
    /**
     * 统计个数
     * @return
     */
    public int countUser(){
        String sql = "select count(*) from user";
        Long count = null;
        try{
            count = (Long)queryRunner.query(sql,new ScalarHandler<>());
        }catch (Exception e){
            e.printStackTrace();
        }
        return count.intValue();
    }
 
}

• 编写业务层

public interface UserService {
 
    User findById(int id);
 
    List<User> list();
 
    Map<String,Object> findByIdWithMap(int id);
 
    List<Map<String,Object>> listWithMap();
 
    int countUser();
 
    int save(User user);
 
}
 
public class UserServiceImpl implements UserService {
 
    private UserDao userDao = new UserDao();
 
    @Override
    public User findById(int id) {
        return userDao.findById(id);
    }
 
    @Override
    public List<User> list() {
        return userDao.list();
    }
 
    @Override
    public Map<String, Object> findByIdWithMap(int id) {
        return userDao.findByIdWithMap(id);
    }
 
    @Override
    public List<Map<String, Object>> listWithMap() {
        return userDao.listWithMap();
    }
 
    @Override
    public int countUser() {
        return userDao.countUser();
    }
 
    @Override
    public int save(User user) {
        try {
            int rows = userDao.save(user);
            return rows;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
 
}

• 编写控制层

@WebServlet(name = "userServlet",urlPatterns = {"/user"})
public class UserServlet extends HttpServlet {
 
    private UserService userService = new UserServiceImpl();
 
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String method = req.getParameter("method");
        // 根据id查询
        if(method.equals("findById")){
            String idStr = req.getParameter("id");
            int userId = Integer.parseInt(idStr);
            User user = userService.findById(userId);
            System.out.println(user.toString());
        }
        // 查询所有
        if(method.equals("list")){
            List<User> list = userService.list();
            System.out.println(list.toString());
        }
        // 根据id查询
        if(method.equals("findByIdWithMap")){
            String idStr = req.getParameter("id");
            int userId = Integer.parseInt(idStr);
            Map<String,Object> map  = userService.findByIdWithMap(userId);
            System.out.println(map.toString());
        }
        // 查询所有
        if(method.equals("listWithMap")){
            List<Map<String,Object>> list = userService.listWithMap();
            System.out.println(list.toString());
        }
        // 统计个数
        if(method.equals("count")){
            int total = userService.countUser();
            System.out.println("user表行数 = "+total);
        }
        // 保存
        if(method.equals("save")){
            User user = new User();
            user.setPhone("sdfsdfwe");
            user.setPwd("pwd");
            user.setSex(1);
            user.setImg("xdclass.net");
            user.setCreateTime(new Date());
            user.setRole(1);
            user.setUsername("二当家小D");
            user.setWechat("xdclass6");
            int row = userService.save(user);
            System.out.println("row="+row);
            if(row == 1){
                System.out.println("插入成功");
            }else {
                System.out.println("插入失败");
            }
        }
    }
 
}

• 测试

# 浏览器
http://localhost:8080/user?method=findById&id=3
# 控制台
User{id=3, phone='2323432', pwd='xdclass-lw', sex=1, img='wwwww', createTime=2020-05-20 12:54:42.0, role=1, username='?当家?
D', wechat='xdclass1'}
 
# 浏览器
http://localhost:8080/user?method=list
# 控制台
[User{id=1, phone='123', pwd='666', sex=1, img='xdclass.net', createTime=2021-09-09 08:00:00.0, role=1, username='jack', wechat='xdclass6'}, User{id=3, phone='2323432', pwd='xdclass-lw', sex=1, img='wwwww', createTime=2020-05-20 12:54:42.0, role=1, username='?当家?
D', wechat='xdclass1'}, User{id=4, phone='2323432', pwd='3232323', sex=1, img='wwwww', createTime=2020-05-20 12:55:07.0, role=1, username='?
王', wechat='xdclass-lw'}, User{id=5, phone='null', pwd='123456', sex=1, img='null', createTime=2022-04-15 14:43:30.0, role=2, username='二当家小D', wechat='null'}]
 
# 浏览器
http://localhost:8080/user?method=findByIdWithMap&id=1
# 控制台
{id=1, phone=123, pwd=666, sex=1, img=xdclass.net, create_time=2021-09-09 08:00:00.0, role=1, username=jack, wechat=xdclass6}
 
# 浏览器
http://localhost:8080/user?method=listWithMap
# 控制台
[{id=1, phone=123, pwd=666, sex=1, img=xdclass.net, create_time=2021-09-09 08:00:00.0, role=1, username=jack, wechat=xdclass6}, {id=3, phone=2323432, pwd=xdclass-lw, sex=1, img=wwwww, create_time=2020-05-20 12:54:42.0, role=1, username=?当家?
D, wechat=xdclass1}, {id=4, phone=2323432, pwd=3232323, sex=1, img=wwwww, create_time=2020-05-20 12:55:07.0, role=1, username=?
王, wechat=xdclass-lw}, {id=5, phone=null, pwd=123456, sex=1, img=null, create_time=2022-04-15 14:43:30.0, role=2, username=二当家小D, wechat=null}]
 
# 浏览器
http://localhost:8080/user?method=count
# 控制台
user表行数 = 4
 
# 浏览器
http://localhost:8080/user?method=count
# 控制台
user表行数 = 4
 
# 浏览器
http://localhost:8080/user?method=save
# 控制台
row=1
插入成功

• 其他操作

    public int save(User user) throws Exception {
        // 插入操作
        // String sql = "insert into user (phone,pwd,sex,img,create_time,role,username,wechat) values(?,?,?,?,?,?,?,?)";
        // Object [] params = {
        //         user.getPhone(),
        //         user.getPwd(),
        //         user.getSex(),
        //         user.getImg(),
        //         user.getCreateTime(),
        //         user.getRole(),
        //         user.getUsername(),
        //         user.getWechat()
        // };
 
        // 删除操作
        // String sql = "delete from user where id= ?";
        // Object [] params = { 1};
 
        // 更新操作
        String sql = "update user set phone= ?, pwd= ? where id= ?;";
        Object [] params = {
                user.getPhone(),
                user.getPwd(),
                user.getId()
        };
 
        // 影响行数
        int i = 0;
        try{
           i = queryRunner.update(sql,params);
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception();
        }
        return i;
    }