Display the JDBC result set for an object

Question

I have a custom class that has 16 attributes such as first name, last name, dock, username, password, etc. They are all stored in the MySQL database, and when I want to get users, I use ResultSet. I want to map each of the columns to user attributes, but the way I do it seems terribly inefficient. For example, I do:

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

ie I retrieve all columns and then create user objects by inserting all column values ​​into the User constructor.

Does anyone know of a faster, more accurate way to do this?

Answer 1

No need to store the result. Set values ​​to String and set the POJO class again. Instead, install while you are extracting.

Or the best way to switch to ORM tools like hibernate instead of JDBC, which maps the POJO object directly to the database.

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

Answer 2

If you do not want to use any JPA provider such as openJPA or hibernate, you can simply try 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); 

Answer 3

Suppose you want to use basic Java, without any strategic framework. If you can guarantee that the name of this field will be equal to the column in the database, you can use the Reflection API (otherwise, create an annotation and define the name of the mapping 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 may seem inefficient, so I will keep the mapping somewhere, and not be repeated every time. However, if our T is a DTO for the purpose of data transfer and will not contain downloads of unnecessary fields, this is normal. In the end, it is much better than using standard methods.

Hope this helps someone.

Answer 4

Complete solution using @ TEH-EMPRAH ideas and general cast object to Generic Type conversion to return

 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 this relates to 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(); } 

Answer 5

Is it possible? try to get resultMap defined in Mybatis xml and then convert the native ResultSet JDBC to a list via some function MyBatis

Answer 6

I know that the questioner works with MySQL, but since a lot of people without MySQL restrictions will get to this page, but with the same question, I would like to hint at q2o. This JPA-based Java object mapping tool helps you cope with many of the tedious tasks associated with SQL and JDBC ResultSet, but despite the complexity of the ORM infrastructure, it comes with it. Using it, mapping a ResultSet to an object is as simple as this:

 while(rs.next()) { users.add(Q2Obj.fromResultSet(rs, User.class)); } 

More about q2o can be found here.

Answer 7

 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 code above. This can be used as a universal method for comparing the JDBC result with the corresponding DTO class.

Answer 8

Use the Statement sample size if you get more records. like this.

 Statement statement = connection.createStatement(); statement.setFetchSize(1000); 

Also, I don't see a problem with how you do it in terms of performance

From the point of view of Nita. Always use a separate method delegate to map results to a POJO. which can be reused later in the same class

like

 private User mapResultSet(ResultSet rs){ User user = new User(); // Map Results return user; } 

If you have the same name for the columnName and objectName names, you can also write a reflection utility to load the records back into the POJO. and use MetaData to read column names. but for small projects using reflection, no problem. but, as I said, there is nothing wrong with how you do it.

Answer 9

using DbUtils ...

The only problem I encountered in this library was that sometimes you have relationships in bean classes, DBUtils does not display this. It only displays properties in the bean class, if you have other complex properties (referencing other bean objects due to database relationships), you will have to create “indirect installers”, as I call them, which are installers that put values ​​in these complex property properties.