Category Archives: Databases

How to call DB procedure with MyBatis and Java annotations

According to MyBatis pages: “MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.”
I often face MyBatis configuration hidden in mapping .xml files. I wanted to try something different. As I learned from Spring, configuration can be also stored not only in XML files, but also in Java annotations. Spring enabled this possibility in its later versions. I found on the net that the MyBatis configuration may be stored there as well, but on the other hand I lacked enough of good tutorials. That is why I decided to write a blog post about this topic. The information, that was needed, I found on stackoverflow.com and I gathered the pieces together here.
Let’s imagine a stored procedure, which is able to change a status of a car entity. For example we want to change the status of the car to ‘sold’. So we need to define an own Java annotation, which is registered in Spring configuration file. Then we define a mapper interface. If you are already familiar with MyBatis, you should have an idea, how this looks like. But on the contrary this time we create no .xml file in resources. We just define everything in the annotation. Let’s see an example:

@MyBatisCarProceduresMapper
public interface ChangeCarStatusMapper {
    
    @Insert(value = { 
            "{ call DATABASE.PACKAGE.CHANGE_CAR_STATUS ( #{car_id, mode=IN, jdbcType=VARCHAR, javaType=String },  #{car_status, mode=IN, jdbcType=VARCHAR, javaType=String },  #{result_code, mode=OUT, jdbcType=NUMERIC, javaType=Integer},  #{error_description, mode=OUT, jdbcType=VARCHAR, javaType=String} ) }"
            })
    @Options(statementType = StatementType.CALLABLE)
    void changeCarStatus(Map<String, Object> parameters);
}

This deserves an explanation. To call a procedure successfully we need an @Insert annotation. We can see two input and two output parameters. Also we need to define type of the statement. That is all. So it’s a more compact and effective solution, but it has also some disadvantages. For procedures with large interfaces it may become confused. Also big problem is lack of friendliness of Java in case multiline String constants. Usually you have to split such long String to more lines and use always concatenation. Also the fact that you are combining more languages in one source file may be questionable from the clean code and design perspective. But I think that despite these disadvantages it can be still advantageous for small procedure interfaces.

MariaDB tips for Fedora 20

Installation

When I did installation of MySQL in Fedora 20, it automatically installed MariaDB database. In order to install MariaDB now, the current commands will be for example:

$ sudo su -
# yum install mariadb mariadb-server phpmyadmin
# sudo systemctl enable mariadb.service
# sudo systemctl start mariadb.service

In order to setup root password for MariaDB after installation you have to do the following:

$ mysql -u root
MariaDB [(none)]> set password for root@localhost = password('your_password');
MariaDB [(none)]> exit;

Now you can login into http://localhost/phpmyadmin as a user root using the defined password. In order to login into MariaDB console again, you have to change the command to request password authentication:

$ mysql -u root -p

Usage

The database has been installed, set up to start every time after reboot. What else can be done? In order to use it as a developer, we have to create also a new database inside. The simplest way is to login to phpmyadmin manager of MariaDB database. Select tab ‘Users’ and click on the button ‘Add user’. Fill in the user name, password, repeat the password and check option ‘Create database with same name and grant all privileges.’. That’s it! You’ll see the all SQL commands, which were executed. If you are able to type the commands into the console, you can use it as well, for example:

MariaDB [(none)]> create database jbpm;
MariaDB [(none)]> use jbpm;

For example now we can deploy and run any arbitrary application, which uses jbpm schema for persistence. The schema will be generated using DDL scripts automatically by hibernate. Later we can inspect the content of the database using other SQL commands.

Working with PostgreSQL

Installation

PostgreSQL installation command on Fedora:

# yum -y install postgresql-server phpPgAdmin

It assumes that you have already Apache web server installed (package httpd)

Setup

PostgreSQL database needs to be initialized first.

# postgresql-setup initdb
# su postgres		
bash-4.2$ createuser --superuser -P pgadmin
bash-4.2$ createdb --encoding=utf-8 --owner=pgadmin pgadmin
# systemctl start postgresql.service
# systemctl enable postgresql.service

Also it is necessary to enable logging into phpPgAdmin:

# vim /etc/phpPgAdmin/config.inc.php
$conf['extra_login_security'] = false;

To change the PostgreSQL password:

$ sudo -u postgres psql
password pgadmin
q

Setting up PostgreSQL database can be tricky. Just remember that after installation there is a user called postgres, who is a user in the operating system. The most comfortable way is to login as superuser and then as postgres user. After that you are able to create a database superuser, for example pgadmin. It’s necessary to create a database for him. This is not enough, you have to disable default authentication methods in /var/lib/pgsql/data/pg_hba.conf and change them to md5. After that and restarting the service I was able to login into phpPgAdmin as a pgadmin user.

# su postgres
bash-4.2$ vim /var/lib/pgsql/data/pg_hba.conf
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
bash-4.2$ exit
# systemctl restart postgresql.service

Creating users and their databases

The easiest way is to login to phpPgAdmin as pgadmin user. First you need to create a role, which seems to be the same as user in phpPgAdmin terminology. Fill in the role’s name, password, password confirmation and don’t forget to check at least “Can login?” check box. Then create a database for this user, for simplicity it is good habit to use the same name as the name of the user (role). The latest thing is to grant privileges for the selected user on already created database.

Running SQL commands from CLI

This example will focus on a simple Bash script, which is able to reset (drop and create) a PostgreSQL database. The script may look like this:

#!/bin/bash
# PostgreSQL 9.1 pg_stat_activity.procpid, 9.2 has just pg_stat_activity.pid
psql -U pgadmin -c "update pg_database set datallowconn = 'false' where datname = '$DATABASE'; SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity WHERE datname = '$DATABASE';"
psql -U pgadmin -c "drop database $DATABASE;"
psql -U pgadmin -c "create database $DATABASE owner $DATABASE encoding 'UTF8';"

Script assumes that you have already set up a super user pgadmin. Still there are few things to be done to run the script. The first thing is to edit the file pg_hba.conf and change authentication method to md5.

# vim /var/lib/pgsql/data/pg_hba.conf
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Now you should be able to run the script, but for each command you would have to fill in the password. To remove this necessity, you can save the password in your home directory.

$ vim ~/.pgpass
localhost:5432:*:pgadmin:password

Please note that this is not safe to save the password as a plain text, but I believe that for a local testing instance of PostgreSQL database it is a satisfactory solution.

 

Caveats

Hints

  • This utility will remove orphaned large objects from the database, its diagnosis mode is:
    /usr/pgsql-9.3/bin/vacuumlo -n -v database_name