0 Vote Up Vote Down
Garmaine Staff asked 2 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

Question Tags: , ,