Configuring multiple data sources with Druid in a Springboot2.0 project

It's always a chore to configure data sources and related things, caches, and so on before Springboot appears, but these basics can be easily done by default when Springboot appears. That's why you're now advocating for templates > configurations, but then again, if you want to match the template, the tedious place is still as cumbersome as it is today, with multiple data sources in the Springboot project. Now let's talk about it gradually.

Adding Druid dependencies to a project

Go directly to MAVEN warehouse search the latest druid and Log4j, Ali's Druid strong reliance on log4j, but did not add to Maven dependency, in fact, do not install log4j words will report run error. The same spring-boot-web, but this basic project has not been deliberately emphasized.

<!--https://mvnrepository.com/artifact/com.alibaba/druid --        <dependency>            <groupId>Com.alibaba</groupId>            <artifactId>Druid</artifactId>            <version>1.1.9</version>        </dependency>        <!--https://mvnrepository.com/artifact/log4j/log4j --        <dependency>            <groupId>Log4j</groupId>            <artifactId>Log4j</artifactId>            <version>1.2.17</version>        </dependency>        <dependency>            <groupId>Org.springframework.boot</groupId>            <artifactId>Spring-boot-starter-web</artifactId>        </dependency>

Generate the beans required for different data sources

After joining the dependency, we need the following steps:

1. Generate a druiddatasource bean corresponding to each data source (because there are multiple types of name beans for multiple databases, it gives each bean a dominant name, explicit injection prevention error)
2. Injecting different data sources into JPA requires configuration of the Entitymanager,jpaproperties,platformtransactionmanager

The code below omits Getter,setter, and some specific questions refer to the comments in the code:

Druiddbconfig.java

