Running Native kdb Queries in Spring JDBC
In my last post on accessing a kdb+ server from spring, we configured Spring to listen to a kdb+ gateway via jdbc. One drawback of using jdbc is queries will now have to be written as single-line sql strings to match the type of JdbcTemplate query arguments. This strips away some advantages
c.java style querying has. In this post we’ll go over a way to use the native kdb executor method in spring jdbc so that we can use
c.java style queries and at the same time retain some advantages of jdbc, such as, simple connection configuration, effortless connection management etc.
Let’s take an example. In a trading system, quotes data is periodically updated with new of snapshots received from exchanges. Let’s make a barebones kdb quote store server. We will define a simple bid-ask quote table and a
upd function to insert list of records into it. The update time will also be recorded along with the new values.
First let’s start the server. The java client will connect to it over TCP port 7000.
Poking inside the JDBC Class
Within the code of jdbc.jar you will find that the connection to kdb gateway is handled by the sub-class
co and in that class there is pretty interesting method which is actually managing executing
JdbcTemplte’s query calls.
h is a handle of a kdb gateway
ex(String arg0,Object arg1) is essentially equivalent to running the following kdb command:
To run a kdb function using
ex you need to pass the function’s name to
arg0, and all the required arguments for that function as a list to
arg1. For example, arguments for
ex to run the q expression
count (`SYM;99.0;101.0) is:
Extracting the underlying
co object from jdbcTemplate
org.apache.tomcat.jdbc.pool.PooledConnection to manage connections. So we need to get a connection from the pool and then extract the
co connection object within. Be careful to use try-with-resources with the pooled connection, because then at the end of the statement the underlying kdb connection will be returned to the pool and it will be reused in the next loops.
I am using the jdbc.jar from kx site, in which, for some reason, the
jdbc class resides in a default package. That means we cannot import
jdbc with an import statement. We’ll have to use reflection to get a handle to the
ex method and invoke that. The class
co is inside
jdbc class, so the fully qualified name for it would be
Spring Java Clent
Maven configurations are same as the previous post. In
application.properties we need to add tomcat datapool specific configurations.
We’ll make an CLI application by implementing
We are going to connect to the gateway and use the
upd function defined in
server.q to insert records into quote. For this example, I’ll create dummy datafeed using a list of price and stocks (example below) and randomizing it a bit to create bid/ask columns.
Here is the complete code incorporating all the things we have discussed:
Open the project in IntelliJ IDEA/Eclipse. Add this jar to your build path and run the client.
Now if you query quote you should see new data coming in every second
~/Workspace/sping-kdb-native/q $ rlwrap q server.q -p 7000 KDB+ 3.4 2016.12.08 Copyright (C) 1993-2016 Kx Systems m32/ 4()core 8192MB subha tuchanka.local 192.168.0.101 NONEXPIRE q)quote time sym bid ask -------------------------------- 20:19:51.463 CERN 52.93 53.93 20:19:51.463 VMW 87.76 90.76 20:19:51.463 GOOGL 829.88 829.88 20:19:51.463 AAPL 132.04 132.04 20:19:51.463 YHOO 44.07 45.07 20:19:51.463 ADI 77.48 78.48 20:19:51.463 BIDU 181.5 181.5 20:19:51.463 INFO 38.71 40.71 20:19:51.463 ITW 127.12 128.12 20:19:51.463 ADP 95.58 96.58 20:19:51.463 STM 13.94 14.94 20:19:51.463 CHKP 99.56 100.56 20:19:51.463 PNR 57.96 58.96 20:19:51.463 STX 44.8 46.8 20:19:51.463 WDC 76.08 78.08 20:19:51.463 XLNX 58.23 59.23 20:19:51.463 AMD 13.56 14.56 20:19:51.463 CTSH 56.45 57.45 20:19:51.463 DVMT 64.84 65.84 20:19:51.463 INFY 13.11 15.11 .. q)count quote 192225 q)
You can find the complete project here.