JDBC DDL Example

Data Definition Language (DDL) is a unique set of SQL commands that lets you manipulate the structure of the database. In this article, we will try to show how the JDBC DDL mechanism can be applied to a Java application.
 
 
 
 
 
 
 

1. Introduction

It might sound like its own programming language, but Data Definition Language (DDL) is really a way to view certain SQL commands. These are commands that are used to modify the structure of a database, rather than the database itself (the categorization of those commands is called Data Manipulation Language). All DDL commands are given below:

• CREATE
• DROP
• ALTER
• TRUNCATE
• COMMENT
• RENAME

We’ll take a look at some of the major commands in DDL i.e. CREATE, DROP, RENAME, and ALTER.

1.1 The CREATE Command

The CREATE command is used to create a table or a database. Since we are dealing with the structure of the database, will not insert any data into the table; the command simply builds the table for use. The syntax for the command is given below:

Create Database

CREATE DATABASE database_name;

The create table command requires a table name and at least one column with its corresponding data type (For e.g.: Text, Numeric etc.).

Create Table

CREATE TABLE table_name (
    Column_1 data_type,
    Column_2 data_type,
    ...
);

1.2 The DROP Command

The DROP command is used to drop a database or a table from the database. When a table is dropped, all the data goes with it. However, for this lesson, we are only concerned with tweaking the structure. The syntax for the command is given below:

Drop Database

DROP DATABASE database_name;

The syntax to DROP a table from the database is as follow,

Drop Table

DROP TABLE table_name;

Note: Be careful while dropping a table. Dropping a table will result in loss of complete information stored in the table.

1.3 The ALTER Command

The DROP command is quite extreme, as it completely wipes out the table and any data in it. However, when the data exists in the table(s) of our database, modifying the structure is easier through other means, such as ALTER. ALTER is used to add, change, or remove columns or fields in the table. It can also be used to rename the table.

Let’s break this one down a little and look at each option:

• Adding column(s)
• Modifying column(s)
• Removing columns

1.3.1 Add Column(s)

In order to add a new column, the ALTER command requires syntax similar to the CREATE statement. The table name is required and so are the column names or the definitions. The syntax for the command is given below:

Add Column

ALTER TABLE table_name ADD COLUMN column_name_1 data_type, column_name_2 data_type;

1.3.2 Modify a Column

The MODIFY commands allow you to:

• Modify Column Data Type.
• Modify Column Constraints.

This command requires the table name, the column name(s), and the column data-type(s). The syntax for the command is given below:

Modify Column

ALTER TABLE table_name MODIFY COLUMN column_name data_type;

Suppose that we want to add a new column at a specific position in the table. We can use the ALTER command together with the AFTER keyword.

After Keyword

ALTER TABLE table_name ADD COLUMN column_name data_type AFTER column_name_1;

1.3.3 Remove a Column

The DROP COLUMN command is used to delete a column from the table structure. The syntax for the command is given below:

Drop Column

ALTER TABLE table_name DROP COLUMN column_name;

1.4 The RENAME Command

The RENAME command is used to change the name of an existing database object (like Table, Column) to a new name. Renaming a table does not make it lose any data that is contained within it. The syntax for the command is given below:

Rename Table

RENAME TABLE current_table_name TO new_table_name;

1.5 What is Statement in JDBC?

• The java.sql.Statement object is used for executing a static SQL statement and returning the results it produces.
• Statement cannot accept parameters at runtime in Java JDBC.
• Statement is slower as compared to PreparedStatement in java JDBC.
• Statement is suitable for executing DDL commands – CREATE, DROP, ALTER, and TRUNCATE in Java JDBC.
• Statement can’t be used for storing or retrieving images and files in the database (i.e. using BLOB, CLOB data types) in Java JDBC.
• Statement enforces SQL injection because we end up using query formed by concatenated SQL strings in Java JDBC.
• java.sql.Statement important methods in Java JDBC are:
  • executeUpdate
  • executeQuery
  • executeBatch
  • execute

1.6 Download and Install MySQL

You can watch this video in order to download and install the MySQL database on your windows operations system.

Now, open up the Eclipse IDE and let’s start building the application!

2. JDBC Connection Pool Example

2.1 Tools Used

We are using Eclipse Kepler SR2, JDK 8, MySQL database and Maven (to download the MySQL connector and Log4J library). Having said that, we have tested the code against JDK 1.7 and it works well.

2.2 Project Structure

Firstly, let’s review the final project structure, in case you are confused about where you should create the corresponding files or folder later!

2.3 Project Creation

This section will demonstrate on how to create a Dynamic Web Java Maven project with Eclipse. In Eclipse IDE, go to File -> New -> Maven Project

In the New Maven Project window, it will ask you to select project location. By default, ‘Use default workspace location‘ will be selected. Select the ‘Create a simple project (skip archetype selection)‘ checkbox and just click on next button to proceed.

It will ask you to ‘Enter a group id for the artifact.’ We will input the details as shown in the below image. The version number will be by default 0.0.1-SNAPSHOT.

Click on Finish and now creating a maven project is completed. If you observe, it has downloaded the maven dependencies and a pom.xml file will be created. It will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcDdl</groupId>
	<artifactId>JdbcDdl</artifactId>
	<version>0.0.1-SNAPSHOT</version>
</project>

We can start adding the dependencies that developers want like MySQL, Log4J Jars etc. Let’s start building the application!

3. Application Building

Below are the steps involved in developing this application:

3.1 Maven Dependencies

In this example, we are using latest MySQL version i.e. mysql-connector-java-5.1.21 and Log4J dependencies. The updated file will have the following code:

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcDdl</groupId>
	<artifactId>JdbcDdl</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<dependencies>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.21</version>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.16</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
	</build>
</project>

3.2 Java Class Creation

Let’s create the required java files. Right click on src/main/java folder, New -> Package.

A new pop window will open where we will enter the package name as com.jcg.jdbc.ddl.example.

Once the package is created in the application, we will need to create the implementation class. Right click on the newly created package, New -> Class.

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBC Ultimate Guide right now!

In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

A new pop window will open and enter the file name as JdbcDdlExample. The implementation class will be created inside the package: com.jcg.jdbc.ddl.example.

3.2.1 Implementation of Main Class

This is the implementation class where we will be issuing the DDL commands via JDBC statement. To issue a statement, we call the statementObj.execute() method. Add the following code to it:

JdbcDdlExample.java

package com.jcg.jdbc.ddl.example;

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

import org.apache.log4j.Logger;

public class JdbcDdlExample implements DbQueryConstants {

	// JDBC Driver Name & Database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	static final String JDBC_DB_URL = "jdbc:mysql://localhost:3306";

	// JDBC Database Credentials
	static final String JDBC_USER = "root";
	static final String JDBC_PASS = "";

	public final static Logger logger = Logger.getLogger(JdbcDdlExample.class);

