Multiple JDBC Clients - How to configure multiple DataSources in Spring

Summary

本视频由Dan Vega主讲，他是Broadcom的Spring开发者代言人。视频主题围绕如何在Spring Boot 3.2中使用JDBC客户端管理多个数据源。Dan从创建一个新的Spring Boot项目开始，逐步讲解了如何配置和使用两个不同的数据源。他详细介绍了每个数据源的设置过程，并展示了如何为每个数据库配置和使用JDBC客户端。此外，Dan还提供了一些代码示例来帮助理解如何在实践中应用这些概念。这个视频是对那些希望在Spring应用程序中管理多个数据库的开发者的有价值的指南。

Takeaways

• 😀 使用Spring Boot 3.2版本可以通过JDBC客户端连接多个数据源。
• 🌐 在Spring Boot中配置单个数据源非常简单，但配置多个数据源需要手动设置。
• 🔧 多个数据源配置涉及创建不同的数据源实例，并指定每个实例的配置属性。
• 🛠️ 使用`@Primary`注解可以指定一个主要的数据源，其他数据源则作为次要数据源。
• 📁 对于每个数据源，需要分别配置其URL、用户名和密码等属性。
• 🗂️ 可以通过`application.properties`文件来分别配置每个数据源的属性。
• 🚀 创建项目时，可以在start.spring.io上选择Spring Web和JDBC API依赖来支持多数据源配置。
• 📚 使用H2数据库进行演示，但实际应用中可以连接到不同类型的数据库。
• 💾 对于每个数据源，可以通过特定的schema.sql文件来初始化数据库模式。
• 👨‍💻 示例项目展示了如何使用JDBC客户端为每个数据源执行CRUD操作。
• 🔍 使用H2控制台可以查看和管理内存中的数据库实例。
• 📖 通过构造不同的数据源Bean和JDBC客户端Bean，可以在Spring应用中灵活地管理多个数据库连接。

Q & A

Dan Vega在视频中介绍了哪个主题？

Dan Vega介绍了在Spring Boot 3.2中使用JDBC客户端处理多个数据源的主题。

Spring Boot中配置多个数据源的挑战是什么？

Spring Boot通过自动配置简化了单个数据源的设置，但当需要配置多个数据源时，必须手动进行配置。

为什么Dan Vega决定在演示中使用两个内存H2数据库？

Dan Vega选择使用两个内存H2数据库来简化演示，便于讲解如何连接到多个数据源。

在项目设置中，为什么需要指定使用Java 21或Java 17？

指定使用Java 21或Java 17是为了确保项目与Spring Boot 3.2及其功能的兼容性。

如何在Spring Boot项目中配置第一个数据源？

在application.properties文件中配置第一个数据源，包括数据库URL、用户名和密码。

为什么需要为不同的数据源创建不同的schema文件？

需要为不同的数据源创建不同的schema文件，以便能够分别管理和初始化每个数据源的数据库结构。

如何在Spring Boot中创建和配置JDBC客户端以连接到特定的数据源？

通过在配置类中定义Bean，并使用@Qualifier注解指定连接到特定数据源的JDBC客户端。

如何验证两个不同的数据源已正确配置并连接到了相应的数据库？

可以通过检查每个数据源的连接信息，例如通过打印数据源的URL，来验证它们是否已正确配置并连接到了预期的数据库。

在这个项目中，'subscriber'和'post'数据模型分别用于什么目的？

'subscriber'数据模型用于保存博客的订阅者信息，而'post'数据模型用于保存博客帖子的内容。

为何Dan Vega强调在YouTube视频中留下问题和反馈的重要性？

Dan Vega强调这一点是因为用户的问题和反馈可以提供有价值的见解，帮助改进内容，并解答社区成员可能有的疑问。

Outlines

00:00

😀 Spring Boot 3.2与多数据源配置

Dan Vega，Broadcom的Spring开发者倡导者，在这段视频中讨论了如何在Spring Boot 3.2中使用JDBC客户端配置多个数据源。这个话题源于YouTube上的一个观众提问，关于如何处理多数据库和多架构的情况。视频首先展示了在start.spring.io创建一个新项目，配置两个不同的数据源，并介绍了如何使用JDBC客户端与它们交互。此外，Dan Vega解释了为什么在配置多个数据源时需要手动设置，以及如何使用Java 21（虽然Java 17也适用于此示例）来创建这个项目。

