JDBC – Drop Tables


JDBC – Drop Table


”;


Previous

Next

This chapter provides an examples on how to drop a table, drop table if exists and truncate 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 or whatever database you are using, is up and running.

NOTE Reformatting JDBC Tutorial This is a serious operation and you have to make a firm decision before proceeding to delete a table, because everything you have in your table would be lost.

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 queryReformatting JDBC Tutorial Requires using an object of type Statement for building and submitting an SQL statement to drop a table in a seleted database.

  • Clean up the environment Reformatting JDBC Tutorial try with resources automatically closes the resources.

Example: Dropping a Table

We”re required to have DROP privileges in order to run DROP TABLE command.

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 drop a table REGISTRATION 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 JDBCExample.java, compile and run as follows −


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

public class JDBCExample {
   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 = "DROP TABLE REGISTRATION";
         stmt.executeUpdate(sql);
         System.out.println("Table deleted in given database...");   	  
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Now let us compile the above example as follows −


C:>javac JDBCExample.java
C:>

When you run JDBCExample, it produces the following result −


C:>java JDBCExample
Table deleted in given database...
C:>

Example: Dropping a Table if Exists

A table can be dropped using following command. If it does not exist, this command will throw error.


DROP TABLE table_name

If we use a check before dropping a table, it will not throw an error and drops table only if table exists.


DROP TABLE IF EXISTS table_name

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 drop a table sampledb1 by calling statement.execute() method.

Once table is dropped, we”ve printed the status and execute another query to show all the tables in given database and all remaining tables are printed.

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 JDBCExample.java, compile and run as follows −


import java.sql.*;

// This file demonstrates use of DROP TABLE IF EXISTS command
public class JDBCExample {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   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 QUERY = "DROP TABLE IF EXISTS sampledb1";
         stmt.execute(QUERY);

         System.out.println("Table sampledb1 dropped successfully.");
         System.out.println("----------------------------------------");
         ResultSet rs = stmt.executeQuery("show tables");
         System.out.println("List of tables");
         System.out.println("----------------------------------------");
         while(rs.next()){
            System.out.println(rs.getString(1));
         }
      }catch (SQLException e){
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −


C:>javac JDBCExample.java
C:>

When you run JDBCExample, it produces the following result −


C:>java JDBCExample
Table sampledb1 dropped successfully.
----------------------------------------
List of tables
----------------------------------------
consumers
employees
employees_o
jdbc_blob_clob
officers
students

C:>

Example: Truncate Table

DROP TABLE deletes not only all table data, but also table definitions. On the other hand, TRUNCATE TABLE deletes only table data (or rows).


TRUNCATE TABLE table_name

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 query a table sampledb4 by calling statement.executeQuery() method to print all the records. Using result in a ResultSet, we”ve printed all the records.

With a new query to truncate the table sampledb4, we”ve truncated the table using execute() method. Now again, we”re fired the select query to get all the records. As resultant resultset is empty, a corresponding message is printed.

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 JDBCExample.java, compile and run as follows −


import java.sql.*;

public class JDBCExample {

   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   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 sel_query = "select * from sampledb4"; 
         ResultSet rs1 = stmt.executeQuery(sel_query);
         System.out.println("Rows from sampledb4");
         System.out.println("--------------------");
         while(rs1.next()){
            System.out.println("id: " + rs1.getInt(1));
            System.out.println("name: " + rs1.getString(2));
         }

         String QUERY = "TRUNCATE TABLE sampledb4";
         stmt.execute(QUERY);

         System.out.println("Table sampledb4 rows successfully deleted.");
         System.out.println("----------------------------------------");
         System.out.println(" Doing a select on sampledb4...");
         rs1 = stmt.executeQuery(sel_query);
         if (!rs1.next()){
            System.out.println(" **** ResultSet is empty *****"); 
         }
      }catch(SQLException e){
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −


C:>javac JDBCExample.java
C:>

When you run JDBCExample, it produces the following result −


C:>java JDBCExample
Rows from sampledb4
--------------------
id: 1
name: Gandhi
id: 2
name: marx
Table sampledb4 rows successfully deleted.
----------------------------------------
 Doing a select on sampledb4.
 **** ResultSet is empty *****

C:>



Print Page




Previous



Next


Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *