Spring管理连接池
前言:
Spring是单实例,而JavaWeb的数据库连接池作为单实例是最好的选择,一个项目就一个连接池,连接直接从池中拿,可以让Spring帮我们创建连接池对象,(管理连接池)
1.引入Jar包
连接池:Spring一般使用dbcp2连接池
commons-dbcp2-2.1.1.jar
commons-pool2-2.4.2.jar
文件上传包
commons-fileupload-1.3.2.jar
commons-io-2.5.jar
jackson
jackson-annotations-2.7.0.jar
jackson-core-2.7.3.jar
jackson-databind-2.7.3.jar
jsp的包:
jstl-1.2.jar
数据库连接与jdbc:
mysql-connector-java-5.1.8.jar
配置JdbcTemplate:
spring-jdbc-4.3.6.RELEASE.jar
事务:
spring-tx-4.3.6.RELEASE.jar
日志包:
commons-logging-1.2.jar
Spring基础包:
spring-aop-4.3.6.RELEASE.jar
spring-beans-4.3.6.RELEASE.jar
spring-context-4.3.6.RELEASE.jar
spring-core-4.3.6.RELEASE.jar
spring-expression-4.3.6.RELEASE.jar
SpringWeb包:
spring-web-4.3.6.RELEASE.jar
spring-webmvc-4.3.6.RELEASE.jar
1.1SQL文件
/*
Navicat MySQL Data Transfer
Source Server : MySQL
Source Server Version : 50560
Source Host : localhost:3306
Source Database : springj
Target Server Type : MYSQL
Target Server Version : 50560
File Encoding : 65001
Date: 2020-12-01 11:26:02
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for t_account
-- ----------------------------
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_account` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL COMMENT '名字',
`password` varchar(6) DEFAULT NULL COMMENT '密码',
`cardno` varchar(16) NOT NULL COMMENT '卡号',
`amount` double DEFAULT NULL COMMENT '主键',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_account
-- ----------------------------
INSERT INTO `t_account` VALUES ('1', '小明', '132', '564', '4564');
INSERT INTO `t_account` VALUES ('2', '小明', 'hhh', 'ha', '12.3');
INSERT INTO `t_account` VALUES ('3', '小明', 'hhh', 'ha', '12.3');
INSERT INTO `t_account` VALUES ('4', '小明', 'hhh', 'ha', '12.3');
INSERT INTO `t_account` VALUES ('5', 'da明', 'hhh', 'ha', '12.3');
INSERT INTO `t_account` VALUES ('6', 'da明', 'hhh', 'ha', '12.3');
INSERT INTO `t_account` VALUES ('7', 'da明', 'hhh', 'ha', '468');
INSERT INTO `t_account` VALUES ('8', 'da明', 'hhh', 'ha', '12.3');
INSERT INTO `t_account` VALUES ('9', 'da明', 'hhh', 'ha', '46');
INSERT INTO `t_account` VALUES ('10', 'da明', 'hhh', 'ha', '46');
INSERT INTO `t_account` VALUES ('11', 'da明', 'hhh', 'ha', '12.3');
-- ----------------------------
-- Table structure for t_line
-- ----------------------------
DROP TABLE IF EXISTS `t_line`;
CREATE TABLE `t_line` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from_cardno` varchar(16) DEFAULT NULL,
`to_cardno` varchar(16) DEFAULT NULL,
`memo` varchar(10) DEFAULT NULL,
`t` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_line
-- ----------------------------
2.写配置
1.用于SpringMVC的配置
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.3.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd">
<!-- 指定需要扫描的包 -->
<context:component-scan base-package="com.wf.controller"/>
<!-- 启动注解驱动 -->
<mvc:annotation-driven/>
<!--静态资源-->
<mvc:resources location="/static/" mapping="/static/**"/>
<mvc:annotation-driven/>
<!-- 定义视图解析器 -->
<bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<!-- 设置前缀 -->
<property name="prefix" value="/WEB-INF/jsp/"/>
<!-- 设置后缀 -->
<property name="suffix" value=".jsp"/>
</bean>
<!--文件上传配置-->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="defaultEncoding" value="UTF-8"/>
<property name="maxUploadSize" value="2097152"/>
</bean>
</beans>
2.IOC的配置:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<!--扫描Dao和service里面的组件-->
<context:component-scan base-package="com.wf.dao,com.wf.service"></context:component-scan>
<!--配置spring的连接池-->
<bean id="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/springj?characterEncoding=UTF8"/>
<property name="username" value="root"/>
<property name="password" value="12345678a"/>
</bean>
<!--配置dbcp2的连接池-->
<bean id="ds2" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/springj?characterEncoding=UTF8"/>
<property name="username" value="root"/>
<property name="password" value="12345678a"/>
</bean>
<!--配置JdbcTemplate-->
<bean id="template" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds"/>
</bean>
<!--配置JdbcTemplate-->
<bean id="template2" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="ds2"/>
</bean>
</beans>
3.web.xml
主要是为了加入监听器和监听器需要的IOC容器
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<!-- 配置加载Spring文件的监听器-->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:beans.xml</param-value>
</context-param>
<!--listener会读取上面的classpath:beans.xml的参数-->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!--过滤器-->
<filter>
<filter-name>encodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
<init-param>
<param-name>forceEncoding</param-name>
<param-value>true</param-value>
</init-param>
</filter>
<!--拦截请求-->
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- 配置DispatcherServlet-->
<servlet>
<servlet-name>Spring-02</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring-config.xml</param-value>
</init-param>
<load-on-startup>1</load-on-startup>
</servlet>
<!--拦截请求-->
<servlet-mapping>
<servlet-name>Spring-02</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
创建listener,将IOC容器里面的bean加载到SpringMVC IOC容器里面
4.MOJO
Account:
package com.wf.entity;
import org.springframework.stereotype.Component;
public class Account {
private int id;
private String name;
private String cardno;
private String password;
private String amount;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCardno() {
return cardno;
}
public void setCardno(String cardno) {
this.cardno = cardno;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getAmount() {
return amount;
}
public void setAmount(String amount) {
this.amount = amount;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", cardno='" + cardno + '\'' +
", password='" + password + '\'' +
", amount='" + amount + '\'' +
'}';
}
}
5.DAO:
package com.wf.dao;
import com.wf.entity.Account;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import javax.annotation.Resource;
import java.util.List;
@Repository("dao")
public class AccountDao {
//从IOC容器里面取template的bean
@Resource(name = "template")
private JdbcTemplate template;
public List selectAll() {
//sql语句
String sql = "select id,name,cardno,password,amount from t_account";
//BeanPropertyRowMapper可以将数据库结果转换为java对象,数据库表字段和实体类自动对应
RowMapper<Account> rowMapper = new BeanPropertyRowMapper<>(Account.class);
//template.query(sql, rowMapper)查询sql,转为RowMapper对象,输出Account的list集合
List<Account> list = template.query(sql, rowMapper);
return list;
}
}
6.Service:
package com.wf.service;
import com.wf.dao.AccountDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service("ser")
public class AccountService {
// @Resource(type = AccountDao.class)
@Resource(name = "dao")
private AccountDao accountDao;
//=new ClassPathXmlApplicationContext("beans.xml").getBean(AccountDao.class);
public List selectAll() {
System.out.println(accountDao);
List list = accountDao.selectAll();
return list;
}
}
7.Conroller:
package com.wf.controller;
import com.wf.service.AccountService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
import java.util.List;
@Controller
public class AccountController {
@Resource(name="ser")
private AccountService accountService;
// =new ClassPathXmlApplicationContext("beans.xml").getBean(AccountService.class);
@RequestMapping("/getAll")
public String getAll(Model model) {
// ClassPathXmlApplicationContext cp = new ClassPathXmlApplicationContext("beans.xml");
// AccountService bean = cp.getBean(AccountService.class);
//System.out.println(accountService);
// System.out.println(bean);
List list = accountService.selectAll();
model.addAttribute("list", list);
return "getAll";
}
}
稍稍简陋的
8.JSP视图层:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%--
Created by IntelliJ IDEA.
User: Administrator
Date: 2020/11/30
Time: 21:13
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<c:forEach items="${list}" var="li">
id:${li.id}<br>
name:${li.name}<br>
cardno:${li.cardno}<br>
password;:${li.password}<br>
amount:${li.amount}<br>
<hr>
</c:forEach>
</body>
</html>
结果:
9.注意点:
1.Resource和@Autowride注入static修饰的变量时候,甚至没法打包!
1.Resource给static修饰的成员变量装配会报错
下面是真正出错的原因:
2.@Autowride给static修饰的成员变量装配可以打包,但没值,会报java.lang.NullPointerException: null异常。
10.原理:
静态变量、类变量不是对象的属性,而是一个类的属性,所以静态方法是属于类(class)的,普通方法才是属于实体对象(也就是New出来的对象)的,spring注入是在容器中实例化对象,所以不能使用静态方法。
而使用静态变量、类变量扩大了静态方法的使用范围。静态方法在spring是不推荐使用的,依赖注入的主要目的,是让容器去产生一个对象的实例,然后在整个生命周期中使用他们,同时也让testing工作更加容易。