JDBC Statement – Create Table Example – Developers Corner

Java

A JDBC statement object is used to send SQL command(s) to your relational database management system (RDBMS). It is associated with an open connection to the database and may not be created without one.

Common Methods used with Statements

Method Name	Description
addBatch(String sql)	Adds the given SQL to the current list of SQL commmands for this Statement object
execute(String sql)	Executes the given SQL statement, which may return multiple results
executeBatch()	Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts
executeQuery(String sql)	Executes the given SQL statement, which returns a single ResultSet object
executeUpdate(String sql)	Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement

Creating a Statement Object

statement = connection.createStatement();
statement.executeUpdate(sql);

Creating Table DDL for SQL Server

CREATE TABLE dbo.user_preference (
  userpref_id       varchar(30)   NOT NULL,
  client_id         varchar(10)   NULL,
  category_tag      varchar(20)   NOT NULL,
  payload           varchar(1024) NULL,
  last_update_time  datetime      DEFAULT getdate() NOT NULL,
  entered_user      varchar(10)   NOT NULL,
  status            varchar(10)   NOT NULL,
  CONSTRAINT userpref_PK
  PRIMARY KEY NONCLUSTERED (userpref_id)
)

Full Program Listing

package com.avaldes.tutorials;

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

public class JDBCStatementCreateTableExample {

  public static void main(String[] args) throws SQLException {
    String database_url   = "jdbc:sqlserver://localhost:1433;databaseName=tutorial";
    String username     = "webuser";
    String password     = "webuser123";
    Connection connection   = null;
    Statement statement   = null;
    ResultSet result      = null;
    
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
   
    try {
      connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }
   
    System.out.println("Trying to get create a table using statement...");
    try {
      if (connection != null) {
        String sql = "CREATE TABLE dbo.user_preference ( "
              + "   userpref_id       varchar(30)   NOT NULL, "
              + "   client_id         varchar(10)   NULL, "
              + "   category_tag      varchar(20)   NOT NULL, "
              + "   payload           varchar(1024) NULL, "
              + "   last_update_time  datetime      DEFAULT getdate() NOT NULL, "
              + "   entered_user      varchar(10)   NOT NULL, "
              + "   status            varchar(10)   NOT NULL, "
              + "   CONSTRAINT userpref_PK "
              + "   PRIMARY KEY NONCLUSTERED (userpref_id))";

        statement = connection.createStatement();
        statement.executeUpdate(sql);
        System.out.println(sql);
        System.out.println("Table successfully created...");
      } else {
        System.out.println("ERROR: Unable to make a database connection!");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      return;
    } finally {
      System.out.println("Closing connection...");
        if (connection != null) connection.close();
    }
  }
}

Check Database to ensure DDL statement executed properly

Output

Trying to get create a table using statement...
CREATE TABLE dbo.user_preference (
   userpref_id       varchar(30)   NOT NULL,
   client_id         varchar(10)   NULL,
   category_tag      varchar(20)   NOT NULL,
   payload           varchar(1024) NULL,
   last_update_time  datetime      DEFAULT getdate() NOT NULL,
   entered_user      varchar(10)   NOT NULL,
   status            varchar(10)   NOT NULL,
   CONSTRAINT userpref_PK
   PRIMARY KEY NONCLUSTERED (userpref_id))
Table successfully created...
Closing connection...