Mapping a JDBC ResultSet to an object
Solution 1
No need of storing resultSet values into String and again setting into POJO class. Instead set at the time you are retrieving.
Or best way switch to ORM tools like hibernate instead of JDBC which maps your POJO object direct to database.
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);
}
Solution 2
If you don't want to use any JPA provider such as OpenJPA or Hibernate, you can just give Apache DbUtils a try.
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);
Solution 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)
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.
Hope this helps someone.
Solution 4
Complete solution using @TEH-EMPRAH ideas and Generic casting from Cast Object to Generic Type for returning
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();
}
Solution 5
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:
while(rs.next()) {
users.add(Q2Obj.fromResultSet(rs, User.class));
}
Related videos on Youtube
Quanqai
Updated on November 20, 2021Comments
-
Quanqai over 2 years
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:
//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?
-
Mani about 10 yearswhat you mean. in efficent ? is it taking too much time
-
a_horse_with_no_name about 10 yearsCheck out Spring JDBC template and its bean mappers
-
aaberg about 10 yearsThere are a lot of tools that makes this kind of task a lot easier. I think the best ones are sql2o, JDBI and jOOQ
-
D3X about 10 years
-
-
Quanqai about 10 yearsYes, that would be slightly neater and better in the sense that I would not have to unnecessarily create all the strings, integers etc. (nor use the constructor). Thanks.
-
vissu over 5 yearsSeems perfect solution for pure jdbc logic without any frameworks. Cool ! +1
-
hephestos over 5 yearsThe pattern is very similar to a case of my own. Just my cents on this pattern. a) You need to check if you can access the field (I override that by settings always the field readable b) You can start checking first your class which might 95% have less fields than those you might get from db. for many reasons a class does not need everything c) Iterating fast to check equality is only feasible by null and equals() d) I recently placed a CacheManager build reading a tutorial and increased the performance of reflection significantly e) You need to cache repeatable query results.
-
Lii over 3 yearsThe code
convertInstanceOfObject
will never throw aClassCastException
so there is no point of trying to catch it. The cast is an unchecked cast. Instead, if the types don't match, aClassCastException
will be thrown when the return value is assigned to a variable by the calling code. -
Lii over 3 yearsDbUtils seems very nice in many ways, but it has two disadvantages: It depends on the
java.desktop
module, and it doesn't help with the task to store a bean state into a statement forINSERT
statements. -
chaitanya gupta over 2 yearswhat if result set contains 300 fields?
-
Amanpreet Singh over 2 yearsWhat if some columns are added in future? Then, changes will be required here as well.