JDBC Program to Create Table

Create a table, drop table, and alter table are the DDL queries or non-select queries. So, we can use executeUpdate() method of Statement / PreparedStatement object to send and execute these queries in database software. In this post, we will develop the JDBC program to create a table in Oracle and MySQL database.

In real-time programmers will not do DDL operations through JDBC applications. Every company contains database teams who design database tables based on normalization rules.

Note- When we are using DDL queries with executeUpdate() method, then executeUpdate() method returns 0 on success. For other queries, executeUpdate() method returns 0 for failure.

JDBC program to create table in Oracle database

Basic details of Oracle thin driver,

JDBC Driver Class Name:: oracle.jdbc.driver.OracleDriver

URL:: 
jdbc:oracle:thin:@<ip-address/host-name>:<port-no-of-oracle-db>:<sid>

Username: <username_of_database>
Password: <password_of_database>

• On a local machine generally, Oracle database is located at the port no => 1521
• If you are developing JDBC application for the first time then use, host-name => localhost

How to find the sid /global name?
Open SQL Plus tool, Login to the user account and type below query,

SQL> select sys_context ('userenv','instance_name') from dual;

SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------
knowprogram

Hence the URL will be =>
jdbc:oracle:thin:@localhost:1521:knowprogram

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class CreateTable {

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      String query = null;
      Connection con = null;
      Statement st = null;
      int result = 0;

      try {
         // read input
         scan = new Scanner(System.in);
         if(scan != null) {
            System.out.println("Enter sql query to "
    	       + "create table, \nDon't place"
    	       + " semicolon at the end: ");
            query = scan.nextLine();
         }

         // establish the connection
         con = DriverManager.getConnection(
           "jdbc:oracle:thin:@localhost:1521:knowprogram",
           "scott", "tiger");

         // create statement object
         if(con != null)
            st = con.createStatement();

         // execute SQL query
         if(st != null)
        	 result = st.executeUpdate(query);

         // process the result
        if(result == 0)
           System.out.println("Table created");
        else
           System.out.println("Table not created");

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // end of try-catch block 

     finally {
        // close JDBC objects
        try {
           if(st != null) st.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(scan != null) scan.close();
        } catch(Exception e) {
           e.printStackTrace();
        }
     }

   } //end of main
} //end of class

Output:-

Enter sql query to create table,
Don’t place semicolon at the end:
create table test(no number(10), name varchar2(15))
Table created

Now, verify that table is created in the Oracle database or not.

SQL> desc test;

If we try to create an already existing table then it will be return java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object.

---

JDBC program to create table in MySQL database

JDBC Driver Class Name:: com.mysql.cj.jdbc.Driver

URL::
=> for local MySQL:: 
jdbc:mysql:///<logical-database-name>

=> for remote MySQL:: 
jdbc:mysql://<host>:<port-no>/<logical-database-name>

Username: <MySQL database username>
Password: <MySQL database password>

If you developing JDBC application for first time then use url for local MySQL

How to find the database name in MySQL?
Type below query to get all databases available in the MySQL server.

show databases;

If you are new to MySQL then create a new database,

CREATE SCHEMA `knowprogram` ;

Here knowprogram will be the name of the database. Hence the URL for local MySQL will be jdbc:mysql:///knowprogram

In MySQL database along with tablename, we need to pass logical database name also.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class CreateTable {

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      String query = null;
      Connection con = null;
      Statement st = null;
      int result = 0;

      try {
    	 // read input
    	 scan = new Scanner(System.in);
    	 if(scan != null) {
    	    System.out.println("Enter sql query "
                 "to create table, \nDon't place"
                  +" semicolon at the end: ");
    	    query = scan.nextLine();
    	 }

         // establish the connection
         con = DriverManager.getConnection(
             "jdbc:mysql:///knowprogram", "root", "[email protected]");

         // create statement object
         if(con != null)
         st = con.createStatement();

         // execute SQL query
         if(st != null)
         result = st.executeUpdate(query);

         // process the result
         if(result == 0)
         System.out.println("Table created");
         else
         System.out.println("Table not created");

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // end of try-catch block
 
     finally {
        // close JDBC objects
        try {
           if(st != null) st.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(scan != null) scan.close();
        } catch(Exception e) {
           e.printStackTrace();
        }
     }

   } //end of main
} //end of class

Output:-

Enter sql query to create table,
Don’t place semicolon at the end:
create table knowprogram.test(no int, name varchar(45))
Table created

---

Sample code to drop table

Statement st = con.createStatement();
int result = 
   st.executeUpdate("DROP TABLE TEST");

if(result == 0)
System.out.println("table dropped");
else
System.out.println("table not found");

In both of the cases (creating a table, and dropping table) we can observe that else block will be never executed. For success executeUpdate() method returns 0 or we get an exception on failure. So, writing else block is meaningless. We are writing else block only for understanding purpose, there is no use of else block in these programs.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also read:-

• Different Types of Statements in JDBC
• JDBC Statement Example
• JDBC Statement Interface
• DriverManager class in Java

Search