CODEWITHSUNDEEP
javamysqljdbc
bbnn
bbnn

Reputation: 3602

How to fetch MySQL data without creating Object from Class in Java

I want to fetch data from MySQL without creating object from class

Normally I do something like 

public ArrayList getInventoryByItemId(String ItemId) throws SQLException {
    ArrayList list = new ArrayList<Inventory>();
    String sql = "SELECT iid, i.uid, item_data, item_id, i.ctime, username, gender FROM Inventory i JOIN user u ON i.uid = u.uid WHERE item_id = '"+ItemId+"'";
    Statement stmt = con.createStatement();
    ResultSet rset = stmt.executeQuery(sql);
    while (rset.next()) {
        a = new Inventory(rset.getInt(1), rset.getInt(2), rset.getString(3), rset.getString(4), rset.getTimestamp(6), rset.getString(7), rset.getString(8));
        list.add(a);
    }
    return list;
}

the problem is because Inventory object does not have user data from the joined user table, I cannot create new Inventory.

I just want to automatically make an object where it has all the data attributes, that I can access using the column name.

Thank You

Upvotes: 0

Views: 549

Answers (2)

Mr. Will
Mr. Will

Reputation: 2308

Based on what you are saying, I take it that your query is not returning data because the inventory does not have the user data that it is being joined with. You need to modify your query to use a left outer join.

String sql = "SELECT iid, i.uid, item_data, item_id, i.ctime, username, gender FROM Inventory i LEFT JOIN user u ON i.uid = u.uid WHERE item_id = '"+ItemId+"'"

This will allow your query to return Inventory data even if the corresponding User data does not exist.

Upvotes: 0

narek.gevorgyan
narek.gevorgyan

Reputation: 4185

If I got you problem,

You can create new map(HashMap I reccomend) and put values using column name or index as key. So, your list will be list of maps.

 while (rset.next()) {
        a = new HashMap<Integer,Object>();
        a.put(1,rset.getInt(1));
        ..........
        list.add(a);
    }

Or, If you know exact number of columns, you can user array instead of Map (it will be faster)

Upvotes: 2

Related Questions