Springboot集成jdbcTemplate，实现复杂sql查询

现在主流的框架就是ssh和ssm。

但ssh框架配合jdbcTemplate实现复杂查询，搭配hibernateTemplate是更主流的选择。

因此springboot实现jdbcTemplate和hibernateTemplate搭建的框架更是不错的选择；

1.引入pom文件

<!-- Spring Boot JDBC -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		
		<!-- Spring Boot JPA -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		
		<!-- MYSQL -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>3.4</version>
		</dependency>

2.配置数据库链接：

# mysql数据库配置
spring.datasource.url=jdbc:mysql://localhost:3306/moshop
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

3.接口类Selectable:

 
public interface Selectable {

    /**
     * 执行指定的SQL语句，返回指定对象集合。该分页查询需传入limit语句，如下：
     * SELECT user.first_name, user.last_name FROM user LIMIT :offset, :pageSize，
     * 其中:offset，:pageSize为固定参数名称。
     * 若SQL语句中不含LIMIT语句，则该方法不会进行分页
     * @param type 对象类型
     * @param limitSql SQL查询语句
     * @param params 查询语句参数
     * @param pageNum 当前页码
     * @param pageSize 每页记录数
     * @param total 总记录条数
     * @param <T> 返回对象类型
     * @return 查询结果
     */
    <T> Page<T> select(Class<T> type, String limitSql, Map<String, ?> params,
                       int pageNum, int pageSize, long total);

    /**
     * 执行指定的SQL语句，返回指定对象集合。该分页查询需传入limit语句，如下：
     * SELECT user.first_name, user.last_name FROM user LIMIT :offset, :pageSize，
     * 其中:offset，:pageSize为固定参数名称。
     * 若SQL语句中不含LIMIT语句，则该方法不会进行分页
     * @param type 对象类型
     * @param limitSql SQL查询语句
     * @param countSql 统计数据总数的SQL
     * @param params 查询语句参数
     * @param pageNum 当前页码
     * @param pageSize 每页记录数
     * @param <T> 返回对象类型
     * @return 查询结果
     */
    <T> Page<T> select(Class<T> type, String limitSql, String countSql,
                       Map<String, ?> params,
                       int pageNum, int pageSize);

    /**
     * 执行指定的SQL语句，返回指定对象集合。该分页查询需传入limit语句，如下：
     * SELECT user.first_name, user.last_name FROM user LIMIT :offset, :pageSize，
     * 其中:offset，:pageSize为固定参数名称。
     * 若SQL语句中不含LIMIT语句，则该方法不会进行分页
     * @param type 对象类型
     * @param limitSql SQL查询语句
     * @param countSql 统计数据总数的SQL
     * @param params 查询语句参数
     * @param countParams 统计数据总数语句参数
     * @param pageNum 当前页码
     * @param pageSize 每页记录数
     * @param <T> 返回对象类型
     * @return 查询结果
     */
    <T> Page<T> select(Class<T> type, String limitSql, String countSql,
                       Map<String, ?> params,
                       Map<String, ?> countParams, int pageNum,
                       int pageSize);

    /**
     * 执行指定的SQL语句，返回指定对象集合（该方法已过期，请勿使用）
     * @param sql SQL查询语句
     * @param params 查询语句参数
     * @param pageNum 当前页码
     * @param pageSize 每页记录数
     * @return 查询结果
     */
    @Deprecated
    Page<Map<String, Object>> select(String sql, Map<String, ?> params,
                       int pageNum, int pageSize);

    /**
     * 执行指定的SQL语句，返回指定Map集合。分页查询需传入limit语句，如下：
     * SELECT user.first_name, user.last_name FROM user LIMIT :offset, :pageSize，
     * 其中:offset，:pageSize为固定参数名称。
     * 若SQL语句中不含LIMIT语句，则该方法不会进行分页
     * @param limitSql SQL查询语句
     * @param params 查询语句参数
     * @param pageNum 当前页码
     * @param pageSize 每页记录数
     * @param total 总记录条数
     * @return 查询结果
     */
    Page<Map<String, Object>> select(String limitSql, Map<String, ?> params,
                                     int pageNum, int pageSize, long total);

    /**
     * 执行指定的SQL语句，返回指定Map集合。分页查询需传入limit语句，如下：
     * SELECT user.first_name, user.last_name FROM user LIMIT :offset, :pageSize，
     * 其中:offset，:pageSize为固定参数名称。
     * 若SQL语句中不含LIMIT语句，则该方法不会进行分页
     * @param limitSql SQL查询语句
     * @param countSql 统计数据总数的SQL
     * @param params 查询语句参数
     * @param pageNum 当前页码
     * @param pageSize 每页记录数
     * @return 查询结果
     */
    Page<Map<String, Object>> select(String limitSql, String countSql,
                                     Map<String, ?> params,
                                     int pageNum, int pageSize);

