Spring Boot Multiple Datasource

Question

I m quite new to spring boot and I d like to create a multiple datasource for my project  Here is my current case  I have two packages for entity for multiple database  Let s say  com test entity db mysql   for entities that belong to MySql com test entity db h2   for entities that belong to H2 Databases   So  currently I have two entities class  UserMySql java   Entity  Table name  usermysql   public class UserMysql        Id      GeneratedValue     public int id       public String name       UserH2 java   Entity  Table name  userh2   public class Userh2         Id      GeneratedValue     public int id       public String name      I d like to achieve a configuration where if I create user from UserMySql  it will be saved to MySql Database  and if I create user from Userh2 it will be saved to H2 Databases  So  I also have two DBConfig  let s say MySqlDbConfig and H2DbConfig     com test model is package where I ll put my Repositories class  It will be defined below   MySqlDbConfig java   Configuration  EnableJpaRepositories      basePackages  com test model       entityManagerFactoryRef    mysqlEntityManager   public class MySqlDBConfig     Bean  Primary  ConfigurationProperties prefix  datasource test mysql   public DataSource mysqlDataSource        return DataSourceBuilder              create                build        Bean name  mysqlEntityManager   public LocalContainerEntityManagerFactoryBean mySqlEntityManagerFactory          EntityManagerFactoryBuilder builder              return builder dataSource mysqlDataSource                                 packages  com test entity db mysql                build              H2DbConfig java   Configuration  EnableJpaRepositories      entityManagerFactoryRef    h2EntityManager   public class H2DbConfig     Bean  ConfigurationProperties prefix  datasource test h2   public DataSource h2DataSource        return DataSourceBuilder              create                driverClassName  org h2 Driver                build        Bean name  h2EntityManager   public LocalContainerEntityManagerFactoryBean h2EntityManagerFactory          EntityManagerFactoryBuilder builder       return builder dataSource h2DataSource                 packages  com test entity db h2                build          My application properties file   DataSource settings for mysql datasource test mysql jdbcUrl   jdbc mysql   127 0 0 1 3306 test datasource test mysql username   root datasource test mysql password   root datasource test mysql driverClassName   com mysql jdbc Driver   DataSource settings for H2 datasource test h2 jdbcUrl   jdbc h2   test datasource test h2 username   sa    DataSource settings  set here configurations for the database connection spring datasource url   jdbc mysql   127 0 0 1 3306 test spring datasource username   root spring datasource password   root spring datasource driverClassName   com mysql jdbc Driver spring datasource validation-query SELECT 1     Specify the DBMS spring jpa database   MYSQL    Show or not log for each sql query spring jpa show-sql   true    Hibernate settings are prefixed with spring jpa hibernate   spring jpa hibernate ddl-auto   update spring jpa hibernate dialect   org hibernate dialect MySQL5Dialect spring jpa hibernate naming strategy   org hibernate cfg ImprovedNamingStrategy spring jpa hibernate show sql   true spring jpa hibernate format sql   true  server port 8080 endpoints shutdown enabled false   And then for crud I have UserMySqlDao and UserH2Dao  UserMySqlDao java   Transactional   Repository public interface UserMysqlDao extends CrudRepository lt UserMysql  Integer gt        public UserMysql findByName String name       UserH2Dao java   Transactional  Repositories public interface UserH2Dao extends CrudRepository lt Userh2  Integer gt        public Userh2 findByName String name       And for last  I have an UserController as endpoint to access my service  UserController java   Controller   RequestMapping   user   public class UserController      Autowired private UserMysqlDao userMysqlDao    Autowired private UserH2Dao userH2Dao    RequestMapping   createM    ResponseBody public String createUserMySql String name       UserMysql user   new UserMysql        try                      user name   name          userMysqlDao save user           return  Success creating user with Id    user id       catch Exception ex           return  Error creating the user      ex toString              RequestMapping   createH    ResponseBody public String createUserH2 String name       Userh2 user   new Userh2        try          user name   name          userH2Dao save user           return  Success creating user with Id    user id       catch Exception ex           return  Error creating the user      ex toString                   Application java   Configuration  EnableAutoConfiguration exclude  DataSourceAutoConfiguration class    EntityScan basePackages  com test entity db    ComponentScan public class Application    public static void main String   args        System out println  Entering spring boot        ApplicationContext ctx   SpringApplication run Application class  args        System out println  Let s inspect the beans provided by Spring Boot         String   beanNames   ctx getBeanDefinitionNames        Arrays sort beanNames       for  String beanName   beanNames            System out print beanName           System out print                  System out println             With this configuration my Spring boot run well  but when I access   http   localhost user createM name myname it writes an exception  Error creating the user  org springframework dao InvalidDataAccessResourceUsageException    could not execute statement  SQL  n a   nested exception is org hibernate exception SQLGrammarException  could not execute statement   I ve googling around and haven t got a solution yet  Any ideas why this exception occurs  And is this the best way to implement multiple datasource to implement my case above  I m open to full refactor if needed   Thanks