05:00

🔧 实现Spring Boot中的多数据源

视频的第二部分详细介绍了在Spring Boot应用程序中配置和使用多个数据源的过程。Dan Vega演示了如何通过构造器注入来获取JDBC客户端实例，并创建了基本的CRUD方法来与数据库交云。接着，他通过命令行运行器测试了与第一个数据库的连接，并在遇到一些初始问题后成功解决。此外，他还展示了如何使用Spring Boot的自动配置来创建数据源，并解释了如果需要进行更复杂的配置时该如何操作。

10:01

🌐 构建多数据库订阅者管理功能

在第三段视频中，Dan Vega着手构建一个涉及多数据库的博客应用程序的订阅者部分。他创建了一个新的subscriber记录和服务，同时设置了两个不同的数据库模式。视频中还展示了如何在application.properties文件中为两个不同的数据源设置独立的配置。接着，他创建了一个新的数据源配置类，定义了两个数据库的连接，并讨论了如何使用Spring的配置属性来指定各自的数据库。

15:02

🛠️ 配置和测试多数据源连接

第四段视频深入讨论了如何在Spring Boot应用程序中配置和测试多数据源连接。Dan Vega展示了如何在配置类中为每个数据库创建和配置数据源属性和Hikari数据源。他还演示了如何使用不同的数据源初始化脚本来设置每个数据库的模式。此外，他通过添加标记和限定符来确保每个服务都使用正确的JDBC客户端。最后，Dan运行了应用程序，验证了两个不同的数据源是否正确配置并连接。

20:02

🚀 完成多数据源集成和总结

视频的最后一部分，Dan Vega完成了对多数据源集成的演示，并对整个过程进行了总结。他展示了如何在应用程序中创建两个JDBC客户端，分别连接到两个不同的数据源，并确保了订阅者服务正确地使用了其对应的数据源。他通过打印出从两个数据库中检索到的数据来演示了这一点，并进一步验证了两个数据库连接的正确性。最后，Dan Vega鼓励观众通过多种渠道向他提出问题，并对视频内容做了总结。

Keywords

💡数据源

数据源是应用程序用来连接和访问数据库的配置。视频中配置了两个不同的数据源,一个用于博客文章,一个用于订阅者信息。它展示了如何在Spring Boot中配置和使用多个数据源。

💡JDBC客户端

JDBC客户端是用于访问数据库的组件。视频中为每个数据源配置了对应的JDBC客户端,它们可以独立地访问各自的数据源。

💡自动配置

Spring Boot可以自动配置单数据源。视频中展示了如何手动配置多个数据源以覆盖自动配置。

💡方言

方言指的是不同数据库的特有功能。视频使用了两个H2内存数据库作为示例,它们有相同的方言。

💡Bean

Bean是Spring中的一个对象,视频中配置了多个Bean来定义不同的数据源、JDBC客户端等。

💡 schema

数据库schema,用于定义数据库中的对象如表、视图等。视频中为每个数据源创建了单独的schema。

💡主数据源

当存在多个数据源时,需要定义一个主数据源。主数据源通常用于实体的默认持久化。

💡限定词

由于存在多个相似的Bean,视频中使用@Qualifier限定词来区分不同的Bean。

💡嵌入式数据库

嵌入式数据库H2,它们以内存形式存在。视频中使用H2来模拟多个数据源。

💡元数据

数据库元数据,描述了数据库的结构信息。视频中使用它来验证不同数据源的配置。

Highlights

Dan Vega, Spring Developer Advocate at Broadcom, discusses multiple data sources using the JDBC client in Spring Boot 3.2.

The ease of setting up a data source in Spring Boot due to autoconfiguration.

Introduction to creating a new project on start.spring.io for handling multiple data sources.

Explanation of using Spring Boot 3.2 and JDBC client for the project.

Setting up the project metadata and selecting dependencies on start.spring.io.

Creation of a simple blogging application as an example to demonstrate handling multiple data sources.

Configuration of the application.properties file for the first data source.

Introduction and setup of the JDBC client for database operations.

Demonstration of inserting and retrieving data from the first database.