    /**
     * 执行指定的SQL语句，返回指定Map集合。分页查询需传入limit语句，如下：
     * SELECT user.first_name, user.last_name FROM user LIMIT :offset, :pageSize，
     * 其中:offset，:pageSize为固定参数名称。
     * 若SQL语句中不含LIMIT语句，则该方法不会进行分页
     * @param limitSql SQL查询语句
     * @param countSql 统计数据总数的SQL
     * @param params 查询语句参数
     * @param countParams 统计数据总数语句参数
     * @param pageNum 当前页码
     * @param pageSize 每页记录数
     * @return 查询结果
     */
    Page<Map<String, Object>> select(String limitSql, String countSql,
                                     Map<String, ?> params,
                                     Map<String, ?> countParams, int pageNum,
                                     int pageSize);

    /**
     * 执行指定的SQL语句，返回统计的数据数
     * @param sql SELECT COUNT语句
     * @param params 统计语句参数
     * @return 统计的数据数
     */
    long count(String sql, Map<String, ?> params);

    /**
     * 执行指定的SQL语句，返回指定对象集合
     * @param type 对象类型
     * @param sql SQL查询语句
     * @param params 查询语句参数
     * @param <T> 返回对象类型
     * @return 查询结果
     */
    <T> List<T> select(Class<T> type, String sql, Map<String, ?> params);

    /**
     * 执行指定的SQL语句，以Map形式返回数据，其中Map Key是列名称，Value是列值
     * @param sql SQL查询语句
     * @param params 查询语句参数
     * @return 查询结果
     */
    List<Map<String, Object>> select(
            String sql, Map<String, ?> params);

    /**
     * 执行指定SQL语句，返回指定的对象，对记录不存在，则返回null，
     * 该方法要求sql执行结果返回0或1条，若返回超过1条记录，将抛出org.springframework.dao.IncorrectResultSizeDataAccessException
     * 该方法可用于仅返回一行一列的查询，如：Integer/Long/Double/Datetime/Boolean/String等返回值类型
     * @param type 返回值类型
     * @param sql SQL查询语句
     * @param params 查询语句参数
     * @param <T>
     * @return 查询结果
     */
    <T> T selectOne(Class<T> type, String sql, Map<String, ?> params);
}

接口实现类

@Component
public class DefaultSelectable implements Selectable {
	
	@Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Override
    public <T> Page<T> select(Class<T> type, String limitSql, Map<String, ?> params,
                              int pageNum, int pageSize, long total) {
        List<T> list = pageQuery(type, limitSql, params, pageNum, pageSize);
        return Pages.create(list, pageNum, pageSize, total);
    }

    @Override
    public <T> Page<T> select(Class<T> type, String limitSql, String countSql,
                              Map<String, ?> params, int pageNum, int pageSize) {
        return select(type, limitSql, countSql, params, params, pageNum, pageSize);
    }

    @Override
    public <T> Page<T> select(Class<T> type, String limitSql, String countSql,
                              Map<String, ?> params, Map<String, ?> countParams,
                              int pageNum, int pageSize) {
        long total = count(countSql, countParams);
        if (total == 0L) {
            return Pages.create(new ArrayList<T>(0), pageNum, pageSize, total);
        }
        return select(type, limitSql, params, pageNum, pageSize, total);
    }

    @Override
    public Page<Map<String, Object>> select(String sql, Map<String, ?> params,
                                            int pageNum, int pageSize) {
        String countSql = String.format("SELECT COUNT(*) FROM (%s) t", sql);
        long total = count(countSql, params);
        if (total == 0L) {
            return Pages.create(new ArrayList<Map<String, Object>>(0), pageNum, pageSize, total);
        }
        //前置查询参数，total <= pageSize时虽然无需用到offset和pageSize分页参数，但sql中已有分页参数，没有加入该参数执行SQL会报错
        Map<String, Object> allParams = new HashMap<String, Object>(params);
        allParams.put("offset", pageSize * (pageNum - 1));
        allParams.put("pageSize", pageSize);
        if (total <= pageSize) {
            List<Map<String, Object>> content = select(sql, allParams);
            return Pages.create(content, pageNum, pageSize, total);
        }
        //TODO：Limit语句应由调用端传入，拼接limit语句将影响效率
        String limitSql = String.format(
                "SELECT * FROM (%s) t limit :offset, :pageSize", sql);
        List<Map<String, Object>> content = select(limitSql, allParams);
        return Pages.create(content, pageNum, pageSize, total);
    }

    @Override
    public Page<Map<String, Object>> select(String limitSql, Map<String, ?> params,
                                            int pageNum, int pageSize, long total) {
        if (total == 0L) {
            return Pages.create(new ArrayList<Map<String, Object>>(0), pageNum, pageSize, total);
        }
        //前置查询参数，total <= pageSize时虽然无需用到offset和pageSize分页参数，但sql中已有分页参数，没有加入该参数执行SQL会报错
        Map<String, Object> allParams = new HashMap<String, Object>(params);
        allParams.put("offset", pageSize * (pageNum - 1));
        allParams.put("pageSize", pageSize);
        if (total <= pageSize) {
            List<Map<String, Object>> content = select(limitSql, allParams);
            return Pages.create(content, pageNum, pageSize, total);
        }
        List<Map<String, Object>> content = select(limitSql, allParams);
        return Pages.create(content, pageNum, pageSize, total);
    }

