In this chapter you will learn:

1. How to create table using JDBC in Java?
2. How to Update, delete or rename table in JDBC?
3. Programming Example

After creating database using JDBC in Java, next is creating and working with MySQL table in JDBC. Here, I am going to write a program which will create, update, rename and delete the table. Inserting and manipulating data will be discussed in next chapter so, keep focus on creating, updating and deleting table in this chapter.

Programming Example

In the previous chapter we created a database STOREDB. We will use this database to store table.

package TableDemo;
import java.sql.*;

public class CreateTable 
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query="CREATE TABLE ITEM(" +
	    	    "ID int NOT NULL AUTO_INCREMENT," +
	    	    "PRODUCT varchar(50)," +
	    	    "PRICE varchar(10)," + 
	    	    "PRIMARY KEY(ID))";	     
	    
	    //Step 4 : Run Query
	    stmt.executeUpdate(query);
	    System.out.println("TABle ITEM Created Successfully");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Updating Table

Several times you need to alter table definition mostly when you want to add more field or remove a column or sometimes changing column range. Here is an example of Table Update in JDBC is given below.

Programming Example

package TableDemo;
import java.sql.*;

public class UpdateTable
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to Server
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    //Add New Column Quantity
	    String query="ALTER TABLE ITEM ADD Quantity int(5)";
	    //Remove Column PRICE
	    String query1="ALTER TABLE ITEM DROP COLUMN PRICE";
	    //Edit Column PRODUCT
	    String query2="ALTER TABLE ITEM MODIFY COLUMN PRODUCT varchar(200)";
	    //Step 4 : Run Query
	    stmt.executeUpdate(query);
	    System.out.println("Column Quantity Added Successfully");
	    stmt.executeUpdate(query1);
	    System.out.println("Table Price Removed");
	    stmt.executeUpdate(query2);
	    System.out.println("Changed PRODUCT varchar(50) to PRODUCT nvarchar(200)");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Rename Table

When you need to rename MySQL Table using JDBC in your java program you can use the following program to rename table.

package TableDemo;
import java.sql.*;

public class RenameTable
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query="RENAME TABLE ITEM TO COSMETIC";
	    
	    //Step 4 : Run Query
	    stmt.executeUpdate(query);
	    
	    System.out.println("TABLE ITEM Renamed to COSMETIC Successfully");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Deleting Table

Finally when you no longer need to Table you can delete it using DROP command. Here, is the complete program.

Programming Example

package TableDemo;
import java.sql.*;

public class DeleteTable
{
	   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	   static final String dburl = "jdbc:mysql://localhost/STOREDB";	   
	   static final String dbuser = "root";
	   static final String dbpass = "root";
	   
public static void main(String[] args)
{
	Connection con = null;
	Statement stmt = null;
	
	try 
	{
	    //Step 1 : Connecting to server and database
	    con = DriverManager.getConnection(dburl, dbuser, dbpass);	    
	    //Step 2 : Initialize Statement
	    stmt=con.createStatement();
	    //Step 3 : SQL Query
	    String query="DROP TABLE COSMETIC";
	    
	    //Step 4 : Run Query
	    stmt.executeUpdate(query);
	    
	    System.out.println("TABLE COSMETIC Deleted Successfully");
	} 
	
	catch (SQLException e) 
	{
	    System.err.println("Cannot connect ! ");
	    e.printStackTrace();
	}
	
	finally {
	    System.out.println("Closing the connection.");
	    if (con != null) try { con.close(); } catch (SQLException ignore) {}
	}
	
}
}

Output

Summary

In this tutorial I explained how can you create, delete, update or rename table using JDBC. You must recreate ITEM table because this table will be used for demo example in next chapter. Hope, you wouldn’t have face any difficulties learning this tutorial. The next chapter is Inserting, Updating, Selecting or Deleting Data in Table.