JDBC - Create Table
If you have successfully tested the GetEmp.java file, let's practice another JDBC programming example. The prepared example is the business card management program. This chapter 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.
- Loading the JDBC Driver
- Obtaining a Connection
- Execute SQL
- [If the SQL statement is a select statement, use the returned ResultSet to process the data.]
- 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.
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)
2. Obtaining a Connection
You can get a connection using the getConnection() method of the DriverManager class.
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.
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.
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.
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.
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;".
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.
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 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 exist.
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 many mistakes when replacing SQL statements with Java strings.
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).