Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 24-11-2009
Member
 
Join Date: Aug 2009
Posts: 59
Difference in SQLite and SQLite3 query result

For a project, I am trying to read a table containing transactions. I use the following code:
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?
Reply With Quote
  #2  
Old 24-11-2009
Member
 
Join Date: Nov 2008
Posts: 1,054
Re: Difference in SQLite and SQLite3 query result

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
Reply With Quote
  #3  
Old 24-11-2009
Member
 
Join Date: Aug 2009
Posts: 59
Re: Difference in SQLite and SQLite3 query result

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
Reply With Quote
  #4  
Old 24-11-2009
Member
 
Join Date: Nov 2008
Posts: 1,054
Re: Difference in SQLite and SQLite3 query result

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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Difference in SQLite and SQLite3 query result"
Thread Thread Starter Forum Replies Last Post
The Search Result in Outlook 2007 does not show current years result Shika Windows Software 4 24-04-2012 07:53 AM
Insert sql query result in a file TechGate Software Development 5 21-01-2010 11:52 AM
How to sort the query result in SQL? Owen Fernandes Software Development 5 12-01-2010 08:45 AM
Query about places.sqlite file Adene Operating Systems 5 24-12-2009 06:31 PM


All times are GMT +5.5. The time now is 04:34 AM.