JDBC:Getting Started

The sample code that comes with this tutorial creates a database that is used by a proprietor of a small coffee house called The Coffee Break, where coffee beans are sold by the pound and brewed coffee is sold by the cup. To keep things simple, the proprietor needs only two tables, one for types of coffee and one for coffee suppliers.
The following steps configure a JDBC development environment with which you can compile and run the tutorial samples:

1. Install the latest version of the Java SE SDK on your computer.
2. Install your database management system (DBMS) if needed.
   You may use Java DB, which comes with the latest version of Java SE SDK. This tutorial has been tested for the following DBMS:
   
   • Java DB
   • MySQL
   Note that if you are using another DBMS, you might have to alter the code of the tutorial samples.
   
3. Install a JDBC driver from the vendor of your database.
   If you are using Java DB, it already comes with a JDBC driver. If you are using MySQL, install the latest version of Connector/J.
   To obtain a JDBC driver for a particular DBMS, refer to JDBC Data Access API.
   There are many possible implementations of JDBC drivers. These implementations are categorized as follows:
   
   • Type 1: Drivers that implement the JDBC API as a mapping to another data access API, such as ODBC (Open Database Connectivity). Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an example of a Type 1 driver.
   • Type 2: Drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited. Oracle's OCI (Oracle Call Interface) client-side driver is an example of a Type 2 driver.
   • Type 3: Drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client's requests to the data source.
   • Type 4: Drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.
   Check which driver type comes with your DBMS. Java DB comes with two Type 4 drivers, an Embedded driver and a Network Client Driver. MySQL Connector/J is a Type 4 driver.
   Installing a JDBC driver generally consists of copying the driver to your computer, then adding the location of it to your class path. In addition, many JDBC drivers other than Type 4 drivers require you to install a client-side API. No other special configuration is usually needed.
   
4. Install Apache Ant.
   These steps use Apache Ant, a Java-based tool, to build, compile, and run the JDBC tutorial samples. Go to the following link to download Apache Ant:
   http://ant.apache.org/
   Ensure that the Apache Ant executable file is in your PATH environment variable so that you can run it from any directory.
   
5. Download the sample code.
   The sample code, JDBCTutorial.zip, consists of the following files:
   
   • properties
     • javadb-build-properties.xml
     • javadb-sample-properties.xml
     • mysql-build-properties.xml
     • mysql-sample-properties.xml
   • sql
     • javadb
       • create-procedure-show-suppliers.sql
       • create-tables.sql
       • drop-tables.sql
       • populate-tables.sql
     • mysql
       • create-procedure-show-suppliers.sql
       • create-tables.sql
       • drop-tables.sql
       • populate-tables.sql
   • src/com/oracle/tutorial/jdbc
     • CoffeesTable.java
     • JDBCTutorialUtilities.java
     • JoinSample.java
     • StoredProcedureJavaDBSample.java
     • StoredProcedureMySQLSample.java
     • SuppliersTable.java
   • build.xml
   Create a directory to contain all the files of the sample. These steps refer to this directory as <JDBC tutorial directory>. Unzip the contents of JDBCTutorial.zip into <JDBC tutorial directory>.
   
