13. JDBC

Main steps

• Set up MySQL
• Set up database
• Configure detailed MySQL logging (Optional)
• Implement activity
• Extra exercise
• Error solution
• Extra exercises
• Additional info
  • In English
  • In Latvian

Set up MySQL

1. If necessary:

   1. To enable additional software repositories, run

      sudo mousepad /etc/apt/sources.list

      and uncomment lines staring with # deb... (delete #)

      Warning

      Do not uncomment # deb cdrom..., because then manager will look for installation disk!

   2. To refresh info about repositories run command:

      sudo apt-get update

2. To install MySQL server, run following command:

   sudo apt-get install mysql-server
3. Download MySQL Workbench installation file from https://dev.mysql.com/downloads/workbench/ for 64-bit Ubuntu 20.04
4. Open file and install it.

Set up database

1. If necessary, change values of user, password and database variables in TeacherManager.java class and  key values of src/resources/appplication.properties configuration file

2. Create new database user, by executing following commands in terminal (each line one by one!):

   sudo mysql
   CREATE USER 'uXX'@'localhost' IDENTIFIED BY 'uXX';
   FLUSH PRIVILEGES;
   exit

   where XX is your given number.

3. Create structure of the database, by executing following commands in terminal:

   cd ~/workspace/JTM/src/main/java/jtm/activity13/
   sudo mysql
   create database databaseXX;
   GRANT ALL ON databaseXX.* TO 'uXX'@'localhost';
   FLUSH PRIVILEGES;
   exit
   mysql -uuXX -puXX databaseXX < database.sql

   where XX is your given number.

Configure detailed MySQL logging (Optional)

To enable detailed logging on MySQL server:

1. Open configuration file:

   sudo mousepad /etc/mysql/mysql.conf.d/mysqld.cnf

2. Change two lines to following (i.e. remove # from beginning of line):

   general_log_file        = /var/log/mysql/query.log
   general_log             = 1

3. Restart mysql server:

   sudo systemctl restart mysql

4. To watch log in real time, run command:

   sudo tail -f /var/log/mysql/query.log

Implement activity

1. Investigate jtm.activity13 package
2. Implement class TeacherManager to pass unit tests

Extra exercise

• Extra exercise 7: JPA

Error solution

1. To check that server is running:

   ss -ntlp|grep 3306

   should return:

   LISTEN    0         151              127.0.0.1:3306             0.0.0.0:*
   LISTEN    0         70               127.0.0.1:33060            0.0.0.0:*

2. Check, that you can log into server, run MySQL console:

   mysql -uuXX -puXX

   Then, inside MySQL console:

   show databases;

   should return list of databases.

   exit

Extra exercises

• Extra 8. JPA

Additional info

In English

• DBeaver tool
• JDBC tutorial (PDF file)

In Latvian

• MySQL datubāze
• Datu bāzu tehnoloģijas