Hi,

I have a Java Application that accesses a PostgreSQL Database via a c3p0 Connection pool.

My application's Business Logic is implemented as Postgres Stored Procedures, as I want to be able to change the front end without much effort should the need arise. I had originally planned on using EJB 3.0, but Dali failed to generate Entity Beans from my Postgres Database which is still being looked at.

I continued my work by using stored procedures, with a simple Singleton Pattern to handle connections. However, after looking closer at SQL Injection, I decided to implement CallableStatements together with c3p0 to manage connections to my database. The c3p0 packages are only found on the client side (Java Swing front end), with just PostgreSQL 8.4 on my server. Is this a correct structure?

I'm able to retrieve multiple ResultSets using this setup/structure, but when I have a Stored Function that is supposed to insert values into a database table, the Stored Function seems to run well, but no data is inserted. I even have a sequenced ID (auto increment) which is incremented, yet the data isn't inserted.

I will break down the code into parts so that you can see where/if I went wrong.

First the database. Here is an example table:

CREATE TABLE "zzTest"
(
  id bigserial NOT NULL,
  "zzCostSubjectId" bigint NOT NULL,
  "updatedInt" bigint,
  timed date,
  CONSTRAINT "pk_zzTest" PRIMARY KEY (id)
)

A simple Stored Function that inserts values into the table, returning the newly generated id.

CREATE OR REPLACE FUNCTION zztest_insert(zzcostsubject bigint, updatedint bigint)
  RETURNS bigint AS
$$
declare
subjectId bigint;
currentTime timestamp without time zone;
begin
currentTime = NOW()::timestamp without time zone;
INSERT INTO "zzTest"("zzCostSubjectId", "updatedInt", timed)
VALUES (zzCostSubject, updatedInt, currentTime);
select into subjectId currval('"zzTest_id_seq"');
return subjectId;
end;
$$
LANGUAGE 'plpgsql';

Since I had already implemented a Singleton Pattern for the jdbc connection, I've simply used the same pattern to implement the connection pool.

public class DatabaseConnection {

private static ComboPooledDataSource datasource;
private static final String DRIVER_NAME = "org.postgresql.Driver"; 
private static final String UNAME = "postgres";
private static final String PWD = "m1a2p3s";
private static final String URL = "jdbc:postgresql://localhost:5432/fps";

public DatabaseConnection (){
datasource = setupDataSource();
}

// load jdbc driver
public static ComboPooledDataSource setupDataSource (){
ComboPooledDataSource cpds = new ComboPooledDataSource();
try{
cpds.setDriverClass(DRIVER_NAME);
} catch (PropertyVetoException e){
e.printStackTrace();
}
cpds.setJdbcUrl(URL);
cpds.setUser(UNAME);
cpds.setPassword(PWD);
// cpds.setMinPoolSize(5);
// cpds.setAcquireIncrement(5);
// cpds.setMaxPoolSize(20);
return cpds;
}
// create connection
public static Connection establishConnection() throws SQLException {
return datasource.getConnection();
}

// close connection
public static void closeConnection() throws SQLException{
DataSources.destroy(datasource);
}
}

NOTE: I had been getting connection errors with defined setMinPoolSize(), setAcquireIncrement() and setMaxPoolSize() values. Leaving the default values seems to have solved this problem though I'm not certain.

Here is the Singleton Pattern:

public class SingletonConnection {

private static DatabaseConnection con;

public SingletonConnection(){}

public static DatabaseConnection instance(){

assert con == null;
con = new DatabaseConnection();
return con;
}
}

I've also implemented an MVC Architecture, and since all Model Classes have certain things in common that I want them all to do, I've created an abstract class AbstractModel, which also does a session check as to whether the user "session" is indeed the one the user is currently on. If not, then the user should be notified and prompted to re-log in. Here is the AbstractModel class which is still being worked on:

