Database Tutorial

Course Tutorial Site

Site Admin

MySQL and Java

with 5 comments

This demonstrates how to create an Java infrastructure for reading and writing large text files to a MySQL database. The example provides:

  • A FileIO.jar library that lets you enter MySQL connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
  • A mysql-connector-java-3.1.14-bin.jar file, which is MySQL’s library for JDBC communication with the MySQL Databases.

The steps to compiling and testing this code are qualified below:

  1. Download and install the Java Software Development Kit (JSDK) for Java 6.
  2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
  3. Download and position the mysql-connector-java-3.1.14-bin.jar and FileIO.jar files in the JavaTest directory.
  4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:
set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\mysql-connector-java-3.1.14-bin.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

  1. Copy the WriteReadCLOBMysql.java code from the bottom of this posting and also put it into the JavaTest directory.
  2. Compile the WriteReadCLOBMysql.java source code with the javac utility, as shown below:
javac WriteReadCLOBMysql.java

After you compile it, you should run it as follows:

java WriteReadCLOBMysql
  1. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
  2. When it runs, you’ll see the following tabbed JOptionPane.

You need to enter the following values before clicking the OK button:

  • Host: The localhost key word, or hostname of your physical machine running the database.
  • Port: The port that the MySQL Listener is running on (the default value is 3306).
  • Database: The Oracle TNS Alias, which is sampledb for the full database sample database.
  • UserID: The user name with permissions to the database entered that can access an ITEM table.
  • Password: The password for the user’s account.

In the JFileChooser, select a file to upload to the database.

You should see what you uploaded displayed in a JFrame.

Written by michaelmclaughlin

October 8th, 2009 at 9:28 pm

Posted in

5 Responses to 'MySQL and Java'

Subscribe to comments with RSS or TrackBack to 'MySQL and Java'.

  1. This post tells you to “Copy the WriteReadCLOBMysql.java code from the bottom of this posting”

    when I scroll down, I can not find WriteReadCLOBMysql.java I do find WriteReadCLOB.java? I am not clear if that is the same thing or if they are different.

    spencer

    7 Dec 10 at 4:55 pm

  2. In case you haven’t already received a dozen emails, the links for the jar files appears to have an issue.

    spencer

    7 Dec 10 at 5:01 pm

  3. I don’t know if it is intentional. The current version of java is jdk1.6.0_23, a lot of students are getting stuck on this step because you get an error unless you change the path to

    ‘set PATH=C:\Program Files\Java\jdk1.6.0_23\bin;%PATH%’

    I am having trouble as well and I think it is because there is no such folder as C:\JavaDev\Java6…….

    It should read

    C:\JavaTest\mysql-connector-java-3.1.14-bin.jar;C:\JavaTest\FileIO.jar;

    and have no . at the end.

    One last question is when it says ‘select a file to upload to the database’ is there any file in particular we should upload? if we are choosing our own file, are there any format/size restrictions?

    spencer

    7 Dec 10 at 6:41 pm

  4. That’s just an example directory. There’s no magic to where you put it provided the %PATH% statement points to the same place.

    michaelmclaughlin

    18 Dec 10 at 11:41 pm

  5. Unfold the Java Source Code Program title, and you’ll see the code source.

    michaelmclaughlin

    22 Oct 14 at 2:08 am

Leave a Reply