”;
This chapter provides examples on how to create table, temporary table and duplicate table using JDBC application. Before executing the following example, make sure you have the following in place −
-
To execute the following example you can replace the username and password with your actual user name and password.
-
Your MySQL is up and running.
Required Steps
The following steps are required to create a new Database using JDBC application −
-
Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.
-
Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.
-
Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to create a table in a seleted database.
-
Clean up the environment − try with resources automatically closes the resources.
Example: Creating a Table
In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared a SQL string to create a new table REGISTRATION and created the table in database by calling statement.executeUpdate() method.
In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.
Copy and paste the following example in TestApplication.java, compile and run as follows −
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestApplication {
static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
static final String USER = "guest";
static final String PASS = "guest123";
public static void main(String[] args) {
// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
) {
String sql = "CREATE TABLE REGISTRATION " +
"(id INTEGER not NULL, " +
" first VARCHAR(255), " +
" last VARCHAR(255), " +
" age INTEGER, " +
" PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Now let us compile the above example as follows −
C:>javac TestApplication.java C:>
When you run TestApplication, it produces the following result −
C:>java TestApplication Created table in given database... C:>
Example: Creating a Temporary Table
We can create a temporary table, which exists only during an active session. Temporary tables are supported in MySQL, SQL Server, Oracle etc.
In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared a SQL string to create a new Temporary table EMPLOYEES_COPY and created the table in database by calling statement.execute() method.
In next line of code, we”ve created a query string to get all records from the newly created temporary table EMPLOYEES_COPY. Query is fired using statement.executeQuery() method and result is stored in a ResultSet. ResultSet is iterated to print all the employees.
In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.
Copy and paste the following example in TestApplication.java, compile and run as follows −
import java.sql.*;
public class TestApplication {
static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
static final String USER = "guest";
static final String PASS = "guest123";
public static void main(String args[]) {
try{
Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
String QUERY1 = "CREATE TEMPORARY TABLE EMPLOYEES_COPY SELECT * FROM EMPLOYEES";
stmt.execute(QUERY1);
String QUERY2 = "SELECT * FROM EMPLOYEES_COPY";
ResultSet rs = stmt.executeQuery(QUERY2);
while (rs.next()){
System.out.print("Id: " + rs.getInt("id"));
System.out.print(" Age: " + rs.getInt("age"));
System.out.print(" First: " + rs.getString("first"));
System.out.println(" Last: " + rs.getString("last"));
System.out.println("------------------------------------------");
}
}catch (SQLException e){
e.printStackTrace();
}
}
}
Now let us compile the above example as follows −
C:>javac TestApplication.java C:>
When you run TestApplication, it produces the following result −
C:>java TestApplication Id: 1 Age: 18 First: Zara Last: Ali ------------------------------------------ Id: 2 Age: 25 First: Mahnaz Last: Fatma ------------------------------------------ Id: 3 Age: 20 First: Zaid Last: Khan ------------------------------------------ Id: 4 Age: 28 First: Sumit Last: Mittal ------------------------------------------ Id: 7 Age: 20 First: Rita Last: Tez ----------------------------------------- Id: 8 Age: 20 First: Sita Last: Singh ------------------------------------------ Id: 21 Age: 35 First: Jeevan Last: Rao ------------------------------------------ Id: 22 Age: 40 First: Aditya Last: Chaube ------------------------------------------ Id: 25 Age: 35 First: Jeevan Last: Rao ------------------------------------------ Id: 26 Age: 35 First: Aditya Last: Chaube ------------------------------------------ Id: 34 Age: 45 First: Ahmed Last: Ali ------------------------------------------ Id: 35 Age: 50 First: Raksha Last: Agarwal ------------------------------------------ C:>
Example: Creating a Duplicate Table
We can create a TABLE which is exactly similar to an existing table. The syntax is:
CREATE new_table_name LIKE orig_table_name;
After executing the command, all data from original table is copied to new table.
In this example, we”ve three static strings containing a dababase connection url, username, password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared a SQL string to create a new duplicate table EMPLOYEES_O and created the table in database by calling statement.execute() method.
In next line of code, we”ve created a query string to get all records from the newly created duplicate table EMPLOYEES_O. Query is fired using statement.executeQuery() method and result is stored in a ResultSet. ResultSet is iterated to print all the employees.
In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.
Copy and paste the following example in TestApplication.java, compile and run as follows −
import java.sql.*;
// This class demonstrates the way of creating a table which is exactly similar to another table.
public class TestApplication {
static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
static final String USER = "guest";
static final String PASS = "guest123";
public static void main(String args[]) {
try{
Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
String QUERY1 = "CREATE TABLE EMPLOYEES_O LIKE EMPLOYEES";
stmt.execute(QUERY1);
ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEES");
System.out.println("Displaying records from EMPLOYEES_O");
while (rs.next()){
System.out.print("Id: " + rs.getInt("id"));
System.out.print(" Age: " + rs.getInt("age"));
System.out.print(" First: " + rs.getString("first"));
System.out.println(" Last: " + rs.getString("last"));
System.out.println("------------------------------------------");
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
Now let us compile the above example as follows −
C:>javac TestApplication.java C:>
When you run TestApplication, it produces the following result −
C:>java TestApplication Displaying records from EMPLOYEES_O Id: 1 Age: 18 First: Zara Last: Ali ------------------------------------------ Id: 2 Age: 25 First: Mahnaz Last: Fatma ------------------------------------------ Id: 3 Age: 20 First: Zaid Last: Khan ------------------------------------------ Id: 4 Age: 28 First: Sumit Last: Mittal ------------------------------------------ Id: 7 Age: 20 First: Rita Last: Tez ------------------------------------------ Id: 8 Age: 20 First: Sita Last: Singh ------------------------------------------ Id: 21 Age: 35 First: Jeevan Last: Rao ------------------------------------------ Id: 22 Age: 40 First: Aditya Last: Chaube ----------------------------------------- Id: 25 Age: 35 First: Jeevan Last: Rao ------------------------------------------ Id: 26 Age: 35 First: Aditya Last: Chaube ------------------------------------------ Id: 34 Age: 45 First: Ahmed Last: Ali ------------------------------------------ Id: 35 Age: 50 First: Raksha Last: Agarwal ------------------------------------------ C:>
”;