Category Archives: Development

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 "
    + "p.id, "
    + "p.name, "
    + "(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);
}

Controller:

@ResponseBody
@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.

What are you doing anyway the whole day staring at your screen?

Here is a handy script which logs the current timestamp and the title of the currently focused window in an SQLite3 database.

It does not insert a row if the current window title is the same than on the previous run.

You can run it by cron every minute, and will help you to find out where you spent most time 🙂

#!/bin/bash

# sqlite3 ~.windowlog.db
# CREATE TABLE windowlog(moment integer not null primary key, window);
# echo "select * from windowlog order by moment desc;" | sqlite3 ~.windowlog.db

: ${DISPLAY:=:0}
export DISPLAY

WIN_ID=`xprop -root | grep "_NET_ACTIVE_WINDOW(WINDOW)"| cut -d ' ' -f 5`
WIN_NAME=`xwininfo -id $WIN_ID |awk 'BEGIN {FS="\""}/xwininfo: Window id/{print $2}' | sed 's/-[^-]*$//g'`
OLD_WIN_NAME=`echo "select window from windowlog order by moment desc limit 1;" | sqlite3 ~.windowlog.db`

if [ "$WIN_NAME" != "$OLD_WIN_NAME" ]; then
        #echo "$WIN_NAME ----- $OLD_WIN_NAME"
        timestamp=`date +%s`
        #echo `date +%s`,$WIN_NAME >> ~.windowlog
        sqlite3 ~.windowlog.db "insert into windowlog (moment,window) values ('$timestamp','$WIN_NAME');"
fi

Before running it you need to create the database and the table:

$ sqlite3 ~.windowlog.db
sqlite> CREATE TABLE windowlog(moment integer not null primary key, window);

Edit: This code is now living in github, and being greatly improved. Thanks to flazzarini!

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(); 
session.getTransaction().begin(); 

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()); 

session.close();

JavaScript code (from code.getCode())

main(); 

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.