|
Using JDBC with MySQL, Getting Started
By Richard G. Baldwin
Java Programming Notes # 662
Preface
Purpose
The purpose of this lesson 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.
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 Gamelan.com.
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.
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.
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-ga\docs\index.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.2\jre\lib\ext, 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:\mysql\bin\mysqld --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:\mysql\bin\mysqld-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:\mysql\bin\mysqld-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:\mysql\bin\mysqld-opt --console
040918 13:59:47 InnoDB: Started
C:\mysql\bin\mysqld-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:\mysql\bin\mysqladmin -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:\mysql\bin\mysqladmin -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:\mysql\bin\mysqld-opt:
Normal shutdown
040918 14:00:03 InnoDB: Starting shutdown...
040918 14:00:05 InnoDB: Shutdown completed
040918 14:00:05 C:\mysql\bin\mysqld-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:\mysql\bin. 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:\mysql\bin\mysql --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:\mysql\bin\mysql --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:\mysql\bin\mysql --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.
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:\mysql\bin\mysql --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).
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:
- Load and register the JDBC driver classes (programming interface) for the database server that
you intend to use.
- Get a Connection object that represents a connection to the database
server (analogous to logging onto the server).
- Get one or more Statement objects for use in manipulating the database.
- Use the Statement objects to manipulate the database.
- 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 | 
Managing the Modern Network
Business Service Management: Generate Revenue Through IT 
Evaluating Software as a Service for Your Business
Storage Networking: Configuration and Planning
Is Your Disaster Recovery Plan Good Enough? 
