Simple MySQL Database Connection Class

Description:

​

Kudos to my friend David, the owner of UX-Soft, who has came up with this simple MySQL database connection class. I have used it through numerous amount of​

networking projects to store information, and I hope to display it to you guys too.​

​

Code:

Code:

public class DatabaseConnection {
    private static final HashMap<Integer, ConWrapper> connections =
            new HashMap();
    private static String dbDriver, dbUrl, dbUser, dbPass;
    private static boolean propsInited = false;
    private static long connectionTimeOut = 30 * 60 * 60;

    private DatabaseConnection() {}

    public static Connection getConnection() {
        Thread cThread = Thread.currentThread();
        int threadID = (int) cThread.getId();
        ConWrapper ret = connections.get(threadID);

        if (ret == null) {
            Connection retCon = connectToDB();
            ret = new ConWrapper(retCon);
            connections.put(threadID, ret);
            System.err.println("[Database] Thread [" + threadID + "] has created a new Database Connection.");
        }

        return ret.getConnection();
    }

    private static Connection connectToDB() {
        if (!propsInited) {
            PropertyReader dbReader;

            try {
                dbReader = PropertyReader.load("db.cfg");
            } catch (IOException ex) {
                throw new DatabaseException(ex);
            }

            dbDriver = dbReader.getProperty("driver");
            dbUrl = dbReader.getProperty("url");
            dbUser = dbReader.getProperty("user");
            dbPass = dbReader.getProperty("password");
            propsInited = true;
        }

        try {
            Class.forName(dbDriver);    // touch the MySQL driver
        } catch (ClassNotFoundException e) {
            throw new DatabaseException(e);
        }

        try {
            Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);
            return con;
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
    }

    static class ConWrapper {
        private long lastAccessTime;
        private Connection connection;

        public ConWrapper(Connection con) {
            this.connection = con;
        }

        public Connection getConnection() {
            if (expiredConnection()) {
                try { // Assume that the connection is stale
                    connection.close();
                } catch (SQLException err) {
                    // Who cares
                }
                this.connection = connectToDB();
            }

            lastAccessTime = System.currentTimeMillis(); // Record Access
            return this.connection;
        }

        /**
         * Returns whether this connection has expired
         * @return
         */
        public boolean expiredConnection() {
            return System.currentTimeMillis() - lastAccessTime >= connectionTimeOut;
        }
    }

    public static void closeAll() throws SQLException {
        for (ConWrapper con : connections.values()) {
            con.connection.close();
        }
    }
}

Customization:

Feel free to modify the MySQL Properties configuration reading method to suit your own, or feel free to link it up to another method.

Usage:

Code:

Connection con = DatabaseConnection.getConnection(); // get connection
PreparedStatement ps = con.prepareStatement("SELECT * FROM thisisatable WHERE someid = ?"); // prepare query
ps.setInt(1, 1); // This is a madeup table and variable!
ResultSet rs = ps.executeQuery(); // execute query
// do some random resultset interpreting here
while (rs.next()) {
    System.out.println("We have found a value Mr. Robertson! " + rs.getInt("sometablevaluefromtableid"));
}
ps.close();
rs.close();
// This example has been done very poorly.