通过C3P0连接池连接MySQL数据库

C3P0是一个开源的JDBC连接池。

一、准备工作

1、下载

C3P0工具包

• c3p0-0.9.5.5.jar  （C3P0核心）

• mchange-commons-java-0.2.19.jar （C3P0依赖）

下载地址：https://sourceforge.net/projects/c3p0/

MySQL的JDBC驱动包

mysql-connector-java-5.1.49.jar

下载地址：https://dev.mysql.com/downloads/connector/j/

 

DBUtils工具类库

我们还需要使用DBUtils和C3P0一起配合使用。

下载地址：https://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

 

2、导入

将jar包复制到libs文件夹，将jar包添加到库文件中（右击jar包 --> Build Path  --> Add To Build Path）

3、配置 c3p0-config.xml

在工程的 src 目录下新建一个名为 c3p0-config 的XML文件（文件名不能自定义，必须在src目录下）

然后开始配置：

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/db_health</property>
        <property name="user">root</property>
        <property name="password">1234</property>
    </default-config> 
</c3p0-config> 

二、代码实现

1、utils层

通常都是工具类，如数据库连接、字符串处理、日期处理等。

编写C3P0Utils.java工具类。

C3P0Utils类的作用是为我们访问数据库提供链接。

我们在工具类包com.sdbi.utils下创建一个C3P0Utils.java工具类，代码如下：

package com.sdbi.utils;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
    // 通过读取c3p0-config文件获取连接池对象
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

    // 提供一个DataSource数据源
    public static DataSource getDataSource() {
        return dataSource;
    }

    // 创建一个ThreadLocal对象，以当前线程作为key
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

    // 提供当前线程中的Connection
    public static Connection getConnection() throws SQLException {
        Connection conn = threadLocal.get(); // 尝试从本地的线程中找到connection
        if (null == conn) { // 如果拿不到或者拿到的不可用
            conn = dataSource.getConnection(); // 重新创建一个connection
            threadLocal.set(conn); // 存储到ThreadLocal中
        }
        return conn;
    }
}

这里使用ThreadLocal（线程本地变量 / 线程本地存储）来管理数据库链接。

什么是ThreadLocal？

多线程之间的通信可以通过共享变量来实现（通常以 public static 来修饰共享变量），当有多个线程对共享变量进行操作时，为保证其安全性，我们通常需要对其进行同步处理来保证安全性。但是这又会造成程序执行效率的降低。

在某些情况下，若我们是对共享变量的副本进行操作，而非直接操作其本体，那么就可以在既保证效率的情况下又保证其安全性（如数据库连接池获取connection，以及getSession等场景），这个时候我们的主角ThreadLocal就出现了。

ThreadLocal，线程本地变量。ThreadLocal为共享变量在每个线程中都创建了一个副本，每个线程都可以访问自己内部的副本变量。各个线程之间的变量互不干扰。

ThreadLocal底层相当于一个Map，key用来存储当前线程，value用来存储当前线程下共享的数据。常用方法如下：

1、get() 获取ThreadLocal中当前线程共享变量的值。

2、set(T value) 设置ThreadLocal中当前线程共享变量的值。

3、remove() 移除ThreadLocal中当前线程共享变量的值。

2、service层

供外部调用，对dao，model等进行了包装。

（1）UserService接口

负责业务逻辑（功能）的设计，是一个接口。

在com.sdbi.service包下创建UserService.java。代码如下：

package com.sdbi.service;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.sdbi.pojo.Area;
import com.sdbi.pojo.Page;
import com.sdbi.pojo.User;

public interface UserService {
    User findUserByUsernameAndPassword(String username, String password);

    void saveUser(User user);

    List findAllUsers() throws SQLException;

    User findUserById(String id);

    void updateUser(User user);

    void deleteUser(String id);
}

（2）UserServiceImpl实现类

对Service接口的具体实现。

在com.sdbi.service.impl包下创建UserServiceImpl.java，实现UserService接口。代码如下：

package com.sdbi.service.impl;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.sdbi.dao.UserDao;
import com.sdbi.dao.impl.UserDaoImpl;
import com.sdbi.pojo.Area;
import com.sdbi.pojo.Page;
import com.sdbi.pojo.User;
import com.sdbi.service.UserService;
import com.sdbi.utils.ExcelUtil;

public class UserServiceImpl implements UserService {
    private UserDao userDao = new UserDaoImpl();

    @Override
    public User findUserByUsernameAndPassword(String username, String password) {
        User user = userDao.findUserByUsernameAndPassword(username, password);
        return user;
    }

    @Override
    public void saveUser(User user) {
        UserDao userDao = new UserDaoImpl();
        userDao.saveUser(user);
    }

    @Override
    public List findAllUsers() throws SQLException {
        UserDao userDao = new UserDaoImpl();
        return userDao.findAllUsers();
    }

