Slide 7.1: A sample JDBC program

A Sample JDBC Program

The following sample program is modified from a program in the page Sample Applications. Many JDBC program examples can be found from there. This program includes the following SQL commands:

01SQL> DROP TABLE student_table;
02Table dropped.
03     
04SQL> CREATE TABLE student_table (
05  2    ID   INTEGER GENERATED ALWAYS AS IDENTITY,
06  3    name VARCHAR(32),
07  4    age  INTEGER );
08  5    /
09Table created.
10 
11SQL> INSERT INTO student_table ( name, age )
12  2    VALUES ( 'Digi Mon', 10 );
131 row created.
14 
15SQL> SELECT * FROM student_table s
16  2    WHERE name LIKE '%Mon%';
17     
18    ID        NAME         AGE
19  ------ -------------- ---------
20     3   Poke Mon          25
21     1   Digi Mon          10

Drop Create Insert Select (args[0])

   Name (args[1]) = [for insert and select (listing all if empty)]

   Age (args[2]) = (for insert)

01// Import the following packages to use JDBC.
02import  java.sql.*;
03import  java.sql.DatabaseMetaData;
04import  java.io.*;
05import  oracle.sql.*;
06import  oracle.jdbc.*;
07import  oracle.jdbc.pool.OracleDataSource;
08 
09class  Student {
10  public static void  main( String args[ ] ) throws SQLException {
11    String user     = "C##user_id";
12    String password = "password";
13    String database = "20.185.147.112:1521/xe";
14 
15    // Open an OracleDataSource and get a connection.
16    OracleDataSource ods = new OracleDataSource( );
17    ods.setURL     ( "jdbc:oracle:thin:@" + database );
18    ods.setUser    ( user );
19    ods.setPassword( password );
20    Connection conn = ods.getConnection( );
21     
22    String     cmd;
23    Statement  stmt = conn.createStatement( );
24    ResultSet  rset;
25    DatabaseMetaData  dbm = conn.getMetaData( );
26 
27    if ( args[0].equals( "drop" ) ) {
28      cmd  = "drop table student_table";
29      System.out.println( cmd );
30      stmt.execute( cmd );
31    }
32 
33    else if ( args[0].equals( "create" ) ) {
34      // Check if "student_table" table is there.
35      rset = dbm.getTables(
36               null, "C##user_id", "STUDENT_TABLE", new String[] {"TABLE"} );
37      if ( rset.next( ) ) {
38        // Table does exist.
39        cmd  = "drop table student_table";
40        System.out.println( cmd );
41        stmt.execute( cmd );
42      }
43  
44      cmd  = "create table student_table (";
45      cmd += "  ID   integer generated always as identity,";
46      cmd += "  name VARCHAR(32),";
47      cmd += "  age  INTEGER )";
48      System.out.println( cmd );
49      stmt.execute( cmd );
50 
51      cmd  = "insert into student_table( name, age ) values (";
52      cmd += "  'Digi Mon', 10 )";
53      System.out.println( cmd );
54      stmt.execute( cmd );
55      rset.close( );
56    }
57 
58    else if ( args[0].equals( "insert" ) ) {
59      cmd  = "insert into student_table( name, age ) values ( '";
60      cmd += args[1].trim( ) + "', '" + args[2].trim( ) + "' )";
61      System.out.println( cmd );
62      stmt.execute( cmd );
63    }
64 
65    else if ( args[0].equals( "select" ) ) {
66      cmd  = "select * from student_table s where ";
67      cmd += "name like '%" + args[1].trim( ) + "%'";
68      System.out.println( cmd );
69      rset = stmt.executeQuery( cmd );
70 
71      while ( rset.next( ) ) {
72        // Print the results.
73        System.out.println( "\nStudent ID => " + rset.getInt(1) );
74        System.out.println( "Student name => " + rset.getString(2) );
75        System.out.println( "Student age => "  + rset.getInt(3) );
76      }
77      rset.close( );
78    }
79    stmt.close( );
80    conn.close( );
81  }
82}