public abstract class AbstractModel {

protected  Connection myConnection;
protected  Statement stmt;
protected  CallableStatement cstmt;
protected  ResultSet rs;

protected boolean loginCheck; // if userId and userLoginHistoryId are valid - true, else false
protected boolean userLoggedIn; // if user is already logged in - true, else false

public AbstractModel (int userId, Long userLoginHistoryId){
createConnection(); // establish connection
loginCheck = false;
userLoggedIn = false;
if (userLoginHistoryId == 0){ // special case for login
loginCheck = true; // userLoginHistoryId = 0
userLoggedIn = false; // set loginCheck to true, userLogged in to false
} else {
userLoggedIn = true;
try{

myConnection.setAutoCommit(false);
cstmt = myConnection.prepareCall("{ ? = call \"user_login_session_check\"(?,?) }"); 
cstmt.registerOutParameter(1, Types.BOOLEAN);
cstmt.setInt(2, userId);
cstmt.setLong(3, userLoginHistoryId);
cstmt.execute();
loginCheck = cstmt.getBoolean(1);

// rs = (ResultSet) cstmt.getObject(1);
// ResultSetMetaData rsmd = rs.getMetaData();
// int columnCount = rsmd.getColumnCount();
// UserIdPasswordDAO idPassobj;
// while (rs.next()){
// for (int i = 1; i <= columnCount; i++){
// System.out.println("resultset " + i + ": "+ rs.getObject(i));
// if (i == 1){ // userId
// loginCheck = rs.getBoolean(1);
//
// }
// }
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
} finally{
// closeConnection();
}
}

}
// // close connection
// public void closeConnection(){
// try{
// if(!rs.equals(null)){
// rs.close();
// }
//
// cstmt.close();
// myConnection.close();
// } catch (SQLException e){
// System.out.println("SQL Exception: ");
// e.printStackTrace();
// }
//
// }

// close connection
public void closeConnection(){
try{
// if (!rs.isClosed()){
// rs.close();
// }
cstmt.close();
SingletonConnection.instance();
DatabaseConnection.closeConnection();
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
}

}

// establish connection
public void createConnection(){
try {
SingletonConnection.instance();
myConnection = DatabaseConnection.establishConnection();
} catch (SQLException e){
e.printStackTrace();
}
}

// login session check
public boolean expiredLoginCheck (){
if (loginCheck == false && userLoggedIn == true){
closeConnection();
return false;
} else {
return true;
}
}
}

Main problem I'm having is deciding how to close a connection. Both seem to work (commented out and not commented closeConnection() methods) but both still raise the connection error when setMinPoolSize(), setAcquireIncrement() and setMaxPoolSize() are defined.

Here is the implementation of how I would call the Stored Function:

public class UserModel extends AbstractModel{

public UserModel(int userId, Long userLoginHistoryId) {
super(userId, userLoginHistoryId);
// TODO Auto-generated constructor stub
}

// test for insert (zzTest)
public Long zzTestInsert(){
Long result = new Long(0);
try{
myConnection.setAutoCommit(false);
cstmt = myConnection.prepareCall("{ ? = call \"zztest_insert\"(?,?) }");
cstmt.registerOutParameter(1, Types.BIGINT);
cstmt.setLong(2, 22222);
cstmt.setLong(3, 33333);
cstmt.execute();
result = cstmt.getLong(1);
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
} finally {
closeConnection();
}
return result;
}

public Long zzTestInsertSimple(){
Long result = new Long(0);
try{
String sql = "select \"zztest_insert\"(222222, 333333);";
stmt = myConnection.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()){
result = rs.getLong(1);
}
} catch (SQLException e){
System.out.println("SQL Exception: ");
e.printStackTrace();
} finally {
closeConnection();
}
return result;
}
}

Both methods run identically. I had originally been using Statement stmt for all my calls, but due to concerns over SQL Injection, I decided to go with the CallableStatement. Either way, when using a c3p0 connection pool, neither of them work. They actually return the correct ID (from the sequence) but there is no data inserted into the database.

Finally, I call these methods from my Controllers in the following manner

@Override
public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
UserModel umodel = new UserModel(userId, userLoginHistoryId);
Long result = umodel.zzTestInsert();
System.out.println(result);
}

How is the structure of my application? Is it correctly implemented? What improvements can I make?

Most importantly, how can I get these Stored Functions to actually insert values into respective tables? Does Postgres have some sort of security mechanism that restricts table data modification in such an instance? Am I supposed to add some settings to my ComboPooledDataSource?

Any help would be greatly appreciated. Need to sort this out first before I can go continuing with my project.

Best regards,

Shalin.