 Keyword search on MySQL database in JPA [message #1717164] |
Thu, 10 December 2015 11:49 |
 Faizan Ali
Messages: 1
Registered: December 2015 |
Junior Member |
|
|
After searching through Google and Stack Overflow, I haven't been able to find a proper answer to my searching dilemma.
TL;dr: I want to implement a keyword search across a few MySQL database tables through my JPA (EclipseLink) code.
Here's my setup:
I have three entities; Candidates, CandidateEducation, Schools
The relationships are as follows:
Candidates has a OneToMany relationship with CandidateEducation
CandidateEducation has a ManyToOne relationship with Schools
I've gone through the FULLTEXT search documentation for MySQL but I do not believe it is the most effective way to search based on my setup. As of now, the Criteria API seems most promising but I do not know if it is the best approach. Search engines like Solr are out of the question for now.
And to put things at scale (for efficiency purposes), I will not have more than 10,000 Candidates at any time, no more than 15,000 CandidateEducation at a time, and no more than 250 Schools at a time.
I will only be searching certain fields within the entities, so the entire entity does not need to be searchable.
A few example searches:
columbia welding: This would return all Candidates entities which have Columbia as a City (within the Candidates entity) and welding as a Field (within the CandidateEducation entity). But since the search implementation does not know that columbia and welding are a City and Field respectively, it will still search through all the fields in the entities.
montgomery college: This would return all Candidates entities which have montgomery college as a Name (within the Schools entity).
What is the best way to implement a keyword search here? Please let me know if additional clarification is needed, I will be happy to go into as much detail as required.
The code (if it helps) is:
Candidates
public class Candidates implements Serializable {
private static final long serialVersionUID = 1L;
// Overriding column names to match database tables
// Tells JPA that the following field is the primary key for this entity
@Id
// Tells JPA to use auto-incremented values of the MySQL table as the userID
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "CandidateID")
private int candidateID;
@Column(name = "FirstName")
private String firstName;
@Column(name = "LastName")
private String lastName;
@Column(name = "City")
private String city;
@Column(name = "State")
private String state;
@Column(name = "Email")
private String email;
@Column(name = "Password")
private String password;
@Column(name = "Phone")
private String phone;
@Column(name = "AlternatePhone")
private String alternatePhone;
@Column(name = "Ethnicity")
private String ethnicity;
@Column(name = "Veteran")
private String veteran;
@Column(name = "AdditionalInformation")
private String additionalInformation;
@Column(name = "WorkExperience")
private String workExperience;
@Column(name = "Employed")
private String employed;
@Column(name = "Relocate")
private String relocate;
@Column(name = "PastEducation")
private String pastEducation;
@Column(name = "PastTraining")
private String pastTraining;
@Lob
@Column(name = "Resume")
private byte[] resume;
// If this entity's education is updated then its children do not have to be
// persisted. If this entity is deleted then so are its children.
@OneToMany(mappedBy = "candidate", cascade = { CascadeType.PERSIST,
CascadeType.REMOVE }, fetch = FetchType.EAGER, orphanRemoval = true)
private List<CandidateEducation> education = new ArrayList<>();
CandidateEducation
public class CandidateEducation implements Serializable {
private static final long serialVersionUID = 1L;
// Tells JPA that the following field is the primary key for this entity
@Id
// Tells JPA to use auto-incremented values of the MySQL table as the userID
@GeneratedValue(strategy = GenerationType.IDENTITY)
// Overriding column names to match database tables
@Column(name = "CandidateEducationID")
private int candidateEducationID;
@Column(name = "CandidateID", insertable=false, updatable=false)
private int candidateID;
@Column(name = "SchoolID", insertable=false, updatable=false)
private int schoolID;
@Column(name = "Field")
private String field;
@Column(name = "AnticipatedCertification")
private String anticipatedCertification;
@Column(name = "CompletionDate")
private String completionDate;
@Column(name = "ObtainedCertification")
private String obtainedCertification;
@ManyToOne (cascade = CascadeType.PERSIST, optional = false)
@JoinColumn(name = "CandidateID", referencedColumnName = "CandidateID")
private Candidates candidate;
@ManyToOne (cascade = CascadeType.PERSIST, optional = false)
@JoinColumn(name = "SchoolID", referencedColumnName = "SchoolID")
private Schools school;
Schools
public class Schools implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name="SchoolID")
private int schoolID;
@Column(name="Name")
private String name;
@Column(name = "State")
private String state;
@OneToMany(mappedBy = "school", cascade = CascadeType.PERSIST,
fetch = FetchType.EAGER)
private List<CandidateEducation> candidates = new ArrayList<>();
TL;dr: Want to implement a keyword search on a MySQL database with multiple tables within JPA (EclipseLink).
I sincerely appreciate any help! Every answer no matter how small pushes me closer toward figuring this out.
|
|
|
Powered by
FUDForum. Page generated in 0.04043 seconds
] 
Search
Help
Register
Login
Home
