JDBC - Create Table

If you have successfully tested the GetEmp.java file, let's practice another JDBC programming example. The prepared example is a business card management program. In this chapter, we will create a table and a sequence using JDBC.

CREATE TABLE NAMECARD (
    NO  NUMBER CONSTRAINT PK_NAMECARD PRIMARY KEY,
    NAME    VARCHAR2(20) NOT NULL,
    MOBILE  VARCHAR2(20) NOT NULL,
    EMAIL   VARCHAR2(40),
    COMPANY VARCHAR2(60)
);
 
CREATE SEQUENCE SEQ_NAMECARD_NO
INCREMENT BY 1
START WITH 1;

Let's recall the JDBC programming order again.

  1. Loading the JDBC Driver
  2. Obtaining a Connection
  3. Execute SQL
  4. [If the SQL statement is a select statement, use the returned ResultSet to process the data.]
  5. Returning Resources

In the Package Explorer view in Eclipse, create NamecardDDL.java in the jdbc project to be in the package net.java_school.jdbc.test. Write all code within the main().

1. Loading a JDBC Driver

Class.forName() method loads the startup class of the Oracle JDBC driver into memory. Copy and paste this snippet from GetEmp.java.
Loading a JDBC Driver
Since the forName() method of Class class is a method that must handle ClassNotFoundException, the above compilation error occurs. With the help of Eclipse's code assist, Select the second solution to insert a try ~ catch statement. (for Eclipse's code assist, place your mouse in the code where the compilation error occurs)
Handing a ClassNotFoundException

2. Obtaining a Connection

You can get a connection using the getConnection() method of the DriverManager class.
Obtaining a Connection
Connection and DriverManager are in the java.sql package. Add the import statement by selecting the first solution in the code assist of the compile error.
Add import java.sql.*;
The first argument to the DriverManager's getConnection() method is URL. The second argument is the user, and the third argument is the user's password. Since we decided to create a table and a sequence in the Scott account, the second and third argument values are scott and tiger.
DriverManager.getConnection(String,String,String)
The DriverManager's getConnection() method can raise an SQLException. Select the second solution of the code assist and handle the exception with a try ~ catch statement.
Handing a SQLException
DriverManager's getConnection() and the Connection interface's methods are all methods that must handle SQLException. So put the getConnection() in a try block. The Connection instances used in the try block must be returned in the finally block. So declare the con variable of the Connection type outside the try block.
con variable declaration must be outside the try block

3. Getting a Statement

Decalre the stmt variable of the Statement type outside the try block. The reason for declaring the variable con outside the try block is the same. If the "Statement is of an unresolved type" compile error occurs, let the code assist function of Eclipse to add "import java.sql.Statement;".
stmt variable declaration must be outside the try block. Add import java.sql.Statement;

4. Execute SQL

The next step is to execute the SQL statement. First, make the SQL statement.

Connection con = null;
Statement stmt = null;
String sql = null;
try {
  con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:XE", "scott", "tiger");
  stmt = con.createStatement();
  sql = "CREATE TABLE NAMECARD ( " +
    "NO  NUMBER CONSTRAINT PK_NAMECARD PRIMARY KEY, " +
    "NAME    VARCHAR2(20) NOT NULL, " +
    "MOBILE  VARCHAR2(20) NOT NULL, " +
    "EMAIL   VARCHAR2(40), " +
    "COMPANY VARCHAR2(60))";
  stmt.executeUpdate(sql);
} catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
}

Execute the SQL statement by calling ExecuteUpdate() of the Statement by passing the string that creates the table as an argument. Next, the SQL statement that generates the sequence. Execute the SQL statement by calling executeUpdate() of the Statement by passing the string that generates the sequence as an argument.

Connection con = null;
Statement stmt = null;
String sql = null;

try {
  con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:XE", "scott", "tiger");
  stmt = con.createStatement();
  sql = "CREATE TABLE NAMECARD ( " +
    "NO  NUMBER CONSTRAINT PK_NAMECARD PRIMARY KEY, " +
    "NAME    VARCHAR2(20) NOT NULL, " +
    "MOBILE  VARCHAR2(20) NOT NULL, " +
    "EMAIL   VARCHAR2(40), " +
    "COMPANY VARCHAR2(60))";
  stmt.executeUpdate(sql);
  sql = "CREATE SEQUENCE SEQ_NAMECARD_NO " +
    "INCREMENT BY 1 " +
    "START WITH 1";
  stmt.executeUpdate(sql);
} catch (SQLException e) {
  // TODO Auto-generated catch block
  e.printStackTrace();
}

5. Returning Resources

Write the finally block and insert the code that returns resources in the block. Since you must return resources in the reverse order of creation, let the Statement instance resource disappers first.
SQLException compile error
Since the close() method of the Statement must handle an SQLException, the compile error occurs. Use Eclipse's code assists to insert a try - catch block. The close() method of the Connection interface also must handle an SQLException. Use Eclipse's code assists to insert a try ~ catch block.
Returning Resources Completed.
Returning resources is most important in the JDBC code. Do not forget to write the code to close resources. Run NamecardDDL.java. If the exception does not occur, access the Scott account with SQL * PLUS to see if the table and sequence exists. If an exception occurs, add code displaying the SQL statement into the catch block. One of the disadvantages of JDBC is that you need to replace the SQL statements with Java strings. Many people make a ot of mistakes when replacing SQL statements with Java strings.

C:\ Command Prompt
C:\Users\java>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on 08-JAN-2011 21:11:20

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> select tname from tab;

TNAME
------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
NAMECARD

5 rows selected.

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME
------------------------------------------------------------
SEQ_NAMECARD_NO

SQL>

If you run NamecardDDL another, you will encounter an exception. Because the table and sequence with the same name already exist in the Scott account.

executeUpdate()
Statement's executeUpdate() method is used to execute DDL statements such as create table .. or DML statements (INSERT, UPDATE, DELETE).