Accessing a MySQL Database from Java

MySQL is an open-source database management system that gives closed-source database products a run for their money. The closed-source products still win, of course, but at least they aren’t allowed to suck as much as they would if a free alternative didn’t exist. Assuming, of course, that Oracle hasn’t run MySQL into the ground by the time you read this. If they have, try whatever the least sucky branch of MySQL turns out to be. Or maybe PostgreSQL, although right now I couldn’t even tell you how to pronounce its name.

1. If you have not already done so, download and install the Java SDK. Details are given in a previous tutorial. Make a note of the directory to which javac.exe is installed.

2. Download the latest installer for XAMPP. XAMPP is a repackaged suite of popular open-source web server applications, including Apache and MySQL. As of this writing, the XAMPP installer is available at the URL “”.

3. Double-click on the icon for the XAMPP installer. Follow the prompts to install XAMPP, choosing the default options wherever possible. Make a note of the directory to which XAMPP is installed.

4. When XAMPP is finished installing, start the XAMPP Control Panel application. Depending on what installation options were selected, this can either be accessed from the desktop, the programs menu, or the system tray. It may also be started automatically when the installer itself is exited.

5. On the XAMPP control panel, click the “Start” button next to the “MySql” label. The text “Running” should appear in green next to the button. This indicates that MySQL is, well, running.

6. In any convenient location, create a new directory called MySQLTest.

7. Download MySQL Connector/J, which provides the libraries allowing Java programs to access and modify MySQL databases. As of this writing, MySQL Connector/J is available at the URL “”. The package is available either as a ZIP file or as a TAR file. Choose the ZIP Archive.

8. Once you have downloaded MySQL Connector/J, extract the archive to any convenient directory and find the correct .jar file within the extracted files. Copy this .jar to the newly created MySQLTest directory. As of this writing, the correct file is named “mysql-connector-java-5.1.16-bin.jar”, though of course the version number at least will almost certainly be different in the future.

9. Rename the .jar file just copied to the MySQLTest directory to “MySQL.jar”, for the sake of convenience.

10. In the newly created MySQLTest directory, create a new text file named “MySQL-LoginAsRoot.bat”, containing the following text. Substitute the name of the directory to which MySQL was installed in the appropriate place. (Note that this text presumes that no password has been set for the root account of MySQL, which from a security point of view is of course a pretty bad idea. But hopefully it’ll be good enough for this tutorial.)

[directory to which XAMPP was installed]\mysql\bin\mysql.exe -user=root

11. Double-click the icon for the newly created MySQL-LoginAsRoot.bat file to run it.

12. At the MySQL prompt, enter the text shown below. A new database named “TestDatabase” will be created, a new table called “TestTable” will be created within the database, and three rows of data will be inserted into the table. The MySQL query tool will then close, because the text ends with the “exit” command.

create database TestDatabase;
use TestDatabase;
create table TestTable ( ID int, Name varchar(64) );
insert into TestTable
    select 1, 'One'
    union all select 2, 'Two'
    union all select 3, 'Three';

13. In the newly created MySQLTest directory, create a new text file named “”, containing the following text.

import java.sql.*;

public class MySQLTest
    public static void main(String[] args)
        System.out.println("program begins");

        catch (Exception ex)

        System.out.println("program ends");

    private static void connectToAndQueryDatabase() 
    throws ClassNotFoundException, java.sql.SQLException    

        System.out.println("about to connect...");

        String connectString = "jdbc:mysql://localhost/TestDatabase?user=root";

        Connection connection = DriverManager.getConnection

        System.out.println("about to perform initial query...");


        System.out.println("about to insert new row");

        PreparedStatement insertStatement = connection.prepareStatement
            "insert into TestTable select 4, 'Four';"


        System.out.println("about to query after insert...");

        System.out.println("about to delete newly inserted row...");

        PreparedStatement deleteStatement = connection.prepareStatement
            "delete from TestTable where Name = ?;"

        deleteStatement.setString(1, "Four");


        System.out.println("about to query after delete...");


    private static void selectAllRowsAndPrintResults(Connection connection) 
    throws java.sql.SQLException
        Statement queryStatement = connection.createStatement();

        ResultSet resultSet = queryStatement.executeQuery("select * from TestTable;");

        System.out.println("query results:");

        while ( == true)
            int testID = resultSet.getInt("ID");
            String testName = resultSet.getString("Name");
            System.out.println("   ID, Name are " + testID + ", " + testName);    

14. Still in the MySQLTest directory, create a new text file named “JavaPathAndProgramNameSet.bat”, containing the following text. Substitute the name of the directory in which javac.exe is located in the appropriate place.

set javaPath="[the directory where javac.exe is located]"
for %%* in (.) do (set programName=%%~n*)

15. Still in the MySQLTest directory, create a new text file named “ProgramBuildAndRun-WithMySQLJar.bat”, containing the following text.

call JavaPathAndProgramNameSet.bat


%javaPath%\java.exe -classpath .;MySQL.jar %programName%


16. Double-click the icon of the newly created ProgramBuildAndRun-WithMySQLJar.bat file to run it. The results of the program will be displayed in a console window.

This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s