package pers.vclee.fastweb.mapper;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Component;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import static com.alibaba.fastjson.serializer.SerializerFeature.DisableCircularReferenceDetect;
import static com.alibaba.fastjson.serializer.SerializerFeature.WriteDateUseDateFormat;
@Component
public class DbCriteria implements ApplicationContextAware {
private static final Logger logger = LoggerFactory.getLogger(DbCriteria.class);
private final static List<String> list = Arrays.asList("String", "Integer", "Long", "Double", "BigDecimal", "BigInteger");
private static JdbcTemplate jdbcTemplate;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
jdbcTemplate = applicationContext.getBean(JdbcTemplate.class);
}
public static <T> List<T> queryList(String preSql, Class<T> clazz, Object... params) {
if (list.contains(clazz.getSimpleName())) {
return jdbcTemplate.queryForList(preSql, params, clazz);
}
return jdbcTemplate.query(preSql, new BeanPropertyRowMapper<>(clazz), params);
}
public static List<Map<String, Object>> queryList(String preSql, Object... params) {
return jdbcTemplate.queryForList(preSql, params);
}
public static <T> T queryObject(String preSql, Class<T> clazz, Object... params) {
try {
if(list.contains(clazz.getSimpleName())) {
return jdbcTemplate.queryForObject(preSql, params, clazz);
}
return jdbcTemplate.queryForObject(preSql, params, new BeanPropertyRowMapper<>(clazz));
} catch (IncorrectResultSizeDataAccessException e) {
return null;
}
}
public static <T> T queryById(Object onlyKey,String tableName,Class<T> clazz) {
return queryObject("select * from " + tableName + " where id=" + preValue(onlyKey), clazz);
}
public static JSONObject queryForJsonObject(String preSql, Object... params) {
try {
return JSONObject.parseObject(JSONObject.toJSONString(jdbcTemplate.queryForMap(preSql, params)));
} catch (IncorrectResultSizeDataAccessException e) {
return null;
}
}
public static int update(String preSql, Object... params) {
return jdbcTemplate.update(preSql, params);
}
public static int updateById(Object obj,String tableName) {
JSONObject json = JSONObject.parseObject(JSON.toJSONString(obj, DisableCircularReferenceDetect,WriteDateUseDateFormat));
StringBuilder preSql = new StringBuilder("update " + tableName + " set ");
json.forEach((key, value) -> {
if (!key.equals("id")) {
preSql.append(camelToUnderline(key)).append("=")
.append(preValue(value)).append(",");
}
});
preSql.deleteCharAt(preSql.length() - 1);
preSql.append(" where id =").append(preValue(json.get("id"))).append(";");
return jdbcTemplate.update(preSql.toString());
}
public static int insertNx(Object object, String tableName){
return jdbcTemplate.update(createSingleTableInsertSqlByEntity(object,tableName));
}
public static int insert(Object object, String tableName){
try {
String preSql = createSingleTableInsertSqlByEntity(object,tableName);
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
int addNum = jdbcTemplate.update(connection -> connection.prepareStatement(preSql, PreparedStatement.RETURN_GENERATED_KEYS), keyHolder);
Class<?> clazz = object.getClass();
Method setMethod = clazz.getMethod("setId", clazz);
setMethod.invoke(object, Objects.requireNonNull(keyHolder.getKey()).intValue());
return addNum;
}catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e){
logger.error("pojo对象反射Id字段失败:"+e.getMessage());
return 0;
}
}
public static int insert(String preSql, Object... params) {
GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(connection -> {
PreparedStatement ps = connection.prepareStatement(preSql, PreparedStatement.RETURN_GENERATED_KEYS);
for (int i = 0; i < params.length; i++) {
Object param = params[i];
ps.setObject(i + 1, param);
}
return ps;
}, keyHolder);
return Objects.requireNonNull(keyHolder.getKey()).intValue();
}
public static int delete(String preSql, Object... params) {
return jdbcTemplate.update(preSql, params);
}
private static String createSingleTableInsertSqlByEntity(Object obj, String tableName) {
JSONObject json = JSONObject.parseObject(JSON.toJSONString(obj, DisableCircularReferenceDetect,WriteDateUseDateFormat));
StringBuilder preSql = new StringBuilder("insert into " + tableName);
StringBuilder keys = new StringBuilder(" (");
StringBuilder values = new StringBuilder("(");
json.forEach((key, value) -> {
keys.append(camelToUnderline(key)).append(",");
values.append(preValue(value)).append(",");
});
keys.deleteCharAt(keys.length() - 1);
values.deleteCharAt(values.length() - 1);
keys.append(") values ");
values.append(");");
preSql.append(keys.toString()).append(values.toString());
return preSql.toString();
}
private static Object preValue(Object value) {
if (value instanceof String) {
return "'" + value + "'";
} else if (value instanceof Number) {
return value;
} else if(value instanceof Date){
return Timestamp.valueOf(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(value));
}else {
return "'" + JSONObject.toJSONString(value) + "'";
}
}
private static String camelToUnderline(String filedName) {
int len = filedName.length();
StringBuilder sb = new StringBuilder(len);
for (int i = 0; i < len; i++) {
char c = filedName.charAt(i);
if (Character.isUpperCase(c)) {
sb.append("_");
sb.append(Character.toLowerCase(c));
} else {
sb.append(c);
}
}
return sb.toString();
}
private static String getTableName(Object obj) {
Type type = obj.getClass().getGenericSuperclass();
String typeName = type.getTypeName();
String preName = typeName.substring(typeName.lastIndexOf(".") + 1);
String first = preName.substring(0, 1);
return camelToUnderline(preName.replaceFirst(first, first.toLowerCase()));
}
}
使用说明
- 需要引入spring-jdbc
- 需要引入alibaba-fastjson