new to jdbc!

S

shya

hi there!
I'm new to jdbc programming, and I can't find an answer for my problem.
Take for example this bunch of code:

protected void executeQuery( Connection con, String sqlStatement )
throws Exception {

try {
Statement s = con.createStatement ( );
ResultSet rs = s.executeQuery( sqlStatement );

while ( rs.next ( ) ) {
String id = ( rs.getObject ("id").toString() );
String text = ( rs.getObject ("text").toString() );

System.out.println ( "found record : " + id + " " + text );
}

rs.close ( );

} catch ( SQLException e ) {
System.out.println ( "Error executing sql statement" );
throw ( e );
}
}

well, in this query, it retrives the attributes "id" and "text" from the
result set. That's ok. But, how could I retrive _all_ the attributes in
a particular ResultSet without know them before?
For instance, my executeQuery method should be perform query like:

SELECT NAME, SURNAME
FROM TABLE1
WHERE etc...

or

SELECT *
FROM TABLE1
WHERe ...

can you help me?
thanks!
 
C

Christian Dillinger

shya said:
hi there!
I'm new to jdbc programming, and I can't find an answer for my problem.
Take for example this bunch of code:

protected void executeQuery( Connection con, String sqlStatement )
throws Exception {

try {
Statement s = con.createStatement ( );
ResultSet rs = s.executeQuery( sqlStatement );

while ( rs.next ( ) ) {
String id = ( rs.getObject ("id").toString() );
String text = ( rs.getObject ("text").toString() );

System.out.println ( "found record : " + id + " " + text );
}

rs.close ( );

} catch ( SQLException e ) {
System.out.println ( "Error executing sql statement" );
throw ( e );
}
}

You should close your ResultSet and your Statement (!) in a finally-block.
well, in this query, it retrives the attributes "id" and "text" from the
result set. That's ok. But, how could I retrive _all_ the attributes in
a particular ResultSet without know them before?
For instance, my executeQuery method should be perform query like:

SELECT NAME, SURNAME
FROM TABLE1
WHERE etc...

or

SELECT *
FROM TABLE1
WHERe ...

can you help me?
thanks!

You can use ResultSetMetaData to retrieve the number of columns.
getColumnLabel(int index) gives you the name of that column. Hint:
columns in ResultSets are one-based not zero-based!

ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
....

HTH
 
R

Raymond DeCampo

Christian said:
You should close your ResultSet and your Statement (!) in a finally-block.


You can use ResultSetMetaData to retrieve the number of columns.
getColumnLabel(int index) gives you the name of that column. Hint:
columns in ResultSets are one-based not zero-based!

ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
...

HTH

This is good advice, especially concerning the finally block.

I would add another: this kind of method is not as useful as you might
think. Also, if you are working with other programmers, they will
likely prefer to use JDBC directly, which they are familiar with, rather
than use your wrapper methods.

Just my 2 cents.

(If this was just example or proof of concept code, please ignore my
rantings.)

Ray
 
J

Juha Laiho

shya said:
I'm new to jdbc programming, and I can't find an answer for my problem.
Take for example this bunch of code:

protected void executeQuery( Connection con, String sqlStatement )
throws Exception { ....

System.out.println ( "found record : " + id + " " + text ); ....
}

As Mr DeCampo wrote, this kind of utility routines are, after all,
more hindrance than help -- they don't provide a useful abstraction
over the base JDBC functionality. Some problems I've seen in
"generic database helpers" include hiding/losing detailed error
information, leaking connection and/or statement objects,
prohibiting uses of some SQL constructs, and the helper code itself
being an additional source of errors.

In real code I don't think just printing out the values (esp. to
some fixed descriptor like System.out) would be suitable for all
users of the routine -- so, rather than printing, this should
return some data structure suitable for the data fetched from
the database (which is troublesome as you don't know anything about
the structure bweforehand). Also, as this piece of code is declared
to throw 'Exception' (instead of just picking the relevant Exception
subclasses), it'll make error handling in the calling routines
a pain (anything calling this will either have to catch Exception,
or be declared to throw Exception - propagating the problem upward
in the call tree).

There are enough generic JDBC wrappers to not write another one;
these are called persistence frameworks; see iBatis for one
(http://ibatis.apache.org/). These are useful in some contexts,
and may make a nice separation between database interaction and
application logic code -- but definitely writing another
framework like this is not for the faint of heart. On the other
hand, it takes some time to learn to use this kind of framework.

The other useful way of wrapping JDBC is to get rid of generalization,
and just provide meaningful, single-purpose methods to wrap the
(percieved?) database interaction complexity. So, for example,
you could write a class called "CustomerDb", with methods
fetch(CustId id)
fetch(String name)
both returning an instance of Customer object, with appropriate
information filled in. Depending on your application, the error
case of customer not fould would be indicating by returning null
or a NullCustomer object (see the NullObject pattern), or by
throwing an appropriate application-specific exception. These
methods would then hide all database interaction from the
calling routines: no SQL statements to pass, no parsing of the
returned rowset structures, ... . Just indicate what the calling
routine needs, and in return get either the desired data (in
object form suitable for the business logic), or some indication
that the data was not available.
 
L

Lothar Kimmeringer

well, in this query, it retrives the attributes "id" and "text" from the
result set. That's ok. But, how could I retrive _all_ the attributes in
a particular ResultSet without know them before?

With the get-methods with the int-parameter.

Read the API, there you can find the more detailed descriptions.


Regards, Lothar
--
Lothar Kimmeringer E-Mail: (e-mail address removed)
PGP-encrypted mails preferred (Key-ID: 0x8BC3CD81)

Always remember: The answer is forty-two, there can only be wrong
questions!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,902
Latest member
Elena68X5

Latest Threads

Top