2014-10-07 12:55:15

[Bug更新]Java 反射JDBC ResultSet自动映射成对应实体

[更新]:修复了查询字段映射bug,导致结果集数量=字段*字段数问题。ls.add(c);应该放到for (int i = 1; i < rsm.getColumnCount() + 1; i++) {}外面。

以前一直用Spring的JdbcTemplate查询数据,JdbcTemplate被封装的及其简单易用。某些时候不太适合使用Spring这样庞大的框架,所以自己封装了个JDBC查询的自动映射类以备后用。实现了一个简单的映射数据库表到实体类数组。

TableMapping.java:

/* 
 * Copyright yz 2016-01-14  Email:[email protected] 
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.javaweb.core.db;

import org.javaweb.core.db.annotation.Column;
import org.javaweb.core.db.annotation.Entity;
import org.javaweb.core.utils.StringUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据表和查询实体类映射,简化查询
 *
 * @author yz
 */
public class TableMapping {

    private static TableMapping TableMapping = null;

    /**
     * 获取 TableMapping 对象
     *
     * @return
     */
    public static TableMapping getInstance() {
        if (TableMapping == null) {
            synchronized (TableMapping.class) {
                return TableMapping = new TableMapping();
            }
        }
        return TableMapping;
    }

    /**
     * 反射获取某类的所有成员变量名和成员变量(Field[])
     *
     * @param <T>
     * @param entityClass
     * @return
     */
    protected <T> Map<String, Field> getFieldsMap(Class<T> entityClass) {
        Map<String, Field> fieldMap = new HashMap<String, Field>();
        Field[]            field    = entityClass.getDeclaredFields();

        for (int i = 0; i < field.length; i++) {
            fieldMap.put(field[i].getName().toLowerCase(), field[i]);
        }
        return fieldMap;
    }

    /**
     * 反射获取某类及其父类的所有成员变量名和成员变量(Field[])
     *
     * @param <T>
     * @param entityClass
     * @return
     */
    protected <T> Map<String, Field> getAllFieldsMap(Class<T> entityClass) {
        Map<String, Field> map = new HashMap<String, Field>();
        Class<?>           c   = entityClass;
        map.putAll(getFieldsMap(entityClass));

        while (c.getSuperclass() != null) {
            c = c.getSuperclass();
            map.putAll(getFieldsMap(c));
        }
        return map;
    }

    /**
     * 反射获取某类的所有方法名和方法Method[]
     *
     * @param <T>
     * @param entityClass
     * @return
     */
    protected <T> Map<String, Method> getMethodsMap(Class<T> entityClass) {
        Method[]            method = entityClass.getMethods();
        Map<String, Method> map    = new HashMap<String, Method>();

        for (int i = 0; i < method.length; i++) {
            map.put(method[i].getName().toLowerCase(), method[i]);
        }
        return map;
    }

