4 Replies Latest reply: Apr 2, 2013 6:51 PM by 1000389 RSS

    Getting data from a record set

    1000389

      Hi everyone,

      My apologies if this has been answered before but I'm finding it difficult to find an answer online so thought I would ask. I'm pretty new to Java development, but have several years .Net development behind me, and have a question regarding getting data from a resultset once its pulled from MS SQL in an efficient manner.

      I've created a sample db to test connecting to SQL, called tblTeams and has the column names team_id, team_name, team_principal, driver_one, driver_two and have managed to populate my resultset fine from MS SQL 2008, and am looking at outputting the data using println. Looking online I can do this using a while loop as below:

      while (rs.next()) {
      System.out.print(rs.getString("team_name")+ "\t");
      System.out.print(rs.getString("team_principal")+ "\t");
      System.out.print(rs.getString("driver_one")+ "\t");
      System.out.println(rs.getString("driver_two"));
      }

      My question is, is there any way I can pull the data row by row instead of doing each data item one by one per column, and then just use println to print out each row?

      Something along the following is what I'm looking for if it helps describe what I'm after:

      while (rs.next()) {
      System.out.print(rs.getRow(row) + "\t");
      }

      Thanks

      Lee

        • 1. Re: Getting data from a record set
          gimbal2
          Since you've been using .NET you're probably more familiar with mapping rows to objects directly - under Java you have the Java Persistence API (JPA) that can do this for you. Low level JDBC is really that - low level, its not designed to make your life easy.
          • 2. Re: Getting data from a record set
            rp0428
            >
            My question is, is there any way I can pull the data row by row instead of doing each data item one by one per column, and then just use println to print out each row?
            >
            A ResultSet gives you data 'row by row'. Each row is an array of columns. What you do with that array is up to you but no, there is no 'print' that will print the array of columns for a row.

            The closest you can come is to define a SQL type with a structure that maps to the table row and then query the data as instances of that type.

            See page 13-13 in the JDBC Dev Guide.
            http://docs.oracle.com/cd/B28359_01/java.111/b31224.pdf
            >
            Retrieving SQLData Objects from a Callable Statement OUT Parameter
            Consider you have an OracleCallableStatement instance, ocs, that calls a
            PL/SQL function GETEMPLOYEE. The program passes an employee number to the
            function. The function returns the corresponding Employee object.
            >
            Sample type and query to load it
            -- type to match emp record
            create or replace type emp_scalar_type as object
              (EMPNO NUMBER(4) ,
               ENAME VARCHAR2(10),
               JOB VARCHAR2(9),
               MGR NUMBER(4),
               HIREDATE DATE,
               SAL NUMBER(7, 2),
               COMM NUMBER(7, 2),
               DEPTNO NUMBER(2)
              )
              /
             
            -- query that creates an instance of emp_scalar_type for each row
            SELECT emp_scalar_type( empno, ename, job, mgr, hiredate, sal, comm, deptno ) FROM emp;
            That query returns one instance of the type for each row. Each instance has attributes for each column. You could then use the code like the sample from the JDBC doc to do what you wanted with the individual pieces. But there is still no way to 'print' it all at once.
            • 3. Re: Getting data from a record set
              Joe Weinstein-Oracle
              You can 'un-relationalize' the row at the DBMS, by doing the SQL query, telling the DBMS to convert each column value to a string and to concatenate them into one string to return.
              eg:

              "select mycol1 + ", " + mycol2 + ", " + mycol3 from myTable"

              that should return a result set with one row per table row, but with one column
              that you would get via rs.getString(1), and would allow you to 'print the whole row'.
              • 4. Re: Getting data from a record set
                1000389
                Thanks everyone for the replies and answering my query, and for saving me more time than searching for something that doesn't exist (no wonder I found it difficult!). I'll look into the alternative methods that you have mentioned to see what works best.