User · Answer

once you start work with jpa and some driver is in your class path spring boot right away puts it inside as your data source (e.g h2 ) for using the defult data source therefore u will need only to define

spring.datasource.url= jdbc:mysql://localhost:3306/
spring.datasource.username=test
spring.datasource.password=test

if we go one step farther and u want to use two I would reccomend to use two data sources such as explained here : Spring Boot Configure and Use Two DataSources

User · Answer

Using two datasources you need their own transaction managers    Configuration public class MySqlDBConfig        Bean      Primary      ConfigurationProperties prefix  datasource test mysql       public DataSource mysqlDataSource             return DataSourceBuilder                   create                     build                Bean  mysqlTx       public DataSourceTransactionManager mysqlTx             return new DataSourceTransactionManager mysqlDataSource                   same for another DS     And then use it accordingly within  Transaction   Transactional  mysqlTx    Repository public interface UserMysqlDao extends CrudRepository lt UserMysql  Integer gt       public UserMysql findByName String name

User · Answer

Update 2018-01-07 with Spring Boot 1 5 8 RELEASE  If you want to know how to config it  how to use it  and how to control transaction  I may have answers for you   You can see the runnable example and some explanation in https   www surasint com spring-boot-with-multiple-databases-example   I copied some code here   First you have to set application properties like this   Database database1 datasource url jdbc mysql   localhost testdb database1 datasource username root database1 datasource password root database1 datasource driver-class-name com mysql jdbc Driver  database2 datasource url jdbc mysql   localhost testdb2 database2 datasource username root database2 datasource password root database2 datasource driver-class-name com mysql jdbc Driver   Then define them as providers   Bean  like this    Bean name    datasource1    ConfigurationProperties  database1 datasource    Primary public DataSource dataSource        return DataSourceBuilder create   build        Bean name    datasource2    ConfigurationProperties  database2 datasource   public DataSource dataSource2        return DataSourceBuilder create   build        Note that I have  Bean name  datasource1   and  Bean name  datasource2    then you can use it when we need datasource as  Qualifier  datasource1   and  Qualifier  datasource2     for example    Qualifier  datasource1    Autowired private DataSource dataSource    If you do care about transaction  you have to define DataSourceTransactionManager for both of them  like this    Bean name  tm1    Autowired  Primary DataSourceTransactionManager tm1  Qualifier   datasource1   DataSource datasource        DataSourceTransactionManager txm    new DataSourceTransactionManager datasource       return txm      Bean name  tm2    Autowired DataSourceTransactionManager tm2  Qualifier   datasource2   DataSource datasource        DataSourceTransactionManager txm    new DataSourceTransactionManager datasource       return txm      Then you can use it like   Transactional   this will use the first datasource because it is  primary   or   Transactional  tm2     This should be enough  See example and detail in the link above

User · Answer

I solved the problem  How to connect multiple database using spring and Hibernate  in this way  I hope it will help     NOTE  I have added the relevant code  kindly make the dao with the help of impl I used in the below mentioned code   web xml   lt  xml version  1 0  encoding  UTF-8   gt   lt web-app xmlns xsi  http   www w3 org 2001 XMLSchema-instance      xmlns  http   java sun com xml ns javaee  xmlns web  http   java sun com xml ns javaee web-app 2 5 xsd      xsi schemaLocation  http   java sun com xml ns javaee http   java sun com xml ns javaee web-app 3 0 xsd      id  WebApp ID  version  3 0  gt       lt display-name gt MultipleDatabaseConnectivityInSpring lt  display-name gt       lt welcome-file-list gt           lt welcome-file gt index jsp lt  welcome-file gt       lt  welcome-file-list gt        lt servlet gt           lt servlet-name gt dispatcher lt  servlet-name gt           lt servlet-class gt org springframework web servlet DispatcherServlet lt  servlet-class gt            lt load-on-startup gt 1 lt  load-on-startup gt       lt  servlet gt        lt listener gt           lt listener-class gt org springframework web context ContextLoaderListener lt  listener-class gt       lt  listener gt        lt context-param gt           lt param-name gt contextConfigLocation lt  param-name gt           lt param-value gt               WEB-INF dispatcher-servlet xml          lt  param-value gt       lt  context-param gt       lt servlet-mapping gt           lt servlet-name gt dispatcher lt  servlet-name gt           lt url-pattern gt   htm lt  url-pattern gt       lt  servlet-mapping gt       lt session-config gt           lt session-timeout gt 30 lt  session-timeout gt       lt  session-config gt   lt  web-app gt    persistence xml   lt  xml version  1 0  encoding  UTF-8   gt   lt persistence version  1 0      xmlns  http   java sun com xml ns persistence  xmlns xsi  http   www w3 org 2001 XMLSchema-instance      xsi schemaLocation  http   java sun com xml ns persistence http   java sun com xml ns persistence persistence 1 0 xsd  gt       lt persistence-unit name  localPersistenceUnitOne          transaction-type  RESOURCE LOCAL  gt           lt provider gt org hibernate ejb HibernatePersistence lt  provider gt           lt class gt in india entities CustomerDetails lt  class gt           lt exclude-unlisted-classes   gt           lt properties gt               lt property name  hibernate dialect  value  org hibernate dialect PostgreSQLDialect    gt               lt property name  hibernate connection driver class  value  org postgresql Driver    gt               lt property name  hibernate jdbc batch size  value  0    gt               lt property name  hibernate show sql  value  false    gt               lt property name  hibernate connection url  value  jdbc postgresql   localhost 5432 shankar sslmode require    gt               lt property name  hibernate connection username  value  username    gt               lt property name  hibernate connection password  value  password    gt               lt property name  hibernate hbm2ddl auto  value  update    gt           lt  properties gt       lt  persistence-unit gt       lt persistence-unit name  localPersistenceUnitTwo          transaction-type  RESOURCE LOCAL  gt           lt provider gt org hibernate ejb HibernatePersistence lt  provider gt           lt class gt in india entities CompanyDetails lt  class gt           lt exclude-unlisted-classes   gt           lt properties gt               lt property name  hibernate dialect  value  org hibernate dialect PostgreSQLDialect    gt               lt property name  hibernate connection driver class  value  org postgresql Driver    gt               lt property name  hibernate jdbc batch size  value  0    gt               lt property name  hibernate show sql  value  false    gt               lt property name  hibernate connection url  value  jdbc postgresql   localhost 5432 shankarTwo sslmode require    gt               lt property name  hibernate connection username  value  username    gt               lt property name  hibernate connection password  value  password    gt               lt property name  hibernate hbm2ddl auto  value  update    gt           lt  properties gt       lt  persistence-unit gt   lt  persistence gt    dispatcher-servlet   lt  xml version  1 0  encoding  UTF-8   gt   lt beans xmlns  http   www springframework org schema beans      xmlns task  http   www springframework org schema task  xmlns p  http   www springframework org schema p      xmlns xsi  http   www w3 org 2001 XMLSchema-instance  xmlns context  http   www springframework org schema context      xmlns mvc  http   www springframework org schema mvc  xmlns tx  http   www springframework org schema tx      xmlns aop  http   www springframework org schema aop  xmlns util  http   www springframework org schema util      default-autowire  byName      xsi schemaLocation  http   www springframework org schema beans http   www springframework org schema beans spring-beans xsd        http   www springframework org schema context http   www springframework org schema context spring-context-3 0 xsd         http   www springframework org schema aop  http   www springframework org schema aop spring-aop-3 0 xsd        http   www springframework org schema task http   www springframework org schema task spring-task-3 0 xsd       http   www springframework org schema tx http   www springframework org schema tx spring-tx-3 0 xsd http   www springframework org schema mvc        http   www springframework org schema mvc spring-mvc-3 0 xsd  gt       lt  -- Configure messageSource -- gt        lt mvc annotation-driven   gt       lt context component-scan base-package  in india      gt       lt bean id  messageResource          class  org springframework context support ResourceBundleMessageSource          autowire  byName  gt           lt property name  basename  value  messageResource  gt  lt  property gt       lt  bean gt        lt bean         class  org springframework web servlet view InternalResourceViewResolver  gt           lt property name  prefix  gt               lt value gt  WEB-INF jsp  lt  value gt           lt  property gt           lt property name  suffix  gt               lt value gt  jsp lt  value gt           lt  property gt       lt  bean gt          lt bean id  entityManagerFactoryOne          class  org springframework orm jpa LocalContainerEntityManagerFactoryBean          autowire  constructor  gt           lt property name  persistenceUnitName  value  localPersistenceUnitOne    gt       lt  bean gt        lt bean id  messageSource          class  org springframework context support ResourceBundleMessageSource          autowire  byName  gt           lt property name  basename  value  messageResource    gt       lt  bean gt        lt bean id  entityManagerFactoryTwo          class  org springframework orm jpa LocalContainerEntityManagerFactoryBean          autowire  constructor  gt           lt property name  persistenceUnitName  value  localPersistenceUnitTwo    gt       lt  bean gt        lt bean id  manager1  class  org springframework orm jpa JpaTransactionManager  gt           lt property name  entityManagerFactory  ref  entityManagerFactoryOne    gt       lt  bean gt        lt bean id  manager2  class  org springframework orm jpa JpaTransactionManager  gt           lt property name  entityManagerFactory  ref  entityManagerFactoryTwo    gt       lt  bean gt        lt tx annotation-driven transaction-manager  manager1    gt       lt tx annotation-driven transaction-manager  manager2    gt        lt  -- declare dependies here -- gt        lt bean class  in india service dao impl CustomerServiceImpl    gt       lt bean class  in india service dao impl CompanyServiceImpl    gt        lt  -- Configure MVC annotations -- gt       lt bean         class  org springframework web servlet mvc annotation DefaultAnnotationHandlerMapping    gt       lt bean         class  org springframework web servlet mvc annotation AnnotationMethodHandlerAdapter    gt       lt bean         class  org springframework http converter json MappingJacksonHttpMessageConverter    gt   lt  beans gt    java class to persist into one database  package in india service dao impl   import in india entities CompanyDetails  import in india service CompanyService   import javax persistence EntityManager  import javax persistence PersistenceContext   import org springframework transaction annotation Transactional   public class CompanyServiceImpl implements CompanyService         PersistenceContext unitName    entityManagerFactoryTwo       EntityManager entityManager        Transactional  manager2        Override     public boolean companyService CompanyDetails companyDetails             boolean flag   false          try                        entityManager persist companyDetails               flag   true                     catch  Exception e                        flag   false                     return flag             java class to persist in another database   package in india service dao impl   import javax persistence EntityManager  import javax persistence PersistenceContext   import org springframework transaction annotation Transactional   import in india entities CustomerDetails  import in india service CustomerService   public class CustomerServiceImpl implements CustomerService         PersistenceContext unitName    localPersistenceUnitOne       EntityManager entityManager        Override      Transactional value    manager1       public boolean customerService CustomerDetails companyData             boolean flag   false          entityManager persist companyData           return flag            customer jsp   lt   page language  java  contentType  text html  charset ISO-8859-1      pageEncoding  ISO-8859-1   gt   lt   taglib uri  http   www springframework org tags form  prefix  form   gt   lt  DOCTYPE html PUBLIC  -  W3C  DTD HTML 4 01 Transitional  EN   http   www w3 org TR html4 loose dtd  gt   lt html gt   lt head gt   lt meta http-equiv  Content-Type  content  text html  charset ISO-8859-1  gt   lt title gt Insert title here lt  title gt   lt  head gt   lt body gt       lt center gt           lt h1 gt SpringWithMultipleDatabase s lt  h1 gt       lt  center gt       lt form form method  GET  action  addCustomer htm   modelAttribute  customerBean   gt           lt table gt               lt tr gt                   lt td gt  lt form label path  firstName  gt First Name lt  form label gt  lt  td gt                   lt td gt  lt form input path  firstName    gt  lt  td gt               lt  tr gt               lt tr gt                   lt td gt  lt form label path  lastName  gt Last Name lt  form label gt  lt  td gt                   lt td gt  lt form input path  lastName    gt  lt  td gt               lt  tr gt               lt tr gt                   lt td gt  lt form label path  emailId  gt Email Id lt  form label gt  lt  td gt                   lt td gt  lt form input path  emailId    gt  lt  td gt               lt  tr gt               lt tr gt                   lt td gt  lt form label path  profession  gt Profession lt  form label gt  lt  td gt                   lt td gt  lt form input path  profession    gt  lt  td gt               lt  tr gt               lt tr gt                   lt td gt  lt form label path  address  gt Address lt  form label gt  lt  td gt                   lt td gt  lt form input path  address    gt  lt  td gt               lt  tr gt               lt tr gt                   lt td gt  lt form label path  age  gt Age lt  form label gt  lt  td gt                   lt td gt  lt form input path  age    gt  lt  td gt               lt  tr gt               lt tr gt                   lt td gt  lt input type  submit  value  Submit   gt  lt  td gt                lt  tr gt           lt  table gt       lt  form form gt   lt  body gt   lt  html gt    company jsp   lt    page language  java  contentType  text html  charset ISO-8859-1  pageEncoding  ISO-8859-1   gt   lt   taglib uri  http   www springframework org tags form  prefix  form   gt   lt  DOCTYPE html PUBLIC  -  W3C  DTD HTML 4 01 Transitional  EN   http   www w3 org TR html4 loose dtd  gt   lt html gt   lt head gt   lt meta http-equiv  Content-Type  content  text html  charset ISO-8859-1  gt   lt title gt ScheduleJobs lt  title gt   lt  head gt   lt body gt    lt center gt  lt h1 gt SpringWithMultipleDatabase s lt  h1 gt  lt  center gt    lt form form method  GET  action  addCompany htm   modelAttribute  companyBean   gt    lt table gt       lt tr gt           lt td gt  lt form label path  companyName  gt Company Name lt  form label gt  lt  td gt           lt td gt  lt form input path  companyName    gt  lt  td gt       lt  tr gt       lt tr gt           lt td gt  lt form label path  companyStrength  gt Company Strength lt  form label gt  lt  td gt           lt td gt  lt form input path  companyStrength    gt  lt  td gt       lt  tr gt       lt tr gt           lt td gt  lt form label path  companyLocation  gt Company Location lt  form label gt  lt  td gt           lt td gt  lt form input path  companyLocation    gt  lt  td gt       lt  tr gt        lt tr gt           lt td gt               lt input type  submit  value  Submit   gt           lt  td gt       lt  tr gt    lt  table gt    lt  form form gt   lt  body gt   lt  html gt    index jsp   lt    page language  java  contentType  text html  charset ISO-8859-1      pageEncoding  ISO-8859-1   gt   lt  DOCTYPE html PUBLIC  -  W3C  DTD HTML 4 01 Transitional  EN   http   www w3 org TR html4 loose dtd  gt   lt html gt   lt head gt   lt meta http-equiv  Content-Type  content  text html  charset ISO-8859-1  gt   lt title gt Home lt  title gt   lt  head gt   lt body gt    lt center gt  lt h1 gt Multiple Database Connectivity In Spring sdfsdsd lt  h1 gt  lt  center gt    lt a href  customerRequest htm  gt Click here to go on Customer page lt  a gt   lt br gt   lt a href  companyRequest htm  gt Click here to go on Company page lt  a gt   lt  body gt   lt  html gt    success jsp   lt    page language  java  contentType  text html  charset ISO-8859-1      pageEncoding  ISO-8859-1   gt   lt  DOCTYPE html PUBLIC  -  W3C  DTD HTML 4 01 Transitional  EN   http   www w3 org TR html4 loose dtd  gt   lt html gt   lt head gt   lt meta http-equiv  Content-Type  content  text html  charset ISO-8859-1  gt   lt title gt ScheduleJobs lt  title gt   lt  head gt   lt body gt    lt center gt  lt h1 gt SpringWithMultipleDatabase lt  h1 gt  lt  center gt       lt b gt Successfully Saved lt  b gt   lt  body gt   lt  html gt    CompanyController  package in india controller   import in india bean CompanyBean  import in india entities CompanyDetails  import in india service CompanyService   import org springframework beans factory annotation Autowired  import org springframework stereotype Controller  import org springframework ui Model  import org springframework ui ModelMap  import org springframework web bind annotation ModelAttribute  import org springframework web bind annotation RequestMapping  import org springframework web bind annotation RequestMethod  import org springframework web servlet ModelAndView    Controller public class CompanyController         Autowired     CompanyService companyService        RequestMapping value     companyRequest htm   method   RequestMethod GET      public ModelAndView addStudent ModelMap model            CompanyBean companyBean   new CompanyBean            model addAttribute companyBean           return new ModelAndView  company                RequestMapping value     addCompany htm   method   RequestMethod GET      public ModelAndView companyController  ModelAttribute  companyBean   CompanyBean companyBean  Model model            CompanyDetails  companyDetails   new CompanyDetails            companyDetails setCompanyLocation companyBean getCompanyLocation             companyDetails setCompanyName companyBean getCompanyName             companyDetails setCompanyStrength companyBean getCompanyStrength             companyService companyService companyDetails           return new ModelAndView  success               CustomerController  package in india controller   import in india bean CustomerBean  import in india entities CustomerDetails  import in india service CustomerService   import org springframework beans factory annotation Autowired  import org springframework stereotype Controller  import org springframework ui Model  import org springframework ui ModelMap  import org springframework web bind annotation ModelAttribute  import org springframework web bind annotation RequestMapping  import org springframework web bind annotation RequestMethod  import org springframework web servlet ModelAndView    Controller public class CustomerController         Autowired     CustomerService customerService        RequestMapping value     customerRequest htm   method   RequestMethod GET      public ModelAndView addStudent ModelMap model            CustomerBean customerBean   new CustomerBean            model addAttribute customerBean           return new ModelAndView  customer                RequestMapping value     addCustomer htm   method   RequestMethod GET      public ModelAndView customerController  ModelAttribute  customerBean   CustomerBean customer  Model model            CustomerDetails customerDetails   new CustomerDetails            customerDetails setAddress customer getAddress             customerDetails setAge customer getAge             customerDetails setEmailId customer getEmailId             customerDetails setFirstName customer getFirstName             customerDetails setLastName customer getLastName             customerDetails setProfession customer getProfession             customerService customerService customerDetails           return new ModelAndView  success               CompanyDetails Entity  package in india entities   import javax persistence Column  import javax persistence Entity  import javax persistence GeneratedValue  import javax persistence GenerationType  import javax persistence Id  import javax persistence SequenceGenerator  import javax persistence Table    Entity  Table name    company details   public class CompanyDetails         Id      SequenceGenerator name    company details seq   sequenceName    company details seq   initialValue   1  allocationSize   1       GeneratedValue strategy   GenerationType SEQUENCE  generator    company details seq        Column name    company details id       private Long companyDetailsId       Column name    company name       private String companyName       Column name    company strength       private Long companyStrength       Column name    company location       private String companyLocation       public Long getCompanyDetailsId             return companyDetailsId             public void setCompanyDetailsId Long companyDetailsId            this companyDetailsId   companyDetailsId             public String getCompanyName             return companyName             public void setCompanyName String companyName            this companyName   companyName             public Long getCompanyStrength             return companyStrength             public void setCompanyStrength Long companyStrength            this companyStrength   companyStrength             public String getCompanyLocation             return companyLocation             public void setCompanyLocation String companyLocation            this companyLocation   companyLocation            CustomerDetails Entity  package in india entities   import javax persistence Column  import javax persistence Entity  import javax persistence GeneratedValue  import javax persistence GenerationType  import javax persistence Id  import javax persistence SequenceGenerator  import javax persistence Table    Entity  Table name    customer details   public class CustomerDetails         Id      SequenceGenerator name    customer details seq   sequenceName    customer details seq   initialValue   1  allocationSize   1       GeneratedValue strategy   GenerationType SEQUENCE  generator    customer details seq        Column name    customer details id       private Long customerDetailsId       Column name    first name        private String firstName       Column name    last name        private String lastName       Column name    email id       private String emailId       Column name    profession       private String profession       Column name    address       private String address       Column name    age       private int age      public Long getCustomerDetailsId             return customerDetailsId             public void setCustomerDetailsId Long customerDetailsId            this customerDetailsId   customerDetailsId             public String getFirstName             return firstName             public void setFirstName String firstName            this firstName   firstName             public String getLastName             return lastName             public void setLastName String lastName            this lastName   lastName             public String getEmailId             return emailId             public void setEmailId String emailId            this emailId   emailId             public String getProfession             return profession             public void setProfession String profession            this profession   profession             public String getAddress             return address             public void setAddress String address            this address   address             public int getAge             return age             public void setAge int age            this age   age

