package com.extreamtech.bean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import com.extreamtech.exception.WCMException;
public abstract class MasterBean {
private static final String CONTEXT_NAME = "java:/comp/env";
private static final String RESOURCE_NAME = "jdbc/wcmsdb";
protected long id;
protected final String tablename = "";
protected final String fieldnames = "";
protected String selectquery = "";
protected String savequery = "";
protected String deletequery = "";
protected String updatequery = "";
protected ResultSet resultset;
public abstract void prepareUpdate() throws WCMException;
public MasterBean(){}
public MasterBean(long id){}
private Connection conn;
protected PreparedStatement ps;
protected void getData() throws WCMException {
openConnection();
prepareStatement(selectquery);
executeQuery();
}
public void save() throws WCMException {
try {
openConnection();
prepareStatement(savequery);
executeUpdate();
} finally {
closeConnection();
}
}
public void delete() throws WCMException {
try {
openConnection();
prepareStatement(deletequery);
executeUpdate();
} finally {
closeConnection();
}
}
public void update() throws WCMException {
try {
openConnection();
prepareStatement(updatequery);
prepareUpdate();
executeUpdate();
} finally {
closeConnection();
}
}
protected void executeUpdate() throws WCMException {
try {
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}
}
protected void executeQuery() throws WCMException {
try {
resultset = ps.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}
}
protected void prepareStatement(String sql) throws WCMException {
try {
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}
}
protected void openConnection() throws WCMException {
try {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup(CONTEXT_NAME);
DataSource ds = (DataSource) envContext.lookup(RESOURCE_NAME);
conn = ds.getConnection();
} catch (NamingException e) {
e.printStackTrace();
throw new WCMException(e);
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}
}
protected void closeConnection() throws WCMException {
try {
if (ps != null) {
ps.close();
ps = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}
}
protected String questionMarks() {
String temp = "";
int fieldlength = fieldnames.split(",").length;
for(int i=0; i
temp = temp + "?";
if(i
temp = temp + ", ";
}
}
return temp;
}
protected String updateQuestionMarks() {
String temp = "";
int fieldlength = fieldnames.split(",").length;
for(int i=0; i
temp = temp + fieldnames.split(",")[i] + " = ?";
if(i
temp = temp + ", ";
}
}
return temp;
}
protected void initeValue() {
selectquery = "SELECT " + fieldnames + " FROM " + tablename
+ " WHERE id = " + id;
savequery = "INSERT INTO " + tablename + "(" + fieldnames + ") "
+ "VALUES (" + questionMarks() + ")";
deletequery = "DELETE FROM " + tablename + " WHERE id = " + id;
updatequery = "UPDATE " + tablename + " SET " + updateQuestionMarks() + " WHERE id = "
+ id;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
}
Connection Pool in Tomcat
Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to your context.xml.
<Context>
<Resource name="jdbc/dbname" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/dbname?autoReconnect=true"/>
Context>
web.xml configuration
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">
<description>MySQL Test Appdescription>
<resource-ref>
<description>DB Connectiondescription>
<res-ref-name>jdbc/dbnameres-ref-name>
<res-type>javax.sql.DataSourceres-type>
<res-auth>Containerres-auth>
<resource-ref><web-app>
Code sample in JSP/JSTL
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<sql:query var="rs" dataSource="jdbc/dbname">
select id, foo, bar from testdata
</sql:query>
<html>
<head>
<title>DB Testtitle>
<head>
<body>
<h2>Resultsh2>
<c:forEach var="row" items="${rs.rows}">
Foo ${row.foo}<br/>
Bar ${row.bar}<br/>
c:forEach>
<body>
<html>
Code sample in Java
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/dbname");
Connection conn = ds.getConnection();
//etc.
unit Logger;
interface
uses SysUtils, Forms;
const
DEBUG_MODE = True;
procedure Debug(FormName, msg: String);
implementation
procedure Debug(FormName, msg: String);
var
myFile : TextFile;
Path : String;
FileHandle: Integer;
myDate : TDateTime;
// Stream : FileStream;
begin
myDate := Now;
Path := ExtractFilePath( Application.ExeName ) + 'application.log';
if not FileExists(Path) then
begin
FileHandle := FileCreate(Path);
FileClose(FileHandle);
end;
if DEBUG_MODE then begin
// Try to open the Test.txt file for writing to
AssignFile(myFile, path ) ;
ReWrite(myFile);
// Append(myFile);
// Write a couple of well known words to this file
WriteLn(myFile, FormatDateTime('c', myDate) + ' [' + FormName + '] ' + msg);
// Close the file
CloseFile(myFile);
end;
end;
end.
