JDBC - Create Table

Previous
Next

This chapter provides examples on how to create table, temporary table and duplicate table using JDBC application. Before executing the following example, make sure you have the following in place −

  • To execute the following example you can replace the username and password with your actual user name and password.

  • Your MySQL is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

  • Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

  • Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

  • Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to create a table in a seleted database.

  • Clean up the environment − try with resources automatically closes the resources.

Example: Creating a Table

In this example, we've three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared a SQL string to create a new table REGISTRATION and created the table in database by calling statement.executeUpdate() method.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

Copy and paste the following example in TestApplication.java, compile and run as follows −

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

public class TestApplication {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {		      
          String sql = "CREATE TABLE REGISTRATION " +
                   "(id INTEGER not NULL, " +
                   " first VARCHAR(255), " + 
                   " last VARCHAR(255), " + 
                   " age INTEGER, " + 
                   " PRIMARY KEY ( id ))"; 

         stmt.executeUpdate(sql);
         System.out.println("Created table in given database...");   	  
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Now let us compile the above example as follows −

C:\>javac TestApplication.java
C:\>

When you run TestApplication, it produces the following result −

C:\>java TestApplication
Created table in given database...
C:\>

Example: Creating a Temporary Table

We can create a temporary table, which exists only during an active session. Temporary tables are supported in MySQL, SQL Server, Oracle etc.

In this example, we've three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared a SQL string to create a new Temporary table EMPLOYEES_COPY and created the table in database by calling statement.execute() method.

In next line of code, we've created a query string to get all records from the newly created temporary table EMPLOYEES_COPY. Query is fired using statement.executeQuery() method and result is stored in a ResultSet. ResultSet is iterated to print all the employees.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

Copy and paste the following example in TestApplication.java, compile and run as follows −

import java.sql.*;

public class TestApplication {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void main(String args[]) {
      try{
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();

         String QUERY1 = "CREATE TEMPORARY TABLE EMPLOYEES_COPY SELECT * FROM EMPLOYEES";
         stmt.execute(QUERY1);
         String QUERY2 = "SELECT * FROM EMPLOYEES_COPY";
         ResultSet rs = stmt.executeQuery(QUERY2);

         while (rs.next()){
            System.out.print("Id: " + rs.getInt("id"));
            System.out.print(" Age: " + rs.getInt("age"));
            System.out.print(" First: " + rs.getString("first"));
            System.out.println(" Last: " + rs.getString("last"));
            System.out.println("------------------------------------------");
         }
      }catch (SQLException e){
         e.printStackTrace();
      }       
   }
}

Now let us compile the above example as follows −

C:\>javac TestApplication.java
C:\>

When you run TestApplication, it produces the following result −

C:\>java TestApplication
Id: 1 Age: 18 First: Zara Last: Ali
------------------------------------------
Id: 2 Age: 25 First: Mahnaz Last: Fatma
------------------------------------------
Id: 3 Age: 20 First: Zaid Last: Khan
------------------------------------------
Id: 4 Age: 28 First: Sumit Last: Mittal
------------------------------------------
Id: 7 Age: 20 First: Rita Last: Tez
-----------------------------------------
Id: 8 Age: 20 First: Sita Last: Singh
------------------------------------------
Id: 21 Age: 35 First: Jeevan Last: Rao
------------------------------------------
Id: 22 Age: 40 First: Aditya Last: Chaube
------------------------------------------
Id: 25 Age: 35 First: Jeevan Last: Rao
------------------------------------------
Id: 26 Age: 35 First: Aditya Last: Chaube
------------------------------------------
Id: 34 Age: 45 First: Ahmed Last: Ali
------------------------------------------
Id: 35 Age: 50 First: Raksha Last: Agarwal
------------------------------------------
C:\>

Example: Creating a Duplicate Table

We can create a TABLE which is exactly similar to an existing table. The syntax is: 

CREATE new_table_name LIKE orig_table_name;

After executing the command, all data from original table is copied to new table.

In this example, we've three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've prepared a Statement object using createStatement() method. As next step, We've prepared a SQL string to create a new duplicate table EMPLOYEES_O and created the table in database by calling statement.execute() method.

In next line of code, we've created a query string to get all records from the newly created duplicate table EMPLOYEES_O. Query is fired using statement.executeQuery() method and result is stored in a ResultSet. ResultSet is iterated to print all the employees.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

Copy and paste the following example in TestApplication.java, compile and run as follows −

import java.sql.*;
// This class demonstrates the way of creating a table which is exactly similar to another table.        
public class TestApplication {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void main(String args[]) {

      try{
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         String QUERY1 = "CREATE TABLE EMPLOYEES_O LIKE EMPLOYEES";            
         stmt.execute(QUERY1);

         ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEES");            

         System.out.println("Displaying records from EMPLOYEES_O");

         while (rs.next()){

            System.out.print("Id: " + rs.getInt("id"));
            System.out.print(" Age: " + rs.getInt("age"));
            System.out.print(" First: " + rs.getString("first"));
            System.out.println(" Last: " + rs.getString("last"));

            System.out.println("------------------------------------------");
         }

      }catch(SQLException e){
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:\>javac TestApplication.java
C:\>

When you run TestApplication, it produces the following result −

C:\>java TestApplication
Displaying records from EMPLOYEES_O
Id: 1 Age: 18 First: Zara Last: Ali
------------------------------------------
Id: 2 Age: 25 First: Mahnaz Last: Fatma
------------------------------------------
Id: 3 Age: 20 First: Zaid Last: Khan
------------------------------------------
Id: 4 Age: 28 First: Sumit Last: Mittal
------------------------------------------
Id: 7 Age: 20 First: Rita Last: Tez
------------------------------------------
Id: 8 Age: 20 First: Sita Last: Singh
------------------------------------------
Id: 21 Age: 35 First: Jeevan Last: Rao
------------------------------------------
Id: 22 Age: 40 First: Aditya Last: Chaube
-----------------------------------------
Id: 25 Age: 35 First: Jeevan Last: Rao
------------------------------------------
Id: 26 Age: 35 First: Aditya Last: Chaube
------------------------------------------
Id: 34 Age: 45 First: Ahmed Last: Ali
------------------------------------------
Id: 35 Age: 50 First: Raksha Last: Agarwal
------------------------------------------
C:\>
Print Page
Previous
Next
Advertisements