Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

in function using PreparedStatement create performance problem or not?

 
ahmet oguz
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I want to ask about PreparedStatement performance. Which one is more efficient.
Not: In my Sample class foo and func function is used many many times in my project. These are the most called function in my project so its performans is very important.

1)
class Sample{
private Connection con;
private PreparedStatement pre;
private String sql_foo;
private String sql_func;
puclic Sample(){
// set the connection object;
}
public Arraylist foo(){
pre = con.preparedStatement(sql);
Arraylist list = new Arraylist;
pre.setString();...
ResultSet rs = pre.executeQuery();
....
...
return list;
}
}
public Arraylist func(){
pre = con.preparedStatement(sql_func);
Arraylist list = new Arraylist;
pre.setString();...
ResultSet rs = pre.executeQuery();
....
...
return list;
}
}

2)
class Sample{
private Connection con;
private PreparedStatement pre_foo;
private PreparedStatement pre_func;
private String sql_foo;
private String sql_func;
public Sample(){
//set the con object
//SET THE pre_foo and pre_func object with appropriate string(sql_foo or sql_func)
}
public ArrayList foo(){
Arraylist list = new ArrayList();
pre_foo.setString();
resultSet = ...
return list;
}

public ArrayList func(){
Arraylist list = new ArrayList();
pre_func.setString();
resultSet = ...
return list;
}
******************************************

My question is settting preparedStatement object in a most used function
is bad way? In both foo and func function is this bad?
pre = con.preparedStatement(appropriateString);

Thanks,
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you are asking whether it is a good thing to re-use a PreparedStatement - to which the answer is "yes". That is largely what they are for.
 
ahmet oguz
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok, but which one is effective? if we writes in function everytime
pre = con.prepareStatement(sql);
is this the right usage or not? if not, must i make this not in function, out of function and in function only set the parameters?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35266
383
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahmet,
This is the correct usage. The JDBC driver only cares that the SQL is exactly the same. It does not care how many times you call "conn.preparedStatement()" or from where. With the exception if you use too many different SQL statements, you will run out of cache and it will have to re-prepare some.

In your example, the prepared statements will be cached. You don't have to do anything extra explicitly.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic