Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Keyword search on MySQL database in JPA(Want to implement a keyword search on a MySQL database with multiple tables within JPA)
icon5.gif  Keyword search on MySQL database in JPA [message #1717164] Thu, 10 December 2015 11:49
Faizan Ali is currently offline Faizan AliFriend
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.
Previous Topic:JPA Criteria Query
Next Topic:EclipseLink less than clause doesn't work correctly
Goto Forum:
  


Current Time: Fri Mar 05 20:45:55 GMT 2021

Powered by FUDForum. Page generated in 0.01904 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top