import com.github.pagehelper.Page;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
<!--more-->
import java.util.List;
import java.util.Map;
/**
* @author Jamin
* @date 2020/9/17 10:06
*/
@Slf4j
public class BaseDao {
@Autowired
NamedParameterJdbcTemplate namedParameterJdbcTemplate;
/**
* 查询
*
* @param sql sql语句
* @param object 参数与返回类型
* @return {@link List<?>}
* @author Jamin
* @date 2020/9/5 9:41
*/
public List<?> queryForList(String sql, Object object) {
BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(object);
List<?> list = namedParameterJdbcTemplate.query(sql, source, new BeanPropertyRowMapper<>(object.getClass()));
return list;
}
/**
* 查询
*
* @param sql sql语句
* @param map 参数
* @return {@link List<?>}
* @author Jamin
* @date 2020/9/5 9:41
*/
public List<?> queryForList(String sql, Class clazz, Map<String, ?> map) {
List<?> list = namedParameterJdbcTemplate.query(sql, map, new BeanPropertyRowMapper<>(clazz));
return list;
}
/**
* 执行增加删除修改
*
* @param sql
* @param object map或者javaBean
* @return {@link int}
* @author Jamin
* @date 2020/9/5 9:52
*/
public int excute(String sql, Object object) {
if (object != null) {
if (object instanceof Map) {
return namedParameterJdbcTemplate.update(sql, ((Map<String, ?>) object));
} else {
BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(object);
return namedParameterJdbcTemplate.update(sql, source);
}
} else {
return namedParameterJdbcTemplate.getJdbcOperations().update(sql);
}
}
/**
* 查询条数
*
* @param sql
* @param object
* @return {@link int}
* @author Jamin
* @date 2020/9/5 9:55
*/
public int queryCount(String sql, Object object) {
BeanPropertySqlParameterSource source = null;
if (object != null) {
source = new BeanPropertySqlParameterSource(object);
}
return namedParameterJdbcTemplate.queryForObject(sql, source, Integer.class).intValue();
}
/**
* 返回namedParameterJdbcTemplate对象
*
* @param
* @return {@link NamedParameterJdbcTemplate}
* @author Jamin
* @date 2020/9/17 10:09
*/
public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
return namedParameterJdbcTemplate;
}
/**
* 返回JdbcTemplate对象
*
* @param
* @return {@link JdbcTemplate}
* @author Jamin
* @date 2020/9/17 10:08
*/
public JdbcTemplate getJdbcTemplate() {
return namedParameterJdbcTemplate.getJdbcTemplate();
}
/**
* 返回JdbcOperations对象
*
* @param
* @return {@link JdbcOperations}
* @author Jamin
* @date 2020/9/17 10:11
*/
public JdbcOperations getJdbcOperations() {
return namedParameterJdbcTemplate.getJdbcOperations();
}
/**
* 分页查询
*
* @param sql sql语句
* @param pagination 分页对象
* @param params 参数 object或 map
* @param clazz bean
* @return {@link PageBean<T>}
* @author Jamin
* @date 2020/9/17 20:10
*/
public <T> PageBean<T> queryForPage(String sql, Page<T> pagination, Object params, Class clazz) {
PageBean<T> result = new PageBean<T>();
String countSql = "select count(1) as count from (" + sql + ") temp";
log.info(countSql);
Integer count = null;
if (params != null) {
if (params instanceof Map) {
count = namedParameterJdbcTemplate.queryForObject(countSql, ((Map<String, ?>) params), Integer.class);
} else {
BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(params);
count = namedParameterJdbcTemplate.queryForObject(countSql, source, Integer.class);
}
} else {
count = namedParameterJdbcTemplate.getJdbcOperations().update(countSql);
}
result.setTotal(count);
result.setSize(count);
result.setPageNum(pagination.getPageNum());
result.setPageSize(pagination.getPageSize());
int pageCount = result.getSize() % result.getPageSize();
result.setPages(pageCount == 0 ? (result.getSize() / result.getPageSize()) :
(result.getSize() / result.getPageSize() + 1));
sql += parseLimit(result);
List<T> list = null;
if (params != null) {
if (params instanceof Map) {
list = namedParameterJdbcTemplate.query(sql, ((Map<String, ?>) params),
new BeanPropertyRowMapper<>(clazz));
} else {
BeanPropertySqlParameterSource source = new BeanPropertySqlParameterSource(params);
list = namedParameterJdbcTemplate.query(sql, source, new BeanPropertyRowMapper<>(clazz));
}
} else {
list = namedParameterJdbcTemplate.getJdbcOperations().query(sql, new BeanPropertyRowMapper<>(clazz));
}
result.setList(list);
return result;
}
/**
* 生成分页语句
*
* @param pageBean
* @return {@link java.lang.String}
* @author Jamin
* @date 2020/9/17 10:55
*/
private <T> String parseLimit(PageBean<T> pageBean) {
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append(" ");
stringBuffer.append("limit");
stringBuffer.append(" ");
//总数为空
if (pageBean.getPageNum() == 0) {
stringBuffer.append("0");
} else {
stringBuffer.append((pageBean.getPageNum() - 1) * pageBean.getPageSize());
}
stringBuffer.append(",");
stringBuffer.append(pageBean.getPageSize());
return stringBuffer.toString();
}
}
jdbcTemplate BaseDao
于 2020-11-07 13:05:43 首次发布