JavaData & JavaUsing JDBC with MySQL, Getting Started

Using JDBC with MySQL, Getting Started

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Java Programming Notes # 662

The purpose of this tutorial is to get you beyond the initial hurdles involved in:

  • Downloading and installing a MySQL database server.
  • Preparing that database for use with JDBC.
  • Writing and testing your first JDBC programs to administer the database and to manipulate the data stored in the MySQL database.

Use the navigation links below to jump to the section that best addresses your information needs, or read to entire article to master the use of MySQL with JDBC.


Preface

What is JDBC?

JDBC technology is an API (included in both J2SE and J2EE) that provides cross-DBMS connectivity to a wide range of SQL databases and access to other tabular data sources, such as spreadsheets or flat files.

What is MySQL?

The MySQL database server is probably the world’s most popular open source database software, with more than five million active installations as of September 2004.

The database server software from MySQL is available under a “dual licensing” model. Under this model, users may choose to use MySQL products under the free software/open source GNU General Public License (commonly known as the “GPL”) or under a commercial license.

A powerful combination

Simply stated, JDBC makes it possible to write platform independent Java programs that can be used to manipulate the data in a wide range of SQL databases without the requirement to modify and/or recompile the Java programs when moving from platform to platform or from DBMS to DBMS.

MySQL is available for a wide variety of platforms.

Since both JDBC and MySQL are freely available for many purposes, the combination of JDBC and MySQL is a powerful combination that should be of interest for a wide variety of applications.

Viewing tip

You may find it useful to open another copy of this lesson in a separate browser window. That will make it easier for you to scroll back and forth among the different listings and figures while you are reading about them.

Supplementary material

I recommend that you also study the other lessons in my extensive collection of online Java tutorials. You will find those lessons published at the Developer.com Java section. However, as of the date of this writing, Gamelan doesn’t maintain a consolidated index of my Java tutorial lessons, and sometimes they are difficult to locate there. You will find a consolidated index at www.DickBaldwin.com.

Preview

Download, install, and prepare the database server

It is often possible to use the same Java program to manipulate the data in a wide variety of SQL databases without a requirement to modify and/or recompile the Java program.

However, the installation and preparation procedures for different SQL databases vary widely. A large part of the battle in using JDBC with a particular database is getting the database installed and properly prepared for use with JDBC.

In this lesson, I will show you how to download, install, and prepare a MySQL database as a localhost server on a Windows platform for use with JDBC. Then I will show you how to write three simple JDBC programs to administer the database server and to manipulate data stored on the database server after it is installed.

(I will assume that you already have Java SDK v1.4.2 or later, which includes JDBC, installed on your computer.)

URLs and version numbers for downloading

I will provide specific URLs and version numbers for downloading MySQL software and documentation as of September 2004. The version numbers will certainly change over time as new versions of the software are released. Hopefully, the folks at MySQL will preserve the integrity of the URLs.

At least one URL, http://www.mysql.com/ should remain constant over time. If the other links to MySQL in this lesson become broken with time, you should revert to the main MySQL URL given above and begin your search for the software and documentation from that point.

Saving time

If you are a newcomer to the installation of database server software and the preparation of that software for use with JDBC, the information that I will provide in this lesson should save you several days of effort in pouring through documentation trying to figure out how to download, install, and tie everything together.

Even if you are experienced in these matters, this information should save you several hours of effort.

Installation and Preparation of MySQL

Getting things up and running

As a minimum, getting up and running with MySQL and JDBC involves at least the following steps:

  • Download and install the appropriate release of the MySQL database server software (several different releases are available).
  • Download and install the MySQL Connector/J — for connecting to a MySQL database server from Java.
  • Download and install the documentation for the MySQL database server.
  • Download and install the documentation for the Connector, which is a separate documentation package from the database server documentation.
  • Write and test one or more JDBC programs that will act as a database administrator, creating one or more users and possibly one or more databases on the database server. (I will show you three different ways to accomplish this.)
  • Write and test a JDBC program that will log in as a user and manipulate data stored in one of those databases.

Additional MySQL software

Beyond the minimum, there are a variety of additional software packages, (such as GUI administrator packages) that can be downloaded from MySQL and installed on your computer.

Since the main thrust of this lesson has to do with JDBC rather than database administration, I won’t get into that. Rather, I will show you how to use a command-line monitor program that is included with the MySQL database software to perform the minimal database administrative tasks required to satisfy the objectives of this lesson.

Documentation

I will begin with a discussion of the available documentation for both the MySQL database server and the MySQL Connector/J.

MySQL database server documentation

The MySQL Reference Manual can be downloaded from http://dev.mysql.com/doc/. In addition, there is an online searchable version of the Reference Manual available at http://dev.mysql.com/doc/mysql/en/Reference.html.

The downloadable version is available in several different formats, including:

You would probably do well to have both of these formats locally available on your computer if you have sufficient disk space.

The one-page-per-chapter formatted manual

The first format in the above list consists of a large number of HTML files. There is one HTML file for the table of contents plus about thirty-three additional files containing the text of the reference manual.

This format has a major advantage over the second format in terms of speed. It is relatively fast to click on a hyperlink in the table of contents and to see that material appear in the browser window.

There are a couple of downsides to this format, however. One downside is that this format is not very useful for searching the entire manual for keywords, (using your browser) because it is broken down into a large number of separate HTML files.

A second downside is that even though MySQL 4.0 is the recommended release in September of 2004, this manual contains information up through version 5.0.1-alpha. Sometimes information about the newer versions tends to obscure information about version 4.0.

Installing the one-page-per-chapter formatted manual

All that you need to do to install the database server documentation in this format is to download the zip file and extract the various HTML files into a folder on your disk. Then open the file named manual_toc.html in your browser to view the manual.

For convenience, I created a desktop icon that links to the table of contents file.

The all-on-one-page formatted manual

This format is very useful for searching (using your browser) because all of the text is in a single HTML file.

(There are actually two HTML files. One file contains a hyperlinked table of contents. The second file contains the text of the entire manual.)

The primary downside to this format is speed, or lack thereof. The HTML file containing the text of the manual is about four megabytes in size. On my machine, navigating this manual in a browser is a very slow process.

The downloadable version in this format also contains information up through version 5.0.1-alpha, resulting in the same disadvantage mentioned earlier.

Included in the software distribution

When you download and install the currently recommended version of MySQL, (which is version 4.0.21), the Docs folder in the installation tree structure will contain a copy of the manual in the all-on-one-page format that purports to be for version 4.0.21. Thus, you don’t need to download this format separately. You will get it when you download the software.

(Even though this version purports to be for version 4.0.21, it also contains a lot of information about later versions. It may be exactly the same as the version that can be downloaded separately except that the title page is different.)

Installation of the all-on-one-page formatted manual

As mentioned above, you don’t need to do anything special to install this format of the manual. It will be installed automatically when you install the MySQL 4.0.21 version of the database.

(Presumably, later versions of the software will also contain a copy of the current manual in this format.)

Once you have installed the database, the Docs folder of the installation tree will contain the files named manual_toc.html and manual.html. The first file contains a hyperlinked table of contents, and the second file contains the entire text of the manual.

Once again, for convenience, I created a desktop icon linked to the table of contents file to make it convenient to open in my browser.

Using both formats

Because I have plenty of space on my disk, I have both formats installed on my computer with an icon on the desktop for each. I occasionally open the version that contains the entire manual in a single HTML file when I need to search the entire document for something. Most of the time, however, I open and use the multi-file version due to its increased speed.

MySQL Connector/J documentation

I did not find a separate downloadable version of the connector documentation at the MySQL site. However, I did find an online version at http://dev.mysql.com/doc/connector/j/en/index.html. I was able to save the connector documentation locally by selecting the Save Page As… item on the File menu of my Netscape 7.2 browser.

(I was unable to save the page locally using Internet Explorer version 6 for some reason. However, I also discovered later that essentially the same documentation is contained in the downloadable zip file for the connector software in a file named mysql-connector-java-3.0.15-gadocsindex.html.)

Saving the page in Netscape 7.2 resulted in a local file named index.html and an associated folder named index_files. The file contains the text of the connector documentation. The folder contains style sheets and other related material.

Installation of the connector documentation consisted simply of saving this material locally and creating a desktop icon linked to the file named index.html.

Downloading the MySQL Database Server

The download page

The main download page for both the database server and the connector as of September 2004 is http://dev.mysql.com/downloads/. Hopefully, this URL will also remain intact as MySQL releases later versions of the software.

