Business Programming Concepts II

MIS 3023

Business Programming Concepts II

Professor: Akhilesh Bajaj

Welcome to our JDBC Lab !

In this lab, we shall learn how to do the following:

Set up a User Data Source Name as an ODBC object, using either an MS access

Database or a MYSQL database.

Write a java program that creates 3 tables in the database with primary and foreign keys.
Write a java program that drops these tables.
Write a java program to insert data in the tables we create.

Note:It’s important that we all type in the code on our own. As some people say, learning programming is all in the fingers! So, typing in the code will allow us to think about it, as we type it in, and prepare us much better for understanding the JDBC lecture.

Let’s get started!

Creating the MyGarage Database in MySQL

Go to SQL YOG on our desktop and login using localhost, root, letmein and 3306 as server, username, password and port respectively. Leave Database blank. Test the connection first to make sure it is successful.

Go to Query window in YOG and type in

CREATE DATABASE MyGarage;

Then right click in that box and select EXECUTE Query or just hit F9.

Hit F5 to refresh the Object browser so MyGarage can be seen.

Next, click the WINDOWS icon on the bottom left of our screen and type in odbc in the text line there.

Select the ODBC 32 bit option if available.

A window will open to allow us to create a DSN.

We should select the user DSN tab. Next, making sure none of the data sources are selected, we should click ADD. The MYSQL/Connector/ODBC window pops up.

We should put in MyGarage in the Data Source name and Description. We should use localhost, root, letmein and 3306 as server, username, password and port respectively. Select our newly created Database: MyGarage.

Next, we will see how to use Access as an underlying database. We need to use either MySQL OR Access. If we have already use MYSQL, we can skip directly to writing Java code.

Creating the MyGarage Database in MS Access:

1. Please start MS Access and create a new database called MyGarage. We should not create any tables in it, just create the database, and then close MS Access.

2. Next, we go to start  programs  administrative tools  data sources (ODBC). If you are using a lab machine, got to All programs and then type in ODBC in the text box there. Click on MS Access, and then Configure. Fill in the data as below.

Click OK and again OK to finish. Great! We have finished creating a database that will be accessible using the built-in JDBC-ODBC driver, in J2SE!!

Having create an ODBC DSN, let’s see how to write Java programs to connect to it.

Writing JDBC programs in Java:

All the files we create today should be saved in a directory with a convenient name like JDBClab1. When we are done, we should copy the folder contents to a portable medium, like a floppy disk.

Creating File CreateGarage.java:

Using the Notepad editor on our PC (see under start  program  accessories  notepad), or some other editor (like EJE), please type in the following file. Remember to keep saving the file frequently in your folder. Call the file CreateGarage.java. The file extension should be .java and NOT .txt. The first time we save the file, use the Save as command on the file menu. After that, save periodically using the Save command in the file menu of the Notepad editor (or the relevant command in another editor).

import java.sql.*;

import java.io.*;

public class CreateGarage {

public static void main(String args[]) {

String url = "jdbc:odbc:MyGarage";

Connection con;

String createCars, createMechs, createRepairs;

String addConstraints1, addConstraints2, addConstraints3;

createCars = "CREATE TABLE cars (car_num INTEGER PRIMARY KEY, car_model VARCHAR(32) NOT NULL, " +

"car_year INTEGER NOT NULL); ";

createMechs = "CREATE TABLE mechanics " +

"(mech_num INTEGER PRIMARY KEY, "+

"mech_name VARCHAR(50), "+

"mech_salary FLOAT, " +

"mech_specialty VARCHAR(20) ); ";

createRepairs = "CREATE TABLE repairs" +

"(mech_num INTEGER NOT NULL, "+

" car_num INTEGER NOT NULL, "+

"repair_date DATE NOT NULL); ";

addConstraints1 = "ALTER TABLE repairs ADD "+

"CONSTRAINT rep_PK1 "+

"PRIMARY KEY (mech_num, car_num, repair_date); ";

addConstraints2= "ALTER TABLE repairs ADD CONSTRAINT rep_FK1 "+

"FOREIGN KEY (car_num)REFERENCES CARS (car_num); ";

addConstraints3= "ALTER TABLE repairs ADD CONSTRAINT rep_FK2 "+

"FOREIGN KEY (mech_num) REFERENCES MECHANICS (mech_num); " ;

Statement stmt;

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

} catch(java.lang.ClassNotFoundException e) {

System.err.print("ClassNotFoundException: ");

System.err.println(e.getMessage());

}

try {

con = DriverManager.getConnection(url,

"", "");

stmt = con.createStatement();

stmt.executeUpdate(createCars);

System.out.println(" CREATED TABLE CARS WITH PRIMARY KEY\n");

stmt.executeUpdate(createMechs);

System.out.println(" CREATED TABLE MECHS WITH PRIMARY KEY\n");

stmt.executeUpdate(createRepairs);

System.out.println(" CREATED TABLE REPAIRS \n");

stmt.executeUpdate(addConstraints1);

System.out.println(" ADDED PRIMARY KEY TO REPAIRS TABLE \n");

stmt.executeUpdate(addConstraints2);

stmt.executeUpdate(addConstraints3);

System.out.println(" ADDED FOREIGN KEYS TO REPAIRS TABLE \n");

stmt.close();

con.close();

} catch(SQLException ex) {

System.err.println("SQLException: " + ex.getMessage());

ex.printStackTrace();

}

In this file, we are creating 3 tables in the database MyGarage, along with primary keys and foreign keys. We should check that there are no errors in our code, by comparing it with the code given here.

Next, we should create the class files by typing in

javac CreateGarage.java

If there are no typing errors, we can run the fileby typing in

java CreateGarage

If we have the MySQL database created, we can go into YOG to see the tables.

If we have the MS Access database, we can go intothe Access database (using startprograms MS OfficeMS Access), and can see that the tables have been created. If we right-click on a table and select Design View, we can see that the primary keys have been created. Also, if we select toolsrelationships from the main menu, we can see that the foreign key links have been created.

Now let’s write a java program to drop these tables.

Dropping the Tables:

We’ll create a file called DropGarage.java, in the same folder: JDBClab1.

Using an editor, please create the following file, calling it DropGarage.java.

import java.sql.*;

import java.io.*;

public class DropGarage {

public static void main(String args[]) {

String url = "jdbc:odbc:MyGarage";

Connection con;

String dropTables1, dropTables2, dropTables3;

dropTables1 = "DROP TABLE repairs;";

dropTables2 = "DROP TABLE cars;";

dropTables3 = "DROP TABLE mechanics;";

Statement stmt;

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

} catch(java.lang.ClassNotFoundException e) {

System.err.print("ClassNotFoundException: ");

System.err.println(e.getMessage());

}

try {

con = DriverManager.getConnection(url,

"", "");

stmt = con.createStatement();

stmt.executeUpdate(dropTables1);

System.out.println(" TABLE REPAIRS DROPPED\n");

stmt.executeUpdate(dropTables2);

System.out.println(" TABLE CARS DROPPED\n");

stmt.executeUpdate(dropTables3);

System.out.println(" TABLE MECHANICS DROPPED\n");

stmt.close();

con.close();

} catch(SQLException ex) {

System.err.println("SQLException: " + ex.getMessage());

}/*try*/

}/*main*/

}/*class*/

