Java 将 JDBC ResultSet 映射到对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21956042/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mapping a JDBC ResultSet to an object
提问by Quanqai
I have a user class that has 16 attributes, things such as firstname, lastname, dob, username, password etc... These are all stored in a MySQL database and when I want to retrieve users I use a ResultSet. I want to map each of the columns back to the user attributes but the way I am doing it seems terribly inefficient. For example I am doing:
我有一个用户类，它有 16 个属性，比如名字、姓氏、dob、用户名、密码等……这些都存储在 MySQL 数据库中，当我想检索用户时，我使用 ResultSet。我想将每一列映射回用户属性，但我这样做的方式似乎非常低效。例如我在做：
//ResultSet rs;
while(rs.next()) {
String uid = rs.getString("UserId");
String fname = rs.getString("FirstName");
...
...
...
User u = new User(uid,fname,...);
//ArrayList<User> users
users.add(u);
}
i.e I retrieve all the columns and then create user objects by inserting all the column values into the User constructor.
即我检索所有列，然后通过将所有列值插入到 User 构造函数中来创建用户对象。
Does anyone know of a faster, neater, way of doing this?
有谁知道一种更快、更整洁的方法？
采纳答案by Shoaib Chikate
No need of storing resultSet values into String and again setting into POJO class. Instead set at the time you are retrieving.
无需将 resultSet 值存储到 String 中并再次设置到 POJO 类中。而是在您检索时设置。
Or best way switch to ORM tools like hibernate instead of JDBC which maps your POJO object direct to database.
或者最好的方法是切换到像 hibernate 这样的 ORM 工具而不是 JDBC，后者将您的 POJO 对象直接映射到数据库。
But as of now use this:
但截至目前使用这个：
List<User> users=new ArrayList<User>();
while(rs.next()) {
User user = new User();
user.setUserId(rs.getString("UserId"));
user.setFName(rs.getString("FirstName"));
...
...
...
users.add(user);
}
回答by Mani
Use Statement Fetch Size , if you are retrieving more number of records. like this.
如果要检索更多记录，请使用 Statement Fetch Size 。像这样。
Statement statement = connection.createStatement();
statement.setFetchSize(1000);
Apart from that i dont see an issue with the way you are doing in terms of performance
除此之外，我认为您在性能方面的做法没有问题
In terms of Neat. Always use seperate method delegate to map the resultset to POJO object. which can be reused later in the same class
在整洁方面。始终使用单独的方法委托将结果集映射到 POJO 对象。以后可以在同一个类中重用
like
喜欢
private User mapResultSet(ResultSet rs){
User user = new User();
// Map Results
return user;
}
If you have the same name for both columnName and object's fieldName , you could also write reflection utility to load the records back to POJO. and use MetaData to read the columnNames . but for small scale projects using reflection is not an problem. but as i said before there is nothing wrong with the way you are doing.
如果 columnName 和对象的 fieldName 具有相同的名称，则还可以编写反射实用程序将记录加载回 POJO。并使用 MetaData 读取 columnNames 。但是对于使用反射的小规模项目来说不是问题。但正如我之前所说，你的做法没有任何问题。
回答by Leo
If you don't want to use any JPA provider such as openJPA or hibernate, you can just give apache dbutils a try.
如果您不想使用任何 JPA 提供程序，例如 openJPA 或 hibernate，您可以试试 apache dbutils。
http://commons.apache.org/proper/commons-dbutils/examples.html
http://commons.apache.org/proper/commons-dbutils/examples.html
then your code will look like this
那么您的代码将如下所示
QueryRunner run = new QueryRunner(dataSource);
// Use the BeanListHandler implementation to convert all
// ResultSet rows into a List of Person JavaBeans.
ResultSetHandler<List<Person>> h = new BeanListHandler<Person>(Person.class);
// Execute the SQL statement and return the results in a List of
// Person objects generated by the BeanListHandler.
List<Person> persons = run.query("SELECT * FROM Person", h);
回答by TEH EMPRAH
Let's assume you want to use core Java, w/o any strategic frameworks. If you can guarantee, that field name of an entity will be equal to the column in database, you can use Reflection API (otherwise create annotation and define mapping name there)
假设您想使用核心 Java，没有任何战略框架。如果可以保证实体的字段名称将等于数据库中的列，则可以使用反射 API（否则在那里创建注释并定义映射名称）
By FieldName
按字段名称
/**
Class<T> clazz - a list of object types you want to be fetched
ResultSet resultSet - pointer to your retrieved results
*/
List<Field> fields = Arrays.asList(clazz.getDeclaredFields());
for(Field field: fields) {
field.setAccessible(true);
}
List<T> list = new ArrayList<>();
while(resultSet.next()) {
T dto = clazz.getConstructor().newInstance();
for(Field field: fields) {
String name = field.getName();
try{
String value = resultSet.getString(name);
field.set(dto, field.getType().getConstructor(String.class).newInstance(value));
} catch (Exception e) {
e.printStackTrace();
}
}
list.add(dto);
}
By annotation
通过注释
@Retention(RetentionPolicy.RUNTIME)
public @interface Col {
String name();
}
DTO:
DTO：
class SomeClass {
@Col(name = "column_in_db_name")
private String columnInDbName;
public SomeClass() {}
// ..
}
Same, but
一样，但是
while(resultSet.next()) {
T dto = clazz.getConstructor().newInstance();
for(Field field: fields) {
Col col = field.getAnnotation(Col.class);
if(col!=null) {
String name = col.name();
try{
String value = resultSet.getString(name);
field.set(dto, field.getType().getConstructor(String.class).newInstance(value));
} catch (Exception e) {
e.printStackTrace();
}
}
}
list.add(dto);
}
Thoughts
想法
In fact, iterating over all Fields might seem ineffective, so I would store mapping somewhere, rather than iterating each time. However, if our Tis a DTO with only purpose of transferring data and won't contain loads of unnecessary fields, that's ok. In the end it's much better than using boilerplate methods all the way.
事实上，遍历所有字段似乎无效，因此我会将映射存储在某处，而不是每次都进行迭代。但是，如果我们T的 DTO 仅用于传输数据并且不会包含大量不必要的字段，那也没关系。最后，它比一直使用样板方法要好得多。
Hope this helps someone.
希望这可以帮助某人。
回答by Christian
Complete solution using @TEH-EMPRAH ideas and Generic casting from Cast Object to Generic Type for returning
完整的解决方案，使用@TEH-EMPRAH 的想法和从Cast Object 到 Generic Type 的Generic cast返回
import annotations.Column;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.*;
public class ObjectMapper<T> {
private Class clazz;
private Map<String, Field> fields = new HashMap<>();
Map<String, String> errors = new HashMap<>();
public DataMapper(Class clazz) {
this.clazz = clazz;
List<Field> fieldList = Arrays.asList(clazz.getDeclaredFields());
for (Field field : fieldList) {
Column col = field.getAnnotation(Column.class);
if (col != null) {
field.setAccessible(true);
fields.put(col.name(), field);
}
}
}
public T map(Map<String, Object> row) throws SQLException {
try {
T dto = (T) clazz.getConstructor().newInstance();
for (Map.Entry<String, Object> entity : row.entrySet()) {
if (entity.getValue() == null) {
continue; // Don't set DBNULL
}
String column = entity.getKey();
Field field = fields.get(column);
if (field != null) {
field.set(dto, convertInstanceOfObject(entity.getValue()));
}
}
return dto;
} catch (IllegalAccessException | InstantiationException | NoSuchMethodException | InvocationTargetException e) {
e.printStackTrace();
throw new SQLException("Problem with data Mapping. See logs.");
}
}
public List<T> map(List<Map<String, Object>> rows) throws SQLException {
List<T> list = new LinkedList<>();
for (Map<String, Object> row : rows) {
list.add(map(row));
}
return list;
}
private T convertInstanceOfObject(Object o) {
try {
return (T) o;
} catch (ClassCastException e) {
return null;
}
}
}
and then in terms of how it ties in with the database, I have the following:
然后就它与数据库的关系而言，我有以下几点：
// connect to database (autocloses)
try (DataConnection conn = ds1.getConnection()) {
// fetch rows
List<Map<String, Object>> rows = conn.nativeSelect("SELECT * FROM products");
// map rows to class
ObjectMapper<Product> objectMapper = new ObjectMapper<>(Product.class);
List<Product> products = objectMapper.map(rows);
// display the rows
System.out.println(rows);
// display it as products
for (Product prod : products) {
System.out.println(prod);
}
} catch (Exception e) {
e.printStackTrace();
}
回答by Jo?o Lutz
using DbUtils...
使用 DbUtils...
The only problem I had with that lib was that sometimes you have relationships in your bean classes, DBUtils does not map that. It only maps the properties in the class of the bean, if you have other complex properties (refering other beans due to DB relationship) you'd have to create "indirect setters" as I call, which are setters that put values into those complex properties's properties.
我对该库的唯一问题是有时您的 bean 类中有关系，DBUtils 不会映射它。它只映射 bean 类中的属性，如果您有其他复杂的属性（由于 DB 关系而引用其他 bean），您必须创建我所说的“间接设置器”，它们是将值放入这些复杂的设置器属性的属性。
回答by Holger Thurow
I would like to hint on q2o. It is a JPA based Java object mapper which helps with many of the tedious SQL and JDBC ResultSet related tasks, but without all the complexity an ORM framework comes with. With its help mapping a ResultSet to an object is as easy as this:
我想暗示 q2o。它是一个基于 JPA 的 Java 对象映射器，可帮助处理许多与 SQL 和 JDBC ResultSet 相关的繁琐任务，但没有 ORM 框架带来的所有复杂性。在它的帮助下，将 ResultSet 映射到对象就像这样简单：
while(rs.next()) {
users.add(Q2Obj.fromResultSet(rs, User.class));
}
回答by Pradhap Rajamani
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.json.simple.JSONObject;
import com.google.gson.Gson;
public class ObjectMapper {
//generic method to convert JDBC resultSet into respective DTo class
@SuppressWarnings("unchecked")
public static Object mapValue(List<Map<String, Object>> rows,Class<?> className) throws Exception
{
List<Object> response=new ArrayList<>();
Gson gson=new Gson();
for(Map<String, Object> row:rows){
org.json.simple.JSONObject jsonObject = new JSONObject();
jsonObject.putAll(row);
String json=jsonObject.toJSONString();
Object actualObject=gson.fromJson(json, className);
response.add(actualObject);
}
return response;
}
public static void main(String args[]) throws Exception{
List<Map<String, Object>> rows=new ArrayList<Map<String, Object>>();
//Hardcoded data for testing
Map<String, Object> row1=new HashMap<String, Object>();
row1.put("name", "Raja");
row1.put("age", 22);
row1.put("location", "India");
Map<String, Object> row2=new HashMap<String, Object>();
row2.put("name", "Rani");
row2.put("age", 20);
row2.put("location", "India");
rows.add(row1);
rows.add(row2);
@SuppressWarnings("unchecked")
List<Dto> res=(List<Dto>) mapValue(rows, Dto.class);
}
}
public class Dto {
private String name;
private Integer age;
private String location;
//getters and setters
}
Try the above code .This can be used as a generic method to map JDBC result to respective DTO class.
试试上面的代码。这可以用作将 JDBC 结果映射到相应 DTO 类的通用方法。
