Java Web Programming with Eclipse contents
Last modified January 27, 2011 05:05 am

back next

The MySQL Database Server

Objectives

References

From the MySQL Manual (version 5.1)

Video

Overview

This chapter describes the installation and use of the MySQL database server for use in Java Web applications. MySQL is a free, open source database server that has been in development since 1995. Over the years it has matured significantly so that it is now widely known and relied on as a backend service for Web applications.

Install MySQL

Go to the MySQL website, locate the generally available release (stable, production-ready release) of the MySQL community database server for your platform, download and install. Make sure that you add the folder of mysql executibles added to your path. If you are installing on Windows, you can do this by checking Include Bin Directory in Windows PATH.

Configure Firewall

If you are running security software on your computer, such as a firewall program, you need to configure it so that MySQL server has sufficient privileges. For our purposes, the privileges needed by the MySQL server include the following.

Although we took this precaution in the previous subsection, it is still a good idea to block incoming connections to port 3306, so that only processes running on the local machine may connect to the server using TCP. If you set this restriction, make sure that the port is still open to local processes.

Test

After installation is complete, test that everything is OK. In Windows, run the MySQL command line client through the start menu. On Linux, Mac or Windows, you can run the mysql command line client from a command prompt with the command mysql -u root -p. Enter the password that you specified during installation. The mysql prompt appears as shown in the following figure.

MySQL Command Line Client window

The MySQL command line client is a program that allows the user to submit standard SQL commands to the database server and view the results. It also allows the user to submit non-SQL commands that are uniquely supported by MySQL. In a web application, you will specify these commands as strings in your java code, and submit them to the database through the JDBC database abstraction layer. To test that the server is running, enter the following command at the mysql prompt and press the enter key. Note that the command is terminated with a semicolon, which is a detail that you should remember to perform on all the database commands.

mysql> show databases;

The following figure shows the result of the above command.

Result of the show databases command

As you can see, the show databases command resulted in a table with a single column. The column heading includes the name Database and the three rows under it show the names of the three databases that were created by the installation program. In general, when a command produces a response, the response will be formatted as a table.

MySQL-Specific Commands

This section contains description of several important commands that are specific to MySQL that you will need to know in order to develop web applications with MySQL database support.

List Databases

The following command lists the databases on the MySQL server host.

mysql> show databases;

Quit

The following command terminates the mysql command line client.

mysql> quit;

Cancel Partially Entered Command

If you want to cancel a command that has been partially entered, type \c. The following illustrates how to cancel a multi-line command.

mysql> select *
mysql> from \c

Select a Database

mysql> use db_name;

This command tells MySQL to use the db_name database as the current database for subsequent commands.

Create a Database

The following command creates a database with the given name.

mysql> create database db_name;

Drop a Database

The following command drops all tables in the database and deletes the database. Be very careful with this command!

mysql> drop database db_name;

Describe a Table

mysql> desc tbl_name;

This command provides information about the columns in a table.

Basic SQL Commands

This section describes several important SQL commands that are part of the SQL language standard, and thus available on most, if not all, relational databases.

Create a Table

mysql> create table tbl_name(col_definition1, col_definition2, ...);

This command creates a table with the given name and column definitions. A column definition includes a column name, a data type and optional attributes (unique, not null, etc.).

Drop a Table

mysql> drop table tbl_name;

This command deletes a table. All table data and the table definition are removed, so be careful with this command!

Insert Data

mysql> insert into tbl_name values(col_value1, col_ value2, ...);

This command inserts a new row into an existing table.

Retrieve Data From a Single Table

mysql> select * from tbl_name;
mysql> select * from tbl_name where col_name1 = col_value1;
mysql> select col_name1, col_name2 from tbl_name;

These commands show different ways to retrieve data from a single table. The asterisk in the first and second examples indicates that data is to be retrieved from all columns in the table, whereas the third example lists the columns from which data is to be retrieved. The first and third examples retrieve data from every row in the table, whereas the second example retrieves data only from rows in which col_name1 has the specific value col_value1.

In a later chapter, we explain how to use the select command to retrieve data from multiple tables.

Examples

The following examples illustrate the commands described previously. In each example, we show the command that is submitted at the mysql prompt and the result that is displayed to the user.

Create a new database called bookstore.

mysql> create database bookstore;
Query OK, 1 row affected (0.03 sec)

Make the bookstore database the current database on which all subsequent commands will operate.

mysql> use bookstore;
Database changed

