Simple CRM using Spring 5, Hibernate and MySQL

Spring and Hibernate combine to quickly create this CRM

Authentication and Authorization powered by Spring — Hibernate

First things first; Why this tech stack?

Spring is one of the most used Java EE Frameworks. Hibernate is also one the most popular ORM frameworks used for mapping domain classes to relational databases.

Spring provides extensive utilities to integrate Hibernate leveraging the productivity of the developer. In fact, they go pretty well with each other and that’s why Spring - Hibernate combo is used a lot in enterprise applications.

The goal of this project was a simple CRM (Customer Relationship Management) tool, where users with proper authentication and authorization would be able to perform CRUD operations on a customer data set.

The technology stack used was Java Spring 5, Hibernate, MySQL and basic HTML with CSS.


The Implementation

Starting with creating two new database schema with one table each, one to hold the application users:

CREATE DATABASE  IF NOT EXISTS `users_db`;
USE `users_db`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`username` varchar(50) NOT NULL,
`password` char(68) NOT NULL,
`enabled` tinyint(1) NOT NULL,
PRIMARY KEY (`username`)
)

and one to hold the actual data, e.g the customers:

CREATE DATABASE  IF NOT EXISTS `customers_db`
CREATE TABLE `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) DEFAULT NULL,
`last_name` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`))

Using a properties file to hold the JDBC and pool connection properties, a few Spring annotations ( @PropertySource, @Autowired) and a DataSource, we can establish a connection between the Java application and the database:

@Autowired
private Environment env;
@Bean
public DataSource securityDataSource() {
// create connection pool
ComboPooledDataSource securityDataSource = new ComboPooledDataSource();
// set the jdbc driver class
try {
securityDataSource.setDriverClass(env.getProperty("security.jdbc.driver"));
} catch (PropertyVetoException exc) {
throw new RuntimeException(exc);
}
securityDataSource.setJdbcUrl(env.getProperty("security.jdbc.url"));
securityDataSource.setUser(env.getProperty("security.jdbc.user"));
securityDataSource.setPassword(env.getProperty("security.jdbc.password"));
// set connection pool props
securityDataSource.setInitialPoolSize(
getIntProperty("security.connection.pool.initialPoolSize"));
securityDataSource.setMinPoolSize(
getIntProperty("security.connection.pool.minPoolSize"));
securityDataSource.setMaxPoolSize(
getIntProperty("security.connection.pool.maxPoolSize"));
securityDataSource.setMaxIdleTime(
getIntProperty("security.connection.pool.maxIdleTime"));
return securityDataSource;
}

In order for users to be able to access pages and perform CRUD operations on the customer data set (based on their role), the following HTTP configuration was added:

@Override
protected void configure(HttpSecurity http) throws Exception {
http.authorizeRequests()
.antMatchers("/customer/showForm*").hasAnyRole("MANAGER", "ADMIN")
.antMatchers("/customer/save*").hasAnyRole("MANAGER", "ADMIN")
.antMatchers("/customer/delete").hasRole("ADMIN")
.antMatchers("/customer/**").hasRole("EMPLOYEE")
.antMatchers("/resources/**").permitAll()
.and()
.formLogin()
.loginPage("/showMyLoginPage")
.loginProcessingUrl("/authenticateTheUser")
.permitAll()
.and()
.logout().permitAll()
.and()
.exceptionHandling().accessDeniedPage("/access-denied");
}

In the above block, additional configuration was added to override Spring’s default login form (to add a custom one with some CSS ) and also Tomcat’s default access denied error page.

The controller to handle those looks like:

@Controller
public class LoginController {
@GetMapping("/showMyLoginPage")
public String showMyLoginPage() {
return "fancy-login";
}
// add request mapping for /access-denied
@GetMapping("/access-denied")
public String showAccessDenied() {
return "access-denied";
}
}

Even though log in form was overridden, authentication code was not required as redirecting the data to /authenticateTheUser lets Spring handle that for us.

