时间戳

首页 > 技术文章 > 简单数据访问类，生成简单SQL，自动转换成java对象

简单数据访问类，生成简单SQL，自动转换成java对象

lhp2012 2015-06-29 12:34 原文

 

 

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());
            }
        }
    }

}

 

推荐阅读