Java & SQL - ResultSet with class' toString() method

Question

I have a class called Item, which - besides everything else as usual - defines the toString method like this:

@Override
public String toString() {
    return "Item [id=" + id + ", name=" + name + ", type=" + type + "]";
}

Then I have an SQL connector for dumping all items in the table like this:

public ArrayList<Item> dumpAll() {
        ArrayList<Item> dump = new ArrayList<Item>();
        connectDB();
        try {
            result = connectDB().prepareStatement("SELECT * FROM Items").executeQuery();
            if (result != null) {
                connectDB().close();
                while (result.next()) {
                    Item item = new Item();
                    item.toString();
                    dump.add(item);
                }
            }
        } catch (Exception e) {
            System.out.println("Query failed.");
            // e.printStackTrace();
        }
        return dump;
    }

connectDB(); is working fine. When I call the toString method elsewhere, I can see a result like this:

Item [id=0, name=null, type=null]

This line is repeated as many times as I have items in the table, i.e. I have 50 items; I get 50 lines showing zeroes and nulls. The format is exactly as I want it but obviusly the results are not. I think I'm missing something obvious handling the result, but trying to debug with String itm = result.toString();, Item.equals(result.toString()); and so on hasn't worked out.

Answer 1

Answer

It's your

Item item = new Item();

line. That says: "initialise an Item with NOTHING!" So of course you have empty objects. It should be:

Item item = new Item(result.getInt(0), result.getString(1), result.getString(2));

Or whatever column names you gave them in the SQL table.

---

Side note

It's bad practice to allow invalid objects. You shouldn't HAVE a default constructor for your Item object, because there should never be an Item object with invalid data. Trust me when I say it makes everything easier in the long run.

https://softwareengineering.stackexchange.com/questions/261585/should-a-getter-throw-an-exception-if-its-object-has-invalid-state

---

Experimental solution

If you must use the SQL columns automatically (without having to specify each column), it's possible using reflection, but it's fairly brittle. It only works if the following is satisfied:

• The property names on the Java object are exactly the same as the SQL columns
• The property types on the Java object are the default types from ResultSet (primitives to Wrapper classes are ok)

The reflection part can be put into a base class to be extended elsewhere. Here I've called it SimpleSQL. (This has the distinct feeling that I'm being too clever with this answer. I take no responsibility for this code; it's purely being presented as a "you could do this" scenario)

import java.lang.reflect.*;
import java.sql.ResultSet;

public class SimpleSQL {

    public SimpleSQL(ResultSet result) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {

        for (Field f: getClass().getDeclaredFields()) {
            // Get the field type name, converting the first character to uppercase
            String fTypNam = f.getType().getSimpleName();
            fTypNam = fTypNam.substring(0,1).toUpperCase() + fTypNam.substring(1);

            // Get an object that represents each of the methods we want to call
            Method getM = result.getClass().getMethod("get" + fTypNam, String.class);
            Method setM = f.getClass().getMethod("set", Object.class, Object.class);

            // Set the property of this object using the field object's set
            //  and the ResultSet's get.
            setM.invoke(f, this, getM.invoke(result, f.getName()));
            // For a hypothetical 'id' field that is an int, this is equivalent to:
            //   this.id = result.getInt("id");
        }

    }

    @Override
    public String toString() {
        String result = getClass().getName()+"[";
        try {
            Field[] fields = getClass().getDeclaredFields();
            result += fields[0].getName() + ": " + fields[0].get(this);
            for(int i = 1; i < fields.length; ++i) {
                result += ", " + fields[i].getName() + ": " + fields[i].get(this);
            }
        } catch(IllegalAccessException e) {
            // This happens if the field is private.
            throw new RuntimeException(e.getMessage());
        }
        result += "]";
        return result;
    }
}

(see more on toString and reflection here. tl;dr, the Apache Commons library provides a ReflectionToStringBuilder class for a better version of my toString())

Then you can just define your Item as:

import java.lang.reflect.*;
import java.sql.*;

public class Item extends SimpleSQL {
    protected int id;
    protected String name;
    protected String description;

    public Item(ResultSet result) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        super(result);
    }
}

Answer 2

Your Item class should have getters(), setters(), constructor(), and the toString() methode like this:

public class Item {

    private int id;
    private String name;
    private String type;

    public Item(int id, String name, String type) {
        this.id = id;
        this.name = name;
        this.type = type;
    }

    //...Getters and Setters

    @Override
    public String toString() {
        return "Item [id=" + id + ", name=" + name + ", type=" + type + "]";
    }
}

So after that you should to create an object like this:

while (result.next()) {
    Item item = new Item(result.getInt("id"), result.getString("name"), result.getString("type"));
    item.toString();
}

Hope this can help you.