# HOWTO Build a health database and FHIR API Server in 15 mins using open source
**Category:** [open forum](https://openhealthhub.org/c/open-forum/9)
**Created:** 2016-03-05 17:05 UTC
**Views:** 47961
**Replies:** 36
**URL:** https://openhealthhub.org/t/howto-build-a-health-database-and-fhir-api-server-in-15-mins-using-open-source/155
---
## Post #1 by @mayfield.g.kev
Ok maybe not 15 mins but if your familiar with maven (and tomcat) it will be less than 10mins.
Instructions can be found here:
http://jamesagnew.github.io/hapi-fhir/doc_jpa.html
That gets the server running on Tomcat using an Apache Derby database. Reconfiguring to use another database is also pretty simple. A version I created can be found here:
https://github.com/KevinMayfield/AyeUp/tree/master/hapi-fhir-jpaserver
This assumes you've created a database in MySQL called _hapifhir_ with user _fhirjpa_ and password _fhirjpa_
But it's pretty easy to change this to whatever you want by altering **FhirServerConfig.java**
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/hapifhir");
retVal.setUsername("fhirjpa");
retVal.setPassword("fhirjpa");
return retVal;
}
Also add the MySQL drivers to the project by adding a dependency in the **pom.xml** file.
mysql
mysql-connector-java
5.1.6
If all is in order you should be able to browse to your API server via http://localhost:8080/hapi-fhir-jpaserver/
The API should cover most requirements for apps and is the same API being adopted by Code4Health Demonstrator (Ripple OSI and Endeavour Health) and NHS England.
It doesn't have any data but you can use examples from the FHIR website (http://www.hl7.org/FHIR/) or others. I'd recommend using Postman for working with the API (both XML and JSON is supported)
Ideally I'd want to put all this into a docker image and then it would be less than 15 mins to get it running.
Ideal for a hack days as it let developers get working with apps rather than messing with api's and databases?
We have this running in our trust at the moment for evaluation taking live data feeds from our PAS, Document (EDMS) and ED systems. It's been pretty easy so far to build sample apps (AngularJS and mumps!). We've not fully populated the database and stress tested but it's holding up so far.
---
## Post #2 by @pacharanero
Legendary! Thansk @mayfield.g.kev
---
## Post #3 by @mayfield.g.kev
Thanks.
I should mention it's the hard work of University Health Network (Toronto) and James Agnew.
Also maybe worth another HOWTO: http://smarthealthit.org/smart-on-fhir/
from Boston and Harvard. (This covers the security side, not just for FHIR. Again open source)
---
## Post #4 by @wongwaikeong
@mayfield.g.kev Are you coming to any of the NHS hackdays?? I think the next one is going to be in mid-May in London (TBC)
---
## Post #5 by @mayfield.g.kev
Not London but intend to do those in the north.
---
## Post #6 by @clive.spindley
thanks,i will git it a go
---
## Post #7 by @clive.spindley
gave it a go, i am not looking to do anything at this time, just understand the data model - is there any supporting documentation?
---
## Post #8 by @mayfield.g.kev
The main documentation for the api is here: http://www.hl7.org/FHIR/
Endeavour Health have started extending these for UK and this can be found: http://fhir.endeavourhealth.org/profiles.html
I know people have been asking about data models but I think FHIR team have some reluctance but I just noticed a SQL variant called CQL which may be of use. A 'chat' about it is here https://chat.fhir.org/#narrow/stream/implementers/topic/query.20language (it's also a good place to ask about a model)
---
## Post #9 by @clive.spindley
i am up and running with this now
if i use the FHIR data model i would also need to extend it
i understand the reluctance to share in this area, this is an area that needs discussing {point of interest: by the NHS? health technicians with years of experience who have chosen to care by following an IT career pathway need rewarding app ropriately just like clinicians, and NHS leaders}
in order to support integration of H&SC i need to work out how to implement a many to many relationship between PERSON pathways (i would use the class care plan, but am not sure if this is a template {care pathway} or has an instance for each unique person)
---
## Post #10 by @Espera
Very interesting forum.
We are building a full remote monitoring platform (cloud and apps for user and provider) we have completed a lot of the work, including auto integration of devices, cloud analytics, clinical parameters for alerts, scheduling availability for clinician (looking to integrate to NHS system using the encore health apis, and also for medication and summary of histories etc. We also have inference deep learning engine to look at all the data from devices (medical and wellness) which will inform the alert structure.
However am very keen to build the care pathway into this model and include for instance regime, contacts (when how to triage etc )
It seems that you have very much specialised in this area of pathways for some time and appear to have possible ways to build integrations for this .
I very much welcome you reply and possible avenues for ways forward J
Best Regards
David Hollick
cid:image002.png@01D00BFE.96F82D30
T: +44 (0)01892 458998
M +44 (0) 7702 136544
Trichrome Healthcare Limited, 32 Easlands Close, Tunbridge Wells, TN4 8JX
Company registered in England and Wales No 10017866
---
## Post #11 by @mayfield.g.kev
Morning,
I've moved the code to a new repository https://github.com/KevinMayfield/Jorvik
and included a few apps for populating the server either from TRUD flat files, traditional HL7 and/or FHIR feed.
These are all Apache Camel apps.
Hoping to include some Spring OAuth2 examples at some point.
(also in the process of building up the documentation but it's a huge topic. It's roughly install java, tomcat, eclipse, git, MySQL, etc)
---
## Post #12 by @sujayr
This is really useful. I was able to build it but now i want to change the POM file.
What i am not understanding is i dont find this group at all : uk.co.mayfieldis in the repository "https://oss.sonatype.org/content/repositories/snapshots/".
---
## Post #13 by @mayfield.g.kev
When do you get that error?
---
## Post #14 by @geo
This is very helpful. I got the server up and running.
But, and this may be a very amateur question, where is the source code that is creating the left menu lising FHIR methods (valueSet, Patient...etc) and where is the code that executes when the page is posted? I've searching the code and I can not find it.
---
## Post #15 by @mayfield.g.kev
Looking at the pom.xml file, it's pulling the app/war file
ca.uhn.hapi.fhir
hapi-fhir-testpage-overlay
${hapi-fhir-version}
war
provided
This should be the hapi code in git https://github.com/jamesagnew/hapi-fhir/tree/master/hapi-fhir-testpage-overlay (looks like a mix of java and angularJS)
This contains the most part of the server code - the main hapi side may be more useful http://hapifhir.io/
Also for help on hapi, try https://chat.fhir.org/# or this googlegroup https://groups.google.com/forum/#!forum/hapi-fhir
---
## Post #16 by @geo
Thank you for the reply! I appreciate it.
---
## Post #17 by @Codehunt
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?
---
## Post #18 by @mayfield.g.kev
I think you've used the older repository, the latest one is here
https://github.com/KevinMayfield/Jorvik/tree/master/hapi-fhir-jpaserver
But I think the problem was with maven repositories and may work if you try again.
---
## Post #19 by @MatthewVita
@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
---
## Post #20 by @mayfield.g.kev
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)
---
## Post #21 by @mayfield.g.kev
If you have maven and java installed, it should run using **mvn spring-boot:run**
---
## Post #22 by @MatthewVita
This is awesome. Thanks @mayfield.g.kev. I will test and report back.
---
## Post #23 by @MatthewVita
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?
---
## Post #24 by @mayfield.g.kev
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.
---
## Post #25 by @mayfield.g.kev
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.
---
## Post #26 by @juworld
@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
---
## Post #27 by @mayfield.g.kev
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
---
## Post #28 by @juworld
[quote="mayfield.g.kev, post:27, topic:155"]
datasource.dialect=org.hibernate.dialect.MySQL57Dialect
[/quote]
Thanks Kev! That was it. Got this up and running now
---
## Post #29 by @hackerninaritai
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.
---
## Post #30 by @mayfield.g.kev
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.
---
## Post #31 by @hackerninaritai
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
---
## Post #32 by @mayfield.g.kev
Code for a two versions of this is available here
https://github.com/project-wildfyre/clinical-data-repository-sql
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)
https://github.com/project-wildfyre/clinical-data-repository-hapi-jpa
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.
---
## Post #33 by @yhamla
Any has SQL DDL for me to create complete tables for FHIR resources? I will really appreciate if you can share. Thanks.
P
---
## Post #34 by @riksmithies
Someone may, but in general this is difficult for the reasons mentioned here:
https://chat.fhir.org/#narrow/search/fhir.20ddl
(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:
https://github.com/FHIR/sql-on-fhir
https://github.com/FHIR/sql-on-fhir/blob/master/sql-on-fhir.md
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
---
## Post #35 by @mayfield.g.kev
[quote="yhamla, post:33, topic:155"]
L for me to create complete tables for FHIR resources? I will really appreciate if you can share. Thanks.
[/quote]
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.
---
## Post #36 by @Tim_Harsch
links in this blog are 404?
http://jamesagnew.github.io/hapi-fhir/doc_jpa.html
and
https://github.com/KevinMayfield/AyeUp/tree/master/hapi-fhir-jpaserver
---
## Post #37 by @mayfield.g.kev
I would recommend using this repository instead
https://github.com/hapifhir/hapi-fhir-jpaserver-starter
---
**Canonical:** https://openhealthhub.org/t/howto-build-a-health-database-and-fhir-api-server-in-15-mins-using-open-source/155
**Original content:** https://openhealthhub.org/t/howto-build-a-health-database-and-fhir-api-server-in-15-mins-using-open-source/155