User · Answer

I faced same issue few days back  I followed the link mentioned below and I could able to overcome the problem  http   www baeldung com spring-data-jpa-multiple-databases

User · Answer

MySqlBDConfig java   Configuration  EnableTransactionManagement  EnableJpaRepositories basePackages    PACKAGE OF YOUR CRUDS USING MYSQL DATABASE  entityManagerFactoryRef    mysqlEmFactory   transactionManagerRef    mysqlTransactionManager   public class MySqlBDConfig    Autowired private Environment env    Bean name  mysqlProperities    ConfigurationProperties prefix  spring mysql   public DataSourceProperties mysqlProperities        return new  DataSourceProperties         Bean name  mysqlDataSource   public DataSource interfaceDS  Qualifier  mysqlProperities  DataSourceProperties dataSourceProperties       return dataSourceProperties initializeDataSourceBuilder   build         Primary  Bean name  mysqlEmFactory   public LocalContainerEntityManagerFactoryBean mysqlEmFactory  Qualifier  mysqlDataSource  DataSource mysqlDataSource EntityManagerFactoryBuilder builder       return builder dataSource mysqlDataSource  packages  PACKAGE OF YOUR MODELS   build         Bean name  mysqlTransactionManager   public PlatformTransactionManager mysqlTransactionManager  Qualifier  mysqlEmFactory  EntityManagerFactory factory       return new JpaTransactionManager factory         H2DBConfig java   Configuration  EnableTransactionManagement  EnableJpaRepositories basePackages    PACKAGE OF YOUR CRUDS USING MYSQL DATABASE  entityManagerFactoryRef    dsEmFactory   transactionManagerRef    dsTransactionManager   public class H2DBConfig            Autowired         private Environment env            Bean name  dsProperities            ConfigurationProperties prefix  spring h2           public DataSourceProperties dsProperities                return new  DataSourceProperties                     Bean name  dsDataSource       public DataSource dsDataSource  Qualifier  dsProperities  DataSourceProperties dataSourceProperties           return dataSourceProperties initializeDataSourceBuilder   build                 Bean name  dsEmFactory       public LocalContainerEntityManagerFactoryBean dsEmFactory  Qualifier  dsDataSource  DataSource dsDataSource EntityManagerFactoryBuilder builder           LocalContainerEntityManagerFactoryBean em    builder dataSource dsDataSource  packages  PACKAGE OF YOUR MODELS   build            HibernateJpaVendorAdapter ven   new HibernateJpaVendorAdapter            em setJpaVendorAdapter ven           HashMap lt String  Object gt  prop   new HashMap lt  gt             prop put  hibernate dialect   env getProperty  spring jpa properties hibernate dialect             prop put  hibernate show sql   env getProperty  spring jpa show-sql              em setJpaPropertyMap prop           em afterPropertiesSet            return em               Bean name  dsTransactionManager       public PlatformTransactionManager dsTransactionManager  Qualifier  dsEmFactory  EntityManagerFactory factory           return new JpaTransactionManager factory             application properties   ---mysql DATASOURCE--- spring mysql driverClassName   com mysql jdbc Driver spring mysql url   jdbc mysql   127 0 0 1 3306 test spring mysql username   root spring mysql password   root  ----------------------   ---H2 DATASOURCE---- spring h2 driverClassName   org h2 Driver spring h2 url   jdbc h2 file   test spring h2 username   root spring h2 password   root  ---------------------------   ------JPA-----  spring jpa properties hibernate dialect   org hibernate dialect H2Dialect spring jpa show-sql true spring jpa properties hibernate temp use jdbc metadata defaults   false spring jpa hibernate ddl-auto   update spring jpa properties hibernate enable lazy load no trans true   Application java   SpringBootApplication public class Application         public static void main String   args              ApplicationContext ac SpringApplication run KeopsSageInvoiceApplication class  args           UserMysqlDao userRepository   ac getBean UserMysqlDao class            for exemple save a new user using your repository          userRepository save new UserMysql