    @Override
    public User findUserById(String id) {
        UserDao userDao = new UserDaoImpl();
        User user = userDao.findUserById(id);
        return user;
    }

    @Override
    public void updateUser(User user) {
        UserDao userDao = new UserDaoImpl();
        userDao.updateUser(user);
    }

    @Override
    public void deleteUser(String id) {
        UserDao userDao = new UserDaoImpl();
        userDao.deleteUser(id);
    }
}

3、dao层

DAO：Data Acess Object，数据访问对象。跟数据库打交道的系统都会有这样的DAO类，主要的作用：

封装对数据库的访问，常规的增删改查（CRUD操作）都通过DAO来实现。

（1）UserDao接口

负责数据库操作（功能）的设计，是一个接口。

在com.sdbi.dao包下创建UserDao.java。代码如下：

package com.sdbi.dao;

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

import com.sdbi.pojo.Area;
import com.sdbi.pojo.Page;
import com.sdbi.pojo.User;

public interface UserDao {
    User findUserByUsernameAndPassword(String username, String password);

    void saveUser(User user);

    List findAllUsers() throws SQLException;

    User findUserById(String id);

    void updateUser(User user);

    void deleteUser(String id);
}

（2）UserDaoImpl实现类

对Dao接口的具体实现，具体的操作数据库的SQL语句的编写和执行。

在com.sdbi.dao.impl包下创建UserDaoImpl.java，实现UserDao接口。代码如下：

package com.sdbi.dao.impl;

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

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.ScalarHandler;

import com.sdbi.dao.UserDao;
import com.sdbi.pojo.Area;
import com.sdbi.pojo.Page;
import com.sdbi.pojo.User;
import com.sdbi.utils.C3P0Utils;

public class UserDaoImpl implements UserDao {
    private QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

