Mapping a JDBC ResultSet to an object

Question

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 lt User gt  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

User · Answer

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 lt List lt Person gt  gt  h   new BeanListHandler lt Person gt  Person class       Execute the SQL statement and return the results in a List of    Person objects generated by the BeanListHandler  List lt Person gt  persons   run query  quot SELECT   FROM Person quot   h

User · Answer

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 lt Map lt String  Object gt  gt  rows Class lt   gt  className  throws Exception            List lt Object gt  response new ArrayList lt  gt              Gson gson new Gson             for Map lt String  Object gt  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 lt Map lt String  Object gt  gt  rows new ArrayList lt Map lt String  Object gt  gt                 Hardcoded data for testing         Map lt String  Object gt  row1 new HashMap lt String  Object gt             row1 put  name    Raja            row1 put  age   22           row1 put  location    India              Map lt String  Object gt  row2 new HashMap lt String  Object gt             row2 put  name    Rani            row2 put  age   20           row2 put  location    India             rows add row1           rows add row2              SuppressWarnings  unchecked           List lt Dto gt  res  List lt Dto gt   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

User · Answer

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        More about q2o can be found here

User · Answer

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 lt T gt         private Class clazz      private Map lt String  Field gt  fields   new HashMap lt  gt         Map lt String  String gt  errors   new HashMap lt  gt          public DataMapper Class clazz            this clazz   clazz           List lt Field gt  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 lt String  Object gt  row  throws SQLException           try               T dto    T  clazz getConstructor   newInstance                for  Map Entry lt String  Object gt  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 lt T gt  map List lt Map lt String  Object gt  gt  rows  throws SQLException           List lt T gt  list   new LinkedList lt  gt              for  Map lt String  Object gt  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 lt Map lt String  Object gt  gt  rows   conn nativeSelect  SELECT   FROM products            map rows to class     ObjectMapper lt Product gt  objectMapper   new ObjectMapper lt  gt  Product class       List lt Product gt  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

User · Answer

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

User · Answer

using 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

User · Answer

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 lt User gt  users new ArrayList lt User gt      while rs next         User user   new User             user setUserId rs getString  UserId        user setFName rs getString  FirstName                           users add user

User · Answer

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 lt T gt  clazz - a list of object types you want to be fetched ResultSet resultSet - pointer to your retrieved results           List lt Field gt  fields   Arrays asList clazz getDeclaredFields         for Field field  fields            field setAccessible true              List lt T gt  list   new ArrayList lt  gt          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

[java] Mapping a JDBC ResultSet to an object

Examples related to java

Examples related to mysql

Examples related to object

Examples related to jdbc

Examples related to resultset