Get record from Table into an Array

public List getArrayList(String sql) throws WCMException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

List list = new ArrayList();
// Open Connection
try {

Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup(Global.DB_CONTEXT_NAME);
DataSource ds = (DataSource) envContext.lookup(Global.DB_RESOURCE_NAME);
conn = ds.getConnection();
ps = conn.prepareStatement(sql);
// Execute Query
rs = ps.executeQuery();

// Retrieve Record
while(rs.next()){
// Generate String array
String[] temp = new String[rs.getMetaData().getColumnCount()];
for(int i=0; i temp[i] = rs.getString(i+1);
}
list.add(temp);
}

} catch (NamingException e) {
e.printStackTrace();
throw new WCMException(e);
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}
// Close Connection
finally{
try {
if (ps != null) {
ps.close();
ps = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
throw new WCMException(e);
}

}
return list;
}

My Model Class

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;

}

}

MySQL Connection Pool in Tomcat

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.

Logger - You may find if useful somehow

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.

You may find if useful somehow... :p