• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • About
  • Life
  • Tech
  • Travel
  • Work
  • Questions
  • Contact

Welcome

.

Java JPA – searching for user across multiple tables

April 11, 2020 by

Questions › Java JPA – searching for user across multiple tables
0
Vote Up
Vote Down
Garmaine asked 3 years ago

I am working with a legacy database that isn't structured in the best way so to speak.

I have 3 tables

  1. account
  2. users
  3. stats_agents

Now the account table holds the 'master account' details with some extra account settings/parameters The Users table holds the details of all users that are defined on the system. The Agents table holds the details of all the Agents who work with the system

Agents and Users are not the same (I am not going to elaborate on their functionality within the system as it's irrelevant)

Now I have a piece of code that delivers the desired output but I do not find it elegant. The purpose of the code is to: – Get login parameters from the URL, search those 3 tables and find the matching result.

public interface AccountRepository extends JpaRepository<Account, Integer> {

    /**
     * Retrieve Admin account specific information
     * @param email
     * @return
     */
    @Query(value = "Select account.id, account.email as masteremail, account.password as masterpassword, "
            + "users.id as userid, users.email as useremail, users.password as userpassword,"
            + "stats_agent.id as agentid, stats_agent.username as agentusername, stats_agent.password as agentpassword "
            + "from account join users on account.id = users.accountid join stats_agent on account.id = stats_agent.accountid "
            + "where account.email= :email or users.email= :email or stats_agent.username= :email limit 1", nativeQuery = true)
    List<Account> getAccount(@Param("email") String email);



}

The Entity class below:

@Entity
@SecondaryTables({
        @SecondaryTable(name = "users", pkJoinColumns = @PrimaryKeyJoinColumn(name = "accountid")),
        @SecondaryTable(name = "stats_agent", pkJoinColumns = @PrimaryKeyJoinColumn(name = "accountid"))
})
public class Account {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name = "id")
    private Integer id; 

    @Column(name = "masteremail", table = "account")
    private String masteremail; 

    @Column(name = "masterpassword", table = "account")
    private String masterpassword;

    @Column(name = "userid", table = "users")
    private Integer userId;

    @Column(name = "useremail", table = "users")
    private String userEmail;

    @Column(name = "userpassword", table = "users")
    private String userPassword;

    @Column(name = "agentid", table = "stats_agent")
    private String agentid;

    @Column(name = "agentusername", table = "stats_agent")
    private String agentusername;

    @Column(name = "agentpassword", table = "stats_agent")
    private String agentpassword;

    @Override
    public String toString() {
        return "Account [id=" + id + ", masteremail=" + masteremail + ", masterpassword=" + masterpassword + ", userId="
                + userId + ", userEmail=" + userEmail + ", userPassword=" + userPassword + ", agentid=" + agentid
                + ", agentusername=" + agentusername + ", agentpassword=" + agentpassword + "]";
    }

At the moment when I enter master email address I retrieve the correct details from the Account table but on top I also get the details from agent table and users table

like so: [Account [id=1, masteremail=maciej@alpha.com, masterpassword=1234, userId=1, userEmail=test@alpha.com, userPassword=test, agentid=83, agentusername=1004, agentpassword=1004]]

Now ideally if I type in an email address it will only pull out the record from the relevant table where it finds a match, and the other fields would, therefore, be null as I think at the moment the output is clunky

Are you looking for the answer?
Original Question and Possible Answers can be found on `http://stackoverflow.com`

Question Tags: java, jpa, spring

Please login or Register to submit your answer




Primary Sidebar

Tags

Advancements best Business strategies commercial convenience economic Finances Cognitive decline Financial growth firm Future Hidden Gems Home hydration Impact Innovations lighting line of work Mental health Must-See New York City office patronage Productivity profession Profitability tips Profit optimization pursuit recreation Revenue enhancement romance sippy cups social station Technological breakthroughs technology toddlers trading transaction Treasures Uncover undertaking Well-being Wonders Work Young onset dementia

Newsletter

Complete the form below, and we'll send you all the latest news.

Footer

Footer Funnies

Who knew that reading the footer could be such a hilarious adventure? As we navigate websites, books, and documents, we often stumble upon the unassuming space at the bottom, only to discover a treasure trove of amusement. In this side-splitting compilation, we present 100 jokes that celebrate the unsung hero of content – the footer. Get ready to chuckle, giggle, and maybe even snort as we dive into the world of footnotes, disclaimers, and hidden comedic gems. Brace yourself for a wild ride through the footer!

Recent

  • Unveiling the Enigma: Almost-Magical Lamp Lights Highway Turns
  • The Impact of Young Onset Dementia on Employment and Finances: Optimizing Post-Diagnostic Approaches
  • 11 Wonders of 2023 Technological Breakthrough – Unveiling the Future
  • Work from Home and Stay Mentally Sane – Achieve Productivity and Well-being
  • Hidden Gems of New York City – Uncover the Must-See Treasures!

Search

Tags

Advancements best Business strategies commercial convenience economic Finances Cognitive decline Financial growth firm Future Hidden Gems Home hydration Impact Innovations lighting line of work Mental health Must-See New York City office patronage Productivity profession Profitability tips Profit optimization pursuit recreation Revenue enhancement romance sippy cups social station Technological breakthroughs technology toddlers trading transaction Treasures Uncover undertaking Well-being Wonders Work Young onset dementia

Copyright © 2023