JDBC and Statement
Java database connectivity is API which enable Java application to interact with a database.
In this post we provides many step by step examples on using JDBC Statement, PreparedStatement
,CallableStatement ,JDBC Transaction and how to integrate with other frameworks for example spring.
1 : JDBC with Mysql database
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class MyJDBCExample{
public static void main(String[] argv) {
System.out.println("--MySQL JDBC Connection demo --");
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println("Ops ! Driver is not found, please provide a appropriate driver");
e.printStackTrace();
return;
}
Connection connection = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "password");
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
if (connection != null) {
System.out.println("Your database is connected");
} else {
System.out.println("Database connection failed please check.");
}
}
}
Before you run the application you must have mysql connector in your java lib path.
you can download the mysql driver from here:
Mysql Driver
To Run it
suppose your MyJDBCExample file is located in c:\test with java driver
Go to test folder and compile and run as
Compile
C:\test>javac -cp c:\test\mysql-connector-java-5.1.8-bin.jar;c:\test MyJDBCExample.java
Run
C:\test>java -cp c:\test\mysql-connector-java-5.1.8-bin.jar;c:\test MyJDBCExample
The output is :
--MySQL JDBC Connection demo --
Your database is connected
2 : JDBC with Oracle database
First you need to download the Oracle driver, you can download from here :
Oracle Driver
For Example :
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class MyOracleJDBC {
public static void main(String[] args) {
System.out.println("--Oracle JDBC Connection Demo--");
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Ops ! No jdbc driver found, please provide the appropriate driver");
e.printStackTrace();
return;
}
Connection connection = null;
try {
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:mytest", "username","password");
} catch (SQLException e) {
System.out.println("Connection Failed");
e.printStackTrace();
return;
}
if (connection != null) {
System.out.println("Oracle Database connected");
} else {
System.out.println("Connection Failed");
}
}
}
To run it
C:\test>javac MyOracleJDBC.java
C:\test>java -cp c:\test\ojdbc6.jar;c:\test MyOracleJDBC
Output
--Oracle JDBC Connection Demo--
Oracle Database connected
3 : Jdbc and Statement,PreparedStatement,CallableStatement
The
Statement interface is used to execute a simple SQL statement with no parameters.
The create, insert, update or delete statement uses executeUpdate(sql) and select query, uses executeQuery(sql).
3.1 : Jdbc and Satement
Here Statement is used to insert a record in a table, for example
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
public class JDBCStatementInsertDemo {
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
private static final DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
public static void main(String[] args) {
try {
insertRecordIntoTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void insertRecordIntoTable() throws SQLException {
Connection dbConnection = null;
Statement statement = null;
String insertTableSQL = "INSERT INTO testuser"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
+ "(1,'wakil','system', " + "to_date('"
+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
try {
dbConnection = getDBConnection();
statement = dbConnection.createStatement();
// execute insert SQL stetement here
statement.executeUpdate(insertTableSQL);
System.out.println("Record is inserted into testuser table");
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (statement != null) {
statement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
private static String getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return dateFormat.format(today.getTime());
}
}
Similarly you can use the Statement for updating/deleting records via JDBC and Statement
3.2 : Jdbc and Satement for select
I will show you how to select the entire records from table via JDBC statement,
and display all the records via a ResultSet object.
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCStatementSelectDemo {
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
public static void main(String[] args) {
try {
selectRecordsFromTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void selectRecordsFromTable() throws SQLException {
Connection dbConnection = null;
Statement statement = null;
String selectTableSQL = "SELECT USER_ID, USERNAME from testuser";
try {
dbConnection = getDBConnection();
statement = dbConnection.createStatement();
// execute select SQL stetement here
ResultSet rs = statement.executeQuery(selectTableSQL);
while (rs.next()) {
String userid = rs.getString("USER_ID");
String username = rs.getString("USERNAME");
System.out.println("userid : " + userid);
System.out.println("username : " + username);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (statement != null) {
statement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
Here The Result are :
A list of the records are retrieved from table testuser table and displayed.
3.3 : Jdbc and Satement for Batch
JDBC and Statement
My Example to insert records in batch process, via JDBC Statement.
Note Batch Update is not limited to Insert statement, it can be apply for Update/Delete statement as well.
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
public class JDBCBatchUpdateDemo {
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
private static final DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
public static void main(String[] args) {
try {
batchInsertRecordsIntoTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void batchInsertRecordsIntoTable() throws SQLException {
Connection dbConnection = null;
Statement statement = null;
String insertTableSQL1 = "INSERT INTO testuser"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
+ "(101,'wakil','system', " + "to_date('"
+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
String insertTableSQL2 = "INSERT INTO testuser"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
+ "(102,'wakil','system', " + "to_date('"
+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
String insertTableSQL3 = "INSERT INTO testuser"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
+ "(103,'wakil','system', " + "to_date('"
+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
try {
dbConnection = getDBConnection();
statement = dbConnection.createStatement();
dbConnection.setAutoCommit(false);
statement.addBatch(insertTableSQL1);
statement.addBatch(insertTableSQL2);
statement.addBatch(insertTableSQL3);
statement.executeBatch();
dbConnection.commit();
System.out.println("Records are inserted into testuser table");
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (statement != null) {
statement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
private static String getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return dateFormat.format(today.getTime());
}
}
Here the result are :
3 records are inserted into database via batch update process.
i.e. JDBC and Statement
