serving the solutions day and night

Pages

Thursday, October 7, 2010

This blog is going to explain how to design java database access model?. It contains 3 part, 1st part is for database model code, 2nd part is for access model code and 3rd part is for stored procedure sql code.

Database access model is used to connect database, retrieve records and passing input/output parameters using Stored Procedures. All the connections and transactions are in one class file. Designed simple method which is used to connect database and to produce the result set.

In the class file, one method is taking care of connecting the database, another method is to retrieve the result set and last method is doing for insert, modify and delete the record.
This database access model contains all the database transactions are in one place and easy to maintain the error code instead of checking multiple methods in different classes.

For example if page want to get result set from employee table with some condition, then pass the stored procedure name and the in/out parameter name/value to the class object. This class object will use Callable Statement to built the stored procedure with in/out parameters, connect to the database and retrieve the result set.

The below codes are one single class file, and explained each method functionality in the commented area.

//Constructor method is used to create database connection
//Pass the stored procedure name and number of parameters
//if no of parameters is '0', then code won't generate in/out arguments
//if no of parameters more then '0', let us assume iNoOfParameters = 3,
//then code generate in/out arguments like that 'call storedprocedurename(?,?,?)'

//Insert/Modify/Delete new record//Passing 3 input arguments and 1 output argument//Passing arguments contains by different data type//Calling StoredProcedure constructor, passing stored procedure name and number
of arguments//sDML - is used to identity the functions is for insert(i), modify(m) and delete(d) record