druid+dbutils+spring整合web项目
如何使用druid(连接池)和dbutils再加上spring创建一个maven多模块web项目？
<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>
创建出各个模块：entity，util，dao，service，web（按需求增加或减少，不是一定的）
给每个模块的pom.xml文件添加对应需要的依赖。
将需要的实体类编写，添加相应的属性，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; } } }
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>
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包里面。