	public static void main(String[] args) {

		Connection connObj = null;
		Statement stmtOBj = null;
		try {
			Class.forName(JDBC_DRIVER);
			connObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS);

			stmtOBj = connObj.createStatement();

			// DDL Statement 1 - Create Database Schema!
			logger.info("\n=======CREATE " + DATABASE_NAME + " DATABASE=======");			
			stmtOBj.executeUpdate(CREATE_DATABASE_QUERY);
			logger.info("\n=======DATABASE IS SUCCESSFULLY CREATED=======\n");

			logger.info("\n=======USING " + DATABASE_NAME + " DATABASE=======\n");
			stmtOBj.executeUpdate(USE_DATABASE_QUERY);

			// DDL Statement 2 - Create Table!
			logger.info("\n=======CREATE " + TABLE_NAME + " TABLE=======");			
			stmtOBj.executeUpdate(CREATE_TABLE_QUERY);
			logger.info("\n=======TABLE IS SUCCESSFULLY CREATED=======\n");

			logger.info("\n=======SHOW TABLE STRUCTURE=======");
			showDbTableStructure();

			// DDL Statement 3(a) - Alter Table Column!
			logger.info("\n=======ALTER " + TABLE_NAME + " TABLE=======");
			stmtOBj.executeUpdate(ALTER_TABLE_QUERY);
			logger.info("\n=======TABLE IS SUCCESSFULLY ALTERED=======\n");

			logger.info("\n=======SHOW TABLE STRUCTURE=======");
			showDbTableStructure();

			// DDL Statement 3(b) - Alter Table Column Using After Clause!
			logger.info("\n=======ALTER " + TABLE_NAME + " TABLE WITH AFTER CLAUSE=======");
			stmtOBj.executeUpdate(ALTER_TABLE_WITH_AFTER_CLAUSE_QUERY);
			logger.info("\n=======TABLE IS SUCCESSFULLY ALTERED=======\n");

			logger.info("\n=======SHOW TABLE STRUCTURE=======");
			showDbTableStructure();			

			// DDL Statement 4(a) - Drop Table Column!
			logger.info("\n=======DROP COLUMN=======");
			stmtOBj.executeUpdate(DROP_COLUMN);
			logger.info("\n=======COLUMN IS SUCCESSFULLY DROPPED FROM THE TABLE=======\n");

			logger.info("\n=======SHOW TABLE STRUCTURE=======");
			showDbTableStructure();	

			// DDL Statement 4(b) - Drop Table!
			logger.info("\n=======DROP TABLE=======");
			stmtOBj.executeUpdate(DROP_TABLE);
			logger.info("\n=======TABLE IS SUCCESSFULLY DROPPED FROM THE DATABASE=======\n");

			// DDL Statement 4(c) - Drop Database!
			logger.info("\n=======DROP DATABASE=======");
			stmtOBj.executeUpdate(DROP_DATABASE);
			logger.info("\n=======DATABASE IS SUCCESSFULLY DROPPED=======");
		} catch(Exception sqlException) {
			sqlException.printStackTrace();
		} finally {
			try {
				if(stmtOBj != null) {
					stmtOBj.close();	// Close Statement Object
				}
				if(connObj != null) {
					connObj.close();	// Close Connection Object
				}
			} catch (Exception sqlException) {
				sqlException.printStackTrace();
			}
		}
	}

	// This Method Is Used To Print The Table Structure
	private static void showDbTableStructure() throws SQLException {
		StringBuilder builderObj = new StringBuilder();
		DatabaseMetaData metaObj = DriverManager.getConnection(JDBC_DB_URL, JDBC_USER, JDBC_PASS).getMetaData();
		ResultSet resultSetObj = metaObj.getColumns(DATABASE_NAME, null, TABLE_NAME, "%");

		builderObj.append(TABLE_NAME + " Columns Are?= (");
		while (resultSetObj.next()) {
			String columnName = resultSetObj.getString(4);
			builderObj.append(columnName).append(", ");
		}
		builderObj.deleteCharAt(builderObj.lastIndexOf(",")).deleteCharAt(builderObj.lastIndexOf(" ")).append(")").append("\n");
		logger.info(builderObj.toString());
	}
}

4. Run the Application

To run the application, Right click on the JdbcDdlExample class, Run As -> Java Application.

5. Project Demo

The code shows the following status as output:

• CREATE Statement

Create Database:

Create Table:

• ALTER Statement

Alter Table:

Alter Table with After Keyword:

• DROP Statement

Drop Column:

Drop Table & Database:

That’s all for this post. Happy Learning!!

6. Conclusion

Here, we understood what are DDL statements and how we can implement the same in Java.

7. Download the Eclipse Project

This was an example of JDBC DDL Commands.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.