CODEWITHSUNDEEP
javasqlhibernatejpa
Learner
Learner

Reputation: 21393

How to map entities in JPA or Table structure in DB

I have a requirement where we need to display the below information on user interface:

table {
    font-family: arial, sans-serif;
    border-collapse: collapse;
    width: 100%;
}

td, th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;
}

tr:nth-child(even) {
    background-color: #dddddd;
}
<!DOCTYPE html>
<html>
<body>

<table>
  <tr>
    <th>User Id</th>
    <th>User Name</th>
    <th>Folder1</th>
    <th>Folder2</th>
    <th>Folder3</th>
  </tr>
  <tr>
    <td>1</td>
    <td>U1</td>
    <td>read</td>
    <td>execute</td>
    <td>write</td>
  </tr>
  <tr>
    <td>2</td>
    <td>U2</td>
    <td>execute</td>
    <td>execute</td>
    <td>write</td>
  </tr>
  <tr>
    <td>3</td>
    <td>U3</td>
    <td>read</td>
    <td>write</td>
    <td>write</td>
  </tr>
  <tr>
    <td>4</td>
    <td>U4</td>
    <td>write</td>
    <td>execute</td>
    <td>write</td>
  </tr>
</table>

</body>
</html>

To this requirement I am writing Java entities in JPA.

Here are my entities:

@Entity
User{
   @Id
   int id
   String name

   @ManyToMany
   List<Folder> folders;
}


@Entity
Folder{
   @Id
   int id
   String name
}

@Entity
Permission{
   @Id
   int id
   String name
}

In this mapping I am not able figure out how to map the User with Permissions for a given Folder. Because a Folder can have many permissions, and a permission can be given to different folders. It's a Many-To-Many relationship. But for a given user with a particular folder there will be only 1 permission, so I am confused on how to create entities for this mapping or to prepare the database tables to map between users with folder & permissions.

Here the assumption is the list of folders are fixed, and also the permission list is fixed.

Upvotes: 0

Views: 269

Answers (3)

SwampDev
SwampDev

Reputation: 657

This is too vague. Do you want to display that code literally or do you want it interpreted by a browser? Do you have a database backing dynamic information, or do you literally want to serve that exact code for every user request?

Isah is right. You need a table with a composite unique constraint with user id, folder id, and permission id. That serves as what's called the 'natural id' of any record. It's common to additionally add a 'surrogate primary key' for quick reference lookups from table columns referencing this new table.

Upvotes: 0

isah
isah

Reputation: 5341

One option is to define your own table and enforce unique constraints vi PK or @UniqueConstraint. Something like below:

    @Table(name = "USER_FOLDER_PERMISSION", uniqueConstraints = { @UniqueConstraint(name = "USER_FOLDER_PERM_UNIQUE", columnNames = { "user_id", "folder_id", "permission_id" }) })
    public class UserFolderPermission{
        //either use composite primary key on the 3 fields or define a surrogate primary key
        @ManyToOne
        @JoinColumn(name="user_id")
        private User user;
        @ManyToOne
        @JoinColumn(name="folder_id")
        private Folder folder;
        @ManyToOne
        @JoinColumn(name="permission_id")
        private Permission permission;

        ..
    }

And optionally define @OneToMany on the inverse side of relationship on the three entities.

Upvotes: 1

XtremeBaumer
XtremeBaumer

Reputation: 6435

i am not sure if its satisfies your need but here you have an example on many to many as i think its more suitable:

@Entity
User{
   @Id
   int id
   String name

   @ManyToMany(mappedBy="allUser")
   List<Folder> folders;
}


@Entity
Folder{
   @Id
   int id
   String name
   @ManyToMany(mappedBy="allFolders")
   private List<Permission> permissions;

}

@Entity
Permission{
   @Id
   int id
   String name
   @ManyToMany(mappedBy="permissions")
   List<Folder> allFolders;

   @ManyToMany(mappedBy="folders")
   List<User> allUser;

}

Upvotes: 0

Related Questions