一、pom依赖
<!-- S数据库相关 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>
<!-- E数据库相关 -->
二、application.yml
## 数据源
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.100.172:3306/db_smsmanager_new?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username: root
password: jhtSOFT1478_zgfw315
三、DataSourceConfig
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import com.zaxxer.hikari.HikariDataSource;
/**
* 数据源配置类
*
* @author STILL
*
*/
@Configuration
public class DataSourceConfig {
@Bean(name = "dataSource")
public DataSource datasoruce(Environment env) {
HikariDataSource hds = new HikariDataSource();
hds.setJdbcUrl(env.getProperty("spring.datasource.url"));
hds.setUsername(env.getProperty("spring.datasource.username"));
hds.setPassword(env.getProperty("spring.datasource.password"));
hds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
return hds;
}
}
四、BaseQueryDao
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.still.entity.PageList;
@Repository
public class BaseQueryDao {
@Autowired
JdbcTemplate jt;
/**
* 统计数量
* @param sql
* @return
*/
public Integer countNum(String sql, Object... args) {
Integer queryForObject = jt.queryForObject(sql, Integer.class, args);
return queryForObject;
}
/**
* 查询对象,防sql注入
* @param sql
* @param tClass
* @param params
* @return
*/
public <T> T getObjectBySql(String sql, Class<T> cla, Object... args) {
RowMapper<T> mapper = new BeanPropertyRowMapper<>(cla);
T object = jt.queryForObject(sql, mapper, args);
return object;
}
/**
* 查对象集合,防sql注入
* @param sql
* @param tClass
* @param params
* @return
*/
public <T> List<T> getListOfObjectBySql(String sql, Class<T> cla, Object... args) {
RowMapper<T> mapper = new BeanPropertyRowMapper<>(cla);
List<T> list = jt.query(sql, mapper, args);
return list;
}
/**
*查询map,防sql注入
* @param sql
* @param params
* @return
*/
public Map<String, Object> getMapBySql(String sql, Object... args) {
Map<String, Object> map = jt.queryForMap(sql, args);
return map;
}
/**
* 查询map集合,防sql注入
* @param sql
* @param params
* @return
*/
public List<Map<String, Object>> getListOfMapBySql(String sql, Object... args){
List<Map<String, Object>> queryForList = jt.queryForList(sql, args);
return queryForList;
}
/**
*对象集合 or map集合 分页查询
* @param sql 终执行查询的语句
* @param params 填充sql语句中的问号占位符数
* @param curpage 想要第几页的数据
* @param pagerow 每页显示多少条数
* @param cla 要封装成的实体元类型
* @return pageList对象
*/
public PageList getPageListBySql(String sql, Object[] params, int curpage, int pagerow, Class<?> cla) {
String rowsql="select count(*) from ("+ sql +") gmtxtabs_"; //查询总行数sql
int pages = 0; //总页数
int rows = countNum(rowsql, params); //查询总行数
//判断页数,如果是页大小的整数倍就为rows/pageRow如果不是整数倍就为rows/pageRow+1
if (rows % pagerow == 0) {
pages = rows / pagerow;
} else {
pages = rows / pagerow + 1;
}
//查询第page页的数据sql语句
if(curpage <= 1) {
sql+=" limit 0," + pagerow;
}else{
sql+=" limit " + ((curpage - 1) * pagerow) + "," + pagerow;
}
//查询第page页数据
List<?> list = null;
if(cla != null) {
list = getListOfObjectBySql(sql, cla, params);
}else {
list = getListOfMapBySql(sql, params);
}
//返回分页格式数据
PageList pl =new PageList();
pl.setCurPage(curpage); //设置显示的当前页数
pl.setPages(pages); //设置总页数
pl.setList(list); //设置当前页数据
pl.setTotalRows(rows); //设置总记录数
return pl;
}
}
五、pageList
public class PageList {
private int curpage; // 当前页
private int totalRows; // 总行数
private int pages; // 总页数
private List<?> list = new ArrayList<>();// 返回数据
... ...
}