SQL - how to return multiple rows with one SQL query?

Question

I have a managed bean which makes SQL queries to Oracle database. This is just very simple example how I make SQL queries. This is the table structure:

GLOBALSETTINGS
---------------------------------
SessionTTL          VARCHAR2(40 BYTE)
MAXACTIVEUSERS  NUMBER
ACTIVEUSERS         VARCHAR2(20 BYTE)

I use this table just to store application settings. In the example listed below I can fetch just one string with one SQL statement. I want with SQL query to fetch the content of the three rows - SessionTTL, MAXACTIVEUSERS, ACTIVEUSERS. Is it possible?

   public String CheckUserDB(String userToCheck) throws SQLException {
        String storedPassword = null;        
        String SQL_Statement = null;

        if (ds == null) throw new SQLException();      
   Connection conn = ds.getConnection();
        if (conn == null) throw new SQLException();      

   try {
        conn.setAutoCommit(false);
        boolean committed = false;
            try {
                   SQL_Statement = "SELECT Passwd from USERS WHERE Username = ?";

                   PreparedStatement passwordQuery = conn.prepareStatement(SQL_Statement);
                   passwordQuery.setString(1, userToCheck);

                   ResultSet result = passwordQuery.executeQuery();

                   if(result.next()){
                        storedPassword = result.getString("Passwd");
                   }

                   conn.commit();
                   committed = true;
             } finally {
                   if (!committed) conn.rollback();
                   }
        }
            finally {               
            conn.close();

            }  

   return storedPassword;       
   }

P.S I want the content of the rows.

Perhaps cutting and pasting from an example? Makes sense on an INSERT, UPDATE, etc. but superfluous at best (it's possible there could be an uncommitted SQL statement for this session out there!) on a SELECT. — David Faber
– David Faber, Commented Mar 8, 2012 at 15:07
@Viruzzo: a SELECT statement does start a transaction, so it makes sense to end that transaction as well (some DBMS actually require you to also explictely end "read-only" transactions). Although I agree in this example (and with Oracle) it is not really needed, but it doesn't harm either. — user330315
– user330315, Commented Mar 8, 2012 at 15:17
@user1103606: you want to fetch mutliple columns not multiple rows. — user330315
– user330315, Commented Mar 8, 2012 at 15:18
@DavidFaber but then committing/rolling back would be even worse. — Viruzzo
– Viruzzo, Commented Mar 8, 2012 at 15:41

Sam Holder · Accepted Answer · 2012-03-08 15:04:01Z

3

I'm hoping I understand what you are asking for, but I fear I don't as it seems too simple, but anyway...

I think you want the contents of 3 columns, not rows. And yes you can, you just specify the columns you want returned in your SQL statement:

SELECT SessionTTL, MAXACTIVEUSERS, ACTIVEUSERS FROM GLOBALSETTINGS WHERE (condition)...

you can also use * as a shortcut for all columns iof you don't want to explicitly specify them:

SELECT * FROM GLOBALSETTINGS WHERE (condition)...

Some background reading on SQL syntax might be useful

edited Mar 8, 2012 at 15:04

answered Mar 8, 2012 at 14:58

Sam Holder

33.1k14 gold badges108 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ben Sewards · Accepted Answer · 2012-03-08 15:03:15Z

1

If I read this correctly (sorry if mistaken), all you want to do is change your SQL command to select ALL COLUMNS in your database table.

To do so:

string SqlAll = @"SELECT Database.SessionTTL, Database.MAXACTIVEUSERS, Database.ACTIVEUSERS FROM Database";

This will retrieve ALL columns in the database. You can also have conditional statements in your queries when you want to filter for logical reasons, such as TOP 20 to get the first 20 results from the result set.

answered Mar 8, 2012 at 15:03

Ben Sewards

2,6612 gold badges27 silver badges43 bronze badges

Comments

user1210061 · Accepted Answer · 2012-03-08 15:14:45Z

1

If you like to return multiple lines with one sql query, you may want to look into ArrayList as you need a loop, where the code would go through your records and match and find all possible results until the end of the records list.

answered Mar 8, 2012 at 15:14

user1210061

Collectives™ on Stack Overflow

SQL - how to return multiple rows with one SQL query?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related