WEBVTT 1 00:00:01.404 --> 00:00:02.249 Hey. 2 00:00:02.249 --> 00:00:05.127 In this video, we're going to learn how to set up Tomcat 3 00:00:05.127 --> 00:00:07.794 for database connection pooling. 4 00:00:09.906 --> 00:00:12.165 So we'll start off with the following topics. 5 00:00:12.165 --> 00:00:15.068 We'll discuss database connections in web apps. 6 00:00:15.068 --> 00:00:18.326 We'll also learn about database connection pools. 7 00:00:18.326 --> 00:00:20.532 Then we'll go through the step by step process 8 00:00:20.532 --> 00:00:23.432 of setting up a pool in Tomcat, 9 00:00:23.432 --> 00:00:26.193 and finally we'll pull it all together 10 00:00:26.193 --> 00:00:30.183 by testing our database connection pool configurations. 11 00:00:30.183 --> 00:00:32.400 So a lot of great stuff in this video. 12 00:00:32.400 --> 00:00:34.983 Let's go ahead and get started. 13 00:00:37.753 --> 00:00:38.619 All right. 14 00:00:38.619 --> 00:00:40.800 So whenever you need to connect to a database 15 00:00:40.800 --> 00:00:42.264 using a web application, 16 00:00:42.264 --> 00:00:43.881 you may think that you only need 17 00:00:43.881 --> 00:00:45.708 a single database connection. 18 00:00:45.708 --> 00:00:48.526 Unfortunately, this will not scale for multiple users. 19 00:00:48.526 --> 00:00:51.402 This is very similar to, say for example, 20 00:00:51.402 --> 00:00:53.350 you have a large number of users, 21 00:00:53.350 --> 00:00:55.799 and they all want to make use of one telephone, 22 00:00:55.799 --> 00:00:58.174 so your office only has one telephone, 23 00:00:58.174 --> 00:00:59.983 and you have 40 or 50 people, 24 00:00:59.983 --> 00:01:01.588 and they need to make use of the telephone 25 00:01:01.588 --> 00:01:03.037 to make a phone call. 26 00:01:03.037 --> 00:01:04.301 So this wouldn't work out. 27 00:01:04.301 --> 00:01:05.589 You'll have a long queue, 28 00:01:05.589 --> 00:01:09.756 and people will wait to actually use the telephone. 29 00:01:12.155 --> 00:01:15.217 So we need to have a solution for this. 30 00:01:15.217 --> 00:01:17.163 So the best practice in the industry 31 00:01:17.163 --> 00:01:20.159 is to make use of database connection pools. 32 00:01:20.159 --> 00:01:22.154 This will allow your app to scale 33 00:01:22.154 --> 00:01:24.738 and handle multiple users quickly. 34 00:01:24.738 --> 00:01:26.260 So for all of those users 35 00:01:26.260 --> 00:01:28.290 who need to make use of our application 36 00:01:28.290 --> 00:01:30.552 and our Tomcat server will set up a 37 00:01:30.552 --> 00:01:32.836 pool of connections. 38 00:01:32.836 --> 00:01:35.167 So like a pre-configured pool of connections 39 00:01:35.167 --> 00:01:37.398 that they can use, and once they're done with it 40 00:01:37.398 --> 00:01:39.148 they can put it back into the pool, 41 00:01:39.148 --> 00:01:40.796 and another user can reuse it. 42 00:01:40.796 --> 00:01:43.615 So again, going back to our telephone analogy, 43 00:01:43.615 --> 00:01:46.733 instead of simply having one phone in our office, 44 00:01:46.733 --> 00:01:50.478 now our office may have 30 phones or 50 phones 45 00:01:50.478 --> 00:01:52.418 for the various users to come in. 46 00:01:52.418 --> 00:01:54.611 This will give us high throughput and allow us 47 00:01:54.611 --> 00:01:58.405 to handle more users in a shorter period of time. 48 00:01:58.405 --> 00:02:00.848 Okay, so how do we set up a connection pool? 49 00:02:00.848 --> 00:02:02.405 And you know how I love to do lists. 50 00:02:02.405 --> 00:02:03.853 I love step by steps. 51 00:02:03.853 --> 00:02:05.697 So the first thing we need to do is download 52 00:02:05.697 --> 00:02:08.160 the JDBC Driver JAR file. 53 00:02:08.160 --> 00:02:10.027 Then once we have that, the next step 54 00:02:10.027 --> 00:02:11.930 is setting up the connection pool 55 00:02:11.930 --> 00:02:14.307 in our context.xml file. 56 00:02:14.307 --> 00:02:17.683 Then step three, we'll define our resource reference 57 00:02:17.683 --> 00:02:20.048 in our web.xml file. 58 00:02:20.048 --> 00:02:22.276 And then finally once everything is configured, 59 00:02:22.276 --> 00:02:24.446 then we can pull it all together in step four, 60 00:02:24.446 --> 00:02:26.790 and we can get a connection pool reference 61 00:02:26.790 --> 00:02:28.331 in our Java code. 62 00:02:28.331 --> 00:02:30.375 And I'll actually go through this step by step. 63 00:02:30.375 --> 00:02:32.135 I'll show you how to work through it, 64 00:02:32.135 --> 00:02:33.520 and we'll get everything configured, 65 00:02:33.520 --> 00:02:35.641 and we'll test everything at the end. 66 00:02:35.641 --> 00:02:36.974 So stay with me. 67 00:02:39.607 --> 00:02:41.865 All right, so let's take a look at step one, 68 00:02:41.865 --> 00:02:45.081 downloading the JDBC Driver JAR file. 69 00:02:45.081 --> 00:02:46.832 In order to connect to a database, 70 00:02:46.832 --> 00:02:48.885 you're going to need to have a JDBC Driver 71 00:02:48.885 --> 00:02:50.569 for that given database. 72 00:02:50.569 --> 00:02:53.515 So here we're going to make use of MySQL in the course, 73 00:02:53.515 --> 00:02:56.890 so I can go through the URL listed here, mysql.com, 74 00:02:56.890 --> 00:03:00.441 and this will allow us to download the driver JAR file. 75 00:03:00.441 --> 00:03:02.923 Once we have that JAR file downloaded, 76 00:03:02.923 --> 00:03:05.011 then we can add it into a special directory 77 00:03:05.011 --> 00:03:08.700 in our web application called WEB-INF/lib. 78 00:03:08.700 --> 00:03:10.240 This is a special directory 79 00:03:10.240 --> 00:03:12.824 where you place any supporting JAR files 80 00:03:12.824 --> 00:03:14.743 or any supporting libraries 81 00:03:14.743 --> 00:03:16.401 that your application will need. 82 00:03:16.401 --> 00:03:18.513 So in this case, we need to connect to a database, 83 00:03:18.513 --> 00:03:19.836 so we'll place our JAR file 84 00:03:19.836 --> 00:03:22.122 inside of the WEB-INF/lib directory, 85 00:03:22.122 --> 00:03:26.039 and I'll show you that in the videos coming up. 86 00:03:27.386 --> 00:03:28.269 All right. 87 00:03:28.269 --> 00:03:30.551 Step two, defining the connection pool. 88 00:03:30.551 --> 00:03:32.806 So we'll actually define the connection pool 89 00:03:32.806 --> 00:03:36.283 inside of this file call context.xml. 90 00:03:36.283 --> 00:03:38.673 This is a Tomcat specific file 91 00:03:38.673 --> 00:03:40.530 that basically tells Tomcat 92 00:03:40.530 --> 00:03:42.467 how to connect to your database, 93 00:03:42.467 --> 00:03:44.203 and it also tells Tomcat 94 00:03:44.203 --> 00:03:45.761 how to configure your pool, 95 00:03:45.761 --> 00:03:48.257 such as how many connections to create up front, 96 00:03:48.257 --> 00:03:50.053 the minimum number of connections, 97 00:03:50.053 --> 00:03:51.712 so on and so forth. 98 00:03:51.712 --> 00:03:53.648 So let's take a look at this file. 99 00:03:53.648 --> 00:03:57.686 It's in a directory WebContent/META-INF/ 100 00:03:57.686 --> 00:04:01.072 and the filename is context.xml. 101 00:04:01.072 --> 00:04:02.999 So it's just a small XML file. 102 00:04:02.999 --> 00:04:05.413 We have a resource reference here in the middle. 103 00:04:05.413 --> 00:04:07.644 So the first thing you do is you give the actual 104 00:04:07.644 --> 00:04:12.164 name of the resource, so here we have jdbc/student_tracker. 105 00:04:12.164 --> 00:04:14.657 The name is basically like the alias 106 00:04:14.657 --> 00:04:16.943 that we'll use later on in our application 107 00:04:16.943 --> 00:04:18.765 to look up this connection pool. 108 00:04:18.765 --> 00:04:21.861 So name is similar to alias. 109 00:04:21.861 --> 00:04:23.358 On the next line here, 110 00:04:23.358 --> 00:04:26.084 we tell Tomcat how we're going to authenticate. 111 00:04:26.084 --> 00:04:29.143 So auth="Container" meaning that the Tomcat server 112 00:04:29.143 --> 00:04:31.744 will actually handle the authentication. 113 00:04:31.744 --> 00:04:35.681 Next we have type="javax.sql.DataSource". 114 00:04:35.681 --> 00:04:37.981 That's the actual name of the Java interface 115 00:04:37.981 --> 00:04:39.623 that we'll use for 116 00:04:39.623 --> 00:04:41.382 communicating with the pool. 117 00:04:41.382 --> 00:04:45.084 So in the Java world, whenever you see DataSource, 118 00:04:45.084 --> 00:04:47.137 just think connection pool. 119 00:04:47.137 --> 00:04:49.412 All right, so they use this generic term DataSource, 120 00:04:49.412 --> 00:04:53.382 but effectively, that's the name of your connection pool. 121 00:04:53.382 --> 00:04:55.176 Okay, on the next line here, 122 00:04:55.176 --> 00:04:57.963 we discuss the actual size of the connection pool. 123 00:04:57.963 --> 00:05:01.044 So maxActive of 20, so we're going to have 124 00:05:01.044 --> 00:05:04.155 20 connections in our pool to start out with. 125 00:05:04.155 --> 00:05:06.415 maxIdle is five, so you know 126 00:05:06.415 --> 00:05:08.819 if there are no users in our system, 127 00:05:08.819 --> 00:05:11.614 we'll have at least five connections available. 128 00:05:11.614 --> 00:05:15.185 And then the maxWait is simply a time in milliseconds 129 00:05:15.185 --> 00:05:17.205 as far as how long someone can wait 130 00:05:17.205 --> 00:05:19.001 before they get a connection. 131 00:05:19.001 --> 00:05:21.307 So right here it's 10,000 milliseconds, 132 00:05:21.307 --> 00:05:24.474 which translates over into 10 seconds. 133 00:05:25.356 --> 00:05:28.386 All right, so that's kind of the size configuration stuff. 134 00:05:28.386 --> 00:05:31.702 On the next line here, or actually the next three lines, 135 00:05:31.702 --> 00:05:35.389 we tell Tomcat how to connect to our database. 136 00:05:35.389 --> 00:05:37.746 So we give it the username and password. 137 00:05:37.746 --> 00:05:40.237 So username of demo, password of demo. 138 00:05:40.237 --> 00:05:43.604 If you remember, that's the special MySQL account 139 00:05:43.604 --> 00:05:46.887 that we created earlier for our application. 140 00:05:46.887 --> 00:05:48.915 So I think maybe two or three videos ago, 141 00:05:48.915 --> 00:05:50.750 we ran a little SQL script that would create 142 00:05:50.750 --> 00:05:53.823 a user called demo with a password of demo. 143 00:05:53.823 --> 00:05:56.095 So those are our credentials. 144 00:05:56.095 --> 00:05:59.032 Next we give the actual driverClassName, 145 00:05:59.032 --> 00:06:01.725 so the fully qualified name of the JDBC Driver, 146 00:06:01.725 --> 00:06:03.892 so we're using MySQL here. 147 00:06:04.959 --> 00:06:07.896 And then finally, we give the url to the database 148 00:06:07.896 --> 00:06:10.041 so our database is running on Localhost, 149 00:06:10.041 --> 00:06:12.592 and the actual name of the database schema 150 00:06:12.592 --> 00:06:14.113 is called student_tracker, 151 00:06:14.113 --> 00:06:17.363 and again, we ran some SQL code earlier 152 00:06:18.485 --> 00:06:21.524 to create that database with the schema 153 00:06:21.524 --> 00:06:22.986 and tables and so on. 154 00:06:22.986 --> 00:06:25.156 So basically again, this table, 155 00:06:25.156 --> 00:06:27.138 I mean sorry, this file here tells us 156 00:06:27.138 --> 00:06:31.103 how to connect to MySQL and also how to configure the pool 157 00:06:31.103 --> 00:06:33.036 as in regards to the actual size 158 00:06:33.036 --> 00:06:35.453 or the number of connections. 159 00:06:42.937 --> 00:06:45.025 Now our next step here is step three, 160 00:06:45.025 --> 00:06:47.649 defining the reference in web.xml. 161 00:06:47.649 --> 00:06:51.267 So this little snippet of configuration that will 162 00:06:51.267 --> 00:06:55.756 expose this DataSource to our given web application. 163 00:06:55.756 --> 00:06:57.860 All right, so this is in the file 164 00:06:57.860 --> 00:07:02.219 WebContent/WEB-INF/web.xml. 165 00:07:02.219 --> 00:07:04.491 All right, so we'll define our resource reference. 166 00:07:04.491 --> 00:07:06.708 So you can give a simple description, 167 00:07:06.708 --> 00:07:08.063 the resource reference name. 168 00:07:08.063 --> 00:07:11.732 That's very important, jdbc/student_tracker. 169 00:07:11.732 --> 00:07:14.432 That's the same name that was defined 170 00:07:14.432 --> 00:07:16.730 in the previous slide on step two. 171 00:07:16.730 --> 00:07:18.988 So whatever name you gave on step two, 172 00:07:18.988 --> 00:07:20.697 that's the same name that you use here 173 00:07:20.697 --> 00:07:23.086 'cause we'll use this to actually look up 174 00:07:23.086 --> 00:07:26.252 or get a handle to that connection pool. 175 00:07:26.252 --> 00:07:28.607 Resource type, that's the actual type of the object 176 00:07:28.607 --> 00:07:30.671 that's available in the connection pool. 177 00:07:30.671 --> 00:07:33.203 So again, whenever you see DataSource, 178 00:07:33.203 --> 00:07:34.838 think connection pool. 179 00:07:34.838 --> 00:07:37.192 And as far as the resource authentication, 180 00:07:37.192 --> 00:07:39.372 that's going to be handled by the container, 181 00:07:39.372 --> 00:07:43.457 so Tomcat in the previous slide it has the configs on how to 182 00:07:43.457 --> 00:07:44.941 connect to the container. 183 00:07:44.941 --> 00:07:46.785 I'm sorry, connect to the database 184 00:07:46.785 --> 00:07:48.887 and provide the username and password. 185 00:07:48.887 --> 00:07:50.556 So this is kind of very standard stuff 186 00:07:50.556 --> 00:07:52.820 that you'll use in your web.xml file. 187 00:07:52.820 --> 00:07:55.867 The only thing that's unique is the resource reference name. 188 00:07:55.867 --> 00:07:57.828 You give the same name of the pool 189 00:07:57.828 --> 00:08:00.023 that was defined in step number two. 190 00:08:00.023 --> 00:08:01.633 And we'll see all the code for this 191 00:08:01.633 --> 00:08:03.883 when we go through Eclipse. 192 00:08:07.096 --> 00:08:08.560 All right, so we're almost done here. 193 00:08:08.560 --> 00:08:10.418 This is the final step, step four, 194 00:08:10.418 --> 00:08:12.969 getting the connection pool in our Java code. 195 00:08:12.969 --> 00:08:15.788 Now, actually we have two different options 196 00:08:15.788 --> 00:08:18.686 on how to get the connection pool. 197 00:08:18.686 --> 00:08:20.636 Option one is making use of a feature 198 00:08:20.636 --> 00:08:22.432 called resource injection. 199 00:08:22.432 --> 00:08:23.627 You can use this with Servlets 200 00:08:23.627 --> 00:08:27.295 and some other special Java EE objects. 201 00:08:27.295 --> 00:08:29.348 And option two is that we can make use of 202 00:08:29.348 --> 00:08:33.660 the Java Naming and Directory Interface, or JNDI. 203 00:08:33.660 --> 00:08:35.857 The nice thing about this training class is that 204 00:08:35.857 --> 00:08:37.886 you'll actually see both examples. 205 00:08:37.886 --> 00:08:39.460 So I'll show you option one, 206 00:08:39.460 --> 00:08:41.864 and then we'll also make use of option two. 207 00:08:41.864 --> 00:08:43.507 And we'll get into the code for that 208 00:08:43.507 --> 00:08:46.424 once we move into the Eclipse tool. 209 00:08:48.441 --> 00:08:50.916 All right, so let's go ahead and move into Eclipse. 210 00:08:50.916 --> 00:08:54.591 line:15% The first thing we want to do is import the project. 211 00:08:54.591 --> 00:08:57.060 line:15% So when the source code download, 212 00:08:57.060 --> 00:09:00.031 line:15% I have this JDBC source code directory, 213 00:09:00.031 --> 00:09:02.536 line:15% and I have different versions of the application. 214 00:09:02.536 --> 00:09:05.703 line:15% So just go to File, and select Import. 215 00:09:06.712 --> 00:09:08.322 line:15% And choose the option here, 216 00:09:08.322 --> 00:09:11.269 line:15% Existing Projects into Workspace. 217 00:09:11.269 --> 00:09:13.852 line:15% And go ahead and click on Next. 218 00:09:17.825 --> 00:09:20.492 line:15% And now we need to tell Eclipse the root directory 219 00:09:20.492 --> 00:09:22.337 line:15% to import a project. 220 00:09:22.337 --> 00:09:24.155 line:15% So I'll just choose Browse, 221 00:09:24.155 --> 00:09:25.611 line:15% and I'll move over to that directory. 222 00:09:25.611 --> 00:09:27.850 line:15% So in this course jsf-for-beginners, 223 00:09:27.850 --> 00:09:30.906 line:15% you should have downloaded this 224 00:09:30.906 --> 00:09:34.980 line:15% file called jdbc-soure-code of this directory. 225 00:09:34.980 --> 00:09:37.880 line:15% And that's where we had our SQL scripts before. 226 00:09:37.880 --> 00:09:39.980 line:15% Here we have this, the different versions 227 00:09:39.980 --> 00:09:41.394 line:15% of the application. 228 00:09:41.394 --> 00:09:43.759 line:15% We're going to start with version one for right now. 229 00:09:43.759 --> 00:09:47.008 line:15% So I want you to select version-1-list-students. 230 00:09:47.008 --> 00:09:49.852 line:15% Just select that directory. 231 00:09:49.852 --> 00:09:52.852 line:15% And then go ahead and click on Open. 232 00:09:56.106 --> 00:09:57.866 line:15% And what Eclipse will do is it'll see, 233 00:09:57.866 --> 00:10:01.954 line:15% hey there's a project in there called jdbc-student-tracker. 234 00:10:01.954 --> 00:10:03.537 line:15% So that looks good. 235 00:10:04.390 --> 00:10:06.382 line:15% I'll also go ahead and kind of expand the window 236 00:10:06.382 --> 00:10:08.318 line:15% so just so you can see the full path. 237 00:10:08.318 --> 00:10:11.265 line:15% So again, under jdbc-source-code, 238 00:10:11.265 --> 00:10:13.436 line:15% and then version-1-list-students, 239 00:10:13.436 --> 00:10:16.347 line:15% and your window should mimic mine 240 00:10:16.347 --> 00:10:20.719 line:15% as far as the last couple of directories anyway. 241 00:10:20.719 --> 00:10:21.972 All right, so that's good. 242 00:10:21.972 --> 00:10:23.972 Go ahead and hit Finish. 243 00:10:28.303 --> 00:10:29.176 Okay great. 244 00:10:29.176 --> 00:10:30.766 So it'll go ahead and pull in the project. 245 00:10:30.766 --> 00:10:32.088 It'll go through some validations 246 00:10:32.088 --> 00:10:33.502 and all this other good stuff 247 00:10:33.502 --> 00:10:34.776 as far as building. 248 00:10:34.776 --> 00:10:37.537 However, just to give you a heads up, 249 00:10:37.537 --> 00:10:41.274 you may have some minor warnings or some errors 250 00:10:41.274 --> 00:10:43.117 when you open the project initially, 251 00:10:43.117 --> 00:10:45.424 and I'll show you how to resolve any of those 252 00:10:45.424 --> 00:10:47.326 minor project settings. 253 00:10:47.326 --> 00:10:48.917 So just select the project. 254 00:10:48.917 --> 00:10:51.341 Just do a right click and choose Properties. 255 00:10:51.341 --> 00:10:54.059 That's the first, actually that's the last option 256 00:10:54.059 --> 00:10:55.226 at the bottom. 257 00:10:58.686 --> 00:11:01.412 line:15% And once you're in the properties here, 258 00:11:01.412 --> 00:11:04.150 line:15% make sure you move over to Java Build Path, 259 00:11:04.150 --> 00:11:05.482 line:15% and this is where you may see 260 00:11:05.482 --> 00:11:07.038 line:15% some of the items or issues here, 261 00:11:07.038 --> 00:11:08.672 line:15% and the one area where you may see an issue 262 00:11:08.672 --> 00:11:10.588 line:15% is on the JRE System Library 263 00:11:10.588 --> 00:11:13.024 line:15% because you may have a different version of Java 264 00:11:13.024 --> 00:11:15.134 line:15% on your system than I do. 265 00:11:15.134 --> 00:11:17.350 line:15% So all you have to do is just highlight that item, 266 00:11:17.350 --> 00:11:21.980 line:15% hit remove, and then you can go ahead and add your library. 267 00:11:21.980 --> 00:11:23.730 line:15% So click Add Library. 268 00:11:25.321 --> 00:11:29.488 And then you select the item here, JRE System Library. 269 00:11:30.876 --> 00:11:33.511 Click Next, and then you can choose whichever one you have 270 00:11:33.511 --> 00:11:35.679 on your local computer. 271 00:11:35.679 --> 00:11:39.275 And then that should resolve the JRE System Library problem. 272 00:11:39.275 --> 00:11:41.421 Now if you're using a different version of Tomcat 273 00:11:41.421 --> 00:11:43.620 like maybe Tomcat 7 or whatever, 274 00:11:43.620 --> 00:11:45.085 you may have issues there. 275 00:11:45.085 --> 00:11:46.606 Just repeat the process. 276 00:11:46.606 --> 00:11:47.753 But once you're all done, 277 00:11:47.753 --> 00:11:50.270 you can go ahead and hit OK. 278 00:11:50.270 --> 00:11:51.684 And that should resolve any issues. 279 00:11:51.684 --> 00:11:53.230 And if you have any further problems, 280 00:11:53.230 --> 00:11:55.468 just contact me on the discussion forum, 281 00:11:55.468 --> 00:11:57.913 line:15% and I can help you out with that. 282 00:11:57.913 --> 00:12:00.792 line:15% All right, so let's go ahead and look at our step two here, 283 00:12:00.792 --> 00:12:04.201 line:15% defining the connection pool and context.xml. 284 00:12:04.201 --> 00:12:06.995 line:15% I skipped over step one for the driver. 285 00:12:06.995 --> 00:12:08.434 line:15% I'll come back to that in a second. 286 00:12:08.434 --> 00:12:11.851 line:15% So let's go with context.xml or step two. 287 00:12:13.264 --> 00:12:15.614 line:15% So again, remember this context.xml file 288 00:12:15.614 --> 00:12:17.926 line:15% basically defines our connection pool, 289 00:12:17.926 --> 00:12:21.650 line:15% so it gives us the name of the connection pool here, 290 00:12:21.650 --> 00:12:24.317 line:15% jdbc/student_tracker. 291 00:12:28.072 --> 00:12:30.500 line:15% We tell him that we're going to use container authentication 292 00:12:30.500 --> 00:12:32.415 line:15% and type of DataSource. 293 00:12:32.415 --> 00:12:34.696 line:15% We set up the size of the connection pool 294 00:12:34.696 --> 00:12:37.779 line:15% as far as maxActive, idle, and so on. 295 00:12:39.838 --> 00:12:42.642 line:15% We set up our database connection information 296 00:12:42.642 --> 00:12:45.642 line:15% as far as our username and password. 297 00:12:47.549 --> 00:12:50.299 line:15% We give our JDBC driverClassName, 298 00:12:51.268 --> 00:12:53.120 line:15% and finally we give the url. 299 00:12:53.120 --> 00:12:56.295 line:15% So that's the basic information, the same stuff that we saw 300 00:12:56.295 --> 00:12:59.212 line:15% in the slides a little bit earlier. 301 00:13:03.793 --> 00:13:06.866 line:15% And just as a short note, if you don't know much about JDBC, 302 00:13:06.866 --> 00:13:09.046 line:15% I have a free course on Udemy 303 00:13:09.046 --> 00:13:11.831 line:15% that you can take, so simply search Udemy 304 00:13:11.831 --> 00:13:13.733 for the keyword JDBC. 305 00:13:13.733 --> 00:13:15.217 You'll find my free course, 306 00:13:15.217 --> 00:13:18.255 line:15% and I give you all the details on how to use JDBC. 307 00:13:18.255 --> 00:13:21.690 line:15% All right, so moving ahead here with our driver file 308 00:13:21.690 --> 00:13:23.584 line:15% that I kind of skipped over, 309 00:13:23.584 --> 00:13:25.368 line:15% but that's our driver JDBC file. 310 00:13:25.368 --> 00:13:28.186 line:15% Normally you would download from the MySQL website. 311 00:13:28.186 --> 00:13:30.284 line:15% I have already done this work for you, 312 00:13:30.284 --> 00:13:32.293 line:15% but here remember the driver files 313 00:13:32.293 --> 00:13:35.654 line:15% go into the WEB-INF/lib directory, 314 00:13:35.654 --> 00:13:38.430 line:15% and it's our mysql.jar file. 315 00:13:38.430 --> 00:13:40.274 line:15% So if you have a new project, you can download it, 316 00:13:40.274 --> 00:13:42.606 or you can simply copy this file 317 00:13:42.606 --> 00:13:45.692 and put it into your new project. 318 00:13:45.692 --> 00:13:49.322 All right, so step three defining the reference in web.xml. 319 00:13:49.322 --> 00:13:52.732 So web.xml is simply a configuration file. 320 00:13:52.732 --> 00:13:56.596 We use this file to get a reference or a handle 321 00:13:56.596 --> 00:14:00.763 to that connection pool that was defined in Tomcat. 322 00:14:02.219 --> 00:14:04.320 And again, the most important thing here 323 00:14:04.320 --> 00:14:07.198 in this resource reference is line 27, 324 00:14:07.198 --> 00:14:09.060 the resource reference name. 325 00:14:09.060 --> 00:14:11.260 We have to make sure we use the same name 326 00:14:11.260 --> 00:14:13.631 of the connection pool that was defined 327 00:14:13.631 --> 00:14:15.964 in that previous file. 328 00:14:15.964 --> 00:14:19.442 So here it's called jdbc/student_tracker. 329 00:14:19.442 --> 00:14:21.367 And this will allow, again this will allow 330 00:14:21.367 --> 00:14:24.825 our web application to look up a connection pool 331 00:14:24.825 --> 00:14:27.075 and use it in our web code. 332 00:14:34.152 --> 00:14:35.031 Okay great. 333 00:14:35.031 --> 00:14:37.352 So that takes care of the web.xml. 334 00:14:37.352 --> 00:14:41.493 Now what I have is a very simple test harness 335 00:14:41.493 --> 00:14:42.581 just to test this out. 336 00:14:42.581 --> 00:14:43.928 I have some Java code to make sure 337 00:14:43.928 --> 00:14:45.658 that all of our configs are in place 338 00:14:45.658 --> 00:14:48.512 and that Tomcat can connect to the database. 339 00:14:48.512 --> 00:14:52.850 So under Java Resources, I have this file here 340 00:14:52.850 --> 00:14:55.308 called TestServlet.java. 341 00:14:55.308 --> 00:14:58.175 So this is our step four, getting a connection. 342 00:14:58.175 --> 00:15:01.283 So a servlet is simply a piece of Java code 343 00:15:01.283 --> 00:15:03.624 that will do some work on the server side. 344 00:15:03.624 --> 00:15:05.506 So on lines 20 and 21, I'll make use 345 00:15:05.506 --> 00:15:08.590 of that option one resource injection. 346 00:15:08.590 --> 00:15:12.327 I inject the resource named jdbc/student_tracker. 347 00:15:12.327 --> 00:15:14.853 It'll give us this object caled dataSource. 348 00:15:14.853 --> 00:15:18.371 So this dataSource is effectively our connection pool. 349 00:15:18.371 --> 00:15:22.232 So on line 34, I actually get a connection from the pool. 350 00:15:22.232 --> 00:15:24.423 I set up a SQL statement, 351 00:15:24.423 --> 00:15:27.217 and then I execute that SQL statement, 352 00:15:27.217 --> 00:15:29.495 and then I simply process the result set. 353 00:15:29.495 --> 00:15:31.788 So in this result, in this little loop here, 354 00:15:31.788 --> 00:15:33.741 I'm simply going to get the email address 355 00:15:33.741 --> 00:15:36.895 of each one of the students from the database. 356 00:15:36.895 --> 00:15:38.875 All right, so just very simple code here. 357 00:15:38.875 --> 00:15:40.597 Not going to spend too much time on that. 358 00:15:40.597 --> 00:15:42.138 Again I just want to test and make sure 359 00:15:42.138 --> 00:15:43.715 that this actually works. 360 00:15:43.715 --> 00:15:46.207 So I'll just do a right click on this servlet, 361 00:15:46.207 --> 00:15:50.374 and I'll choose Run As, and I'll say Run on Server. 362 00:15:53.526 --> 00:15:55.340 And once this is up and running, 363 00:15:55.340 --> 00:15:57.746 then we should see some output here. 364 00:15:57.746 --> 00:16:01.653 line:15% So this servlet's just generating output for the browser, 365 00:16:01.653 --> 00:16:04.926 line:15% and so here I get a list of five email addresses. 366 00:16:04.926 --> 00:16:07.417 line:15% These are the five students that I have 367 00:16:07.417 --> 00:16:08.925 line:15% in my database. 368 00:16:08.925 --> 00:16:12.337 line:15% So we have a success factor here. 369 00:16:12.337 --> 00:16:14.179 line:15% We're successful. 370 00:16:14.179 --> 00:16:17.169 line:15% We know that our connection pool is configured properly, 371 00:16:17.169 --> 00:16:20.603 line:15% and we know that our Java code can get that connection pool 372 00:16:20.603 --> 00:16:24.526 line:15% and in turn, send queries to the database and get results. 373 00:16:24.526 --> 00:16:28.693 line:15% So this was very plain vanilla, simple test harness. 374 00:16:29.989 --> 00:16:32.278 line:15% What we're going to do later in this video series 375 00:16:32.278 --> 00:16:34.004 line:15% is that we're going to make this look pretty. 376 00:16:34.004 --> 00:16:35.779 line:15% So when I add JSF, we're going to add tables, 377 00:16:35.779 --> 00:16:38.238 line:15% we're going to add style sheets, and so on and so forth, 378 00:16:38.238 --> 00:16:40.720 line:15% but this was mainly just to prove the point that hey, 379 00:16:40.720 --> 00:16:43.305 line:15% our configs are set up properly, and hey, 380 00:16:43.305 --> 00:16:47.472 line:15% we can connect to the database, so good job so far. 381 00:16:49.988 --> 00:16:51.082 All right, great stuff. 382 00:16:51.082 --> 00:16:52.572 So let's go ahead and wrap up. 383 00:16:52.572 --> 00:16:55.708 So here we discuss database connections in web apps. 384 00:16:55.708 --> 00:16:57.924 We also discuss connection pools, 385 00:16:57.924 --> 00:17:00.034 how to set up the pools in Tomcat, 386 00:17:00.034 --> 00:17:02.344 and finally we ran a little Java servlet 387 00:17:02.344 --> 00:17:04.677 to test our connection pool. 388 00:17:07.816 --> 00:17:09.415 All right, so this wraps up the video 389 00:17:09.415 --> 00:17:11.936 of setting up Tomcat database connection pool. 390 00:17:11.936 --> 00:17:13.632 In the following videos, 391 00:17:13.632 --> 00:17:15.206 we'll get into all the low level JSF work, 392 00:17:15.206 --> 00:17:16.864 and we'll integrate all of this together, 393 00:17:16.864 --> 00:17:19.781 so good job so far, and stay tuned.