Mapping A Row From A SQL Data To A Java Object

February 01, 2023 Post a Comment

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

Solution 1:

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

Solution 2:

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

Solution 3:

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.

Solution 4:

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.

Solution 5:

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.

Lab Sql