[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 919
  • Last Modified:

Jdbc JAva Oracle connection code required

Hi

I am making a standalone app where i need to make a connection manager type of class from where all the java classes would get a connection object.
1) i need code for this class such that it has to show singleton behaviour,
2) read all the connection properties from an properties file
3)this has to done for oracle database

any sample code for this class and properties file is highly required, i am very new to this stuff,
please help.

and dont dont need to make a connection pool,because only one user at a time could use this app
0
manojvashisht
Asked:
manojvashisht
  • 6
  • 5
  • 4
  • +1
7 Solutions
 
Gibu GeorgeCTOCommented:
public Singleton{
 private static Singleton singleton;
 protected Singleton(){
  //write the connection logic
}
public static Singleton getInstance(){
 if(singleton==null){
   singleton=new Singleton();
}
return singleton
}
}
0
 
Gibu GeorgeCTOCommented:
to read the db connection details from properties file use the load() method of the Properties class
http://java.sun.com/javase/6/docs/api/

for oracle connection related things check this out
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-jdbc.html
you need to have the oracle jdbc driver jar file in the class path
0
 
CEHJCommented:
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
manojvashishtAuthor Commented:
do you have a working code

how to use the load method ............. any example
what shud be the structure of properties file?

i am very new to all this jdbc and oracle stuff would really appreciate if any of u experts could extend help.


thanks
0
 
Gibu GeorgeCTOCommented:
the structure of the properties file should be
db_user_name=username
db_user_password=password
db_host=dbhost
db_port=1521
db_name=orcl
should be stored as a db.properties file

Instead of properties file use java.util.ResourceBundle
usage is

ResourceBundle rBundle=ResourceBundle.getbundle("db");
rBundle.getString("db_name") will give you orcl
0
 
manojvashishtAuthor Commented:

you mean i shud firstly read the properties file and store the parameters in string values.

and then put in those details to register driver firstly and then get connections.

but i am confused on one thing how to make singleton instance.

i have attached code for reading prop file and also the steps to perform afetr that

wud the same work just treat it as example the try block code i mean
 /**
     * Loads properties and initializes the instance with its values.
     */
    private void init() {
        InputStream is = getClass().getResourceAsStream("/com/mantas/investmigrationUtility/database/utility/db.properties");
        Properties dbProps = new Properties();
        try {
            dbProps.load(is);
        }
        catch (Exception e) {
        	e.printStackTrace();
            System.err.println("Can't read the properties file. " +
                "Make sure install.cfg is in the CLASSPATH");
            return;
        }
        String logFile = dbProps.getProperty("logfile", "DBConnectionManager.log");
        try {
            log = new PrintWriter(new FileWriter(logFile, true), true);
        }
        catch (IOException e) {
            System.err.println("Can't open the log file: " + logFile);
            log = new PrintWriter(System.err);
        }
       }
 
 
try {
        // Load the JDBC driver
        String driverName = "oracle.jdbc.driver.OracleDriver";
 
        Class.forName(driverName);
    
        // Create a connection to the database
        String serverName = "127.0.0.1";
        String portNumber = "1521";
        String sid = "mydatabase";
        String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
        String username = "username";
        String password = "password";
        connection = DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException e) {
        // Could not find the database driver
    } catch (SQLException e) {
        // Could not connect to the database
    }

Open in new window

0
 
Gibu GeorgeCTOCommented:
I though you wanted to load the db connection details from properties file
0
 
Gibu GeorgeCTOCommented:
Here is an example of singleton implementation
http://www.theserverside.com/discussions/thread.tss?thread_id=31650
0
 
manojvashishtAuthor Commented:
no man i want to load it from properties file the try block code is just sample...............

i am reading props from init method.

then what shud i do store all the parsed values into string values like this

