This is a basic database scheme with users, user emails, and user roles. This Java Spring REST API application will provide endpoints for clients to read various data sets contained in the application's data. This application will also form the basis of a user authentication application developed elsewhere in the course
The table layout is similar to the initial version with the following exceptions:
- The join table userroles is explicitly created. This allows us to add additional columns to the join table
- Since we are creating the join table ourselves, the Many to Many relationship that formed the join table is now two Many to One relationships
- All tables now have audit fields
Thus the new table layout is as follows
- User is the driving table.
- Useremails have a Many-To-One relationship with User. Each User has many user email combinations. Each user email combination has only one User.
- Roles have a Many-To-Many relationship with Users.
Using the provided seed data, expand each endpoint below to see the output it generates.
http://localhost:2019/useremails/useremails
[
{
"useremailid": 5,
"useremail": "[email protected]",
"user": {
"userid": 4,
"username": "admin",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 3,
"name": "DATA"
}
},
{
"role": {
"roleid": 1,
"name": "ADMIN"
}
},
{
"role": {
"roleid": 2,
"name": "USER"
}
}
]
}
},
{
"useremailid": 6,
"useremail": "[email protected]",
"user": {
"userid": 4,
"username": "admin",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 3,
"name": "DATA"
}
},
{
"role": {
"roleid": 1,
"name": "ADMIN"
}
},
{
"role": {
"roleid": 2,
"name": "USER"
}
}
]
}
},
{
"useremailid": 8,
"useremail": "[email protected]",
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}
},
{
"useremailid": 9,
"useremail": "[email protected]",
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}
},
{
"useremailid": 10,
"useremail": "[email protected]",
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}
},
{
"useremailid": 12,
"useremail": "[email protected]",
"user": {
"userid": 11,
"username": "barnbarn",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
}
]
}
}
]http://localhost:2019/useremails/useremail/8
{
"useremailid": 8,
"useremail": "[email protected]",
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}
}DELETE http://localhost:2019/useremails/useremail/8
No Body Data
Status OK
http://localhost:2019/useremails/useremail/9
{
"useremailid": 9,
"useremail": "[email protected]",
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}
}POST http://localhost:2019/useremails/user/14/email/[email protected]
OUTPUT
Status CREATED
Location Header: http://localhost:2019/useremails/useremail/15
http://localhost:2019/useremails/useremail/15
{
"useremailid": 15,
"useremail": "[email protected]",
"user": {
"userid": 14,
"username": "misskitty",
"primaryemail": "[email protected]",
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
}
]
}
}http://localhost:2019/roles/roles
[
{
"roleid": 1,
"name": "ADMIN",
"users": [
{
"user": {
"userid": 4,
"username": "admin",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 5,
"useremail": "[email protected]"
},
{
"useremailid": 6,
"useremail": "[email protected]"
}
]
}
}
]
},
{
"roleid": 2,
"name": "USER",
"users": [
{
"user": {
"userid": 14,
"username": "misskitty",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 15,
"useremail": "[email protected]"
}
]
}
},
{
"user": {
"userid": 13,
"username": "puttat",
"primaryemail": "[email protected]",
"useremails": []
}
},
{
"user": {
"userid": 11,
"username": "barnbarn",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 12,
"useremail": "[email protected]"
}
]
}
},
{
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 9,
"useremail": "[email protected]"
},
{
"useremailid": 10,
"useremail": "[email protected]"
}
]
}
},
{
"user": {
"userid": 4,
"username": "admin",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 5,
"useremail": "[email protected]"
},
{
"useremailid": 6,
"useremail": "[email protected]"
}
]
}
}
]
},
{
"roleid": 3,
"name": "DATA",
"users": [
{
"user": {
"userid": 4,
"username": "admin",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 5,
"useremail": "[email protected]"
},
{
"useremailid": 6,
"useremail": "[email protected]"
}
]
}
},
{
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 9,
"useremail": "[email protected]"
},
{
"useremailid": 10,
"useremail": "[email protected]"
}
]
}
}
]
}
]http://localhost:2019/roles/role/3
{
"roleid": 3,
"name": "DATA",
"users": [
{
"user": {
"userid": 4,
"username": "admin",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 5,
"useremail": "[email protected]"
},
{
"useremailid": 6,
"useremail": "[email protected]"
}
]
}
},
{
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 9,
"useremail": "[email protected]"
},
{
"useremailid": 10,
"useremail": "[email protected]"
}
]
}
}
]
}http://localhost:2019/roles/role/name/data
{
"roleid": 3,
"name": "DATA",
"users": [
{
"user": {
"userid": 4,
"username": "admin",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 5,
"useremail": "[email protected]"
},
{
"useremailid": 6,
"useremail": "[email protected]"
}
]
}
},
{
"user": {
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 9,
"useremail": "[email protected]"
},
{
"useremailid": 10,
"useremail": "[email protected]"
}
]
}
}
]
}POST http://localhost:2019/roles/role
DATA
{
"name" : "ANewRole"
}OUTPUT
Status CREATED
Location Header: http://localhost:2019/roles/role/16
http://localhost:2019/roles/role/name/anewrole
{
"roleid": 16,
"name": "ANEWROLE",
"users": []
}PUT http://localhost:2019/roles/role/16
DATA
{
"name" : "ANewRole"
}OUTPUT
Status OK
http://localhost:2019/users/users
[
{
"userid": 4,
"username": "admin",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 5,
"useremail": "[email protected]"
},
{
"useremailid": 6,
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 3,
"name": "DATA"
}
},
{
"role": {
"roleid": 1,
"name": "ADMIN"
}
},
{
"role": {
"roleid": 2,
"name": "USER"
}
}
]
},
{
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 9,
"useremail": "[email protected]"
},
{
"useremailid": 10,
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
},
{
"userid": 11,
"username": "barnbarn",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 12,
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
}
]
},
{
"userid": 13,
"username": "puttat",
"primaryemail": "[email protected]",
"useremails": [],
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
}
]
},
{
"userid": 14,
"username": "misskitty",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 15,
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
}
]
}
]http://localhost:2019/users/user/7
{
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 9,
"useremail": "[email protected]"
},
{
"useremailid": 10,
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}http://localhost:2019/users/user/name/cinnamon
{
"userid": 7,
"username": "cinnamon",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 9,
"useremail": "[email protected]"
},
{
"useremailid": 10,
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}http://localhost:2019/users/user/name/like/da
[]POST http://localhost:2019/users/user
DATA
{
"username": "Mojo",
"primaryemail": "[email protected]",
"password" : "Coffee123",
"useremails": [
{
"useremail": "[email protected]"
},
{
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 1
}
},
{
"role": {
"roleid": 2
}
}
]
}OUTPUT
No Body Data
Location Header: http://localhost:2019/users/user/17
Status 201 Created
http://localhost:2019/users/user/name/mojo
PUT http://localhost:2019/users/user/14
DATA
{
"username": "stumps",
"primaryemail": "[email protected]",
"password" : "EarlGray123",
"useremails": [
{
"useremail": "[email protected]"
},
{
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 3
}
},
{
"role": {
"roleid": 1
}
}
]
}OUTPUT
No Body Data
Status OK
http://localhost:2019/users/user/name/stumps
{
"userid": 16,
"username": "stumps",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 19,
"useremail": "[email protected]"
},
{
"useremailid": 20,
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 1,
"name": "ADMIN"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}PATCH http://localhost:2019/users/user/7
DATA
{
"username": "cinabun",
"primaryemail": "[email protected]",
"useremails": [
{
"useremail": "[email protected]"
},
{
"useremail": "[email protected]"
},
{
"useremail": "[email protected]"
}
]
}OUTPUT
No Body Data
Status OK
http://localhost:2019/users/user/name/cinabun
{
"userid": 7,
"username": "cinabun",
"primaryemail": "[email protected]",
"useremails": [
{
"useremailid": 21,
"useremail": "[email protected]"
},
{
"useremailid": 22,
"useremail": "[email protected]"
},
{
"useremailid": 23,
"useremail": "[email protected]"
}
],
"roles": [
{
"role": {
"roleid": 2,
"name": "USER"
}
},
{
"role": {
"roleid": 3,
"name": "DATA"
}
}
]
}DELETE http://localhost:2019/users/user/14
No Body Data
Status OK