Moinsen ich bin der Neue.
Ich habe ein webapp das Veranstaltungen verwaltet. Kunden dürfen keine überschneidende Zeiträume für eine Veranstaltung eintragen - das wurde mit RULE und FUNCTION datenbankseitig gelöst.
Mein Problem ist: Wenn die Kunden eine Fehlermeldung bekommen, dass die Veranstaltung nicht eingetragen wurde, weil sich der Zeitraum mit einer anderen Veranstaltung überschneidet, jedoch (sturer weise) nochmal speichern, sagt Hibernate "Ja, OK" !!
Wenn ich folgenden abschnitt zweimal ausführe bekomme ich ein OK - das darf nicht sein!.
Code:
Transaction t = s.beginTransaction();
try {
s.save(e);
t.commit();
System.out.println("OK");
} catch (Exception ex) {
System.out.println("ERROR");
t.rollback();
}
ERROR
OK
System: PG 8.4
Java 1.6 (Sowohl OpenJDK alsauch von Sun)
Tabelle:
Code:
CREATE TABLE "public"."event" (
"eventid" INTEGER NOT NULL,
"textlabel" CHAR(25) NOT NULL,
"groupid" INTEGER NOT NULL,
"timewhen" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"timeuntil" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
"eventroomid" INTEGER,
"membersonly" BOOLEAN DEFAULT false NOT NULL,
CONSTRAINT "event_pkey" PRIMARY KEY("eventid"),
CONSTRAINT "event_fk" FOREIGN KEY ("eventroomid")
REFERENCES "public"."eventroom"("eventroomid")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE,
CONSTRAINT "event_fk1" FOREIGN KEY ("groupid")
REFERENCES "public"."group"("groupid")
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;
CREATE RULE "IllegalTimeRangeInsert" AS ON INSERT TO "public"."event"
WHERE (( SELECT count(*) AS count
FROM event
WHERE new.eventroomid = event.eventroomid AND new.eventid <> event.eventid AND (new.timewhen >= event.timewhen AND new.timeuntil <= event.timeuntil OR new.timewhen <= event.timewhen AND new.timeuntil >= event.timeuntil OR (new.timeuntil - '00:00:01'::interval) >= event.timewhen AND (new.timeuntil - '00:00:01'::interval) <= event.timeuntil OR new.timewhen >= event.timewhen AND new.timewhen <= (event.timeuntil - '00:00:01'::interval)))) > 0
DO (SELECT exception('EventCollision'::character varying) AS exception;);
CREATE RULE "IllegalTimeRangeUpdate" AS ON UPDATE TO "public"."event"
WHERE (( SELECT count(*) AS count
FROM event
WHERE new.eventroomid = event.eventroomid AND new.timewhen > event.timewhen AND new.timeuntil < event.timeuntil OR new.timewhen < event.timewhen AND new.timeuntil > event.timeuntil OR new.timeuntil > event.timeuntil AND new.timeuntil < event.timewhen OR new.timewhen > event.timewhen AND new.timewhen < event.timeuntil)) > 0
DO (SELECT exception('${err.EventCollision}'::character varying) AS exception;);
CREATE RULE "negativeExceptionInsert" AS ON INSERT TO "public"."event"
WHERE new.timewhen > new.timeuntil
DO (SELECT exception('${err.negativeDate}'::character varying) AS exception;);
CREATE RULE "negativeExceptionUpdate" AS ON UPDATE TO "public"."event"
WHERE new.timewhen > new.timeuntil
DO (SELECT exception('${err.negativeDate}'::character varying) AS exception;);
Datenbankfunktion exception(varchar)
Code:
CREATE OR REPLACE FUNCTION "public"."exception" (
varchar
)
RETURNS boolean AS
$body$
BEGIN RAISE EXCEPTION '%',$1;END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
Jemand eine Idee?