JDBC and MySQL: Run the Programs, Page 4
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
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
Originally published on https://www.developer.com.
Page 4 of 4
This article was originally published on October 5, 2004