jdbc query

2

2rajesh.b

hi,
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.


public EmployerTeamForm load(EmployerTeamForm etf) throws
ServiceException {

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

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
// Get connection
ConnectionHandler hdlr =
ConnectionManager.getInstance().getHandler();
conn = (Connection)hdlr.getConnection();

// perform SQL...
StringBuffer sb = new StringBuffer();
sb.append("SELECT ");
sb.append("om.UserID, ");
sb.append("om.JabberID, ");
sb.append("om.LdapID, ");
sb.append("om.RoleID, ");
sb.append("om.UserType, ");
sb.append("om.Active, ");
sb.append("om.RegStatus, ");
sb.append("om.TempPassword, ");
sb.append("om.FirstName, ");
sb.append("om.MiddleInit, ");
sb.append("om.LastName, ");
sb.append("om.Suffix, ");
sb.append("om.CorpPhone, ");
sb.append("om.AltPhone, ");
sb.append("om.Email, ");
sb.append("om.UnderstandHIPAA, ");
sb.append("om.PreferredCont, ");
sb.append("om.PWHintQuestion, ");
sb.append("om.PWHintAnswer, ");
sb.append("om.PWSentDate, ");
sb.append("om.ActiveStartDate, ");
sb.append("om.ActiveEndDate, ");
sb.append("om.ThirdPartyOrganizationID, ");
sb.append("er.EnrollmentID, ");
sb.append("eu.MemberRequest, ");
sb.append("eu.HCNChargesPaidBy, ");
sb.append("eu.AcceptCharges, ");
sb.append("eu.InvStandardInstr, ");
sb.append("eu.WelcomeComment, ");
sb.append("eu.LastUpdateComment, ");
sb.append("eu.ConfirmComment, ");
sb.append("eu.HIPAAAgrmntSgnd, ");
sb.append("eu.GuarConfirmBy, ");
sb.append("eu.GuarConfirmDateTime, ");
sb.append("eu.HIPAAConfirmedBy, ");
sb.append("eu.HIPAAConfirmDateTime, ");
sb.append("eu.ACOEMMbr, ");
sb.append("eu.InitWCCase, ");
sb.append("eu.SecurityCertLink, ");
sb.append("eu.EmpCompany, ");
sb.append("eu.ReAssignCasesTo, ");
sb.append("eu.RoleID as EmpUserRoleID, ");
sb.append("e.CompanyName ");
sb.append("FROM OccMedUser om, CompanyUser eu, Company e,Enrollment
er ");
sb.append("WHERE om.UserID = eu.UserID ");
sb.append("AND eu.CompanyID = e.CompanyID ");
sb.append("AND om.UserID = er.UserID ");
sb.append("AND eu.CompanyID = ? ");
sb.append("AND om.UserID = ? ");

System.out.println("etf.getCompanyID() "+etf.getCompanyID());
System.out.println("etf.getUserID() "+etf.getUserID());

// Prepare Statement
logger.debug("
sb.toString()"+sb.toString());----------------------this is executing
ps = conn.prepareStatement(sb.toString());
ps.setInt(1, Integer.parseInt(etf.getCompanyID()));
ps.setInt(2, Integer.parseInt(etf.getUserID()));
rs = ps.executeQuery();
logger.debug("resultset"+rs);----------------------------------this
does not get executed
// Fetch row and set each value into form bean
if (rs.next()) {
System.out.println(" 1");
// OccMedUser Table
etf.setJabberID(rs.getString("JabberID"));
etf.setLdapID(rs.getString("LdapID"));
etf.setUserType(rs.getString("UserType"));
etf.setEmpUserRoleID(rs.getString("EmpUserRoleID"));
etf.setRoleID(rs.getString("RoleID"));
etf.setActive(rs.getString("Active"));
etf.setRegStatus(rs.getString("RegStatus"));
etf.setTempPassword(rs.getString("TempPassword"));
etf.setFirstName(rs.getString("FirstName"));

String sMiddleInitial = rs.getString("MiddleInit");
if (sMiddleInitial != null && sMiddleInitial.trim().length() > 0)
etf.setMiddleInit(sMiddleInitial);
else
etf.setMiddleInit("");

etf.setLastName(rs.getString("LastName"));
etf.setSuffix(rs.getString("Suffix"));
if (rs.getString("CorpPhone") != null)
etf.setCorpPhone(rs.getString("CorpPhone"));
else
etf.setCorpPhone("");
etf.setPersonalPhone(rs.getString("AltPhone"));
etf.setEmail(rs.getString("Email"));
etf.setUnderstandHIPAA(rs.getString("UnderstandHIPAA"));
etf.setPreferredCont(rs.getString("PreferredCont"));
etf.setPwHintQuestion(rs.getString("PWHintQuestion"));
etf.setPwHintAnswer(rs.getString("PWHintAnswer"));
etf.setPwSentDate(rs.getString("PWSentDate"));
etf.setActiveStartDate(rs.getString("ActiveStartDate"));
etf.setActiveEndDate(rs.getString("ActiveEndDate"));

etf.setThirdPartyOrganizationID(rs.getString("ThirdPartyOrganizationID"));
etf.setEnrollmentID(rs.getString("EnrollmentID"));

// EmployerUser Table
etf.setMemberRequest(rs.getString("MemberRequest"));
etf.setHcnChargesPaidBy(rs.getString("HCNChargesPaidBy"));
etf.setAcceptCharges(rs.getString("AcceptCharges"));
etf.setInvStandardInstr(rs.getString("InvStandardInstr"));
if (rs.getString("WelcomeComment") != null)
etf.setWelcomeComment(rs.getString("WelcomeComment"));
else
etf.setWelcomeComment("");
etf.setLastUpdateComment(rs.getString("LastUpdateComment"));
etf.setHipaaAgreementSigned(rs.getString("HIPAAAgrmntSgnd"));
etf.setGuarConfirmBy(rs.getString("GuarConfirmBy"));
etf.setGuarConfirmDateTime(rs.getString("GuarConfirmDateTime"));
etf.setHipaaConfirmedBy(rs.getString("HIPAAConfirmedBy"));
etf.setHipaaConfirmDateTime(rs.getString("HIPAAConfirmDateTime"));
etf.setAcoemMember(rs.getString("ACOEMMbr"));
etf.setInitWCCase(rs.getString("InitWCCase"));
etf.setSecurityCertLink(rs.getString("SecurityCertLink"));
etf.setEmpCompany(rs.getString("EmpCompany"));
etf.setReAssignCasesTo(rs.getString("ReAssignCasesTo"));
etf.setCompanyName(rs.getString("CompanyName"));
}

// return the form
return etf;
}
catch (Exception ex) {
throw new ServiceException(this, ex);
}
finally {
try {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (conn != null)
conn.close();
}
catch (Exception ex) {
ServiceException sex = new ServiceException(this, ex);
logger.error(sex.getMessage());
}
}



please help me out
}
 
R

Rhino

hi,
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.


public EmployerTeamForm load(EmployerTeamForm etf) throws
ServiceException {

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

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
// Get connection
ConnectionHandler hdlr =
ConnectionManager.getInstance().getHandler();
conn = (Connection)hdlr.getConnection();

// perform SQL...
StringBuffer sb = new StringBuffer();
sb.append("SELECT ");
sb.append("om.UserID, ");
sb.append("om.JabberID, ");
sb.append("om.LdapID, ");
sb.append("om.RoleID, ");
sb.append("om.UserType, ");
sb.append("om.Active, ");
sb.append("om.RegStatus, ");
sb.append("om.TempPassword, ");
sb.append("om.FirstName, ");
sb.append("om.MiddleInit, ");
sb.append("om.LastName, ");
sb.append("om.Suffix, ");
sb.append("om.CorpPhone, ");
sb.append("om.AltPhone, ");
sb.append("om.Email, ");
sb.append("om.UnderstandHIPAA, ");
sb.append("om.PreferredCont, ");
sb.append("om.PWHintQuestion, ");
sb.append("om.PWHintAnswer, ");
sb.append("om.PWSentDate, ");
sb.append("om.ActiveStartDate, ");
sb.append("om.ActiveEndDate, ");
sb.append("om.ThirdPartyOrganizationID, ");
sb.append("er.EnrollmentID, ");
sb.append("eu.MemberRequest, ");
sb.append("eu.HCNChargesPaidBy, ");
sb.append("eu.AcceptCharges, ");
sb.append("eu.InvStandardInstr, ");
sb.append("eu.WelcomeComment, ");
sb.append("eu.LastUpdateComment, ");
sb.append("eu.ConfirmComment, ");
sb.append("eu.HIPAAAgrmntSgnd, ");
sb.append("eu.GuarConfirmBy, ");
sb.append("eu.GuarConfirmDateTime, ");
sb.append("eu.HIPAAConfirmedBy, ");
sb.append("eu.HIPAAConfirmDateTime, ");
sb.append("eu.ACOEMMbr, ");
sb.append("eu.InitWCCase, ");
sb.append("eu.SecurityCertLink, ");
sb.append("eu.EmpCompany, ");
sb.append("eu.ReAssignCasesTo, ");
sb.append("eu.RoleID as EmpUserRoleID, ");
sb.append("e.CompanyName ");
sb.append("FROM OccMedUser om, CompanyUser eu, Company e,Enrollment
er ");
sb.append("WHERE om.UserID = eu.UserID ");
sb.append("AND eu.CompanyID = e.CompanyID ");
sb.append("AND om.UserID = er.UserID ");
sb.append("AND eu.CompanyID = ? ");
sb.append("AND om.UserID = ? ");

System.out.println("etf.getCompanyID() "+etf.getCompanyID());
System.out.println("etf.getUserID() "+etf.getUserID());

// Prepare Statement
logger.debug("
sb.toString()"+sb.toString());----------------------this is executing
ps = conn.prepareStatement(sb.toString());
ps.setInt(1, Integer.parseInt(etf.getCompanyID()));
ps.setInt(2, Integer.parseInt(etf.getUserID()));
rs = ps.executeQuery();
logger.debug("resultset"+rs);----------------------------------this
does not get executed
Click to expand...

There are a couple of ways to interpret the previous line. If you mean that
this line AND ONLY THIS LINE - logger.debug("resultset"+rs); - is not
working, then the problem lies in the debug() method of your logger class.
Since you haven't given us that code, you can't really expect us to figure
out why it works. If it was my problem, I'd use the debugger in my IDE to
trace through the code in logger.debug() one line at a time until I found
the problem.

The OTHER way to interpret the comment in your code is that the
logger.debug() line is the first of several lines that don't behave the way
you want. That's a little bit different since rs.next() is standard JDBC
code.

If the System.out.println(" 1") statement is not printing " 1", I can
practically guarantee you that the reason is that the result set is empty.
That is the normal behaviour of rs.next(). If the result set is empty, it is
VERY likely that the reason is that your SQL statement simply doesn't return
any rows.

For instance, let's say that I had a table containing information about
people and the number of body parts that they had. It looks something like
this:

HUMANS
Name NumberOfHeads NumberOfEyes NumberOfLegs
Joe Blow 1 2 2
Moshe Dayan 1 1 2
Terry Fox 1 2 1

Now, for pretty much any human being who has ever lived, the query

select * from HUMANS where NumberOfHeads = 2

is going to return no rows, unless perhaps you count certain conjoined twins
as having two heads.

I'm sure you realize all that but what you may not realize is that it is
very easy to make a mistake in a query. The person writing the query may
have meant to determine the names of people who had two legs but mistakenly
wrote NumberOfHeads instead of NumberOfLegs. That's an easy mistake to make.

I'm suggesting that your query is accidentally asking for something that
doesn't exist in your table.

Another possibility is that you are asking a reasonable question but that
there is no data in the table. That can happen several ways. Someone may
have deleted the data in the table after you put rows in it. Or maybe your
code is pointing at the wrong version of the table: maybe you meant to read
the Production version of the table but are reading the Test version.

Check out those possibilities and you will probably find your problem pretty
quickly.

// Fetch row and set each value into form bean
if (rs.next()) {
System.out.println(" 1");
// OccMedUser Table
etf.setJabberID(rs.getString("JabberID"));
etf.setLdapID(rs.getString("LdapID"));
Click to expand...

[snip]
 
T

Thomas Hawtin

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.
Click to expand...
if (etf == null) {
etf = new EmployerTeamForm();
}
Click to expand...

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 ");
Click to expand...

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);
Click to expand...

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();
Click to expand...

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
 

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

Similar Threads

problem in inserting record in ms access. 5
how to access connection object in another class? 5
Best way to do this? 13
Closed Statement issue 7
hi everybody me Epsan and i am new to java i have a problem in validattion my code is 0
Why JavaBean load jdbc driver failed? 3
java.io.WriteAbortedException: writing aborted; java.io.NotSerializableException: 3
illegal start of expression 0

Members online

Total: 28 (members: 2, guests: 26)
Robots: 351

Forum statistics

Threads
473,769
Messages
2,569,582
Members
45,058
Latest member
QQXCharlot

Latest Threads

Top