Monday, 21 December 2015

JDBC Example with select insert update delete command


Hi All,

Here I am posting the whole program from which you can learn how we can connect with database with thin client driver. here I am taking the example using oracle database but you can convert this program as per your convenience.

Before testing this program on your PC, please make sure database is running and employee table created in oracle database as given in below screenshot:
Employee Table
/* Program Started from here */
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class JDBCExample {

        //  ** Thinclient Oracle database driver name. for this, you should have driver jar file which name is like ojdbc<version>.jar **
private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";

//  ** 1521 is the default port number where saas is my TNS name. you can cross check this value on your PC**
private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:saas";

private static final String DB_USER = "sachinsin";
private static final String DB_PASSWORD = "123456";

private static final DateFormat dateFormat = new SimpleDateFormat(
"yyyy/MM/dd HH:mm:ss");

public static void main(String[] argv) {

try {

databaseComm();

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

private static void databaseComm() throws SQLException {
Connection con = null;
Statement stmt = null;
String createTableSQL = "CREATE TABLE USERS("
+ "USER_ID NUMBER(5) NOT NULL, "
+ "USERNAME VARCHAR(20) NOT NULL, "
+ "CREATED_BY VARCHAR(20) NOT NULL, "
+ "CREATED_DATE DATE NOT NULL, " + "PRIMARY KEY (USER_ID) "
+ ")";
String insertTableSQL = "INSERT INTO USERS"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
+ "(1,'Sachin','Admin', " + "to_date('"
+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";
String updateTableSQL = "UPDATE USERS"
+ " SET USERNAME = 'Sachin Singh' "
+ " WHERE USER_ID = 1";
String deleteTableSQL = "DELETE USERS WHERE USER_ID = 1";

try {
con = getDBConnection();
stmt = con.createStatement();
// execute create SQL stetement
stmt.executeUpdate(createTableSQL);
System.out.println("Table is created!");
// execute insert SQL stetement
stmt.executeUpdate(insertTableSQL);
System.out.println("Record is inserted into table!");
selectRecordsFromDbUserTable();
// execute update SQL stetement
stmt.execute(updateTableSQL);
System.out.println("Record is updated to table!");
selectRecordsFromDbUserTable();
// execute delete SQL stetement
stmt.execute(deleteTableSQL);
System.out.println("Record is deleted from table!");
selectRecordsFromDbUserTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
}
}
private static void selectRecordsFromDbUserTable() throws SQLException {

Connection con = null;
Statement stmt = null;
String selectTableSQL = "SELECT USER_ID, USERNAME from USERS";
try {
con = getDBConnection();
stmt = con.createStatement();
// execute select SQL stetement
ResultSet rs = stmt.executeQuery(selectTableSQL);
while (rs.next()) {
String userid = rs.getString("USER_ID");
String username = rs.getString("USERNAME");
System.out.print("userid : " + userid +", ");
System.out.println("username : " + username);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (stmt != null) {
stmt.close();
}
if (con != null) {
con.close();
}
}
}
private static String getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return dateFormat.format(today.getTime());
}

private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,DB_PASSWORD);
} catch (Exception e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
/* Program ends here */

you need to set the class-path of ojdbc<version>.jar file when you are running the program.

I have tested this program on my PC and output will be just like below screenshot:


Please let me know if you like my program so that i will post more on other topics too.

3 comments:

  1. clean and organized code. thanks for sharing.

    ReplyDelete
  2. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a Java developer learn from Java Training in Chennai. or learn thru Java Online Training in India . Nowadays Java has tons of job opportunities on various vertical industry.

    ReplyDelete
  3. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.

    Java Training in Electronic city

    ReplyDelete

Hi,

Thanks for coming here. I hope this information helps you for preparing Java Interview.

Br,
Sachin Singh