In the instructions below, substitute your UW Net ID wherever you see "uwnetid".

Last updated: 12 Sep 2007

JDBC for MySQL

JDBC is the Java Database Connectivity API, which allows a Java program to access data stored in tabular form, such as those in relational databases and spreadsheets. Typically, a database driver is written by the database vendor and it is implemented as a class which is loaded prior to using the JDBC methods it provides. The driver and supporting classes and information are typically packaged in a jar file.

MySQL is a popular open-source database management system (DBMS) that supports a wide variety of APIs and means of interfacing. JDBC is one way to access a mySQL database, and the official JDBC driver for MySQL is called MySQL Connector/J. It is a "type 4" driver -- a "native-protocol pure Java driver".

A good source for documentation -- though a bit dated -- on how to use this driver can be found here. In the section on "Establishing a Connection", for this line:

 Class.forName("org.gjt.mm.mysql.Driver").newInstance();

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

JDBC Drivers in the Labs

JDBC drivers for some DBMSes have been installed in the labs for your convenience. If they are not in a CLASSPATH environment variable, you will need to either set one or specify all classpaths as a parameter to the java command when running your application.

For MySQL:

Platform	Location	Comment
Windows	G:\jdbc\mysql\mysql-connector-java-5.0.7-bin.jar	The path separator may need to be "/", not "\"
Linux	/usr/java/tools/mysql/mysql-connector-java-5.0.7-bin.jar	Already present in CLASSPATH on repos/cssgate

The Connection String

The driver documentation lists how to register the driver with the driver manager , which effectively loads the driver so you can create a connection to the database that the driver supports. For this line:

 Class.forName("org.gjt.mm.mysql.Driver").newInstance();

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

A "connection string" is a URI for specifying how to access the database. The format for the connection string is well defined and in that documentation . However, various values are specific to the installation.

An empty MySQL database has already been created for you on the Repository Server. Specifically, the connection string requires the host, port, database, database user, and database user password:

• host

  If using the DNS name fails, try using the IP address.

  Hostname	IP address
  localhost	127.0.0.1
  repos.insttech.washington.edu	128.208.244.213
  iai-db.insttech.washington.edu	128.208.244.5

• port

  The port is 3306.

• database name

  This is your UW Net ID.

• database user name

  This is also your UW Net ID.

• database user password

  The password is available from either faculty or lab staff.

...and the rest

The rest of the code is specific to the JDBC API, your database and table definitions, and the MySQL command set.

Importing Text Files

Due to security concerns, mysql's FILE privilege (which enables LOAD FILE) is not granted to users. This is often used to load a file of text into a SQL table.

However, similar functionality is available on the Repository Server via the gen_insert script. gen_insert is a Perl script that generates SQL INSERT statements from either comma-delimited or tab-delimited text files (e.g., exported from an Excel spreadsheet or another database).

gen_insert requires a table name and the delimited file name. It does not attempt to validate the table name; this makes it independent of any particular database. If an option states that the first line is row of delimited column names, it will use those names in the INSERT statement, which forces the order of the VALUES() to be the order in which the column names are specified. This is much more accurate and flexible than simply listing the values and hoping no one changed the order of the column names.

Input values from the file may be converted to conform to SQL rules in an INSERT statement as follows:

• If no value between delimiters, an empty string (e.g., using two single quotes: '') is used.

• If the value is double-quoted (i.e., using the single character "), it is converted to single-quoted (i.e, using '); e.g., "Smith" becomes 'Smith'.

• If the value contains single quotes, they are doubled to allow for proper insertion (e.g., "don't" becomes 'don''t').

• Otherwise, a value is not converted; therefore, if you want a string, you must quote the value if using a comma-delimited file.

  Generally, a tab-delimited file doesn't need to use quotes around strings because a tab character is not considered a printable character -- the most common use. With a comma-delimited string, when parsing the input file, one must make sure the comma is not within the quotes. So, there is an option (-q) to allow quoting of anything other than a decimal number that is intended to be used with tab-delimited, unquoted values.

gen_insert is in the path of all Repository Server accounts. To see how to use it, enter:

  gen_insert

For example, gen_insert can be used as follows (for example table "people" and delimited file "myfolks.txt", which does not have a row of names as the first line):

   gen_insert people myfolks.txt >myfolks.sql

Redirection of the output (>myfolks.sql) is used here to name and save the output.

As with any automatic conversion, you may encounter situations where something did not convert as expected. One source of errors would be when some values of a column contain decimal numbers and others in that column contain text; presuming the column represents text, the numbers would never be quoted and would probably cause an error when inserting. Always review the values in the output file before submitting the file to the DBMS.

Within a DBMS's command shell, one can import the SQL statements. For example, from mysql:

   source myfolks.sql;

would execute all of the INSERT statements in myfolks.sql as if the user typed them in one by one, thereby populating the "people" table with the data from the myfolks.txt file.

Note that some DBMSes require that you commit the insertions made to the table before you can do anything with the new data.

Change Log