Enhancement of Connection Pooling

Question

0.00/5 (No votes)

See more:

Java

package spo.db;

import com.javaexchange.dbConnectionBroker.DbConnectionBroker;
import java.io.*;
import java.sql.*;
import java.util.Properties;

public class DBAccess
    implements Serializable

{

The above code are DBAcess.java. im trying to make modification of those code to improve performance of connection pool. Example of modification are add the detail log, add connection heading, know source of database. How to make those modification?

Java

    public DBAccess()
        throws SQLException
    {
        try
        {
            prop = new Properties();
            prop.load(new FileInputStream("spo.properties"));
            String s = prop.getProperty("db.driver");
            String s1 = prop.getProperty("db.url");
            String s2 = prop.getProperty("db.username");
            String s3 = prop.getProperty("db.password");
            int i = Integer.parseInt(prop.getProperty("db.min"));
            int j = Integer.parseInt(prop.getProperty("db.max"));
            String s4 = prop.getProperty("db.log");
            double d = 0.01D;
            myBroker = new DbConnectionBroker(s, s1, s2, s3, i, j, s4, d);
        }
        catch(IOException ioexception)
        {
            ioexception.printStackTrace();
            System.err.println((new StringBuilder()).append("connection error").append(ioexception).toString());
            System.exit(-1);
        }
    }

    public static Connection getDBConnection()
        throws SQLException
    {
        Connection connection1 = myBroker.getConnection();
        if(connection1 == null)
            throw new SQLException("Cannot allocate connection from database connection pool.");
        else
            return connection1;
    }

    public static void returnDBConnection(Connection connection1)
    {
        myBroker.freeConnection(connection1);
    }

    public static String returnID(Connection connection1)
    {
        int i = myBroker.idOfConnection(connection1);
        return (new StringBuilder()).append(
                "<h3>DbConnectionBroker</h3>Using connection "
                ).append(i).append(" from connection pool<p>").toString();
    }

    public static synchronized int getSeqID(Connection connection1, String s)
        throws SQLException
    {
        int i = Integer.parseInt(prop.getProperty("db.code"));
        String s1 = prop.getProperty("db.seq");
        if(i == 1)
            try
            {
                File file = new File((new StringBuilder()).append(s1).append(s).append(".dat").toString());
                BufferedReader bufferedreader = new BufferedReader(new FileReader(file));
                int j = Integer.parseInt(bufferedreader.readLine());
                j++;
                bufferedreader.close();
                PrintWriter printwriter = new PrintWriter(new FileOutputStream(file));
                printwriter.println(j);
                printwriter.flush();
                printwriter.close();
                return j;
            }
            catch(Exception exception)
            {
                throw new SQLException((
                        new StringBuilder()).append(
                                exception.getMessage()).append(": Cannot get ID from ").append(s).toString());
            }
        String s2 = (new StringBuilder()).append("select ").append(s).append(".nextval from dual").toString();
        Statement statement1 = connection1.createStatement();
        ResultSet resultset = statement1.executeQuery(s2);
        if(!resultset.next())
        {
            throw new SQLException((
                    new StringBuilder()).append("Error when tring to execute: ").append(s2).toString());
        } else
        {
            int k = resultset.getInt(1);
            resultset.close();
            statement1.close();
            return k;
        }
    }

    private static DbConnectionBroker myBroker;
    private Connection connection;
    private Statement statement;
    protected static Properties prop;
}

Posted 2-Oct-11 17:39pm

nazila24

Updated 5-Oct-11 0:20am

Nagy Vilmos

v3

Add a Solution

Comments

nazila24 2-Oct-11 23:41pm

The above code are DBAcess.java. im trying to make modification of those code to improve performance of connection pool. Example of modification are add the detail log, add connection heading, know source of database. How to make those modification?

Nagy Vilmos 5-Oct-11 6:21am

Formatted code for readability.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nagy Vilmos · Accepted Answer · 2011-10-05T00:42:00

I would not expose the real connection from the DbAccess class.
The class has a reference to the broker, so it does not need to hold on to the thread. I don't know how you intend to use the class, but as it is you will hold the database connection even if it is not in use.
In the access class, implement access to the connection. If there is no connection, then each time a command is received a connection is taken from the broker. If a transaction is started, the connection is kept until it is committed or rolled back. A quick play example [not built]:

Java

public class DbAccess {
    private final static DbConnectionBroker broker;
    private Connection connection;
    private int commitCount;
    // ... other stuff ignored
    
    public Static() {
        // set up ONCE for every instance
        // ... config ...
        DbAccess.broker = new DbConnectionBroker(...);
    }
    
    public DbAccess() {
        // initial state:
        this.commitCount = 0;
    }
    
    // note this is private.
    private Connection getConnection() {
        if (this.connection == null) {
            // returns a temporary connection
            // each time it is called:
            return DbAccess.getConnection();
        }
        return this.connection;
    }
    
    public void beginTransaction() {
        // incriment commit count and get a permenant connection
        this.commitCount++;
        if (this.connection == null) {
            this.connection = this.getConnection();
            this.connection.beginTransaction();
        }
    }
    
    public void commit() {
            throws InvalidCommitState {
        if (this.connection == null) {
            // some error
            throw new InvalidCommitState();
        }
        // decrement commit count and release permenant connection if we're at zero
        this.commitCount--;
        if (this.commitCount == 0} {
            this.connection.commit();
            this.broker.releaseConnection(this.connection);
            this.connection = null;
        }
    }

    public void rollback() {
            throws InvalidCommitState {
        if (this.connection == null) {
            // some error
            throw new InvalidCommitState();
        }
        // decrement commit count and release permenant connection if we're at zero
        this.commitCount = 0;
        this.connection.rollback();
        this.broker.releaseConnection(this.connection);
        this.connection = null;
    }
    
    public void execute (Statement statement) {
        this.getConnection().execute(statement);
    }
}

Enhancement of Connection Pooling

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us