import java.util.HashMap;
import java.util.List;
import java.util.Map; import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
public class SimpleQuery<T> { private static Logger logger = LoggerFactory.getLogger(SimpleQuery.class); private NamedParameterJdbcTemplate jdbcTemplate = ServiceFactory.getNamedParameterJdbcTemplate(); private SqlTemplate sqlTemplate; private Class<T> clazz; public SimpleQuery(Class<T> clazz) {
this.sqlTemplate = new SqlTemplate(clazz);
this.clazz = clazz;
} /**
* 查询所有
*
* @return
*/
public List<T> findAll() {
return queryForList(null);
} /**
* 使用SQL语句查询
* @param sql
* @param params
* @return
*/
public T queryForObject(String sql, Map<String, Object> params) {
MapSqlParameterSource sps = new MapSqlParameterSource(params);
try {
return jdbcTemplate.queryForObject(sql, sps, new BeanPropertyRowMapper<T>(this.clazz));
} catch (EmptyResultDataAccessException e) {
// 没有数据
logger.info("no result , params is {}", params);
}
return null;
} /**
* 查询数量
* @param params
* @return
*/
public int count(Map<String, Object> params) {
MapSqlParameterSource sps = new MapSqlParameterSource(params);
String sql = sqlTemplate.getCountSQL(params.keySet());
Integer count = jdbcTemplate.queryForObject(sql, sps, Integer.class);
return count;
} /**
* 根据查询条件查询
* @param params
* @return
*/
public T queryForObject(Map<String, Object> params) {
String sql = null;
if (params == null) {
sql = sqlTemplate.getSelectSQL();
} else {
sql = sqlTemplate.getSelectSQL(params.keySet());
}
// 拼接SQL语句
return queryForObject(sql, params);
} /**
* 根据对象ID查询
* @param id
* @return
*/
public T queryForObject(String id) {
Map<String, Object> params = new HashMap<String, Object>();
params.put("id", id);
return queryForObject(params);
} /**
* 根据一堆参数查询
* @param params
* @return
*/
public List<T> queryForList(Map<String, Object> params) {
String sql = null;
if (params == null) {
sql = sqlTemplate.getSelectSQL();
} else {
sql = sqlTemplate.getSelectSQL(params.keySet());
}
// 拼接SQL语句
return queryForList(sql, params);
} /**
* 根据一堆参数和自定义的SQL语句查询
* @param sql
* @param params
* @return
*/
public List<T> queryForList(String sql, Map<String, Object> params) { try {
if (params != null && !params.isEmpty()) {
MapSqlParameterSource sps = new MapSqlParameterSource(params);
return jdbcTemplate.query(sql, sps, new BeanPropertyRowMapper<T>(this.clazz));
} else {
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(this.clazz));
}
} catch (EmptyResultDataAccessException e) {
// 没有数据
logger.info("no result , params is {}", params);
}
return null;
} /**
* 根据命名SQL ID 查询数据
*
* @param namingSqlID
* @param params
* @return
*/
public List<T> queryByNamingSQL(String namingSqlID, Map<String, Object> params) { String sql = NamingSqlUtil.getNamingSqlById(namingSqlID); if (sql == null) {
logger.info("error to get naming sql , id = {} ", namingSqlID);
}
// 拼接SQL语句
return queryForList(sql, params);
} /**
* 根据ID删除一个元素
*
* @param id
* @return
*/
public int delete(String id) {
String sql = sqlTemplate.getDeleteSQL();
MapSqlParameterSource sps = new MapSqlParameterSource("id", id);
return jdbcTemplate.update(sql, sps);
} /**
* 插入一个元素
*
* @param entity
* @return
*/
private int insert(T entity) {
if (entity instanceof BaseEntity) {
BaseEntity entity1 = (BaseEntity) entity;
entity1.setId(null);
}
String sql = sqlTemplate.getInsertSQL();
SqlParameterSource sps = new BeanPropertySqlParameterSource(entity);
return jdbcTemplate.update(sql, sps);
} /**
* 保存或更新一个元素
*
* @param entity
* @param params
* 确保一条数据的参数
* @return
*/
public int saveOrUpdate(T entity, Map<String, Object> params) {
T object = this.queryForObject(params);
if (object == null) {
return insert(entity);
} else {
if (object instanceof BaseEntity) {
BaseEntity object1 = (BaseEntity) object;
String id = object1.getId();
delete(id);
}
return insert(entity);
}
} }
import java.lang.reflect.Field;
import java.util.Collection; import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class SqlTemplate { private static Logger logger = LoggerFactory.getLogger(SqlTemplate.class); public final static String TABLE_PREFIX = "t"; private String tableName = ""; /**
* 如果使用多Schema模式可以使用此参数
*/
private String schemaPrefix = ""; /**
* 实体类对应的字段名
*/
private String[] fieldNames; /**
* 数据库表对应的字段名称
*/
private String[] dbFieldNames; /**
* 构造函数,解析类名,字段名,生成对应数据库中的表名和字段名
* @param clazz
*/
public SqlTemplate(Class<?> clazz) { // 获取有get方法的字段
Field[] fields = ObjectUtil.getObjectFields(clazz); int fieldsLength = fields.length; fieldNames = new String[fieldsLength]; dbFieldNames = new String[fieldsLength]; for (int i = 0; i < fieldsLength; i++) {
Field f = fields[i];
String fieldName = f.getName();
fieldNames[i] = fieldName;
dbFieldNames[i] = StringUtil.camelToUnderline(fieldName);
} String clazzName = clazz.getSimpleName(); // 数据库表名
tableName = schemaPrefix + StringUtil.camelToUnderline(TABLE_PREFIX + clazzName); } public String getInsertSQL() {
// 拼SQL语句
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO ");
sql.append("" + tableName + "");
sql.append(" (");
for (int i = 0; i < dbFieldNames.length; i++) {
sql.append("" + dbFieldNames[i] + "");
if (i < dbFieldNames.length - 1) {
sql.append(",");
}
}
sql.append(") ");
sql.append(" VALUES(");
for (int i = 0; i < fieldNames.length; i++) {
String fieldName = fieldNames[i];
sql.append(":" + fieldName);
if (i < fieldNames.length - 1) {
sql.append(",");
}
}
sql.append(") ");
return sql.toString();
} public String getUpdateSQL() {
// 拼SQL语句
StringBuffer sql = new StringBuffer();
sql.append("UPDATE ");
sql.append("" + tableName + "");
sql.append(" SET ");
for (int i = 1; i < dbFieldNames.length; i++) {
String dbFieldName = dbFieldNames[i];
String fieldName = fieldNames[i]; sql.append(dbFieldName);
sql.append("=:" + fieldName); if (i < dbFieldNames.length - 1) {
sql.append(",");
}
}
sql.append(" WHERE ");
sql.append(" id ");
sql.append("=:id");
return sql.toString();
} public String getCountSQL(Collection<String> where){
String[] whereArray = toStringArray(where);
return getCountSQL(whereArray);
} public String getCountSQL(String[] where){
StringBuffer sql = new StringBuffer();
sql.append("SELECT count(0) FROM ");
sql.append("" + tableName + "");
sql.append(toWhereSQL(where));
return sql.toString();
} public String getSelectSQL() {
// 拼SQL语句
StringBuffer sql = new StringBuffer();
sql.append("SELECT * FROM ");
sql.append("" + tableName + "");
return sql.toString();
} public String getSelectSQL(Collection<String> where) { if (where != null && !where.isEmpty()) { String[] whereArray = toStringArray(where); return getSelectSQL(whereArray);
} else {
return getSelectSQL();
}
} public String getSelectSQL(String[] where) {
// 拼SQL语句
StringBuffer sql = new StringBuffer();
sql.append("SELECT * FROM ");
sql.append("" + tableName + ""); // 如果有where条件
sql.append(toWhereSQL(where)); return sql.toString();
} public String getDeleteSQL() {
// 拼SQL语句
StringBuffer sql = new StringBuffer();
sql.append("DELETE FROM ");
sql.append("" + tableName + "");
sql.append(" WHERE ");
sql.append(" id ");
sql.append("=:id");
return sql.toString();
} public String getDeleteSQL(String[] where) {
// 拼SQL语句
StringBuffer sql = new StringBuffer();
sql.append("DELETE FROM ");
sql.append("" + tableName + ""); if (where != null && where.length > 0) {
sql.append(toWhereSQL(where));
} else {
sql.append(" WHERE ");
sql.append(" id ");
sql.append("=:id");
} return sql.toString();
} private String getDbFieldName(String fieldName) {
for (int i = 0; i < fieldNames.length; i++) {
String fName = fieldNames[i];
if (fieldName.equals(fName)) {
return dbFieldNames[i];
}
}
return null;
} public String toWhereSQL(String[] where) { StringBuffer sql = new StringBuffer(); if (where != null && where.length > 0) { sql.append(" WHERE "); for (int i = 0; i < where.length; i++) {
String w = where[i];
String dbFieldName = getDbFieldName(w); if (dbFieldName == null) {
logger.error("can not get the dbFieldName of {}", w);
return null;
} sql.append(" " + dbFieldName + " ");
sql.append("=:" + w); if (i < where.length - 1) {
sql.append(" and ");
}
}
} return sql.toString(); } private String[] toStringArray(Collection<String> where) {
if (where != null && !where.isEmpty()) {
String[] whereArray = new String[where.size()];
int i = 0;
for (String s : where) {
whereArray[i] = s;
i++;
}
return whereArray;
}
return null;
} public void setSchemaPrefix(String schemaPrefix) {
this.schemaPrefix = schemaPrefix;
}
}
import java.io.File;
import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map; import javax.xml.bind.JAXBException;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException; import org.springframework.util.StringUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException; public class NamingSqlUtil { private static final Map<String, String> SQL_MAP = new HashMap<String, String>(); private static final List<String> NAMING_SQL_FILES = new ArrayList<String>(); static {
NAMING_SQL_FILES.add("contacts-naming-sql.xml");
} public static String getNamingSqlById(String namingSqlId) {
return SQL_MAP.get(namingSqlId);
} public static void loadNamingSql() {
for (String fileName : NAMING_SQL_FILES) {
loadNamingSql(fileName);
}
} private static void loadNamingSql(String fileName) { Enumeration<URL> urls = null;
try {
urls = NamingSqlUtil.class.getClassLoader().getResources(fileName);
} catch (IOException e1) {
e1.printStackTrace();
} while (urls.hasMoreElements()) {
URL url = urls.nextElement();
try {
loadNamingSql(url);
} catch (JAXBException | ParserConfigurationException | URISyntaxException | SAXException | IOException e) {
e.printStackTrace();
}
} } private static void loadNamingSql(URL url) throws JAXBException, ParserConfigurationException, URISyntaxException, SAXException, IOException { DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
DocumentBuilder db = dbf.newDocumentBuilder(); File file = new File(url.toURI()); Document document = db.parse(file); NodeList list = document.getElementsByTagName("sql"); for (int i = 0; i < list.getLength(); i++) {
Element element = (Element) list.item(i); String id = element.getAttribute("id"); String sqlContent = element.getFirstChild().getNodeValue(); if (!StringUtils.isEmpty(sqlContent)) {
SQL_MAP.put(id, sqlContent.trim());
}
}
} }