druid+dbutils+spring整合web项目

如何使用druid(连接池)和dbutils再加上spring创建一个maven多模块web项目？

1、准备需要的maven依赖

<dependencyManagement>
        <dependencies>
            <!--spring-context依赖-->
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>${spring.version}</version>
            </dependency>
            <!--mysql驱动依赖-->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.version}</version>
            </dependency>
            <!--druid连接池依赖-->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>${druid.version}</version>
            </dependency>
            <!--dbutils依赖-->
            <dependency>
                <groupId>commons-dbutils</groupId>
                <artifactId>commons-dbutils</artifactId>
                <version>${dbutils.version}</version>
            </dependency>
            <!--测试依赖/可选-->
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>${junit.version}</version>
            </dependency>
            <!--servlet依赖-->
            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>javax.servlet-api</artifactId>
                <version>${servlet.version}</version>
            </dependency>
            <!--json依赖/用的是谷歌的gson-->
            <dependency>
                <groupId>com.google.code.gson</groupId>
                <artifactId>gson</artifactId>
                <version>${gson.version}</version>
            </dependency>
        </dependencies>
    </dependencyManagement>

依赖的版本如下：

<spring.version>5.2.0.RELEASE</spring.version>
<druid.version>1.1.20</druid.version>
<dbutils.version>1.7</dbutils.version>
<junit.version>4.12</junit.version>
<servlet.version>3.1.0</servlet.version>
<mysql.version>5.1.47</mysql.version>
<gson.version>2.8.5</gson.version>

2、创建模块

创建出各个模块：entity，util，dao，service，web（按需求增加或减少，不是一定的）

给每个模块的pom.xml文件添加对应需要的依赖。

 

3、模块操作

3-1 编写entity模块

将需要的实体类编写，添加相应的属性，getter(),setter()，toString()....方法。

3-2 编写dao模块

EmpDao接口：

package com.dao;

import com.entity.Employee;

import java.util.List;

public interface EmpDao {
    List<Employee> queryAll();
}

EmpDaoImpl实现类：

package com.dao;

import com.entity.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
//实现EmpDao接口
public class EmpDaoImpl implements EmpDao{
    private QueryRunner runner;    //通过spring属性注入runner

    public QueryRunner getRunner() {
        return runner;
    }
    
    public void setRunner(QueryRunner runner) {
        this.runner = runner;
    }

    @Override
    public List<Employee> queryAll(){
        //执行查询
        BeanListHandler<Employee> handler = new BeanListHandler<>(Employee.class);
        String sql = "select id,username,salary,gender,hiredate,deptid from employee";
        List<Employee> list = new ArrayList<>();
        try {
            list = runner.query(sql,handler);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询所有时发生异常",e);
        }finally {
            return list;
        }
    }
}

3-3 编写service模块

EmpService接口：

package com.service;

import com.entity.Employee;

import java.util.List;

public interface EmpService {
    List<Employee> queryAll();
}

EmpServiceImpl实现类：

package com.service;

import com.dao.EmpDao;
import com.entity.Employee;

import java.util.List;

public class EmpServiceImpl implements EmpService{

    private EmpDao empDao;    //spring属性注入empDao，使用接口类型而不是实现类，解耦，依赖倒转原则

    public void setEmpDao(EmpDao empDao) {
        this.empDao = empDao;
    }

    @Override
    public List<Employee> queryAll() {
        return empDao.queryAll();
    }
}

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    <!--使用连接池的DruidDataSource类生成dataSource的bean，并且通过属性注入连接需要的值-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/empdb"></property>
    </bean>
    <!--使用dbutils的QueryRunner类生成QueryRunner的bean，通过构造函数注入连接池-->
    <bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
        <constructor-arg name="ds" ref="dataSource"></constructor-arg>
        <constructor-arg name="pmdKnownBroken" value="true"></constructor-arg>
    </bean>
    <!--生成empdao的bean，并且属性注入runner-->
    <bean id="empdao" class="com.dao.EmpDaoImpl">
        <property name="runner" ref="queryRunner"></property>
    </bean>
    <!--生成empservice的bean，并且属性注入empdao-->
    <bean id="empservice" class="com.service.EmpServiceImpl">
        <property name="empDao" ref="empdao"></property>
    </bean>
</beans>

3-4 编写web模块

SelectServlet类：

package com.web;

import com.entity.Employee;
import com.service.EmpServiceImpl;

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;
import java.util.List;
import com.google.gson.*;
import org.springframework.context.ApplicationContext;

@WebServlet("/select")
public class SelectEmpServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        resp.setCharacterEncoding("UTF-8");
        //从上下文作用域中取出容器类对象
        ApplicationContext context = (ApplicationContext)                        req.getServletContext().getAttribute("context");
        EmpServiceImpl empservice = context.getBean("empservice", EmpServiceImpl.class);
        List<Employee> employees = empservice.queryAll();
        //将结果转换成json格式
        Gson gson = new Gson();
        resp.getWriter().write(gson.toJson(employees));
    }
}

LoginFilter过滤器类：

package com.web;

import com.service.EmpServiceImpl;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import java.io.IOException;
@WebFilter(filterName = "/*")
//这个过滤器用来过滤所有servlet
public class LoginFilter implements Filter {
    //初始化方法只会在Tomcat服务器启动时调用一次，所以在这里面创建spring容器类，不会造成容器创建很多次
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {
        //创建容器类
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
        //将容器类对象放在上下文作用域中
        filterConfig.getServletContext().setAttribute("context",context);
    }

    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        //转交给下一个servlet
        filterChain.doFilter(servletRequest,servletResponse);
    }
    //销毁方法
    @Override
    public void destroy() {
        
    }
}

index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>druid加dbutils加spring查询所有</title>
    <script src="jq/jquery-3.4.1.js"></script>
</head>
<body>
    <div>
        <table>
            <tr>
                <th>编号</th>
                <th>姓名</th>
                <th>工资</th>
            </tr>
            <tbody id="tb1">

            </tbody>
        </table>
    </div>
</body>
<script>
    window.onload = function (ev) {
        loadEmp();
    }
    function loadEmp() {
        $.ajax({
            method:"get",
            url:"/taskTwo/select",
            dataType:"json"
        }).done(function (employees) {
            var tr = "";
            $.each(employees,function (index,emp) {
                tr +="<tr>";
                tr +="<td>"+emp.id+"</td>";
                tr +="<td>"+emp.username+"</td>";
                tr +="<td>"+emp.salary+"</td>";
                tr +="</tr>";
            })
            $("#tb1").html(tr);
        }).fail(function () {
            alert("请求失败")
        })
    }
</script>
</html>

4、知识点总结

4-1 一个项目一般只能有一个spring容器对象，因为spring的单例bean在容器对象创建的时候创建，太多容器对象会导致里面的单例bean也会一起创建，造成资源浪费。

4-2 直接在applicationContext.xml中定义DataSource的bean，等价于new DataSource()，setUrl，setUsername，setPassword。

4-3 在applicationContext.xml中定义QueryRunner的bean，相当于new QueryRunner(Datasource ,true)。

4-3 dao或service接口的实现类，一般放在dao下面专门的impl包里面。