Connecting to MySQL with Java

I wanted to go over Database connections with Java, as it is something I've seen come up quite a bit, and it's really quite easy.  My intent is to actually go into detail on what actually happens here, so if all you're interested in is some boiler-plate code to get yourself connected, feel free to stop with the code listed below.  The only things to bear in mind are that I am using Java7 SE syntax for the catch block, and also that this method is tailored for the return of a single line result from the database.  Replace the if with a while loop ( and associated extra code ) if you want to loop through more than a single record

The following method is called supplying an SQL statement as a string, i.e.:  SQLSingleQueryDB("SELECT * FROM products LIMIT 1");  The result is returned as a String:

    private String SQLSingleQueryDB( String query ) {
        String result = null;
        
        try {
              Class.forName("com.mysql.jdbc.Driver").newInstance();
              Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/specific_database", "root", "");
              Statement stmt = (Statement)con.createStatement();
              ResultSet rs = stmt.executeQuery(query);
            
              if ( rs.next() ) result = rs.getString("modelLine");
              
        } catch ( SQLException | ClassNotFoundException | InstantiationException | IllegalAccessException e ) {
              JOptionPane.showMessageDialog( this, e.getMessage() );
        }
        return result;
    }

Now, let's pick apart the different statements, and see what they do

Class.forName("com.mysql.jdbc.Driver").newInstance();

Both of these methods are found under java.lang.Class, which is a subclass of java.lang.Object.

forName("com.mysql.jdbc.Driver")

Returns the Class Object associated with the class or interface with the given string name. So, in this case, the Class Object of "com.mysql.jdbc.Driver" is returned. Upon this returned class object, we're calling:

newInstance()

Which creates a new instance of the class represented by the class object on which it's called ( here, Driver ).  So, in short, we're basically instantiating the Driver of java.sql.* that is associated with MySQL.

So, we've essentially got our MySQL Driver set up, now we need a connection to our MySQL Database, which is handled by Connection, and the DriverManager class ( both members of java.sql ).  So in the next line:

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/specific_database", "root", "");

we are creating a variable of type Connection, and initializing it from our previously instantiated Driver through the DriverManager. It breaks down like this:

Connection is an Interface ( of java.sql ) that handles a connection (session) with a specific database. All our SQL statements that are executed, and the results that are returned, happen within the context of a connection. We initialize our connection by giving it a connection obtained through whatever database Driver we are using for our particular database. In this case, it's MySQL. The DriverManager handles whatever drivers we happen to have loaded, by selecting an appropriate driver from the set of registered JDBC drivers. DriverManager has a method,

getConnection( String url, String user, String password) throws SQLException

that attempts to establish a connection to the given database URL.  So, we feed it the connection url to our database, "jdbc:mysql://localhost:3306/specific_database" ( if your database is on a different server than localhost, make the necessary change.  As well, replace "specific_database" with whatever database name you want to connect to ), our MySQL User, in this case "root", and our MySQL User Password, in this case no password.  The return type of getConnection() is a Connection type.

So, at this point, we've successfully set up our driver, and our connection.  What we need next is to set up our SQL Statement that we want to send to our database.  This is taken care of via the java.sql Statement:

Statement stmt = con.createStatement();

The java.sql package's Connection interface offers a createStatement() method:

Statement createStatement() throws SQLException

that create's a Statement object for sending SQL statements to the database.  SQL statements without parameters are normally executed using Statement objects.  If the same statement is to be executed many times, it might be more efficient to instead utilize a PreparedStatement object.  Anyway, by calling this method on the previously created Connection object, we are returned a Statement object.

Now, we can take this statement object, and fill it with an SQL query using the Statement Interface's executeQuery() method:

ResultSet executeQuery( String sql ) throws SQLException

which as you can see returns a ResultSet type, which brings us to our next line of code:

ResultSet rs = stmt.executeQuery(query);

The ResultSet Interface ( of java.sql ) represents a table of data from our databases result set. The ResultSet object maintains a cursor pointing to its current row of data. Initially, this cursor is positioned before the first row. The next() method moves the cursor to the next row, and it returns false when there are no more rows, so it can be used in a while loop to iterate through the result set... though we're not using it for that here. Getting back on point, that last line of code injected our SQL query, that we sent into our method, into the Statement object's executeQuery() method, and the return result from that query being executed was of type ResultSet. The result from the query ( now in our ResultSet object ) is ready to be examined, which brings us to the following line of code in our method:

if ( rs.next() ) result = rs.getString("modelLine");

The above line essentially says, of course, if there is any data in our result set, then ( if ) there is a column labeled, "modelLine" in the returned data row, assign its contents to our 'result' variable. I'll break this down just a bit further.

We sent an SQL query into our method ( at the beginning of this post ).  Let's say that SQL was, "SELECT * FROM products LIMIT 1".  Let's also say that our products table has a column called, "modelLine", that lists the various model's of goods we sell.  Imagine that under the column "modelLine" we have the model "gizmo" listed, and the row with our "gizmo" model was returned by our SQL query.  So what we have is, within our ResultSet variable, rs, a row of data from our database with the various columns and their associated data, one of which is the column "modelLine", and its associated field, "gizmo".  In the line of code above, our if statement checks rs.next() and it returns true, as we did in fact receive a line of data back.  It then runs the associated line of code that will assign variable 'result' the "modelLine" column's field of data, "gizmo".  So, variable result now holds "gizmo", and it is returned from the method.  The ResultSet Interface actually has a number of methods for retrieving data from a result set.  The method being used here, getString( String columnLabel ) is joined by many others for getting at various types of data, and also for checking rows as well as columns.  Have a look at the API ( linked at the bottom of this post ).

The catch block is Java 7 SE style, which allows all the various kinds of errors that might be thrown to be on a single line, the only thing new there is the Java 7 syntax.

So, this conclude's a walk through the various API calls involved in a basic SQL connection with Java.  Have a look at the API.  There's a whole lot more that can be done.

http://docs.oracle.com/javase/7/docs/api/