User · Answer

I think you can find it usefull  http   docs spring io spring-boot docs current reference htmlsingle  howto-two-datasources  It shows how to define multiple datasources  amp  assign one of them as primary   Here is a rather full example  also contains distributes transactions - if you need it   http   fabiomaffioletti me blog 2014 04 15 distributed-transactions-multiple-databases-spring-boot-spring-data-jpa-atomikos   What you need is to create 2 configuration classes  separate the model repository packages etc to make the config easy   Also  in above example  it creates the data sources manually  You can avoid this using the method on spring doc  with  ConfigurationProperties annotation  Here is an example of this   http   xantorohara blogspot com tr 2013 11 spring-boot-jdbc-with-multiple html  Hope these helps

User · Answer

Thanks all for your help but it is not complicated as it seems  almost everything is handled internally by SpringBoot     In my case I want to use Mysql and Mongodb and the solution was to use EnableMongoRepositories and EnableJpaRepositories annotations on to my application class     SpringBootApplication  EnableTransactionManagement  EnableMongoRepositories includeFilters    ComponentScan Filter type   FilterType ASSIGNABLE TYPE  value   MongoRepository    EnableJpaRepositories excludeFilters    ComponentScan Filter type   FilterType ASSIGNABLE TYPE  value   MongoRepository   class TestApplication         NB  All mysql entities have to extend JpaRepository and mongo enities have to extend MongoRepository    The datasource configs are straight forward as presented by spring documentation      mysql db config spring datasource url  jdbc mysql   localhost 3306 tangio spring datasource username test spring datasource password test   mongodb config spring data mongodb host localhost spring data mongodb port 27017 spring data mongodb database tangio spring data mongodb username tangio spring data mongodb password tangio spring data mongodb repositories enabled true