Explanation of creating and configuring a second data source for subscribers.

Discussion on the necessity of distinct configurations for multiple data sources in Spring.

Demonstration of the separation and management of two different schemas for the databases.

Setting up and using the JDBC client for the second data source.

Explanation of overriding default data source configurations for multiple databases.

Verification of the application's connection to two separate databases and the retrieval of data from both.

Encouragement for viewers to ask questions and the availability of different platforms for discussion.

Transcripts

welcome back everyone Dan Vega here

spring developer Advocate at broadcom

today we're going to be talking about

multiple data sources using the jdbc

client in Spring boot 3.2 now recently I

made a video on jdbc client versus

spring data jdbc and I got a lot of

really great feedback so first off

thanks for that uh but today we're going

to answer one of those questions and

this question comes from YouTube comes

from Abraham says first of all thank you

very much for sharing this video it was

very help ful I wonder what would be the

solution for jdbc client if we had

multiple databases and multiple schemas

and this is an interesting question

right because we know that uh to get a

data source up and running in Spring

boot is pretty trivial because of

autoconfiguration we see some things we

configure some things and you have a

database up and running as soon as you

need to configure something though we

kind of back off and let you configure

it as needed and that's what happens

when you need to configure fure multiple

data sources in spring so we're going to

take a look at an example of this today

we're going to start a new project over

at start. spring.io we'll set up a

project that talks to two different data

sources and we'll see how we can

configure those data sources and then

use a jdbc client for each of those so

uh I hope you learned something new

today with that let's get to it all

right so we're going to start here at

start. spring.io we're going to create a

new Maven project you need spring boot

3.2 because we're going to be using the

jtbc client but I guess this would be

the same if you were before 3.2 and you

wanted to use multiple data sources for

something like the rest template so I'm

going to fill in some metadata here I'm

going to say dev. danan Vega we'll say

the artifact is

multiple DS

demo and we're going to choose Java 21

you could probably use Java 17 for this

example I'm going to go ahead and pick

spring web I'm also going to pick

uh jtbc's API so jdbc API that will

bring in the jdbc support that has like

the jdbc template and what you need to

connect to a data source and then I'm

just going to use an H2 database here uh

I contemplated using both the like a

postest database and an H2 I think I'll

simplify it for this one uh just so we

can take a look at talking to multiple

data sources so we'll use uh two

different inmemory H2 databases and

that's really all we need to get this

project up and running I'm going to go

ahead and click generate this will

download a zip you can open it up in

whatever ID you're most productive in

I'm going to open it up in tell J idea

and with that let's go ahead and write

some code all right so here we are in

our application this is going to be a

simple blogging application we're going

to use that same uh example to kind of

walk through this we're going to talk to

uh a single data source first let's set

up that one this is going to be used for

like saving posts the second data source

will be for maybe saving subscribers to

our blog and so that will use a separate

database but before we get into any of

that let's just get that first one out

of the way so I'm going to go ahead and

create a new package here I'm going to

call this post inside of there I am

going to create a new post record so I'm

come in here and say post record and

this is going to have let's just paste

this in here an ID title slug date time

to read and and tags all right so we're

going to go into application. properties

here and set up that first data source

so I'm going to go in here and say

spring. datas source. URL is going to be

equal to

H2 sorry H2 memory we'll call this uh

blog so I have my first data source and

then I need a username of SE essay and a

password of nothing now normally you

don't need to do this you can actually

just change uh the generate so I might

come in here and say say

spring dot generate unique name equals

the false and then you can set the name

I'm doing this because we're going to

see some uh distinction between the

first one and the second one when we get

to adding that second database so I have

that in place I'm going to need a schema

so I'm going to come in here and create

a new resource now normally I would just

name this schema.sql but because we have

two different schemas here I'm going to

be a little bit more explicit I'm going

to go ahead and call this the blog

schema.sql and I'm going to go ahead and

insert some code here this is just

creating a new table called post with

those columns in it I'm also going to

insert a single Post in there this is my

Hello World post and if we go ahead and

save that we should be good finally I'm

going to need a way to read and persist

data to the database I'm going to go

ahead and create a post service this is

a class this is going to be marked with

the at service annotation

we are going to get an instance of the

