I'm building a JavaFX app on top of a 11G DB.
But now while going further i had a problem with the suggested approach.
I can't insert the detail record unless i commit after inserting the master.
So if i inserted a record in Invoice i must commit before inserting the items in the InvoiceItems table ... which is not good because what if something went wrong while inserting the item.
Instead of just rolling back now i have to delete the invoice ... which i don't think is the best way to do (or is it?)
Both the DepartmentId and EmployeeId are populated using a trigger and a sequence.
I built a test case based on HR schema tow show you how I am implementing the DB connection and operations:
public class Database {
private static final String driver = "oracle.jdbc.driver.OracleDriver";
private static final String username = "HR";
private static final String password = "hr";
private static final String thinConn = "jdbc:oracle:thin:@localhost:1521:xe";
private static Database database;
private static Connection conn;
private Database(){
System.out.println("Databse loading ..");
new Thread(new Runnable(){
@Override
public void run() {
try {
if(conn==null){
setconnection();
}
} catch (SQLException | ClassNotFoundException ex) {
Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
}
}
}).start();
}
/* We create this static method so that all view share the same Database instance
* While the constructor is private
* Now instead of calling "new Database();" from controllers we'll call "Database.getInstance();"
*/
public static Database getInstance(){
if(database == null){
database = new Database();
}
return database;
}
public static Connection setconnection() throws SQLException, ClassNotFoundException {
Class.forName(driver); // load Oracle driver
conn = DriverManager.getConnection(thinConn, username, password);
conn.setAutoCommit(false);
return conn;
}
public ResultSet execQuery(PreparedStatement stmnt) throws SQLException {
ResultSet rs = stmnt.executeQuery();
return rs;
}
public ResultSet execInsert(PreparedStatement stmnt) throws SQLException{
ResultSet generatedKeys = null;
stmnt.executeUpdate();
generatedKeys = stmnt.getGeneratedKeys();
System.out.println("Stmnt Executed");
return generatedKeys;
}
public void execUpdate(PreparedStatement stmnt) throws SQLException{
stmnt.executeUpdate();
System.out.println("Stmnt Executed");
//Close statement
stmnt.close();
}
public void commit() throws SQLException{
conn.commit();
}
public void rollback() throws SQLException{
conn.rollback();
}
public void rollback(Savepoint savepoint) throws SQLException{
conn.rollback(savepoint);
}
public Savepoint setSavepoint() throws SQLException{
return conn.setSavepoint();
}
public void releaseSavepoint(Savepoint savepoint) throws SQLException{
conn.releaseSavepoint(savepoint);
}
public class InsertRollBackApp extends Application {
@Override
public void start(Stage primaryStage) {
Button btn = new Button();
btn.setText("Say 'Hello World'");
btn.setOnAction(new EventHandler<ActionEvent>() {
@Override
public void handle(ActionEvent event) {
try {
int deptId = insertDept();
//If you removed next line The application will hang and insert fail
Database.getInstance().commit();
insertEmp(deptId);
// Database.getInstance().rollback();
Database.getInstance().commit();
} catch (SQLException ex) {
Logger.getLogger(InsertRollBackApp.class.getName()).log(Level.SEVERE, null, ex);
System.out.println("Error Raised: "+ex.getMessage());
} catch (ClassNotFoundException ex) {
System.out.println("Error Raised: "+ex.getMessage());
Logger.getLogger(InsertRollBackApp.class.getName()).log(Level.SEVERE, null, ex);
}
}
});
StackPane root = new StackPane();
root.getChildren().add(btn);
Scene scene = new Scene(root, 300, 250);
primaryStage.setTitle("Hello World!");
primaryStage.setScene(scene);
primaryStage.show();
}
public int insertDept() throws SQLException, ClassNotFoundException{
String sql = "INSERT INTO DEPARTMENTS("
+" DEPARTMENT_NAME)"
+ " VALUES(?)";
PreparedStatement stmnt = Database.setconnection().prepareStatement(sql, new String[]{"DEPARTMENT_ID"});
stmnt.setString(1, "DUMMY_DEPT");
ResultSet rs = Database.getInstance().execInsert(stmnt);
int DeptId = 0;
while(rs.next()){
DeptId = rs.getInt(1);
}
return DeptId;
}
public void insertEmp(int DeptId) throws SQLException, ClassNotFoundException{
String sql = "INSERT INTO EMPLOYEES("
+" FIRST_NAME,"
+" LAST_NAME,"
+" EMAIL,"
+" PHONE_NUMBER,"
+" HIRE_DATE,"
+" JOB_ID,"
+ " DEPARTMENT_ID)"
+ " VALUES(?,?,?,?,(SELECT SYSDATE FROM DUAL),?,?)";
PreparedStatement stmnt = Database.setconnection().prepareStatement(sql, new String[]{"EMPLOYEE_ID"});
stmnt.setString(1, "M");
stmnt.setString(2, "G");
stmnt.setString(3, "mg");
stmnt.setString(4,"515.123.1111");
stmnt.setString(5,"IT_PROG");
stmnt.setInt(6, DeptId);
Database.getInstance().execInsert(stmnt);
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
launch(args);
}
}