String user = props.getProperty(poolName + ".user");
String password = props.getProperty(poolName + ".password");
String maxconn = props.getProperty(poolName + ".maxconn", "0");


 Enumeration propNames = props.propertyNames();
        while (propNames.hasMoreElements()) {
            String name = (String) propNames.nextElement();
            if (name.endsWith(".url")) {
                String poolName = name.substring(0, name.lastIndexOf("."));
                String url = props.getProperty(poolName + ".url");
                if (url == null) {
                    log("No URL specified for " + poolName);
                    continue;
                }
                String user = props.getProperty(poolName + ".user");
                String password = props.getProperty(poolName + ".password");
                String maxconn = props.getProperty(poolName + ".maxconn", "0");
                int max;
                try {
                    max = Integer.valueOf(maxconn).intValue();
                }
                catch (NumberFormatException e) {
                    log("Invalid maxconn value " + maxconn + " for " + poolName);
                    max = 0;
                }
                DBConnectionPool pool =
                    new DBConnectionPool(poolName, url, user, password, max);
                pools.put(poolName, pool);
                log("Initialized pool " + poolName);
            }


Now here a db connection pool is initialised but i dont want this i want just simple single connection

suggest how same cud be done
0
 
CEHJCommented:
>>but i am confused on one thing how to make singleton instance.

Create a connection pool using the example i showed you and make the pool a static variable. That will give you a singleton
0
 
manojvashishtAuthor Commented:
the thing is this code is creating a pool but i dont need that as well,just simple single connection when required.

would it do the same
0
 
CEHJCommented:
You can do the same with a simple Connection object although it's better to use a connection pool
0
 
manojvashishtAuthor Commented:
i just want to know i have a working code now where a connection db pool is made and data is getting retreived.

i am attaching the code but problem is i never would require more than one connection at a time and so there is no point in having a pool of connections being very new to this i am not able to figure out the impact and where to change so to get a dynamic application


this is the code which i ma using and it is working.

can u give  a brief idea whether this approach is ok as my app wud never work in a multiuser environment.

/**
 * 
 */
package com.mantas.investMigrationUtility.database.utility;
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.Date;
/**
 * @author manoj.vashisht
 *
 */
 
 
/**
 * This class is a Singleton that provides access to one or many
 * connection pools defined in a Property file. A client gets
 * access to the single instance through the static getInstance()
 * method and can then check-out and check-in connections from a pool.
 * When the client shuts down it should call the release() method
 * to close all open connections and do other clean up.
 */
public class DBConnectionManager {
    static private DBConnectionManager instance;       // The single instance
    static private int clients;
   
    private Vector drivers = new Vector();
    private PrintWriter log;
    private Hashtable pools = new Hashtable();
   
    /**
     * Returns the single instance, creating one if it's the
     * first time this method is called.
     *
     * @return DBConnectionManager The single instance.
     */
    static synchronized public DBConnectionManager getInstance() {
        if (instance == null) {
            instance = new DBConnectionManager();
        }
        clients++;
        return instance;
    }
   
    /**
     * A private constructor since this is a Singleton
     */
    private DBConnectionManager() {
        init();
    }
   
    /**
     * Returns a connection to the named pool.
     *
     * @param name The pool name as defined in the properties file
     * @param con The Connection
     */
    public void freeConnection(String name, Connection con) {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null) {
            pool.freeConnection(con);
        }
    }
       
    /**
     * Returns an open connection. If no one is available, and the max
     * number of connections has not been reached, a new connection is
     * created.
     *
     * @param name The pool name as defined in the properties file
     * @return Connection The connection or null
     */
    public java.sql.Connection getConnection(String name) {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null) {
            return pool.getConnection();
        }
        return null;
    }
   
    /**
     * Returns an open connection. If no one is available, and the max
     * number of connections has not been reached, a new connection is
     * created. If the max number has been reached, waits until one
     * is available or the specified time has elapsed.
     *
     * @param name The pool name as defined in the properties file
     * @param time The number of milliseconds to wait
     * @return Connection The connection or null
     */
    public java.sql.Connection getConnection(String name, long time) {
        DBConnectionPool pool = (DBConnectionPool) pools.get(name);
        if (pool != null) {
            return pool.getConnection(time);
        }
        return null;
    }
   
    /**
     * Closes all open connections and deregisters all drivers.
     */
    public synchronized void release() {
        // Wait until called by the last client
        if (--clients != 0) {
            return;
        }
       
        Enumeration allPools = pools.elements();
        while (allPools.hasMoreElements()) {
            DBConnectionPool pool = (DBConnectionPool) allPools.nextElement();
            pool.release();
        }
        Enumeration allDrivers = drivers.elements();
        while (allDrivers.hasMoreElements()) {
            Driver driver = (Driver) allDrivers.nextElement();
            try {
                DriverManager.deregisterDriver(driver);
                log("Deregistered JDBC driver " + driver.getClass().getName());
            }
            catch (SQLException e) {
                log(e, "Can't deregister JDBC driver: " + driver.getClass().getName());
            }
        }
    }
   
    /**
     * Creates instances of DBConnectionPool based on the properties.
     * A DBConnectionPool can be defined with the following properties:
     * <PRE>
     * &lt;poolname&gt;.url         The JDBC URL for the database
     * &lt;poolname&gt;.user        A database user (optional)
     * &lt;poolname&gt;.password    A database user password (if user specified)
     * &lt;poolname&gt;.maxconn     The maximal number of connections (optional)
     * </PRE>
     *
     * @param props The connection pool properties
     */
    private void createPools(Properties props) {
        Enumeration propNames = props.propertyNames();
        while (propNames.hasMoreElements()) {
            String name = (String) propNames.nextElement();
            if (name.endsWith(".url")) {
                String poolName = name.substring(0, name.lastIndexOf("."));
                String url = props.getProperty(poolName + ".url");
                if (url == null) {
                    log("No URL specified for " + poolName);
                    continue;
                }
                String user = props.getProperty(poolName + ".user");
                String password = props.getProperty(poolName + ".password");
                String maxconn = props.getProperty(poolName + ".maxconn", "0");
                int max;
                try {
                    max = Integer.valueOf(maxconn).intValue();
                }
                catch (NumberFormatException e) {
                    log("Invalid maxconn value " + maxconn + " for " + poolName);
                    max = 0;
                }
                DBConnectionPool pool =
                    new DBConnectionPool(poolName, url, user, password, max);
                pools.put(poolName, pool);
                log("Initialized pool " + poolName);
            }
        }
    }
   
    /**
     * Loads properties and initializes the instance with its values.
     */
    private void init() {
        InputStream is = getClass().getResourceAsStream("/database/utility/db.properties");
        Properties dbProps = new Properties();
        try {
            dbProps.load(is);
        }
        catch (Exception e) {
        	e.printStackTrace();
            System.err.println("Can't read the properties file. " +
                "Make sure install.cfg is in the CLASSPATH");
            return;
        }
        String logFile = dbProps.getProperty("logfile", "DBConnectionManager.log");
        try {
            log = new PrintWriter(new FileWriter(logFile, true), true);
        }
        catch (IOException e) {
            System.err.println("Can't open the log file: " + logFile);
            log = new PrintWriter(System.err);
        }
        loadDrivers(dbProps);
        createPools(dbProps);
    }
   
    /**
     * Loads and registers all JDBC drivers. This is done by the
     * DBConnectionManager, as opposed to the DBConnectionPool,
     * since many pools may share the same driver.
     *
     * @param props The connection pool properties
     */
    private void loadDrivers(Properties props) {
        String driverClasses = props.getProperty("utils.database.drivers");
        StringTokenizer st = new StringTokenizer(driverClasses);
        while (st.hasMoreElements()) {
            String driverClassName = st.nextToken().trim();
            try {
                Driver driver = (Driver)
                    Class.forName(driverClassName).newInstance();
                DriverManager.registerDriver(driver);
                drivers.addElement(driver);
                log("Registered JDBC driver " + driverClassName);
            }
            catch (Exception e) {
                log("Can't register JDBC driver: " +
                    driverClassName + ", Exception: " + e);
            }
        }
    }
   
    /**
     * Writes a message to the log file.
     */
    private void log(String msg) {
        log.println(new Date() + ": " + msg);
    }
   
    /**
     * Writes a message with an Exception to the log file.
     */
    private void log(Throwable e, String msg) {
        log.println(new Date() + ": " + msg);
        e.printStackTrace(log);
    }
   
    /**
     * This inner class represents a connection pool. It creates new
     * connections on demand, up to a max number if specified.
     * It also makes sure a connection is still open before it is
     * returned to a client.
     */
    class DBConnectionPool {
        private int checkedOut;
        private Vector freeConnections = new Vector();
        private int maxConn;
        private String name;
        private String password;
        private String URL;
        private String user;
       
        /**
         * Creates new connection pool.
         *
         * @param name The pool name
         * @param URL The JDBC URL for the database
         * @param user The database user, or null
         * @param password The database user password, or null
         * @param maxConn The maximal number of connections, or 0
         *   for no limit
         */
        public DBConnectionPool(String name, String URL, String user, String password,
                int maxConn) {
            this.name = name;
            this.URL = URL;
            this.user = user;
            this.password = password;
            this.maxConn = maxConn;
        }
       
        /**
         * Checks in a connection to the pool. Notify other Threads that
         * may be waiting for a connection.
         *
         * @param con The connection to check in
         */
        public synchronized void freeConnection(Connection con) {
            // Put the connection at the end of the Vector
            freeConnections.addElement(con);
            checkedOut--;
            notifyAll();
        }
       
        /**
         * Checks out a connection from the pool. If no free connection
         * is available, a new connection is created unless the max
         * number of connections has been reached. If a free connection
         * has been closed by the database, it's removed from the pool
         * and this method is called again recursively.
         */
        public synchronized java.sql.Connection getConnection() {
            java.sql.Connection con = null;
            if (freeConnections.size() > 0) {
                // Pick the first Connection in the Vector
                // to get round-robin usage
                con = (java.sql.Connection) freeConnections.firstElement();
                freeConnections.removeElementAt(0);
                try {
                    if (con.isClosed()) {
                        log("Removed bad connection from " + name);
                        // Try again recursively
                        con = getConnection();
                    }
                }
                catch (SQLException e) {
                    log("Removed bad connection from " + name);
                    // Try again recursively
                    con = getConnection();
                }
            }
            else if (maxConn == 0 || checkedOut < maxConn) {
                con = newConnection();
            }
            if (con != null) {
                checkedOut++;
            }
            return con;
        }
       
        /**
         * Checks out a connection from the pool. If no free connection
         * is available, a new connection is created unless the max
         * number of connections has been reached. If a free connection
         * has been closed by the database, it's removed from the pool
         * and this method is called again recursively.
         * <P>
         * If no connection is available and the max number has been
         * reached, this method waits the specified time for one to be
         * checked in.
         *
         * @param timeout The timeout value in milliseconds
         */
        public synchronized java.sql.Connection getConnection(long timeout) {
            long startTime = new Date().getTime();
            java.sql.Connection con;
            while ((con = getConnection()) == null) {
                try {
                    wait(timeout);
                }
                catch (InterruptedException e) {}
                if ((new Date().getTime() - startTime) >= timeout) {
                    // Timeout has expired
                    return null;
                }
            }
            return con;
        }
       
        /**
         * Closes all available connections.
         */
        public synchronized void release() {
            Enumeration allConnections = freeConnections.elements();
            while (allConnections.hasMoreElements()) {
                java.sql.Connection con = (java.sql.Connection) allConnections.nextElement();
                try {
                    con.close();
                    log("Closed connection for pool " + name);
                }
                catch (SQLException e) {
                    log(e, "Can't close connection for pool " + name);
                }
            }
            freeConnections.removeAllElements();
        }
       
        /**
         * Creates a new connection, using a userid and password
         * if specified.
         */
        private java.sql.Connection newConnection() {
            java.sql.Connection con = null;
            try {
                if (user == null) {
                    con = DriverManager.getConnection(URL);
                }
                else {
                    con = DriverManager.getConnection(URL, user, password);
                }
                log("Created a new connection in pool " + name);
            }
            catch (SQLException e) {
                log(e, "Can't create a new connection for " + URL);
                return null;
            }
            return con;
        }
    }
}

Open in new window

0
 
CEHJCommented:
>>can u give  a brief idea whether this approach is ok as my app wud never work in a multiuser environment.

It's fine. The pool can contain as many or few connections as you want. The pool is not only there for the benefit of multiuser support, it's also there for multi-threading support
0
 
manojvashishtAuthor Commented:
thanks for ur response but just one thing i want to know that i dont even will support multi threading.........

so is it still make sense that i use this code?

bcoz someone has told me that there is no need of apool as a single user and single thread wud be running everytime i use this
0
 
objectsCommented:
from what you have said you don't need a connection pool, lots of unecessary overhead.
sounds like a single connection will be fine, don't overcomplicate your application


0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now