Within the bookstore database, create a table called book with 3 columns. The name of the first column is id and its data type is integer. The name of the second column is title and its data type is variable-length string with a maximum of 40 characters. The name of the third column is price and its data type is double (double precision floating point number).

mysql> create table book (id integer, title varchar(40), price double);
Query OK, 0 rows affected (0.13 sec)

Insert a new row into the book table. The order of the values correspond to the order of the columns within the create table command, so that the integer 1 is stored into the id column, the string XML Programming is stored into title column and the floating point number 34.0 into the price column.

mysql> insert into book values (1, 'XML Programming', 34.0);
Query OK, 1 row affected (0.03 sec)

Insert a second row into the book table.

mysql> insert into book values (2, 'Algorithms', 42.0);
Query OK, 1 row affected (0.02 sec)

Display the data in all of the columns in all of the rows of the book table.

mysql> select * from book;
+------+-----------------+-------+
| id   | title           | price |
+------+-----------------+-------+
|    1 | XML Programming |    34 |
|    2 | Algorithms      |    42 |
+------+-----------------+-------+
2 rows in set (0.03 sec)

Drop the book table. This will delete all the data in the book table and remove its definition from the bookstore database.

mysql> drop table book;
Query OK, 0 rows affected (0.08 sec)

Drop the bookstore database. This will delete the bookstore database and remove its definition from the database server.

mysql> drop database bookstore;
Query OK, 0 rows affected (0.00 sec)

Create a Database of News Items

In this section we will create 3 scripts to manipulate the publisher database. The first script createdb.sql contains the SQL commands to create the two tables used by the application: news_item and sequence. The second script insertdb.sql contains the SQL commands to insert test data into the news_item table. The third script cleandb.sql contains the SQL commands to delete all data from the publisher, including the table definitions.

It is possible to run these scripts from the command line interface of the operating system. However, it is also convenient to run them from within Eclipse. For this purpose, we create an ant build script that lets us run these scripts from within Eclipse. Ant is a widely used command-line based build system for Java projects, so it's useful to become familar with it. We will also use Ant in later chapters to perform other tasks. Ant is built into Eclipse, so there is no need to install anything in order to use it.

Before running these scripts, we need to first create a mysql database called publisher. You should do that now, using the database root account as follows. (Note that the second command is given in multi-line format.)

mysql> create database publisher;
mysql> grant all privileges on publisher.* to publisher@localhost identified by 'publisher';

The first command given above creates an empty database called publisher. The second command creates a database user called publisher with password publisher (the argument of "identified by") and with privileges to perform all possible manipulations of the database.

Inside Eclipse, create a folder in the publisher project called database and within that folder create a file called createdb.sql with the following contents. (If an external window pops up when you try this, then close this window and right click on cleandb.sql and select Open with text editor.)

create table news_item
(
   id integer primary key,
   title text not null,
   url text not null
);
create table sequence
(
   next_value integer
);
insert into sequence value (1000);

The first command in the above file is the SQL create table command. The command creates a table (in the currently selected database) called news_item with 3 fields. The first field is called id and it has datatype integer (an 8 byte signed integer) and it is declared as a primary key. A primary key is a field (or composite of several fields) that contains a unique value that can be used to identify the data in a single row in the table. The database enforces the uniqueness of this field, which means that if you try to insert a new row with a primary key equal to the primary key of an existing row, the insert operation will fail. The publisher application that we will subsequently discuss will follow the convention of making all primary keys integers (8-byte longs in Java) and they will all have the name id in which ever table they appear. The values for the primary keys will have no "business information." Such keys are refered to as surrogate primary keys or pseudo primary keys rather than natural primary keys, which do contain business information. After examining the publisher application, we will look at an application that implements a wiki. In the wiki application we will use natural primary keys.

Each row in the the news_item table will contain information about a single news item in a news feed. The id field is used to uniquely identify the news_item.

In addition to defining a primary key called id, the first command above also creates a field called title, which will contain the titles of news items, and a field called url, which will contain the link of the news article to which the news item refers. Both title and url are declared as text, which means they contain character data with a very large size limitation. These fields are also declared as not null, which means these fields must contain string data and can not be set to null. Note that setting a not null character field to an empty is allowed because this is different from setting the field to null.

The second command in the above file is also a create table command. In this case, we create a table called sequence that will be used to store a single value: the next value to be used for a new primary key. The third command is an SQL insert command that inserts a row into the sequence table. By inserting the number 1000 in this table, we mean for our primary keys to begin from 1000. Every time we use a primary key, we will increment this value.

