@Entity
data class Election(
@Id @UuidGenerator val electionId: String = "",
var name: String = "",
var description: String = "",
var startDate: LocalDateTime? = null,
var endDate: LocalDateTime? = null,
var electionCode: String = "", // auto generated to be unique
@ManyToOne(cascade = [CascadeType.ALL]) @Column(nullable = false) var electoralCommission: ElectoralCommission = ElectoralCommission(),
@OneToMany(mappedBy = "elections") var candidates: MutableList<Candidate> = mutableListOf(),
@OneToMany var eligibleVoters: MutableList<Voter> = mutableListOf(),
@Enumerated(EnumType.STRING) var electionStatus: ElectionStatus = ElectionStatus.UPCOMING
)
@Entity
data class Voter(
@Id
@UuidGenerator
@Column(name = "voter_id")
private val _voterId: String = "",
val name: String = "",
val email: String = "",
val phoneNumber: String = "",
val dateOfBirth: LocalDateTime = LocalDateTime.now(),
@Column(name = "voter_code", unique = true, length = 8, updatable = false) var voterCode: String = "",
@Enumerated(EnumType.STRING) val voterStatus: VoterStatus = VoterStatus.REGISTERED,
@ManyToOne
@JoinColumn(name = "electoral_commission_id") val electoralCommission: ElectoralCommission? = null
) {
fun toCandidate(election: Election): Candidate {
return Candidate(
name = name,
email = email,
phoneNumber = phoneNumber,
voterCode = voterCode,
candidateStatus = CandidateStatus.REGISTERED,
election = election
)
}
}
The election entity has a list of voters (many to one, uni-directional). When someone votes, the relation of that voter with election will end.
I am not able to figure out how to achieve that using jpa. I have tried doing query using election_voter table but jpa gives error that it can’t parse the query. The Query was
delete from election_voter e where e.voter_voter_code = :voterCode
2
You mention in your question:
I have tried doing query using election_voter table …
so lets start with the table definition for voter.
CREATE TABLE voter
(
voter_id UUID PRIMARY KEY,
election_id UUID NULL,
FOREIGN KEY (election_id) REFERENCES election (election_id)
);
This should allow voter to exists without an election.
When running the code with just this, Hibernate will try to insert records into an intermediate table, so we need a modification in the Election class.
@OneToMany
@JoinColumn(name="election_id") // let Hibernate know about the FK
var eligibleVoters: MutableList<Voter> = mutableListOf()
For detaching a voter from an election with the above, we’ll need a native query:
@NativeQuery("update voter set election_id = null where voter_id = :voterId")
This is not desirable, so let’s try to use a query like this:
@Query("UPDATE Voter v SET v.electionId = NULL WHERE v.voterId = :voterId")
Query says we need to add electionId to Voter:
@Column(name = "election_id")
val electionId: String = "" // or var if updating it here
and change Election again
@OneToMany(mappedBy = "electionId")
var eligibleVoters: MutableList<Voter> = mutableListOf()