This discussion is archived
4 Replies Latest reply: Apr 2, 2013 4:51 PM by 1000389 RSS

Getting data from a record set

1000389 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points