Note that MySQL includes an auto_increment setting for integer primary keys and that many programmers use this as a way to automatically generate unique primary keys. It has the advantage of eliminating the need for a separate sequence table to store the next primary key value. However, this functionality is not part of the SQL standard and is not provided in all databases. For example, PostgreSQL does not provide an auto incrementing feature. For this reason, we use a separate sequence table, which is supported on all relational databases. The benefit is that we can replace the database with any other SQL compliant database without modifying our code. This type of portability is important when a software system may be used by multiple clients who prefer to use a database of their choise rather than the particular database the application is written for.

Create another script called insertdb.sql with the following contents.

insert into news_item (id, title, url) values (1, 'CNN', 'http://www.cnn.com/');
insert into news_item (id, title, url) values (2, 'FOX News', 'http://www.foxnews.com/');

The purpose of insertdb.sql is to insert test data into the database. The above script insert two rows into the news_time table. As you can see from the syntax, the first part of the insert command specifies in a parenthetical list the name and order of the fields (or columns) to be set, and the second part of the command provides in a parenthetical list the values to be placed in those fields.

Create another script called cleandb.sql with the following contents.

drop table if exists news_item;
drop table if exists sequence;

The purpose of the cleandb.sql script is to delete all data in the database including the table definitions.

As we develop the application incrementally, we will need to make frequent changes to the structure of the database. Each time we make a change to the structure of the database, we first run cleandb to delete everything, followed by createdb to create the new structure, followed by insertdb to insert new test data.

Create Ant Build File

Create a file called build.xml in the database folder of the publisher project and insert the contents of the following listing into it.

<?xml version="1.0" encoding="UTF-8"?>

<project name="publisher" default="all" basedir=".">
  <property name="mysql.params" value="-u publisher -ppublisher -D publisher" />
  <target name="all" depends="cleandb, createdb, insertdb"></target>

  <target name="cleandb">
     <exec executable="mysql" input="cleandb.sql">
        <arg line="${mysql.params}" />
     </exec>
  </target>

  <target name="createdb">
     <exec executable="mysql" input="createdb.sql">
        <arg line="${mysql.params}" />
     </exec>
  </target>

  <target name="insertdb">
     <exec executable="mysql" input="insertdb.sql">
        <arg line="${mysql.params}" />
     </exec>
  </target>
</project>

The root element of the build file is the project element. The project element has a required attribute called default, which defines a default target to execute. Under the root element there are various child elements, including target elements. The target elements represent the various jobs that you can execute. Each target element has a required attribute called name, which you can use to identify which targets you wish to run when you run ant.

In our build file, the first child element under the root project element is a property element. Property elements such as this one simply assign a value to a name, which can be de-referenced elsewhere in the build file. In our build file, we assign the value -u publisher -ppublisher -D publisher to the name mysql.params. The value is a string that contains the command line parameters we need to pass to the mysql command line client when running the various scripts against the publisher database. Note that there is no space between -p and publisher.

The first target all simply executes all the other targets, which is done by including the names of the other targets in the depends attribute of the all target. The second target cleandb runs the cleandb.sql script. The third target createdb runs the createdb.sql script. The fourth target insertdb runs the insertdb.sql script.

Run Targets

Run the createdb and insertdb targets by doing the following steps.

  1. Right click build.xml and select Run As ... Ant Build.... (Make sure you choose the Ant Build option that ends with the ellipsis "...")
  2. Uncheck the box next to all [default].
  3. Check the createdb checkbox, and then check the insertdb checkbox in that order.
  4. Look in the box titled Target Execution Order and verify that the order of execution is createdb, insertdb. The following screen shot shows the result of carrying out steps 1 through 4.
  5. Select Run.
Run targets window

To check the result of running targets, do the following MySQL commands.

mysql> use publisher;
mysql> show tables;
mysql> select * from news_item; 

The following figure shows the result of the above commands.

Result of MySQL commands

Exercises

(1) MAX function

Research the mysql documentation to find out how to obtain the largest value stored within a field in a table. Hint: use the MAX function within a select command.

(2) Table Joins

Research the MySQL documentation to learn about table joins and then create a database to store information about shirts and their owners. Construct a person table so that it contains information about persons. Construct a shirt tables so that it contains information about shirts that are owned by the persons in the person table. Use surrogate primary keys for both tables and name the primary keys in both tables with the name id. Construct a select statement that generates a table showing the attributes of each shirt including the names of their owners.

back next

Copyright 2007-2009 David Turner and Jinseok Chae. All rights reserved.