将JDBC ResultSet映射到一个对象。

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个属性，比如firstname, lastname, dob, username, password等。这些都存储在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.

我。我检索所有列，然后通过将所有列值插入到用户构造函数中来创建用户对象。

Does anyone know of a faster, neater, way of doing this?

有人知道一种更快更整洁的方法吗?

6 个解决方案

#1

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.

或者切换到ORM工具(比如hibernate)，而不是将POJO对象直接映射到数据库的JDBC。

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

#2

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

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

#3

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:

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 T is 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.

希望这可以帮助别人。

#4

Complete solution using @TEH-EMPRAH ideas and Generic casting from Cast Object to Generic Type for returning

完整的解决方案，使用@TEH-EMPRAH的思想和泛型从Cast对象转换到泛型类型以返回

import annotations.Column;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.*;

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

#5

Use Statement Fetch Size , if you are retrieving more number of records. like this.

如果您正在检索更多的记录，则使用语句获取大小。像这样。

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

的整洁。总是使用seperate方法委托将resultset映射到POJO对象。哪一个可以在同一类中重用?

就像

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和object的fieldName具有相同的名称，您还可以编写反射实用程序，将记录加载回POJO。并使用元数据读取列名。但是对于小型项目来说，使用反射不是问题。但正如我之前说的，你的做法没有错。

#6

is it possible? try to get resultMap defined in Mybatis's xml, and then convert JDBC native ResultSet to List by some MyBatis function

是可能的吗?尝试在Mybatis的xml中定义resultMap，然后通过Mybatis函数将本机JDBC的ResultSet转换为List

#1