    @Override
    public User findUserByUsernameAndPassword(String username, String password) {
        String sql = "select * from tb_user where username = ? and password = ?";
        Object[] params = new Object[] { username, password };
        User user = null;
        try {
            user = (User) queryRunner.query(sql, new BeanHandler(User.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public void saveUser(User user) {
        String sql = "insert into tb_user(username,password,sex,birthday,createtime,content) values(?,?,?,?,?,?)";
        Object[] params = new Object[] { user.getUsername(), user.getPassword(), user.getSex(), user.getBirthday(), "", "" };
        try {
            queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List findAllUsers() {
        String sql = "select * from tb_user";
        List<User> list = null;
        try {
            list = queryRunner.query(sql, new BeanListHandler<User>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public User findUserById(String id) {
        String sql = "select * from tb_user where id = ?";
        User user = null;
        try {
            user = queryRunner.query(sql, new BeanHandler<>(User.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public void updateUser(User user) {
        String sql = "update tb_user set username=?,password=?,sex=?,birthday=?,createtime=?,content=? where id=?";
        Object[] params = { user.getUsername(), user.getPassword(), user.getSex(), user.getBirthday(), "", "", user.getId() };
        try {
            queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteUser(String id) {
        String sql = "delete from tb_user where id =?";
        try {
            queryRunner.update(sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

我们在Dao的实现类中，使用QueryRunner来操作数据库的增删改查操作。QueryRunner是DBUtils的功能之一。 

DBUtils是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用DBUtils能极大简化JDBC编码的工作量，同时也不会影响程序的性能。我在单独的一篇文章中介绍了DBUtils的使用，可以移步过去看看。 DBUtils详解

4、pojo层

POJO：Plain Ordinary Java Object，简单的Java对象，实际就是普通JavaBean。

有时pojo层也称为model层。POJO类中有属性和get、set方法，但是没有业务逻辑。

名词解释

1、Bean：应用在Spring框架上，所有被Spring管理的类对象就可以将其称作为Bean。它不仅仅可以包括对象的属性以及get/set方法，还可以有具体的业务逻辑。

2、Entity：实体，即指数据库表对应到实体类的映射。

3、POJO：普通Java对象，除了属性和get/set方法外不包含具体的业务逻辑方法，和Entity区别在于没有和数据表中字段一一对应。

4、Model：MVC架构中使用，Model的字段要大于Entity的字段，Model主要用作前端页面数据展示，属性、字段、类型都可以有改变，但Entity则必须与数据表字段一一对应。

总结： 实际上JavaBean、POJO、 Entity、Model， 都是Java 对象，只不过用于不同场合罢了。

在com.sdbi.pojo包下面创建User.java类，我们只需要定义好这个类的成员变量（属性），与之对应的get/set方法和toString方法，我们可以使用Eclipse的快速生成代码功能帮助我们完成，不必自己来写。

但是一定要注意：

User类中一定要有无参的构造方法，因为commons-dbutils在创建Bean对象的过程中使用的是newInstance()方法，该方法只能调用无参构造。

如果类中没写无参构造方法，就不能创建对象，就会报错。

代码如下：

package com.sdbi.pojo;

import java.util.Date;

public class User {
    private Long id; // 编号
    private String username;// 用户名
    private String password;// 登录密码
    private String name; // 姓名
    private Byte sex; // 性别
    private String idCard; // 身份证号
    private Integer roleId; // 角色id
    private Date birthday; // 生日
    private Integer deptId; // 系部/部门ID
    private Integer classId; // 教研室/班级ID
    private String homeAddress; // 家庭住址
    private Integer province; // 省
    private Integer city; // 市
    private Integer district; // 区
    private Integer street; // 街道
    private String dorm; // 宿舍

    public Long getXXX() {
        return xxx;
    }

    public void setXXX(Long xxx) {
        this.xxx = xxx;
    }

    @Override
    public String toString() {
        return ......;
    }
}

5、servlet层

我们在com.sdbi.servlet包下定义UserServlet.java 类。

注意，我们在这里并没有让UserServlet继承HttpServlet，重写里面的doGet()或doPost()方法，而是去继承了我们自己定义的一个统一的父类BaseServlet。

原因我在另一篇文章中进行了说明，大家可以移步过去看看。BaseServlet详解 

package com.sdbi.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadBase;
import org.apache.commons.fileupload.ProgressListener;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.sdbi.pojo.Area;
import com.sdbi.pojo.Page;
import com.sdbi.pojo.User;
import com.sdbi.service.UserService;
import com.sdbi.service.impl.UserServiceImpl;
import com.sdbi.utils.DateUtil;

@WebServlet("/UserServlet")
public class UserServlet extends BaseServlet {
    private UserService userService = new UserServiceImpl(); // 创建服务层实现类的对象

    public String userLogin(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException {
        // 获取JSP提交的用户名和密码
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        // 调用业务层功能，根据用户名查找用户select * from tb_user where username = ? and password = ?
        // 并返回用户

        User user = userService.findUserByUsernameAndPassword(username, password); // 调用Service层实现类的方法

        // 根据返回的用户是否为空，判断用户是否已经存在，向客户端响应
        if (null != user) {
            System.out.println("UserServlet.userLogin()...user = " + user.toString());
            request.getSession().setAttribute("user", user);
            request.getRequestDispatcher("/manage.jsp").forward(request, response);
        } else {
            request.setAttribute("error", "登录失败");
            request.getRequestDispatcher("/loginNew.jsp").forward(request, response);
        }
        return null;
    }

    public String userLogout(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException {
        request.getSession().removeAttribute("user");
        response.sendRedirect("loginNew.jsp");
        return null;
    }

    public String toAddPage(HttpServletRequest request, HttpServletResponse response) throws Exception {
        request.getRequestDispatcher("/adduser.jsp").forward(request, response);
        return null;
    }

    public String userAdd(HttpServletRequest request, HttpServletResponse response) throws Exception {
        User user = new User();
        user.setUsername(request.getParameter("username"));
        user.setPassword(request.getParameter("password"));
        user.setSex(new Byte(request.getParameter("sex")));
        user.setBirthday(DateUtil.parseToDate(request.getParameter("birthday"), DateUtil.yyyyMMdd));

        userService.saveUser(user); // 调用Service层实现类的方法

        List userList = userService.findAllUsers();
        request.setAttribute("userList", userList);
        request.getRequestDispatcher("/listuser.jsp").forward(request, response);
        return null;
    }

    public List userList(HttpServletRequest request, HttpServletResponse response) throws Exception {
        try {
            List userList = userService.findAllUsers(); // 调用Service层实现类的方法

            request.setAttribute("userList", userList);
            request.getRequestDispatcher("/listuser.jsp").forward(request, response);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public String toUpdatePage(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String id = request.getParameter("id");

        User user = userService.findUserById(id);// 调用Service层实现类的方法

        request.setAttribute("user", user);
        request.getRequestDispatcher("/updateuser.jsp").forward(request, response);
        return null;
    }

    public String updateUser(HttpServletRequest request, HttpServletResponse response) throws Exception {
        User user = new User();
        user.setId(new Long(request.getParameter("id")));
        user.setUsername(request.getParameter("username"));
        user.setPassword(request.getParameter("password"));
        user.setSex(new Byte(request.getParameter("sex")));
        user.setBirthday(DateUtil.parseToDate(request.getParameter("birthday"), DateUtil.yyyyMMdd));

        userService.updateUser(user);// 调用Service层实现类的方法

        List userList = userService.findAllUsers();
        request.setAttribute("userList", userList);
        request.getRequestDispatcher("/listuser.jsp").forward(request, response);
        return null;
    }

    public String deleteUser(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String id = request.getParameter("id");

        userService.deleteUser(id); // 调用Service层实现类的方法

        List userList = userService.findAllUsers();
        request.setAttribute("userList", userList);
        request.getRequestDispatcher("/listuser.jsp").forward(request, response);
        return null;
    }
}

 

 

三、注意事项：

1、实体类中的成员变量名和数据库表中字段名要一致