7 tips to improve data quality using database constraints

When it comes to validating application's data, most Java developers think about frontend/backend validation, JSR 303 etc. Sadly, many ignore the database, even though it allows some easy to use, declarative validations.


The number one mistake when using Hibernate

Hibernate is one of the most popular ORM solutions for Java, yet many developers are surprised when they find out that it is state based not SQL based framework.

This means that when you call Hibernate methods like session.save(entity), session.update(entity) etc. it does not go directly to the DB to issue an INSERT/UPDATE etc. but only associates the object passed as an argument with its session and assigns some internal state to it.

At a later point in time (typically before transaction commits) Hibernate performs a flush - it synchronizes the state of objects held in memory with the database, executing any number of INSERT/UPDATE/DELETE statements.

Hibernate is very easy to use. IMHO it is even too easy to use, because it makes an impression, that it is a simple framework. It's definitely not, there is a lot of complexity under the hood and if you ignore it, you can easily shoot yourself in the foot.


Writing more testable code - a simple trick

While writing tests for service layer you can:
  • create unit tests with mocked dependencies
  • create integration tests with real dependencies (typically - DB)
Both solutions have their pros and cons and neither is 100% convenient. This post shows an alternative approach.


Virtual Private Database and how to integrate it with a Java application

Virtual Private Database (VPD) allows you to implement row and column level security in your database by "hiding" data in tables, so that only a subset of all table data is accessible. The cool things is that the "hiding" can be done based on your application user's identity or roles, so that privileged users can access more data than normal users - and it is all managed at database level!


How to draw a tree using window functions and recursive query in PostgreSQL

In this post I would like to show you how to draw a nice looking tree (similar to output of unix tree command) using select statement in PostgreSQL.

Suppose we have a view v_deps, which contains information about dependencies between views, materialized views and tables (the definition of this view is at the end of the post). Every row in this view means that dependent object dep_obj depends on referenced object ref_obj:


PostgreSQL: How to handle table and view dependencies

PostgreSQL is very restrictive when it comes to modyfing existing objects. Very often when you try to ALTER TABLE or REPLACE VIEW it tells you that you cannot do it, because there's another object (typically a view or materialized view), which depends on the one you want to modify. It seems that the only solution is to DROP dependent objects, make desired changes to the target object and then recreate dropped objects.


DB view dependencies in PostgreSQL


I recently learned a clever trick in PostgreSQL, which allows you to check which DB views and tables are used by any view. It allows you to see view's dependency tree, for example:
public.v_top_department depends on public.v_department_sales_per_employee
  public.v_department_sales_per_employee depends on public.v_department_sales
    public.v_department_sales depends on public.departments
    public.v_department_sales depends on public.sales
  public.v_department_sales_per_employee depends on public.v_department_employees
    public.v_department_employees depends on public.departments
    public.v_department_employees depends on public.employees