[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:admin@javaweb.org.
*
* 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:admin@javaweb.org.
*
* 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:admin@javaweb.org.
*
* 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;
}
}