创建Druid数据库连接池与使用

1创建数据库连接信息文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db_mtl?characterEncoding=utf-8
username=root
password=123456
#连接池参数如下

#1连接池初始连接数
initialSize=10
#2连接池最大连接数
maxActive=50
#3连接池最小连接数
minIdle=5
#4最大等待时间,单位毫秒
maxWait=5000

2创建数据库连接池工具类

package com.it.untils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;


//数据库连接池工具类
public class DruidUtils {

    //定义数据库连接池数据源
    private static DruidDataSource druidDataSource;

//    初始化数据库连接池
    static {
    try {
        InputStream resourceAsStream = DruidUtils.class.getResourceAsStream("Druid.properties");
//        实例化properties集合，它是一个映射集合
        Properties properties = new Properties();
//        把流中的数据全部加载到properties集合中
        properties.load(resourceAsStream);
//通过properties集合中的数据来创建连接池
        druidDataSource= (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

//返回DataSource对象
public static DataSource getDataSource(){
        return druidDataSource;
}

//    从连接池中获取数据库连接
    public static Connection getConnection() throws SQLException {
        Connection connection=null;
        connection=druidDataSource.getConnection();
        return  connection;
    }
}

3创建管理员实体类

package com.it.entity;

import java.util.Date;

//管理员信息实体类
//一般情况下类中属性个数的类型与对应的数据表是保持一致的
public class Manager {

    private String mgrId;
    private String loginName;
    private String loginPwd;
    private String mgrName;
    private String mgrGender;
    private String mgrTel;
    private String mgrEmail;
    private String mgrQQ;
    private Date createTime;

//    创建一个实体，一般情况下应写出它的无参构造器，全参构造器，get和set方法，toString方法

    public Manager() {
    }

    public Manager(String mgrId, String loginName, String loginPwd, String mgrName, String mgrGender, String mgrTel, String mgrEmail, String mgrQQ, Date createTime) {
        this.mgrId = mgrId;
        this.loginName = loginName;
        this.loginPwd = loginPwd;
        this.mgrName = mgrName;
        this.mgrGender = mgrGender;
        this.mgrTel = mgrTel;
        this.mgrEmail = mgrEmail;
        this.mgrQQ = mgrQQ;
        this.createTime = createTime;
    }

    public String getMgrId() {
        return mgrId;
    }

    public void setMgrId(String mgrId) {
        this.mgrId = mgrId;
    }

    public String getLoginName() {
        return loginName;
    }

    public void setLoginName(String loginName) {
        this.loginName = loginName;
    }

    public String getLoginPwd() {
        return loginPwd;
    }

    public void setLoginPwd(String loginPwd) {
        this.loginPwd = loginPwd;
    }

    public String getMgrName() {
        return mgrName;
    }

    public void setMgrName(String mgrName) {
        this.mgrName = mgrName;
    }

    public String getMgrGender() {
        return mgrGender;
    }

    public void setMgrGender(String mgrGender) {
        this.mgrGender = mgrGender;
    }

    public String getMgrTel() {
        return mgrTel;
    }

    public void setMgrTel(String mgrTel) {
        this.mgrTel = mgrTel;
    }

    public String getMgrEmail() {
        return mgrEmail;
    }

    public void setMgrEmail(String mgrEmail) {
        this.mgrEmail = mgrEmail;
    }

    public String getMgrQQ() {
        return mgrQQ;
    }

    public void setMgrQQ(String mgrQQ) {
        this.mgrQQ = mgrQQ;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        return "Manager{" +
                "mgrId='" + mgrId + '\'' +
                ", loginName='" + loginName + '\'' +
                ", loginPwd='" + loginPwd + '\'' +
                ", mgrName='" + mgrName + '\'' +
                ", mgrGender='" + mgrGender + '\'' +
                ", mgrTel='" + mgrTel + '\'' +
                ", mgrEmail='" + mgrEmail + '\'' +
                ", mgrQQ='" + mgrQQ + '\'' +
                ", createTime=" + createTime +
                '}';
    }
}

4创建管理员DAO类（持久层）

package com.it.dao;

import com.it.entity.Manager;
import com.it.untils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.SQLException;

//用于完成管理员信息的数据库访问
public class ManagerDao {

    /**
     *查询管理员的信息通过selectManagerByLoginName
     * 根据管理员登录名查询管理员信息
     * @param loginName 管理员登录名
     * @return 如果查询成功则返回管理员对象，查询失败则返回为空。
     * @throws SQLException
     */
    public Manager selectManagerByLoginName(String loginName) throws SQLException {
        Manager query=null;
//       1.SQL指令
//如果数据库列名和实体类中的属性名不一致，需要在写SQL语句时，通过与实体类中属性名相同的别名使其与与实体类产生关联
        String sql="SELECT mgr_id mgrId,login_name loginName,login_pwd loginPwd," +
                "mgr_name mgrName,mgr_gender mgrGender,mgr_tel mgrTel," +
                "mgr_email mgrEmail,mgr_qq mgrQQ,create_time createTime" +
                " from tb_managers WHERE login_name=?";
//       2.导入commons-dbutils jar包中的 apache.commons.dbutils.QueryRunner
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
//       3.通过QueryRunner的query方法执行SQL,第一个参数是sql语句；第二个参数查询结果的存储集合；第三个参数查询的参数，代表SQL中的？
       query = queryRunner.query(sql, new BeanHandler<Manager>(Manager.class),loginName);
        return query;
    }
}

5创建管理员DAO类的测试类

package test.dao;

import com.it.dao.MenuDAO;
import com.it.entity.Menu1;
import org.junit.Test;

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

import static org.junit.Assert.*;

public class MenuDAOTest {

    @Test
    public void selectFirstLevelMenusById() throws SQLException {
        MenuDAO menuDAO = new MenuDAO();
        List<Menu1> menu1List = menuDAO.selectFirstLevelMenusById("10000001");
        System.out.println(menu1List);
    }
}