CODEWITHSUNDEEP
javasqljavabeans
Peter Penzov
Peter Penzov

Reputation: 1668

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

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.

Upvotes: 0

Views: 1730

Answers (3)

user1210061
user1210061

Reputation:

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.

Upvotes: 1

Sam Holder
Sam Holder

Reputation: 32936

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

Upvotes: 3

Ben Sewards
Ben Sewards

Reputation: 2661

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.

Upvotes: 1

Related Questions