HOWTO Build a health database and FHIR API Server in 15 mins using open source

Thank you for the reply! I appreciate it.

Hi Kev,
Thank you for your post. I am new to maven. I followed JPA server setup. I am getting the error when installing maven using the command “mvn install” .
Could you please explain it?

I think you’ve used the older repository, the latest one is here

But I think the problem was with maven repositories and may work if you try again.

1 Like

@mayfield.g.kev,

Thanks for your solution. Please note that the following worked for me in getting MySQL hooked up:

	private Properties jpaProperties() {
		Properties extraProperties = new Properties();
		//extraProperties.put("hibernate.dialect",  org.hibernate.dialect.DerbyTenSevenDialect.class.getName()); // CHANGE 1
		extraProperties.put("hibernate.format_sql", "true");
		extraProperties.put("hibernate.show_sql", "false");
		extraProperties.put("hibernate.hbm2ddl.auto", "create"); // CHANGE 2
		extraProperties.put("hibernate.jdbc.batch_size", "20");
		extraProperties.put("hibernate.cache.use_query_cache", "false");
		extraProperties.put("hibernate.cache.use_second_level_cache", "false");
		extraProperties.put("hibernate.cache.use_structured_entries", "false");
		extraProperties.put("hibernate.cache.use_minimal_puts", "false");
		extraProperties.put("hibernate.search.default.directory_provider", "filesystem");
		extraProperties.put("hibernate.search.default.indexBase", "target/lucenefiles");
		extraProperties.put("hibernate.search.lucene_version", "LUCENE_CURRENT");
//		extraProperties.put("hibernate.search.default.worker.execution", "async");
		return extraProperties;
	}
  • CHANGE 1 is that this had to be commented out.
  • CHANGE 2 is that this had to be tweaked so that the database tables would be created.

Thanks,
Matthew Vita

Thanks.

I’ve changed the code slightly, your first change is now a configuration setting. Maybe the second change should be one also?

I’ve been using this repo recently https://github.com/nhsconnect/careconnect-java-examples/tree/master/careconnect-hapi-jpa-dstu2-springboot Thats aimed at running a FHIR server locally (using spring boot) rather than on an application server. We used it to build the UK version of Argonaut (https://nhsconnect.github.io/CareConnectAPI/explore.html)

If you have maven and java installed, it should run using mvn spring-boot:run

This is awesome. Thanks @mayfield.g.kev. I will test and report back.

Hi @mayfield.g.kev, I have successfully spun up the Spring Boot version, but I’m noticing that the “viewer” isn’t there. Is there anyway to integrate this?

I couldn’t get the the overlay to work with springboot (not sure of the exact reason)

I used a separate webapp as a work around (https://github.com/KevinMayfield/careconnect-hapi-ui-stu3) which is not ideal.

Update:

To create a FHIR Server, see the HAPI JPA Server notes here: http://hapifhir.io/doc_jpa.html
(please use these instructions in place of those above)

Update on the CareConnect links posted previously:

API Documentation: https://nhsconnect.github.io/CareConnectAPI/index.html
Demo server (of API): http://yellow.testlab.nhs.uk/careconnect-ri/home?serverId=home&pretty=true (currently at release 2 which covers Individuals and Entities plus Observations)

This also uses libraries from the HAPI FHIR open source project but is an example of how an existing PAS, EPR, EDMS, SQL Server, etc, system can be exposed as a FHIR server (The database is SQL and the db model is based on actual systems). Source code can be found here https://github.com/nhsconnect/careconnect-reference-implementation.

@mayfield.g.kev Thanks for the tutorial. Really helpful esp for someone what is not that fluent in Java. By following your example and the comments I am able to get over connection error to mysql

However, I’m having a bit of a problem with the actual sql statements. I’m getting the following:

jetty_1  | 2018-05-18 03:26:41.570 [scheduledExecutorService-1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:129] SQL Error: 1064, SQLState: 42000
jetty_1  | 2018-05-18 03:26:41.571 [scheduledExecutorService-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:131] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fetch first 1001 rows only' at line 1
jetty_1  | 2018-05-18 03:26:41.575 [scheduledExecutorService-1] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler [TaskUtils.java:96] Unexpected error occurred in scheduled task.
jetty_1  | org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
jetty_1  | 	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242)
jetty_1  | 	at ca.uhn.fhir.jpa.config.HapiFhirHibernateJpaDialect.convertHibernateAccessException(HapiFhirHibernateJpaDialect.java:59)
jetty_1  | 	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)
jetty_1  | 	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:507)
jetty_1  | 	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
je
...
jetty_1  | Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
jetty_1  | 	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
jetty_1  | 	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
jetty_1  | 	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
...
jetty_1  | Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fetch first 1000 rows only' at line 1
jetty_1  | 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
jetty_1  | 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
...