    @Override
    public Page<Map<String, Object>> select(String limitSql, String countSql,
                                            Map<String, ?> params, int pageNum,
                                            int pageSize) {
        return select(limitSql, countSql, params, params, pageNum, pageSize);
    }

    @Override
    public Page<Map<String, Object>> select(String limitSql, String countSql,
                                            Map<String, ?> params,
                                            Map<String, ?> countParams,
                                            int pageNum, int pageSize) {
        long total = count(countSql, countParams);
        return select(limitSql, params, pageNum, pageSize, total);
    }

    @Override
    public <T> List<T> select(Class<T> type, String sql, Map<String, ?> params) {
       // return this.jdbcOperations.query(sql, params, new BeanPropertyRowMapper<T>(type));
    	  return this.jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<T>(type));
    }

    @Override
    public List<Map<String, Object>> select(String sql, Map<String, ?> params) {
        return this.jdbcTemplate.queryForList(sql, params);
    }

    @Override
    public <T> T selectOne(Class<T> type, String sql, Map<String, ?> params) {
        return this.jdbcTemplate.queryForObject(sql, params, type);
    }

    @Override
    public long count(String sql, Map<String, ?> params) {
        Long total = jdbcTemplate.queryForObject(sql, params, Long.class);
        if(total == null) {
            return 0L;
        }
        return total;
    }
    private <T> List<T> pageQuery(Class<T> type, String sql,
                                  Map<String, ?> params, int pageNum, int pageSize) {
        Map<String, Object> allParams = new HashMap<String, Object>(params);
        PageRequestWrapper pageRequest = new PageRequestWrapper(pageNum, pageSize);
        allParams.put("offset", pageRequest.getOffset());
        allParams.put("pageSize", pageSize);
        return jdbcTemplate.query(sql, allParams,
                new BeanPropertyRowMapper<T>(type));
    }

    private static class Pages {
        public static <T> Page<T> create(List<T> content, int pageNum,
                                         int pageSize, long total) {
            return new PageImpl<T>(content, new PageRequestWrapper(pageNum, pageSize),
                    total);
        }
    }

4.model类

@Component("sysRole.model")
@Scope(BeanDefinition.SCOPE_PROTOTYPE)
@Entity(name = "sysRole")
@Table(name = "sys_role")
public class SysRoleModel extends Model implements Serializable{
	
	private static final long serialVersionUID = 1L;
	
	/** 角色名称 **/
	private String  roleName;
    
    @Column(name = "role_name")
    public String getRoleName() {
        return StringUtils.isBlank(roleName) ? roleName : roleName.trim();
    }
    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
}

dao实现类：

@Repository("roleDao")
public class RoleDaoImpl implements RoleDao   {

	private static final Logger logger = LoggerFactory.getLogger(RoleDaoImpl.class);
	
	@Autowired
	protected Selectable selectable;

	@Override
	public List<SysRoleModel> queryRoleList(){
		logger.info("查询sql语句："+"select * from sys_role where 1=1");
		return selectable.select(SysRoleModel.class,"select * from sys_role where 1=1", null);
	}
	
}

测试类：

@RunWith(SpringJUnit4ClassRunner.class) // SpringJUnit支持，由此引入Spring-Test框架支持！
@SpringBootTest(classes = BearApplication.class) // 指定我们SpringBoot工程的Application启动类
public class RoleDaoTest {

	private static final Logger logger = LoggerFactory.getLogger(RoleDaoTest.class);

	@Autowired
	RoleDao roleDao;
	
     @Test
	public void queryRoleListTest(){
    	 logger.info("测试jdbc开始....");
    	 List<SysRoleModel> roleList = roleDao.queryRoleList();
    	 logger.info("测试结果:"+roleList.get(0).getRoleName());
    	 logger.info("测试jdbc结束....");
	}

}

测试结果：

2017-10-31 15:00:26.854  INFO 8328 --- [           main] com.moshop.dao.sys.RoleDaoTest           : 测试jdbc开始....
2017-10-31 15:00:26.923  INFO 8328 --- [           main] com.moshop.dao.sys.dao.impl.RoleDaoImpl  : 查询sql语句：select * from sys_role where 1=1
2017-10-31 15:00:26.969  INFO 8328 --- [           main] com.moshop.dao.sys.RoleDaoTest           : 测试结果:管理员
2017-10-31 15:00:26.969  INFO 8328 --- [           main] com.moshop.dao.sys.RoleDaoTest           : 测试jdbc结束....