Database Programming in Java

1. Introduction to Relational Databases

Name	Gender	Age
Mary	F	23
Robert	M	31
Sally	F	35
Jane	F	28
Tony	M	30
Alice	F	41
Scott	M	27
Larry	M	49

Each row of the table can represent a variety of data objects, for example, items stocked in a store, airline flights, or students in a course. The columns of a table are the individual data variables of the persons. In the case of the PersonData table, the columns are Name, Gender, and Age.

Usually a relational database contains more than one table. The tables in a database contain related information that can be cross referenced.

2. Creating a Database Table with Access

1. Start Microsoft Access.

2. Select "File" and "New" in the main menu to create a new file. Select "Blank Database," name it Person.mdb and save the new database in the folder where you want to use it by clicking on "Create."

3. Double click on "Create table in design view" to select the column variables for the table. Enter "Name" for the Field Name and "Text" for the datatype. Continue by adding "Gender" and "Text" for the second column and "Age" and "Number" for the third column. Close the "Table1: Table" window when you are finished. Save the table as PersonData. Respond "No" to the question "Do you want to create a primary key now?"

4. Double click on "PersonData." Enter the data in the table shown in Section 1. Close the "PersonData: Table" window.

5. Close Microsoft Access.

3. Structured Query Language

4. SQL Select Statements

1. SELECT * FROM PersonData
   Select all rows and columns from the table PersonData.

   Name	Gender	Age
   Mary	F	23
   Robert	M	31
   Sally	F	35
   Jane	F	28
   Tony	M	30
   Alice	F	41
   Scott	M	27
   Larry	M	49

2. SELECT Name FROM PersonData
   Select all rows, but only the column Name.

   Name
   Mary
   Robert
   Sally
   Jane
   Tony
   Alice
   Scott
   Larry

3. SELECT Name, Age FROM PersonData
   Select all rows but only the columns Name and Age.

   Name	Age
   Mary	23
   Robert	31
   Sally	35
   Jane	28
   Tony	30
   Alice	41
   Scott	27
   Larry	49

4. SELECT * FROM PersonData WHERE Name = 'Alice'
   Select all columns, but only the row with the name Alice. Note that Text constants in SQL use single quotes.

   Name	Gender	Age
   Alice	F	41

5. SELECT Name FROM PersonData WHERE Gender = 'F'
   Select the names with the gender F.

   Name
   Mary
   Sally
   Jane
   Alice

6. SELECT * FROM PersonData WHERE Gender = 'F' and Age = 34
   Select all columns and only the rows with Gender F and Age 34. There are no tuples satisfying these constraints, so the resulting table has zero rows.

   Name	Gender	Age

SQL = "SELECT * FROM PersonData WHERE Name = '" + name + "'"

Note that text constants in SQL must be enclosed in single quotes, even when the text constant is obtained from a variable at run time.

5. Setting Up an ODBC Connection

1. Select Start and Run. Type odbcad32 in the Open ComboBox and click on OK. You will get an ODBC Data Source Administrator dialog.

2. Select the Add button on the right. You will get a Create Data Source dialog.

3. Make sure that Microsoft Access Driver (.mdb) is selected and click on Finish. You will get an ODBC Microsoft Access Setup dialog.

4. Type in the DataSource name (Person) and a Description (optional). Then click on Select. You will get a Select Database dialog.

5. Select the drive and the directory where the database is located. You should then see the .mdb file on the left. Select it. You should see the name of the database appear in the DatabaseName text field. Then click on OK in the Select Database dialog.

6. Click on OK in the ODBC Microsoft Access Setup dialog.

7. Click on OK in the ODBC Data Source Administrator dialog.

8. The ODBC DataSource Person is now ready to use.

6. Creating a Java ResultSet

1. Include the line
   import java.sql.*;
   at the beginning of any file that uses any of the classes Connection, Statement, or ResultSet.

2. Use the statement
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   to load the JDBC ODBC Driver. JDBC means Java Database Connectivity.

3. Create the connection object c using the name of the ODBC data source Person.
   Connection c = DriverManager.getConnection("jdbc:odbc:Person");

4. Create a Statement object, which manipulates the database using SQL expressions.
   Statement s = c.createStatement();

5. Create a Result object that uses the Statement object.
   query = "SELECT * FROM Roster WHERE Gender = 'M'"
   ResultSet r = s.executeQuery(query);
   The query can be any select statement that processes the dataset specified in the Connection object c. Here is the result set that is obtained from the preceding query.

   Name	Gender	Age
   Beginning of File Buffer Row
   Robert	M	31
   Tony	M	30
   Scott	M	27
   Larry	M	49
   End of File Buffer Row

   BOF is the abbreviation for the Beginning of File Buffer; EOF is the abbreviation for the End of File Buffer.

6. Navigate through the result set by moving the cursor forward though the recordset. (The cursor is a reference to the current row.) The following method call moves the cursor forward one row:
   r.next()
   If one wishes to process all the rows of the recordset, a while loop is used. The cursor of the result set is initially set at BOF. r.next() returns true when the new row is valid (not on the EOF or BOF buffer rows). Therefore, the following while loop traverses the entire result set:

   while (r.next())
   {
       // body of while loop
   }
   
   The first r.next() call moves the cursor from BOF to the first valid row. The while loop continues until the cursor reaches EOF where r.next() returns false so the while loop terminates.

   
   Here are ResultSet methods for obtaining field values from a row. They return the value of the field in the requested format.
   int getDouble(int columnNumber)
   int getInt(int columnNumber)
   int getString(int columnNumber)
   Other datatypes like short, long, and float are also available.

