Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
JDBC Example
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