We should check that there are no errors in our code, by comparing it with the code given here.

Next, we should create the class files by typing in

javac DropGarage.java

If there are no typing errors, we can run the fileby typing in

java DropGarage

If we go intothe Access database (using startprograms MS OfficeMS Access), we can see that the tables have been dropped.

Now recreate the tables by running

java CreateGarage

Great! Now, let’s insert some data into the tables via a java program.

Inserting Data Into The Tables:

We’ll create a file called InsertGarage.java, in the same folder: JDBClab1.

Using an editor, please create the following file, calling it InsertGarage.java.

import java.sql.*;

import java.io.*;

public class InsertGarage {

public static void main(String args[]) {

String url = "jdbc:odbc:MyGarage";

Connection con;

String insertCars, insertMechs, insertRepairs;

insertCars = "INSERT INTO cars VALUES (?,?, ?); ";

insertMechs = "INSERT INTO mechanics VALUES (?, ?, ?, ?); ";

insertRepairs = "INSERT INTO repairs VALUES (?, ?, ?); ";

PreparedStatement pstmt;

try {

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

} catch(java.lang.ClassNotFoundException e) {

System.err.print("ClassNotFoundException: ");

System.err.println(e.getMessage());

}

try {

con = DriverManager.getConnection(url,

"", "");

pstmt = con.prepareStatement(insertCars);

pstmt.setInt(1,1);

pstmt.setString(2,"Ferrari Modena");

pstmt.setInt(3,1997);

pstmt.executeUpdate();

System.out.println("ONE ROW IN CARS INSERTED \n");

pstmt = con.prepareStatement(insertMechs);

pstmt.setInt(1,1);

pstmt.setString(2,"John Smith");

pstmt.setDouble(3,50000.00);

pstmt.setString(4,"Ferrari Mechanic");

pstmt.executeUpdate();

System.out.println("ONE ROW IN MECHANICS INSERTED \n");

pstmt = con.prepareStatement(insertRepairs);

pstmt.setInt(1,1);

pstmt.setInt(2,1);

Date rep_date = new Date(101, 0, 05);//date to Jan 5 2001

pstmt.setDate(3,rep_date);

pstmt.executeUpdate();

System.out.println("ONE ROW IN REPAIRS INSERTED \n");

pstmt.close();

con.close();

} catch(SQLException ex) {

System.err.println("SQLException: " + ex.getMessage());

}

This program uses a PreparedStatement object, that allows us to formulate parametrized SQL statements.

We should check that there are no errors in our code, by comparing it with the code given here.

Next, we should create the class files by typing in

javac InsertGarage.java

If there are no typing errors, we can run the fileby typing in

java InsertGarage

For MYSQL, if we go into YOG and right click on Cars table and select Insert/Update data, we will see the row inserted in the cars table.

For Access, if we go intothe Access database (using startprograms MS OfficeMS Access), we can see that the tables now have one row each!

Congratulations! You have finished the JDBC lab!!

For a list of capabilities you acquired in this lab, please refer back to page 1.

Some Hints on Further Learning:

1. In this lab, we wrote all programs as a main() program. The idea was to learn how to set up a connection to a database, and to run SQL queries/commands.

We can use our knowledge of Java and O-O programming principles to plan and write classes, instead of just one main program. So, for example, we can write a class that is related to a single table, and offers methods that allow basic operations on that table. Another option is a class that is supplied a database, a driver and a SQL statement, and that executes the SQL statement on the database, and returns a result if any. To do either of these, we will use the code we learnt in this lab, except we would package it in another class.

______