CODEWITHSUNDEEP
javasqljdbc
Martin
Martin

Reputation: 321

Mapping a row from a SQL data to a Java object

I have a Java class with instance fields (and matching setter methods) that match the column names of a SQL database table. I would like to elegantly fetch a row from the table (into a ResultSet) and map it to an instance of this class.

For example:

I have a "Student" class with instance fields "FNAME", "LNAME", "GRADE" and appropriate getter and setter methods for each.

I also have a SQL table with three columns of the same name.

Right now I am doing something like this:

rs = statement.executeQuery(query);

Student student = new Student();

student.setFNAME(rs.getString("FNAME"));
student.setLNAME(rs.getString("LNAME"));
student.setGRADE(rs.getString("GRADE"));

There has to be a less verbose way of doing this, right? As I add columns this might get really annoying and messy.

Upvotes: 10

Views: 40942

Answers (9)

MrAaronOlsen
MrAaronOlsen

Reputation: 141

When you execute a query you can get metadata from the ResultSet. You have access to the columns from this. Here's an example:


@RestController
public class MyController {

    @GetMapping("/characters")
    public List<Payload> characters() {
        List<Payload> results = new ArrayList<>();

        try (Connection conn = new Connection()) {
            conn.makeConnection();
            Statement stmt = conn.createStatement();

            ResultSet result = stmt.executeQuery("SELECT * FROM public.hello;");
            ResultSetMetaData resultMetaData = result.getMetaData();

            Set<String> columns = new HashSet<>();

            for (int i = 1; i <= resultMetaData.getColumnCount(); i++) {
                columns.add(resultMetaData.getColumnName(i));
            }

            while (result.next()) {
                results.add(new Data(result, columns));
            }
        } catch (Exception e) {
            results.add(new Fail("404", e.getMessage()));
        }

        return results;
    }
}

public class Data implements Payload {
    private final Map<String, Object> data = new HashMap<>();

    public Data(ResultSet result, Set<String> columns) throws SQLException {

        for (String column : columns) {
            data.put(column, result.getString(column));
        }
    }

    public Map<String, Object> getData() {
        return data;
    }
}

Now you can have one class object that parses out the columns and data for any table. You never really care what columns there are. The down side is that all of your info is now stored in a data field. So the payload would look something like:

[
  {"data":{"id":"1","name":"Rick Sanchez"}},
  {"data":{"id":"2","name":"Morty Smith"}},
  {"data":{"id":"3","message":"Summer Smith"}}
]

Upvotes: 0

Holger Thurow
Holger Thurow

Reputation: 824

Give q2o a try. It is a JPA based object mapper which helps you with many of the tedious SQL and JDBC ResultSet related tasks, but without all the complexity an ORM framework comes with.

Bind the Student class to its corresponding table:

@Table(name = "STUDENTS")
public class Student (
    private String FNAME;
    private String LNAME;
    private String GRADE;
    ...
)

Select some students by their grade:

List<Student> students = Q2ObjList.fromClause(Student.class, "GRADE = ?", grade);

Change a student's grade and persist the change to the database:

student.setGRADE(grade);
Q2obj.update(student);

q2o is helpful even when you depend on Spring JDBC:

jdbcTemplate.queryForObject("...", new RowMapper<Student>() {
    @Override
    public Student mapRow(final ResultSet rs, final int rowNum) throws SQLException {
        return Q2Obj.fromResultSet(rs, Student.class);
    }
});

It is pretty easy, isn't it? Find more about q2o here.

Upvotes: 0

Musa
Musa

Reputation: 2662

If you do not want to use any other framework, you can create standard mapping method and use it after every Result.

public class CurrencyDAO(){

        public Currency findById(int id) {
        String sql = "SELECT * FROM CCR.CURRENCY WHERE id = ?";
        Currency currency = null;
        Connection c = null;
        try {
            c = DBConnection.getConnection();
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                currency = processRow(rs);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            DBConnection.close(c);
        }
        return currency;
    }


