Hello everone,
I'm a newbie to java and starting to learn JSP using WSAD 5.1.2. I start creating some JSP pages that access my Database so I can practice this skill, and started to knowtice that I was writing the same code over and over again particually when working with my Database.
I am looking for a good way to create a class of object that handles the "low-level" tasks of ...
1.) getting and returning(closing) JNDI-DB2 pooled connections to and from my webserver.
2.) Does some basic error handleing and logging to each of these connection.
3.) Can be used by beans for encapulatatind database access.
I started writting some test code, and it works, but since I'm new I dont know if the qaility if my code is good (looks very procederal to me
), or if there is a better way.
Here is the code that I started to write.... If anybody has been down this road befor and would like to share, I would like to learn from it
Here is what I wrote and some JSP code that uses it , Please evaluate and let me know if there is a better way to do this.
Thanks in advance for everyones help
__________________________________________________ ___
Code:
/**
* @author me
*
* Create a generic class that encapulates the details of accessing the Database. This class currently needs to be included in the JSP page.
*
*/
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Properties;
public class DBProcessor {
private Context initCtx = null;
public static final String WSSAMPLE_DBCONN = "WSSAMPLE Database Connection";
public static final String CRDM_DATAMART_DBCONN = "CRDM Datamart Database Connection";
public static final String CRDM_APP_DBCONN = "CRDM Application Database Connection";
/**
* Returns Connection from the Pool.
* @param connType
*/
public Connection getConnection(String connType) throws Exception
{
//DebugMessage msg = Log.debug(Log.JDBC,"Database connection is requested");
System.out.println("Database connection is requested");
Connection conn = getConnectionFromPool(connType);
//if(msg != null) msg.setEndTime();
return conn;
}
/**
* Returns a Connection back to the pool
* @param conn
*/
public void closeConnection(Connection conn)
{
// Close the connection and return it to the pool
//DebugMessage msg = Log.debug(Log.JDBC,"Request to close database connection ojbect");
System.out.println("Close database connection requested");
if(conn != null)
try
{
conn.close();
}
catch (Exception e)
{
System.out.println("Close database connection Exception occured." + e);
}
}
/**
* Does a JNDI lookup for the datasrc and gets a connection from the pool.
* @return Connection Gets a db connection from a pool.
*/
private Connection getConnectionFromPool(String connType) throws Exception
{
Connection conn = null;
try {
if(connType.equalsIgnoreCase(DBProcessor.CRDM_DATAMART_DBCONN))
{
initCtx = new InitialContext();
//DataSource ds = (javax.sql.DataSource)initCtx.lookup("java:comp/env/jdbc/database2");
DataSource ds = (javax.sql.DataSource)initCtx.lookup("jdbc/database2");
return ds.getConnection("dev","dev");
}
else if(connType.equalsIgnoreCase(DBProcessor.WSSAMPLE_DBCONN))
{
initCtx = new InitialContext();
//DataSource ds = (javax.sql.DataSource)initCtx.lookup("java:comp/env/jdbc/wssample");
DataSource ds = (javax.sql.DataSource)initCtx.lookup("jdbc/wssample");
return ds.getConnection("dev","dev");
}
else if(connType.equalsIgnoreCase(DBProcessor.CRDM_APP_DBCONN))
{
initCtx = new InitialContext();
//DataSource ds = (javax.sql.DataSource)initCtx.lookup("java:comp/env/jdbc/database1");
DataSource ds = (javax.sql.DataSource)initCtx.lookup("jdbc/database1");
return ds.getConnection("dev","dev");
}
} catch (NamingException ex){
// Log.info(Log.CONTEXT, "Error getting Initital JNDI Context in DBProcessor: " + ex.toString());
System.out.println("Error getting Initital JNDI Context in DBProcessor: " + ex.toString());
throw ex;
} catch (SQLException sqex) {
throw sqex;
// Log.info(Log.JDBC, "Unable to get connection from DBProcessor: " + sqex.toString());
//System.out.println("Unable to get connection from DBProcessor: " + sqex.toString());
}
return conn;
}
}
__________________________________________________ __________
JSP Usage
Code:
//Get a connection to the Database from the pool
DBProcessor dbproc = new DBProcessor();
conn2 = dbproc.getConnection(DBProcessor.WSSAMPLE_DBCONN);
if (conn2 != null)
System.out.println("Connection Successful");
stmt2 = conn2.createStatement();
java.sql.ResultSet rs2 = stmt2.executeQuery("SELECT * FROM BOOKINFO");
%>
<table>
<tr>
<td>BOOKNUM</td>
<td>TITLE</td>
<td>AUTHOR</td>
<td>SUBJECT</td>
<td>LOCATION</td>
<td>SUMMARY</td>
</tr>
<%
while (rs2.next()) {
out.println("<tr>");
out.println("<td>" + rs2.getString("BOOKNUM") + "</td>");
out.println("<td>" + rs2.getString("TITLE") + "</td>");
out.println("<td>" + rs2.getString("AUTHOR") + "</td>");
out.println("<td>" + rs2.getString("SUBJECT") + "</td>");
out.println("<td>" + rs2.getString("LOCATION") + "</td>");
out.println("<td>" + rs2.getString("SUMMARY") + "</td>");
out.println("</tr>");
}
%>
</table>
<%
//Close the connection
dbproc.closeConnection(conn2);
%>
Linear Mode
