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, you create tables and sequences 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. All code will be implemented in the main method.

1. Loading a JDBC Driver

Use the Class.forName() method to load 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 should handle ClassNotFoundException, the above compilation error is seen. With the help of Eclipse's code assist (for help, place it in the code where the compilation error occurs with your mouse) Select the second solution as shown to insert a try ~ catch statement.
Handing a ClassNotFoundException

2. Obtaining a Connection

The connection is obtained using the getConnection (,,) method of the DriverManager class.
Obtaining a Connection
Connection and DriverManager are in java.sql package. Add the import statement by selecting the first solution in the code assist of the compile error as shown above.
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 sequence in the scott account, the second and third argument values are scott and tiger, respectively.
DriverManager.getConnection(String,String,String)
The DriverManager's getConnection (,,) method can raise an SQLException object. Select the second solution of the code assist as shown above and handle the exception with a try ~ catch statement.
Handing a SQLException
Since the following methods must handle SQLException exceptions, put these methods in a try block. The variable declaration of the connection type con must be outside the try block. When returning a resource using the con.close() code snippet in a finally block, the finally block must also be an area where the con variable can be interpreted.
con variable declaration must be outside the try block

3. Getting a Statement

The stmt variable declaration of the statement type is also placed outside the try block considering the code implementation for returning resources later. If you encounter a compile error that the Statement is of an unresolved type, let the code assist help to insert 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 to be executed as a string.

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 is made into a string. 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

Create a finally block and insert the code that returns the resource in the finally block. Since the resources must be returned in the reverse order of creation, the stmt.close() code must first appear.
SQLException compile error
Since the close() method of Statement is a method that can cause SQLException object, the above compile error occurs. Use Eclipse 's code assist to insert a try - catch block. Because Connection's close() method is also a method that can cause an SQLException object, we also use code assists to place the con.close () code in a try ~ catch block.
Returning Resources Completed.
Returning resources is most important in JDBC code. Do not forget to return resources. Run NamecardDDL.java. If the exception does not occur, access the scott account with SQL * PLUS to see if the table and sequence have been created. If an exception occurs, add code to the catch block to output the SQL statement. One of the disadvantages of JDBC is that you need to replace the SQL statement with a Java string. There are a lot of mistakes in the process of replacing an SQL statement with a Java string.

C:\ Command Prompt
C:\Documents and Settings\kim>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>

Running NamecardDDL.java again will cause an exception. This is 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).