How to create a table in a database using JDBC API?

JDBCObject Oriented ProgrammingProgramming

A. 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.

Example

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

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 CHAR (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 01-Apr-2019 14:21:54
Advertisements