Repeated SQL and ResultSet in a method

Thread view:

francan00@yahoo.com - 27 Sep 2007 01:09 GMT

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

Reply to this Message

francan00@yahoo.com - 27 Sep 2007 01:10 GMT

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

Reply to this Message

Donkey Hot - 27 Sep 2007 02:32 GMT

francan00@yahoo.com wrote in news:1190851844.196569.67790
@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.

Reply to this Message

Roedy Green - 27 Sep 2007 03:05 GMT

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

Signature

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

Reply to this Message

Repeated SQL and ResultSet in a method

Free Magazines