Map a ResultSet to Java Objects

Copy Link LinkedIn Message Facebook Email X

Now that you know how to connect to the DB, execute queries, and work with the ResultSet, it's time to tie it all together by mapping your query results to Java objects.

Why Use Java Objects

This is incredibly useful and powerful. It's thanks to Java objects that you can take the ResultSet and easily incorporate the information into your application.

Since you're so capable with Java, and you're pros with Object-oriented programming, there's really nothing you can't do when it comes to analyzing, manipulating, and displaying Java objects. So, take a look below. 

How Java Objects Work with DB Tables

In order to map your DB results to Java objects, you'll want a Java class that matches the structure of the DB table, or ResultSet, that you're getting back from the query. As always, the best way to understand is through example!

Example DB Connection with Java Objects

The first step is to create the class that reflects the table in the database.

public class Course {
  private int id;
  private String name;
  private int units;
    
  public Course() {}

  public Course(int id, String name, int units) {
    this.id = id;
    this.name = name;
    this.units = units;
  }

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public int getUnits() {
    return units;
  }

  public void setUnits(int units) {
    this.units = units;
  }

  @Override
  public String toString() {
    return "Course{" +
      "id=" + id +
      ", name='" + name + ''' +
      ", units=" + units +
      '}';
  }
}

In the example above, you have a Java class that maps exactly to the college.courses DB table. So when you run the query SELECT * FROM college.courses the ResultSet that gets returned will have the same fields and datatypes as the Java class Course above. You can now map the ResultSet onto an ArrayList of Course objects, as seen below:

private ArrayList<Course> 
  mapResultSetToObjects(ResultSet resultSet) throws SQLException {

  ArrayList<Course> retList = new ArrayList();

  // ResultSet is initially before the first data set
  while (resultSet.next()) {
    Course c = new Course();
    c.setId(resultSet.getInt("id"));
    c.setName(resultSet.getString("name"));
    c.setUnits(resultSet.getInt("units"));
    retList.add(c);
  }
  return retList;
} 

In the example above, you have a simple method that takes in a ResultSet as a method parameter, instantiates an empty ArrayList of type Course, and then loops through the ResultSet while there are remaining records.

Next, within the loop, you create an empty Course object and set its instance variables using the object's "setter methods" with the values from the current row of the ResultSet. After the Course object is fully instantiated with values, you add that object to the ArrayList and keep looping to repeat the process (if applicable) before finally returning the ArrayList back to the calling method. Below, you can see it all together in action.

public void readDataBase() throws Exception {

  try {
    Class.forName("com.mysql.jdbc.Driver");  

    connection = DriverManager.getConnection(
      "jdbc:mysql://localhost/University?user=root"
      + "&password=[YOUR_DB_PASSWORD]&useSSL=false");
    
    statement = connection.createStatement();  
    resultSet = statement.executeQuery(
      "SELECT * FROM University.courses;");

    // call method to map ResultSet to ArrayList of objects
    ArrayList<Course> course = mapResultSetToObjects(resultSet);

    // use a for-each loop to print out each object            
    for (Course c : course){
      System.out.println(c.toString());
    }
  } catch (Exception e) {
    e.printStackTrace();
    throw e;
  } finally {
    // close all connections
  }    
}  

private ArrayList<Course> mapResultSetToObjects
  (ResultSet resultSet) throws SQLException {

  ArrayList<Course> retList = new ArrayList();

  // ResultSet is initially before the first data set
  while (resultSet.next()) {
    Course c = new Course();
    c.setId(resultSet.getInt("id"));
    c.setName(resultSet.getString("name"));
    c.setUnits(resultSet.getInt("units"));
    retList.add(c);
  }
  return retList;
} 

Mapping the Result of Complex Queries

The example above uses a simple query that queries a single table, which can easily map to a single object. That said, it is often necessary to map the results of more complex queries, such as queries with one or more join statements.

This begs the question, what do you do if you have results coming back from more than one table? In this case, you can still create custom classes that can map directly to the complex ResultSet. You'll be able to practice this a little later. :)

Summary: Turn a Java ResultSet into a Java Object

• Java objects allow query resultsets to be easily incorporated into an application
• Mapping a ResultSet to a Java object requires a class that mirrors the desired table in the database
• Often a ResultSet is mapped to an ArrayList of these objects
• Mapping to Java objects can be done with simple and complex SQL queries