Two popular open source components used in full stack development are Spring and Postgres. Spring gives the developer a plethora of opportunities to integrate with third party ORM libraries, such as Hibernate and iBatis. Frequently, rather than use a separate ORM library, developers elect to use the Spring JDBC Template found in the Spring-JDBC library.
We use Spring JDBC Template with Postgres JSON data types to store an undefined number and type of audit parameters for the systems data auditing capabilities. As we were developing this system, we ran into a problem in the way that Spring JDBC Template works with the Postgres JSON type. This post discusses that problem and how we were able to resolve it.
Note that the Spring JDBC Template has two beans that are used for SQL CRUD, “JdbcTemplate”, and “NamedParameterJdbcTemplate”. In this post, we’re discussing the NamedParameterJdbcTemplate. We will call it JDBCTemplate for the sake of brevity.
What is Spring JDBC Template?
Spring JDBC Template is a native spring framework that allows the developer to create SQL Queries for CRUD operations. It takes care of SQL injections and other security issues. It consists primarily of a parameterized SQL statement, a parameter map, and a row mapper. The SQL statement is a parameterize String within the repository.
SELECT * FROM report.report WHERE report_id = :report_id;
The parameter map is a java map data structure consisting of a string key and the object being inserted.
Map<String, Object>; // "report_id" -> 1
The row mapper is the routine to map the database results to a new object.
For more information on Spring JDBC Template please visit http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html
What is Postgres JSON?
The JSON type is a Postgres column type that allows for JSON data constructs to be saved in the database. It ensures that the data conforms to valid JSON rules. More information can be found at https://www.postgresql.org/docs/9.4/static/datatype-json.html.
Storing JSON in Postgres will likely not be useful in most situations, but specific cases appear where it is useful. One such case is our example below. In this case we have a generic reporting framework for database auditing. The framework supports multiple reports, with different parameters for the audit. Each report will have a name and variable list of parameters of different types.
Project Setup
Assume that we have been tasked to insert and get report configurations. The Report has a name and can have any length of key values. For this, we choose to store the report parameters as JSON in a table called ‘report’.
Assumptions:
Postgres is installed and a database called ‘jsontest’ has created SQL. The following SQL was used to create the schema and table.
CREATE TABLE jsontest.report (
report_id SERIAL PRIMARY KEY NOT NULL,
name TEXT NOT NUll,
parameters json
);
-- Inserts sample data into the table
INSERT INTO jsontest.report (name, parameters)
VALUES ('Test 1', '{"param1" : "ABBA"}') ;
Notice the ‘parameters’ column type is JSON
Project Setup
The project consists of a maven build file, a spring configuration POJO, a Report domain object, a Row Mapper that converts database data into the domain object, and a database repository that actually performs the CRUD operations. There is also a Main file that can be executed from the command line. The project source can be found at https://github.com/djchi82/PostgresSpringTemplateBlog
pom.xml
The maven build file contains the application’s build details and dependencies, such as:
- Apache Commons Lang 3
- Apache Commons Collections
- Apache Commons DBCP2
- Postgresql database driver
- Spring Framework
The POM can be found in the github source at https://github.com/djchi82/PostgresSpringTemplateBlog/blob/master/pom.xml
Report Domain Object
The Report Domain Objects reflects the report database table and is used by the application.
package com.tenmilesquare.vanfleet.blog;
/**
* Report Domain for example
*/
public class Report {
private String reportParameters;
private Long id;
private String name;
/** Simple class constructor */
public Report() {
super();
}
/**
* Class constructor
* @param name Name
* @param reportParameters json as a string
*/
public Report(String name, String reportParameters) {
this.name = name;
this.reportParameters = reportParameters;
}
public String getReportParameters() {
return reportParameters;
}
public void setReportParameters(String reportParameters) {
this.reportParameters = reportParameters;
}
/*
... Accessor methods here
*/
@Override
public String toString() {
return "Report{" +
"reportParameters='" + reportParameters + '\'' +
", id=" + id +
", name='" + name + '\'' +
'}';
}
}
Spring Config
The Spring configuration file sets up the spring application. In this case we are telling Spring what packages to scan and how to inject the data sources.
package com.tenmilesquare.vanfleet.blog;
/* Import Statements */
/**
* Spring application context config
*/
@Configuration
@ComponentScan(basePackages = {"com.tenmilesquare.vanfleet.blog"})
public class Config {
/**
* DataSource for working with the publishing database.
* @return DataSource
*/
@Bean
public DataSource dataSource() {
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName("org.postgresql.Driver");
dataSource.setUrl("jdbc:postgresql://localhost:5432/jsontest");
dataSource.setUsername("postgres");
dataSource.setPassword("postgres");
dataSource.setValidationQuery("SELECT version()");
dataSource.setAccessToUnderlyingConnectionAllowed(true);
return dataSource;
}
/**
* NamedParameterJdbcTemplate for working with named parameter SQL with the publishing database.
* @return NamedParameterJdbcTemplate
*/
@Bean
public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
return new NamedParameterJdbcTemplate(dataSource());
}
/**
* JdbcTemplate for working with SQL with the publishing database.
* @return JdbcTemplate
*/
@Bean
public JdbcTemplate jdbcTemplate() {
return new JdbcTemplate(dataSource());
}
}
RowMapper
The row mapper is used by spring template to map the SQL result set to the report domain object.
package com.tenmilesquare.vanfleet.blog;
/* Import statements */
/**
* Singleton that maps a Database result set to a domain object
*/
public final class ReportMapper implements RowMapper<Report> {
/*
...
Singleton set and accessor code
...
*/
/**
* Maps the report object from the result set
* @param rs The sql result set
* @param i The row number
* @return A report object
* @throws SQLException Thrown if there was a SQL issue during mapping
*/
public Report mapRow(ResultSet rs, int i) throws SQLException {
Report report = new Report();
report.setId(rs.getLong("report_id"));
report.setName(rs.getString("name"));
report.setReportParameters(rs.getString("parameters"));
return report;
}
}
Repository
The repository is the database access object that will create and get our reports from the database table.
package com.tenmilesquare.vanfleet.blog;
/* Import Statements */
/**
* The Database Repository class that will insert and fetch a report.
*/
@Repository
public class ReportRepo {
@Autowired
private NamedParameterJdbcTemplate jdbcTemplate;
private final static String SQL_GET = "SELECT * FROM jsontest.report WHERE report_id = :report_id";
/**
* Retrieves a report entity by its database id
* @param id the database id
* @return returns the associated report if it exists, Null otherwise
*/
public Report getReportById(Long id){
final Map<String, Object> params = new HashMap<String, Object>();
params.put("report_id", id);
List<Report> reports = jdbcTemplate.query(SQL_GET, params, ReportMapper.getMAPPER());
if(CollectionUtils.isEmpty(reports)){
return null;
}
//Assume that the fist element is the one we are seeking.
return reports.get(0);
}
private final static String INSERT_SQL_CAST_EXC = "INSERT INTO jsontest.report (name, parameters) VALUES (:name, :parameters) RETURNING report_id";
/**
* Insert report will throw Casting exception if JSON is supplied
* @param report the report
* @return the database id.
*/
public Long insertReportCastException(Report report){
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("name", report.getName());
parameters.put("parameters", report.getReportParameters());
return jdbcTemplate.queryForObject(INSERT_SQL_CAST_EXC, parameters, Long.class);
}
private final static String INSERT_SQL_CASTED = "INSERT INTO jsontest.report (name, parameters) VALUES (:name, cast(:parameters AS JSON)) RETURNING report_id";
/**
* Insert report. Will work, because we have casted the string to json
* @param report the report
* @return the database id.
*/
public Long insertReportCasted(Report report){
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("name", report.getName());
parameters.put("parameters", report.getReportParameters());
return jdbcTemplate.queryForObject(INSERT_SQL_CASTED, parameters, Long.class);
}
}
Main
The Main class is how we will execute the application.
package com.tenmilesquare.vanfleet.blog;
/* Import Statements */
/**
* Main class for the application example
*
*/
public class Main {
public static void main(String[] args){
ApplicationContext context = new AnnotationConfigApplicationContext(Config.class);
ReportRepo repo = context.getBean(ReportRepo.class);
Report report1 = repo.getReportById(1L);
System.out.println("Report1: " + report1.toString());
Report report2 = new Report("Report2", "{\"param1\": \"Rolling Stones\"}");
try {
Long report2Id = repo.insertReportCastException(report2);
System.out.println(report2Id);
} catch (Exception e){
e.printStackTrace();
}
// Now let try casting the String to JSON
Long report2Id = repo.insertReportCasted(report2);
System.out.println("Report2: " + repo.getReportById(report2Id));
}
}
Execution
Let take a look at the Main class. After instantiating the Spring Application Context and retrieving the repository, we get the manually inserted Report and print it to console. Next, we try to insert a new Report object using a standard insert statement:
INSERT INTO jsontest.report (name, parameters) VALUES (:name, :parameters) RETURNING report_id;
This will cause a BadSqlGrammarException with the following message:
column "parameters" is of type json but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
The next insert statement uses casting to let Postgres know to cast the string as JSON type. This will succeed.
INSERT INTO jsontest.report (name, parameters) VALUES (:name, cast(:parameters AS JSON)) RETURNING report_id
If we run this application, we will see the following in the console:
Jun 02, 2016 11:16:31 AM org.springframework.context.annotation.AnnotationConfigApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@49c2faae: startup date [Thu Jun 02 11:16:31 EDT 2016]; root of context hierarchy
Report1: Report{reportParameters='{"param1" : "ABBA"}', id=1, name='Test 1'}
Jun 02, 2016 11:16:33 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
Jun 02, 2016 11:16:33 AM org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO jsontest.report (name, parameters) VALUES (?, ?) RETURNING report_id]; nested exception is org.postgresql.util.PSQLException: ERROR: column "parameters" is of type json but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 60
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:695)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:211)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:219)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:233)
at com.tenmilesquare.vanfleet.blog.ReportRepo.insertReportCastException(ReportRepo.java:48)
at com.tenmilesquare.vanfleet.blog.Main.main(Main.java:21)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)
Caused by: org.postgresql.util.PSQLException: ERROR: column "parameters" is of type json but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 60
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:703)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
... 13 more
Report2: Report{reportParameters='{"param1": "Rolling Stones"}', id=3, name='Report2'}
Process finished with exit code 0
Notice that, without casting the Named Parameter in the insert statement, Postgres will reject the Spring Template Insert statement.