Several different versions of the database server are available for downloading as of September 2004, including:

  • MySQL 4.0 — Generally Available (GA) release (recommended)
  • MySQL 4.1 — Gamma release (use this for new development)
  • MySQL 5.0 — Alpha release (use this for previewing and testing new features)
  • MySQL 5.0.1 — Snapshot release (use this for previewing and testing new features)
  • Older releases — older releases (only recommended for special needs)
  • Snapshots — source code snapshots of the development trees

This list can be expected to change over time as new versions of the database server are released. Thus, the links in the above list will become obsolete. When that happens, you should revert back to the download page at http://dev.mysql.com/downloads/ and download the version that best suits your needs at that time.

The different versions of the database server

As of September 2004, the database server documentation has this to say about these different versions:

  • MySQL 5.0 is the newest development release series and is under very active development for new features. Alpha releases have been issued to allow more widespread testing.
  • MySQL 4.1 is in gamma status, soon moving to production status.
  • MySQL 4.0 is the current stable (production-quality) release series. New releases are issued for bugfixes. No new features are added that could diminish the code stability.
  • MySQL 3.23 is the old stable (production-quality) release series. This series is retired, so new releases are issued only to fix critical bugs.

I elected MySQL 4.0

I elected to download MySQL 4.0 since it is the stable production quality version as of September 2004. This resulted in the downloading of a distribution file named mysql-4.0.21-win.zip.

(The distribution file name is likely to be different for future versions of the MySQL database server.)

Installing MySQL Database Server

Installation instructions

Installation instructions for the database server are provided in the database server documentation, Section 2, entitled Installing MySQL.

Since I was installing on Windows XP and had no desire to deal with source code, I quickly skipped down to Section 2.2.1.2 entitled Installing a Windows Binary Distribution.

In my case, installation was easy

