• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Need suggestions on: Database connection performance confirmation.

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Friends,

Please send me your openion regarding this issue.

Actually i am using a DAOImpl class where i will get the data base connection. The code snippet for one method is like this...
And i am repeating the same procedure in each method like opening the connection and closing the connection in each method.

My problem is:

Is it right to open and closing connection in each method of DAO implementaton class?
and how can i alter the code if there exist any performance issue?

This application is a web based appliation using struts.

In the Action class i am calling each required implementation method.

public EmployeeDAOImpl(DataSource dataSource) {
this.dataSource = dataSource;
}

/**
Checks if an employee is Admin for the specified module in RMS_MODULE_ADMINS table.
**/

public boolean isAdmin(String employeeId, String moduleId) {

logger
.debug("Entered - isAdmin() of EmployeeDAOIMPL with arguments EmployeeId:"
+ employeeId + "and ModuleId:" + moduleId + ".");

Connection conn_Admin = null;
PreparedStatement pst_Admin = null;
ResultSet rs_Admin = null;

boolean isAdmin = false;

try {
conn_Admin = dataSource.getConnection();
pst_Admin = conn_Admin
.prepareStatement("SELECT ROLE_ID FROM RMS_MODULE_ADMINS "
+ "WHERE EMP_ID = ? AND MODULE_ID = ?");
pst_Admin.setString(1, employeeId.toLowerCase());
pst_Admin.setString(2, moduleId);
rs_Admin = pst_Admin.executeQuery();

if (rs_Admin.next()) {

isAdmin = true;

}
} catch (Exception e) {
logger.error("Error in isAdmin() of EmployeeDAOIMPL:"
+ e.getMessage());
} finally {
Util.closeConnection(rs_Admin, pst_Admin, conn_Admin);
logger.debug("Exiting - isAdmin() of EmployeeDAOIMPL");
}
return isAdmin;
}
 
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi Phanibabu,

About opening and closing connections: it is right to close a connection as soon as you don't need it anymore.

If you have a transaction that has 2 or more insert-methods, you will not want to open and close a new connection with every method.
You can use a framework like Spring to open the connection for you, pass the connection to the insert-methods that need and then close it.

About performance: if necessary you can ask your datasource for more connections(by configuration), so that you don't need to change any Java-code.

I think your code is OK, it would be a little faster if you would put an if(...) around the debug-logmessages.

if (logger.isDebugEnabled()){
logger.debug("Entered - isAdmin()...")
}

Herman
 
Kanuri Phanibabu
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thankyou Herman Scheltinga, for your reply and suggesions.
If you have any tips that increase the performance of the application / know any url's please suggest me.

 
Herman Schelti
Ranch Hand
Posts: 387
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi,

here are some of my tips (all SQL/database related)


-if something is not likely to change (somebody will probably have admin rights for a long time), you can cache the result (eg in session-scope of your webapplication), so you don't have to execute the same sql with the same result everytime.

-if you think your SQL looks complicated, or if you are doing complicated stuff in your Java-code with the results: talk to somebody who knows SQL more than you do.

-ask your Database administrator if the database is tuned right for the sql you want to execute. Tuning can make a BIG difference.

-monitor your application, so that you know how long sql-statements take.
In a framework like Spring it's easy to write a class that does this.
(Aspect Oriented Programminng)

-don't overdo this performance tuning, especially if you don't have and don't expect any problems.

Herman
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic