Re: jdbc query

2rajesh.b@xxxxxxxxx wrote:
i am working using jdbc prepared statement in our application the below
is the sample code ,i am able to print the query using logger but the
resultset is not getting executed.

> if (etf == null) {
> etf = new EmployerTeamForm();
> }

It's best to check arguments, rather than try to do something random instead.

Does the ResultSet.toString cause the results to be iterated through. ResultSet.first might move the cursor back to the first result.

StringBuffer sb = new StringBuffer();
sb.append("SELECT ");

There is no point in using StringBuffer here. Literal strings will get concatenated at compile time (use javap -c on the class to see what happens).


}
catch (Exception ex) {
throw new ServiceException(this, ex);

It's generally best to catch specific exceptions.

}
finally {
try {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (conn != null)
conn.close();

If rs.close throws an exception, ps and conn will not be closed.

The general form of resource handling is: acquire try { use } finally { release }. So:

ResultSet results = statement.executeQuery();
try {
... use results ...
} finally {
results.close();
}

Only needs a single catch, no null testing and it works.

You can use the execute around idiom to tidy this lot up:

public EmployerTeamForm load(
final EmployerTeam employerTeam
) throws ServiceException {
if (employerTeam == null) {
throw new NullPointerException();
}
return new ExecuteQuery<EmployerTeamForm>() {
protected void init(
PreparedStatement statement
) throws SQLException {
statement.setInt(1, employerTean.getCompanyID());
statement.setInt(2, employerTean.getUserID());
}
protected EmployerTeamForm handle(
ResultSet results
) throws SQLException {
if (!results.next()) {
return null;
}
...
return form;
}
}.executeQuery(
connection,
"..."
);
}

With the non-query specific:

public class ExecuteQuery<T> {
protected abstract void init(
PreparedStatement statement
) throws SQLException;

protected abstract T handle(
ResultSet results
) throws SQLException;

public final T executeQuery(
Connection connection, String sql
) throws ServiceException {
try {
PreparedStatement statement =
connection.prepareStatement(sql);
try {
init(statement);
ResultSet results = statement.executeQuery();
try {
handle(results);
} finally {
results.close();
}
} finally {
statement.close();
}
} catch (SQLException exc) {
throw new ServiceException(exc);
}
}
}

(Disclaimer: Not compiled or tested.)

Tom Hawtin
--
Unemployed English Java programmer
http://jroller.com/page/tackline/
.

Relevant Pages

  • Re: recordset not opening even though query analyzer returns resul
    ... I'm convinced that VB can execute this complex SP. ... > only way i could get an earlier version of it to return in a recordset. ... > table into a cursor and add up th evalues of the ccjs based on values in ... >> execute a query the SQL engine returns a resultset. ...
    (microsoft.public.vb.database.ado)
  • Re: Datenbank in JTable
    ... static JTable getAllCustomers() { ... ResultSet resultSet = null; ... Connection connection = DAToDB.getConnection; ... catch (SQLException sqlex) { ...
    (de.comp.lang.java)
  • Re: sqlCeResultSet readPrevious() method doesnt work always after a readAbsolut(currentPosition)
    ... On the desktop site the application is using MS Access. ... you now call ReadAbsoluteon rs2, a value of -1 is returned. ... When i execute a readPreviousafterwards, the resultset stays at the ...
    (microsoft.public.sqlserver.ce)
  • Re: sqlexception
    ... ResultSet rs = st.executeQuery; ... The calls to getConnection, createStatement and executeQuery can all throw an SQLException, yet your code does not enclose them in a try-catch block nor does the method which calls them declare that it throws an SQLException itself. ... Actually, I'm not sure that it's permitted to have the "main" method declare that it throws exceptions, so your only option is to enclose the calls to JDBC methods inside a try-catch block: ...
    (comp.lang.java.databases)
  • Re: running complete transactions
    ... the resultset is retrieved from the first select statement, ... --into tableB or tableC before these next two select statements have ... I would be grateful if you could tell me if I have understood the API ... then execute a number of ResultSet ...
    (comp.lang.java.databases)