在上一篇文章(spring boot开发web api应用实践(一)入门),简单介绍了如何利用spring boot搭建一个web应用,但是没有数据库操作层,本篇介绍如何引用数据库操作层。
步骤:1.前置准备---创建数据库
2.编辑pom.xml文件添加spring-boot-starter-jdbc依赖
3.编辑application.properties文件配置数据源
4.编写DAO接口代码
5.编写Service接口代码
6.修改Controller层LoginController
一、前置准备
在mysql中新建test数据,并创建tbl_user表
CREATE TABLE `tbl_user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`account` varchar(32) NOT NULL COMMENT '账户名 account name',
`password` varchar(128) NOT NULL COMMENT '密码 password',
`name` varchar(256) NOT NULL COMMENT '名字',
`email` varchar(256) NOT NULL COMMENT 'email',
`last_login_date` datetime DEFAULT NULL COMMENT '最近登录时间',
`incorrect_login_attempt` int(10) NOT NULL DEFAULT '0' COMMENT '错误登录次数,登录成功后会重置为0',
`is_locked_out` int(1) NOT NULL DEFAULT '0' COMMENT '用户是否锁定'
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
二、编辑pom.xml文件:
在<dependencies>里面增加jdbc相关依赖:
<!-- 开启数据库访问层:自动注入数据源、默认采用tomcat-jdbc连接池,默认使用spring-jdbc 默认自动开启事务配置,在需要事务的类或方法上使用@Transactional --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> <scope>compile</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>
三、配置数据源:
在工程的src\main\resources目录下面创建application.properties文件,这是spring boot的默认配置文件,可以做一些定制化的配置,如数据库配置:
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=20
spring.datasource.min-idle=5
spring.datasource.initial-size=5
spring.datasource.max-wait=10000
spring.datasource.validation-query=SELECT 1
spring.datasource.test-on-borrow=false
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=18800
spring.datasource.jdbc-interceptors=ConnectionState;SlowQueryReport(threshold=3)
上一篇入门中,结尾提到修改默认访问端口,即在application.properties文件配置server.port即可:
server.port=18081
还可以配置tomcat相关配置项,如:
server.tomcat.minSpareThreads=5
server.tomcat.maxThreads=150
server.tomcat.uriEncoding=utf-8
这些都是spring boot默认支持的配置项,更多配置项请自行参考spring boot的官方文档。
四、编写dao层
JdbcBaseDAO.java
package com.zweichxu.springboot.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import com.google.common.collect.Lists;
import com.zweichxu.platform.entity.SqlParams;
public class JdbcBaseDAO{
public static final int MAX_IN_SIZE = 1000;
@Autowired
protected JdbcTemplate jdbcTemplate;
//移除集合中的重复值
private <M> void removeDupData(List<M> inList){
int size = inList.size();
List<M> retList = new ArrayList<M>(size);
inList.forEach(m -> {
if(!retList.contains(m)){
retList.add(m);
}
});
inList.clear();
inList.addAll(retList);
}
//将in集合分组
protected <M> List<List<M>> divideArrayForSqlIn(List<M> inList){
removeDupData(inList);
return Lists.partition(inList, MAX_IN_SIZE);
}
protected <M> String whereIn(List<M> c, SqlParams params){
int size = c.size();
if(size <= 0){
throw new RuntimeException("数据集合为空,无法拼装 where in 语句");
}
StringBuilder sb = new StringBuilder();
c.forEach(m -> {
sb.append("?,");
params.addParam(m);
});
return sb.substring(0, sb.length() - 1);
}
protected String whereIn(Object[] c, SqlParams params){
int size = c.length;
if(size <= 0){
throw new RuntimeException("数据集合为空,无法拼装 where in 语句");
}
StringBuilder sb = new StringBuilder();
for(int i = 0; i < size; i++){
sb.append("?,");
params.addParam(c[i]);
}
return sb.substring(0, sb.length() - 1);
}
protected <M> String multiValue(List<M> c, SqlParams params){
int size = c.size();
if(size <= 0){
throw new RuntimeException("数据集合为空,无法拼装 where in 语句");
}
StringBuilder sb = new StringBuilder();
if (size > 1){
sb.append(" in (").append(whereIn(c, params)).append(")");
}else{
sb.append("=?");
params.addParam(c.get(0));
}
return sb.toString();
}
}
UserDAO.java
package com.zweichxu.springboot.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.zweichxu.springboot.model.User;
/**
* @author zweichxu
* @date 2017年3月30日 15:40:56
* @version 1.0
*/
@Repository(UserDAO.ANNOTATION_NAME)
public class UserDAO extends JdbcBaseDAO {
public final static String ANNOTATION_NAME = "userDAO";
public User findUserByAccount(String acct){
String sql = "select `id`, `account`, `password`, `name`, `email` from tbl_user where `account`=?";
return jdbcTemplate.query(sql, new ResultSetExtractor<User>(){
@Override
public User extractData(ResultSet rs) throws SQLException, DataAccessException{
if (rs.next()){
return parseUser(rs);
}
return null;
}
}, acct);
}
public User findUserById(int id){
String sql = "select `id`, `account`, `password`, `name`, `email` from tbl_user where `id`=?";
return jdbcTemplate.query(sql, new ResultSetExtractor<User>(){
@Override
public User extractData(ResultSet rs) throws SQLException, DataAccessException{
if (rs.next()){
return parseUser(rs);
}
return null;
}
}, id);
}
public void updateUserLoginInfo(int id, int isLockedOut, int incorrectLoginAttempt){
String sql = "update tbl_user set is_locked_out =?, incorrect_login_attempt =?,"
+ " last_login_date =now() where `id`=?";
jdbcTemplate.update(sql, isLockedOut, incorrectLoginAttempt, id);
}
private User parseUser(ResultSet rs) throws SQLException{
User user = new User();
user.setId(rs.getInt("id"));
user.setAccount(rs.getString("account"));
user.setPassword(rs.getString("password"));
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
user.setIsLockedOut(covert(rs.getInt("is_locked_out")));
user.setIncorrectLoginAttempt(rs.getInt("incorrect_login_attempt"));
user.setLastLoginDate(rs.getTimestamp("last_login_date"));
return user;
}
private boolean covert(Integer value){
return value==null?false:(value==1?true:false);
}
}
五、编写Service层代码
UserService.java
package com.zweichxu.springboot.service;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import com.zweichxu.platform.entity.BaseResp;
import com.zweichxu.platform.entity.ServiceException;
import com.zweichxu.springboot.dao.UserDAO;
import com.zweichxu.springboot.model.User;
@Service(UserService.ANNOTATION_NAME)
public class UserService {
public final static String ANNOTATION_NAME = "userService";
@Resource(name=UserDAO.ANNOTATION_NAME)
private UserDAO userDao;
public User doLogin(String acct, String pwd){
User user = userDao.findUserByAccount(acct);
if (user == null || !pwd.equals(user.getPassword())){
int maxErr = 5;
int errCnt = 0;
if (user != null){
errCnt = user.getIncorrectLoginAttempt() + 1;
userDao.updateUserLoginInfo(user.getId(), errCnt>=maxErr?1:0, errCnt);
}
throw new ServiceException(BaseResp.DATA_NOT_EXIST, errCnt>=maxErr?"用户名或密码错误,您已尝试多次,帐号已被锁定":"用户名或密码错误");
}
if (user.getIsLockedOut()){
throw new ServiceException(BaseResp.DATA_NOT_EXIST, "您的帐号已被锁定,请联系管理员");
}
userDao.updateUserLoginInfo(user.getId(), 0, 0);
return user;
}
}
六、修改LoginController(参见spring boot开发web api应用实践(一)入门)
@Resource(name=UserService.ANNOTATION_NAME)
private UserService userService; //注入Service类
@RequestMapping(value="login")
public BaseResp doLogin(@RequestParam("acct") String userName, @RequestParam("pwd") String password){
if (Util.isEmpty(userName) || Util.isEmpty(password)){
return BaseResp.fail(BaseResp.RET_CODE_PROTOCOL_ERROR, "用户名及密码均不能为空");
}
//do something
try{
User user = userService.doLogin(userName, password);
return BaseResp.success().setRetMsg("登录成功").addProperty("loginUser", user);
}catch(ServiceException e){
return BaseResp.fail(e.getRetCode(), e.getMessage());
}
}
要想引入数据库操作,关键在于添加spring-boot-starter-jdbc依赖,然后spring boot会根据配置的数据库信息及连接池参数,自动装配tomcat-jdbc连接池,并自动初始化数据源及JdbcTemplate;
然后在浏览器地址栏访问:http://localhost:8080/login?acct=zweichxu&pwd=123,可以看到浏览器页面返回值
下一篇介绍如何将tomcat-jdbc连接池换成proxool连接池(个人喜欢proxool连接池 ^_^)