jdbc client we'll call this jdbc client

we'll get it through Constructor

injection and then we just need a couple

of methods to be able to uh access and

persist data to and from the database so

I've done uh videos on this in the past

even in the last one we took a look at

it I'm not going to get into kind of how

the jdbc client is working we're going

to talk more about how to configure

multiple ones of these so I have some uh

basic cred methods in here find all find

by ID create update and delete so with

that I think we have everything we need

to uh at least test out this initial

connection to the first database so if I

go into

application I can go ahead and create a

new command line Runner so I'm going to

say Bean command line Runner command

line Runner and then we are going to

return

args co-pilot doing its thing that's not

what I want

um and then there so I need to get an

instance of that post

repository uh post repository did I not

I'm sorry Post Service Dan come on you

got this post service Post Service uh

and now we can go ahead and use that so

all I want to do is get out all the

posts so post service. findall go ahead

and print them out and we should be able

to go ahead and run this application uh

if everything works we should see them

and we did not so let's figure out

what's going on uh URL must start with

jdbc did I not start with jdbc I did not

so gdbc colon H2 in memory blog let's

try that again all right we got another

error here table post not found let's

find out what's going on with our schema

drop table if exists posts create our

table posts insert into post create

table post that looks fine uh insert

into

post that looks okay okay so um we are

going to have to name this schema for

now I forgot in my Infinite Wisdom here

we need to pick this up by convention we

will rename it later so that we can be

distinct and have two separate schemas

that's what I have it named in the final

uh code that of course you can find in

the description below but that post

table was not there because we weren't

reading a schema to go ahead and set

that up so let's see if we can go ahead

and do that and now we get our single

post that we are um outputting there to

the command line all right so we know

that that works but I want to kind of

point out one other thing here so I'm

going to create another Bean I'm going

to create another command line Runner

we'll call this the DS command line

Runner uh stands for data source so

we're going to return args I don't want

to print anything

out so let's just say that and what I'm

going to do is I'm going to get access

to the data source so we talked about

this in the previous video if you want

to kind of go back and look at that you

can kind of the history of being able to

connect to a database in Java in Spring

what that means what do you need to make

that happen and one of those things that

you need is a data source now you would

normally have to create this on your own

in an application like Java but in

Spring Boop because we use Auto

configuration we see that you have uh H2

on the class path uh there's the jwc API

there um and because of that we will go

ahead and create a data source for you

this uh reads the properties from

application. properties it knows how to

connect to a database now we create that

data source be for you uh this is great

but if you have to uh go ahead and do

some configurations you might have to

create your own data source or in this

case create two different data source

beans to talk to two different data

sources but I just want to show you the

original data source here uh this is the

data source that uh was created by

Spring and as you can see copilot is on

its game today I want to just print out

the connection info so get connection.

metadata. G.G URL will tell us exactly

where this database is connecting to and

if we look at this we can see of course

we know this but jwc H2 in memory blog

this going to be important because what

I want to do is over basically override

the connection to the database we can

create our own data source Bean so we

can then create a second data source

beam uh this is going to be important

I'll come back to this method so we can

actually get access to both data sources

and see that the connection URLs are

indeed pointing to the right database

all right so with that let's go ahead

and reconfigure this uh to use our own

data source uh and then we can go ahead

and add a second data source all right

so let's close some of this down I'm

going to introduce a new package here

let's go ahead and call this

subscriber we're going to create a new

uh subscriber record in here we'll call

this subscriber we'll say this is a

record I'll go ahead and paste some info

in here we'll go ahead and create a

subscriber service so

subscriber service we'll this will be a

class we'll mark this with that service

and then we'll come back to this in a

second so we're going to need to um

basically update this I want to rename

this to kind of what I was talking about

before we're going to have a Blog

specific schema so blog. schema.sql and

then I'm going to introduce a new schema

here so uh

subscriber

schema.sql and now we have uh two

different two different uh schemas for

our application I'm going to go ahead

and paste this in in and there we go so

we have ID name email and the primary

keyb and ID so we'll insert into

subscriber we're just going to insert

one single record that will give us

enough to kind of uh give some

distinction between the two so now we in

the application. properties we currently

have the spring. DAT Source now spring.

dat source is how we configure a single

data source in our application but we're

