I just want to share this simple derby database helper class that I've been using on my small jsp (Java Server Pages) project. This is just really simple, something that just simplifies the loading of database driver, establishing connection and some query helper methods.

So here's the code:

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author marconi
 */
public class Database {

    private String db_url  = null;
    private String db_user = null;
    private String db_pass = null;

    private Statement stmt = null;
    private Connection conn = null;
    private ResultSet rs = null;

    public Database(String url, String uname, String pword) {
        db_url = url;
        db_user = uname;
        db_pass = pword;
        init_connection();
    }

    private void init_connection() {
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
        } catch (ClassNotFoundException cnfe) {
            System.out.println("Driver error: " + cnfe);
        }
        try {
            conn = DriverManager.getConnection(db_url, db_user, db_pass);
            stmt = conn.createStatement();
        } catch (SQLException sqle) {
            System.out.println(sqle);
        }
    }

    public ResultSet query(String sql) {
        try {
            rs = stmt.executeQuery(sql);
        } catch (SQLException sqle) {
            System.out.println("Error in query method: " + sqle);
        }
        return rs;
    }

    public int execute(String sql) {
        int result = 0;
        try {
            result = stmt.executeUpdate(sql);
        } catch (SQLException sqle) {
            System.out.println("Error in execute method: " + sqle);
        }
        return result;
    }

    public int get_last_id(String table) {
        int id = 0;
        try {
            rs = stmt.executeQuery("select MAX(id) as id from " + table);
            rs.next();
            id = rs.getInt("id");
        } catch (SQLException sqle) {
            System.out.println("Error in get_last_id method: " + sqle);
        }
        return id;
    }

}

I might post the app for download when its done, but for now I'm having weird feelings about jsp's response.sendRedirect(). It seems that it doesn't actually stops the execution there but continues to load the succeeding statements instead. I already have a quick hack but its kinda ugly.

For SASN students: Derby's auto-increment works like this, GENERATED ALWAYS AS IDENTITY(start with 1, increment by 1)