Category Archives: Java

Return raw JSON from PostgreSQL query in Spring Controller

If you want to return raw JSON from a PostgreSQL query in a Spring Controller, you need to create your query like this.

Service method:

@Transactional(readOnly = true)
public String getRawJson() {
    String sql = "select json_agg(row_to_json(json)) from (select "
    + ", "
    + ", "
    + "(select array_to_json(array_agg(row_to_json(c))) from ( "
    + " ... some subselect ... "
    + ") c ) as subq "
    + "from person p "
    + "where type = :type "
    + ") json";

    MapSqlParameterSource params = new MapSqlParameterSource("type", 1);

    return jdbcTemplate.queryForObject(sql, params, String.class);


@GetMapping(value = "/json", produces = "application/json")
public String getRawJson() {
    return miscService.getRawJson();

What is actually important is to return the query result as String, to make sure there are no automatic conversion attempts made.

Installing OpenCms with PostgreSQL

This post explains you how to set up OpenCms with PostgreSQL on Ubuntu Linux.

In case you are not aware: PostgreSQL is an enterprise-grade open-source relational database system. IMHO it’s the most powerful and interesting open-source database, and the first choice if you do not need or want to use a commercial product.

I do not focus on installing Tomcat (or any other web container), nor PostgreSQL itself, but mainly on the database-related tasks you need to fulfill during the OpenCms set-up.

Versions used for this tutorial:

  • Ubuntu 12.10 / 64 bit
  • PostgreSQL 9.1.7
  • Java 1.7.0_09
  • Tomcat 6.0.36
  • OpenCms 8.5.0

Adding the setup-user

The OpenCms installer needs a user which can create users and databases. Here is how you can create it:

sudo -u postgres createuser
Enter name of role to add: opencmssetup
Shall the new role be a superuser? (y/n) y

Then we set a password for the newly created user:

sudo -u postgres psql
psql (9.1.7)
Type “help” for help.
postgres=# alter user opencmssetup with encrypted password 'opencmssetup';
postgres=# \q

Important: There is no need to change the password of the postgres Linux-system-user. In Ubuntu (and probably Debian as well) you execute the psql-command as user postgres through sudo.

Deploying the war-file

Next you should start Tomcat, and deploy the opencms.war file into the webapps-folder. After some seconds Tomcat will deploy it and you should see a folder named opencms.

Running the installer

Now you are ready to launch the installer at http://localhost:8080/opencms/setup

After going to the first two basic screens, you will launch the most important screen where you set up the database connection:


Now there are a few important things to note:

  • The user opencmssetup (Setup connection) is used only to create the database, plus the separate user for actually running OpenCms. The user opencmssetup will be deleted after installing.
  • The user opencms (OpenCms Connection) is the separate user which OpenCms uses to connect to its database. It is created by the installer.
  • The “Database Name”. The database is also created by the installer.

The database and the user can be named as you like, of course, as long as they don’t yet exist on the target database server.

When you press Continue, the OpenCms-installer will ask a few more questions about its setup. You can safely leave the defaults.

While the installer does its job, it’s important not to close the browser tab or window it’s running in. It can take quite some time since it “manually” imports the whole content into the OpenCms VFS. On my aging dual-core P8400-based notebook it took almost an hour. Note that I had a default-install of PostgreSQL. Probably it could be sped up by changing the fsync or synchronous_commit parameters.

Removing the setup-user again

Once the installer is done, you can remove the user opencmssetup, since it’s not needed to run OpenCms:

sudo -u postgres dropuser opencmssetup

If you have general questions about setting-up or configuring OpenCms, you can subscribe to the mailing list.

Scripting in Java

It can sometimes be useful to externalize some business logic from Java to a scripting environment which allows you to modify the logic at runtime. You can store the script in an external file or in database, and load it on each run. With little effort you can even add a code editor (e.g. using the excellent Editarea) to your application.

Meet javax.script.

A well-known implementation of javax.script. ScriptEngine is Mozilla Rhino.

Java 6 SE comes with Rhino 1.6r2 bundled, so you don’t even need to add any jar to your project.

The nice thing about Rhino is that you can access the full Java API from the JavaScript it executes, and you can inject objects from Java to JavaScript, and the operations you perform on those objects from inside the scripting, are directly reflected into the Java instances of the Object.

Let’s take a look at an example:

Java code

final Session session = HibernateUtil.getSession(); 

final Code code = (Code) session.get(Code.class, "1"); 

final ScriptEngineManager manager = new ScriptEngineManager(); 
final ScriptEngine jsEngine = manager.getEngineByName(code.getType());

final Employee employee = new Employee();

jsEngine.put("employee", employee); 

Integer result = (Integer) jsEngine.eval(code.getCode()); 


JavaScript code (from code.getCode())


function main() { 
    employee.setName("JavaScript is my Name"); 
    employee.setName(employee.getName()+" !!!"); 
    return 1+2+3+4+5; 

This illustrates on how to load some JavaScript from a database, and have it executed by the ScriptEngine. The JavaScript can directly call the setter methods on the injected Employee object. The return code from the function main() is stored in the result variable.

By the way, you are not bound to use JavaScript. There are several JSR-223 script implementations, e.g. Groovy, Python, Ruby, Scheme, PHP, …

I think this can be really useful and I will certainly use it in some of my projects in the near future.