9 Replies Latest reply: May 1, 2012 4:53 PM by jschellSomeoneStoleMyAlias RSS

    Create an Encapsulation of Select * Statements?

    933309

      I'm looking to encapsulate a mysql db call that returns a large result set due to Select *. I prefer to return the results and let the calling routine parse (or display) the data. Unfortunately most of the methods I've tried -- multidimensional arrays, objects, returning the result set itself -- I have either run in to issues or the solution was less than ideal. I've posted some code below.

      If anyone could suggest an excellent method for handling this issue it would be appreciated. Sample code would be even better!

      Thanks in advance.





      ---------------------------------------

      try{
      Statement st = con.createStatement();
      /* ResultSet res = st.executeQuery("SELECT * FROM MyTable"); */
      ResultSet rs = st.executeQuery(SQLQuery);


      ResultSetMetaData rsmd = rs.getMetaData();
      int numberOfColumns = rsmd.getColumnCount() + 1;
      RowSetDynaClass rsdc = new RowSetDynaClass(rs);
      List rowset = rsdc.getRows();
      int rowcount = rowset.size() + 1;

      Object[][] arr = new Object[rowcount][numberOfColumns];
      for (int i = 1; i < rowcount; i++) {
      /* read row */
      rs.absolute(i);
      for (int j = 1; j < numberOfColumns; j++){
      /* select column from row */
      arr[i][j] = rs.getString(j);
      /* System.out.println("Value arr: " + arr[i][j]); */


      }
      }

      con.close();
      return arr;

        • 1. Re: Create an Encapsulation of Select * Statements?
          EJP
          Return a Map[] where the keys are column names, the values are column values, and the indexes are row numbers.

          If you're in JSF or JSP land you can use javax.servlet.jsp.jstl.sql.Result.
          • 2. Re: Create an Encapsulation of Select * Statements?
            933309
            Well, this is where I cry java inexperience. I've done some research on maps but I am a but confused about how to make sure 1) the object is returned to the calling routine and 2) to make it multidimensional. Could you provide a quick sample? I should also add that the columns are not known ahead of time.

            Thanks.

            PS - I'm just in regular java land.

            Edited by: 930306 on Apr 25, 2012 8:52 PM

            Edited by: 930306 on Apr 25, 2012 8:54 PM
            • 3. Re: Create an Encapsulation of Select * Statements?
              EJP
              I don't often do this but here's some code:
              PreparedStatement ps;  // ...
              // etc
              ResultSet rs = ps.executeQuery();
              ResultSetMetaData rsmd = rs.getMetaData();
              int columns = rsmd.getColumnCount();
              @SuppressWarnings("unchecked")
              Map<String, Object>[] result = new Map[columns];
              int row = 0;
              while (rs.hasNext())
              {
                result[row] = new HashMap<String, Object>();
                for (int col = 1; col <= columns; col++)
                {
                  result[row].put(rsmd.getColumnName(col), rs.getObject(col));
                }
                row++;
              }
              return result;
              • 4. Re: Create an Encapsulation of Select * Statements?
                933309
                Thank you very much for the code. Instead of cut and paste it leads me to three other questions. Perhaps this will show my ignorance of Map[] and Java but so be it.

                Correct me if I'm wrong but my research leads me to believe that what we are creating is an array object in memory and that Java will assign keys to the array elements. By using HashMap and Map we can create a multidimensional array object that can be read later. In your code we create an array of 5 columns.

                First off, why store the column name in the object (repetitively)? I suppose if one were to look into memory, the table of the array would look slightly different than an open office spreadsheet since the column name is stored in each row and we are not assuming the same data type based on position. Theoretically speaking, is the column name necessary?

                Second, how do you pass it back to the calling routine. My Map declaration does not appear to be declared properly. Eclipse keeps throwing an error.

                Finally, upon return, since you have an object that does not have too many methods associated with it, how the hell do you read through it and parse the data? Unlike result set it does not have a method for know where you're at in the table. Also, when reading through the map [] perhaps this is where the column names come into play? That is the calling routine retrieve the value of the cell via row and colname. Am I close?


                Thanks in advance. I hope these are good questions. I admit it, I'm a newbie when it comes to Java.
                • 5. Re: Create an Encapsulation of Select * Statements?
                  EJP
                  Correct me if I'm wrong but my research leads me to believe that what we are creating is an array object in memory
                  Yes. (Research!)
                  and that Java will assign keys to the array elements.
                  No. The code I posted assigns Maps to the array elements.
                  In your code we create an array of 5 columns.
                  We create an array of N rows where N is the number of rows in the ResultSet. Each row contains M columns where M is the number of columns in each row.
                  First off, why store the column name in the object (repetitively)?
                  So that you can get the value back by using the column name just as you can with the ResultSet itself. It isn't compulsory, you could just store an Object if you think the calling code is going to know the colums by index instead of name.
                  Second, how do you pass it back to the calling routine.
                  With a return statement. How much Java do you know exactly?
                  My Map declaration does not appear to be declared properly. Eclipse keeps throwing an error.
                  No. Eclipse keeps reporting a compiler error. But if you're not going to tell us what it is you can't get any help about it, can you? The code compiled for me so you must have done something wrong in transcription.
                  Finally, upon return, since you have an object that does not have too many methods associated with it, how the hell do you read through it and parse the data?
                  It's an array. You iterate over it.
                  Unlike result set it does not have a method for know where you're at in the table.
                  It has indexes.
                  Also, when reading through the map [] perhaps this is where the column names come into play? That is the calling routine retrieve the value of the cell via row and colname. Am I close?
                  Let's just say no cigar.

                  All this merely confirms me in the basic idiocy of posting code. I suggest you seek some help from closer at hand, such as a colleague. If you can't understand eight or so lines of perfectly straightforward Java, you are just wasting time trying to sort it out on a forum. You're also wasting time trying to program JDBC when you dont' even understand two of the most fundamental data structures in the language.
                  • 6. Re: Create an Encapsulation of Select * Statements?
                    933309
                    I really don't want to get into a flame war here but I would like to clarify a bit, esp since what I wrote was before my morning coffee. I've been getting very little sleep these last few days and I'm sure that what I wrote did not come out 100% right. I'll post some code a bit later on today.
                    In your code we create an array of 5 columns.
                    We create an array of N rows where N is the number of rows in the ResultSet. Each row contains M columns where M is the number of columns in each row.
                    According to the code you posted, we create the column object first then assign rows to it. Hence we create an array of 5 columns, then we add rows filling each column with data. We do not declare the number of rows of the resultset because this is not known in your code before the while loop: we create new object rows per while iteration. Not the other way around as per your reply, technically speaking. No mystery here. Not sure why you jumped on that.
                    With a return statement. How much Java do you know exactly?
                    Of course. It's not the return but is in the declaration of the class that was/is the issue.
                    It's an array. You iterate over it.
                    Sure. But it's a manual process since there isn't a method associated with it. Naturally I could create a for loop** but I thought you might have a better method, esp about how to know the number of row and column elements before looping through.
                    All this merely confirms me in the basic idiocy of posting code.
                    I disagree. I've certainly improved my knowledge of Java by having an issue, seeing code that could fix it, analyzing that code, making mistakes and trying to get things fixed. The way I see it it's called learning.
                    If you can't understand eight or so lines of perfectly straightforward Java, you are just wasting time trying to sort it out on a forum.
                    A little harsh. I'm just unfamiliar with Maps and I am by certainly no means an expert. Let's say advanced beginner to intermediate. I think I admitted it while writing my post. I do appreciate your help though. Hope we can continue this when I post some code later today.

                    ** or do you mean use the iterator class object?

                    Edited by: 930306 on Apr 26, 2012 7:05 AM
                    • 7. Re: Create an Encapsulation of Select * Statements?
                      933309
                      OK. Fixed the issue. Perhaps it is my coding marathon that had me overlook this issue. I think it was one of those situations where I would fix one bug and create another. I'll post the final code at the latest this weekend. Thanks EJP. Even though I didn't use your solution it brought me to understand where there were bugs in mine. Cheers.
                      • 8. Re: Create an Encapsulation of Select * Statements?
                        933309
                        As promised in an earlier in the thread, here is my working code. It'll dynamically create an array, fill it with data and pass it back to the calling routine. Good for any select statement but I would refrain from ones that select too much data.... It's good for my project but may not be great for yours..... Enjoy....
                        import java.sql.Connection;
                        import java.sql.DriverManager;
                        import java.sql.ResultSet;
                        import java.sql.ResultSetMetaData;
                        import java.sql.SQLException;
                        import java.sql.Statement;
                        import java.util.List;
                        import org.apache.commons.beanutils.RowSetDynaClass;
                        
                        /* need to put a class here!!! */
                        
                        public static void main(String[] args) throws SQLException {
                                  
                                  String SQLquery = "SELECT * FROM MyTable;";
                                  
                        
                                  Object[][] rs = DBConnect.connectSELECT(SQLquery); 
                                  
                                  int rows = rs.length;
                                  int cols=0;
                        
                                  boolean flag = true;
                                  
                                  /* here we get column dimensions of array */
                                  while (flag) {
                                       try {
                                       System.out.println("Value arr: " + rs[1][cols++]);
                                       } catch (IndexOutOfBoundsException kenb){
                                            flag = false;
                                            
                                            }
                                  }
                        
                                  /* here we print out table for old times sake */
                                  
                                  for (int i = 1; i < rows; i++) {
                                            for (int j = 1; j < cols -1; j++){
                                                 System.out.println("Value rs: " + rs[i][j]); 
                                                 }
                                       }
                        
                             }
                        
                        }
                        
                        
                        public static Object[][] connectSELECT(String SQLQuery){
                                  
                                            
                                  System.out.println(SQLQuery);
                                    Connection con = null;
                               
                                    String url = "jdbc:mysql://localhost:3306/";
                                    String db = "MyDB";
                                    String driver = "com.mysql.jdbc.Driver";
                                    String user = "UserID";
                                    String pass = "UserPWD";
                                    
                                    
                                    try{
                                              Class.forName(driver).newInstance();
                                              con = DriverManager.getConnection(url+db, user, pass);
                                                   try{
                                                        Statement st = con.createStatement(); 
                                                        ResultSet rs = st.executeQuery(SQLQuery);
                        
                        
                                                        ResultSetMetaData rsmd = rs.getMetaData();
                                                        int numberOfColumns = rsmd.getColumnCount() + 1;
                                                        RowSetDynaClass rsdc = new RowSetDynaClass(rs);
                        
                                                        List rowset = rsdc.getRows();
                                                        int rowcount = rowset.size() +1;
                                                        
                                                      System.out.println("Rows =" + rowcount + " - Cols =" + numberOfColumns);
                        
                                                        Object[][] arr = new Object[rowcount][numberOfColumns];
                        
                                                        for (int i = 1; i < rowcount; i++) {
                                                             /* read row */
                                                             rs.absolute(i);
                                                             for (int j = 1; j < numberOfColumns; j++){
                                                                       /* select column from row */
                                                                  arr[i][j] = rs.getString(j);
                                                                  System.out.println("Value arr: " + arr[i][j] + " - i="+i + " j="+j);
                        
                                                                  }
                                                        }
                        
                                                        con.close();
                                                        return arr;
                        
                                                        
                                                        
                                                        
                                                   }
                                                   catch (SQLException s){
                                                        System.out.println("SQL code does not execute: " + s);
                                                        /* on error return null */
                                                   }  
                                    }
                                    catch (Exception e){
                                         e.printStackTrace();
                               }
                                  return null;
                             }
                        • 9. Re: Create an Encapsulation of Select * Statements?
                          jschellSomeoneStoleMyAlias
                          kbnyc wrote:
                          I'm looking to encapsulate a mysql db call that returns a large result set due to Select *. I prefer to return the results and let the calling routine parse (or display ) the data.
                          Hopefully that "large" is actually rather small.