Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 4 of 4

Thread: JDBC - any query and the corresponding result set

  1. #1
    Junior Member
    Join Date
    Oct 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default JDBC - any query and the corresponding result set

    I am having difficulty outputting the result set after a user input query. I need the user to be able to input any SQL query and print out the result set with field names and corresponding data. I can run any query but I am having trouble with the output, it stops after just one row (when I know there are several). Here is what I have:
    public void sqlQuery1(String query) {
    		System.out.println("Enter you sql query:");
    		this.query = scan.nextLine();
     
    		Connection conn = getConnection();
            Statement stmt = null;
            try {
            	stmt = conn.createStatement();
            	boolean resultsAvailable = stmt.execute(this.query);
                if(resultsAvailable){
                    //query is a select query.
                	ResultSet rs = stmt.getResultSet();
                    int i = 1;
                	while(rs.next()){
                        System.out.print(rs.getString(i) + "\t");
     
                        i++;
                    }
                } else {
                    //query can be update or any query apart from select query
                    int count = stmt.getUpdateCount();
                    System.out.println("Total records updated: " + count);
                }
    		} catch (SQLException ex) {
    			System.out.println("SQLException: " + ex.getMessage());
    			System.out.println("SQLState: " + ex.getSQLState());
    			System.out.println("VendorError: " + ex.getErrorCode());
    		} catch (Exception e) {
                System.out.println(e.toString());
            }finally {
                try {
                    if(stmt!=null)
                       stmt.close();
                 } catch(SQLException se2) {
                 try {
                    if(conn!=null)
                       conn.close();
                 } catch(SQLException se) {
                    se.printStackTrace();
                 }
     
                 }
     
            }
            System.out.println();
            System.out.println();
    }


  2. #2
    Super Moderator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,104
    Thanks
    169
    Thanked 780 Times in 726 Posts
    Blog Entries
    5

    Default Re: JDBC - any query and the corresponding result set

    it stops after just one row
    Does it throw an Exception? Is there a reason why are you incrementing the i variable in the loop in which you iterate over the ResultSet? FWIW, while i don't know the nature of what you are building, you might want a bit more restraint on the query, for instance a user could drop your tables if there isn't such a permission constraint

  3. #3
    Junior Member
    Join Date
    Oct 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: JDBC - any query and the corresponding result set

    No exceptions, it just stops after printing the first employees information (name, ssn, address, etc., there are 9 columns in all). Without the i loop it only printed out the first column. Actually after further inspection of the database it actually prints out the first column for the first row, the second column for the second row and so on. I am building a simple app for homework (I have finished the entirety of it except for printing out the result set for this method) so the security of it is not really an issue. I just can't figure out how to iterate through a result set with an unknown number of results.

    Here is the actual question:

    a method that will take a String query as its paramter and return the result of the query. The query can be ANY SQL select statement (you may assume that it will be select statement). In the result it should show the column names followed by the result. Note that since the method should work for any query, e.g select * from EMPLOYEE, select dno from EMPLOYEE, you need to write the code flexible enough so that it will print out the column names instead of hardcoding the column names.

  4. #4
    Junior Member
    Join Date
    Oct 2011
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: JDBC - any query and the corresponding result set

    I actually got it to work. Here's the final code:
    public void sqlQuery1(String query) {
    		System.out.println("Enter you sql query:");
    		this.query = scan.nextLine();
     
    		Connection conn = getConnection();
            Statement stmt = null;
            try {
            	stmt = conn.createStatement();
            	boolean notDone = true;
     
            	while(notDone) {
     
            			ResultSet rs = stmt.executeQuery(this.query);
            			ResultSetMetaData rsmd = rs.getMetaData();
            			int columnCount = rsmd.getColumnCount();
     
            			for(int i = 1; i <= columnCount; i++) {
            				int columnWidth = rsmd.getColumnDisplaySize(i) + 5;
                			System.out.format("%-" + columnWidth + "s", rsmd.getColumnName(i));
            			} //for
            			System.out.println();
            			while(rs.next()) {
            				for(int i = 1; i <= columnCount; i++) {
            					int columnWidth = rsmd.getColumnDisplaySize(i) + 5;
            					System.out.format("%-" + columnWidth + "s", rs.getString(i));
     
            				} //for
            				System.out.println();
            			} //rs.next while
            			notDone = false;
     
            	} //notDone while
            } catch (SQLException ex) {
    			System.out.println("SQLException: " + ex.getMessage());
    			System.out.println("SQLState: " + ex.getSQLState());
    			System.out.println("VendorError: " + ex.getErrorCode());
    		} catch (Exception e) {
                System.out.println(e.toString());
            }finally {
                try {
                    if(stmt!=null)
                       stmt.close();
                 } catch(SQLException se2) {
                 try {
                    if(conn!=null)
                       conn.close();
                 } catch(SQLException se) {
                    se.printStackTrace();
                 } //last catch
            } //finally
            } //original try
            System.out.println();
            System.out.println();
    	}

Similar Threads

  1. Result set is closed
    By keshav_agrawal89 in forum What's Wrong With My Code?
    Replies: 6
    Last Post: June 11th, 2011, 08:35 AM
  2. Replies: 1
    Last Post: April 25th, 2011, 04:06 PM
  3. Problem with accessing result set with postgresql
    By vrp in forum JDBC & Databases
    Replies: 2
    Last Post: December 23rd, 2010, 06:10 AM
  4. Problem with accessing result set with postgresql
    By vrp in forum Member Introductions
    Replies: 2
    Last Post: December 23rd, 2010, 06:10 AM
  5. result set array button
    By dread_arisawa in forum What's Wrong With My Code?
    Replies: 2
    Last Post: August 21st, 2010, 10:05 AM

Tags for this Thread