Category Archives: JavaRanch

postgresql and jdbc

Posted on by
1

In postgresql – selecting maximum for each group, we saw the actual stored function.  That was the easy part.  Then there was dealing with Postgresql and JDBC.  I encountered a few surprises while doing this.

Calling the stored function

I expected to use JDBC’s CallableStatement since I was calling a stored procedure.  Nope.  The proper way to call it is:


PreparedStatement stmt = conn.prepareStatement("select * from highlighted_topic_per_forum(?) limit ?");

stmt.setString(1, formattedDate);
stmtp.setInt(2, limit);

Passing a timestamp

Originally, I was passing a timestamp into the stored function by calling stmt.setTimestamp() and a type of “timestamp without time zone”.  Try as I may, postgresql didn’t like this.  It complains $1 not found.  I eventually decided to pass the timestamp as a string.

Passing an array

I was originally planning to pass the category ids via JDBC.  Given what what I experienced with timestamps, I wasn’t so enthusiastic about trying this on an application I work on in my free time.  When I saw the advice online to extend the Array class, I decided to hard code the two category ids.

postgresql – selecting maximum for each group

Posted on by
3

I had a requirement to return the thread with the most replies in each forum at JavaRanch‘s Coderanch forums.  In Postgresql 8.4, this would be very easy – just use the window functions.  Unfortunately, we aren’t on Postgresql 8.4 yet.  The other common pattern is something like

select stuff
from mytable t1
where date = (select max(date)
from mytable t2
where t1.key= t2.key)

This doesn’t work well for me either because the value I am comparing (number of posts is dynamic.)  I decided to use a stored procedure to “simplify” things.  I’ve written some stored procedures in Postgresql to do updates before and stored procedures in other languages to do queries so this didn’t seem like a huge task.

Postgresql calls them stored functions, so let’s proceed.  First you need to create a type to represent a row that gets returned by the stored function.

CREATE TYPE highlighted_topic_per_forum_holder AS
(last_user_id integer,
post_time timestamp without time zone,
<other columns go here>);

Then you create the stored procedure.  The outer loop goes through each forum.  The inner loop is the SQL that finds the post with the most replies that was posted to in some time period.  It uses a nested query with a limit to return only 1 thread per forum.  The rest of the SQL adds the relevant data.  See postgresql and JDBC for why it takes varchar rather than timestamp.

CREATE or replace FUNCTION highlighted_topic_per_forum(character varying)
RETURNS SETOF highlighted_topic_per_forum_holder AS
$BODY$
DECLARE
forum RECORD;
r highlighted_topic_per_forum_holder%rowtype;
BEGIN
for forum in EXECUTE 'select forum_id from jforum_forums where categories_id in (1,7)' loop
for r in EXECUTE 'select p.user_id AS last_user_id, p.post_time, p.attach AS attach, t.* '
|| 'from jforum_topics t, jforum_posts p, '
|| '(select topic_id, count(*) from jforum_posts '
|| ' where post_time >= date '' ' || $1 || ' '' '
|| ' and forum_id = ' || forum.forum_id
|| ' AND need_moderate = 0 '
|| ' group by topic_id order by 2 desc limit 1) nested '
|| ' where p.topic_id = nested.topic_id '
|| ' and p.post_id = t.topic_last_post_id '
|| ' order by post_time desc' loop
return next r;
end loop;
end loop;
return;
END;
$BODY$
LANGUAGE 'plpgsql';

process builder to run multi-platform postgres import

Posted on by
Reply

We saw how to clone a postgresql database for testing cleanly in an earlier blog post. The next step is to do so from Java so it can be part of an integration test.

How to run each of 4 DDLs:
Running a DDL file is easy which is good since we have four of them.

importDdlOrSqlFile("initialJForumSchema.ddl");
importDdlOrSqlFile("javaranchCustom.ddl");
importDdlOrSqlFile("users.ddl");
importDdlOrSqlFile("databaseChangesAfterMostRecentInstall.txt");

Ok. So maybe it is easy because all the functional code is in another method.

What is Process Builder?

Process Builder was introduced in Java 5.  The Runtime.exec JavaDoc says

ProcessBuilder.start() is now the preferred way to start a process with a modified environment.

Since I need to set two system properties, this is perfect.

How to create the Process Builder

On Windows, you just pass the command to run at the DOS prompt.  The actual command was shown in the closing a postgresql database blog post.  On UNIX/Mac/etc, you need to create a new shell and pass the command that way.  Luckily, it is easy to check for a Windows operating system:


private ProcessBuilder createProcessBuilder(String command) {
String os = System.getProperty("os.name").toLowerCase();
if (os.contains("win")) {
return new ProcessBuilder(command);
}
return new ProcessBuilder("sh", "-c", command);
}

How to set system properties

I need to set the user id and password as operating system properties so postgresql doesn’t prompt for them interactively.  Using process builder, I can call environment() to get a map of environmental properties for my process.  Then setting them is simply a matter of setting them in the map.


private void importDdlOrSqlFile(String fileName) throws Exception {
String command = importToPostgresCommand(fileName);
System.out.println(command);
ProcessBuilder processBuilder = createProcessBuilder(command);
Map<String, String> env = processBuilder.environment();
env.put("PGUSER",   SystemGlobals.getValue(DATABASE_CONNECTION_USERNAME));
env.put("PGPASSWORD",  SystemGlobals.getValue(DATABASE_CONNECTION_PASSWORD));
processBuilder.redirectErrorStream(true);
runProcess(fileName, processBuilder);
}

How to run the Process Builder

All ready to run it!   start() kicks it off.  I think read all the output from the commands via the process builder’s input stream and output it to the console.  (I do this since many commands are run and the developer can see what it is up to.)  Finally, I check the error code.  After all, if the database didn’t get created properly, there isn’t much point in having a whole pile of failing tests.


private void runProcess(String fileName, ProcessBuilder processBuilder) throws IOException, InterruptedException {
Process proc = processBuilder.start();
Scanner scanner = new Scanner(proc.getInputStream());
while (scanner.hasNext()) {
String line = scanner.nextLine();
System.out.println("importing data: " + line);
}
int exitVal = proc.waitFor();
System.out.println("Completed loading " + fileName + " Exit value: " + exitVal);
if (exitVal != 0) {
System.out.println("Please fix error in database script and re-run.");
System.exit(1);
}

}

Testing

This code was tested on multiple developer’s machines including Windows and Mac.