JDBC Basic
➤ Intro to JDBC
➤ JDBC vs ODBC
➤ JDBC API
➤ JDBC Driver
Connect to Database
➤ Oracle Connection
➤ MySQL Connection
➤ Using Eclipse IDE
➤ Coding Standards
➤ Close JDBC Object
➤ Java.sql Package
➤ DriverManager Class
➤ Get Connection
JDBC Statement
➤ Statement Interface
➤ Statement Example
➤ Create a database
PreparedStatement
➤ Types of statements
➤ PreparedStatement
➤ Statement vs PreParedStatement
PreparedStatement Example
➤ Insert record
➤ Update record
➤ Select record
➤ Create a table
➤ More Examples
➤ Surrogate Key
Others
➤ DatabaseMetaData
➤ ResultSetMetaData & ParameterMetaData
Create a table, drop table, and alter table are the DDL queries or non-select queries. So, we can use executeUpdate()
method of Statement / PreparedStatement object to send and execute these queries in database software. In this post, we will develop the JDBC program to create a table in Oracle and MySQL database.
In real-time programmers will not do DDL operations through JDBC applications. Every company contains database teams who design database tables based on normalization rules.
Note- When we are using DDL queries with executeUpdate()
method, then executeUpdate()
method returns 0 on success. For other queries, executeUpdate()
method returns 0 for failure.
JDBC program to create table in Oracle database
Basic details of Oracle thin driver,
JDBC Driver Class Name:: oracle.jdbc.driver.OracleDriver
URL::
jdbc:oracle:thin:@<ip-address/host-name>:<port-no-of-oracle-db>:<sid>
Username: <username_of_database>
Password: <password_of_database>
- On a local machine generally, Oracle database is located at the port no => 1521
- If you are developing JDBC application for the first time then use, host-name => localhost
How to find the sid /global name?
Open SQL Plus tool, Login to the user account and type below query,
SQL> select sys_context ('userenv','instance_name') from dual;
SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------
knowprogram
Hence the URL will be =>jdbc:oracle:thin:@localhost:1521:knowprogram
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class CreateTable {
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
String query = null;
Connection con = null;
Statement st = null;
int result = 0;
try {
// read input
scan = new Scanner(System.in);
if(scan != null) {
System.out.println("Enter sql query to "
+ "create table, \nDon't place"
+ " semicolon at the end: ");
query = scan.nextLine();
}
// establish the connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:knowprogram",
"scott", "tiger");
// create statement object
if(con != null)
st = con.createStatement();
// execute SQL query
if(st != null)
result = st.executeUpdate(query);
// process the result
if(result == 0)
System.out.println("Table created");
else
System.out.println("Table not created");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // end of try-catch block
finally {
// close JDBC objects
try {
if(st != null) st.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(con != null) con.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(scan != null) scan.close();
} catch(Exception e) {
e.printStackTrace();
}
}
} //end of main
} //end of class
Output:-
Enter sql query to create table,
Don’t place semicolon at the end:
create table test(no number(10), name varchar2(15))
Table created
Now, verify that table is created in the Oracle database or not.
SQL> desc test;
If we try to create an already existing table then it will be return java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object.
JDBC program to create table in MySQL database
JDBC Driver Class Name:: com.mysql.cj.jdbc.Driver
URL::
=> for local MySQL::
jdbc:mysql:///<logical-database-name>
=> for remote MySQL::
jdbc:mysql://<host>:<port-no>/<logical-database-name>
Username: <MySQL database username>
Password: <MySQL database password>
If you developing JDBC application for first time then use url for local MySQL
How to find the database name in MySQL?
Type below query to get all databases available in the MySQL server.
show databases;
If you are new to MySQL then create a new database,
CREATE SCHEMA `knowprogram` ;
Here knowprogram
will be the name of the database. Hence the URL for local MySQL will be jdbc:mysql:///knowprogram
In MySQL database along with tablename, we need to pass logical database name also.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class CreateTable {
public static void main(String[] args ) {
// declare variables
Scanner scan = null;
String query = null;
Connection con = null;
Statement st = null;
int result = 0;
try {
// read input
scan = new Scanner(System.in);
if(scan != null) {
System.out.println("Enter sql query "
"to create table, \nDon't place"
+" semicolon at the end: ");
query = scan.nextLine();
}
// establish the connection
con = DriverManager.getConnection(
"jdbc:mysql:///knowprogram", "root", "[email protected]");
// create statement object
if(con != null)
st = con.createStatement();
// execute SQL query
if(st != null)
result = st.executeUpdate(query);
// process the result
if(result == 0)
System.out.println("Table created");
else
System.out.println("Table not created");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} // end of try-catch block
finally {
// close JDBC objects
try {
if(st != null) st.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(con != null) con.close();
} catch(SQLException se) {
se.printStackTrace();
}
try {
if(scan != null) scan.close();
} catch(Exception e) {
e.printStackTrace();
}
}
} //end of main
} //end of class
Output:-
Enter sql query to create table,
Don’t place semicolon at the end:
create table knowprogram.test(no int, name varchar(45))
Table created
Sample code to drop table
Statement st = con.createStatement();
int result =
st.executeUpdate("DROP TABLE TEST");
if(result == 0)
System.out.println("table dropped");
else
System.out.println("table not found");
In both of the cases (creating a table, and dropping table) we can observe that else block will be never executed. For success executeUpdate()
method returns 0 or we get an exception on failure. So, writing else block is meaningless. We are writing else block only for understanding purpose, there is no use of else block in these programs.
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!
Also read:-