Execute create table statement java

JAVA JDBC – Create, Update and Delete Table in MySQL Database

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) <> > > >

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) <> > > >

Column Quantity Added Successfully
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.

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) <> > > >

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) <> > > >

NOTE: You must recreate ITEM Table because this table will be used to storing or updating data in next chapter.

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.

Источник

JDBC — Create Table

If you have successfully tested the GetEmp.java file, let’s practice another JDBC programming example. The prepared example is the business card management program. This chapter will create a table and a sequence using JDBC.

CREATE TABLE NAMECARD ( NO NUMBER CONSTRAINT PK_NAMECARD PRIMARY KEY, NAME VARCHAR2(20) NOT NULL, MOBILE VARCHAR2(20) NOT NULL, EMAIL VARCHAR2(40), COMPANY VARCHAR2(60) ); CREATE SEQUENCE SEQ_NAMECARD_NO INCREMENT BY 1 START WITH 1;

Let’s recall the JDBC programming order again.

1. Loading the JDBC Driver
2. Obtaining a Connection
3. Execute SQL
4. [If the SQL statement is a select statement, use the returned ResultSet to process the data.]
5. Returning Resources

In the Package Explorer view in Eclipse, create NamecardDDL.java in the jdbc project to be in the package net.java_school.jdbc.test. Write all code within the main().

1. Loading a JDBC Driver

Class.forName() method loads the startup class of the Oracle JDBC driver into memory. Copy and paste this snippet from GetEmp.java.

Since the forName() method of Class class is a method that must handle ClassNotFoundException, the above compilation error occurs. With the help of Eclipse’s code assist, Select the second solution to insert a try ~ catch statement. (for Eclipse’s code assist, place your mouse in the code where the compilation error occurs)

2. Obtaining a Connection

You can get a connection using the getConnection() method of the DriverManager class.

Connection and DriverManager are in the java.sql package. Add the import statement by selecting the first solution in the code assist of the compile error.

The first argument to the DriverManager’s getConnection() method is URL. The second argument is the user, and the third argument is the user’s password. Since we decided to create a table and a sequence in the Scott account, the second and third argument values are scott and tiger.

The DriverManager’s getConnection() method can raise an SQLException. Select the second solution of the code assist and handle the exception with a try ~ catch statement.

DriverManager’s getConnection() and the Connection interface’s methods are all methods that must handle SQLException. So put the getConnection() in a try block. The Connection instances used in the try block must be returned in the finally block. So declare the con variable of the Connection type outside the try block.

3. Getting a Statement

Decalre the stmt variable of the Statement type outside the try block. The reason for declaring the variable con outside the try block is the same. If the «Statement is of an unresolved type» compile error occurs, let the code assist function of Eclipse to add «import java.sql.Statement;».

4. Execute SQL

The next step is to execute the SQL statement. First, make the SQL statement.

Connection con = null; Statement stmt = null; String sql = null; try < con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:XE", "scott", "tiger"); stmt = con.createStatement(); sql = "CREATE TABLE NAMECARD ( " + "NO NUMBER CONSTRAINT PK_NAMECARD PRIMARY KEY, " + "NAME VARCHAR2(20) NOT NULL, " + "MOBILE VARCHAR2(20) NOT NULL, " + "EMAIL VARCHAR2(40), " + "COMPANY VARCHAR2(60))"; stmt.executeUpdate(sql); > catch (SQLException e) < // TODO Auto-generated catch block e.printStackTrace(); >

Execute the SQL statement by calling ExecuteUpdate() of the Statement by passing the string that creates the table as an argument. Next, the SQL statement that generates the sequence. Execute the SQL statement by calling executeUpdate() of the Statement by passing the string that generates the sequence as an argument.

Connection con = null; Statement stmt = null; String sql = null; try < con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:XE", "scott", "tiger"); stmt = con.createStatement(); sql = "CREATE TABLE NAMECARD ( " + "NO NUMBER CONSTRAINT PK_NAMECARD PRIMARY KEY, " + "NAME VARCHAR2(20) NOT NULL, " + "MOBILE VARCHAR2(20) NOT NULL, " + "EMAIL VARCHAR2(40), " + "COMPANY VARCHAR2(60))"; stmt.executeUpdate(sql); sql + "INCREMENT BY 1 " + "START WITH 1"; stmt.executeUpdate(sql); > catch (SQLException e) < // TODO Auto-generated catch block e.printStackTrace(); >

5. Returning Resources

Write the finally block and insert the code that returns resources in the block. Since you must return resources in the reverse order of creation, let the Statement instance resource disappers first.

Since the close() method of the Statement must handle an SQLException, the compile error occurs. Use Eclipse’s code assists to insert a try — catch block. The close() method of the Connection interface also must handle an SQLException. Use Eclipse’s code assists to insert a try ~ catch block.