6. Modify the build.xml file.
   The build.xml file is the build file that Apache Ant uses to compile and execute the JDBC samples. The files properties/javadb-build-properties.xml and properties/mysql-build-properties.xml contain additional Apache Ant properties required for Java DB and MySQL, respectively. The files properties/javadb-sample-properties.xml and properties/mysql-sample-properties.xml contain properties required by the sample.
   Modify these XML files as follows:
   
   1. In the build.xml file, modify the property ANTPROPERTIES to refer to either properties/javadb-build-properties.xml or properties/mysql-build-properties.xml, depending on your DBMS. For example, if you are using Java DB, your build.xml file would contain this:
      

      <property name="ANTPROPERTIES" value="properties/javadb-build-properties.xml"/>
      
        <import file="${ANTPROPERTIES}"/>

      Similarly, if you are using MySQL, your build.xml file would contain this:
      

      <property name="ANTPROPERTIES" value="properties/mysql-build-properties.xml"/>
      
        <import file="${ANTPROPERTIES}"/>

   2. In the properties/javadb-build-properties.xml or properties/mysql-build-properties.xml file (depending on your DBMS), modify the following properties, as described in the following table:
      

      Property	Description
      JAVAC	The full path name of your Java compiler, javac
      JAVA	The full path name of your Java runtime executable, java
      PROPERTIESFILE	The name of the properties file, either properties/javadb-sample-properties.xml or properties/mysql-sample-properties.xml
      MYSQLDRIVER	The full path name of your MySQL driver. For Connector/J, this is typically <Connector/J installation directory>/mysql-connector-java-version-number.jar.
      JAVADBDRIVER	The full path name of your Java DB driver. This is typically <Java DB installation directory>/lib/derby.jar.
      DB.VENDOR	A value of either derby or mysql depending on whether you are using Java DB or MySQL, respectively. The tutorial uses this value to construct the URL required to connect to the DBMS and identify DBMS-specific code and SQL statements.
      DB.DRIVER	The fully qualified class name of the JDBC driver. For Java DB, this is org.apache.derby.jdbc.EmbeddedDriver. For MySQL, this is com.mysql.jdbc.Driver.
      DB.HOST	The host name of the computer hosting your DBMS.
      DB.PORT	The port number of the computer hosting your DBMS.
      DB.SID	The name of the database the tutorial creates and uses.
      DB.URL	The connection URL used to connect to your DBMS. You do not need to change this value.
      DB.USER	The name of the user that has access to create databases in the DBMS.
      DB.PASSWORD	The password of the user specified in DB.USER.
      DB.DELIMITER	The character used to separate SQL statements. Do not change this value. It should be the semicolon character (;).

7. Modify the tutorial properties file.
   The tutorial samples use the values in either the properties/javadb-sample-properties.xml and properties/mysql-sample-properties.xml file (depending on your DBMS) to connect to the DBMS and initialize databases and tables, as described in the following table:
   

   Property	Description
   dbms	A value of either derby or mysql depending on whether you are using Java DB or MySQL, respectively. The tutorial uses this value to construct the URL required to connect to the DBMS and identify DBMS-specific code and SQL statements.
   jar_file	The full path name of the JAR file that contains all the class files of this tutorial. Only the sample StoredProcedureJavaDBSample.java requires this value. See Using Stored Procedures for more information.
   driver	The fully qualified class name of the JDBC driver. For Java DB, this is org.apache.derby.jdbc.EmbeddedDriver. For MySQL, this is com.mysql.jdbc.Driver.
   database_name	The name of the database the tutorial creates and uses.
   user_name	The name of the user that has access to create databases in the DBMS.
   password	The password of the user specified in user_name.
   server_name	The host name of the computer hosting your DBMS.
   port_number	The port number of the computer hosting your DBMS.

8. Compile the samples.
   At a command prompt, change the current directory to <JDBC tutorial directory>. From this directory, run the following command to compile the samples:
   ant compile
   
9. Run the samples.
   Each target in the build.xml file corresponds to a Java class or SQL script in the JDBC samples. The following table lists the targets in the build.xml file, which class or script each target executes, and other classes or files each target requires:
   

   Ant Target	Class or SQL Script	Other Required Classes or Files
   drop-tables	drop-tables.sql	No other required files
   build-tables	build-tables.sql	Runs the target drop-tables
   populate-tables	populate-tables.sql	Runs the target build-tables
   javadb-create-procedure	javadb-create-procedure.sql	No other required files
   mysql-create-procedure	See the build.xml file to view SQL statements that are executed.	No other required files
   run	JDBCTutorialUtilities	No other required classes
   runct	CoffeesTable	JDBCTutorialUtilities
   runst	SuppliersTable	JDBCTutorialUtilities
   runjs	JoinSample	JDBCTutorialUtilities, CoffeesTable, SuppliersTable
   runspjavadb	StoredProcedureJavaDBSample Note: This class requires extra configuration. See Using Stored Procedures for more information.	JDBCTutorialUtilities, SuppliersTable, CoffeesTable
   runspmysql	StoredProcedureMySQLSample	JDBCTutorialUtilities, SuppliersTable, CoffeesTable

   For example, to run the class CoffeesTable, change the current directory to <JDBC tutorial directory>, and from this directory, run the following command:
   

   ant runct