April 20, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

JDBC and MySQL: Run the Programs, Page 4

  • October 5, 2004
  • By Richard G. Baldwin
  • Send Email »
  • More Articles »

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:\mysql\bin\mysqld-opt --console
pause

 

Listing 30 Contents of MySqlStart.bat

 

C:\mysql\bin\mysqladmin -u root shutdown
pause

 

Listing 31 Contents of MySqlStop.bat

 

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

 

Listing 32 MySqlCreateDatabase01.bat

 

CREATE DATABASE JunkDB;
\q

 

Listing 33 MySqlCreateDatabase01.txt

 

c:\mysql\bin\mysql --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:\mysql\bin\mysql --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:\mysql\bin\mysql --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:\mysql\bin\mysqld-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:\mysql\bin\mysqladmin -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
jre\lib\ext 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 jre\lib\ext 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:\mysql\bin\mysqld-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:\mysql\bin\mysqladmin -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
jre\lib\ext 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 jre\lib\ext 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:\mysql\bin\mysqld-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:\mysql\bin\mysqladmin -u root shutdown

You can prepare MySQL for use in three
alternative ways:
1. Using the control program named mysql located
in folder C:\mysql\bin 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
jre\lib\ext 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 jre\lib\ext 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


Tags: MySQL, JDBC



Page 4 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel