Java: OOP DB Connections

[K. Wolfe]

I'm still working on getting my head around this type of design. I might have been a little further along a few years ago in PHP, but some of it has slipped. I know what i have so far can be cleaned up, so by all means point out those mistakes as well.

What I need to do is create a Close() function. I need to close "connection" that resides inside Connect(). I'm thinking that needs defined within database() but I'm not 100% sure. The idea behind what I'm doing is I wasnt to register the driver, make a connection, and be able to return multiple query results using database() before using database.close()

Any help and pointers is much appreciated with this little self teaching exercise of mine.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class Main {
 
    public static void main(String[] argv) {
        database db = new database();
        if (db.registerDriver("oracle.jdbc.driver.OracleDriver") == true) {
            System.out.println("Driver Registered");
            if (db.Connect("jdbc:oracle:thin:@host:port:alias", "user", "pass") == true) {
                System.out.println("Connection made");
            }
        }
 
    }
}
 
class database {
 
    public boolean registerDriver(String sDriver) {
        try {
            Class.forName(sDriver);
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        }
    }
 
    public boolean Connect(String sHost, String sUser, String sPass) {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(sHost, sUser, sPass);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        //Check Connection
        if (connection != null) {
            return true;
        } else {
            return false;
        }
    }
 
}

[wwb_99]

I don't do java, but I do C# whcih is very semantically similar. I think issue is scoping -- your connection is a variable inside the Connect method, it probably should be a class variable or perhaps just returned from the function so the caller can manage the lifecycle.

I'd also advise not catching the exceptions but letting them bubble. If I was writing code against this I'd want exceptions not stacktraces and false returns.

[K. Wolfe]

So in C#, how would that be written do you think so I can give it a try?

[K. Wolfe]

Duh. I just remembered about $this:

class Database {
    private Connection conn;
 
    public boolean registerDriver(String sDriver) {
        try {
            Class.forName(sDriver);
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        }
    }
 
    public void connect(String sHost, String sUser, String sPass) {
        try {
            this.conn = DriverManager.getConnection(sHost, sUser, sPass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    public void close() {
        try {
            this.conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Ahhhh the little victories.

[K. Wolfe]

What do you guys think? Please help me correct anything. My goal is that I can run multiple queries inside the class before closing the connection by calling resultSetClose, once completed, close the connection with dbClose().

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
 
 
class DbConnection {
    private Connection conn;
    private Statement stmt;
    private ResultSet rset;
 
    public boolean registerDriver(String sDriver) {
        try {
            Class.forName(sDriver);
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        }
    }
 
    public void connect(String sHost, String sUser, String sPass) {
        try {
            this.conn = DriverManager.getConnection(sHost, sUser, sPass);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    public ResultSet select(String sql) {
        try {
            this.stmt = this.conn.createStatement();
            this.rset = this.stmt
                .executeQuery(sql);
            return this.rset;
        } catch (SQLException e) {
             e.printStackTrace();
             return null;
        }
    }
 
    public void dbClose() {
        try {
            this.stmt.close();
            this.rset.close();
            this.conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
    public void resultSetClose() {
        try {
            this.stmt.close();
            this.rset.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}