Repeated SQL and ResultSet in a method

[francan00]

I have methods that call the same ResultSet statement and SQL and was
wondering if I can put them into a method and call the method instead
of repeating the same lines in each one of my methods:

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
....
}

 

[francan00]

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 * 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
....
}

 

[Donkey Hot]

(e-mail address removed) wrote in @k79g2000hse.googlegroups.com:

String query = "SELECT * FROM UserT " +
"WHERE firstname = '" + theobject.getFirstname () + "' and lastname =
'" + theobject.getLastname() + "'";

You should/could use prepared statements and bind variables.

String query = "SELECT * FROM UserT " +
"WHERE firstname = ? and lastname = ?" ;

Make a PreparedStatement, bind variables to it, and use the same prepared
statement over and over again, only varying to data in the bint variables.

That way your application is

a) effective. The SQL statement is parsed and prepared only once in the
database, saves LOTS of resources and processor time

b) secure and safe. No SQL-injection (google:sql injection) posibility.
Creating SQL statements on the fly using variable values is always
dangerous. What happens if the user enters "Anders'; drop table UserT;
select 1 " to the "First Name" field in your application?


1) Use always Prepared Statements.
2) Prepare them once. Keep them as your class's instance variabled for
example, not local to a procedure.
3) Use them many times. Change only data, not the SQL statement.

 

[Roedy Green]

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:

There is nothing magic about JDBC calls. You can abbreviate them by
encapsulating in methods and passing parameters just like any other
code.

You might want also to look up on PreparedStatement to avoid the
overhead of constructing a query from scratch every time.