The whole lecture could be seen on YouTube via this link: https://www.youtube.com/watch?v=xkQNunagT2w
- Installing MySQL
- Set up a default database
- Accessing RBDMSs through OO Languages
This tutorial is going to guide you throughout the setup of your workspace. First, we’re going to see how to install the MySQL RDBMS in your preferred OS. This is going to be the only part that is OS dependent.
For this tutorial we’re going to use the brew package manager. The reason behind this is that we want to deal with a system that can be easily updated (we do not want to have different versions of the same db). So, you must first be sure that brew is actually installed in your Mac. Please visit the website brew.sh and follow the instructions.
Now we’re ready to kill all the processes and to completely remove MySQL (some traces could be left behind!).
#!/bin/bash brew services stop mysql sudo killall mysql sudo killall mysql brew remove mysql brew cleanup sudo rm /usr/local/mysql sudo rm -rf /usr/local/var/mysql sudo rm -rf /usr/local/mysql* sudo rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist sudo rm -rf /Library/StartupItems/MySQLCOM sudo rm -rf /Library/PreferencePanes/My* launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist sudo rm -rf ~/Library/PreferencePanes/My* sudo rm -rf /Library/Receipts/mysql* sudo rm -rf /Library/Receipts/MySQL* sudo rm -rf /private/var/db/receipts/*mysql*
Now you can install MySQL by just running the following command:
brew install mysql
First, we must start
mysql as a service. This is done by the following
brew services start mysql
The default user is called
root. In order to change its password and remove unsafe settings, call the following command:
If the root user already has a password, then change the aforementioned command to
mysql_secure_installation -p and then type the password. Now, depending on your OS setup, there could be some problems:
- If there are any pw problems, then probably the install setted automatically setted the password that is given in
/Users/<yourusername>/.mysql_secret. Use that password
- If mysql cannot read the tmp lock file, this probably means that you did not start the server. Check if the start-service command went smoothly.
Now MySQL could be accessed by typing the following command:
mysql -u root -p
If you ignored the
mysql_secure_installation setup, then just type
mysql -u root. Please remember that new users and passwords could be set using some default commands.
For GNU/Linux systems you have to install both the mysql server (the actual database) and the client (the client where to send the SQL queries via terminal).
sudo apt-get install mysql-server mysql-client
By doing so, the system will automatically ask you the password of choice.
Set up a default database
In this tutorial we’re going to use the mysql test database provided at https://github.com/datacharmer/test_db. Please not that, since now you setted a password, the default command is changed to:
mysql -u root -p < employees_partitioned.sql
Now we have to check if the procedure went smoothly. In order to do so, enter the MySql client:
mysql -u root -p
then we can see all the databases currently listed:
> show DATABASES;
our database is called
employees. So now you have to type:
> use employees;
now you can list all the tables inside this database by typing:
> show tables;
We can see each table schema with the command
Accessing RBDMSs through OO Languages
Each DB framework uses JDBC as a common interface for accessing to the relational database. This means that you must add the MySQL driver for the database within your
pom.xml file, alongside with the
By loading the MySQL driver, Maven automatically import the jar in your project and hence there is no need to load the driver by class name,
com.mysql.jdbc.Driver. This means that we can directly access to the database with the following Syntax:
Within the next subsections we’re going to see how to perform
INSERT SQL queries.
A SQL statement has to be compiled from a string. The result set is scanned through a pointer which hasn’t the standard Iterator java Syntax.
Moreover, please note that by doing so you have to remember which are the correct types for each attribute and the JDBC methods do not reflect the actual attributes’ names. Moreover, the same information (the attribute name) is repeated more than once. All these aspects are quite problematics when queries are the result of join operations involving more than one table. Moreover, any SQL query could be prone to SQL injection.
At this point, if we want to insert some values into the database, we have to:
- Create a POJO class in order to store the values in an ordered way (
- Define a SQL query in order to avoid the SQL Injection (use
- Define batch insertions (
addBatch): since single transactions do not support limitless object insertions, from time to time you must push all the values to the database (
- We have to unpack the data information in order to populate the query with the default values.
There are two possible ways to access databases from an OO language. We could either define an ORM mapping (Object-Relational Mapping), or extend the language’s syntax by allowing the usage of specific SQL statements. jOOQ implements both those paradigms.
Now create a Maven project with your favourite IDE, and add the following dependencies for your database.
At this point, we want to automatically generate the POJOs and the DAOs for our class and to automate the SQL query formulation in Java. By doing so, we have to integrate the
pom.xml file with the
build command stating:
- Which database driver we’re going to use,
- Which is the datbase URL,
- Set the database username and password.
- Choose as an
inputSchemayour MySQL database of choice,
- Select the source folder within your project,
- Specify the destination package,
All the POJOs and DAOs are generated by the
complile command. As an example:
mvn clean compile
At this point, jOOQ will handle automatically the transaction for you by using the DSL factory:
“Language Extension” (SELECT)
Suppose that we want to perform the following SQL query as before:
jOOQ allows a 1-1 mapping with methods of some specific objects through the DSLContext as follows:
At this point we can specify to return such attributes within an
employees record as follows:
Java 8 could be used to automatically print the result
We could even decide to perform join queries. If the tables share a Primary Key and a Foreign Key, then the
onKey statement could be used to carry out the operation.
This solution requires that a specific POJO class, ResultClass, must be defined:
More simply, we could even use the
fetch() method without specifying a specific POJO class.
ORM Mapping through DAOs: one-table operations (SELECT, INSERT, UPDATE)
DAO (Data Access Objects) is an architectural pattern for handling “persistency” in OO languages. By doing so we separate the
Model Java object layer from the actual operations required to access the database. This technique allows to map each Database tuple as one object in the OO model. In order to do so, we must first initialize our DAO with our current DSLContext configuration:
At this point we can use the POJOs generated by jOOQ to create new rows within the database as follows:
Later on we could even fetch a specific row (e.g.) by ID and then perform some updates: