Database Interface
Database Connections in Java can be somewhat of a tricky business, with lots of room for nasty errors. Also, you need to have the database driver for your particular database installed in your java classpath before you can do anything, and pretty much anything you do needs to be wrapped in a try/catch loop.
Connecting to the Database
Before you can connect to the database, you need to register the Database Driver. I'm still not quite sure what exactly this does, I just know that you need to do it before you do anything else.
try {Class.forName("com.mysql.jdbc.Driver");}
catch (Exception e){ e.printStackTrace();}
Next you want to creat your connection URL. The URL is a standardized string which describes what your connecting to and how you're connecting, the form of the url is :
jdbc:database_driver://hostname:port_number/database_table
So lets create the URL and use it to get a connection. Note that we create a null connection object outside of the try/catch loop in order to avoid compile errors :
Connection con = null;
String url = "jdbc:mysql://somehost.somewhere.com:3306/databasetable";
try { con = DriverManager.getConnection(url, "username", "password");}
catch(Exception e){e.printStackTrace();
Next step is to create a statement object which you use to submit queries to the database, when you create a statement object you can pass various options to the constructor. The option TYPE_SCROLL_INSENSITIVE means that we can go forwards and backward through any returned data, by default you can only go one direction. The CONCUR_READ_ONLY option means that if there are concurrent database connections to the same database table, you can't change the table data :
try { stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY); }
catch(Exception e){e.printStackTrace();
Retrieving from Database
Once you have a statement object, you can submit queries to the database. The first thing you want to do is to create MySQL query string, and a null ResultSet Object :
String q = "SELECT * FROM Data";
ResultSet rs = null;
Once that's done, use the statement object to execute the query, and get the resultset :
try {
//execute the statement
stmt.execute(q);
//get the result set and return it
rs = stmt.getResultSet();
}
catch(Exception e){
System.out.println("Query failed");
e.printStackTrace();
}
Assuming that you were sucessful in getting a resultset object, you can access the rows through the resultsets next() function. This returns a boolean which is false once there is no more data :
try {
while(rs.next()){
System.out.println("Text Column" + rs.getString("text"));
System.out.println("Number Column" + rs.getInt("number"));
} catch(Exception e){e.printStackTrace();}}
Writing to the Database
Writing to the database is accomplished in pretty much the same manner, you build the query and then execute it :
String q = "INSERT INTO Data VALUES(NULL, 'String Value', 33)";
int id = -1;
try {
//execute the statement
stmt.execute(q);
If you want to get the autoinsertID for the newly created object, you can query the database for the last insert id, and retrieve it from the resultset object :
//get the id of the created object
stmt.execute("SELECT LAST_INSERT_ID()");
//get the result set
ResultSet f = stmt.getResultSet();
//get the ID
while(f.next()){id = f.getInt(1);}
return(id);
}
catch(Exception e){
e.printStackTrace();
System.out.println("Unable to execute query : " + q);
return(id);
}
