Getting Java to talk to MySQL

Java is a very powerful,  popular, annoyingly verbose object oriented programming language. 

I don't use Java at work or for personal programming (LAMP stuff does what I need there) but I'm slowly chipping away at my CS degree, and I found myself in the middle of a Java project for which a database was the only non-stupid solution.  Figuring out how to get it to talk to MySQL was a pretty easy process.  I'm documenting my results here.



Importing the Libraries
The needed libraries are in the java.sql.* part of the class heirarchy.  You'll need to simply put:
import java.sql*;
at the top of your code.  You'll also need to either have mysql.jar in your CLASSPATH or add it to your build path.  Eclipse can handle this automatically for you, and I'm willing to bet most other IDEs can do the same.

Connecting to the Database

Creating a connection to the database is quite simple.  You simply need to know the host on which MySQL is running, the name of the database, and the username and password.  The example below illustrates this:

try {
            String host = "127.0.0.1";
            String dbName = "baseOfData";
            String username = "sqlUsername";
            String password = "sqlPassword"; 
            String hostUrl = "jdbc:mysql://" + host + '/' + dbName;
            dbConn = DriverManager.getConnection(hostUrl, username, password );       
        } catch (SQLException ex) {
            //Handle the errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        }
If connecting too the database fails, it will throw an SQLException.  The code in the catch block should print out enough information for you to figure out what went wrong.

Executing Queries
You can prepare and execute queries from Java, just like any other programming language.    Simply building and executing a query looks like this:

PreparedStatement sqlStatement = dbConn.prepareStatement(
                    "select p.id, p.name, p.address, p.city, p.state, p.zip, sum(s.cost) " +
                    "from services_provided sp join providers p on p.id = sp.provider_id " +
                    "join services s on sp.service_id = s.id group by p.id"
                    );
ResultSet results = sqlStatement.executeQuery();

Please note that this (and all examples) should be done in a try block.

Java supports bind variables as well.  To use bind variables you build the query with wildcards, set them, then run the query as shown below:

PreparedStatement sqlStatement = dbConn.prepareStatement(
                "update services set name = ?, cost = ? where id = ?"   
);
sqlStatement.setString(1, serviceName);
sqlStatement.setFloat(2,cost);
sqlStatement.setFloat(3, id);
sqlStatement.execute();

Getting Data Back
Getting data out of the database is quite straight forward as well.   You create a result set from the query output, which you can iterate through.  The results object will have a variety of get methods (getInt, getString, etc) for extracting the needed data as shown below:

PreparedStatement sqlStatement = dbConn.prepareStatement("select * from services");
ResultSet results = sqlStatement.executeQuery();
while (results.next()) {
                Map <String,String> service = new HashMap<String,String>();
                service.put("id", Integer.toString(results.getInt(1)));
                service.put("name", results.getString(2));
                service.put("cost", Float.toString(results.getFloat(3)));
                services.add(service);
}

Conclusion
Getting data in and out of MySQL in Java is pretty much like it is everywhere else, except more heavily object oriented than in most languages I am used to dealing with.  Despite their differences, programming languages are all more or less the same.  This is why I think it is important to focus on concepts, rather than implementations.