Could it be because

  • I don’t have a mysql schema in place?
  • I’m not using the right driver?
  • I’m not using the right mysql version?

I’m not sure where to look. Can anyone point me to the right direction?

Thanks in advance,

Justin

The schema is created automatically by the server.

Which repository/instructions did you use? I’m presuming the one from the hapifhir.io

I suspect it’s the datasource dialect that is wrong, for MySQL 5.7 I’ve used
datasource.dialect=org.hibernate.dialect.MySQL57Dialect

Thanks Kev! That was it. Got this up and running now

Hi @mayfield.g.kev.
In Eclipse Oxygen, I edited FhirServerConfig.java file like this:
Datasource Bean:

@Bean(destroyMethod = "close")
public DataSource dataSource() {
	BasicDataSource retVal = new BasicDataSource();
	try {
		retVal.setDriver(new com.mysql.jdbc.Driver());
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	retVal.setUrl("jdbc:mysql://localhost:3306/dbhapi");
	retVal.setUsername("root");
	retVal.setPassword("root");
	return retVal;
}

dbhapi is an empty database - no table is created.

jpaProperties:

private Properties jpaProperties() {
	Properties extraProperties = new Properties();
	extraProperties.put("hibernate.dialect", "org.hibernate.dialect.MySQL57InnoDBDialect");
	extraProperties.put("hibernate.format_sql", "true");
	extraProperties.put("hibernate.show_sql", "false");
	extraProperties.put("hibernate.hbm2ddl.auto", "update");
	extraProperties.put("hibernate.jdbc.batch_size", "20");
	extraProperties.put("hibernate.cache.use_query_cache", "false");
	extraProperties.put("hibernate.cache.use_second_level_cache", "false");
	extraProperties.put("hibernate.cache.use_structured_entries", "false");
	extraProperties.put("hibernate.cache.use_minimal_puts", "false");
	// the belowing properties are used for ElasticSearch integration
	extraProperties.put(ElasticsearchEnvironment.ANALYZER_DEFINITION_PROVIDER, ElasticsearchMappingProvider.class.getName());
	extraProperties.put("hibernate.search.default.indexmanager", "elasticsearch");
	extraProperties.put("hibernate.search.default.elasticsearch.host", "http://127.0.0.1:9200");
	extraProperties.put("hibernate.search.default.elasticsearch.index_schema_management_strategy", "CREATE");
	extraProperties.put("hibernate.search.default.elasticsearch.index_management_wait_timeout", "10000");
	extraProperties.put("hibernate.search.default.elasticsearch.required_index_status", "yellow");
	return extraProperties;
}

I used Maven build goals: clean compile package.
Build successfully but I think there is something wrong in the created .war file: browseing to API server http://localhost:8080/hapi-fhir-jpaserver-example/ gives me status code 404.

If you look in your tomcat installation you should have a logs folder. In there you should find a tomcat8-stdout.*.log files.

This probably has details on the actual fault.

Hi @mayfield.g.kev.
I open localhost.2018.10.16.log as you said and found a file “localhost.2018.10.16.logs”. I am reading but i don’t think i can debug by myself. Please help. Thank you

16-Oct-2018 12:36:31.665 INFO [ContainerBackgroundProcessor[StandardEngine[Catalina]]] org.apache.catalina.core.ApplicationContext.log No Spring WebApplicationInitializer types detected on classpath
16-Oct-2018 12:36:31.698 INFO [ContainerBackgroundProcessor[StandardEngine[Catalina]]] org.apache.catalina.core.ApplicationContext.log Initializing Spring root WebApplicationContext
16-Oct-2018 12:36:38.554 SEVERE [ContainerBackgroundProcessor[StandardEngine[Catalina]]] org.apache.catalina.core.StandardContext.listenerStart Exception sending context initialized event to listener instance of class [org.springframework.web.context.ContextLoaderListener]
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘entityManagerFactory’ defined in ca.uhn.fhir.jpa.demo.FhirServerConfig: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: HAPI_PU] Unable to build Hibernate SessionFactory
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1710)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1085)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:858)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:409)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:291)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:103)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4643)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5109)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:743)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:719)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:703)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:986)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1858)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.util.concurrent.AbstractExecutorService.submit(Unknown Source)
at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:772)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:426)
at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1629)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:304)
at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1176)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1398)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1402)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1370)
at java.lang.Thread.run(Unknown Source)
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: HAPI_PU] Unable to build Hibernate SessionFactory
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:970)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:895)
at org.hibernate.jpa.HibernatePersistenceProvider.createContainerEntityManagerFactory(HibernatePersistenceProvider.java:151)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:388)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1769)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1706)
… 34 more
Caused by: org.hibernate.exception.GenericJDBCException: Unable to open JDBC Connection for DDL execution
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:69)
at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcConnection(ImprovedExtractionContextImpl.java:60)
at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcDatabaseMetaData(ImprovedExtractionContextImpl.java:67)
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTables(InformationExtractorJdbcDatabaseMetaDataImpl.java:329)
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.getTablesInformation(DatabaseInformationImpl.java:120)
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:65)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:312)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:460)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892)
… 41 more
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Client does not support authentication protocol requested by server; consider upgrading MySQL client)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2294)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2039)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180)
at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:43)
… 53 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Client does not support authentication protocol requested by server; consider upgrading MySQL client
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:910)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3923)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1273)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:718)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2304)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2290)
… 58 more