Returning resources is most important in the JDBC code. Do not forget to write the code to close resources. Run NamecardDDL.java. If the exception does not occur, access the Scott account with SQL * PLUS to see if the table and sequence exist. If an exception occurs, add code displaying the SQL statement into the catch block. One of the disadvantages of JDBC is that you need to replace the SQL statements with Java strings. Many people make many mistakes when replacing SQL statements with Java strings.

C:\ Command Prompt

C:\Users\java>sqlplus scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on 08-JAN-2011 21:11:20 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production SQL> select tname from tab; TNAME ------------------------------------------------------------ DEPT EMP BONUS SALGRADE NAMECARD 5 rows selected. SQL> select sequence_name from user_sequences; SEQUENCE_NAME ------------------------------------------------------------ SEQ_NAMECARD_NO SQL>

If you run NamecardDDL another, you will encounter an exception. Because the table and sequence with the same name already exist in the Scott account.

executeUpdate() Statement’s executeUpdate() method is used to execute DDL statements such as create table .. or DML statements (INSERT, UPDATE, DELETE).

JDBC

• JDBC Introduction
• Oracle Install
• SCOTT account
• Select
• Insert Update Delete
• Oracle JDBC Test
• JDBC Guide
• JDBC — Create Table
• JDBC — Insert
• JDBC — Select
• JDBC — Update
• PreparedStatement
• JDBC -Join
• JDBC — Transaction
• Connection Pool
• Examples
• Namecard
• Java Bank

Источник

Java creating tables in MySQL Database

When it code reaches these points, it generates an error which is caught by the SQLException block. It is either very simple or it is very complicated Could anybody point out where this newbie to Java MySQL programming has made the error and hopefully not errors, thanks in advance Here is the Rest of the code in full

 public class DbStuff < private String jdbcDriver = "com.mysql.jdbc.Driver"; private String dbAddress = "jdbc:mysql://localhost:3306/"; private String userPass = "?user=root&password="; private String dbName = "TIGER19"; private String userName = "root"; private String password = ""; private PreparedStatement preStatement; private Statement statement; private ResultSet result; private Connection con; public DbStuff() < try < Class.forName(jdbcDriver); con = DriverManager.getConnection(dbAddress + dbName, userName, password); >catch (ClassNotFoundException e) < e.printStackTrace(); >catch (SQLException e) < createDatabase(); createTableCub1(); >> private void createDatabase() < try < Class.forName(jdbcDriver); con = DriverManager.getConnection(dbAddress + userPass); Statement s = con.createStatement(); int myResult = s.executeUpdate("CREATE DATABASE IF NOT EXISTS " + dbName); >catch (ClassNotFoundException | SQLException e) < e.printStackTrace(); >> private void createTableCub1() < String myTableName = "CREATE TABLE AgentDetail (" + "idNo INT(64) NOT NULL AUTO_INCREMENT," + "initials VARCHAR(2)," + "agentDate DATE," + "agentCount INT(64))"; try < Class.forName(jdbcDriver); con = DriverManager.getConnection(dbAddress + dbName, userName, password); statement = con.createStatement(); //The next line has the issue statement.executeUpdate(myTableName); System.out.println("Table Created"); >catch (SQLException e ) < System.out.println("An error has occurred on Table Creation"); >catch (ClassNotFoundException e) < System.out.println("An Mysql drivers were not found"); >> > 

Источник

SQLite Java: Create a New Table

Summary: in this tutorial, you will learn how to create a new table in an SQLite database from a Java program using SQLite JDBC Driver.

To create a new table in a specific database, you use the following steps:

1. First, prepare a CREATE TABLE statement to create the table you want.
2. Second, connect to the database.
3. Third, create a new instance of the Statement class from a Connection object.
4. Fourth, execute the CREATE TABLE statement by calling the executeUpdate() method of the Statement object.

The following program illustrates the steps of creating a table.

package net.sqlitetutorial; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; /** * * @author sqlitetutorial.net */ public class Main < /** * Create a new table in the test database * */ public static void createNewTable() < // SQLite connection string String url = "jdbc:sqlite:C://sqlite/db/tests.db"; // SQL statement for creating a new table String sql = "CREATE TABLE IF NOT EXISTS warehouses (\n" + " id integer PRIMARY KEY,\n" + " name text NOT NULL,\n" + " capacity real\n" + ");"; try (Connection conn = DriverManager.getConnection(url); Statement stmt = conn.createStatement()) < // create a new table stmt.execute(sql); > catch (SQLException e) < System.out.println(e.getMessage()); >> /** * @param args the command line arguments */ public static void main(String[] args)  < createNewTable(); >> Code language: Java (java)

In this tutorial, you have learned how to create a new table in an SQLite database from a Java program using the SQLite JDBC driver.

Источник