DataBases 2 class project A.Y. 2020-2021 // Group 67 // Danelutti, Di Dio Lavore, Franchini
IntelliJ project with source code for the DataBases 2 class optional project A.Y. 2020-2021. The purpose is to deploy a Java EE application with a MySQL database.
An application deals with gamified consumer data collection. A user registers with a username, a password and an email. A registered user logs in and accesses a HOME PAGE where a “Questionnaire of the day” is published. The HOME PAGE displays the name and the image of the “product of the day” and the product reviews by other users. The HOME PAGE comprises a link to access a QUESTIONNAIRE PAGE with a questionnaire divided in two sections: a section with a variable number of marketing questions about the product of the day (e.g., Q1: “Do you know the product?” Q2: Have you purchased the product before?” and Q3 “Would you recommend the product to a friend?”) and a section with fixed inputs for collecting statistical data about the user: age, sex, expertise level (low, medium high). The user fills in the marketing section, then accesses (with a next button) the statistical section where she can complete the questionnaire and submit it (with a submit button), cancel it (with a cancel button), or go back to the previous section and change the answers (with a previous button). All inputs of the marketing section are mandatory. All inputs of the statistical section are optional. After successfully submitting the questionnaire, the user is routed to a page with a thanks and greetings message. The database contains a table of offensive words. If any response of the user contains a word listed in the table, the transaction is rolled back, no data are recorded in the database, and the user’s account is blocked so that no questionnaires can be filled in by such account in the future. When the user submits the questionnaire one or more trigger compute the gamification points to assign to the user for the specific questionnaire, according to the following rule:
- One point is assigned for every answered question of section 1 (remember that the number of questions can vary in different questionnaires).
- Two points are assigned for every answered optional question of section 2.
When the user cancels the questionnaire, no responses are stored in the database. However, the database retains the information that the user X has logged in at a given date and time. The user can access a LEADERBOARD page, which shows a list of the usernames and points of all the users who filled in the questionnaire of the day, ordered by the number of points (descending). The administrator can access a dedicated application on the same database, which features the following pages:
- A CREATION page for inserting the product of the day for the current date or for a posterior date and for creating a variable number of marketing questions about such product.
- An INSPECTION page for accessing the data of a past questionnaire. The visualized data for a given questionnaire include:
- List of users who submitted the questionnaire.
- List of users who cancelled the questionnaire.
- Questionnaire answers of each user.
- A DELETION page for ERASING the questionnaire data and the related responses and points of all users who filled in the questionnaire. Deletion should be possible only for a date preceding the current date.
You'll need to download the following tools:
- TomEE
- MySQL
- Connector/J (choose the Platform Indipended version)
- Clone this repository
git clone https://github.com/LucaDanelutti/gamifiedMarketingApplication.git
# or
gh repo clone LucaDanelutti/gamifiedMarketingApplication.git
- Download and uncompress TomEE somewhere
- Download and install MySQL
- Open the project folder with IntelliJ IDEA Ultimate
- In the menu bar, Run > Edit Configurations...
- Click '+' in the top left and select TomEE Server > Local
- If needed, click Configure... (beside Application Server) and point to the TomEE folder
- Select a TomEE version
- Leave everything else on default settings
- In the Deployment tab, under Deploy at the server startup click '+' and select Artifact... and choose the one ending with ":war exploded"
- Click Apply
- If you haven't configured MySQL yet, do it now and take note of the credentials
- Create a schema called
marketing_application
- In IntelliJ navigate to _View > Tool Windows > Database, click '+' and select Data Source > MySQL and fill in with your database connection credentials
- In Database field, specify
marketing_application
and test the connection - Click Apply
- In the Database panel, select marketing_application under your database, right click and select Run SQL script...
- Select
sql-dump/marketing_application.sql
and run it - Navigate to the TomEE folder, and open
conf/tomee.xml
- Between the
<tomee>...</tomee>
tags, add the following code, replacing SERVER, USERNAME and PASSWORD with the credentials the application will use to connect to the database
<Resource id="MarketingApplicationDB" type="DataSource">
JdbcDriver com.mysql.cj.jdbc.Driver
JdbcUrl jdbc:mysql://SERVER:3306/marketing_application
UserName USERNAME
Password PASSWORD
</Resource>
- Download Connector/J, uncompress it and drop mysql-connector-java- in /lib
- Run the server with Shift + F10 (or click the green play button); if it's not clickable, close and reopen IntelliJ
- Once the browser opens, try logging in with username
user1
and passworduser
- If the setup was successful, you'll see a welcome page. If you get a 404 error page you probably didn't setup tomee.xml at step 20; if you can't get the server to load, you probably haven't installed TomEE or Connector/J correctly.