Difference in SQLite and SQLite3 query result

[KAILEY]

For a project, I am trying to read a table containing transactions. I use the following code:

/** 
     * Import pending transactions records from the database 
     * @return HashMap containing deposit and associated account 
     */
    public HashMap<Deposit, Account> importPendingTransactions() {
        HashMap<Deposit, Account> pendingTransactions = new HashMap<Deposit, Account>();
        Statement stat = null;
        try {
            stat = dbConnection.createStatement();
            ResultSet transactionRS = stat.executeQuery("SELECT * FROM " + PENDING_TRANSACTION_TABLE + ";" );
            while (transactionRS.next()) {
                // Convert SQL Date to Java one. 
                Date transactionDate = new Date(transactionRS.getDate(TRANSACTION_DATE).getTime());
                Deposit depositTransaction = new Deposit(transactionRS.getFloat(TRANSACTION_AMOUNT), transactionDate);
                ResultSet accountRs = stat.executeQuery("SELECT * FROM " + ACCOUNT_TABLE + " WHERE " + ACCOUNT_ID + "=" + transactionRS.getString(TRANSACTION_ACCOUNT) + ";" );
                Account userAccount = null;
                while (accountRs.next()) {
                    userAccount = new Account(accountRs.getInt(ACCOUNT_ID), accountRs.getString(ACCOUNT_NUMBER), Account.ACCOUNT_TYPE.CREDITCARD_ACCOUNT, accountRs.getFloat(ACCOUNT_BALANCE));
                }
                accountRs.close();
                if (userAccount != null) {
                    // Add transaction & account couple to the hashmap. 
                    pendingTransactions.put(depositTransaction, userAccount);
                }
            }
            // Close Accounts Result Set 
            transactionRS.close();
        } catch (SQLException e) {
            Logger.getAnonymousLogger().log(Level.SEVERE, "Failed to execute SQL request to import pending transactions: " + e.getLocalizedMessage());
            return null;
        } finally {
            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException ex) {
                    // Ignore exceptions in finally 
                }
            }
        }
        Logger.getAnonymousLogger().log(Level.INFO, pendingTransactions.size() + " pending transactions imported." );
        return pendingTransactions;
    }

My problem is that my SQLite query returns a single line, whereas when I manually browse the table using sqlite3, I have dozens of transactions. How is this possible? Am I only one or it is common?

[!const]

I am not an expert in SQLite, but it would not because you do a 2nd query in the first, from the same Statement of the same connection? The minimum would be to create a new statement for the 2nd query.

But personally I advise you to make a single query with a join table which will be MUCH more efficient. In this way:

SELECT t.date, t.amount, ua.id, ua.number, ua.balance FROM transactions t, user_accounts ua WHERE t.user_account_id = ua.id

[KAILEY]

Why I have not thought about that before! The problem was actually that I used the same statement for both applications. I was really confused after doing all that. And thank you for the advice on the joint. It is a test project so I am just manipulating data, and performance is really in the background

[!const]

Joins is really a minimum SQL, especially in this case where there is a clear need to use them. I thought that you do not understand SQL. And more, it makes your code more simple. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. But please note that JOIN returns rows if and only if there is at least one match in both tables.