Interface and inheritance for the Java connection class

Question

Considering the object orientation, would the use of inheritance and interface in this way be correct? But in this way, any request for connection to the database will need a new object. Would have some way to statically access or would need a new object created when initializing the system using Singleton?

Connection interface

public interface ConnectionDB {
    public Connection getConnection();
}

Genericconnection class

public class GenericConnection {

    protected static Properties dbProperties = new Properties();    

    public static Properties getDbProperties() {
        if(dbProperties == null){
            try {
                dbProperties.load(new FileInputStream("src/properties/conf.properties"));
            } catch (FileNotFoundException ex) {
                Logger.getLogger(GenericConnection.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IOException ex) {
                Logger.getLogger(GenericConnection.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return dbProperties;
    }

    public static void setDbProperties(Properties dbProperties) {
        GenericConnection.dbProperties = dbProperties;
    }
}

Oracleconnection class

public class OracleConnection extends GenericConnection implements ConnectionDB {

    private Connection conn;
    private Statement st;

    public Statement getSt() {
        return st;
    }

    public void setSt(Statement st) {
        this.st = st;
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    @Override
    public Connection getConnection() {
        if (getConn() != null) {
            return getConn();
        } else {
            try {
                String url = "jdbc:oracle:thin:@" + getDbProperties().getProperty("ServerOracle") + ":" + getDbProperties().getProperty("portOracle") + ":" + getDbProperties().getProperty("sidOracle");
                setConn(DriverManager.getConnection(url, getDbProperties().getProperty("userOracle"), getDbProperties().getProperty("passwdOracle")));
                setSt(getConn().createStatement());
                return getConn();
            } catch (SQLException ex) {
                Logger.getLogger(OracleConnection.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        }
    }

}

Answer 1

It’s hard to say for certain, some say it’s as certain as possible, others say it’s a waste to have to create new objects all the time.

Particularly I would make a Singleton. But I don’t know the specific need. This class is almost a.

There is a serious error in this implementation since the mother class only has static members, so it will not inherit anything.

Maybe until it was the case of having everything static, but then it would not make sense to use inheritance.

What I think is that this interface is unnecessary. It seems to me that the class GenericConnection should be abstract and contain the method getConnection() without implementation to let the concrete class solve this. I actually think that a good part of what’s in the derivative class could be in the inherited class.

Example:

public abstract class GenericConnection {

    private Connection conn;
    private Statement st;

    public Statement getSt() {
        return st;
    }

    public void setSt(Statement st) {
        this.st = st;
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    protected Properties dbProperties = new Properties();    

    public Properties getDbProperties() {
        if (dbProperties == null) {
            try {
                dbProperties.load(new FileInputStream("src/properties/conf.properties"));
            } catch (FileNotFoundException ex) {
                Logger.getLogger(GenericConnection.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IOException ex) {
                Logger.getLogger(GenericConnection.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return dbProperties;
    }

    public void setDbProperties(Properties dbProperties) {
        GenericConnection.dbProperties = dbProperties;
    }

    public Connection getConnection();
}

public class OracleConnection extends GenericConnection {

    @Override
    public Connection getConnection() {
        if (getConn() != null) {
            return getConn();
        } else {
            try {
                String url = "jdbc:oracle:thin:@" + getDbProperties().getProperty("ServerOracle") + ":" + getDbProperties().getProperty("portOracle") + ":" + getDbProperties().getProperty("sidOracle");
                setConn(DriverManager.getConnection(url, getDbProperties().getProperty("userOracle"), getDbProperties().getProperty("passwdOracle")));
                setSt(getConn().createStatement());
                return getConn();
            } catch (SQLException ex) {
                Logger.getLogger(OracleConnection.class.getName()).log(Level.SEVERE, null, ex);
                return null;
            }
        }
    }
}

I put in the Github for future reference.

Just an improved example, I’m not saying you should use exactly this.

Answer 2

The comment was going to get too big so it turned out to be an answer.

I’m not sure I understand the issue of creating objects all the time, but unless you’re doing batch operations it’s best that every bank operation is done by one Connection different. This is because reusing the same connection for two requested operations in a spaced interval can cause timeouts undesirable.

On the subject of object orientation, I would avoid creating a generic DAO from which other Daos inherit. There is one caelum’s post explaining that it is an unnecessary use of inheritance.

As for how to get the properties strings to create the connections, I would prefer to read them from the file in a separate code and then pass them to a class FabricaDeConexoes via constructor (this class would have a method criarConexao()), and then pass that object fabrica for each DAO also via DAO builder.

Note that I didn’t get into the question of having a pool of connections, which in your case may not even be necessary.

I accept criticism and suggestions.