SIMS 255: Lab 9: Database Tutorial

Thursday, November 20, 2003

Connecting to Your Database on Dream

To connect to your database from the Unix command line:
  1. Use SecureCRT or some other program to login to dream.sims.berkeley.edu

  2. At the 'Dream%' prompt, type:

    mysql

    And press enter.

  3. Type your password (same as your login name) and press enter.

  4. At the 'mysql>' prompt, type (substituting your login name for 'loginname' below):

    use loginname

    And press enter.

  5. To display a list of the tables that currently exist in your database, type (NOTE the semicolon at the end of the line!):

    show tables;

    And press enter.

  6. To display the fields that exist in the 'mytable' table, type:

    describe mytable;

    And press enter.

  7. To display the records that exist in the 'mytable' table, type:

    select * from mytable;

    And press enter.

  8. To exit mysql, type:

    quit

  9. Information on using mysql at SIMS can be found here. Complete mysql documentation can be found here.

Connecting to Your Database Using Java

The following zip file, ScheduleDB.zip, contains a program that connects to a database and reads/writes information from it. The program loads information about SIMS professors and courses into a database and then allows you to view a list of courses either by Semester or by Professor. To run the code, you'll need to include a jar file in the classpath, because it contains the mysql driver (see instructions below).

You'll also need to modify the ScheduleDB.java file slightly. In the main() method, you'll need to change the 'dblogin' variable to your own username. Change this line so that your username is within the quotes.

String dblogin = "yourlogin";

Following are instructions for including the jar file and running the code from Eclipse.

Eclipse

  1. Download ScheduleDB.zip and the mysql.jar files to the root of your H drive (don't copy them into the Eclipse\workspace directory). Extract the zip files to the same location.

  2. Launch Eclipse.

  3. Create a new Java project called ScheduleDB.

  4. Right-click on the ScheduleDB project, choose Import... > File System. Click Next, then click Browse, navigate to the H drive, and click OK. Check off the three java files and the two text files and click Finish.

  5. To include the jar file:
    1. Right-click on the ScheduleDB project.
    2. Choose Properties from the context-sensitive menu.
    3. Click on Java Build Path.
    4. Click on the Libraries Tab.
    5. Click the Add External JARs... button.
    6. Navigate to the H drive, select mysql.jar, and click Open.
    7. Click OK to close the Properties window.

  6. Open the ScheduleDB.java file. Modify the main as described above.

  7. Choose Run > Run As > Java Application.

  8. Take a look at the code and make sure you understand the parts about how the data is loaded into/ read from the database.

NF,JM -- last modified 11/20/03