going to need to take a different

approach here if we want to go ahead and

set up multiple data sour our es so I'm

going to go ahead and paste in my

properties and we'll walk through them

first off I'm going to enable the H2

console we want to be able to take a

look at the database and see everything

and then I'm going to set up two

separate connections to two different

databases and I'm going to use my own

properties here I'm going to say app.

dat source and then I'm going to name

each database so this could work for

three or four if you wanted to but here

we have two data sources the first one

is blog same properties as before um

username and password uh actually we

want to

say let's say we'll probably need two

different connections ah actually let's

just leave it at let's say I say

blog I say subscriber and then no

password for each of them so we have two

separate connections here but if we go

ahead and run the application now

nothing's going to happen right we're

still we we see H2 on the class path in

fact uh we're not defining anything

under spring. data source so a randomly

generated uh ID for the data source will

get created and no schema will get

picked up and things just won't work yet

so how do we go ahead and make this

happen all right so what we need to do

in our main package here is create a new

configuration class I'm going to call

this uh let's call this data source

configuration this is of course going to

be a configuration class I'm going to

proxy Bean methods equals false and we

can are off and running so what we need

to do is Define our different

connections to our different database so

I'm going to start here with our blog

and we need to go ahead and set this up

so I'm going to create a new Bean now if

we're going to create two different data

sources we need to mark one as primary

this is the primary data source and we

need to say where is it going to get its

configuration properties from now

remember we just Define that in app.

datas source. blog so we have that here

uh what we're going to do is we're going

to say yep copot you are on your money

here um so data source properties is how

we kind of fill out the data source

properties and we're going to return new

data source properties but again those

are getting pulled from app. datas

source. blog right so we we have our

properties but we need an actual

connection to the database now so we're

going to create one more Bean we'll go

ahead and say that this is the primary

and we are going to use a data

source I'm sorry

whop we are going to say this is a

Hikari

Hikari data source and this is going to

be called blog data source let's see

co-pilot you are saying data source

properties that's the blog data source

properties this looks pretty good let me

just double check it yep so we're just

giving it a type we're building it and

we're returning it so this will create

our data source now there's one more

step needed because we are using kind of

a special schema right we renamed it we

basically want to create this data

source and point it to that schema so it

can get initialized if we weren't using

a separate schema and we were using like

something like spring data jpu a where

that schema was created for us we could

probably skip this step step but we need

to do this here so I'm going to say app

Bean this is a data source script uh is

that database initializer yes um I think

you are on your game today co-pilot and

again this is kind of pulling from a

preious previous project as I as I set

this tutorial up so that's what's

happening here so we're going to create

some new settings we're going to say hey

go ahead and look for that schema

location in the class path because it's

under Source main resources look for

something called blog schema.sql go

ahead and uh set the mode to embedded

because that is what we are using and

then this is we don't want to do this

here we actually just want to call this

data source and let's get this through

here and data source now the problem

with this is going to be that at the end

of the day we might have two two data

sources right we only have one right now

but we're going to end up having two so

I need to be very specific with what

data source this is and all I'm going to

do is say blog data source so now it

knows exactly which one so that gets

wired in here automatically for us and

then we could pass it to our data source

script initializer which just takes the

data source and what settings really the

settings here is just hey this is

embedded this is the schema where I want

you to look so with that being said um

what are we

what's oh

I'm we want configuration that's why

that didn't work so with that said I

think we can um go ahead let's see here

let's go ahead and try and run our

application and see if it still works as

it did before and it does so we still

have our um blog post and we are still

using our uh connection to our inmemory

blog database so great that is data

source one now we can start to Define

data source 2 which is our subscribers

so this is going to look pretty similar

I'm just going to say public data source

properties uh we'll say data source

properties actually let's say subscriber

data source properties this is going to

be a new data source properties and just

like before we need to go ahead and say

that this is a bean and where are we

going to pull remember we did this

before with at configuration properties

we are pulling this from app.at source.

subscribers that is where that's coming

from all right next we need to create

another Bean this is going to be the

actual data source so this will return

I'm just going to show you a different

way of doing this um let's see

subscriber data source this looks pretty

good uh subscriber data source so the

qualifier again because we have multiple

data source properties now we need to be

