Closing the Connection

You must close your connection to the database once you finish your work. Use the close method of the Connection object to do this: 
   conn.close( );
Typically, you should put close( ) statements in a final clause. The steps in the preceding slides are summarized in the following example, which performs the following tasks:
  1. Import packages.
  2. Open an OracleDataSource.
  3. Connect to the database cdb1.
  4. Create a Statement object.
  5. Execute an SQL statement.
  6. Processe the result set.
  7. Close the result set, statement, and connection.


A one-to-one (1-1) relationship has a person having only one active ID card, which also has only one owner. You can find the database implementation of the one-to-one relationship below:


⇓ Transforming (1-1 relationship)
person
pid IDcard_number name
1 14 Super Mario
2 31 Poke Mon
110 2 Digi Mon
24 6 Sponge Bob
... ... ...
IDcard
ID_number issue_date valid_to
36 02/17/2000 02/16/2010
1 10/21/2015 10/20/2025
2 09/28/2019 02/28/2022
38 11/01/2018 06/01/2022
... ... ...
⇓ Implementation (SQL commands) 
DROP TABLE person2 FORCE;
DROP TABLE IDcard2 FORCE;
DROP SEQUENCE IDcard2_seq;
CREATE SEQUENCE IDcard2_seq START WITH 1 INCREMENT BY 1 CACHE 100;
CREATE TABLE IDcard2 (
  ID_number   INTEGER PRIMARY KEY,
  issue_date  DATE NOT NULL UNIQUE,
  valid_to    DATE NOT NULL );

INSERT INTO IDcard2 VALUES( IDcard2_seq.NEXTVAL,
  TO_DATE( '10-21-2015', 'MM/DD/YYYY' ), TO_DATE( '10-20-2025', 'MM/DD/YYYY' ) );
INSERT INTO IDcard2 VALUES( IDcard2_seq.NEXTVAL,
  TO_DATE( '06-03-2015', 'MM/DD/YYYY' ), TO_DATE( '06-02-2025', 'MM/DD/YYYY' ) );
INSERT INTO IDcard2 VALUES( IDcard2_seq.NEXTVAL,
  TO_DATE( '12-10-2009', 'MM/DD/YYYY' ), TO_DATE( '12-09-2019', 'MM/DD/YYYY' ) );
INSERT INTO IDcard2 VALUES( IDcard2_seq.NEXTVAL,
  TO_DATE( '10-01-2018', 'MM/DD/YYYY' ), TO_DATE( '10-20-2024', 'MM/DD/YYYY' ) );
INSERT INTO IDcard2 VALUES( IDcard2_seq.NEXTVAL,
  TO_DATE( '09-28-2019', 'MM/DD/YYYY' ), TO_DATE( '02-28-2022', 'MM/DD/YYYY' ) );
INSERT INTO IDcard2 VALUES( IDcard2_seq.NEXTVAL,
  TO_DATE( '10-31-2000', 'MM/DD/YYYY' ), TO_DATE( '10-31-2010', 'MM/DD/YYYY' ) );
INSERT INTO IDcard2 VALUES( IDcard2_seq.NEXTVAL,
  TO_DATE( '02-29-2020', 'MM/DD/YYYY' ), TO_DATE( '01-31-2030', 'MM/DD/YYYY' ) );
INSERT INTO IDcard2 VALUES( IDcard2_seq.NEXTVAL,
  TO_DATE( '08-12-2010', 'MM/DD/YYYY' ), TO_DATE( '07-19-2015', 'MM/DD/YYYY' ) );

DROP SEQUENCE person2_seq;
CREATE SEQUENCE person2_seq START WITH 1 INCREMENT BY 1 CACHE 100;
CREATE TABLE person2 (
  pid            INTEGER PRIMARY KEY,
  IDcard_number  INTEGER NOT NULL,
  name           VARCHAR(32) NOT NULL,
  FOREIGN KEY ( IDcard_number ) REFERENCES IDcard2( ID_number ) ON DELETE CASCADE );

INSERT INTO person2
  SELECT person2_seq.NEXTVAL, ID_number, 'Poke Mon' FROM IDcard2
    WHERE issue_date = TO_DATE( '10-21-2015', 'MM/DD/YYYY' );
INSERT INTO person2
  SELECT person2_seq.NEXTVAL, ID_number, 'Digi Mon' FROM IDcard2
    WHERE issue_date < TO_DATE( '02-29-2004', 'MM/DD/YYYY' );
INSERT INTO person2
  SELECT person2_seq.NEXTVAL, ID_number, 'Sponge Bob' FROM IDcard2
    WHERE issue_date = TO_DATE( '06-03-2015', 'MM/DD/YYYY' );
INSERT INTO person2
  SELECT person2_seq.NEXTVAL, ID_number, 'Luigi' FROM IDcard2
    WHERE issue_date = TO_DATE( '12-10-2009', 'MM/DD/YYYY' );
INSERT INTO person2
  SELECT person2_seq.NEXTVAL, ID_number, 'Super Mario' FROM IDcard2
    WHERE issue_date = TO_DATE( '10-01-2018', 'MM/DD/YYYY' );
INSERT INTO person2
  SELECT person2_seq.NEXTVAL, ID_number, 'Pikachu' FROM IDcard2
    WHERE issue_date = TO_DATE( '09-28-2019', 'MM/DD/YYYY' );
INSERT INTO person2
  SELECT person2_seq.NEXTVAL, ID_number, 'Spider Man' FROM IDcard2
    WHERE issue_date = TO_DATE( '02-29-2020', 'MM/DD/YYYY' );
INSERT INTO person2
  SELECT person2_seq.NEXTVAL, ID_number, 'Super Man' FROM IDcard2
    WHERE issue_date = TO_DATE( '08-12-2010', 'MM/DD/YYYY' );

Name =

              
/*******************************************************************
*                                                                  *
*  This program shows how to list a person's ID card information.  *
*                                                                  *
*******************************************************************/

// Import the following packages and class to use JDBC.
import  java.sql.*;
import  java.io.*;
import  oracle.jdbc.*;
import  oracle.jdbc.pool.OracleDataSource;

class  One2One {
  public static void  main( String args[ ] ) throws SQLException {
    String user     = "C##wenchen";
    String password = "oracle1";
    String database = "65.52.222.73:1521/cdb1";

    // Open an OracleDataSource and get a connection.
    OracleDataSource ods = new OracleDataSource( );
    ods.setURL     ( "jdbc:oracle:thin:@" + database );
    ods.setUser    ( user );
    ods.setPassword( password );
    Connection conn = ods.getConnection( );

    try {
      Statement stmt = conn.createStatement( );
      String query  = "SELECT ID_number, name, issue_date, valid_to ";
      query += "FROM person2 p, IDcard2 i ";
      query += "WHERE UPPER(p.name) LIKE '%" + args[0].trim( ) + "%' AND ";
      query += "p.IDcard_number=i.ID_number";

      ResultSet rset = stmt.executeQuery( query );
      // Iterate through the result and print the data.
      if ( !rset.next( ) )
        System.out.println( "No persons are found!" );
      else
        do {
          System.out.print  ( rset.getInt(1) + ": " );
          System.out.println( rset.getString(2) + ", " );
          System.out.println( rset.getDate(3) + " to " );
          System.out.println( rset.getDate(4) );
        } while ( rset.next( ) );

      // Close the ResultSet and Statement.
      rset.close( );
      stmt.close( );
    }
    catch ( SQLException ex ) {
      System.out.println( ex );
    }
    // Close the Connection.
    conn.close( );
  }
}




      A stitch in time saves nine.