How to drop a table from JavaDB using JDBC?

JDBCObject Oriented ProgrammingProgramming

You can create a table in a database using the CREATE TABLE query.

Syntax

CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

To create a table in a database using JDBC API you need to −

  • Register the driver − Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.
  • Establish a connection − Connect ot the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.
  • Create Statement − Create a Statement object using the createStatement() method of the Connection interface.
  • Execute the Query − Execute the query using the execute() method of the Statement interface.

Following JDBC program establishes connection with MySQL and creates a table named customers in the database named SampleDB

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateTableExample {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/SampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Query to create a table
      String query = "CREATE TABLE CUSTOMERS("
         + "ID INT NOT NULL, "
         + "NAME VARCHAR (20) NOT NULL, "
         + "AGE INT NOT NULL, "
         + "SALARY DECIMAL (18, 2), "
         + "ADDRESS VARCHAR (25) , "
         + "PRIMARY KEY (ID))";
      stmt.execute(query);
      System.out.println("Table Created......");
   }
}

Output

Connection established......
Table Created......

The show tables command gives you the list of tables in the current database in MySQL.

If you verify the list of tables in the database named sampledb, you can observe the newly created table in it as −

mysql> show tables;
+--------------------+
| Tables_in_sampledb |
+--------------------+
| articles           |
| customers          |
| dispatches         |
| technologies       |
| tutorial           |
+--------------------+
5 rows in set (0.00 sec)
raja
Published on 04-Jul-2019 16:15:57
Advertisements