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
     
       {    private static Logger logger = LoggerFactory.getLogger(SimpleQuery.class);    private NamedParameterJdbcTemplate jdbcTemplate = ServiceFactory.getNamedParameterJdbcTemplate();    private SqlTemplate sqlTemplate;    private Class
      
        clazz;    public SimpleQuery(Class
       
         clazz) {        this.sqlTemplate = new SqlTemplate(clazz);        this.clazz = clazz;    }        /**     * 查询所有     *      * @return     */    public List
        
          findAll() {        return queryForList(null);    }    /**     * 使用SQL语句查询     * @param sql     * @param params     * @return     */    public T queryForObject(String sql, Map
         
           params) {        MapSqlParameterSource sps = new MapSqlParameterSource(params);        try {            return jdbcTemplate.queryForObject(sql, sps, new BeanPropertyRowMapper
          
           (this.clazz)); } catch (EmptyResultDataAccessException e) { // 没有数据 logger.info("no result , params is {}", params); } return null; } /** * 查询数量 * @param params * @return */ public int count(Map
           
             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
            
              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
             
               params = new HashMap
              
               (); params.put("id", id); return queryForObject(params); } /** * 根据一堆参数查询 * @param params * @return */ public List
               
                 queryForList(Map
                
                  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
                 
                   queryForList(String sql, Map
                  
                    params) { try { if (params != null && !params.isEmpty()) { MapSqlParameterSource sps = new MapSqlParameterSource(params); return jdbcTemplate.query(sql, sps, new BeanPropertyRowMapper
                   
                    (this.clazz)); } else { return jdbcTemplate.query(sql, new BeanPropertyRowMapper
                    
                     (this.clazz)); } } catch (EmptyResultDataAccessException e) { // 没有数据 logger.info("no result , params is {}", params); } return null; } /** * 根据命名SQL ID 查询数据 * * @param namingSqlID * @param params * @return */ public List
                     
                       queryByNamingSQL(String namingSqlID, Map
                      
                        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
                       
                         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
     
       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
      
        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
       
         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
     
       SQL_MAP = new HashMap
      
       ();    private static final List
       
         NAMING_SQL_FILES = new ArrayList
        
         ();    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
         
           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());            }        }    }}