The two full length programs that follow assumes you are on the z server and that you have a table called Name in your database. Check the MySQL Tutorial for information on how you can create this table.

The SimpleJDBC program just tests that you can connect to the MySQL database. Make sure that you can do this before you run more complex programs. Also change the database name, user name, and password to the values that are appropriate for your case. To prevent others from accessing your database password change permission on your file:

chmod 600 SimpleJDBC.java

import java.sql.*;

public class SimpleJDBC
{
  public static void main (String[] args) 
  {
    try
    {
      // load the JDBC driver
      Class.forName("com.mysql.jdbc.Driver");
      System.out.println ("Driver loaded");

      // Establish a connection
      Connection connect = DriverManager.getConnection
      ("jdbc:mysql://z.cs.utexas.edu:3306/dbName", "userName", "password"); 
      System.out.println ("Database connected");

      // Create a statement
      Statement statement = connect.createStatement();

      // Execute a statement
      ResultSet resultSet = statement.executeQuery ("select * from Name");

      // Iterate through the result and print
      while (resultSet.next())
      {
        System.out.println (resultSet.getString(1) + "  " +
	                    resultSet.getString(2));
      }

      // Close the connection
      connect.close();
    }
    catch (Exception e)
    {
      System.out.println (e.getMessage());
      e.printStackTrace();
      System.exit(0);
    }
  }
}

When you compile your program you must include the path to mysql.jar file. You can do this on the command line:

CLASSPATH=$CLASSPATH:/usr/share/java/mysql.jar java SimpleJDBC

echo $SHELL

For BASH users place this line in your .profile or .bash_profile file:

export "${CLASSPATH}:/usr/share/java/mysql.jar"

For T-SHELL or C-SHELL users place this line in your .cshrc file:

setenv CLASSPATH .:/usr/share/java/mysql.jar

The program JDBCPlus.java shows you how to create a table and insert values in it. The values are read from a file called names.txt. I would not recommend creating a table this way. This program deletes the table everytime it runs and recreates. Before you run your program make sure that your CLASSPATH has been set properly.

import java.sql.*;
import java.util.*;
import java.io.*;

public class JDBCPlus
{
  public static void main (String[] args) 
  {
    try
    {
      // load the JDBC driver
      Class.forName("com.mysql.jdbc.Driver");
      System.out.println ("Driver loaded");

      // Establish a connection
      Connection connect = DriverManager.getConnection
      ("jdbc:mysql://z.cs.utexas.edu:3306/dbName", "userName", "password");
      System.out.println ("Database connected");

      // Create a statement
      Statement statement = connect.createStatement();

      // Create a ResultSet reference variable
      ResultSet rSet = null;

      // Get MetaData
      DatabaseMetaData dbMetaData = connect.getMetaData();

      // Get Tables
      String[] types = {"TABLE"};
      ResultSet rsTables = dbMetaData.getTables(null, null, null, types);

      // Check if Table exists
      boolean foundTable = false;
      while (rsTables.next())
      {
        String tableName = rsTables.getString("TABLE_NAME");
        if (tableName.equalsIgnoreCase("Name"))
	{
          foundTable = true;
	  break;
	}
      }

      // Drop table Name only if it exists
      int result;
      if (foundTable)
      {
        result = statement.executeUpdate ("drop table Name");
      }

      // Create table Name
      String create_table = "create table Name " +
                            "(FirstName varchar(25), " +
			    "LastName varchar(25))";
      result = statement.executeUpdate (create_table);

      // Open data file
      File inFile = new File ("./names.txt");
      Scanner sc = new Scanner (inFile);

      // Create a Prepared Statement
      String insert_stmt = "insert into Name (FirstName, LastName) " +
                           "values (?, ?)";
      PreparedStatement dataEntry = connect.prepareStatement(insert_stmt);

      // Read data from file and insert into table
      while (sc.hasNextLine())
      {
        String line = sc.nextLine();
	String[] names = line.split ("\\s+");
	dataEntry.setString (1, names[0]);
	dataEntry.setString (2, names[1]);
	result = dataEntry.executeUpdate();
      }

      // Close file
      sc.close();

      // Check if the data was entered in the table
      System.out.println();

      // Execute a search statement
      ResultSet resultSet = statement.executeQuery ("select * from Name");

      // Iterate through the result and print
      while (resultSet.next())
      {
        System.out.println (resultSet.getString(1) + "  " +
	                    resultSet.getString(2));
      }

      // Create a Prepared Statement for Update
      String update_stmt = "update Name set FirstName = ? " +
                           "where LastName = ?";
      PreparedStatement dataUpdate = connect.prepareStatement(update_stmt);

      // Insert values into Update Statement and Execute
      dataUpdate.setString (1, "Lev");
      dataUpdate.setString (2, "Tolstoy");
      result = dataUpdate.executeUpdate();

      // Check if data was updated
      System.out.println ("\n" + "Checking if update was made");
      resultSet = statement.executeQuery ("select * from Name");
      while (resultSet.next())
      {
        System.out.println (resultSet.getString(1) + "  " +
	                    resultSet.getString(2));
      }

      // Close the connection
      connect.close();
    }
    catch (Exception e)
    {
      System.out.println (e.getMessage());
      e.printStackTrace();
      System.exit(0);
    }
  }
}