Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Put repeated ResultSet into a method

Status
Not open for further replies.

dreampolice

Technical User
Dec 20, 2006
85
US
I have methods that call the same ResultSet statement and was wondering if I can put the ResultSet into a method and call it instead:

Code:
public int mymethod(MyBean theobject)
{
//db connection part here

String query = "SELECT EmailAddress FROM UserT " +
"WHERE firstname  = '" + theobject.getFirstname () + "' and lastname = '" + theobject.getLastname() + "'";
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery(query);
...
}

public int anothermethod(MyBean theobject)
{
//db connection part here

String query = "SELECT EmailAddress FROM UserT " +
"WHERE firstname  = '" + theobject.getFirstname () + "' and lastname = '" + theobject.getLastname() + "'";
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery(query);
...
}


This would be better:
Code:
public int mymethod(MyBean theobject)
{
//db connection part here

//method call here
...
}

public int anothermethod(MyBean theobject)
{
//db connection part here

//method call here
...
}


 
sorry error in my post. Here is the correct info:
Code:
public int mymethod(MyBean theobject)
{
//db connection part here

String query = "SELECT * FROM UserT " +
"WHERE firstname  = '" + theobject.getFirstname () + "' and lastname = '" + theobject.getLastname() + "'";
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery(query);
...
}

public int anothermethod(MyBean theobject)
{
//db connection part here

String query = "SELECT * FROM UserT " +
"WHERE firstname  = '" + theobject.getFirstname () + "' and lastname = '" + theobject.getLastname() + "'";
Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery(query);
...
}

This would be better:

Code:
public int mymethod(MyBean theobject)
{
//db connection part here

//method call here
...
}

public int anothermethod(MyBean theobject)
{
//db connection part here

//method call here
...
}
 
For repeated statements I would use a PreparedStatement. I would also move the connection part to another method and create a method to return the Resultset. Tip: it is a good practice to keep SQL statements outside of your code (f.e. in a property file).

private PreparedStatement stat;
private Connection conn;

private boolean dbConnect() {
try {
// dbconnection part
conn = DriverManager.getConnection("some connect string");
} catch (Exception e) {
return false;
}
String query = "SELECT * FROM UserT WHERE firstname = ? and lastname = ?";
stat = conn.prepareStatement(query);
return true;
}

private void dbClose() {
if (conn != null) {
conn.close;
conn = null;
}
}

private ResultSet executeQuery(MyBean bean) {
stat.setString(1, bean.getFirstname());
stat.setString(2, bean.getLastname());
return stat.executeQuery();
}

public int myMethod(MyBean theObject) {
if (dbConnect()) {
ResultSet results = executeQuery(theObject);
......
dbClose();
}
.....
}

public int anotherMethod(MyBean theObject) {
if (dbConnect()) {
ResultSet results = executeQuery(theObject);
......
dbClose();
}
.....
}
 
tom62, may i know where do you close the resultset and preparedstatement?
 
You could close the PreparedStatement in the dbClose() method

private void dbClose() {
if (conn != null) {
stat.close();
conn.close;
conn = null;
}
}
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top