1. You should drop the USERS table that we created in the last example. In the same database already configured for the strategy table, execute the following DDL to create a users table, a roles table, and a user_roles table. I use HeidiSQL to both create and populate the tables.
CREATE TABLE `users` (
`ID` INT(6) NOT NULL AUTO_INCREMENT,
`USERNAME` VARCHAR(50) NOT NULL,
`PASSWORD` VARCHAR(50) NOT NULL,
`ENABLED` TINYINT(1) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE INDEX `USERNAME` (`USERNAME`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1;
CREATE TABLE `roles` (
`ID` INT(6) NOT NULL AUTO_INCREMENT,
`ROLENAME` VARCHAR(50) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE INDEX `ROLENAME` (`ROLENAME`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1;
CREATE TABLE `USER_ROLES` (
`USER_ID` int(6) NOT NULL,
`ROLE_ID` int(6) NOT NULL,
KEY `USER` (`USER_ID`),
KEY `ROLE` (`ROLE_ID`),
CONSTRAINT `USER` FOREIGN KEY (`USER_ID`) REFERENCES `USERS` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `ROLE` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) COLLATE='utf8_general_ci' ENGINE=InnoDB;
The ID column in both the Users and Roles tables is the primary key and is also set to AUTO_INCREMENT. When we create users or roles rows, the ID value will not need to be specified because MySQL will create this value for us. Also notice that both the USERNAME and ROLENAME columns are set as UNIQUE INDEXES since we cannot have duplicate usernames or rolenames.
The user_roles table is called an association table, link table, joint table. This table allows us to maintain roles separately from users, with the link between the two provided by the association table. The USER_ID column is a foreign key to the ID column in the USERS table, and the ROLE_ID is a foreign key to the ID column in the ROLES table. If a user or role is deleted or updated in the USERS or ROLES tables respectively, then the corresponding entry (or entries) will be deleted or updated in the USER_ROLES table. More information can be found in the MySQL guide at:
http://dev.mysql.com/doc/refman/5.5/en/create-table.html
2. Add the dummy data to our database tables.
INSERT INTO `USERS` (`USERNAME`, `PASSWORD`, `ENABLED`) VALUES
('admin', 'password', TRUE),
('trader', 'password', TRUE),
('user', 'password', TRUE);
INSERT INTO `ROLES` (`ROLENAME`) VALUES
('ROLE_ADMIN'),
('ROLE_TRADER'),
('ROLE_USER');
INSERT INTO `USER_ROLES` (`USER_ID`, `ROLE_ID`) VALUES
(1,1),
(2,2),
(3,3);
3. The next step is to modify the User class that we created in the last example.
package com.dtr.oas.model;
import java.util.ArrayList;
import java.util.Collection;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.JoinTable;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import org.hibernate.validator.constraints.NotEmpty;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;
import com.google.common.base.Objects;
@Entity
@Table(name="USERS")
public class User extends BaseEntity implements UserDetails {
private static final long serialVersionUID = 6311364761937265306L;
static Logger logger = LoggerFactory.getLogger(User.class);
@NotNull(message = "{error.user.username.null}")
@NotEmpty(message = "{error.user.username.empty}")
@Size(max = 50, message = "{error.user.username.max}")
@Column(name = "username", length = 50)
private String username;
@NotNull(message = "{error.user.password.null}")
@NotEmpty(message = "{error.user.password.empty}")
@Size(max = 50, message = "{error.user.password.max}")
@Column(name = "password", length = 50)
private String password;
@Column(name = "enabled")
private boolean enabled;
@OneToOne
@JoinTable(name = "user_roles",
joinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")},
inverseJoinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "id")}
)
private Role role;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public boolean getEnabled() {
return enabled;
}
public void setEnabled(boolean enabled) {
this.enabled = enabled;
}
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
@Override
public String toString() {
return String.format("%s(id=%d, username=%s, password=%s, role=%s, enabled=%b)",
this.getClass().getSimpleName(),
this.getId(),
this.getUsername(),
this.getPassword(),
this.getRole().getRolename(),
this.getEnabled());
}
@Override
public boolean equals(Object o) {
if (this == o)
return true;
if (o == null)
return false;
if (o instanceof User) {
final User other = (User) o;
return Objects.equal(getId(), other.getId())
&& Objects.equal(getUsername(), other.getUsername())
&& Objects.equal(getPassword(), other.getPassword())
&& Objects.equal(getRole().getRolename(), other.getRole().getRolename())
&& Objects.equal(getEnabled(), other.getEnabled());
}
return false;
}
@Override
public int hashCode() {
return Objects.hashCode(getId(), getUsername(), getPassword(), getRole().getRolename(), getEnabled());
}
@Override
public Collection<? extends GrantedAuthority> getAuthorities() {
Collection<GrantedAuthority> authorities = new ArrayList<>();
Role userRoles = this.getRole();
if(userRoles != null) {
SimpleGrantedAuthority authority = new SimpleGrantedAuthority(userRoles.getRolename());
authorities.add(authority);
}
return authorities;
}
@Override
public boolean isAccountNonExpired() {
//return true = account is valid / not expired
return true;
}
@Override
public boolean isAccountNonLocked() {
//return true = account is not locked
return true;
}
@Override
public boolean isCredentialsNonExpired() {
//return true = password is valid / not expired
return true;
}
@Override
public boolean isEnabled() {
return this.getEnabled();
}
}
Besides adding the role instance variable to this class, the modifications are primarily associated with the annotations. There are references to @OneToOne and @JoinTable and foreign key references. These annotations primarily mean that there is a one to one reference between the USERS table and the USER_ROLES table. The Hibernate documentation on these annotations can be found at:http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#d5e3678
http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#example-one-to-many-with-join-table
4. We then create the Role class
package com.dtr.oas.model;
import java.io.Serializable;
import java.util.Set;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.JoinTable;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import org.hibernate.validator.constraints.NotEmpty;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.google.common.base.Objects;
@Entity
@Table(name = "ROLES")
public class Role extends BaseEntity implements Serializable {
private static final long serialVersionUID = 6874667425302308430L;
static Logger logger = LoggerFactory.getLogger(Role.class);
/*
CREATE TABLE `ROLES` (
`ID` INT(6) NOT NULL,
`ROLENAME` VARCHAR(50) NOT NULL,
PRIMARY KEY (`ID`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/
@NotNull(message = "{error.roles.role.null}")
@NotEmpty(message = "{error.roles.role.empty}")
@Size(max = 50, message = "{error.roles.role.max}")
@Column(name = "rolename", length = 50)
private String rolename;
//@OneToMany(cascade = CascadeType.ALL)
@OneToMany
@JoinTable(name = "user_roles",
joinColumns = {@JoinColumn(name = "role_id", referencedColumnName = "id")},
inverseJoinColumns = {@JoinColumn(name = "user_id", referencedColumnName = "id")}
)
private Set<User> userRoles;
public String getRolename() {
return rolename;
}
public void setRolename(String rolename) {
this.rolename = rolename;
}
public Set<User> getUserRoles() {
return userRoles;
}
public void setUserRoles(Set<User> userRoles) {
this.userRoles = userRoles;
}
@Override
public String toString() {
return String.format("%s(id=%d, rolename='%s')",
this.getClass().getSimpleName(),
this.getId(), this.getRolename());
}
@Override
public boolean equals(Object o) {
if (this == o)
return true;
if (o == null)
return false;
if (o instanceof Role) {
final Role other = (Role) o;
return Objects.equal(getId(), other.getId())
&& Objects.equal(getRolename(), other.getRolename());
}
return false;
}
@Override
public int hashCode() {
return Objects.hashCode(getId(), getRolename());
}
}
A very good example of the @JoinTable relationship can be found at:http://fruzenshtein.com/hibernate-join-table-intermediary/
5. Based on unit testing, I made some very slight changes to the UserDAO, UserDAOImpl, UserService, and UserServiceImpl classes as the code fragments below highlight.
...
public void addUser(User user) throws DuplicateUserException;
...
...
@Override
public void addUser(User user) throws DuplicateUserException {
logger.debug("UserDAOImpl.addUser() - [" + user.getUsername() + "]");
try {
// if the user is not found, then a UserNotFoundException is
// thrown from the getUser method call, and the new user will be
// added.
//
// if the user is found, then the flow will continue from the getUser
// method call and the DuplicateUserException will be thrown.
User userCheck = getUser(user.getUsername());
String message = "The user [" + userCheck.getUsername() + "] already exists";
throw new DuplicateUserException(message);
} catch (UserNotFoundException e) {
getCurrentSession().save(user);
}
}
...
@Override
public void updateUser(User user) throws UserNotFoundException {
User userToUpdate = getUser(user.getId());
userToUpdate.setUsername(user.getUsername());
userToUpdate.setPassword(user.getPassword());
userToUpdate.setEnabled(user.getEnabled());
userToUpdate.setRole(user.getRole());
getCurrentSession().update(userToUpdate);
}
...
...
public void addUser(User user) throws DuplicateUserException;
...
...
@Override
public void addUser(User user) throws DuplicateUserException {
userDAO.addUser(user);
}
...
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
try {
return getUser(username);
} catch (UserNotFoundException e) {
throw new UsernameNotFoundException(e.getMessage());
}
}
...
6. Following the same pattern for User that we used in Part 2, we will now create the DAO layer to access the Role object. As with the User class, we will need an interface and a class that implements the interface. First the interface, then the implementation:
package com.dtr.oas.dao;
import java.util.List;
import com.dtr.oas.model.Role;
public interface RoleDAO {
public void addRole(Role role) throws DuplicateRoleException;
public Role getRole(int id) throws RoleNotFoundException;
public Role getRole(String roleName) throws RoleNotFoundException;
public void updateRole(Role role) throws RoleNotFoundException;
public void deleteRole(int id) throws RoleNotFoundException;
public List<Role> getRoles();
}
package com.dtr.oas.dao;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.dtr.oas.model.Role;
@Repository
public class RoleDAOImpl implements RoleDAO {
static Logger logger = LoggerFactory.getLogger(RoleDAOImpl.class);
@Autowired
private SessionFactory sessionFactory;
private Session getCurrentSession() {
return sessionFactory.getCurrentSession();
}
@Override
public void addRole(Role role) throws DuplicateRoleException {
logger.debug("RoleDAOImpl.addRole() - [" + role.getRolename() + "]");
try {
// if the role is not found, then a RoleNotFoundException is
// thrown from the getRole method call, and the new role will be
// added.
//
// if the role is found, then the flow will continue from the getRole
// method call and the DuplicateRoleException will be thrown.
Role roleCheck = getRole(role.getRolename());
String message = "The role [" + roleCheck.getRolename() + "] already exists";
throw new DuplicateRoleException(message);
} catch (RoleNotFoundException e) {
getCurrentSession().save(role);
}
}
@Override
public Role getRole(int role_id) throws RoleNotFoundException {
Role roleObject = (Role) getCurrentSession().get(Role.class, role_id);
if (roleObject == null ) {
throw new RoleNotFoundException("Role id [" + role_id + "] not found");
} else {
return roleObject;
}
}
@SuppressWarnings("unchecked")
@Override
public Role getRole(String usersRole) throws RoleNotFoundException {
logger.debug("RoleDAOImpl.getRole() - [" + usersRole + "]");
Query query = getCurrentSession().createQuery("from Role where rolename = :usersRole ");
query.setString("usersRole", usersRole);
logger.debug(query.toString());
if (query.list().size() == 0 ) {
throw new RoleNotFoundException("Role [" + usersRole + "] not found");
} else {
logger.debug("Role List Size: " + query.list().size());
List<Role> list = (List<Role>)query.list();
Role roleObject = (Role) list.get(0);
return roleObject;
}
}
@Override
public void updateRole(Role role) throws RoleNotFoundException {
Role roleToUpdate = getRole(role.getId());
roleToUpdate.setId(role.getId());
roleToUpdate.setRolename(role.getRolename());
getCurrentSession().update(roleToUpdate);
}
@Override
public void deleteRole(int role_id) throws RoleNotFoundException {
Role role = getRole(role_id);
if (role != null) {
getCurrentSession().delete(role);
}
}
@Override
@SuppressWarnings("unchecked")
public List<Role> getRoles() {
return getCurrentSession().createQuery("from Role").list();
}
}
7. Next we create the service layer interface and implementation classes. First the interface:
package com.dtr.oas.service;
import java.util.List;
import com.dtr.oas.dao.DuplicateRoleException;
import com.dtr.oas.dao.RoleNotFoundException;
import com.dtr.oas.model.Role;
public interface RoleService {
public void addRole(Role role) throws DuplicateRoleException;
public Role getRole(int id) throws RoleNotFoundException;
public Role getRole(String rolename) throws RoleNotFoundException;
public void updateRole(Role role) throws RoleNotFoundException;
public void deleteRole(int id) throws RoleNotFoundException;
public List<Role> getRoles();
}
package com.dtr.oas.service;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.dtr.oas.dao.DuplicateRoleException;
import com.dtr.oas.dao.RoleDAO;
import com.dtr.oas.dao.RoleNotFoundException;
import com.dtr.oas.model.Role;
@Service
@Transactional
public class RoleServiceImpl implements RoleService {
static Logger logger = LoggerFactory.getLogger(RoleServiceImpl.class);
@Autowired
private RoleDAO roleDAO;
@Override
public void addRole(Role role) throws DuplicateRoleException {
roleDAO.addRole(role);
}
@Override
public Role getRole(int id) throws RoleNotFoundException {
return roleDAO.getRole(id);
}
@Override
public Role getRole(String rolename) throws RoleNotFoundException {
return roleDAO.getRole(rolename);
}
@Override
public void updateRole(Role role) throws RoleNotFoundException {
roleDAO.updateRole(role);
}
@Override
public void deleteRole(int id) throws RoleNotFoundException {
roleDAO.deleteRole(id);
}
@Override
public List<Role> getRoles() {
return roleDAO.getRoles();
}
}
In the next post, we will add authorization to the controllers and methods. In a later post on user management, we will encrypt the passwords in the user table.
Code at GitHub: https://github.com/dtr-trading/spring-ex10-security-db2