    protected Currency processRow(ResultSet rs) throws SQLException {
        Currency currency = new Currency();
        currency.setId(rs.getInt("id"));
        currency.setEUR(rs.getString("EUR"));
        currency.setUSD(rs.getString("USD"));
        currency.setRate(rs.getString("rate"));     
        return currency;

    }
}

Upvotes: 0

Yaron Landau
Yaron Landau

Reputation: 87

You can do it generically by doing the following simple methods:

Interface to use as a method pointer:

public interface I_DBtoJavaObjectConvertable<T>
{
    public T createFromDB(ResultSet i_rs) throws SQLException;
}

Generic class to handle every mapping from SQL to java Object:

public class DBManager
{

    static volatile Connection conn;

    //set here a static c'tor to handle the connection to the database

    //The General generic method:    
    public static <T> List<T> GetObjectsFromDB(String i_Query, I_DBtoJavaObjectConvertable i_Converter)
    {
        List<T> ResList = new ArrayList<>();

        try
        {
            Statement st = conn.createStatement();
            for (ResultSet rs = st.executeQuery(i_Query); rs.next();)
            {
                ResList.add((T) i_Converter.createFromDB(rs));
            }
        }
        catch (SQLException ex)
        {
            _LOG_ERROR(ex.getMessage());
        }

        return ResList;
    }
}

Now By using Lanbda expression use can easlly convert an sql row to object, by given your convertion method, for example:

public static User FetchUserFromDB(ResultSet i_rs)
{
    User userToCreate = null;
    try
    {
        String FirstName = i_rs.getString("FirstName");
        String LastName = i_rs.getString("LastName");
        String Password = i_rs.getString("Password");

        userToCreate = new User(FirstName, LastName, Password);

    }
    catch (SQLException ex)
    {
        _LOG_ERROR("Error in fetching user from DB: \n" + ex.getMessage());
    }
    return userToCreate;
}

And now you can use this this method to bring any Users you want:

public static List<User> GetAllUsersFromDB() throws SQLException
{
    String Query = "select * "
            + "from UsersTable";

    return DBManager.GetObjectsFromDB(Query, rs -> FetchUserFromDB(rs));
}

Or:

public static List<String> GetAllNamesFromDB() throws SQLException
{
    String Query = "select FirstName "
            + "from UsersTable";

    return DBManager.GetObjectsFromDB(Query, rs -> rs.getString("FirstName"));
}

Upvotes: 4

Jeff Miller
Jeff Miller

Reputation: 1444

There are many ORM libraries that simplify or eliminate the JDBC drudgery. See Source Forge ORM for some examples. I like my library, sormula, since it can be used with minimal configuration.

Upvotes: 0

Paul
Paul

Reputation: 20091

I recommend using Spring JDBC. You don't need to use the rest of Spring to use their JDBC library. It will manage connections for you (no more closing Connection, Statement, or ResultSet) and has many conveniences, including row mapping.

We've retrofitted legacy code with Spring JDBC with little trouble.

Here is a presentation (PDF) of an overview of Spring JDBC. It's a few years old but it still works essentially the same, even without letting Spring inject the dependencies.

Spring JDBC Presentation PDF

Upvotes: 5

Jack Edmonds
Jack Edmonds

Reputation: 33211

A slightly less verbose way would be to give Student a constructor that accepts 3 strings. Then you could do this:

Student student = new Student(rs.getString("FNAME"), rs.getString("LNAME"), rs.getString("GRADE"));

The other way to do it is to use an ORM like Hibernate but Hibernate only becomes worth the massive setup effort for really big projects dealing with lots of tables.

Upvotes: 0

kosa
kosa

Reputation: 66667

If you use JDBC that is how it works. If you want to avoid adding columns like this in Java, you may consider using some ORM frameworks.

Upvotes: 0

Jamie McCrindle
Jamie McCrindle

Reputation: 9204

You could use an ORM like one of the JPA providers e.g. Hibernate. This lets you set up mappings between your objects and your tables.

Upvotes: 1

Related Questions