Moving forward with the customer data, a new simple class with getters and setters was created to hold the basic customer info:

@Entity
@Table(name="customer")
public class Customer {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="id")
private int id;
@Column(name="first_name")
private String firstName;
@Column(name="last_name")
private String lastName;
@Column(name="email")
private String email;
}

Using @Entity, @Table and @Column annotations, Spring understands how these properties match the MySQL tables.

To handle the CRUD operations, a service:

@Service
public class CustomerServiceImpl implements CustomerService {
// Inject customer dao
@Autowired
private CustomerDAO customerDAO;
@Override
@Transactional
public List<Customer> getCustomers() {
return customerDAO.getCustomers();
}
@Override
@Transactional
public void saveCustomer(Customer theCustomer) {
customerDAO.saveCustomer(theCustomer);
}
@Override
@Transactional
public Customer getCustomer(int theId) {
return customerDAO.getCustomer(theId);
}
@Override
@Transactional
public void deleteCustomer(int theId) {
customerDAO.deleteCustomer(theId);
}
}

and a data access object were added:

@Repository
public class CustomerDAOImpl implements CustomerDAO {
// inject session factory
@Autowired
private SessionFactory sessionFactory;
@Override
public List<Customer> getCustomers() {
// get the current hibernate session
Session currentSession = sessionFactory.getCurrentSession();
// create a query
Query<Customer> theQuery =
currentSession.createQuery("from Customer order by lastName",
Customer.class);
// execute query and get result list
List<Customer> customers = theQuery.getResultList();
// return the results
return customers;
}
@Override
public void saveCustomer(Customer theCustomer) {
// get current hibernate session
Session currentSession = sessionFactory.getCurrentSession();
// save/update the customer
currentSession.saveOrUpdate(theCustomer);
}
@Override
public Customer getCustomer(int theId) {
// get the current hibernate session
Session currentSession = sessionFactory.getCurrentSession();
// retrieve/read from database using the primary key
Customer theCustomer = currentSession.get(Customer.class, theId);
return theCustomer;
}
@Override
public void deleteCustomer(int theId) {
// get the current hibernate session
Session currentSession = sessionFactory.getCurrentSession();
// delete object with primary key
Query theQuery =
currentSession.createQuery("delete from Customer where id=:customerId");
theQuery.setParameter("customerId", theId);
theQuery.executeUpdate();
} }

Finally, a customer controller was added to handle the URL mapping, render the views and make the appropriate service calls:

@Controller
@RequestMapping("/customer")
public class CustomerController {
// inject customer service
@Autowired
private CustomerService customerService;
@GetMapping("/list")
public String listCustomers(Model theModel) {
// get customers from the service
List<Customer> theCustomers = customerService.getCustomers();
// add the customers to the model
theModel.addAttribute("customers", theCustomers);
return "list-customers";
}
@GetMapping("/showFormForAdd")
public String showFormForAdd(Model theModel) {
// create model attribute to bind form data
Customer theCustomer = new Customer();
theModel.addAttribute("customer", theCustomer);
return "customer-form";
}
@PostMapping("/saveCustomer")
public String saveCustomer(@ModelAttribute("customer") Customer theCustomer) {
// save the customer using service
customerService.saveCustomer(theCustomer);
return "redirect:/customer/list";
}
@GetMapping("/showFormForUpdate")
public String showFormForUpdate(@RequestParam("customerId") int theId,
Model theModel) {
// get the customer from service
Customer theCustomer = customerService.getCustomer(theId);
// set customer as a model attribute to pre-populate the form
theModel.addAttribute("customer", theCustomer);
// send over to form
return "customer-form";
}
@GetMapping("/delete")
public String deleteCustomer(@RequestParam("customerId") int theId) {
// delete the customer
customerService.deleteCustomer(theId);
return "redirect:/customer/list";
}
}

It’s worth mentioning that those controller functions use Spring’s Model to achieve data binding on the rendered views.

The Application

Here are some screenshots of the project: