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


(Kev Mayfield) #21

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


(Matthew Vi) #22

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


(Matthew Vi) #23

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?


(Kev Mayfield) #24

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.


(Kev Mayfield) #25

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.


(Justin Ho) #26

@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


(Kev Mayfield) #27

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


(Justin Ho) #28

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


(Hackerninaritai) #29

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.


(Kev Mayfield) #30

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.


(Hackerninaritai) #31

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