Travel notes, or the taste of coffee for elephants

Have you already guessed what the article will be about?

For the third year I have been developing a large system in Java using the PostgreSQL DBMS. The system is desktop, client-server. We don't have an experienced Senior-Java-Developer, so we have to think for ourselves. To think, to build, to break, to build again, to break again ...
During my work, I have gained some experience both in organizing work directly from the database, and in interconnecting these platforms, which I want to talk about in this article.
')
I will selectively describe some of the issues that we encountered in the development and which were solved.

1. Using ORM

The principles used in Hibernate, as well as the fact that almost every job of a Java developer requires knowledge of Hibernate, caused an obsession with me to introduce this technology into our system.

However, we work with vector graphics, and it turned out to be extremely inconvenient to make vector objects as Persistence objects, with appropriate setters and getters. Vector objects are complex, with many logical properties (ie, not only graphics, but also logic). I never thought up how to map them to Hibernate.
But he invented his own structure for describing the logical properties of objects in XML, where for each property there is a table and a database column, and algorithms that form dynamic SQL queries on this basis.
So - we do not use ORM. We work through JDBC and use our own XML-mapping.
And dependence on the DBMS is still there, because without stored procedures can not do (!!!).

2. Organization of connections to the database

PostgreSQL JDBC driver, java.sql.Connection, and forward - in each class, opening and closing connections.

So it was the first couple of months.
Today we have one class - connection manager, very simple. It stores the object itself - the connection (one for the whole project!) And ensures that this connection to the database is not used at the same time, especially in several threads, because it is not safe.

3. Organization of SQL queries in the project.

At the beginning of development, in every second class, SQL queries could be encountered. They worked great.

But the database was changing in parallel, and at some point it took to take and rename the table. Rename something simple, but what about the project? Refactoring? But no, you can rename all references to the table in the source code only with the help of contextual replacement by project. But the medium does not know where you have the name of the table, and where the variable of the same name ...
After it took 2 days to rename a table in the database, we decided to make a separate class containing all SQL queries to the database, either in the form of constants or in the form of methods. All constants are named according to the same principle "<query_type> _ <class name> _ <query meaning>". For example, the constant on the selection of graphic lines, called in the class Graph, called SELECT_GRAPH_LINES . If it were a method, it would be called selectGraphLines () . Now we have easily replaceable and beautiful code with constants instead of sql-text.

4. Optimization of execution of SQL queries.

I'll tell you not how to configure PostgreSQL. And about the well-known JDBC method - PreparedStatement - executeBatch ();

If we talk about queries for insertion, the question arises: how to quickly insert into the table immediately many rows?
PostgreSQL supports multiinsert, i.e. you can write something like

insert into table (a, b, c) values ( (1,2,3), (4,5,6), (...) ) 

So, it is not good to form multincertes dynamically. It’s enough to write once

 PreparedStatement stmt = db.prepareStatement("insert into table (a, b, c) values (?, ?, ?)"). 

and then in the loop do

 stmt.setString(1,a); stmt.setString(2,b); stmt.setString(3,c); stmt.addBatch(); 

and then do

 stmt.executeBatch() 

and all the data will be inserted for you in an extremely short time.
For example, inserting data from 1600 xml files of ~ 10 kB using simple INSERT took more than 10 minutes. Paste using executeBatch is about 2 seconds. Therefore, it is desirable to apply such a scheme wherever many insert queries of the same type are used.

5. Working with XPath in PostgreSQL

Faced a problem, the answer to which in google is hard to find, and from the PostgreSQL documentation it is not clear.
It is given: the table in a DB with a field of type xml.
Required: make a selection of all records from the table, where some XML element has such and such value.

It's clear that the simplest thing is to do it through XPath. We write:

 SELECT xpath('//child/child/text()', < XML->) from  

We see that all our records are empty. After searching, we understand that the XML data is recorded with a so-called. default namespace, i.e. all XML tags are written simply:

 <xmltag></xmltag> 

but not

 <ns:xmltag></ns:xmltag> 

It took a lot of time to understand that you need to register the namespace by default, and do it like this:

 SELECT xpath('//my:root/my:child/text()', < XML->, ARRAY[ARRAY['my', 'http://example.com']]); 

where my is any word to your taste - namespace name http: / /example.com is the value of xmlns :

 xmlns="http://example.com"/ 

And you definitely need the word my to precede all the elements in the xpath request, but the attributes are not necessary.

6. PostgreSQL text editors and stored procedures

In the Netbeans SQL editor, this is the code

 CREATE FUNCTION AAA (a int, b varchar) RETURNS int AS $body$ BEGIN ... END $body$ LANGUAGE plpgsql; 

will cause an error. The same will happen in SQL Workbench and in Eclipse. And in many applications where the JDBC driver is used to execute SQL queries.

And the problem in $ body $ is the so-called dollar-quoting, which is used in postgres to denote the body of functions. $ body $ opens the function body and closes it.
There are no problems in pgAdmin. In SQL Workbench, this case is specially processed, but crutches are used: it is required to mark the end of the body of the stored procedure in the editor with a special symbol - alternate delimiter, the default is /.

And in Netbeans - does not work. And in Eclipse too.

PS
Of course, there were many more problems and questions in three years. This is what I remembered and what I wanted to share.
This article is not an authoritative statement “do it this way.” It will be interesting to see the criticism of how we solved the problems described in the article.