Java + SQLite 项目。外键 “On Update” 不更新。

huangapple go评论77阅读模式
英文:

java + SQLite project. Foreign key "On Update" not updating

问题

我正在使用SQLite 3.30.1版本以及DB Browser for SQLite在JavaFX应用程序(在IntelliJ中使用Java JDK 11)中进行开发。
我有一个名为"beehives"的表,每个蜂箱都可以携带疾病(存储在"diseases"表中)。
这是我的"beehives"表:

CREATE TABLE "beehives" (
	"number"	INTEGER NOT NULL,
	"id_apiary"	INTEGER NOT NULL DEFAULT -2,
	"date"	DATE,
	"type"	TEXT,
	"favorite"	BOOLEAN DEFAULT 'false',
	PRIMARY KEY("number","id_apiary"),
	FOREIGN KEY("id_apiary") REFERENCES "apiaries"("id") ON DELETE SET NULL
);

这是我的"diseases"表:

CREATE TABLE "diseases" (
	"id"	INTEGER NOT NULL,
	"id_beehive"	INTEGER NOT NULL,
	"id_apiary"	INTEGER NOT NULL,
	"disease"	TEXT NOT NULL,
	"treatment"	TEXT NOT NULL,
	"start_treat_date"	DATE NOT NULL,
	"end_treat_date"	DATE,
	PRIMARY KEY("id"),
	FOREIGN KEY("id_beehive","id_apiary") REFERENCES "beehives"("number","id_apiary") ON UPDATE CASCADE
);

这是我"apiaries"表的内容,以防你需要:

CREATE TABLE "apiaries" (
	"id"	INTEGER NOT NULL,
	"name"	TEXT NOT NULL,
	"address"	TEXT,
	PRIMARY KEY("id")
);

一切工作正常,但是当我更新一个蜂箱(例如,当我更新"number",它是"beehives"表中的主键)时,与之相关的疾病并不会更新"number"。结果是,蜂箱正确地更改了它的"number",但疾病并没有更新。并没有出现错误消息。
我调用更新的Java方法如下:

public void updateBeehiveInDB(Beehives newBeehive, Beehives oldBeehive){        

    try {

        s = "UPDATE beehives SET number=?, id_apiary=?, date=?, type=?, favorite=? WHERE number=? and id_apiary=? ";
        preparedStatement = connection.prepareStatement(s);
        preparedStatement.setInt(1, newBeehive.getNumber());
        preparedStatement.setInt(2, newBeehive.getId_apiary());
        preparedStatement.setDate(3, newBeehive.getDate());
        preparedStatement.setString(4, newBeehive.getType());
        preparedStatement.setBoolean(5, newBeehive.isFavorite());
        preparedStatement.setInt(6, oldBeehive.getNumber());
        preparedStatement.setInt(7, oldBeehive.getId_apiary());

        int i = preparedStatement.executeUpdate();

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

我尝试查看外键是否启用,根据SQLite文档中的说明此处,但我的英语不够好,我正在使用DB Manager。所以我不知道如何检查是否启用外键,或者如何手动启用它。

在我更新"beehives"表中的"number"时,我应该如何更新"diseases"表中的"id_beehives"呢?

英文:

I am making a javafx (intelliJ with java jdk 11) app using SQLite version 3.30.1 with DB Browser for SQLite.
I have a table called "beehives" and each beehive can have diseases (stored in the table "diseases").
this is my "beehives" table:

CREATE TABLE "beehives" (
"number"	INTEGER NOT NULL,
"id_apiary"	INTEGER NOT NULL DEFAULT -2,
"date"	DATE,
"type"	TEXT,
"favorite"	BOOLEAN DEFAULT 'false',
PRIMARY KEY("number","id_apiary"),
FOREIGN KEY("id_apiary") REFERENCES "apiaries"("id") ON DELETE SET NULL
);

this is my "diseases" table:

CREATE TABLE "diseases" (
"id"	INTEGER NOT NULL,
"id_beehive"	INTEGER NOT NULL,
"id_apiary"	INTEGER NOT NULL,
"disease"	TEXT NOT NULL,
"treatment"	TEXT NOT NULL,
"start_treat_date"	DATE NOT NULL,
"end_treat_date"	DATE,
PRIMARY KEY("id"),
FOREIGN KEY("id_beehive","id_apiary") REFERENCES "beehives"("number","id_apiary") ON UPDATE CASCADE
);

this is my "apiaries" table in case you need it:

CREATE TABLE "apiaries" (
"id"	INTEGER NOT NULL,
"name"	TEXT NOT NULL,
"address"	TEXT,
PRIMARY KEY("id")
);

Everything works fine, but when I update a beehive (for example when I update the "number", which is the primary key in beehives table) the diseases does not update the number. The result is that the diseases get some kind of disconnected since the beehive change his "number" correctly, but the disease doesn't update it. There is no error message.
My java method that calls the update is:

    public void updateBeehiveInDB(Beehives newBeehive,Beehives oldBeehive){        

    try {

        s = "UPDATE beehives SET number=?, id_apiary=?, date=?, type=?, favorite=? WHERE number=? and id_apiary=? ";
        preparedStatement = connection.prepareStatement(s);
        preparedStatement.setInt(1, newBeehive.getNumber());
        preparedStatement.setInt(2, newBeehive.getId_apiary());
        preparedStatement.setDate(3, newBeehive.getDate());
        preparedStatement.setString(4, newBeehive.getType());
        preparedStatement.setBoolean(5, newBeehive.isFavorite());
        preparedStatement.setInt(6, oldBeehive.getNumber());
        preparedStatement.setInt(7,oldBeehive.getId_apiary());

        int i = preparedStatement.executeUpdate();

    } catch (SQLException e) {
        e.printStackTrace();
    }
}

I tried to check if foreign keys are "on" following the SQLite documentation here, but my English is not good enough and I am using DB Manager. So no idea how to check if this is on, or how to turn it on manually.

What can I do to update the diseases "id_beehives" when I update "number" on beehives table?

答案1

得分: 0

问题在于我正在使用复合外键,并且我需要在其他表格上正确实现它,即使在这个新项目中我还没有使用它们。很难找到问题,因为IntelliJ通常会显示所有的SQL错误消息,但在这种情况下,它什么都没有显示。但是当我尝试在数据库浏览器中手动执行SQL语句时,我得到了一个错误消息,然后就能够修复它。

还必须在连接上启用外键:

public Connection openConnection() {

    try {

        String dbPath = "jdbc:sqlite:resources/db/datab.db";
        Class.forName("org.sqlite.JDBC");

        SQLiteConfig config = new SQLiteConfig();
        config.enforceForeignKeys(true);
        connection = DriverManager.getConnection(dbPath, config.toProperties());

        return connection;

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}
英文:

The problem was that i am using a composite foreign key and i need to implement it correctly on other tables too even if i was not using them yet in this new project. Was very hard to find the problem because intellij normally show all the SQL error messages, but in this case, it was not showing anything. But when i tried to do the SQL sentence manually in the DB Browser, there i got an error message and was able to fix it.

Also had to activate foreign key on the connection:

   public Connection openConnection() {

    try {

        String dbPath = "jdbc:sqlite:resources/db/datab.db";
        Class.forName("org.sqlite.JDBC");

        SQLiteConfig config = new SQLiteConfig();
        config.enforceForeignKeys(true);
        connection = DriverManager.getConnection(dbPath,config.toProperties());

        return connection;

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

huangapple
  • 本文由 发表于 2020年5月2日 18:51:22
  • 转载请务必保留本文链接:https://go.coder-hub.com/61558162.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定