Instructor: Hey.
In this video, we're going to learn how to set up Tomcat
for database connection pooling.
So we'll start off with the following topics.
We'll discuss database connections in web apps.
We'll also learn about database connection pools.
Then we'll go through the step by step process
of setting up a pool in Tomcat,
and finally we'll pull it all together
by testing our database connection pool configurations.
So a lot of great stuff in this video.
Let's go ahead and get started.
All right.
So whenever you need to connect to a database
using a web application,
you may think that you only need
a single database connection.
Unfortunately, this will not scale for multiple users.
This is very similar to, say for example,
you have a large number of users,
and they all want to make use of one telephone,
so your office only has one telephone,
and you have 40 or 50 people,
and they need to make use of the telephone
to make a phone call.
So this wouldn't work out.
You'll have a long queue,
and people will wait to actually use the telephone.
So we need to have a solution for this.
So the best practice in the industry
is to make use of database connection pools.
This will allow your app to scale
and handle multiple users quickly.
So for all of those users
who need to make use of our application
and our Tomcat server will set up a
pool of connections.
So like a pre-configured pool of connections
that they can use, and once they're done with it
they can put it back into the pool,
and another user can reuse it.
So again, going back to our telephone analogy,
instead of simply having one phone in our office,
now our office may have 30 phones or 50 phones
for the various users to come in.
This will give us high throughput and allow us
to handle more users in a shorter period of time.
Okay, so how do we set up a connection pool?
And you know how I love to do lists.
I love step by steps.
So the first thing we need to do is download
the JDBC Driver JAR file.
Then once we have that, the next step
is setting up the connection pool
in our context.xml file.
Then step three, we'll define our resource reference
in our web.xml file.
And then finally once everything is configured,
then we can pull it all together in step four,
and we can get a connection pool reference
in our Java code.
And I'll actually go through this step by step.
I'll show you how to work through it,
and we'll get everything configured,
and we'll test everything at the end.
So stay with me.
All right, so let's take a look at step one,
downloading the JDBC Driver JAR file.
In order to connect to a database,
you're going to need to have a JDBC Driver
for that given database.
So here we're going to make use of MySQL in the course,
so I can go through the URL listed here, mysql.com,
and this will allow us to download the driver JAR file.
Once we have that JAR file downloaded,
then we can add it into a special directory
in our web application called WEB-INF/lib.
This is a special directory
where you place any supporting JAR files
or any supporting libraries
that your application will need.
So in this case, we need to connect to a database,
so we'll place our JAR file
inside of the WEB-INF/lib directory,
and I'll show you that in the videos coming up.
All right.
Step two, defining the connection pool.
So we'll actually define the connection pool
inside of this file call context.xml.
This is a Tomcat specific file
that basically tells Tomcat
how to connect to your database,
and it also tells Tomcat
how to configure your pool,
such as how many connections to create up front,
the minimum number of connections,
so on and so forth.
So let's take a look at this file.
It's in a directory WebContent/META-INF/
and the filename is context.xml.
So it's just a small XML file.
We have a resource reference here in the middle.
So the first thing you do is you give the actual
name of the resource, so here we have jdbc/student_tracker.
The name is basically like the alias
that we'll use later on in our application
to look up this connection pool.
So name is similar to alias.
On the next line here,
we tell Tomcat how we're going to authenticate.
So auth="Container" meaning that the Tomcat server
will actually handle the authentication.
Next we have type="javax.sql.DataSource".
That's the actual name of the Java interface
that we'll use for
communicating with the pool.
So in the Java world, whenever you see DataSource,
just think connection pool.
All right, so they use this generic term DataSource,
but effectively, that's the name of your connection pool.
Okay, on the next line here,
we discuss the actual size of the connection pool.
So maxActive of 20, so we're going to have
20 connections in our pool to start out with.
maxIdle is five, so you know
if there are no users in our system,
we'll have at least five connections available.
And then the maxWait is simply a time in milliseconds
as far as how long someone can wait
before they get a connection.
So right here it's 10,000 milliseconds,
which translates over into 10 seconds.
All right, so that's kind of the size configuration stuff.
On the next line here, or actually the next three lines,
we tell Tomcat how to connect to our database.
So we give it the username and password.
So username of demo, password of demo.
If you remember, that's the special MySQL account
that we created earlier for our application.
So I think maybe two or three videos ago,
we ran a little SQL script that would create
a user called demo with a password of demo.
So those are our credentials.
Next we give the actual driverClassName,
so the fully qualified name of the JDBC Driver,
so we're using MySQL here.
And then finally, we give the url to the database
so our database is running on Localhost,
and the actual name of the database schema
is called student_tracker,
and again, we ran some SQL code earlier
to create that database with the schema
and tables and so on.
So basically again, this table,
I mean sorry, this file here tells us
how to connect to MySQL and also how to configure the pool
as in regards to the actual size
or the number of connections.
Now our next step here is step three,
defining the reference in web.xml.
So this little snippet of configuration that will
expose this DataSource to our given web application.
All right, so this is in the file
WebContent/WEB-INF/web.xml.
All right, so we'll define our resource reference.
So you can give a simple description,
the resource reference name.
That's very important, jdbc/student_tracker.
That's the same name that was defined
in the previous slide on step two.
So whatever name you gave on step two,
that's the same name that you use here
'cause we'll use this to actually look up
or get a handle to that connection pool.
Resource type, that's the actual type of the object
that's available in the connection pool.
So again, whenever you see DataSource,
think connection pool.
And as far as the resource authentication,
that's going to be handled by the container,
so Tomcat in the previous slide it has the configs on how to
connect to the container.
I'm sorry, connect to the database
and provide the username and password.
So this is kind of very standard stuff
that you'll use in your web.xml file.
The only thing that's unique is the resource reference name.
You give the same name of the pool
that was defined in step number two.
And we'll see all the code for this
when we go through Eclipse.
All right, so we're almost done here.
This is the final step, step four,
getting the connection pool in our Java code.
Now, actually we have two different options
on how to get the connection pool.
Option one is making use of a feature
called resource injection.
You can use this with Servlets
and some other special Java EE objects.
And option two is that we can make use of
the Java Naming and Directory Interface, or JNDI.
The nice thing about this training class is that
you'll actually see both examples.
So I'll show you option one,
and then we'll also make use of option two.
And we'll get into the code for that
once we move into the Eclipse tool.
All right, so let's go ahead and move into Eclipse.
The first thing we want to do is import the project.
So when the source code download,
I have this JDBC source code directory,
and I have different versions of the application.
So just go to File, and select Import.
And choose the option here,
Existing Projects into Workspace.
And go ahead and click on Next.
And now we need to tell Eclipse the root directory
to import a project.
So I'll just choose Browse,
and I'll move over to that directory.
So in this course jsf-for-beginners,
you should have downloaded this
file called jdbc-soure-code of this directory.
And that's where we had our SQL scripts before.
Here we have this, the different versions
of the application.
We're going to start with version one for right now.
So I want you to select version-1-list-students.
Just select that directory.
And then go ahead and click on Open.
And what Eclipse will do is it'll see,
hey there's a project in there called jdbc-student-tracker.
So that looks good.
I'll also go ahead and kind of expand the window
so just so you can see the full path.
So again, under jdbc-source-code,
and then version-1-list-students,
and your window should mimic mine
as far as the last couple of directories anyway.
All right, so that's good.
Go ahead and hit Finish.
Okay great.
So it'll go ahead and pull in the project.
It'll go through some validations
and all this other good stuff
as far as building.
However, just to give you a heads up,
you may have some minor warnings or some errors
when you open the project initially,
and I'll show you how to resolve any of those
minor project settings.
So just select the project.
Just do a right click and choose Properties.
That's the first, actually that's the last option
at the bottom.
And once you're in the properties here,
make sure you move over to Java Build Path,
and this is where you may see
some of the items or issues here,
and the one area where you may see an issue
is on the JRE System Library
because you may have a different version of Java
on your system than I do.
So all you have to do is just highlight that item,
hit remove, and then you can go ahead and add your library.
So click Add Library.
And then you select the item here, JRE System Library.
Click Next, and then you can choose whichever one you have
on your local computer.
And then that should resolve the JRE System Library problem.
Now if you're using a different version of Tomcat
like maybe Tomcat 7 or whatever,
you may have issues there.
Just repeat the process.
But once you're all done,
you can go ahead and hit OK.
And that should resolve any issues.
And if you have any further problems,
just contact me on the discussion forum,
and I can help you out with that.
All right, so let's go ahead and look at our step two here,
defining the connection pool and context.xml.
I skipped over step one for the driver.
I'll come back to that in a second.
So let's go with context.xml or step two.
So again, remember this context.xml file
basically defines our connection pool,
so it gives us the name of the connection pool here,
jdbc/student_tracker.
We tell him that we're going to use container authentication
and type of DataSource.
We set up the size of the connection pool
as far as maxActive, idle, and so on.
We set up our database connection information
as far as our username and password.
We give our JDBC driverClassName,
and finally we give the url.
So that's the basic information, the same stuff that we saw
in the slides a little bit earlier.
And just as a short note, if you don't know much about JDBC,
I have a free course on Udemy
that you can take, so simply search Udemy
for the keyword JDBC.
You'll find my free course,
and I give you all the details on how to use JDBC.
All right, so moving ahead here with our driver file
that I kind of skipped over,
but that's our driver JDBC file.
Normally you would download from the MySQL website.
I have already done this work for you,
but here remember the driver files
go into the WEB-INF/lib directory,
and it's our mysql.jar file.
So if you have a new project, you can download it,
or you can simply copy this file
and put it into your new project.
All right, so step three defining the reference in web.xml.
So web.xml is simply a configuration file.
We use this file to get a reference or a handle
to that connection pool that was defined in Tomcat.
And again, the most important thing here
in this resource reference is line 27,
the resource reference name.
We have to make sure we use the same name
of the connection pool that was defined
in that previous file.
So here it's called jdbc/student_tracker.
And this will allow, again this will allow
our web application to look up a connection pool
and use it in our web code.
Okay great.
So that takes care of the web.xml.
Now what I have is a very simple test harness
just to test this out.
I have some Java code to make sure
that all of our configs are in place
and that Tomcat can connect to the database.
So under Java Resources, I have this file here
called TestServlet.java.
So this is our step four, getting a connection.
So a servlet is simply a piece of Java code
that will do some work on the server side.
So on lines 20 and 21, I'll make use
of that option one resource injection.
I inject the resource named jdbc/student_tracker.
It'll give us this object caled dataSource.
So this dataSource is effectively our connection pool.
So on line 34, I actually get a connection from the pool.
I set up a SQL statement,
and then I execute that SQL statement,
and then I simply process the result set.
So in this result, in this little loop here,
I'm simply going to get the email address
of each one of the students from the database.
All right, so just very simple code here.
Not going to spend too much time on that.
Again I just want to test and make sure
that this actually works.
So I'll just do a right click on this servlet,
and I'll choose Run As, and I'll say Run on Server.
And once this is up and running,
then we should see some output here.
So this servlet's just generating output for the browser,
and so here I get a list of five email addresses.
These are the five students that I have
in my database.
So we have a success factor here.
We're successful.
We know that our connection pool is configured properly,
and we know that our Java code can get that connection pool
and in turn, send queries to the database and get results.
So this was very plain vanilla, simple test harness.
What we're going to do later in this video series
is that we're going to make this look pretty.
So when I add JSF, we're going to add tables,
we're going to add style sheets, and so on and so forth,
but this was mainly just to prove the point that hey,
our configs are set up properly, and hey,
we can connect to the database, so good job so far.
All right, great stuff.
So let's go ahead and wrap up.
So here we discuss database connections in web apps.
We also discuss connection pools,
how to set up the pools in Tomcat,
and finally we ran a little Java servlet
to test our connection pool.
All right, so this wraps up the video
of setting up Tomcat database connection pool.
In the following videos,
we'll get into all the low level JSF work,
and we'll integrate all of this together,
so good job so far, and stay tuned.