IDEA学习记录22--数据库连接池和Apache DBUtils实战

1、Apache dbcp数据库连接池封装 DataSourceUtils

1）、配置文件database.properties
在src目录下

注意mysql8与mysql5配置文件的不同写法！！

driverClassName = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://127.0.0.1:3306/xd_class?userUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
username = root
password = 123456

initialSize = 10            //连接池建立时创建连接的数量
maxActive = 30              //连接池同一时间内最多能够分配的活动连接的数量

2）、数据库连接池工具类DataSourceUtil
用构造函数或者静态代码块进行初始化

package net.xdclass.web.util;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.util.Properties;

//数据库连接池工具类
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("初始化DBCP失败");
        }
    }

    //获取连接池
    public static DataSource getDataSource(){
        return dataSource;
    }

}

2、Apachedbutils工具

1）、DbUtils中的核心类/接口

3、BeanHandler和BeanListHandler结果集处理器

BeanHandler ： 结果集中的第⼀行数据封装到一个对应的JavaBean实例
BeanListHandler 结果集中的每⼀行数据都封装到一个对应的JavaBean实例中，存放到List⾥

controller–>service–>dao

1）、User

package net.xdclass.web.domain;

import java.util.Date;

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;
    
…… …… ……以上对应的get、set
重写ToString方法，方便调试：
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", phone='" + phone + '\'' +
                ", pwd='" + pwd + '\'' +
                ", sex=" + sex +
                ", img='" + img + '\'' +
                ", createTime=" + createTime +
                ", role=" + role +
                ", username='" + username + '\'' +
                ", wechat='" + wechat + '\'' +
                '}';
    }
}

2）、UserDao

package net.xdclass.web.dao;

import net.xdclass.web.domain.User;
import net.xdclass.web.util.DataSourceUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

public class UserDao {
    //根据id找用户
    private QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

    public User findById(int id){
        String sql = "select * from user where id=?";
        User user = null;
        try{
            user = queryRunner.query(sql,new BeanHandler<>(User.class),id);//查询
        }catch (Exception e){
            e.printStackTrace();
        }
        return user;
    }
}

根据id找用户

3）、UserService

package net.xdclass.web.service;

import net.xdclass.web.domain.User;

public interface UserService {

    User finById(int id);
}

4）、UserServiceImpl

package net.xdclass.web.service.impl;

import net.xdclass.web.dao.UserDao;
import net.xdclass.web.domain.User;
import net.xdclass.web.service.UserService;

public class UserServiceImpl implements UserService {
    //利用service调用dao
    private UserDao userDao = new UserDao();

    //实现接口中的方法
    @Override
    public User finById(int id) {
        return userDao.findById(id);
    }

}

5）、UserServlet

package net.xdclass.web.controller;

import net.xdclass.web.domain.User;
import net.xdclass.web.service.UserService;
import net.xdclass.web.service.impl.UserServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

//@WebServlet("/userServlet/find")
@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");

        if(method.equals("findById")){
            String idStr = req.getParameter("id");
            int userId = Integer.parseInt(idStr);
            User user = userService.finById(userId);

            System.out.println(user.toString());
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    }
}

4、MapHandler-ScalarHandler结果集处理器

MapHandler ： 结果集中的第一行数据封装到一个Map里， key是列名， value就是对应的值

MapListHandler ： 结果集中的每⼀行数据都封装到一个Map里，然后再存放到List

ScalarHandler ： 结果集中第一行数据指定列的值,常用来进行单值查询