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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | 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
Closing the connection.
_
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 | 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
Table Price Removed
Changed PRODUCT varchar(50) to PRODUCT nvarchar(200)
Closing the connection.
Rename Table
When you need to rename MySQL Table using JDBC in your java program you can use the following program to rename table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | 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
Closing the connection.
Deleting Table
Finally when you no longer need to Table you can delete it using DROP command. Here, is the complete program.
Programming Example
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | 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
Closing the connection.
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.