Rdbms

A simple setup when two Postgres databases prefilled with schema/data needed. docker-compose.yml version: '3.6' services: postgres1: image: postgres restart: always environment: - DATABASE_HOST=127.0.0.1 - POSTGRES_USER=root - POSTGRES_PASSWORD=root - POSTGRES_DB=root ports: - "15432:15432" volumes: - ./postgres1-init.sql:/docker-entrypoint-initdb.d/docker_postgres_init.sql postgres2: image: postgres restart: always environment: - DATABASE_HOST=127.0.0.1 - POSTGRES_USER=root - POSTGRES_PASSWORD=root - POSTGRES_DB=root ports: - "25432:25432" volumes: - ./postgres2-init.sql:/docker-entrypoint-initdb.d/docker_postgres_init.sql And our sample init scripts: CREATE USER tester WITH PASSWORD 'tester' CREATEDB; CREATE DATABASE testdb WITH OWNER = tester ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TABLESPACE = pg_default CONNECTION LIMIT = -1; ...

I have written about other database migration frameworks before but in this article I’d like to cover the Liquibase framework in combination with WildFly as Java EE 7 compatible application server. In the following tutorial, we’re going to write a full Java EE 7 book store application with a few steps and with Liquibase on board to create the database structure and insert example data into the database. Thanks to the WildFly Maven Plug-in we even do not need to download and configure the application server but let Maven and the plug-in do the work for us. ...

When you need to derive meta-models from existing databases and want to create type-safe queries with an elegant, fluent-API, jOOQ definitely is a tool to consider here. In the following tutorial I’d like to demonstrate how to integrate the jOOQ meta-model generator into a Maven build using the jOOQ Maven Plug-in, the Build Helper Maven Plug-in and Maven profiles to finally create a running application to query an existing RDBMS using such a generated meta-model. ...

Precondition: Postgres Schema exists Using this precondition should run a migration only if the schema my_schema exists. <preConditions onFail="CONTINUE"> <sqlCheck expectedResult="1"> SELECT COUNT(1) FROM information_schema.schemata WHERE schema_name = 'my_schema'; </sqlCheck> </preConditions> Resources: https://docs.liquibase.com/concepts/changelogs/preconditions.html

Use List of Parameters in Annotation-based Query Possible using MyBatis Dynamic SQL feature package com.hascode.example.mybatis; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.Select; import java.util.List; @Mapper public interface SampleMapper { @Select({"<script>", "SELECT sample.bar", "FROM sampletable sample", "WHERE sample.id IN", "<foreach item='item' index='index' collection='ids'", "open='(' separator=',' close=')'>", "#{item}", "</foreach>", "</script>"}) List<Foo> getSamplesMatchingIds(@Param("ids") List<String> ids); } The mapper may now be used with a list of parameter objects: var samples = sampleMapper.getSamplesMatchingIds(List.of("24059e5b-aa07-424d-855e-50f499b8f697", "65140fc0-fc9f-42d2-9531-5e5d6caeba30")); Call a Procedure package com.hascode.example.mybatis; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Options; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.mapping.StatementType; @Mapper public interface SampleMapper { @Select("CALL SCHEMA.CL.setScope(#{scope})") @Options(statementType = StatementType.CALLABLE) void setScope(int scope); } ...

Get size of a table SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename')); Select unique combination of fields / tuples SELECT DISTINCT ON(field1, field2) field1, field2 FROM thetable Select rows where a combination of fields is not unique SELECT columnA, columnB, count(*) AS count FROM thetable GROUP BY columnA, columnB HAVING count(*) > 1 Search for rows with array containing value Assuming, the field appointments has the type date[] SELECT * FROM mtable WHERE appointments @> ARRAY['2023-09-19'::date] ...

Count unique / all values SELECT COUNT(DISTINCT <FIELDNAME>), COUNT(ALL <FIELDNAME>) FROM <TABLE>; Partially anonymize e-mail addresses UPDATE <TABLE_NAME> SET <EMAIL_FIELD>= INSERT( <EMAIL_FIELD>, POSITION('@' IN <EMAIL_FIELD>), 100, CONCAT(FLOOR(1 + (RAND() * 100)),'@hascode.com')) WHERE POSITION('@' IN <EMAIL_FIELD>)>0; Find duplicate entries SELECT COUNT(*), <FIELDNAME> FROM <TABLENAME> GROUP BY <FIELDNAME> HAVING COUNT(*)>1; MySQL Fix Zero Dates for Data Imports Error: ERROR 1067 (42000) at line 1234: Invalid default value for ‘datefield’ In newer MySQL Versions zero in date or zero dates are forbidden .. check this with: ...