Code for a two versions of this is available here

This is a hapi server with custom SQL implementation (similar to most EPR databases and shows how a fhir service can be added on to an existing system)

This is the standard HAPI JPA Server wrapped up in docker and spring boot.
I would look at this is you’re starting out with a system/app or just want a fhir server to explore.

See the docker-compose file for running with docker.

The other projects in this repo mostly take existing api’s (soap, hl7v3, ITK, LDAP) and add a Fhir service to them.

Any has SQL DDL for me to create complete tables for FHIR resources? I will really appreciate if you can share. Thanks.

P

Someone may, but in general this is difficult for the reasons mentioned here:

(free registration required.)

It says “there is no standard DDL like this because everyone makes different decisions about how they want their tables laid out. Most of the resources have 3-4 levels of nested structure, though there may be more (and some are recursive). You have to decide this for yourself”

There is also an initiative called SQL-on-FHIR that looks at this issue. I don’t know how far it has got:


But many FHIR implementations either don’t use FHIR natively (FHIR as a facade onto your own non-FHIR RDBMS) or they use NoSQL storage, so there are no tables and no DDL.

Rik

The HAPI JPA Server (the CDR above is a version of this) uses RDBMS storage but it stores all the resources in a column in one table. This is for the reasons Rik just listed.
If you get HAPI running locally with a hibernate compatible database (postgresql, mysql, sql server, etc) it will build the database for you.

The CCRI (which is also the CDR-Sql above) will also build a database (again it uses hibernate but has only been tested on postgresql). It is not a complete set of FHIR resources but these do get broken down into a traditional RDBMS structure.

Both need some familiarity with java to configure.