Connecting to MySQL via Java

The Web server ozark is also configured as a database server using MySQL. You can write Java programs to access it using Java's database API, called JDBC. As an example of how to use JDBC as it's installed here, we'll use the ListStates program listed at the bottom of this page.

• If you are using the command line, you can issue the following two commands. (The javadb command is a specialized script for simplifying the task of setting up JDBC before starting the Java Virtual Machine.)

  linux% javac ListStates.java
  linux% javadb ListStates
  

• If you are using Eclipse, you can configure a project to use JDBC by right-clicking the project in the Package Explorer (normally the left side of the Eclipse window) and selecting Properties. In the Properties window, select Java Build Path on the left, then select the Libraries tab, then click Add External JARs…. Navigate into /export/shared/lib/mysql and select mysql-connector-java-5.1.5-bin.jar. You should then be able to execute the program as normal within Eclipse.

Note that MySQL is configured to allow connections only from within the Linux network.

An example JDBC program

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

public class ListStates {
    /* DataBase account info for MySQL */
    private static final String URL = "jdbc:mysql://ozark";
    private static final String DATABASE = "census";
    private static final String USERNAME = "jdbc";
    private static final String PASSWORD = "warriors";

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch(ClassNotFoundException e) {
            System.err.println("Exception in static initializer:");
            e.printStackTrace();
        }
    }

    private static ResultSet execSql(String cmd) {
        try {
            Connection conn = DriverManager.getConnection(URL
                + "/" + DATABASE, USERNAME, PASSWORD);
            Statement stat = conn.createStatement();
            boolean hasResultSet = stat.execute(cmd);
            if(hasResultSet) {
                return stat.getResultSet();
            }
        } catch(SQLException e) {
            System.err.println("Exception in execSql:");
            e.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {
        try {
            ResultSet results = execSql("SELECT name FROM States");
            if(results != null) {
                while(results.next()) {
                    System.out.println(results.getString(1));
                }
            }
        } catch(SQLException e) {
            System.err.println("Exception in main:");
            e.printStackTrace();
        }
    }
}