Because I did not have an earlier version of MySQL installed and I was logged onto Windows as an administrator, all that I needed to do was to execute the following instructions from the database server documentation to install the MySQL database server on my computer.

  • Unzip the distribution file to a temporary directory.
  • Run the setup.exe program to begin the installation process. If you want to install MySQL into a location other than the default directory (`C:mysql’), use the Browse button to specify your preferred directory. If you do not install MySQL into the default location, you will need to specify the location whenever you start the server. The easiest way to do this is to use an option file, as described in Section 2.2.1.3 Preparing the Windows MySQL Environment.

Because I didn’t want to deal with option files, I elected to allow the software to be installed in the default directory, C:mysql.

Testing the installation

After completing the installation, I performed some of the procedures shown in the database server documentation, Section 2.4.1 entitled Windows Post-Installation Procedures. Although I didn’t get exactly the same results as those shown in the documentation, my results were close enough to convince me that the MySQL database server was correctly installed on my computer.

(The reason that I didn’t get exactly the same results was that I didn’t log in with administrator privileges.)

Not installed as a Windows service

Section 2.2.1.7 of the database server documentation entitled Starting MySQL as a Windows Service contains the following:

“On the NT family (Windows NT, 2000, or XP), the recommended way to run MySQL is to install it as a Windows service. Then Windows starts and stops the MySQL server automatically when Windows starts and stops.”

I have no desire for the MySQL database server to start running every time I start Windows running. I already waste enough time waiting for Windows XP to become ready for use on my laptop each time I start it.

Therefore, I did not install the database server as a service. I will explain how I manually start and stop the database server whenever I need to use it later in this lesson.

Downloading MySQL Connector/J

What is MySQL Connector/J?

For those who don’t know, let me begin by explaining the purpose of MySQL Connector/J.

The JDBC API is designed to make it possible for you to write a single Java program and to use it to manipulate the data in a variety of different SQL database servers without a requirement to modify and/or recompile the program. In order to do this, it is necessary for you to:

  • Inform the Java program as to the URL of the database server. You can accomplish this with input data when you start the program.
  • Provide the Java program with a programming interface to the specific database server that you intend to use. Assuming that the programming interface has been installed on your computer, you can also accomplish this with input data when you run the program.

The programming interface

The programming interface deals with the interface peculiarities of the different database servers.

Sun refers to the process of providing this information to the program as registering the database server with the Java program. You will see how this is done in the sample programs later in this lesson.

The connector download page

The download page for MySQL Connector/J is http://dev.mysql.com/downloads/index.html. As of September 2004, the following versions are available for downloading from this page:

As with the MySQL database server software, these individual links are likely to become obsolete as new versions of the software are released. Hopefully the link to http://dev.mysql.com/downloads/index.html will remain intact.

The MySQL Connector/J 3.0 distribution file

Because I was very interested in stability, I elected to download and install MySQL Connector/J 3.0, identified above as the production release. This resulted in the download of a file named mysql-connector-java-3.0.15-ga.zip.

This zip file encapsulates 194 individual files in different folders including source code files, class files, pdf files, xml files, jar files, license files, files with no extensions, a manifest file, HTML files, and other file types not listed here.

The zip file also contains several java programs in a folder named testsuite that can be used to test your installation. You may find them useful for that purpose. In addition, these programs illustrate a variety of database operations using JDBC, so you may find them useful as example programs as well.

Fortunately, as I will explain below, all but one of these files can be ignored insofar as installation of the connector software is concerned.

Installing MySQL Connector/J

General installation instructions

The following statement appears in the connector documentation Section 2.2.1.entitled Setting the CLASSPATH (For Standalone Use).

“Once you have un-archived the distribution archive, you can install the driver in one of two ways: Either copy the “com” and “org” subdirectories and all of their contents to anywhere you like, and put the directory holding the “com” and “org” subdirectories in your classpath, or put mysql-connector-java-[version]-bin.jar in your classpath, either by adding the FULL path to it to your CLASSPATH environment variable, or by copying the .jar file to $JAVA_HOME/jre/lib/ext.

My installation

Actually, the above quotation describes three options instead of just two. To make a long story short, I elected the third option. I extracted the jar file named mysql-connector-java-3.0.15-ga-bin.jar from the zip file and copied it into the folder named c:j2sdk1.4.2jrelibext, which is the installation directory tree for the currently installed version of Java on my computer.

The advantage of doing it this way was that I didn’t have to modify the classpath environment variable. The disadvantage is that the next time I upgrade to a new version of Java, I must remember to save the MySQL connector jar file and copy it into the directory tree for my new Java installation.

Your installation

If you prefer the first option, the connector documentation contains a wealth of information to help you perform the necessary steps to modify the classpath, etc.

Testing the installation

I didn’t use any of the test programs mentioned above in the folder named testsuite. Rather, I tested my installation using JDBC programs that I had developed earlier using a different SQL database server.

You can use the test programs mentioned earlier in the testsuite folder to test your installation. Also, I will provide and explain three sample JDBC programs later in this lesson that you can use to test your installation. Before you can test the installation, however, you must start the MySQL database server running.

Starting the database server

At this point, all of the software necessary to use the database server in a JDBC program should be installed on your computer ready for use. The next step is to confirm that you can start the database server running.

Selecting a Windows server

I found it necessary to pull together several pieces of information from the database server documentation to determine the best way to start the server from a command line. For example, the following table is found in the database server documentation, Section 2.2.1.4 entitled Selecting a Windows Server.

Binary Description
mysqld Compiled with full debugging and automatic memory allocation checking, symbolic links, and InnoDB and BDB tables.
mysqld-opt Optimized binary. From version 4.0 on, InnoDB is enabled. Before 4.0, this server includes no transactional table support.
mysqld-nt Optimized binary for Windows NT, 2000, and XP with support for named pipes.
mysqld-max Optimized binary with support for symbolic links, and InnoDB and BDB tables.
mysqld-max-nt Like mysqld-max, but compiled with support for named pipes.

Explaining the different types of servers

The following explanation follows the table:

“We have found that the server with the most generic name (mysqld) is the one that many users are likely to choose by default. However, that is also the server that results in the highest memory and CPU use due to the inclusion of full debugging support. The server named mysqld-opt is a better general-use server choice to make instead if you don’t need debugging support and don’t want the maximal feature set offered by the -max servers or named pipe support offered by the -nt servers.”

Changes in MySQL 4.1.2

This is followed by another explanation indicating that beginning with MySQL 4.1.2, the server names were changed eliminating the server name mysqld-opt and replacing the debug version (mysqld) with mysqld-debug. Therefore, if you are installing MySQL 4.1.2 or a later version, you should use the syntax mysqld instead of mysqld-opt to start the server running from an optimized binary file.

Starting MySQL 4.0.21

Since I am running MySQL 4.0.21 and need to make certain that what I am doing is compatible with a large number of students having different operating systems, I concluded that I should start the server running by using the syntax mysqld-opt.

Section 2.2.1.5 of the database server documentation entitled Starting the Server for the First Time indicates that the following command should be used at the command prompt to start the server running:

C:mysqlbinmysqld –console

As I understand it, the purpose of –console is to cause error messages to be displayed on the standard error device (typically the screen) rather than to be entered into an error log file. This is what I want to happen.

Combining the two pieces of information given above, I concluded that I should start the MySQL database server by entering the following command at a command prompt:

C:mysqlbinmysqld-opt –console

Encapsulated in a batch file

Therefore, I created a batch file named MySqlStart.bat and linked that file to an icon on the desktop for convenience. The batch file contains the two commands shown in Listing 1 and repeated later in Listing 30 near the end of the lesson.

C:mysqlbinmysqld-opt --console
pause

Listing 1 Contents of MySqlStart.bat

The startup screen output

Figure 1 shows the screen output following the execution of the batch file named MySqlStart.bat.

(Note that it was necessary for me to manually enter a line break ahead of the word port to cause the screen output to fit in this narrow publication format.)

C:mysql>C:mysqlbinmysqld-opt --console
040918 13:59:47 InnoDB: Started
C:mysqlbinmysqld-opt: ready for connections.
Version: '4.0.21' socket: ''
port: 3306 Source distribution

Figure 1 MySQL database server startup sequence

The process window shown in Figure 1 remains open and active until the server is stopped. It should be possible to connect to the server using JDBC during this period.

Stopping the database server

It is probably a good idea to shut down the server before shutting down the computer. Section 2.2.1.6 of the database server documentation entitled Starting MySQL from the Windows Command Line states that you can stop the MySQL server by executing the following command:

C:mysqlbinmysqladmin -u root shutdown

Therefore, I created a batch file named MySqlStop.bat and linked that file to an icon on my desktop to make it convenient to stop the server. The batch file contains the two commands shown in Listing 2 and repeated in Listing 31 near the end of the lesson.

C:mysqlbinmysqladmin -u root shutdown
pause

Listing 2 Contents of MySqlStop.bat

 

The screen output at server shutdown

Figure 2 shows the screen output in the server process window when the file named MySqlStop.bat is executed.

040918 14:00:02 C:mysqlbinmysqld-opt: 
Normal shutdown

040918 14:00:03 InnoDB: Starting shutdown...
040918 14:00:05 InnoDB: Shutdown completed
040918 14:00:05 C:mysqlbinmysqld-opt:
Shutdown Complete


C:mysql>pause
Press any key to continue . . .
Figure 2 Screen output when MySQL server is 
stopped.

(As before, it was necessary for me to manually enter line breaks in Figure 2 to cause the screen output to fit in this narrow publication format.)

Once the server is shut down, attempts to connect to the server from JDBC will fail.

 

Creating a New Database using the Monitor Program

Now that you know how to start the MySQL database server, it’s time to learn how to:

  • Create a database that can be manipulated using JDBC in a Java program.
  • Create a new user having the necessary privileges to manipulate the database using JDBC in a Java program.

Three different approaches

I’m going to show you three different ways to accomplish this:

  • Using a command-line program named mysql coupled with manual data entry at runtime. (I will refer to this as the monitor program for reasons that will become self-evident later.)
  • Using the monitor program coupled with data input derived from a text file.
  • Using JDBC in a Java program.

I will illustrate the first approach in this and the later section entitled Creating a New User using the Monitor Program .

I will illustrate the second approach in the section entitled Administering the Database Server using Text Files.

I will illustrate the third approach in the section entitled Discussion and Sample Code, which shows how to use JDBC to manage and manipulate the database server.

The monitor program and manual data entry

The monitor program is named mysql.exe. It is located in c:mysqlbin. This program makes it possible to log onto the database server and to enter commands at the command line to:

  • Create databases
  • Add new users
  • Modify databases
  • Perform ad-hoc queries, etc.

(Unless you really enjoy typing, the monitor program is not a lot of fun to use.)

To really learn MySQL …

In order to really learn how to use MySQL, you will need to study the MySQL database server documentation in detail and probably some good SQL books as well.

The purpose of this lesson is to teach you just enough to get you started. When you finish this lesson, you should be able to successfully write and execute simple JDBC programs that will manipulate database tables on the MySQL database server.

The default administrative user

As I understand it, when the MySQL database server is first installed, there is a default user named root with full administrative privileges and no password. At this point, the server is totally wide open and insecure.

(The server documentation provides various suggestions as to what you should do to add security to the server.)

The user named root has the ability to create new databases as well as to create new users and to register those users on the databases.

Existing databases at MySQL installation

Also, as I understand it, there are two existing databases on the server when it is first installed. There is a database named test, which is wide open with no password requirements. Any user can access this database.

There is also a database named mysql, which is apparently used to keep track of things such as databases, users, etc. I believe that this database is accessible only by users having administrative privileges.

Adding a new database using the monitor program

The first step in adding a new database using the monitor program is to log onto the database server as the administrative user named root with access to the database named mysql. Until a password is assigned to the root user, login can be accomplished by entering the following command at the command prompt:

c:mysqlbinmysql –user=root mysql

(In case it isn’t clear on your display, the word user is preceded by two minus sign characters.)

The screen output

Assuming that the MySQL database server is running, the screen output produced by entering this command is shown in Figure 3.

(Note that in Figure 3, and several of the figures that follow, it was necessary for me to manually enter line breaks in the screen output to force the material to fit in this narrow publication format.)

C:jnk>c:mysqlbinmysql --user=root mysql
Welcome to the MySQL monitor. Commands end with ;
or g.
Your MySQL connection id is 26 to server version:
4.0.21

Type 'help;' or 'h' for help. Type 'c' to clear
the buffer.

mysql>
Figure 3 Monitor output for administrator login.

The monitor program

Note that this program refers to itself as the MySQL monitor. That explains why I refer to it as the monitor or the monitor program.

The purpose of this program is to make it possible for you to enter SQL database commands from the keyboard. Note in particular the prompt shown in boldface at the end of Figure 3, which reads:

mysql>

This is not a command-line prompt, which typically looks something like:

C:jnk>

Rather, this is a program-generated prompt where the monitor program is requesting input from the user.

SQL command terminators

As indicated in Figure 3, SQL commands end with either a semicolon character or g (note the difference in typeface for the character g in this text relative to that shown in Figure 3).

SQL commands are often quite long. You can enter successive portions of SQL commands at successive program prompts.

(Later, we will see that the monitor program uses a different syntax for continuation prompts.)

It is not until you enter a semicolon character or a g that the program responds to and attempts to execute the entire SQL command.

Terminating the monitor program

You can terminate the monitor program by entering a q at the program prompt.

(Note that this is a q as in quit and is not a g as in good.)

Creating a new database named JunkDB

Figure 4 shows the screen output for the use of the monitor program by the user named root to create a new database named JunkDB and then to terminate the monitor program.

C:jnk>c:mysqlbinmysql --user=root mysql
Welcome to the MySQL monitor. Commands end with ;
or g.
Your MySQL connection id is 27 to server version:
4.0.21

Type 'help;' or 'h' for help. Type 'c' to clear
the buffer.

mysql> CREATE DATABASE JunkDB;
Query OK, 1 row affected (0.13 sec)

mysql> q
Bye


C:jnk>
Figure 4 Creating database named JunkDB

The new material in Figure 4 is shown in boldface in the bottom half of the figure. The material in the top half of Figure 4 is a repeat of the material shown in Figure 3.

Using a batch file and a text file

Shortly, I will show you how to create a new database using a Windows batch file and an associated text file. Later on, I will show you how to create a new database using a Java JDBC program.

Creating a New User with the Monitor Program

Figure 5 shows the screen output for the use of the monitor program by the user named root to add a new user named auser.

The new material is shown in boldface in the lower half of the figure.

C:jnk>c:mysqlbinmysql --user=root mysql
Welcome to the MySQL monitor. Commands end with ;
or g.
Your MySQL connection id is 30 to server version:
4.0.21

Type 'help;' or 'h' for help. Type 'c' to clear
the buffer.

mysql> GRANT SELECT,INSERT,UPDATE,
-> DELETE,CREATE,DROP
-> ON JunkDB.*
-> TO 'auser'@'localhost'
-> IDENTIFIED BY 'drowssap';
Query OK, 0 rows affected (0.01 sec)


mysql> q
Bye

C:jnk>
Figure 5 Adding new user named auser.

A longer SQL command

This SQL command is much longer than the command used to create the new database. This command requires several continuation lines to complete to prevent it from exceeding the screen width.

(Note the difference in the syntax of new program prompts and continuation program prompts. The prompts that look like an arrow are the continuation prompts.)

I won’t try to explain the SQL command is detail. I will simply refer you to the MySQL database documentation and a good SQL book for that purpose. However, the SQL command is relatively self explanatory.

The meaning of the SQL command

This SQL command grants a list of six different privileges on the database named JunkDB to a user named auser who will be accessing the database from localhost.

(Granting access to the same user from a different machine on the network would require a different syntax.)

The user named auser will be allowed to access the database named JunkDB using the password drowssap, (which is password spelled backwards to make it easy for me to remember).

Administering the Database Server using Text Files

As mentioned earlier, unless you really enjoy typing, using the monitor program in manual data entry mode is not a fun way to work with the database. For example, if you make a typing error, you must go back and retype the entire command from the beginning.

Fortunately, there is a better approach. That approach is to provide the commands to the monitor program using a text file as input. Then if you make an error, you can simply edit the text file and rerun the process.

How does it work?

To make a long story short, you start the monitor program by redirecting the input so that the input is derived from a text file instead of from the keyboard. This process is described in the database server documentation, Section 3.5 entitled Using mysql in Batch Mode.

Creating a new database

There are probably several ways to set this process up. I elected to use a combination of a batch file and a text file. The batch file starts the monitor program, logging in as root, and redirects input to the associated text file.

For example, the files used to create a new database named JunkDB are shown in Listing 32 and Listing 33 near the end of the lesson.

Making a new user

The files used to make a new user named auser are shown in Listing 34 and Listing 35.

Compare the contents of these two files with the manual data entry shown earlier in Figure 5. The new user is granted six different privileges on the database named JunkDB from localhost with a password of drowssap.

Removing the user named auser

The files used to remove the user named auser are shown in Listing 36 and Listing 37. The procedure for revoking a user’s privileges and removing the user is explained near the end of Section 14.5.1.1 entitled DROP USER Syntax in the database server documentation.

Deleting the database named JunkDB

The files used to delete the database named JunkDB are shown in Listing 38 and Listing 39. The procedure for deleting a database is explained in the database server documentation, Section 14.2.8 entitled DROP DATABASE Syntax.

Discussion and Sample Code for JDBC Programs

With all of the above as preparation, it is now time to learn how to write JDBC programs to administer and manipulate the data on the MySQL database server.

Three separate programs

I will explain three programs. The first program, named Jdbc11 shows how to:

  • Log onto the server as the administrator named root.
  • Create a new database named JunkDB.
  • Register a new user named auser on the database named JunkDB with six different privileges and a password of drowssap.

The second program named Jdbc12 shows how to:

  • Log onto the server as the administrator named root.
  • Revoke the privileges of and remove the user named auser.
  • Delete the database named JunkDB.

The third program named Jdbc10 shows how to log onto the server as the user named auser and to manipulate the database named JunkDB in a variety of ways.

I will break each of these programs down into fragments and discuss the fragments. Complete listings of all the programs are shown in Listings 40, 41, and 42 near the end of the lesson.

Jdbc11 – Create a database and make a new user

The purpose of the program named Jdbc11 is to log onto the master database named mysql as the default administrator named root whose password is blank in order to perform the following updates on the MySQL database server:

  • Create a new database named JunkDB.
  • Create a new user named auser with a password of drowssap with six different privileges on the database named JunkDB.

The output, or lack thereof

These two operations produce no visible output when successful. However, they produce error messages in the output when unsuccessful.

(Note, however, that print statements in the program produce several lines of output that are independent of the operations being performed on the database server.)

Server must be running

The MySQL server must be running on localhost before this program is started. Instructions for starting and stopping the database server were provided earlier (see Listing 1, Figure 1, Listing 2, and Figure 2).

The program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.

Critical steps in using JDBC

There are five critical steps in using JDBC to manipulate a database:

  1. Load and register the JDBC driver classes (programming interface) for the database server that you intend to use.
  2. Get a Connection object that represents a connection to the database server (analogous to logging onto the server).
  3. Get one or more Statement objects for use in manipulating the database.
  4. Use the Statement objects to manipulate the database.
  5. Close the connection to the database.

I will highlight these five steps in the discussion of the sample program that follows.

Beginning of class definition for Jdbc11

The first program fragment for the program named Jdbc11 is shown in Listing 3. The entire program is shown in Listing 40 near the end of the lesson.

public class Jdbc11 {
public static void main(String args[]){
System.out.println(
"Copyright 2004, R.G.Baldwin");
try {
Statement stmt;

Listing 3

The code in Listing 3 is straightforward, showing the beginning of the class, the beginning of the main method, and a print statement.

Listing 3 also declares a local variable of type Statement. I will have more to say about the Statement interface later.

Register the JDBC driver for MySQL

Listing 4 shows the statement that implements the first critical step listed earlier (load and register the JDBC driver classes). This statement registers the MySQL driver classes with the Java program, making it possible for this program to manipulate data on the MySQL server.

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

Listing 4

Reference to the driver class

The following statement appears in the MySQL Connector documentation, Section 2.2.1.entitled Setting the CLASSPATH (For Standalone Use).

“If you are going to use the driver with the JDBC DriverManager, you would use “com.mysql.jdbc.Driver” as the class that implements java.sql.Driver.”

This information is also provided in the connector documentation in Section 2.2.2.entitled Driver Class Name and JDBC URL Format.

The Driver interface

Note the reference to the Driver interface in the above quotation. Here is some of what Sun has to say about the Driver interface:

“The interface that every driver class must implement.

The Java SQL framework allows for multiple database drivers.

Each driver should supply a class that implements the Driver interface.

The DriverManager will try to load as many drivers as it can find and then for any given connection request, it will ask each driver in turn to try to connect to the target URL. …

When a Driver class is loaded, it should create an instance of itself and register it with the DriverManager. This means that a user can load and register a driver by calling

Class.forName(“foo.bah.Driver”)”

The name of the driver class

In order to use a JDBC program with a specific database server, you must obtain the name of this critical driver class from the database vendor (or from some third party that supports the database server). You must then cause your program to load the class.

This is the class that connects the other classes in the connector package to the Java program. Without it, the Java program would be unable to communicate successfully with the database server.

Loading the driver class

The statement in Listing 4 causes this class to be loaded as described in the Sun documentation quoted above.

(If you are unfamiliar with the use of the forName method of the class named Class, see The Essence of OOP using Java: Static Members for a brief introduction to the class named Class. Then open your Google search engine, set the number of results to 100, and search for all of the keywords java forname richard baldwin. This should point you to several previous lessons that I have published that discuss this topic. If you don’t find what you need there, click on the link on the bottom of the last Google page that reads repeat the search with the omitted results included to see even more lessons.)

Specification of Driver class as a String

There are several alternative ways to register the Driver class, only one of which is shown in Listing 4. The statement in Listing 4 makes it possible to specify the Driver class as a String. The primary advantage of this approach is that this string can be obtained by the program in a variety of ways at runtime.

Although the string was hard coded into this simple program, that is not a requirement. For a more general program intended to be used with two or more database servers, this string would most likely be provided as some form of user input.

The URL of the database server

MySQL and other similar database engines behave as servers on a network. They are identified by a URL much as other types of servers (such as HTTP servers and FTP servers) are identified. The next fragment defines the URL for the MySQL database server that I used in this sample program.

The code in Listing 5 defines the URL of the master database named mysql on the MySQL database server residing on localhost and servicing the default port number 3306.

(I could have omitted the default port number from the URL, but I decided to include it to remind me to mention it. Note that the “//” characters shown to the right of “mysql:” form part of the URL. They are not comment indicators.)

 String url =
"jdbc:mysql://localhost:3306/mysql";

Listing 5

The URL format is provided in the connector documentation in Section 2.2.2.entitled Driver Class Name and JDBC URL Format. The URL format contains several optional elements. This URL will be referenced in the statement in Listing 6 for the purpose of getting a connection to the database.

Get a connection to the database

The code in Listing 6 implements the second critical step listed earlier (get a connection object).

Listing 6 gets a connection to the database at the specified URL (mysql on localhost port 3306) for a user named root with a blank password. As you are already aware, this user is the default administrator having full privileges to do anything, including creating new databases and registering new users on those databases.

 Connection con =
DriverManager.getConnection(
url,"root", "");

Listing 6

In effect, Listing 6 logs the JDBC program onto the MySQL database server in a manner that is analogous to the first line in Figures 3, 4, and 5 as well as the statements in the batch files shown in Listings 32, 34, 36, and 38.

The getConnection method

The getConnection method is a static method of the DriverManager class. When getConnection is invoked, the DriverManager will attempt to locate a suitable driver from among those loaded at initialization and those loaded explicitly using the same classloader as the current applet or application.

There are several overloaded versions of the getConnection method. The version used in Listing 6 attempts to establish a connection to the given database URL for a specific user with a specific password.

If the attempt to get a connection to the database server is successful, the method returns an object of type Connection. In this program, a reference to the Connection object is stored in the reference variable named con.

If the attempt is not successful, an exception of type SQLException will be thrown. Information pertinent to the nature of the problem will be encapsulated in the SQLException object.

As you will see later, SQL statements are executed and results are returned within the context of a connection.

Display some information

The code in Listing 7 is not critical to the program. This code simply displays information about the URL and the connection.

 System.out.println("URL: " + url);
System.out.println("Connection: " + con);

Listing 7

Get a Statement object

The code in Listing 8 implements the third critical step listed earlier (get one or more Statement objects).

This code invokes the createStatement method of the Connection interface to get an object of type Statement.

 stmt = con.createStatement();

Listing 8

Recall that con is a reference to an object of type Connection. A Connection object defines a connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.

According to Sun, a Statement object is:

“… used for executing a static SQL statement and returning the results it produces.”

The results that are returned

The results, if any, are returned in the form of a ResultSet object. I will have more to say about the ResultSet interface later in conjunction with the discussion of the program named Jdbc10.

(The SQL commands used in this program don’t return any results.)

Methods of the Statement interface

The Statement interface declares many methods that can be used to access the database server and to manipulate the data in the database. One of those methods is executeUpdate, which will be used in this program.

The executeUpdate method has a single String parameter. This parameter must be a valid SQL command. The method is used to execute SQL INSERT, UPDATE or DELETE statements. In addition, other SQL statements that return nothing can be executed using this method.

Create the new database

The code in Listing 9 implements the fourth critical step in the list provided earlier, (use of a Statement object to manipulate the database).

Listing 9 invokes the executeUpdate method on the Statement object to create the new database named JunkDB.

 stmt.executeUpdate(
"CREATE DATABASE JunkDB");

Listing 9

The executeUpdate method

Sun has this to say about the executeUpdate method:

“public int executeUpdate(Stringsql) throws SQLException

Executes the given SQL statement, …

Parameters: sql – an SQL INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing

Returns: either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing.”

The method parameter is an SQL command

Note the similarity of the method parameter in Listing 9 to the interactive input shown in Figure 4 and the contents of the text file shown in Listing 33.

In all three cases, an SQL command is invoked on the database server to cause a new database named JunkDB to be created. This SQL command returns nothing, so it is suitable for use with the executeUpdate method described above.

In all three cases, the SQL command is invoked by the default administrator named root who has the ability to create new databases.

Make a new user

The code in Listing 10 also implements the fourth critical step listed earlier, (use of a Statement object to manipulate the database).

The code in Listing 10 invokes the executeUpdate method once again to make a new user named auser who is capable of accessing the database named JunkDB from localhost using the password drowssap with six different privileges.

 stmt.executeUpdate(
"GRANT SELECT,INSERT,UPDATE,DELETE," +
"CREATE,DROP " +
"ON JunkDB.* TO 'auser'@'localhost' " +
"IDENTIFIED BY 'drowssap';");
Listing 10

Another SQL command

Once again, note the similarity of the executeUpdate method parameter in Listing 10 to the interactive input shown in Figure 5 and to the contents of the text file shown in Listing 35.

Figure 5, Listing 10, and Listing 35 illustrate three different ways for the default administrator named root to invoke the same SQL command on the database server.

Close the connection and terminate the program

Listing 11 implements the fifth critical step listed earlier, closing the connection and terminating the program.

 con.close();
}catch( Exception e ) {
e.printStackTrace();
}//end catch
}//end main
}//end class Jdbc11

Listing 11

The results of running the program named Jdbc11

Once this program has been run successfully, the MySQL database server contains a database named JunkDB, as well as a user named auser, having various privileges relative to that database with a password of drowssap.

At this point, it is possible to execute JDBC programs by which the user named auser manipulates the contents of the database named JunkDB. That will be the purpose of the program named Jdbc10, which I will explain later.

First, however, I am going to show you how to write a JDBC program to remove the user named auser and to delete the database named JunkDB from the MySQL database server.

JDBC12 – Remove a user and delete a database

The purpose of the program named Jdbc12 is to log onto the master database named mysql as the default administrator named root whose password is blank in order to perform the following updates on the MySQL database server:

  • Revoke the privileges of and remove a user named auser.
  • Delete a database named JunkDB.

The output

These two operations produce no visible output when successful. However, they produce error messages in the output when unsuccessful.

(Print statements in the program do produce output that is unrelated to the operations listed above.)

A reversal

This program is the reverse of the program named Jdbc11, discussed earlier, which creates the database named JunkDB and registers the user named auser on that database.

Server must be running

The MySQL server must be running on localhost before this program is started. Instructions for starting and stopping the database server were provided earlier (see Listing 1, Figure 1, Listing 2, and Figure 2).

Testing

This program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.

Beginning of the Jdbc12 class

Listing 12 shows the beginning of the class definition and the beginning of the main method for the program named Jdbc12. A complete listing of the program is provided in Listing 41

The code in Listing 12 is the same as the code discussed earlier for the program named Jdbc11. Therefore, I won’t repeat that discussion.

public class Jdbc12 {
public static void main(String args[]){
System.out.println(
"Copyright 2004, R.G.Baldwin");
try {
Statement stmt;

//Register the JDBC driver for MySQL.
Class.forName("com.mysql.jdbc.Driver");

//Define URL of database server for
// database named mysql on the localhost
// with the default port number 3306.
String url =
"jdbc:mysql://localhost:3306/mysql";

//Get a connection to the database for a
// user named root with a blank password.
// This user is the default administrator
// having full privileges to do anything.
Connection con =
DriverManager.getConnection(
url,"root", "");

//Display URL and connection information
System.out.println("URL: " + url);
System.out.println("Connection: " + con);

//Get a Statement object
stmt = con.createStatement();

Listing 12

Revoke privileges and remove user named auser

Listing 13 invokes the executeUpdate method four times in succession to cause the database server to revoke privileges for and to remove the user named auser. The SQL commands required to accomplish this were discussed earlier and illustrated in Listings 36 and 37.

 stmt.executeUpdate(
"REVOKE ALL PRIVILEGES ON *.* " +
"FROM 'auser'@'localhost'");
stmt.executeUpdate(
"REVOKE GRANT OPTION ON *.* " +
"FROM 'auser'@'localhost'");
stmt.executeUpdate(
"DELETE FROM mysql.user WHERE " +
"User='auser' and Host='localhost'");
stmt.executeUpdate("FLUSH PRIVILEGES");

Listing 13

Delete the database named JunkDB

Listing 14 invokes the executeUpdate method to delete the database named JunkDB, using an SQL command discussed earlier and illustrated in Listings 38 and 39.

 stmt.executeUpdate(
"DROP DATABASE JunkDB");

Listing 14

Closing the connection and terminating the program

Listing 15 closes the connection and terminates the program.

 con.close();
}catch( Exception e ) {
e.printStackTrace();
}//end catch
}//end main
}//end class Jdbc12

Listing 15

Jdbc10 – Manipulating the data in the database

The behavior of each of the two previous programs has been in the nature of administering or managing the database server. In particular, those programs added and removed databases and users from the database server.

The user perspective

The purpose of the program named Jdbc10 is to illustrate the ability to use JDBC to access a MySQL database server on localhost and to manipulate the data stored in that database.

Server must be running

The MySQL server must be running on localhost before the program named Jdbc10 is started. Instructions for starting and stopping the MySQL database server were provided earlier (see Listing 1, Figure 1, Listing 2, and Figure 2).

Server must be prepared

In addition, a database named JunkDB must have been created on the server and a user named auser must have been registered on that database with a password of drowssap before this program is started.

The user named auser must have privileges that allow for the creation of tables in the database and the insertion of data into the tables. In addition the user must be allowed to perform SELECT queries on the tables in the database.

Creating the database and adding the user

The database may have been created and the user may have been added in at least three different ways:

  • Performing manual data entry with the monitor program as illustrated in Figures 4 and 5.
  • Using the monitor program coupled with data input derived from a text file as illustrated in Listings 32, 33, 34, and 35.
  • Running the program named Jdbc11 discussed earlier.

Behavior of Jdbc10 program

This program:

  • Logs in as auser with a password of drowssap.
  • Accesses the database named JunkDB.
  • Creates a table named myTable.
  • Puts five rows of data into the table named myTable.
  • Accesses the data in the table named myTable.
  • Displays the data.
  • Deletes the table named myTable.

Two different approaches are used to display the contents of the table. The first approach displays all of the data in the table. The second approach displays only the data in a specific row in the table.

A precaution

As a precaution, before attempting to create the new table, the program attempts to delete a table having the same name. If a table having the same name already exists as residue from a previous run, it is deleted.

If it doesn’t already exist when the attempt is made to delete it, an exception is thrown. This exception is caught, displayed, and ignored.

The program output

This program produces the output shown in Figure 6 under normal conditions where the table named myTable does not exist when the program is started (the specifics regarding the Connection object may vary from one run to the next).

Copyright 2004, R.G.Baldwin
URL: jdbc:mysql://localhost:3306/JunkDB
Connection: com.mysql.jdbc.Connection@1430b5c
java.sql.SQLException: Base table or view not
found message from server: "Unknown table
'mytable'"No existing table to delete
Display all results:
test_id= 1 str = One
test_id= 2 str = Two
test_id= 3 str = Three
test_id= 4 str = Four
test_id= 5 str = Five
Display row number 2:
test_id= 2 str = Two
Figure 6

Testing

This program was tested using Java SDK 1.4.2 under WinXP, MySQL version 4.0.21-win, and JDBC connector version mysql-connector-java-3.0.15-ga.

Beginning of the class definition

The class definition begins in Listing 16. A complete listing of the program is shown in Listing 42 near the end of the lesson.

public class Jdbc10 {
public static void main(String args[]){
System.out.println(
"Copyright 2004, R.G.Baldwin");
try {
Statement stmt;
ResultSet rs;

//Register the JDBC driver for MySQL.
Class.forName("com.mysql.jdbc.Driver");

Listing 16

Except for the declaration of a variable of type ResultSet, the code in Listing 16 is the same as the code in the two previous programs. I will have more to say about the ResultSet type later.

Define the database URL

Listing 17 defines the URL of the database server and the database that will be accessed later by this program.

 String url =
"jdbc:mysql://localhost:3306/JunkDB";

Listing 17

Note that the previous two programs accessed the master database named mysql, whereas this program accesses the user database named JunkDB. Otherwise, the code in Listing 17 is the same as in the two previous programs.

Get a connection to the database

Listing 18 gets a connection to the database.

 Connection con =
DriverManager.getConnection(
url,"auser", "drowssap");

Listing 18

Recall that the code in Listing 18 is analogous to a specific user logging onto a specific database using a specific password.

The previous two programs got a connection to the master database named mysql for the default administrator named root whose password was blank.

This program gets a connection to the database named JunkDB for the user named auser whose password is drowssap.

Display some information and get a statement object

As in the previous two programs, Listing 19 displays some information about the URL and the connection, and then gets a Statement object.

 //Display URL and connection information
System.out.println("URL: " + url);
System.out.println("Connection: " + con);

//Get a Statement object
stmt = con.createStatement();

Listing 19

Delete the table named myTable if it exists

A table named myTable may already exist in the database named JunkDB for some reason such as the premature ending of a previous run of this program. If the table already exists, it will not be possible to create a new empty table having that name. A requirement of this program is to create an empty table named myTable in a particular format.

Therefore, as a precaution, before attempting to create the new table, the code in Listing 20 attempts to delete a table named myTable. If a table having that name already exists, it is deleted.

If it doesn’t already exist when the attempt is made to delete it, an exception is thrown. This exception is simply caught, displayed, and ignored.

 try{
stmt.executeUpdate("DROP TABLE myTable");
}catch(Exception e){
System.out.print(e);
System.out.println(
"No existing table to delete");
}//end catch

Listing 20

The code in Listing 20 invokes the executeUpdate method, with which you are already familiar. Only the syntax of the SQL command provided as a parameter to the method is new to this program.

Create the new table named myTable

The code in Listing 22 invokes the executeUpdate method to create a new table named myTable. I will refer you to an SQL book for a full understanding of the SQL command that is passed as a parameter to the method.

 stmt.executeUpdate(
"CREATE TABLE myTable(test_id int," +
"test_val char(15) not null)");

Listing 22

Briefly, the new table will have two columns. The first column will be named test_id and will be designed to contain integer data.

The second column will be named test_val and will be designed to contain character data up to 15 characters in length (in Java, we might refer to that as String data, except that there is no limit to the length of a String in Java).

Insert some values into the table

Listing 23 invokes the executeUpdate method to insert one value into each column of the first row in the table.

 stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(1,'One')");

Listing 23

 

The integer value 1 is inserted into the column named test_id. The three characters, One, are inserted into the column named test_val.

Insert values into four more rows

Listing 24 invokes the executeUpdate method four more times in succession to insert values into the columns in rows 2 through 5. You should be able to examine the SQL commands and determine the values inserted into the table.

 stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(2,'Two')");
stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(3,'Three')");
stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(4,'Four')");
stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(5,'Five')");

Listing 24

 

The table is populated

At this point, the first five rows in the table named myTable have been populated with data. The remaining code in the program will:

  • Access and display all of the data in the table.
  • Display the data in a specific row in the table.
  • Delete the table from the database.

Get another Statement object

The code in Listing 25 gets a different Statement object, initialized as shown by the parameters that are passed to the createStatement method.

 stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

Listing 25

 

I won’t try to explain the meaning of the initialization parameters. Rather, I will let you look them up in the Sun documentation for the ResultSet interface, and then perhaps do further research in an SQL book.

The ResultSet interface provides about ten symbolic constants that can be used as parameters to this method. The values of the parameters exercise control over the behavior of the ResultSet object returned by a subsequent query based on the Statement object.

Query the database

All of the database operations to this point have been based on the use of the executeUpdate method of the Statement interface. Recall that I told you earlier that the executeUpdate method can be used to execute SQL commands that don’t return anything.

We have now reached the point where we want to execute an SQL command that does return something. For this, we will invoke the executeQuery method on the Statement object.

What does Sun have to say?

Here is part of what Sun has to say about this method:

“public ResultSet executeQuery(Stringsql) throws SQLException

Executes the given SQL statement, which returns a single ResultSet object.

Parameters: sql – an SQL statement to be sent to the database, typically a static SQL SELECT statement

Returns: a ResultSet object that contains the data produced by the given query; never null”

In other words, this method will execute the SQL command on the database and encapsulate the returned values in an object of type ResultSet.

A ResultSet object

A ResultSet object provides access to an encapsulated table of data. The object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row (similar to an iterator or an enumerator in Java).

Once the results are encapsulated in a ResultSet object, the ResultSet interface provides several methods that can be used to extract the information from the object.

Methods of a ResultSet object

The get methods (such as getString) retrieve column values for the current row. You can retrieve values using either the index number of the column or the name of the column. I have read that using the column index is more efficient but I can’t give you a reference on that.

(Columns are numbered beginning with 1, not with 0.)

For the get methods, the JDBC driver attempts to convert the underlying data to the specified Java type and returns a suitable Java value.

The life of a ResultSet object

A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Perform the query

Listing 26 invokes the executeQuery method to perform the query on the database selecting all columns in all rows, and sorting the results in order by the values in the column named test_id. The results are encapsulated in the ResultSet object referred to by the reference variable named rs.

 rs = stmt.executeQuery("SELECT * " +
"from myTable ORDER BY test_id");

Listing 26

 

Display all of the results in the ResultSet object

Listing 27 uses a while loop to:

  • Iterate on the ResultSet object one row at a time.
  • Invoke the getInt method to get and save the value in the column named test_id for each row.
  • Invoke the getString method to get and save the value in the column named test_val for each row.
  • Display the two values on a new output line on the screen.
 System.out.println("Display all results:");
while(rs.next()){
int theInt= rs.getInt("test_id");
String str = rs.getString("test_val");
System.out.println("ttest_id= " + theInt
+ "tstr = " + str);
}//end while loop

Listing 27

 

The output

The output produced by the code in Listing 27 is shown in Figure 7.

Display all results:
test_id= 1 str = One
test_id= 2 str = Two
test_id= 3 str = Three
test_id= 4 str = Four
test_id= 5 str = Five
Figure 7

Display the data in row number 2

The code in Listing 28 invokes the absolute method on the same ResultSet object to get, save, and display the data in the two columns of row number 2.

 System.out.println(
"Display row number 2:");
if( rs.absolute(2) ){
int theInt= rs.getInt("test_id");
String str = rs.getString("test_val");
System.out.println("ttest_id= " + theInt
+ "tstr = " + str);
}//end if

Listing 28

 

(Note that row numbers begin with 1 and do not begin with 0 as would be the case in most Java contexts. Although not demonstrated here, the same is true for column numbers. Apparently this is the norm in database work.)

The output

Figure 8 shows the output produced by the code in Listing 28.

Display row number 2:
test_id= 2 str = Two
Figure 8

Delete the table, close the connection, and terminate

Listing 29 invokes the executeUpdate method to delete the table named myTable from the database named JunkDB.

 stmt.executeUpdate("DROP TABLE myTable");
con.close();
}catch( Exception e ) {
e.printStackTrace();
}//end catch
}//end main
}//end class Jdbc10

Listing 29

 

Then Listing 29 closes the connection (logs off the database) and terminates the program.

Run the Programs

I encourage you to download and install the MySQL database server and the MySQL connector as described in this lesson.

Then copy the code from the listings near the end of this lesson. Execute the batch files. Compile and execute the programs. Experiment with the files and the programs, making changes, and observing the results of your changes.

Summary

I showed you how to download, install, and prepare a MySQL database as a localhost server on a Windows platform for use with JDBC.

Then I showed you how to write three simple JDBC programs to administer the database server and to manipulate data stored in a MySQL database.

Complete Program Listings

A complete listing of each of the programs, batch files, and text files discussed in this lesson is shown below.

C:mysqlbinmysqld-opt --console
pause

 

Listing 30 Contents of MySqlStart.bat

 

C:mysqlbinmysqladmin -u root shutdown
pause

 

Listing 31 Contents of MySqlStop.bat

 

c:mysqlbinmysql --user=root mysql < MySqlCreateDatabase01.txt
pause

 

Listing 32 MySqlCreateDatabase01.bat

 

CREATE DATABASE JunkDB;
q

 

Listing 33 MySqlCreateDatabase01.txt

 

c:mysqlbinmysql --user=root mysql < MySqlMakeUser01.txt
pause

 

Listing 34 MySqlMakeUser01.bat

 

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 
ON JunkDB.*
TO 'auser'@'localhost'
IDENTIFIED BY 'drowssap';
q

 

Listing 35 MySqlMakeUser01.txt

 

c:mysqlbinmysql --user=root mysql < MySqlRemoveUser01.txt
pause

 

Listing 36 MySqlRemoveUser01.bat

 

REVOKE ALL PRIVILEGES ON *.* FROM 'auser'@'localhost';
REVOKE GRANT OPTION ON *.* FROM 'auser'@'localhost';
DELETE FROM mysql.user WHERE User='auser' and Host='localhost';
FLUSH PRIVILEGES;
q

 

Listing 37 MySqlRemoveUser01.txt

 

c:mysqlbinmysql --user=root mysql < MySqlDropDatabase01.txt
pause

 

Listing 38 MySqlDropDatabase01.bat

 

DROP DATABASE JunkDB;
q

 

Listing 39 MySqlDropDatabase01.txt

 

/*File Jdbc11.java
Copyright 2004, R.G.Baldwin
Rev 09/18/04

The purpose of this program is to log onto
the master database named mysql as the default
administrator named root whose password is blank
in order to perform the following updates:

1. Create a new database named JunkDB.
2. Create a new user named auser with a password
of drowssap with broad access to the
database named JunkDB.

These two operations produce no visible output
when successful. However, they produce error
messages in the output when unsuccessful.

This program is the reverse of the program named
Jdbc12, which deletes the database JunkDB and
removes the user named auser.

The MySQL server must be running on localhost
before this program is started.

It is necessary to manually start the MySQL
database server running on localhost. See the
documentation sections 2.2.1.5,Starting the
Server for the First Time and 2.2.1.4 Selecting a
Windows Server. This is accomplished by executing
the following command at the command prompt:

C:mysqlbinmysqld-opt --console

Similarly, it is necessary to manually stop the
MySQL database server. See the documentation
Section 2.2.1.6 Starting MySQL from the Windows
Command Line. This is accomplished by executing
the following command at the command prompt:

C:mysqlbinmysqladmin -u root shutdown

To install the JDBC interface classes, I copied
the jar file named
mysql-connector-java-3.0.15-ga-bin.jar into the
jrelibext folder of my Java installation. I
did this to avoid having to make changes to the
classpath.

I am currently running SDK v1.4.2. When I
upgrade to a newer version of the SDK, it will be
necessary for me to copy the JDBC jar file into
the jrelibext folder for the new version of the
SDK.

This program produces the following output as
a result of a successful run:

Copyright 2004, R.G.Baldwin
URL: jdbc:mysql://localhost:3306/mysql
Connection: com.mysql.jdbc.Connection@1430b5c

Tested using SDK 1.4.2 under WinXP, MySQL
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/
import java.sql.*;

public class Jdbc11 {
public static void main(String args[]){
System.out.println(
"Copyright 2004, R.G.Baldwin");
try {
Statement stmt;

//Register the JDBC driver for MySQL.
Class.forName("com.mysql.jdbc.Driver");

//Define URL of database server for
// database named mysql on the localhost
// with the default port number 3306.
String url =
"jdbc:mysql://localhost:3306/mysql";

//Get a connection to the database for a
// user named root with a blank password.
// This user is the default administrator
// having full privileges to do anything.
Connection con =
DriverManager.getConnection(
url,"root", "");

//Display URL and connection information
System.out.println("URL: " + url);
System.out.println("Connection: " + con);

//Get a Statement object
stmt = con.createStatement();

//Create the new database
stmt.executeUpdate(
"CREATE DATABASE JunkDB");
//Register a new user named auser on the
// database named JunkDB with a password
// drowssap enabling several different
// privileges.
stmt.executeUpdate(
"GRANT SELECT,INSERT,UPDATE,DELETE," +
"CREATE,DROP " +
"ON JunkDB.* TO 'auser'@'localhost' " +
"IDENTIFIED BY 'drowssap';");
con.close();
}catch( Exception e ) {
e.printStackTrace();
}//end catch
}//end main
}//end class Jdbc11

 

Listing 40 Jdbc11.java

 

/*File Jdbc12.java
Copyright 2004, R.G.Baldwin
Rev 09/18/04

The purpose of this program is to log onto
the master database named mysql as the default
administrator named root whose password is blank
in order to perform the following updates:

1. Remove a user named auser.
2. Delete a database named JunkDB.

These two operations produce no visible output
when successful. However, they produce error
messages in the output when unsuccessful.

This program is the reverse of the program named
Jdbc11, which creates the database named JunkDB
and registers the user named auser on that
database.

The MySQL server must be running on localhost
before this program is started.

It is necessary to manually start the MySQL
database server running on localhost. See the
documentation sections 2.2.1.5,Starting the
Server for the First Time and 2.2.1.4 Selecting a
Windows Server. This is accomplished by executing
the following command at the command prompt:

C:mysqlbinmysqld-opt --console

Similarly, it is necessary to manually stop the
MySQL database server. See the documentation
Section 2.2.1.6 Starting MySQL from the Windows
Command Line. This is accomplished by executing
the following command at the command prompt:

C:mysqlbinmysqladmin -u root shutdown

To install the JDBC interface classes, I copied
the jar file named
mysql-connector-java-3.0.15-ga-bin.jar into the
jrelibext folder of my Java installation. I
did this to avoid having to make changes to the
classpath.

I am currently running SDK v1.4.2. When I
upgrade to a newer version of the SDK, it will be
necessary for me to copy the JDBC jar file into
the jrelibext folder for the new version of the
SDK.

This program produces the following output as
a result of a successful run:

Copyright 2004, R.G.Baldwin
URL: jdbc:mysql://localhost:3306/mysql
Connection: com.mysql.jdbc.Connection@1430b5c

Tested using SDK 1.4.2 under WinXP, MySQL
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/
import java.sql.*;

public class Jdbc12 {
public static void main(String args[]){
System.out.println(
"Copyright 2004, R.G.Baldwin");
try {
Statement stmt;

//Register the JDBC driver for MySQL.
Class.forName("com.mysql.jdbc.Driver");

//Define URL of database server for
// database named mysql on the localhost
// with the default port number 3306.
String url =
"jdbc:mysql://localhost:3306/mysql";

//Get a connection to the database for a
// user named root with a blank password.
// This user is the default administrator
// having full privileges to do anything.
Connection con =
DriverManager.getConnection(
url,"root", "");

//Display URL and connection information
System.out.println("URL: " + url);
System.out.println("Connection: " + con);

//Get a Statement object
stmt = con.createStatement();

//Remove the user named auser
stmt.executeUpdate(
"REVOKE ALL PRIVILEGES ON *.* " +
"FROM 'auser'@'localhost'");
stmt.executeUpdate(
"REVOKE GRANT OPTION ON *.* " +
"FROM 'auser'@'localhost'");
stmt.executeUpdate(
"DELETE FROM mysql.user WHERE " +
"User='auser' and Host='localhost'");
stmt.executeUpdate("FLUSH PRIVILEGES");

//Delete the database
stmt.executeUpdate(
"DROP DATABASE JunkDB");

con.close();
}catch( Exception e ) {
e.printStackTrace();
}//end catch
}//end main
}//end class Jdbc12

 

Listing 41 Jdbc12.java

 

/*File Jdbc10.java
Copyright 2004, R.G.Baldwin
Rev 09/16/04

The purpose of this program is to test the
ability to use JDBC to access a MySQL database
server on localhost.

The MySQL server must be running on localhost
before this program is started. In addition, a
database named JunkDB must have been created and
a user named auser must have been registered on
that database with a password of drowssap before
this program is started.

It is necessary to manually start the MySQL
database server running on localhost. See the
documentation sections 2.2.1.5,Starting the
Server for the First Time and 2.2.1.4 Selecting a
Windows Server. This is accomplished by executing
the following command at the command prompt:

C:mysqlbinmysqld-opt --console

Similarly, it is necessary to manually stop the
MySQL database server. See the documentation
Section 2.2.1.6 Starting MySQL from the Windows
Command Line. This is accomplished by executing
the following command at the command prompt:

C:mysqlbinmysqladmin -u root shutdown

You can prepare MySQL for use in three
alternative ways:
1. Using the control program named mysql located
in folder C:mysqlbin in interactive mode.
2. Using a batch file to start the mysql program
along with a text file to provide the input.
3. By running the Java programs named Jdbc11 and
Jdbc12.

See the MySQL documentation Section 3.5, entitled
Using mysql in Batch Mode, for an explanation of
how to access the MySQL database server using
batch files and text files.

There is one bat file and one txt file used in
each case. The bat file logs into the mysql
monitor program as root having administrator
privileges and points to the txt file, which
provides the commands that are executed by the
monitor program.

The following two files create a new database
named JunkDB. See the documentation Section 3.3,
entitled Creating and Using a Database.

MySqCreateDatabase01.bat
MySqlCreateDatabase01.txt

The following two files make a new user named
auser registered on the database named JunkDB
with a password of drowssap. This user can
access the JunkDB database, but only from
localhost. See the documentation Section 5.6.2,
entitled Adding New User Accounts to MySQL.

MySqlMakeUser01.bat
MySqlMakeUser01.txt

Another way to create the database and register
a user named auser on that database is to run
the Java program named Jdbc11.

The following two files delete the database named
JunkDB. See documentation Section 14.2.8, DROP
DATABASE Syntax.

MySqDropDatabase01.bat
MySqlDropDatabase01.txt

The following two files remove the user named
auser. See the documentation Section 5.6.3,
Removing User Accounts from MySQL.

MySqlRemoveUser01.bat
MySqlRemoveUser01.txt

Another way to delete a database named JunkDB
and remove the user named auser is to run the
program named Jdbc12.

This program:
Accesses the database named JunkDB,
Creates a table named myTable,
Puts five rows of data into the table,
Displays the data,
Deletes the table.

Two different approaches are used to display the
contents of the table. The first approach
displays all of the data in the table. The
second approach displays only the data in a
specific row in the table.

As a precaution, before attempting to create the
new table, the program attempts to delete a table
having the same name. If a table having the same
name already exists as residue from a previous
run, it is deleted. If it doesn't already exist
when the attempt is made to delete it, an
exception is thrown. This exception is simply
caught and ignored.

To install the JDBC interface classes, I copied
the jar file named
mysql-connector-java-3.0.15-ga-bin.jar into the
jrelibext folder of my Java installation. I
did this to avoid having to make changes to the
classpath.

I am currently running SDK v1.4.2. When I
upgrade to a newer version of the SDK, it will be
necessary for me to copy the JDBC jar file into
the jrelibext folder for the new version of the
SDK.

This program produces the following output under
normal conditions where the table named myTable
does not exist when the program is started (the
specifics regarding the Connection object may
vary from one run to the next):

Copyright 2004, R.G.Baldwin
URL: jdbc:mysql://localhost:3306/JunkDB
Connection: com.mysql.jdbc.Connection@1430b5c
java.sql.SQLException: Base table or view not
found message from server: "Unknown table
'mytable'"No existing table to delete
Display all results:
test_id= 1 str = One
test_id= 2 str = Two
test_id= 3 str = Three
test_id= 4 str = Four
test_id= 5 str = Five
Display row number 2:
test_id= 2 str = Two

Tested using SDK 1.4.2 under WinXP, MySQL
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/
import java.sql.*;

public class Jdbc10 {
public static void main(String args[]){
System.out.println(
"Copyright 2004, R.G.Baldwin");
try {
Statement stmt;
ResultSet rs;

//Register the JDBC driver for MySQL.
Class.forName("com.mysql.jdbc.Driver");

//Define URL of database server for
// database named JunkDB on the localhost
// with the default port number 3306.
String url =
"jdbc:mysql://localhost:3306/JunkDB";

//Get a connection to the database for a
// user named auser with the password
// drowssap, which is password spelled
// backwards.
Connection con =
DriverManager.getConnection(
url,"auser", "drowssap");

//Display URL and connection information
System.out.println("URL: " + url);
System.out.println("Connection: " + con);

//Get a Statement object
stmt = con.createStatement();

//As a precaution, delete myTable if it
// already exists as residue from a
// previous run. Otherwise, if the table
// already exists and an attempt is made
// to create it, an exception will be
// thrown.
try{
stmt.executeUpdate("DROP TABLE myTable");
}catch(Exception e){
System.out.print(e);
System.out.println(
"No existing table to delete");
}//end catch

//Create a table in the database named
// myTable.
stmt.executeUpdate(
"CREATE TABLE myTable(test_id int," +
"test_val char(15) not null)");

//Insert some values into the table
stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(1,'One')");
stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(2,'Two')");
stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(3,'Three')");
stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(4,'Four')");
stmt.executeUpdate(
"INSERT INTO myTable(test_id, " +
"test_val) VALUES(5,'Five')");

//Get another statement object initialized
// as shown.
stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);

//Query the database, storing the result
// in an object of type ResultSet
rs = stmt.executeQuery("SELECT * " +
"from myTable ORDER BY test_id");

//Use the methods of class ResultSet in a
// loop to display all of the data in the
// database.
System.out.println("Display all results:");
while(rs.next()){
int theInt= rs.getInt("test_id");
String str = rs.getString("test_val");
System.out.println("ttest_id= " + theInt
+ "tstr = " + str);
}//end while loop

//Display the data in a specific row using
// the rs.absolute method.
System.out.println(
"Display row number 2:");
if( rs.absolute(2) ){
int theInt= rs.getInt("test_id");
String str = rs.getString("test_val");
System.out.println("ttest_id= " + theInt
+ "tstr = " + str);
}//end if

//Delete the table and close the connection
// to the database
stmt.executeUpdate("DROP TABLE myTable");
con.close();
}catch( Exception e ) {
e.printStackTrace();
}//end catch
}//end main
}//end class Jdbc10

 

Listing 42 Jdbc10.java


Copyright 2004, Richard G. Baldwin. Reproduction in whole or in part in any form or medium without express written permission from Richard Baldwin is prohibited.

About the author

Richard Baldwin is a college professor (at Austin Community College in Austin, TX) and private consultant whose primary focus is a combination of Java, C#, and XML. In addition to the many platform and/or language independent benefits of Java and C# applications, he believes that a combination of Java, C#, and XML will become the primary driving force in the delivery of structured information on the Web.

Richard has participated in numerous consulting projects, and he frequently provides onsite training at the high-tech companies located in and around Austin, Texas. He is the author of Baldwin’s Programming Tutorials, which have gained a worldwide following among experienced and aspiring programmers. He has also published articles in JavaPro magazine.

Richard holds an MSEE degree from Southern Methodist University and has many years of experience in the application of computer technology to real-world problems.

Baldwin@DickBaldwin.com

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories