Travel notes, or the taste of coffee for elephants
guessed it, what's the article?
The third year develop large systems in Java using PostgreSQL database. System of desktop, client-server. Experienced Senior-Java-Developer-and we have not, so you have to think for themselves. Think, build, break, rebuild, break again...
During work has accumulated some experience of organization directly work with databases and the integration of these platforms, of which I want to tell you in this article.
Describe selectively some of the issues we faced during development and which have decided.
1. Using ORM
The principles used in Hibernate, and also that almost in each vacancy Java developer required knowledge of Hibernate, I have caused obsession to implement this technology in our system.
However, we work with vector graphics, and was extremely uncomfortable vector objects to do the Persistence objects with the corresponding setters and getters. Vector objects are complex, with lots of logical properties (i.e. not only graphics, but also logic). As their muppet in Hibernate — that's what I came up with.
Instead, come up with its structure describe the logical properties of objects in XML, where for each property specified table and column databases, and algorithms based on this form of dynamic SQL queries.
So — we do not use ORM. Work through JDBC and use XML mapping their own development.
And the dependence on the DBMS still there, because without stored procedures do not (!!!).
2. The organization of connections to the database
PostgreSQL JDBC driver, java.sql.Connection, and forward — in every classroom opening and closing the connection.
So was the first couple of months.
Today we have one Manager class the connection, very simple. It stores the connection object (one for the entire project!) and ensures that the connection to the database is not used at the same time, especially multiple streams, since it is unsafe.
3. The organization of SQL queries in the project.
In the beginning of the development in each second grade, it was possible to meet SQL queries. They worked great.
But DB has varied in parallel, and at some point need to take and rename the table. Rename easy, but what about the project? Refactoring? But no, to rename all references to the table in the source code only by using shortcut replacement project. But the environment doesn't know where you got the table name, and where the variable...
After renaming the table in the database it took 2 days, we decided to make a separate class that contains all SQL queries to the database either as constants or methods. All constants are named on the same principle "<tiptaps>_<class name>_<the meaning of the query>". For example, the constant on the sample graphic lines, called class Graph, called SELECT_GRAPH_LINES. If it were a method, then it would be called selectGraphLines(). Now we have an easily replaceable and beautiful code with constants instead of sql text.
4. Optimizing the SQL queries.
Does not explain how to configure PostgreSQL. But the known method JDBC — PreparedStatement — executeBatch();
If we talk about the queries for inserting, then the question arises: how to quickly insert into the table from a lot of lines?
PostgreSQL supports multiinsert, i.e. you can write something like
the
insert into table (a, b, c)
values (
(1,2,3),
(4,5,6),
(...)
)
Now, to form multientity dynamically is not good. Once is enough to write
the
PreparedStatement stmt = db.prepareStatement("insert into table (a, b, c) values (?, ?, ?)").
and then in a loop do
the
stmt.setString(1,a);
stmt.setString(2,b);
stmt.setString(3,c);
stmt.addBatch();
and then make
the
stmt.executeBatch()
and all data is inserted you in a very short time.
For example, the 1600 insert data from an xml file at ~10 KB simple INSERT took more than 10 minutes. Insert using executeBatch — about 2 s. Therefore, to apply such a scheme is desirable wherever used many similar queries in the box.
5. Working with XPath in PostgreSQL
Faced with a problem whose answer to google hard to find, and from the PostgreSQL documentation is unclear.
Given: a table in a database field of type xml.
Required: make a selection of all records from table where some XML element has the value this.
It's clear that the simplest way to do this using XPath. Write:
the
SELECT xpath('//child/child/text()', <name XML field>) from table
See that all records have an empty. After searching we understand that the XML data is written with the so-called default namespace, i.e. all XML tags are written just:
<xmltag>blablabla</xmltag>
<ns:xmltag>blablabla</ns:xmltag>
Took a long time to understand that you need to register the namespace by default, and to do it like this:
the
SELECT xpath('//my:root/my:child/text()', <name XML field>, ARRAY[ARRAY['my', 'http://example.com']]);
where my is any word on your taste — the name of the namespace http:/ /example.com — xmlns:
the
xmlns="http://example.com"/
And be sure to have the word my to Preface all elements in the xpath query, but the attributes is not necessary.
6. Text editors and stored procedures PostgreSQL
In the SQL editor of Netbeans code like this
the
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 yet in many applications, where the execution of the SQL queries used by JDBC driver.
But the problem is $body$ — so-called dollar-quoting, which is used in postgres to denote body functions. $body$ opens the body of the function and closes it.
In pgAdmin there is no problem. In SQL Workbench specially handled this case, but used crutches: required to indicate the end of the body of the stored procedure in editor special character — alternate delimiter, the default is /.
And in Netbeans is not working. And in Eclipse too.
PS
Problems and issues in three years of course had a lot more. That's what I remembered and what I wanted to share.
This article is not an authoritative statement "it is necessary to do so." It will be interesting to see criticism of how we've solved this in the article task.