User · Answer

Use multiple datasource or realizing the separation of reading  amp  writing  you must have a knowledge of Class AbstractRoutingDataSource which support dynamic datasource choose   Here is my datasource yaml and I figure out how to resolve this case  You can refer to this project spring-boot   quartz  Hope this will help you   dbServer    default  localhost 3306   read  localhost 3306   write  localhost 3306 datasource    default      type  com zaxxer hikari HikariDataSource     pool-name  default     continue-on-error  false     jdbc-url  jdbc mysql     dbServer default  schedule job useSSL true amp verifyServerCertificate false amp useUnicode true amp characterEncoding utf8     username  root     password  lh1234     connection-timeout  30000     connection-test-query  SELECT 1     maximum-pool-size  5     minimum-idle  2     idle-timeout  600000     destroy-method  shutdown     auto-commit  false   read      type  com zaxxer hikari HikariDataSource     pool-name  read     continue-on-error  false     jdbc-url  jdbc mysql     dbServer read  schedule job useSSL true amp verifyServerCertificate false amp useUnicode true amp characterEncoding utf8     username  root     password  lh1234     connection-timeout  30000     connection-test-query  SELECT 1     maximum-pool-size  5     minimum-idle  2     idle-timeout  600000     destroy-method  shutdown     auto-commit  false   write      type  com zaxxer hikari HikariDataSource     pool-name  write     continue-on-error  false     jdbc-url  jdbc mysql     dbServer write  schedule job useSSL true amp verifyServerCertificate false amp useUnicode true amp characterEncoding utf8     username  root     password  lh1234     connection-timeout  30000     connection-test-query  SELECT 1     maximum-pool-size  5     minimum-idle  2     idle-timeout  600000     destroy-method  shutdown     auto-commit  false

[java] Spring Boot Multiple Datasource

Examples related to java

Examples related to spring

Examples related to spring-mvc

Examples related to spring-boot