    /**
     * 反射映射数据库表字段到实体层,需要实体层必须包括成员变量和对应的public set方法 如: private int id; 对应public
     * void setId(int id);方法 成员变量不考虑大小写,默认会忽略数据库字段下划线。如:user_id 等于userId
     *
     * @param <T>
     * @param connection
     * @param entityClass 需要返回的实体类类型
     * @param sql         参数 sql 查询语句
     * @param arr         可变参数,有则传,没有可忽略
     * @return
     * @throws SQLException
     */
    protected <T> List<T> tableMapping(Connection connection, String sql, Class<T> entityClass, Object... arr) throws SQLException {
        List<T> ls = new ArrayList<T>();
        try {
            ResultSet           rs        = SqlHelp.executQuery(connection, sql, arr);
            ResultSetMetaData   rsm       = rs.getMetaData();
            Map<String, Method> methodMap = getMethodsMap(entityClass);
            Map<String, Field>  fieldMap  = getAllFieldsMap(entityClass);

            while (rs.next()) {
                try {
                    T c = entityClass.newInstance();

                    //反射设值
                    for (int i = 1; i < rsm.getColumnCount() + 1; i++) {
                        String columnName    = rsm.getColumnName(i);
                        String field         = columnName.toLowerCase().replaceAll("_", "");
                        String setColumnName = "set" + field;
                        if (methodMap.containsKey(setColumnName) && fieldMap.containsKey(field)) {
                            Method method = methodMap.get(setColumnName);
                            method.invoke(c, new Object[]{rs.getObject(columnName)});
                        }
                    }
                    ls.add(c);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (SQLException e) {
            throw e;
        }
        return ls;
    }

    /**
     * 查询数据库中一条记录并映射成对应的实体类类型 如果查询结果数大于一条抛出数据访问异常
     *
     * @param <T>
     * @param connection
     * @param sql
     * @param entityClass
     * @param arr
     * @return
     * @throws SQLException
     */
    public <T> T queryForEntity(Connection connection, String sql, Class<T> entityClass, Object... arr) throws SQLException {
        List<T> ls = tableMapping(connection, sql, entityClass, arr);

        if (ls.size() > 1) {
            throw new IncorrectResultSizeDataAccessException(ls.size());
        } else if (ls.size() == 1) {
            return ls.get(0);
        } else {
            return null;
        }
    }

    /**
     * 查询数据库中任意条记录并映射成对应的实体类集合类型
     *
     * @param <T>
     * @param connection
     * @param sql
     * @param entityClass
     * @param arr
     * @return
     */
    public <T> List<T> queryForList(Connection connection, String sql, Class<T> entityClass, Object... arr) {
        try {
            return tableMapping(connection, sql, entityClass, arr);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 映射数据库表实现更新实体类即可更新对应的表,需要先查询出对应的column信息后update
     *
     * @param <T>
     * @param connection
     * @param obj
     * @return
     * @throws Exception
     */
    public <T> int update(Connection connection, Object obj) throws Exception {
        Class<T> entityClass = (Class<T>) obj.getClass();
        if (entityClass.isAnnotationPresent(Entity.class)) {
            Entity              entity     = entityClass.getAnnotation(Entity.class);
            StringBuilder       sqlBuilder = new StringBuilder("update ");
            Map<String, Method> methodMap  = getMethodsMap(entityClass);
            Map<String, Field>  fieldMap   = getAllFieldsMap(entityClass);
            Object              id         = methodMap.get("get" + entity.id()).invoke(obj, new Object[]{});
            sqlBuilder.append(entity.table()).append(" set ").append(entity.id()).append("=").append("?");
            List<Object> ls = new ArrayList<Object>();
            ls.add(id);

            for (String str : fieldMap.keySet()) {
                String field = str.toLowerCase();

                if (!entity.id().equals(field) && methodMap.containsKey("set" + field) && methodMap.containsKey("get" + field)) {
                    Method  method     = methodMap.get("set" + str);
                    String  columnName = field;//初始化列名为field的值
                    boolean updatable  = true;

                    if (method.isAnnotationPresent(Column.class)) {
                        Column column = method.getAnnotation(Column.class);
                        columnName = column.name();
                        updatable = column.updatable();//是否允许更新
                    }

                    if (updatable) {
                        Method getMethod = methodMap.get("get" + str);
                        sqlBuilder.append(", ").append(columnName).append("=").append("?");
                        ls.add(getMethod.invoke(obj, new Object[]{}));
                    }
                }
            }

            sqlBuilder.append(" where ").append(entity.id()).append("=").append("? ");
            ls.add(id);
            return SqlHelp.executUpdate(connection, sqlBuilder.toString(), ls.toArray(new Object[ls.size()]));
        }
        return 0;
    }

    /**
     * 保存数据,如果某个field不需要保存需在其set方法上添加@Column(updatable = false)
     *
     * @param <T>
     * @param connection
     * @param obj
     * @return
     * @throws Exception
     */
    public <T> int save(Connection connection, Object obj) throws Exception {
        Class<T> entityClass = (Class<T>) obj.getClass();
        if (entityClass.isAnnotationPresent(Entity.class)) {
            Entity              entity     = entityClass.getAnnotation(Entity.class);
            StringBuilder       sqlBuilder = new StringBuilder("insert into ");
            Map<String, Method> methodMap  = getMethodsMap(entityClass);
            Map<String, Field>  fieldMap   = getAllFieldsMap(entityClass);
            Object              id         = methodMap.get("get" + entity.id()).invoke(obj, new Object[]{});
            sqlBuilder.append(entity.table()).append(" (");
            List<Object> ls         = new ArrayList<Object>();
            List<String> columnList = new ArrayList<String>();

            for (String str : fieldMap.keySet()) {
                String field = str.toLowerCase();

                if (!entity.id().equals(field) && methodMap.containsKey("set" + field) && methodMap.containsKey("get" + field)) {
                    Method  method     = methodMap.get("set" + str);
                    String  columnName = field;//初始化列名为field的值
                    boolean updatable  = true;

                    if (method.isAnnotationPresent(Column.class)) {
                        Column column = method.getAnnotation(Column.class);
                        columnName = column.name();
                        updatable = column.updatable();//是否允许更新
                    }

                    if (updatable) {
                        columnList.add(columnName);
                        Method getMethod = methodMap.get("get" + str);
                        ls.add(getMethod.invoke(obj, new Object[]{}));
                    }
                }
            }
            sqlBuilder.append(StringUtils.join(columnList, ",")).append(") values (");
            for (int i = 0; i < ls.size(); i++) {
                if (i > 0) {
                    sqlBuilder.append(",");
                }
                sqlBuilder.append("?");
            }
            sqlBuilder.append(")");
            return SqlHelp.executUpdate(connection, sqlBuilder.toString(), ls.toArray(new Object[ls.size()]));
        }
        return 0;
    }

    /**
     * 通过主键获取单个实体
     *
     * @param <T>
     * @param connection
     * @param entityClass
     * @param id
     * @return
     * @throws SQLException
     */
    public <T> T getEntity(Connection connection, Class<T> entityClass, Object id) throws SQLException {
        if (entityClass.isAnnotationPresent(Entity.class)) {
            Entity entity = entityClass.getAnnotation(Entity.class);
            String sql    = "select * from " + entity.table() + " where " + entity.id() + "=" + id;
            return queryForEntity(connection, sql, entityClass);
        } else {
            return null;
        }
    }

}
/* 
 * Copyright yz 2016-01-14  Email:[email protected] 
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.javaweb.core.db;

import com.sun.rowset.CachedRowSetImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

public class SqlHelp {

    /**
     * 执行任意的SQL查询语句并返回结果集
     *
     * @param connection 一个已打开的JDBC 连接
     * @param sql        需要执行的SQL语句
     * @param prr        SQL语句中参数预编译
     * @return rs SQL查询结果集
     * @throws SQLException
     */
    public static ResultSet executQuery(Connection connection, String sql, Object... prr) throws SQLException {
        PreparedStatement pstt = null;
        ResultSet         rs   = null;

        try {
            pstt = connection.prepareStatement(sql);
            CachedRowSetImpl cachedRowSetImpl = new CachedRowSetImpl();
            for (int i = 0; i < prr.length; i++) {
                pstt.setObject(i + 1, prr[i]);
            }
            rs = pstt.executeQuery();
            cachedRowSetImpl.populate(rs);
            return cachedRowSetImpl;
        } catch (SQLException e) {
            throw e;
        } finally {
            if (pstt != null) {
                pstt.close();
            }
            if (rs != null) {
                rs.close();
            }
        }
    }

    /**
     * 执行任意的SQL更新语句并影响行数
     *
     * @param connection 一个已打开的JDBC 连接
     * @param sql        需要执行的SQL语句
     * @param prr        SQL语句中参数预编译
     * @return i SQL更新后的影响行数
     * @throws SQLException
     */
    public static int executUpdate(Connection connection, String sql, Object... prr) throws SQLException {
        PreparedStatement pstt = null;
        try {
            pstt = connection.prepareStatement(sql);
            for (int i = 0; i < prr.length; i++) {
                pstt.setObject(i + 1, prr[i]);
            }
            return pstt.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            if (pstt != null) {
                pstt.close();
            }
        }
    }

    /**
     * 批量执行任意的SQL更新语句并影响行数数组,执行后会清空传入的参数List对象
     *
     * @param connection 一个已打开的JDBC 连接
     * @param sql        需要执行的SQL语句
     * @param parameters SQL语句中参数预编译
     * @return
     * @throws SQLException
     */
    public static int[] executBatchUpdate(Connection connection, String sql, List<Object[]> parameters) throws SQLException {
        PreparedStatement pstt = null;
        try {
            pstt = connection.prepareStatement(sql);
            for (Iterator<Object[]> it = parameters.iterator(); it.hasNext(); ) {
                Object[] parameter = it.next();
                for (int i = 0; i < parameter.length; i++) {
                    pstt.setObject(i + 1, parameter[i]);
                }
                pstt.addBatch();
                it.remove();
            }
            return pstt.executeBatch();
        } catch (SQLException e) {
            throw e;
        } finally {
            if (pstt != null) {
                pstt.close();
            }
        }
    }

}

IncorrectResultSizeDataAccessException.java:

/* 
 * Copyright yz 2016-01-14  Email:[email protected] 
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.javaweb.core.db;

import java.sql.SQLException;

/**
 * SQL查询异常,查询结果数量与期待的查询结果数不一致
 *
 * @author yz
 */
public class IncorrectResultSizeDataAccessException extends SQLException {

    private static final long serialVersionUID = -4579851381128020945L;

    public IncorrectResultSizeDataAccessException(long size) {
        super("Data access exception thrown when a result was not of the expected size,for example when expecting a single row but getting 0 or more than 1 rows.it's " + size + " rows.");
    }

}

Site.java:

package org.javaweb.core.db;

public class Site {

    private int    id;

    private int    nType;

    private int    nCodePage;

    private int    nFail;

    private int    nAlexa;

    private String siteUrl;

    private String sitePass;

    private String config;

    private String ip;

    private int    nScript;

    private String accessTime;

    private String note;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getnType() {
        return nType;
    }

    public void setnType(int nType) {
        this.nType = nType;
    }

    public int getnCodePage() {
        return nCodePage;
    }

    public void setnCodePage(int nCodePage) {
        this.nCodePage = nCodePage;
    }

    public int getnFail() {
        return nFail;
    }

    public void setnFail(int nFail) {
        this.nFail = nFail;
    }

    public int getnAlexa() {
        return nAlexa;
    }

    public void setnAlexa(int nAlexa) {
        this.nAlexa = nAlexa;
    }

    public String getSiteUrl() {
        return siteUrl;
    }

    public void setSiteUrl(String siteUrl) {
        this.siteUrl = siteUrl;
    }

    public String getSitePass() {
        return sitePass;
    }

    public void setSitePass(String sitePass) {
        this.sitePass = sitePass;
    }

    public String getConfig() {
        return config;
    }

    public void setConfig(String config) {
        this.config = config;
    }

    public String getIp() {
        return ip;
    }

    public void setIp(String ip) {
        this.ip = ip;
    }

    public int getnScript() {
        return nScript;
    }

    public void setnScript(int nScript) {
        this.nScript = nScript;
    }

    public String getAccessTime() {
        return accessTime;
    }

    public void setAccessTime(String accessTime) {
        this.accessTime = accessTime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }
}


发表回复