INEW 2338, Advanced Java Study Guide:  Java Database Connectivity (JDBC)

Published November 3, 2004
By Richard G. Baldwin

File: Inew2338Sg003.htm

Welcome

The purpose of this series of tutorial lessons is to help you learn the essential topics covered in INEW 2338, Advanced Java.

These lessons provide questions, answers, and explanations designed to help you to understand the essential Java features covered by the Advanced Java course.

The textbook for this course is Advanced Java Internet Applications, Second Edition by Art Gittleman, ISBN 1-57676-096-0.  This study guide is for Chapter 3 in the textbook.

In addition to the textbook, I recommend that you also study my extensive collection of online Java tutorial lessons.  Those tutorial lessons are published at http://www.dickbaldwin.com.

For this particular study guide, you should study lesson 662 entitled Using JDBC with MySQL, Getting Started at the URL given above.  In addition, several of the other lessons in the series from 660 through 695 contain important information about using JDBC.

Comments Regarding the Database Management System

Chapter 3 in the textbook is written around the use of Microsoft Access as a DBMS.  That might be all right in a classroom setting where the college provides laboratory computers running under Windows with Microsoft Access installed.  However, I believe that we should be much more generic for the benefit of our online students.  If possible, we should avoid requiring our students to have access to either the Windows operating system or the Microsoft Access DBMS software.

Therefore, this study guide is written around the use of the DBMS known as MySQL.  This DBMS is compatible with a variety of platforms, and is freely available for downloading at http://www.mysql.com/.

More specifically, this study guide is written around the use of MySQL version 4.0.21.  It is strongly recommended that you download this version, or some version that is at least as recent as 4.0.21 but earlier than version 4.1.  At the time of preparation of this study guide, version 4.1 had not been released for production use.  However, the early release documentation indicated that there may be changes in version 4.1 that may not be backward compatible with version 4.0.21.

Unless the folks at MySQL change their policy, version 4.0.21 will continue to be available for downloading even after version 4.1 has been released for production.

The following information was extracted from the database server documentation for MySQL 4.0.21, Section 2.4.3 entitled Securing the Initial MySQL Accounts.

Part of the MySQL installation process is to set up the mysql database containing the grant tables:

The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows:

As noted, none of the initial accounts have passwords. This means that your MySQL installation is unprotected until you do something about it.

All of the questions in this study guide assume that the MySQL software is installed in the default location, which is C:\mysql.  Unless the question deals with starting or stopping the server, it is assumed that the server has been started and is "ready for connections."  The questions assume that root accounts and anonymous accounts exist on the server as described above.

Unfortunately, in order to make it possible to write meaningful questions, it is sometimes necessary to assume a particular operating system for such things as directory separator symbols, etc.  In those cases, the syntax for a Windows operating system will be used.  If you are using a different operating system, you will need to translate the Windows syntax into the syntax that is appropriate for your operating system.

Questions

1.  True or False?  The MySQL version 4.0.21 server can be started by executing the following command at the command prompt.

C:\mysql\bin\mysqld-opt --console

Answer and Explanation

2.  True or False?  The MySQL version 4.0.21 server can be shut down by executing the following command at the command prompt.

C:\mysql\bin\mysqladmin root shutdown

Answer and Explanation

3.  True or False?  When MySQL is first installed, and before any databases or any new users have been added, execution of the following statement on the command line will produce the output shown.

C:\jnk>C:\mysql\bin\mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
+-----------+

C:\jnk>

Answer and Explanation

4.  True or False?  When MySQL is first installed, and before any databases or any new users have been added, execution of the following statement on the command line will produce the output shown.

C:\jnk>C:\mysql\bin\mysqlshow mysql -u root
Database: mysql
+--------------+
| Tables       |
+--------------+
| columns_priv |
| db           |
| func         |
| host         |
| tables_priv  |
| user         |
+--------------+

C:\jnk>

Answer and Explanation

5.  True or False?  Entering the command shown below will start the monitor program running and produce a screen output similar to that shown.  (Note that line breaks were manually entered  into this and several following displays of screen output to cause the screen output to fit into this narrow publication format.)

