Last active
April 7, 2018 19:07
-
-
Save sachinsmc/133945fdf94adf9dc5e642c14dc03bfd to your computer and use it in GitHub Desktop.
JDBC Example
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package com.smc.jdbc; | |
import java.sql.DriverManager; | |
import java.sql.Connection; | |
import java.sql.SQLException; | |
import java.sql.Statement; | |
public class JDBCStatementCreateExample { | |
private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; | |
private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:BEN"; | |
private static final String DB_USER = "user"; | |
private static final String DB_PASSWORD = "password"; | |
public static void main(String[] argv) { | |
try { | |
createDbUserTable(); | |
selectRecordsFromDbUserTable(); | |
insertRecordIntoDbUserTable(); | |
} catch (SQLException e) { | |
System.out.println(e.getMessage()); | |
} | |
} | |
private static void createDbUserTable() throws SQLException { | |
Connection dbConnection = null; | |
Statement statement = null; | |
String createTableSQL = "CREATE TABLE DBUSER(" | |
+ "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) " | |
+ ")"; | |
try { | |
dbConnection = getDBConnection(); | |
statement = dbConnection.createStatement(); | |
System.out.println(createTableSQL); | |
// execute the SQL stetement | |
statement.execute(createTableSQL); | |
System.out.println("Table \"dbuser\" is created!"); | |
} catch (SQLException e) { | |
System.out.println(e.getMessage()); | |
} finally { | |
if (statement != null) { | |
statement.close(); | |
} | |
if (dbConnection != null) { | |
dbConnection.close(); | |
} | |
} | |
} | |
private static void selectRecordsFromDbUserTable() throws SQLException { | |
Connection dbConnection = null; | |
Statement statement = null; | |
String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER"; | |
try { | |
dbConnection = getDBConnection(); | |
statement = dbConnection.createStatement(); | |
System.out.println(selectTableSQL); | |
// execute select SQL stetement | |
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 void insertRecordIntoDbUserTable() throws SQLException { | |
Connection dbConnection = null; | |
Statement statement = null; | |
String insertTableSQL = "INSERT INTO DBUSER" | |
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES" | |
+ "(1,'mkyong','system', " + "to_date('" | |
+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))"; | |
try { | |
dbConnection = getDBConnection(); | |
statement = dbConnection.createStatement(); | |
System.out.println(insertTableSQL); | |
// execute insert SQL stetement | |
statement.executeUpdate(insertTableSQL); | |
System.out.println("Record is inserted into DBUSER 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); | |
} catch (ClassNotFoundException e) { | |
System.out.println(e.getMessage()); | |
} | |
try { | |
dbConnection = DriverManager.getConnection( | |
DB_CONNECTION, DB_USER,DB_PASSWORD); | |
return dbConnection; | |
} catch (SQLException e) { | |
System.out.println(e.getMessage()); | |
} | |
return dbConnection; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment