an effective way for JSP/servlets to interact with databases

hai,

it is better if u go for javaBeans

for connection purpose

as this is reused many times

and u can avoid large stuff of code

in the jsp.Also try connection pooling pooling if

user name to db for most user is same !!!!

sample code for database bean

import java.sql.*;

import java.io.*;

/**

* This class determines the database connectivity by defining the database url,

* database driver and database connection and other functions to prepare & execute

* sql queries.

*/

public class DBBean {

String dbURL; //database url

String dbDriver = "sun.jdbc.odbc.JdbcOdbcDriver"; //database driver

private Connection dbCon; //database connection

private PreparedStatement pre;

private Statement s ;

boolean prepareflag=false;

public IntranetBean() {

super();

}

public boolean connect() throws ClassNotFoundException, SQLException {

//install the driver

Class.forName(dbDriver);

//opens a connection

dbCon = DriverManager.getConnection("jdbc:odbc:ureodbc");

return true;

}

public void close() throws SQLException {

//close the statement

if(prepareflag){pre.close();}

else{

s.close();}

//close the connection

dbCon.close();

prepareflag=false;

}

public ResultSet execSQL(String sql) throws SQLException {

//gives a statement object

s = dbCon.createStatement();

//execute the sql command

ResultSet r = s.executeQuery(sql);

return (r == null) ? null : r;

}

public void execUPDATE(String sql) throws SQLException {

//gives a statement object

s = dbCon.createStatement();

//execute the sql command

s.executeUpdate(sql);

}

public boolean prepareSQL(String sql) throws SQLException {

//gives a prepared statement object

pre = dbCon.prepareStatement(sql);

prepareflag=true;

return true;

}

public boolean execpreparedSQL(String sql) throws SQLException {

//execute the sql command

pre.execute(sql);

return true;

}

public ResultSet getpreparedResultSet() throws SQLException {

//get resultset

ResultSet r = pre.getResultSet();

return (r == null) ? null : r;

}

public void setInt(int i,int no) throws SQLException {

pre.setInt(i,no);

}

public void setDouble(int i,double dbl) throws SQLException {

pre.setDouble(i,dbl);

}

public void setString(int i,String str) throws SQLException {

pre.setString(i,str);

}

public void setDate(int i,Date d) throws SQLException {

pre.setDate(i,d);

}

public void setTime(int i,Time tm) throws SQLException {

pre.setTime(i,tm);

}

public void setNull(int i,int type) throws SQLException {

pre.setNull(i,type);

}

}

manu_mr at 2007-6-29 2:48:23 >