CODEWITHSUNDEEP
java
henry434
henry434

Reputation: 107

Storing one column of a database table into an ArrayList

I am writing a Program which tracks the movement of an RFID Tag as it moves between RFID censors. This is mimicking the movement of a wallet between rooms. Each censor will be a door to a different room.

I have a SQLite database which holds the Name of the Card, the Location, and the name of the Tag to differentiate between different cards.

I need an ArrayList which tells me what Tags are currently on the database.

I have a DAO method that displays all the values in a database and stores it in an ArrayList as shown: 

SELECT * FROM Wallets;
[name: Henry, location: 0, tag: 5c00ce6df0, name: jim, location: 0, tag: wallet1]

I am trying to write another DAO method that will just display the tags like this:

SELECT Tag FROM Wallets;
[5c00ce6df0, wallet1]

Once i have these values saved in an ArrayList i intend to pass them into another DAO method which takes the tags and returns all the relevant information, like this:

SELECT * FROM Wallets WHERE Tag = 'wallet1';
name: jim, location: 0, tag: wallet1

Here is the code for my getAllWallets() DAO:

public ArrayList<Wallet> getAllWallets() throws SQLException{
    Connection dbConnection = null;
    Statement statement = null;
    ResultSet resultset = null;
    String query = "SELECT * FROM Wallets;";
    ArrayList<Wallet> list = new ArrayList<>(); 

    try {
        dbConnection = getDBConnection();
        statement = dbConnection.createStatement();
        System.out.println(query);
        // execute SQL query
        resultset = statement.executeQuery(query);

    while (resultset.next()) {
        Wallet w = new Wallet(query, 0, query);

        w.setName(resultset.getString("Name"));
        w.setLocation(resultset.getInt("Location"));
        w.setTag(resultset.getString("Tag"));
        list.add(w);
        }   
    } 

    catch (SQLException e) {
        System.out.println(e.getMessage());
    } 
    finally 
    {
        resultset.close();
        statement.close();
        dbConnection.close();
        }   
    return list;
    }

Here is what i have tried so far for my getWalletTag() DAO:

public ArrayList<String> getWalletTag() throws SQLException {
    Connection dbConnection = null;
    ResultSet resultset = null;
    Statement statement = null;
    ArrayList<String> list = new ArrayList<String>(); 
    String query = "SELECT Tag FROM Wallets;";

    try {
        dbConnection = getDBConnection();
        statement = dbConnection.createStatement();
        // execute SQL query
        System.out.println(query);
        resultset = statement.executeQuery(query);

        String tag = tag.toString(); // i know this is totally wrong but i got stuck

        list.add(tag);

    }
    catch (SQLException e) {
        System.out.println(e.getMessage());
    }

    finally 
    {
        if (resultset != null) {
        resultset.close();
        }
        if (statement != null) {
        statement.close();
        }
        if (dbConnection != null) {
        dbConnection.close();
        }
    }
    return list;
}

What i am struggling with is how to get all the values of the Tag column into an array list. Is there an easy way to do this? Any help will be greatly appreciated. Thanks.

Upvotes: 1

Views: 3139

Answers (1)

paulsm4
paulsm4

Reputation: 121669

In an "ideal universe", you would probably want to use an ORM (like Hibernate), and a framework (like Spring Boot).

Either or both might be "overkill" for your application.

If your goal is to get a "list of tags", then the code you've got looks OK:

  • Connect to the database.
  • Make a query.
  • Copy the resultset into a list of Java objects, one at a time.
  • Close the DB connection when you're done.

If your goal is to "optimally" find a specific tag (without making another DB query), then perhaps you should use a Java Map or Set instead of an ArrayList.

Should you wish to consider Spring Boot and Sqlite, here are a couple of tutorials:

This part of your code seems good:

public ArrayList<Wallet> getAllWallets() throws SQLException{
   resultset = statement.executeQuery(query);
   while (resultset.next()) {
     Wallet w = new Wallet(query, 0, query);
     w.setName(resultset.getString("Name"));
     w.setLocation(resultset.getInt("Location"));
     w.setTag(resultset.getString("Tag"));
     list.add(w);
   }   
   ...

So one of two choices:

  1. Either forget about getWalletTag(), and just use your wallets to identify tags, or

  2. Use the same query, just save the "tag" column into your array list (instead of anything else).

Option 2:

 public ArrayList<String> getWalletTag() throws SQLException {
   String query = "SELECT Tag FROM Wallets;";
   ...
   resultset = statement.executeQuery(query);
   List<String> tags = new ArrayList<String>();
   while (resultset.next()) {
     tags.add(resultset.getString("Tag"));
     ...

... or ...

     ...
   Set<String> tags = new HashSet<String>();
   while (resultset.next()) {
     tags.add(resultset.getString("Tag"));

Upvotes: 0

Related Questions