I'm using JDBC in javaFx project with SQlite database. I have two tables "PROJECT" and "SMAllPROJECT" such as PROJECT has this structure PROJECT(name,date,state) and SMAllPROJECT has this structure SMAllPROJECT(name,date,state,*project_name*). Project_name is referenced from name column of PROJECT table.
I'm trying to delete row from PROJECT and the rows referenced in SMAllPoject should be deleted. In sqlite studio I configure onCascade method when I apply delete a row from parent table and every thing is well when I test it in SQlite studio, but in my code it deleted rows just from parent table.
This is my code :
Controller method
public void ExecuteDeleteProject() {
if (!SearchIdProjectSupp.getText().isEmpty()) {
Project project = new ProjectDao().FindString(SearchIdProjectSupp.getText());
new ProjectDao().Delete(project);
String title = "Suppression";
String message = "Vous avez supprimé le projet " + SearchIdProjectSupp.getText() + ".";
NotificationType notification = NotificationType.SUCCESS;
TrayNotification tray = new TrayNotification();
tray.setTitle(title);
tray.setMessage(message);
tray.setNotificationType(notification);
tray.setAnimationType(AnimationType.SLIDE);
tray.setImage(new Image("Images/check.png"));
tray.setRectangleFill(Paint.valueOf("#a8a9fe"));
tray.showAndDismiss(Duration.seconds(4));
SearchIdProjectSupp.setText("");
SuppPaneProject.setVisible(false);
DeleteProjetButton.setDisable(true);
CountP.setVisible(true);
CountP();
CountR();
CountPP();
}
}
DAO pattern method
public boolean Delete(Project Object) {
try {
String queryDeletePerson = "DElETE FROM PROJECT WHERE name=" + "'" + Object.getName() + "'";//Query Insertion in Person_Table
PreparedStatement preparedStatementPerson = Dbaconnection.getConnection().prepareStatement(queryDeletePerson);//Prepared statement i use this for high performance
preparedStatementPerson.execute();
} catch (SQLException ex) {
Logger.getLogger(EMPDao.class.getName()).log(Level.SEVERE, null, ex);
}
return true;
}
And this is my DDL
CREATE TABLE PROJECT (
NAME VARCHAR2 (100) PRIMARY KEY,
STATE VARCHAR2 (100),
DATEDBT DATE,
);
CREATE TABLE SMALLPROJECT (
NAMEPROJECT VARCHAR2 (100) REFERENCES PROJECT (NAME) ON DELETE CASCADE
ON UPDATE CASCADE,
NAME VARCHAR2 (20) PRIMARY KEY,
DATEDBT DATE,
STATE VARCHAR2 (20) PRIMARY KEY
);
According to SQLite Foreign Key Support and the answer of How do you enforce foreign key constraints in SQLite through Java? ,it should configure database connection before execute any query.I had this problem because i did not enforce foreign key constraints.
public static final String DB_URL = "jdbc:sqlite:database.db";
public static final String DRIVER = "org.sqlite.JDBC";
public static Connection getConnection() throws ClassNotFoundException {
Class.forName(DRIVER);
Connection connection = null;
try {
SQLiteConfig config = new SQLiteConfig(); //I add this configuration
config.enforceForeignKeys(true);
connection = DriverManager.getConnection(DB_URL,config.toProperties());
} catch (SQLException ex) {}
return connection;
}