DemoOracleApplication.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Scanner;
import java.util.logging.Logger;
public class DemoOracleApplication {
private static final Logger log;
static {
System.setProperty("java.util.logging.SimpleFormatter.format", "[%4$-7s] %5$s %n");
log =Logger.getLogger(DemoPostgresApplication.class.getName());
}
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
log.info("Loading application properties");
Properties properties = new Properties();
properties.load(DemoSqlServerApplication.class.getClassLoader().getResourceAsStream("oracle.properties"));
log.info("Connecting to the database");
Connection connection = DriverManager.getConnection(properties.getProperty("jdbc.url"), properties.getProperty("jdbc.username"), properties.getProperty("jdbc.password"));
log.info("Database connection test: " + connection.getCatalog());
log.info("Create database schema");
Scanner scanner = new Scanner(DemoPostgresApplication.class.getClassLoader().getResourceAsStream("./SQL/Oracle.sql"));
Statement statement = connection.createStatement();
while (scanner.hasNextLine()) {
statement.execute(scanner.nextLine());
}
Todo1 todo = new Todo1(1L, "configuration", "congratulations, you have set up JDBC correctly!", 0);
insertData(todo, connection);
todo = readData(connection);
todo.setDetails("congratulations, you have updated data!");
updateData(todo, connection);
deleteData(todo, connection);
log.info("Closing database connection");
connection.close();
System.out.println("####################################################################");
System.out.println("Oracle DB link CRUD successful!");
System.out.println("####################################################################");
}
private static void deleteData(Todo1 todo, Connection connection) throws SQLException {
// TODO Auto-generated method stub
log.info("Delete data");
PreparedStatement deleteStatement = connection.prepareStatement("DELETE FROM todo WHERE id = ?");
deleteStatement.setLong(1, todo.getId());
deleteStatement.executeUpdate();
readData(connection);
}
private static void updateData(Todo1 todo, Connection connection) throws SQLException {
// TODO Auto-generated method stub
log.info("Update data");
PreparedStatement updateStatement = connection
.prepareStatement("UPDATE todo SET description = ?, details = ?, done = ? WHERE id = ?");
updateStatement.setString(1, todo.getDescription());
updateStatement.setString(2, todo.getDetails());
updateStatement.setInt(3, todo.isDone());
updateStatement.setLong(4, todo.getId());
updateStatement.executeUpdate();
readData(connection);
}
private static Todo1 readData(Connection connection) throws SQLException {
// TODO Auto-generated method stub
log.info("Read data");
PreparedStatement readStatement = connection.prepareStatement("SELECT * FROM todo");
ResultSet resultSet = readStatement.executeQuery();
if (!resultSet.next()) {
log.info("There is no data in the database!");
return null;
}
Todo1 todo = new Todo1();
todo.setId(resultSet.getLong("id"));
todo.setDescription(resultSet.getString("description"));
todo.setDetails(resultSet.getString("details"));
todo.setDone(resultSet.getInt("done"));
log.info("Data read from the database: " + todo.toString());
return todo;
}
private static void insertData(Todo1 todo, Connection connection) throws SQLException {
// TODO Auto-generated method stub
log.info("Insert data");
PreparedStatement insertStatement = connection
.prepareStatement("INSERT INTO todo (id, description, details, done) VALUES (?, ?, ?, ?)");
insertStatement.setLong(1, todo.getId());
insertStatement.setString(2, todo.getDescription());
insertStatement.setString(3, todo.getDetails());
insertStatement.setInt(4, todo.isDone());
insertStatement.executeUpdate();
}
}
Todo.java
package Application;
public class Todo {
private Long id;
private String description;
private String details;
private boolean done;
public Todo() {
}
public Todo(Long id, String description, String details, boolean done) {
this.id = id;
this.description = description;
this.details = details;
this.done = done;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public String getDetails() {
return details;
}
public void setDetails(String details) {
this.details = details;
}
public boolean isDone() {
return done;
}
public void setDone(boolean done) {
this.done = done;
}
@Override
public String toString() {
return "Todo{" + "id=" + id + ", description='" + description + '\'' + ", details='" + details + '\''
+ ", done=" + done + '}';
}
}
oracle.properties
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.username=xxxxxx
jdbc.password=xxxxxx
initialSize=0
maxActive=20
maxIdle=20
minIdle=1
maxWait=60000
Oracle.sql
CREATE TABLE todo (id Number(4) NOT NULL PRIMARY KEY, description varchar(100), details varchar(100), done Number(1) default '0' NOT NULL)