Put repeated ResultSet into a method

[dreampolice]

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:

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:

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

//method call here
...
}

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

//method call here
...
}

 

[dreampolice]

sorry error in my post. Here is the correct info:

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:

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

//method call here
...
}

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

//method call here
...
}

 

[tom62]

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();
}
.....
}

 

[lowbk]

tom62, may i know where do you close the resultset and preparedstatement?

 

[tom62]

You could close the PreparedStatement in the dbClose() method

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