SpringBoot：集成SpringJDBC

一、创建项目，添加模块：

二、配置数据源和数据库连接池，application.properties配置参考如下：

######################################################
## EMBEDDED SERVER CONFIGURATION (ServerProperties) ##
######################################################
# Server HTTP port.
server.port = 8080
# Session timeout. If a duration suffix is not specified, seconds will be used.
server.servlet.session.timeout = 30m

#####################################################################
## DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties) ##
#####################################################################
spring.datasource.url = jdbc:mysql://192.168.1.4:3306/example
spring.datasource.username = root
spring.datasource.password = 1234

############################
## Hikari connection pool ##
############################
logging.level.com.zaxxer.hikari = INFO
spring.datasource.hikari.minimum-idle = 5
spring.datasource.hikari.maximum-pool-size = 100
spring.datasource.hikari.connection-timeout = 60000

三、引入ExtJdbcTemplate

1、添加ExtJdbcTemplate的Maven包：

<dependency>
    <groupId>org.cuckoo</groupId>
    <artifactId>spring-jdbc-ext</artifactId>
    <version>1.0.0</version>
</dependency>

2、配置ExtJdbcTemplate：

package com.example.config;

import javax.sql.DataSource;

import org.cuckoo.springframework.jdbc.ext.ExtJdbcTemplate;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class AppConfig {

    @Bean
    public ExtJdbcTemplate extJdbcTemplate(DataSource dataSource) {
        return new ExtJdbcTemplate(dataSource);
    }
}

四、在DAO层中装配和应用

1、SysUserDAO.java

package com.example.dao;

import java.util.ArrayList;
import java.util.List;

import org.cuckoo.springframework.jdbc.PageInfo;
import org.cuckoo.springframework.jdbc.ext.ExtJdbcTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.example.entity.SysPrivilegeButton;
import com.example.entity.SysPrivilegeMenu;
import com.example.entity.SysRole;
import com.example.entity.SysUser;

import net.sf.jsqlparser.JSQLParserException;

@Repository
public class SysUserDAO {
    
    @Autowired
    private ExtJdbcTemplate extJdbcTemplate;
    
    public PageInfo<SysUser> find(SysUser entity, int pageNum, int pageSize) throws DataAccessException, JSQLParserException{
        
        StringBuilder sql = new StringBuilder("SELECT * FROM sys_user WHERE 1 = 1");
        List<Object> args = new ArrayList<>();
        if (!StringUtils.isEmpty(entity.getNickname())) {
            sql.append(" AND nickname LIKE ?");
            args.add("%"+entity.getNickname()+"%");
        }
        
        PageInfo<SysUser> pageInfo = extJdbcTemplate.query(sql.toString(), args.toArray(), BeanPropertyRowMapper.newInstance(SysUser.class), pageNum, pageSize);
        return pageInfo;
    }

    /**
     * 此处是使用SpringJdbc的默认映射实现，需传递BeanPropertyRowMapper的实例
     * @param username
     * @return
     */
    public SysUser findByUsername(String username) {
        String sql = "SELECT * FROM sys_user WHERE username = ?";
        SysUser dbEntity = extJdbcTemplate.queryForObject(sql, new Object[]{username}, new BeanPropertyRowMapper<>(SysUser.class));
        return dbEntity;
    }
    
    /**
     * 此处是使用SpringJdbc的默认映射实现，需传递BeanPropertyRowMapper的实例
     * @param id
     * @return
     */
    public List<SysRole> findRolesById(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" m.*");
        sql.append(" FROM");
        sql.append(" sys_role m");
        sql.append(" LEFT JOIN sys_user_role s1 ON m.id = s1.sys_role_id");
        sql.append(" WHERE s1.sys_user_id = ?");
        return extJdbcTemplate.query(sql.toString(), new Object[]{id}, BeanPropertyRowMapper.newInstance(SysRole.class));
    }
    
    /**
     * 此处是自定义实现映射过程，实体类需实现RowMapper接口，主要用于需要实现多对一映射实体关系的情况
     * @param id
     * @return
     */
    public List<SysPrivilegeMenu> findPrivilegeMenusById(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" DISTINCT m.*,");
        sql.append(" IF(m.parent_id IS NULL, NULL, (SELECT name FROM sys_privilege_menu WHERE id = m.parent_id)) AS parent_name");
        sql.append(" FROM");
        sql.append(" sys_privilege_menu m");
        sql.append(" LEFT JOIN sys_role_privilege_menu s1 ON m.id = s1.sys_privilege_menu_id");
        sql.append(" WHERE s1.sys_role_id IN (SELECT sys_role_id FROM sys_user_role WHERE sys_user_id = ?) AND m.status = 1");
        sql.append(" ORDER BY m.`level`,m.parent_id,m.sort");
        return extJdbcTemplate.query(sql.toString(), new Object[]{id}, new SysPrivilegeMenu());
    }
    
    public List<SysPrivilegeButton> findPrivilegeButtonsById(String id) {
        return null;
    }
}

2、实体类SysPrivilegeMenu.java，自定义实现映射过程参考：

package com.example.entity;

import java.io.Serializable;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.StringUtils;

public class SysPrivilegeMenu implements RowMapper<SysPrivilegeMenu>, Serializable {

    private static final long serialVersionUID = 6882248614995484823L;
    
    private String id;
    private String name;
    private String url;
    private Integer level;
    private String parentId;
    private Integer sort;
    private Integer status;
    private SysPrivilegeMenu sysPrivilegeMenu;

    public String getId() { return id; }
    public void setId(String id) { this.id = id; }

    public String getName() { return name; }
    public void setName(String name) { this.name = name; }

    public String getUrl() { return url; }
    public void setUrl(String url) { this.url = url; }

    public Integer getLevel() { return level; }
    public void setLevel(Integer level) { this.level = level; }

    public String getParentId() { return parentId; }
    public void setParentId(String parentId) { this.parentId = parentId; }

    public Integer getSort() { return sort; }
    public void setSort(Integer sort) { this.sort = sort; }

    public Integer getStatus() { return status; }
    public void setStatus(Integer status) { this.status = status; }
    
    public SysPrivilegeMenu getSysPrivilegeMenu() {
        return sysPrivilegeMenu;
    }
    public void setSysPrivilegeMenu(SysPrivilegeMenu sysPrivilegeMenu) {
        this.sysPrivilegeMenu = sysPrivilegeMenu;
    }
    
    @Override
    public SysPrivilegeMenu mapRow(ResultSet rs, int rowNum) throws SQLException {
        
        SysPrivilegeMenu entity = new SysPrivilegeMenu();
        entity.setId(rs.getString("id"));
        entity.setName(rs.getString("name"));
        entity.setUrl(rs.getString("url"));
        entity.setLevel(rs.getInt("level"));
        entity.setParentId(rs.getString("parent_id"));
        entity.setSort(rs.getInt("sort"));
        entity.setStatus(rs.getInt("status"));
        
        String parentName = rs.getString("parent_name");
        if (!StringUtils.isEmpty(parentName)) {
            SysPrivilegeMenu parentEntity = new SysPrivilegeMenu();
            parentEntity.setName(parentName);
            entity.setSysPrivilegeMenu(parentEntity);
        }
        return entity;
    }
    
    public String buildPrivilegeString() {
        String privilegeString = null;
        if (this.level == 2 && this.sysPrivilegeMenu != null) {
            privilegeString = "MENU-" + this.sysPrivilegeMenu.getName() + "-" + this.name;
        } else {
            privilegeString = "MENU-" + this.name;
        }
        return privilegeString;
    }

}