I need a Spring boot project, using latest stable version of all componentes.
Must implement mvc, hibernate, multitenancy, security (using a login form), and all connected to a MySQL database. Deliver and IntelliJ Idea project.
For Multitenancy, I need the single database, multiple schemas approach. So I need a master schema that has relation between an username and the schema associated with, and then each user will have his own unique schema, dynamically created after registration and selected after login.
There must be a master database (schema in MySql) that contains the user ID and the table schema the user is allowed to use. Then each user will have one schema associated with one or more tables.
There must be a register option. When the user registers, it will receive a confirmation email, and when the user clicks is taken to the web app to validate his account, and then create a unique schema for him and the corresponfing tables. This unique schema along with his username, will be stored in the master database.
The password stored in the database must be encrypted.
In the login form, there must be a validation to know if the user is allowed into the system or not, and then he can only use the schema associated with him. There must be also a "Remember Me" checkbox in the login form, so the user doesnt need to enter his details again to login, unless he clicks Logout.
For this project, we can use the following db structure
CREATE TABLE IF NOT EXISTS `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`firstName` varchar(255) DEFAULT NULL,
`lastName` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`accountType` varchar(255) DEFAULT NULL,
`billingAddress` varchar(255) DEFAULT NULL,
`mobilePhone` varchar(255) DEFAULT NULL,
`officePhone` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`notes` varchar(255) DEFAULT NULL,
`createadBy` bigint(20) DEFAULT NULL,
`modifiedBy` bigint(20) DEFAULT NULL,
`createdDate` datetime DEFAULT NULL,
`modifiedDate` datetime DEFAULT NULL,
`lastLoggedIn` datetime DEFAULT NULL,
'active'
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`role` int(11) DEFAULT NULL,
`userId` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK3580769128426C` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `productGroup` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`groupName` varchar(255) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`createadBy` bigint(20) DEFAULT NULL,
`modifiedBy` bigint(20) DEFAULT NULL,
`createdDate` datetime DEFAULT NULL,
`modifiedDate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `groupName` (`groupName`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `product` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`productName` varchar(255) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`productGroup` bigint(20) DEFAULT NULL,
`price` bigint(20) DEFAULT NULL,
`createadBy` bigint(20) DEFAULT NULL,
`modifiedBy` bigint(20) DEFAULT NULL,
`createdDate` datetime DEFAULT NULL,
`modifiedDate` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `productname` (`productName`),
KEY `FKPRODGROUP` (`productGroup`),
CONSTRAINT `FKPRODGROUP` FOREIGN KEY (`productGroup`) REFERENCES `productGroup` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
For HTML forms, use Thymeleaf.
For testing purposes, implement a few controllers and views.
1. Add user (each user can add another user that can interact with his account, something like members of the same company that all will have access to the same account). Only an user with admin role can add other user to his account.
2. List/Delete users
3. Add/List/Delete Product
4. List Product
5. List/Edit Roles