Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.tmsps.ne4spring.base.BaseMSSQLService Maven / Gradle / Ivy
package com.tmsps.ne4spring.base;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.ArrayUtils;
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.Service;
import com.alibaba.fastjson2.JSON;
import com.tmsps.ne4spring.exception.NEServiceException;
import com.tmsps.ne4spring.orm.ClassUtil;
import com.tmsps.ne4spring.orm.ORMUtil;
import com.tmsps.ne4spring.orm.SqlServerUtil;
import com.tmsps.ne4spring.orm.model.DataModel;
import com.tmsps.ne4spring.orm.param.NeParamList;
import com.tmsps.ne4spring.orm.param.NeParamTools;
import com.tmsps.ne4spring.page.Page;
import com.tmsps.ne4spring.utils.ChkUtil;
import com.tmsps.ne4spring.utils.GenerateLongUtil;
import com.tmsps.ne4spring.utils.GenerateUtil;
import com.tmsps.ne4spring.utils.StrUtil;
import lombok.extern.slf4j.Slf4j;
/**
*
* @author zhangwei MsSQL server service
*/
@Slf4j
@Service
public class BaseMSSQLService implements IBaseService {
@Autowired
private JdbcTemplate jt;
@Override
public JdbcTemplate getJdbcTemplate() {
return this.jt;
}
@Override
public int saveObj(DataModel model) {
log.debug("save the model" + model.toJsonString());
if (ChkUtil.isNull(model)) {
log.error("保存的对象不能为空");
throw new NEServiceException("保存的对象不能为空");
}
// 获取对象中ID属性字段
Field idField = ClassUtil.getIdField(model.getClass());
// 获取ID的值
Object idVal = ClassUtil.getClassVal(idField, model);
// 如果ID字段为空，则生成一个主键
if (ChkUtil.isNull(idVal)) {
if (idField.getType() == String.class) {
idVal = GenerateUtil.getBase58ID();
ClassUtil.setClassVal(idField, model, idVal);
} else if (idField.getType() == long.class || idField.getType() == Long.class) {
idVal = GenerateLongUtil.getInstance().nextId();
ClassUtil.setClassVal(idField, model, idVal);
}
}
// 获取Bean中的属性值
final List vals = ClassUtil.getValuesPar(model);
// 根据Bean生成插入语句
String sql = SqlServerUtil.getInsSQL(model.getClass());
log.debug(sql);
return jt.update(sql, vals.toArray());
}
@Override
public DataModel saveObj(DataModel model, boolean sync) {
log.debug("save the model" + model.toJsonString());
this.saveObj(model);
if (sync) {
model = findObjById(model.getPK(), model.getClass());
} else {
}
return model;
}
@Override
public int saveTemplateObj(DataModel model) {
log.debug("save the model by template:{}", model.toJsonString());
if (ChkUtil.isNull(model)) {
log.error("保存的对象不能为空");
throw new NEServiceException("保存的对象不能为空");
}
// 获取对象中ID属性字段
Field idField = ClassUtil.getIdField(model.getClass());
// 获取ID的值
Object idVal = ClassUtil.getClassVal(idField, model);
// 如果ID字段为空，则生成一个主键
if (ChkUtil.isNull(idVal)) {
if (idField.getType() == String.class) {
idVal = GenerateUtil.getBase58ID();
ClassUtil.setClassVal(idField, model, idVal);
} else if (idField.getType() == long.class || idField.getType() == Long.class) {
idVal = GenerateLongUtil.getInstance().nextId();
ClassUtil.setClassVal(idField, model, idVal);
}
}
LinkedHashMap modelKeyVal = ClassUtil.getClassKeyValNotNull(model);
// 获取属性名称
final List propertys = ClassUtil.getKeyList(modelKeyVal);
// 获取属性值
final List vals = ClassUtil.getValList(modelKeyVal);
// 根据Bean生成插入语句
String sql = SqlServerUtil.getTemplateInsSQL(ClassUtil.getTableName(model.getClass()), propertys);
log.debug(sql);
return jt.update(sql, vals.toArray());
}
@Override
public DataModel saveTemplateObj(DataModel model, boolean sync) {
log.debug("save the model by template:{} with {}", model.toJsonString(), String.valueOf(sync));
this.saveTemplateObj(model);
if (sync) {
model = this.findById(model.getPK(), model.getClass());
}
return model;
}
@Override
public void saveObjs(List objs) {
if (ChkUtil.listIsNull(objs)) {
log.error("保存的对象集合不能为空");
throw new NEServiceException("保存的对象集合不能为空");
}
objs.forEach(model -> {
this.saveTemplateObj(model);
});
}
@Override
public T findObjById(Object idVal, Class clazz) {
return this.findById(idVal, clazz);
}
@Override
public T findById(Object idVal, Class clazz) {
// 主键为空，无法查询，返回null
if (ChkUtil.isNull(idVal)) {
log.warn("主键为null,无法查询,返回null");
return null;
}
String sql = SqlServerUtil.getSelectSQL(clazz, false);
log.debug(sql);
return jt.query(sql, new Object[] { idVal }, new ResultSetExtractor() {
@SuppressWarnings("unchecked")
public T extractData(ResultSet rs) throws SQLException, DataAccessException {
while (rs.next()) {
T obj = ((T) SqlServerUtil.fillPojoByResultSet(rs, clazz));
// 只取第一个
return (T) obj;
}
return null;
}
});
}
@Override
public Map findById(String tableName, String pkName, String pkVal) {
List> list = jt.queryForList(SqlServerUtil.getSelectByIdSQL(tableName, pkName), pkVal);
return list.size() == 1 ? list.get(0) : null;
}
@Override
public Map findById(String tableName, String pkVal) {
return jt.queryForMap(SqlServerUtil.getSelectByIdSQL(tableName), pkVal);
}
@Override
public Map findObj(String sql) {
List> list = findList(sql);
if (ChkUtil.listIsNotNull(list)) {
return list.get(0);
} else {
return null;
}
}
@Override
public Map findObj(String sql, Object[] vals) {
log.debug("{},{}", sql, JSON.toJSONString(vals));
List> list = this.findList(sql, vals);
if (ChkUtil.listIsNotNull(list)) {
return list.get(0);
} else {
return null;
}
}
@SuppressWarnings("unchecked")
@Override
public T findObj(String sql, Class clazz) {
List> list = findList(sql);
if (ChkUtil.listIsNotNull(list)) {
return (T) ORMUtil.fillMapToBean(clazz, list.get(0));
} else {
return null;
}
}
@Override
public T findForObj(String sql, Class clazz) {
return findObj(sql, clazz);
}
@Override
public List> findList(String sql) {
log.debug(sql);
return jt.queryForList(sql);
}
@Override
public List findList(String sql, Class clazz) {
List list = new ArrayList();
jt.query(sql, new RowMapper() {
@Override
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
T t = ORMUtil.fillResultSetToBean(rs, clazz);
list.add(t);
return t;
}
});
return list;
}
@Override
public List> findList(String sql, Object[] vals) {
log.debug("{},{}", sql, JSON.toJSONString(vals));
return jt.queryForList(sql, vals);
}
@Override
public List findList(Class clazz, String sql, Object... vals) {
List> list = this.findList(sql, vals);
return ORMUtil.fillMapToList(list, clazz);
}
@Override
public T findObj(String sql, Object[] vals, Class modelClass) {
return ORMUtil.fillMapToBean(modelClass, findObj(sql, vals));
}
@Override
public T findObj(Class clazz, String sql, Object... vals) {
Map map = this.findObj(sql, vals);
return ORMUtil.fillMapToAnyBean(clazz, map);
}
@Override
public T findObj(Class clazz, String sql, NeParamList params) {
return this.findObj(clazz, sql, params.getParamValues());
}
@Override
public int deleteObjById(Object id, Class clazz) {
final String sql = SqlServerUtil.getDelRealSQL(clazz);
return jt.update(sql, id);
}
@Override
public int deleteByID(String pkColumn, Object id, String tableName) {
return jt.update(SqlServerUtil.getDelRealSQL(tableName, pkColumn));
}
@Override
public int updateObj(DataModel obj) {
return this.updateObjT(obj);
}
@Override
public int updateObjT(DataModel obj) {
if (ChkUtil.isNull(obj)) {
log.error("update faile , the obj can not null !");
return 0;
} else {
String sql = SqlServerUtil.getUpdateSQL(obj.getClass());
final LinkedHashMap clsky = ClassUtil.getClassKeyValWithoutPk(obj);
List vals = new ArrayList();
clsky.entrySet().forEach(entry -> {
vals.add(entry.getValue());
});
Object pkVal = ClassUtil.getClassVal(ClassUtil.getIdField(obj.getClass()), obj);
vals.add(pkVal);
log.debug("sql:{} and paras is {}", sql, vals.toString());
return jt.update(sql, vals.toArray());
}
}
@Override
public int updateChangeObj(DataModel obj) {
return updateTemplateObj(obj);
}
@Override
public int updateTemplateObj(DataModel obj) {
if (ChkUtil.isNull(obj)) {
log.error("修改失败！对象不允许为空！");
return 0;
} else {
String sql = SqlServerUtil.getChangeUpdateSQL(obj);
final LinkedHashMap clsky = ClassUtil.getClassKeyValNotNull(obj);
List vals = new ArrayList();
clsky.entrySet().forEach(entry -> {
vals.add(entry.getValue());
});
String pk = ClassUtil.getIdField(obj.getClass()).getName();
Object pkVal = clsky.get(pk);
vals.remove(pkVal);
vals.add(pkVal);
log.debug("sql:{} and paras is {}", sql, vals.toString());
return jt.update(sql, vals.toArray());
}
}
@Override
public List> findList(String sql, String sql_cnt, Object[] vals, Page page) {
return null;
}
@Override
public List> findList(String sql, Object[] vals, Map sort_params, Page page) {
// TODO Auto-generated method stub
return null;
}
@Override
public List> findList(String sql, Object[] vals, Page page) {
// TODO Auto-generated method stub
return null;
}
@Override
public List> findList(String sql, Page page) {
// TODO Auto-generated method stub
return null;
}
@Override
public List findList(Class clazz, Page page, String sql, String sql_cnt, Object... vals) {
// TODO Auto-generated method stub
return null;
}
@Override
public List findList(Class clazz, Page page, String sql, Map sort_params,
Object... vals) {
// TODO Auto-generated method stub
return null;
}
@Override
public List findList(Class clazz, Page page, String sql, Object... vals) {
// TODO Auto-generated method stub
return null;
}
@Override
public List findList(Class clazz, Page page, String sql) {
// TODO Auto-generated method stub
return null;
}
@Override
public int updObj(Class clazz, Map parm, Map whereparm) {
StringBuffer sb = new StringBuffer(" update ");
sb.append(" " + ClassUtil.getClassName(clazz) + " ");
sb.append(" set ");
List cxparm = new ArrayList();
int i = 0;
for (String key : parm.keySet()) {
if (i > 0) {
sb.append(",");
}
sb.append(" " + key + " = ? ");
i++;
cxparm.add(parm.get(key));
}
sb.append(" where 1=1 ");
if (ChkUtil.isNotNull(whereparm)) {
for (String key : whereparm.keySet()) {
sb.append(" and " + key + " = ? ");
cxparm.add(whereparm.get(key));
}
}
return this.jt.update(sb.toString(), cxparm.toArray());
}
@Override
public Map getMap(String key, Object val) {
return getMap(new String[] { key }, new Object[] { val });
}
@Override
public Map> getSearchMap() {
Map> cxparms = new HashMap>();
cxparms.put("cname", new ArrayList());
cxparms.put("cwhere", new ArrayList());
cxparms.put("cval", new ArrayList());
return cxparms;
}
@Override
public Map getMap(String[] key, Object[] val) {
Map mapobj = new HashMap();
for (int i = 0; i < key.length; i++) {
mapobj.put(key[i], val[i]);
}
return mapobj;
}
@Override
public Page pageinate(int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras) {
// TODO Auto-generated method stub
return null;
}
@Override
public Page pageinate(int pageNumber, int pageSize, String select, String sqlExceptSelect) {
// TODO Auto-generated method stub
return null;
}
@Override
public Page pageinate(Page page, String select, String sqlExceptSelect, NeParamList params) {
// TODO Auto-generated method stub
return null;
}
@Override
public Page pageinate(Page page, String select, String sqlExceptSelect, Object... paras) {
// TODO Auto-generated method stub
return null;
}
@Override
public Page pageinate(Page page, String select, String sqlExceptSelect) {
// TODO Auto-generated method stub
return null;
}
@Override
public List> findList(String sql, NeParamList params, Map sort_params,
Page page) {
// TODO Auto-generated method stub
return null;
}
@Override
public List> findList(String sql, NeParamList params) {
String endSql = NeParamTools.handleSql(sql, params);
log.debug("{},{}", endSql, params.toString());
return jt.queryForList(endSql, params.getParamValues());
}
@Override
public List findList(Class clazz, String sql, NeParamList params, Map sort_params,
Page page) {
// TODO Auto-generated method stub
return null;
}
@Override
public List findList(Class clazz, String sql, NeParamList params) {
List> list = this.findList(sql, params);
return ORMUtil.fillMapToList(list, clazz);
}
@Override
public T queryForObject(String sql, Class requiredType) {
log.debug("{},{}", sql, requiredType.getClass().toString());
return getJdbcTemplate().queryForObject(sql, requiredType);
}
@Override
public T queryForObject(String sql, NeParamList params, Class requiredType) {
String endSql = NeParamTools.handleSql(sql, params);
log.debug("{},{}", endSql, params.toString());
return getJdbcTemplate().queryForObject(endSql, requiredType, params.getParamValues());
}
/**
* selectContent 获得构造sql查询字段 fromContent 获得查询数据表的关联关系 orderByContent 定义排序对象
*/
public Page pageinate(int pageNumber, int pageSize, String selectContent, String fromContent,
String orderByContent) {
if (pageNumber < 1 || pageSize < 1) {
log.error("当前页面页数和页面记录展示条数不能小于1");
return new Page(pageNumber, pageSize);
}
long totalRow = 0;
int totalPage = 0;
// 构造查询记录总条数的sql语句
StringBuffer countSqlBuffer = new StringBuffer("select count(*) from ");
countSqlBuffer.append(fromContent);
totalRow = jt.queryForObject(countSqlBuffer.toString(), Long.class);
if (totalRow % pageSize == 0) {
totalPage = (int) (totalRow / pageSize);
} else {
totalPage = (int) (totalRow / pageSize) + 1;
}
log.debug("The TotalPage is :" + totalPage);
String sql = SqlServerUtil.getPageSQL(pageSize, selectContent, fromContent, orderByContent);
log.debug("SQL: {}", sql);
// 判断当前页面是否大于最大页面
pageNumber = pageNumber >= totalPage ? totalPage : pageNumber;
List> result = jt.queryForList(sql,
(pageNumber - 1) * pageSize < 0 ? 0 : (pageNumber - 1) * pageSize);
return new Page(result, pageNumber, pageSize, totalPage, ((Number) totalRow).intValue());
}
public Page pageinate(Page page, String selectContent, String fromContent, String orderByContent) {
return pageinate(page.getPageNumber(), page.getPageSize(), selectContent, fromContent, orderByContent);
}
public Page pageinate(int pageNumber, int pageSize, String selectContent, String fromContent, String orderByContent,
Object... paras) {
if (pageNumber < 1 || pageSize < 1) {
log.error("当前页面页数和页面记录展示条数不能小于1");
return new Page(pageNumber, pageSize);
}
long totalRow = 0;
int totalPage = 0;
if (!StrUtil.notNull(paras)) {
return pageinate(pageNumber, pageSize, selectContent, fromContent, orderByContent);
}
// 构造查询记录总条数的sql语句
StringBuffer countSqlBuffer = new StringBuffer("select count(*) from ");
countSqlBuffer.append(fromContent);
totalRow = jt.queryForObject(countSqlBuffer.toString(), paras, Long.class);
if (totalRow % pageSize == 0) {
totalPage = (int) (totalRow / pageSize);
} else {
totalPage = (int) (totalRow / pageSize) + 1;
}
log.debug("The TotalPage is :" + totalPage);
String sql = SqlServerUtil.getPageSQL(pageSize, selectContent, fromContent, orderByContent);
log.debug("SQL: " + sql);
// 判断当前页面是否大于最大页面
pageNumber = pageNumber >= totalPage ? totalPage : pageNumber;
List objs = new ArrayList(Arrays.asList(paras));
objs.add((pageNumber - 1) * pageSize < 0 ? 0 : (pageNumber - 1) * pageSize);
List> result = jt.queryForList(sql, objs.toArray());
return new Page(result, pageNumber, pageSize, totalPage, ((Number) totalRow).intValue());
}
public Page pageinate(Page page, String selectContent, String fromContent, String orderByContent, Object... paras) {
return pageinate(page.getPageNumber(), page.getPageSize(), selectContent, fromContent, orderByContent, paras);
}
public List> findList(Page page, String selectContent, String fromContent,
String orderByContent) {
List> ret = null;
// 构造查询记录总条数的sql语句
StringBuffer countSqlBuffer = new StringBuffer("select count(*) from ");
countSqlBuffer.append(fromContent);
int cnt = jt.queryForObject(countSqlBuffer.toString(), Integer.class);
page.setTotalRow(cnt);
page.setTotalPage((page.getTotalRow() - 1) / page.getPageSize() + 1);
if (page.getPageNumber() > page.getTotalPage()) {
page.setPageNumber(page.getTotalPage());
}
if (page.getPageNumber() <= 0) {
page.setPageNumber(1);
}
String sql = SqlServerUtil.getPageSQL(page.getPageSize(), selectContent, fromContent, orderByContent);
log.debug("SQL: " + sql);
ret = jt.queryForList(sql, (page.getPageNumber() - 1) * page.getPageSize() < 0 ? 0
: (page.getPageNumber() - 1) * page.getPageSize());
return ret;
}
public List> findList(Page page, String selectContent, String fromContent,
String orderByContent, Object[] vals) {
List> ret = null;
// 构造查询记录总条数的sql语句
StringBuffer countSqlBuffer = new StringBuffer("select count(*) from ");
countSqlBuffer.append(fromContent);
int cnt = jt.queryForObject(countSqlBuffer.toString(), vals, Integer.class);
page.setTotalRow(cnt);
page.setTotalPage((page.getTotalRow() - 1) / page.getPageSize() + 1);
if (page.getPageNumber() > page.getTotalPage()) {
page.setPageNumber(page.getTotalPage());
}
if (page.getPageNumber() <= 0) {
page.setPageNumber(1);
}
String sql = SqlServerUtil.getPageSQL(page.getPageSize(), selectContent, fromContent, orderByContent);
log.debug("SQL: " + sql);
vals = ArrayUtils.add(vals, (page.getPageNumber() - 1) * page.getPageSize() < 0 ? 0
: (page.getPageNumber() - 1) * page.getPageSize());
ret = jt.queryForList(sql, vals);
return ret;
}
public List findList(Class clazz, Page page, String selectContent, String fromContent,
String orderByContent) {
List> list = this.findList(page, selectContent, fromContent, orderByContent);
return ORMUtil.fillMapToList(list, clazz);
}
public List findList(Class clazz, Page page, String selectContent, String fromContent,
String orderByContent, Object[] vals) {
List> list = this.findList(page, selectContent, fromContent, orderByContent, vals);
return ORMUtil.fillMapToList(list, clazz);
}
}