C:\jnk>c:\mysql\bin\mysql
Welcome to the MySQL monitor. Commands end 
with ; or \g.
Your MySQL connection id is 15 to server 
version: 4.0.21

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

mysql>

Answer and Explanation

6.  True or False?  Starting the monitor program as shown below and entering the SQL command shown at the mysql> prompt will cause a new database named JunkDB to be created with the confirmation shown (assuming that a database with that name doesn't already exist).  Then entering \q at the prompt will cause the monitor program to terminate as shown.

C:\jnk>c:\mysql\bin\mysql --user=root mysql
Welcome to the MySQL monitor. Commands end 
with ; or \g.
Your MySQL connection id is 16 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.02 sec)

mysql> \q
Bye

C:\jnk>

Answer and Explanation

7.  Assume that a file in the current directory named a.txt contains the text shown below.

CREATE DATABASE JunkDB;

Assume that the user enters the following command at the command prompt.

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

True or False?  This procedure will create a new database named JunkDB, (assuming that a database with that name doesn't already exist).

Answer and Explanation

8.  What output is produced by the following program? 

/*File Inew2338_030.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;

public class Inew2338_030{
  public static void main(String args[]){
    try {
      Class.yourName("mysql.jdbc.Driver");
      String url = "mysql://localhost/test";
      Connection con =
          DriverManager.getConnection(url,"","");

      DatabaseMetaData metaData =
                               con.getMetaData();
      System.out.print(
         metaData.supportsANSI92EntryLevelSQL());
      System.out.print(" " +
       metaData.supportsANSI92IntermediateSQL());
      System.out.println(" " +
               metaData.supportsANSI92FullSQL());

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

Answer and Explanation

9.  What output is produced by the following program? 

/*File Inew2338_031.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;

public class Inew2338_031{
  public static void main(String args[]){
    try {
      Class.forName("mysql.jdbc.Driver");
      String url = "mysql://localhost/test";
      Connection con =
          DriverManager.getConnection(url,"","");

      DatabaseMetaData metaData =
                               con.getMetaData();
      System.out.print(
         metaData.supportsANSI92EntryLevelSQL());
      System.out.print(" " +
       metaData.supportsANSI92IntermediateSQL());
      System.out.println(" " +
               metaData.supportsANSI92FullSQL());

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

Answer and Explanation

10.  What output is produced by the following program? 

/*File Inew2338_032.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;

public class Inew2338_032{
  public static void main(String args[]){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url = "mysql://localhost/test";
      Connection con =
          DriverManager.getConnection(url,"","");

      DatabaseMetaData metaData =
                               con.getMetaData();
      System.out.print(
         metaData.supportsANSI92EntryLevelSQL());
      System.out.print(" " +
       metaData.supportsANSI92IntermediateSQL());
      System.out.println(" " +
               metaData.supportsANSI92FullSQL());

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

Answer and Explanation

11.  What output is produced by the following program? 

/*File Inew2338_033.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;

public class Inew2338_033{
  public static void main(String args[]){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://localhost/test";
      Connection con =
          DriverManager.getConnection(url,"","");

      DatabaseMetaData metaData =
                               con.getMetaData();
      System.out.print(
         metaData.supportsANSI92EntryLevelSQL());
      System.out.print(" " +
       metaData.supportsANSI92IntermediateSQL());
      System.out.println(" " +
               metaData.supportsANSI92FullSQL());

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

Answer and Explanation

12.  True or False?  The program shown in the first box below will produce an output very similar to the output shown in the second box, where the second box contains a list of the databases currently on the database server, which are known to the user named root.

/*File Inew2338_036.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, and MySql
version 4.0.21-win.
************************************************/
import java.io.*;

public class Inew2338_036{
  public static void main(String args[]){

    try {
      Process pr = Runtime.getRuntime().exec(
               "C:/mysql/bin/mysqlshow -u root");
      InputStream inStr = pr.getInputStream();
      int input = inStr.read();
      while(input != -1){
        System.out.print((char)input);
        input = inStr.read();
      }//end while
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end main

}//end class Inew2338_036
 

+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
+-----------+

Answer and Explanation

13.  True or False?  The program shown in the first box below will produce an output very similar to the output shown in the second box, where the second box contains a database named junkdb, which may have case preserved on some systems, and may not have case preserved on other systems.  

/*File Inew2338_037.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;
import java.io.*;

public class Inew2338_037{
  public static void main(String args[]){

    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();

      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      stmt.executeUpdate(
                       "CREATE DATABASE JunkDB");

      showDB();

      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

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

  static void showDB(){
    try{
      Process pr = Runtime.getRuntime().exec(
               "C:/mysql/bin/mysqlshow -u root");
      InputStream inStr = pr.getInputStream();
      int input = inStr.read();
      while(input != -1){
        System.out.print((char)input);
        input = inStr.read();
      }//end while
    }catch(Exception e){
      e.printStackTrace();
    }//end catch
  }//end showDB

}//end class Inew2338_037
 

+-----------+
| Databases |
+-----------+
| junkdb    |
| mysql     |
| test      |
+-----------+

Answer and Explanation

14.  True or False?  The program shown in the first box below will produce an output very similar to the output shown in the second box, where the second box identifies a table belonging to a database named junkdb.  The database name and table name may have case preserved on some systems, and may not have case preserved on other systems.

/*File Inew2338_038.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;
import java.io.*;

public class Inew2338_038{
  public static void main(String args[]){

    try {
      makeJunkDB();
      makeUser();
      makeTable();
      showTable();
      dropJunkDB();
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end main
  //-------------------------------------------//

  static void makeTable(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/JunkDB";
      Connection con =
                     DriverManager.getConnection(
                        url,"auser", "drowssap");
      Statement stmt = con.createStatement();
      stmt.executeUpdate(
            "CREATE TABLE myTable(test_id int," +
                  "test_val char(15) not null)");
      con.close();
    }catch( Exception e ) {
      e.printStackTrace();
    }//end catch
  }//end makeTable
  //-------------------------------------------//

  static void makeUser(){
    try{
      Statement stmt;
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      stmt = con.createStatement();
      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 makeUser
  //-------------------------------------------//

  static void showTable(){
    try{
      Process pr = Runtime.getRuntime().exec(
        "C:/mysql/bin/mysqlshow JunkDB -u root");
      InputStream inStr = pr.getInputStream();
      int input = inStr.read();
      while(input != -1){
        System.out.print((char)input);
        input = inStr.read();
      }//end while
    }catch(Exception e){
      e.printStackTrace();
    }//end catch
  }//end showTable
  //-------------------------------------------//

  static void makeJunkDB(){
    try{
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();

      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      stmt.executeUpdate(
                       "CREATE DATABASE JunkDB");
      con.close();
    }catch(Exception e){
      e.printStackTrace();
    }//end catch
  }//end makeJunkDB
  //-------------------------------------------//

  static void dropJunkDB(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();
      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      con.close();
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end dropJunkDB

}//end class Inew2338_038
 

Database: JunkDB
+---------+
| Tables  |
+---------+
| mytable |
+---------+

Answer and Explanation

15.  True or False?  The program shown in the first box below produces the output shown in the second box.

/*File Inew2338_039.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;
import java.io.*;

public class Inew2338_039{
  public static void main(String args[]){

    try {
      makeJunkDB();
      makeUser();
      makeTable();
      processTable();
      dropJunkDB();
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end main
  //-------------------------------------------//

  static void processTable(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/JunkDB";
      Connection con =
                     DriverManager.getConnection(
                        url,"auser", "drowssap");
      Statement stmt = con.createStatement();

      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')");

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

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

      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

      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

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

  static void makeTable(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/JunkDB";
      Connection con =
                     DriverManager.getConnection(
                        url,"auser", "drowssap");
      Statement stmt = con.createStatement();
      stmt.executeUpdate(
            "CREATE TABLE myTable(test_id int," +
                  "test_val char(15) not null)");
      con.close();
    }catch( Exception e ) {
      e.printStackTrace();
    }//end catch
  }//end makeTable
  //-------------------------------------------//

  static void makeUser(){
    try{
      Statement stmt;
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      stmt = con.createStatement();
      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 makeUser
  //-------------------------------------------//

  static void makeJunkDB(){
    try{
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();

      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      stmt.executeUpdate(
                       "CREATE DATABASE JunkDB");
      con.close();
    }catch(Exception e){
      e.printStackTrace();
    }//end catch
  }//end makeJunkDB
  //-------------------------------------------//

  static void dropJunkDB(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();
      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      con.close();
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end dropJunkDB

}//end class Inew2338_039
 

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

Answer and Explanation

16.  Which, if any, of the following lines of text were likely to be produced by the following program? 

/*File Inew2338_040.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;

public class Inew2338_040{
  public static void main(String args[]){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://localhost/test";
      Connection con =
          DriverManager.getConnection(url,"","");

      DatabaseMetaData metaData =
                               con.getMetaData();
      System.out.print(
        metaData.getDatabaseProductName() + " ");
      System.out.println(
        metaData.getDatabaseProductVersion());
      System.out.print(
        metaData.getDriverName() + " ");
      System.out.print(
        metaData.getDriverMajorVersion() + ".");
      System.out.println(
        metaData.getDriverMinorVersion());
      System.out.print("JDBC " +
        metaData.getJDBCMajorVersion() + ".");
      System.out.println(
        metaData.getJDBCMinorVersion() );
      System.out.println(
        metaData.getURL());
      System.out.println(
        metaData.getUserName());
      con.close();
    }catch( Exception e ) {
      e.printStackTrace();
    }//end catch
  }//end main
}//end class Inew2338_040

Answer and Explanation

17.  True or False?  The program in the first box below produces the output shown in the second box?

/*File Inew2338_041.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;

public class Inew2338_041{
  public static void main(String args[]){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql://localhost/test";
      Connection con =
          DriverManager.getConnection(url,"","");
      Statement stmt = con.createStatement();

      try{
        stmt.executeUpdate("DROP TABLE myTable");
      }catch(Exception e){
        //Do nothing
      }//end catch

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

      ResultSet rs = stmt.executeQuery(
                        "SELECT * from myTable");

      ResultSetMetaData rsmd = rs.getMetaData();
      System.out.println("Table: " +
                           rsmd.getTableName(1));
      System.out.println("Number of columns: " +
                          rsmd.getColumnCount());
      for(int col = 0;
              col < rsmd.getColumnCount();col++){
        System.out.println("\nColumn Number: " +
                        Integer.toString(col+1));
        System.out.println("Column Label: " +
                     rsmd.getColumnLabel(col+1));
        System.out.println("Colum Name: " +
                      rsmd.getColumnName(col+1));
        System.out.println("Column Type: " +
                  rsmd.getColumnTypeName(col+1));
      }//end for loop

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

Table: theTable
Number of columns: 2

Column Number: 1
Column Label: testID
Colum Name: testID
Column Type: int

Column Number: 2
Column Label: testVal
Colum Name: testVal
Column Type: String

Answer and Explanation

18.  What output is produced by the following program? 

/*File Inew2338_042.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;
import java.io.*;

public class Inew2338_042{
  public static void main(String args[]){

    try {
      makeJunkDB();
      makeUser();
      makeTable();
      processTable();
      dropJunkDB();
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end main
  //-------------------------------------------//

  static void processTable(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/JunkDB";
      Connection con =
                     DriverManager.getConnection(
                        url,"auser", "drowssap");
      Statement stmt = con.createStatement();

      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')");

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

      ResultSet rs = stmt.executeQuery(
              "SELECT SUM(test_id),MAX(test_id),"
                + "COUNT(test_id) from myTable");

      while(rs.next()){
        System.out.println(rs.getInt(0) + " "
                         + rs.getInt(1) + " "
                         + rs.getInt(2));
      }//end while loop

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

  static void makeTable(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/JunkDB";
      Connection con =
                     DriverManager.getConnection(
                        url,"auser", "drowssap");
      Statement stmt = con.createStatement();
      stmt.executeUpdate(
            "CREATE TABLE myTable(test_id int," +
                  "test_val char(15) not null)");
      con.close();
    }catch( Exception e ) {
      e.printStackTrace();
    }//end catch
  }//end makeTable
  //-------------------------------------------//

  static void makeUser(){
    try{
      Statement stmt;
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      stmt = con.createStatement();
      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 makeUser
  //-------------------------------------------//

  static void makeJunkDB(){
    try{
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();

      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      stmt.executeUpdate(
                       "CREATE DATABASE JunkDB");
      con.close();
    }catch(Exception e){
      e.printStackTrace();
    }//end catch
  }//end makeJunkDB
  //-------------------------------------------//

  static void dropJunkDB(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();
      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      con.close();
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end dropJunkDB

}//end class Inew2338_042

Answer and Explanation

19.  Using MySQL 4.0.21, what output is produced by the following program? 

/*File Inew2338_043.java
Copyright 2004, R.G.Baldwin

Tested using Java SDK 1.4.2, WinXP, MySql
version 4.0.21-win, and JDBC connector
version mysql-connector-java-3.0.15-ga.
************************************************/

import java.sql.*;
import java.io.*;

public class Inew2338_043{
  public static void main(String args[]){

    try {
      makeJunkDB();
      makeUser();
      makeTable();
      processTable();
      dropJunkDB();
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end main
  //-------------------------------------------//

  static void processTable(){
    String[] data = {"One","Two","Three","Four",
                                   "Five","Six"};
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/JunkDB";
      Connection con =
                     DriverManager.getConnection(
                        url,"auser", "drowssap");
      Statement stmt = con.createStatement();
      String query =
         "INSERT INTO myTable(test_id,test_val) "
                                 + "VALUES(?,?)";
      PreparedStatement pStmt =
                     con.prepareStatement(query);
      for(int cnt = 0;cnt < 7;cnt++){
        pStmt.setInt(1,cnt + 1);
        pStmt.setString(2,data[cnt]);
        pStmt.executeUpdate(query);
      }//end for loop

      ResultSet rs = stmt.executeQuery(
              "SELECT SUM(test_id),MAX(test_id),"
                + "COUNT(test_id) from myTable");

      while(rs.next()){
        System.out.println(rs.getInt(1) + " "
                         + rs.getInt(2) + " "
                         + rs.getInt(3));
      }//end while loop

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

  static void makeTable(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/JunkDB";
      Connection con =
                     DriverManager.getConnection(
                        url,"auser", "drowssap");
      Statement stmt = con.createStatement();
      stmt.executeUpdate(
            "CREATE TABLE myTable(test_id int," +
                  "test_val char(15) not null)");
      con.close();
    }catch( Exception e ) {
      e.printStackTrace();
    }//end catch
  }//end makeTable
  //-------------------------------------------//

  static void makeUser(){
    try{
      Statement stmt;
      Class.forName("com.mysql.jdbc.Driver");
      String url =
            "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      stmt = con.createStatement();
      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 makeUser
  //-------------------------------------------//

  static void makeJunkDB(){
    try{
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();

      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      stmt.executeUpdate(
                       "CREATE DATABASE JunkDB");
      con.close();
    }catch(Exception e){
      e.printStackTrace();
    }//end catch
  }//end makeJunkDB
  //-------------------------------------------//

  static void dropJunkDB(){
    try {
      Class.forName("com.mysql.jdbc.Driver");
      String url =
             "jdbc:mysql://localhost:3306/mysql";
      Connection con =
                     DriverManager.getConnection(
                                 url,"root", "");
      Statement stmt = con.createStatement();
      stmt.executeUpdate(
               "DROP DATABASE IF EXISTS JunkDB");

      con.close();
    }catch( Exception e ){
      e.printStackTrace();
    }//end catch
  }//end dropJunkDB

}//end class Inew2338_043

Answer and Explanation

- end -


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 and XML. In addition to the many platform-independent benefits of Java applications, he believes that a combination of Java and XML will become the primary driving force in the delivery of structured information on the Web.

Richard has participated in numerous consulting projects involving Java, XML, or a combination of the two.  He frequently provides onsite Java and/or XML training at the high-tech companies located in and around Austin, Texas.  He is the author of Baldwin's Java Programming Tutorials, which has gained a worldwide following among experienced and aspiring Java programmers. He has also published articles on Java Programming in Java Pro 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

Answers and Explanations

Answer 19

A.  Runtime Error:  java.sql.SQLException: Syntax error or access violation message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?)' at line 1"

Explanation 19

The documentation for MySQL 4.0.21 contains the following statement in Section 21.2.4 C entitled API Prepared Statements.

"As of MySQL 4.1, the client/server protocol provides for the use of prepared statements."

This indicates that MySQL 4.0.21 does not support prepared statements, which is required for this program.

You might also want go to Google and search for the following keywords:

This might help you to locate some useful publications on this topic.

Back to Question 19

Answer 18

A.  Runtime error:  java.sql.SQLException: Column Index out of range ( 0 > 3).

Explanation 18

If you were to modify the while loop in the processTable method to read as follows, the correct answer would be:

B.  15 5 5

      while(rs.next()){
        System.out.println(rs.getInt(1) + " "
                         + rs.getInt(2) + " "
                         + rs.getInt(3));
      }//end while loop

Unlike in many modern programming languages, when you specify column numbers in SQL, you must start with column number 1 instead of column number 0.

This program illustrates the use of Aggregate Functions.

You might also want go to Google and search for the following keywords:

This might help you to locate some useful publications on these topics.

Back to Question 18

Answer 17

False.

Explanation 17

This program produces the output shown below. 

Table: myTable
Number of columns: 2

Column Number: 1
Column Label: test_id
Colum Name: test_id
Column Type: INTEGER

Column Number: 2
Column Label: test_val
Colum Name: test_val
Column Type: CHAR

The purpose of this program is to illustrate the types of information that are available through the use of an object of type ResultSetMetaData.

You can learn more about this topic in lessons 682 and 688 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 17

Answer 16

F.  All of the given lines of text were produced by the program.

Explanation 16

The purpose of this question is to illustrate a few of the more than 150 methods that are provided to make it possible for the JDBC program to gather information about the database.

You can learn more about this topic in lesson 688 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 16

Answer 15

False.  The program throws the following exception:

java.sql.SQLException: Invalid authorization specification message from server:  "Access denied for user: 'auser@localhost' (Using password: NO)"

Explanation 15

This exception is thrown because the user was registered on the database named JunkDB with the password drowssap.  However, the user attempted to access the database with a blank password in the following statement:

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

Correcting that statement to make it read as follows would cause the program to produce the specified output.

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

This program illustrates how to perform the following operations from JDBC:

You can learn more about this topic in lessons 660 and 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 15

Answer 14

True

Explanation 14

The main purpose of this program is to show you one way to create new tables from within JDBC.

You can learn more about this topic in lessons 660 and 682 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 14

Answer 13

True.

Explanation 13

The main purpose of this program is to show you one way to create new databases from within JDBC.

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 13

Answer 12

True.

Explanation 12

The main purpose of this program is to acquaint you with the Java code that can be used to execute a MySQL Client or Utility program in a separate process and to display the output produced by that program.

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 12

Answer 11

B.  true false false

Explanation 11

This JDBC program gets a connection for the anonymous user whose name is blank and whose password is blank on the database named test on the MySQL server running on localhost.  The first three statements in this program implement the first three critical steps that must be performed in every JDBC program:

Then the program uses the connection to get a DatabaseMetaData object and uses that object to learn about the SQL level that is supported by MySQL.  There are many other operations that could be performed at this point, such as using a Statement object to perform updates or queries on the database.

Finally, the program closes the connection before terminating.  This is analogous to the user logging off of the database.

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 11

Answer 10

A.  Runtime Error:  SQLException: No suitable driver

Explanation 10

The correct URL for the database named test on the MySQL database server running on localhost is "jdbc:mysql://localhost/test".

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 10

Answer 9

A.  Runtime Error:  ClassNotFoundException: mysql.jdbc.Driver

Explanation 9

The correct name for the JDBC driver class for MySQL, written as a Java String is "com.mysql.jdbc.Driver".

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 9

Answer 8

A.  Compiler Error

Explanation 8

The correct method for registering a JDBC database driver class is named forName.   It is not named yourName.

You can learn more about this topic in lesson 660 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 8

Answer 7

True.

Explanation 7

The monitor program can be inconvenient to use interactively from the keyboard because it has no editing capability.  If you make a typing error in a long SQL command, you have no choice but to start over.

Fortunately, it is possible to run the monitor program with input redirection such that its input will be obtained from a text file instead of being received from the keyboard.  This makes it possible for you to use an editor to create and correct the SQL commands, and then to deliver the correct SQL commands directly to the monitor program.

You can learn more about this topic in lessons 660 and 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 7

Answer 6

False.

Explanation 6

Failing to end the SQL command with either a semicolon or \g simply causes the monitor program to display a continuation prompt as shown below.  The SQL command isn't operated upon until it is properly terminated.

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

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

mysql> CREATE DATABASE JunkDB
->

Properly terminating the SQL command with a semicolon or \g will cause the database to be created with the confirmation message shown below.

C:\jnk>c:\mysql\bin\mysql --user=root mysql
Welcome to the MySQL monitor. Commands end 
with ; or \g.
Your MySQL connection id is 16 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.02 sec)

mysql> \q
Bye

C:\jnk>

Also, entering \q at the mysql> prompt will cause the program to terminate as shown above.  Note that this is a q as in quit and is not a g as in good.

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 6

Answer 5

True.

Explanation 5

The monitor program is a program that allows a user to enter commands into the database server from the keyboard.

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 5

Answer 4

True.

Explanation 4

This command displays the tables that exist in the database named mysql at installation.

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 4

Answer 3

False

Explanation 3

The execution of the following command produces the output shown.

C:\jnk>C:\mysql\bin\mysqlshow
+-----------+
| Databases |
+-----------+
| test      |
+-----------+

C:\jnk>

It appears that there is an error in the MySQL documentation Section 2.4.1 entitled Windows Post-Installation Procedures.  While a new installation of MySQL does contain the two databases named mysql and test, it is necessary to log in as an administrator to see the table named mysql.  The following command, which establishes the user as the default administrator named root, will list both tables.

C:\jnk>C:\mysql\bin\mysqlshow -u root
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
+-----------+

C:\jnk>

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 3

Answer 2

False

Explanation 2

The proper command is shown below.

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

See Section 2.2.1.6 of the database server documentation entitled Starting MySQL from the Windows Command Line.

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 2
 

Answer 1

True

Explanation 1

The MySQL database server documentation refers to mysqld-opt as an optimized binary and recommends it for general use.  However, the documentation goes on to say that there will be a name change in MySQL 4.1.2 and the name of the optimized binary will be changed to simply mysqld in that version.

You can learn more about this topic in lesson 662 at http://www.dickbaldwin.com/tocadv.htm.

You might also want go to Google and search for the following keywords:

This might help you to locate some of Prof. Baldwin's publications on these topics that were not included in the lessons listed earlier.  Go to the last page of the Google results and click on the link that reads repeat the search with the omitted results included to make certain that Google didn't omit any links that might be useful to you.

Back to Question 1


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 and XML. In addition to the many platform-independent benefits of Java applications, he believes that a combination of Java and XML will become the primary driving force in the delivery of structured information on the Web.

Richard has participated in numerous consulting projects involving Java, XML, or a combination of the two.  He frequently provides onsite Java and/or XML training at the high-tech companies located in and around Austin, Texas.  He is the author of Baldwin's Java Programming Tutorials, which has gained a worldwide following among experienced and aspiring Java programmers. He has also published articles on Java Programming in Java Pro 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

-end-