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.github.edgar615.util.spring.jdbc.JdbcImpl Maven / Gradle / Ivy
package com.github.edgar615.util.spring.jdbc;
import com.github.edgar615.util.base.MorePreconditions;
import com.github.edgar615.util.base.StringUtils;
import com.github.edgar615.util.db.Jdbc;
import com.github.edgar615.util.db.Persistent;
import com.github.edgar615.util.db.SQLBindings;
import com.github.edgar615.util.db.SqlBuilder;
import com.github.edgar615.util.search.Example;
import com.google.common.base.Joiner;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
/**
* Created by Edgar on 2017/8/8.
*
* @author Edgar Date 2017/8/8
*/
public class JdbcImpl implements Jdbc {
private final DataSource dataSource;
public JdbcImpl(DataSource dataSource) {
this.dataSource = dataSource;
}
@Override
public void insert(Persistent persistent) {
SQLBindings sqlBindings = SqlBuilder.insert(persistent);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.update(sqlBindings.sql(), sqlBindings.bindings().toArray());
}
@Override
public void insertAndGeneratedKey(Persistent persistent) {
SQLBindings sqlBindings = SqlBuilder.insert(persistent);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
connection -> {
PreparedStatement ps
= connection.prepareStatement(sqlBindings.sql(),
new String[]{StringUtils.underscoreName(
persistent.primaryField())}
);
int i = 1;
for (Object arg : sqlBindings.bindings()) {
ps.setObject(i++, arg);
}
return ps;
},
keyHolder);
persistent.setGeneratedKey(keyHolder.getKey());
}
@Override
public > int deleteById(Class elementType, ID id) {
SQLBindings sqlBindings = SqlBuilder.deleteById(elementType, id);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate.update(sqlBindings.sql(), sqlBindings.bindings().toArray());
}
@Override
public > int deleteByExample(Class elementType,
Example example) {
example = removeUndefinedField(elementType, example);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SQLBindings sqlBindings = SqlBuilder.whereSql(example.criteria());
String tableName = StringUtils.underscoreName(elementType.getSimpleName());
String sql = "delete from "
+ tableName;
if (!example.criteria().isEmpty()) {
sql += " where " + sqlBindings.sql();
}
return jdbcTemplate.update(sql, sqlBindings.bindings().toArray());
}
@Override
public > int updateById(Persistent persistent, ID id) {
boolean noUpdated = persistent.toMap().values().stream()
.allMatch(v -> v == null);
if (noUpdated) {
return 0;
}
SQLBindings sqlBindings = SqlBuilder.updateById(persistent, id);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate.update(sqlBindings.sql(), sqlBindings.bindings().toArray());
}
@Override
public > int updateByExample(Persistent persistent,
Example example) {
boolean noUpdated = persistent.toMap().values().stream()
.allMatch(v -> v == null);
if (noUpdated) {
return 0;
}
//对example做一次清洗，将表中不存在的条件删除，避免频繁出现500错误
example = example.removeUndefinedField(persistent.fields());
Map map = persistent.toMap();
List columns = new ArrayList<>();
List params = new ArrayList<>();
List virtualFields = persistent.virtualFields();
map.forEach((k, v) -> {
if (v != null && !virtualFields.contains(k)) {
columns.add(StringUtils.underscoreName(k) + " = ?");
params.add(v);
}
});
if (columns.isEmpty()) {
return 0;
}
MorePreconditions.checkNotEmpty(columns, "no update field");
String tableName = StringUtils.underscoreName(persistent.getClass().getSimpleName());
StringBuilder sql = new StringBuilder();
sql.append("update ")
.append(tableName)
.append(" set ")
.append(Joiner.on(",").join(columns));
List args = new ArrayList<>(params);
if (!example.criteria().isEmpty()) {
SQLBindings sqlBindings = SqlBuilder.whereSql(example.criteria());
sql.append(" where ")
.append(sqlBindings.sql());
args.addAll(sqlBindings.bindings());
}
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate.update(sql.toString(), args.toArray());
}
@Override
public > int setNullById(Class elementType, List fields,
ID id) {
List columns = removeUndefinedColumn(elementType, fields);
if (columns.isEmpty()) {
return 0;
}
SQLBindings sqlBindings = SqlBuilder.setNullById(elementType, columns, id);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate.update(sqlBindings.sql(), sqlBindings.bindings().toArray());
}
@Override
public > int setNullByExample(Class elementType,
List fields,
Example example) {
List columns = removeUndefinedColumn(elementType, fields);
if (columns.isEmpty()) {
return 0;
}
List updatedColumn = columns.stream()
.map(c -> c + " = null")
.collect(Collectors.toList());
String tableName = StringUtils.underscoreName(elementType.getSimpleName());
StringBuilder sql = new StringBuilder();
sql.append("update ")
.append(tableName)
.append(" set ")
.append(Joiner.on(",").join(updatedColumn));
List args = new ArrayList<>();
if (!example.criteria().isEmpty()) {
SQLBindings sqlBindings = SqlBuilder.whereSql(example.criteria());
sql.append(" where ")
.append(sqlBindings.sql());
args.addAll(sqlBindings.bindings());
}
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
return jdbcTemplate.update(sql.toString(), args.toArray());
}
@Override
public > T findById(Class elementType, ID id,
List fields) {
Persistent persistent = newDomain(elementType);
fields.removeIf(f -> persistent.fields().contains(f));
SQLBindings sqlBindings = SqlBuilder.findById(elementType, id, fields);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
List result =
jdbcTemplate.query(sqlBindings.sql(), sqlBindings.bindings().toArray(),
BeanPropertyRowMapper.newInstance(elementType));
if (result.isEmpty()) {
return null;
}
return result.get(0);
}
@Override
public > List findByExample(Class elementType,
Example example) {
example = removeUndefinedField(elementType, example);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SQLBindings sqlBindings = SqlBuilder.whereSql(example.criteria());
String tableName = StringUtils.underscoreName(elementType.getSimpleName());
String sql = "select * from "
+ tableName;
if (!example.criteria().isEmpty()) {
sql += " where " + sqlBindings.sql();
} else {
sql += " " + sqlBindings.sql();
}
if (!example.orderBy().isEmpty()) {
sql += SqlBuilder.orderSql(example.orderBy());
}
return jdbcTemplate.query(sql, sqlBindings.bindings().toArray(),
BeanPropertyRowMapper.newInstance(elementType));
}
@Override
public > List findByExample(Class elementType, Example example,
int start, int limit) {
example = removeUndefinedField(elementType, example);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SQLBindings sqlBindings = SqlBuilder.whereSql(example.criteria());
String tableName = StringUtils.underscoreName(elementType.getSimpleName());
String sql = "select * from "
+ tableName;
if (!example.criteria().isEmpty()) {
sql += " where " + sqlBindings.sql();
} else {
sql += " " + sqlBindings.sql();
}
if (!example.orderBy().isEmpty()) {
sql += SqlBuilder.orderSql(example.orderBy());
}
sql += " limit ?, ?";
List args = new ArrayList<>(sqlBindings.bindings());
args.add(start);
args.add(limit);
return jdbcTemplate.query(sql, args.toArray(),
BeanPropertyRowMapper.newInstance(elementType));
}
@Override
public > int countByExample(Class elementType,
Example example) {
example = removeUndefinedField(elementType, example);
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
SQLBindings sqlBindings = SqlBuilder.whereSql(example.criteria());
String tableName = StringUtils.underscoreName(elementType.getSimpleName());
String sql = "select count(*) from "
+ tableName;
if (!example.criteria().isEmpty()) {
sql += " where " + sqlBindings.sql();
} else {
sql += " " + sqlBindings.sql();
}
List args = new ArrayList<>(sqlBindings.bindings());
return jdbcTemplate.queryForObject(sql, Integer.class, args.toArray());
}
private > List removeUndefinedColumn(Class elementType,
List fields) {
Persistent persistent = newDomain(elementType);
List domainColumns = persistent.fields();
return fields.stream()
.filter(f -> domainColumns.contains(f))
.collect(Collectors.toList());
}
private > Example removeUndefinedField(Class elementType,
Example example) {
//对example做一次清洗，将表中不存在的条件删除，避免频繁出现500错误
Persistent persistent = newDomain(elementType);
example = example.removeUndefinedField(persistent.fields());
return example;
}
private Persistent newDomain(Class> clazz) {
try {
return clazz.newInstance();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