7. Close the connection with this line:
   c.close();
   

// Sample Java Application that reads from
// the PersonData table of the Person database.

import java.sql.*;

public class Main
{
    public static void main(String[] a)
       throws ClassNotFoundException, SQLException
    {
       // Load database driver.
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

       // Create Connection object.
       Connection c = DriverManager.getConnection(
          "jdbc:odbc:Person");

       // Create Statement object.
       Statement s = c.createStatement();

       // Create ResultSet object.
       String query = "SELECT * FROM PersonData";
       ResultSet r = s.executeQuery(query);

       // Traverse the result set to print data.
       String name;
       char gender;
       int age;
       while (r.next())
       {
          name = r.getString(1);
          gender = r.getString(2)getChar(0);
          age = r.getInt(3);
          System.out.println(
             name + " " + gender + " " + age);
       }

       // Close the Connection object c.
       c.close();
    }
}

Here is the output:

Mary F 23
Robert M 31
Sally F 35
Jane F 28
Tony M 30
Alice F 41
Scott M 27
Larry M 49

7. SQL Update Statements

To create a new table PersonData with fields like the PersonData table use this
SQL statement:
CREATE TABLE PersonData2 (Name Text, String Text, Age Number)

To insert a row in the table, use this line:
INSERT INTO PersonData2 VALUES ("Nancy", "F", 19);

Here is a Java application that reads data from the text file d:\Person.txt. and loads it into the Person2 table.

// Sample Java Application to create a new
// table in an Access database.

import java.sql.*;

public class Main
{
    public static void main(String[] a)
       throws ClassNotFoundException, SQLException
    {
       // Load database driver.
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

       // Create Connection object.
       Connection c = DriverManager.getConnection(
          "jdbc:odbc:Person");

       // Create Statement object.
       Statement s = c.createStatement();

       // Create PersonData2 table.
       s.execute(
          "CREATE TABLE PersonData2 " +
          "(Name Text, Gender Text, Age Number)");

       // Insert a row into the table.
       s.executeUpdate(
          "INSERT INTO PersonData2 VALUES " +
          "('Nancy', 'F', 19)");

       // Close the Connection object c.
       c.close();

    }
}

To add 1 to the ages of all the females in the table, use this line:
UPDATE Person SET Age = Age + 1 WHERE Gender = 'F';

The following line deletes all lines from the database satisfying the WHERE condition:
DELETE Person WHERE Age > 55;

Finally this line removes the table from the database:
DROP TABLE Person;

8. Populating a Table with Data from a Text File

// Sample Java Application to create a new
// table in an Access database and populate
// it with data from text file.

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

public class Main
{
    public static void main(String[] args)
             throws SQLException
    {
       // Load database driver.
       try
       {
          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       }
       catch(ClassNotFoundException e)
       {
          System.out.println(e);
       }

       // Create Connection object.
       Connection c = DriverManager.getConnection(
          "jdbc:odbc:Person");

       // Create Statement object.
       Statement s = c.createStatement();

       // Create PersonData2 table if
       // table does not exist already.
       try
       {
          s.execute(
             "CREATE TABLE PersonData2 " +
             "(Name Text, Gender Text, Age Number)");
       }
       catch(SQLException e)
       {
          System.out.println(
             "PersonData2 table already exists.");
       }

       // Create BufferedReader object.
       BufferedReader br = null;
       try
       {
          br = new BufferedReader(
             new FileReader("d:\\Person.txt"));
       }
       catch(IOException e)
       {
          System.out.println(e);
       }

       // Insert rows into the table.
       String line, name, gender, sql;
       int age;
       StringTokenizer st;
       try
       {
          while ((line = br.readLine()) != null)
          {
             st = new StringTokenizer(line, " ");
             name = st.nextToken();
             gender = st.nextToken();
             age = Integer.parseInt(st.nextToken());
             sql = "INSERT INTO PersonData2 " +
                "VALUES ('" + name + "', '" +
                gender + "', " + age + ")";
             // The next line is for debugging.
             System.out.println(sql);
             s.executeUpdate(sql);
          }
       }
       catch(IOException e)
       {
          System.out.println(e);
       }

       // Close the Connection object c.
       c.close();
    }
}

Here is the output to the terminal window.

INSERT INTO PersonData2 VALUES ('Mary', 'F', 23)
INSERT INTO PersonData2 VALUES ('Robert', 'M', 31)
INSERT INTO PersonData2 VALUES ('Sally', 'F', 35)
INSERT INTO PersonData2 VALUES ('Jane', 'F', 28)
INSERT INTO PersonData2 VALUES ('Tony', 'M', 30)
INSERT INTO PersonData2 VALUES ('Alice', 'F', 41)
INSERT INTO PersonData2 VALUES ('Scott', 'M', 27)
INSERT INTO PersonData2 VALUES ('Larry', 'M', 49)

The new table PersonData2 has also been created if everything is working properly.