spring boot开发web api应用实践（二）操作数据库

在上一篇文章(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连接池 ^_^）