@Configuration@ConfigurationProperties("Druid") Public classDruiddbconfig {//Replace here with your own logger solution    Private FinalLogger Logger = Logsingleton.Getlogsingleton().GetLogger();//These variables are configured in YML    PrivateString DBURL1;PrivateString username1;PrivateString Password1;PrivateString driverClassName1;PrivateString ValidationQuery1;PrivateString DbUrl2;PrivateString username2;PrivateString Password2;PrivateString driverClassName2;PrivateString ValidationQuery2;@Value("5")Private intInitialSize;@Value("5")Private intMinidle;@Value("a")Private intmaxactive;/*** units are milliseconds     */    @Value("60000")Private intmaxwait;/*** How long does the configuration interval take to detect idle connections that need to be closed, in milliseconds     */    @Value("60000")Private intTimebetweenevictionrunsmillis;/*** Configure a connection in the pool minimum time to live, in milliseconds     */    @Value("300000")Private intMinevictableidletimemillis;@Value("true")Private BooleanTestwhileidle;@Value("true")Private BooleanTestonborrow;@Value("false")Private BooleanTestonreturn;/*** Open Pscache, and specify the size of Pscache on each connection     */    @Value("true")Private Booleanpoolpreparedstatements;@Value(" the")Private intMaxpoolpreparedstatementperconnectionsize;/*** Configure monitoring statistics interception filters, remove post-monitoring interface SQL cannot be counted, ' wall ' for firewalls     */    @Value("stat,wall,log4j")PrivateString filters;/*** Open Mergesql function via Connectproperties property; slow SQL record     */    @Value("druid.stat.mergesql=true;druid.stat.slowsqlmillis=500")PrivateString connectionproperties;@Bean(name ="Primarydatasource")@Qualifier("Primarydatasource")@Primary     PublicDataSourcePrimarydatasource() {return Getdruiddatasource(UserName1, Password1, DBURL1, driverClassName1, validationQuery1); }@Bean(name ="Secondarydatasource")@Qualifier("Secondarydatasource") PublicDataSourceSecondarydatasource() {return Getdruiddatasource(UserName2, Password2, DbUrl2, driverClassName2, ValidationQuery2); }/*/Generate Druiddatasource based on configuration file     */    PrivateDruiddatasourceGetdruiddatasource(string username, string password, string url, String driverclassname, String validationquery) {Druiddatasource DataSource =New Druiddatasource(); DataSource.SetUrl(URL); DataSource.Setusername(username); DataSource.SetPassword(password); DataSource.Setdriverclassname(Driverclassname);//configurationDataSource.setinitialsize(InitialSize); DataSource.Setminidle(Minidle); DataSource.setmaxactive(maxactive); DataSource.setmaxwait(maxwait); DataSource.Settimebetweenevictionrunsmillis(Timebetweenevictionrunsmillis); DataSource.Setminevictableidletimemillis(Minevictableidletimemillis); DataSource.Setvalidationquery(Validationquery); DataSource.Settestwhileidle(Testwhileidle); DataSource.Settestonborrow(Testonborrow); DataSource.Settestonreturn(Testonreturn); DataSource.setpoolpreparedstatements(poolpreparedstatements); DataSource.setmaxpoolpreparedstatementperconnectionsize(maxpoolpreparedstatementperconnectionsize);Try{DataSource.setfilters(filters); }Catch(SQLException e) {logger.Severe("Druid Configuration Initialization filter:"+ e); } datasource.setconnectionproperties(connectionproperties);returnDataSource }}

Primaryconfig.java

@Configuration@EnableTransactionManagement@EnableJpaRepositories(Entitymanagerfactoryref ="Entitymanagerfactoryprimary", Transactionmanagerref ="Transactionmanagerprimary", basepackages = {"com. Xx. XX Please replace with your data source 1Repository directory "}) Public classPrimaryconfig {@Resource    @Qualifier("Primarydatasource")PrivateDataSource Primarydatasource;@Primary    @Bean(name ="Entitymanagerprimary") PublicEntitymanagerEntitymanager(Entitymanagerfactorybuilder builder) {return entitymanagerfactoryprimary(builder).GetObject().Createentitymanager(); }@Resource    PrivateJpaproperties jpaproperties;PrivateMap<string, object>getvendorproperties() {//Configure Hibernate in Yml, so only one empty hibernate setting is required here.         returnJpaproperties.gethibernateproperties(New hibernatesettings()); }/*** Set the entity class location     */    @Primary    @Bean(name ="Entitymanagerfactoryprimary") PublicLocalcontainerentitymanagerfactorybeanentitymanagerfactoryprimary(Entitymanagerfactorybuilder builder) {returnBuilder.DataSource(Primarydatasource).Packages("com. Xx. XX Please replace with your data source 1Entity directory ")                .Persistenceunit("Primarypersistenceunit")                .Properties(getvendorproperties())                .Build(); }@Primary    @Bean(name ="Transactionmanagerprimary") PublicPlatformtransactionmanagertransactionmanagerprimary(Entitymanagerfactorybuilder builder) {return New Jpatransactionmanager(entitymanagerfactoryprimary(builder).GetObject()); }}

Secondaryconfig.java

@Configuration@EnableTransactionManagement@EnableJpaRepositories(Entitymanagerfactoryref ="Entitymanagerfactorysecondary", Transactionmanagerref ="Transactionmanagersecondary", basepackages = {"com. Xx. XX Please replace with your data source 2Repository directory "}) Public classSecondaryconfig {@Resource    @Qualifier("Secondarydatasource")PrivateDataSource Secondarydatasource;@Bean(name ="Entitymanagersecondary") PublicEntitymanagerEntitymanager(Entitymanagerfactorybuilder builder) {return entitymanagerfactorysecondary(builder).GetObject().Createentitymanager(); }@Resource    PrivateJpaproperties jpaproperties;PrivateMap<string, object>getvendorproperties() {//Configure Hibernate in Yml, so only one empty hibernate setting is required here.         returnJpaproperties.gethibernateproperties(New hibernatesettings()); }@Bean(name ="Entitymanagerfactorysecondary") PublicLocalcontainerentitymanagerfactorybeanentitymanagerfactorysecondary(Entitymanagerfactorybuilder builder) {returnBuilder.DataSource(Secondarydatasource).Packages("com. Xx. XX Please replace with your data source 2Entity directory ")                .Persistenceunit("Secondarypersistenceunit")                .Properties(getvendorproperties())                .Build(); }@Bean(name ="Transactionmanagersecondary") Platformtransactionmanagertransactionmanagersecondary(Entitymanagerfactorybuilder builder) {return New Jpatransactionmanager(entitymanagerfactorysecondary(builder).GetObject()); }}

After configuring YML, inject values that are not set in the Druiddbconfig:

Druid:    # Configuration of the first data source    driverClassName1:Com.microsoft.sqlserver.jdbc.SQLServerDriverDBURL1:Jdbc:sqlserver://xxx;databasename=xxxusername1:SaPassword1:123456# test connectivity, must be a single SQL statement that can be executed smoothly.     ValidationQuery1:Select 1# Configuration of the second data source    driverClassName2:Com.microsoft.sqlserver.jdbc.SQLServerDriverDBURL2:Jdbc:sqlserver://yyy;databasename=yyyusername2:SaPassword2:123456# test connectivity, must be a single SQL statement that can be executed smoothly.     ValidationQuery2:Select 1Spring:    # Hibernate configuration    JPA:        Database:Sql_serverGENERATE-DDL:TrueShow-sql:TrueHibernate:            Ddl-auto:Updatenaming:                Physical-strategy:Org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy

Some pits

Be sure to add log4j dependency

The primary key auto-increment policy for entity is @GeneratedValue changed from @GeneratedValue(strategy=GeneratedType.IDENTITY) , because the default method does not work under Druid.

Configuring multiple data sources with Druid in a Springboot2.0 project