specific we are going to say hey uh the

subscriber data source properties is

what you want to use here and then we're

going to use a new data source Builder

this is just a different example of a

way to do this uh looks like I

misspelled that uh so we're going to

pass in the URL uh and we're going to

get the URL from those subscriber

properties the the username and the

password again just a different way of

building a data source and this one it's

a a hiari data source this is just a

plain data source so just two different

ways to do that here so finally we need

to do the same thing as we did before we

want a data source script database

initializer because we want to go ahead

and use a different schema for this

database so here we're going to go ahead

and make sure we have that qualifier

again this is the subscriber data source

and we're going to set our uh schema

location to the class CL path of

subscribers. schema let me just make

sure I made that plural I did not so

let's go ahead and refactor this so it's

the same as what I have in the GitHub

repository so let's go ahead and

refactor that uh we're saying

subscribers D schema.sql

blog. schema.sql yes okay so so far so

good this is exactly what the data

source configuration is going to look at

look like

but now we need to do a couple of things

right so first off back in our post

service we have this jdbc client we need

to be very specific with what jdbc

client this is so here I'm going to add

a qualifier and I'm going to say this is

the blog gdbc client which I don't think

we've created yet so let's do that so

let's go back over to our application

and let's just create each of these I'm

going to create a new Bean again this

can go in any configuration class I'm

kind of doing this for Simplicity sake

in the form of a demo but uh yeah you

can do this anywhere so this is going to

be a jdbc client because again this is

similar to the data source when when you

only have a single data source we create

an instance of the jtbc client that uses

that default data source if you have

multiple data sources you need to be

very specific we need to create our own

jdbc client and tell it which database

it's going to use so we're going to

create a couple of jdbc clients here one

is going to be the jdbc client here

we're going to pass in a qualifier again

we want to be very specific we're saying

blog data source and the way that you

create a new jdb jdbc client is by using

this static Factory method here called

create and then you pass in a data

source so we want to create a second

Bean we this will be of jdbc client

we'll call this the subscriber jdbc

client right I just want to make sure

it's singular yes we're going to add a

qualifier here called subscriber data

source now we have two days data sources

here and then we're going to call the

create method and pass in that data

source so it looks like we're getting

there we're almost there um back in our

subscriber service we don't have

anything here yet we'll do something

similar we'll get a jdbc client we'll

have the qualifier for the correct one

in my findall method all I'm doing is

selecting ID name and email from

subscriber and I'm turning that into a

list we know that in the schema there's

only one so we only have a single row

there uh but that's okay let's take a

look I'm getting some some little

squigglies here telling me something's

not

right and that's because this is at Bean

Dan okay that's a little bit better so

now back in my application I'm going to

do a couple things one I want to get

both data sources here I want to say at

qualifier is the blog data source right

so let's call this blog data source now

I can change this to blog data source

and I'm going to get the other one as

well which is the subscriber data source

so that down here I can basically say uh

what is the subscriber data source get

that connection and get the metadata URL

so let's rerun this and see if those two

URLs point to the right database and

they do so what this is telling me right

now is we've successfully created the

appropriate beans to connect to multiple

data sources so to round this kind of

demo out we have this post service I'm

going to get my subscriber service call

this subscriber

service why did it erase that and what I

want to do is get all the subscribers so

I'll call subscriber service. findall

and we'll go ahead and print those out

and let's see what that looks like and

there we go so we've printed out all our

posts we printed out all our subscribers

we have connections to two different

databases and if you wanted to kind of

examine this further the H2 console is

enabled so you can go over to the

browser and take a look at each database

separately if you wanted to all right

and there you have it how to connect to

multiple data sources in Spring and then

use the jdbc client for multiple data

sources I thought that was a really

interesting question I love getting

questions like that so hey if you're

watching a YouTube video like this one

and you have questions please leave them

in the comments below you can also find

me at danve gmail.com you can find me on

Twitter Spring Office hours.i is our

podcast so go ahead and check that out

there are a bunch of different ways you

can give me questions and I'm trying to

find a good way to kind of wrangle those

questions into a single place where I

can get to them quicker so please bear

with me if you do with that if you learn

something new today friends do me a big

favor give me a thumbs up on this video

subscribe to the channel and as always

happy

coding