WEBVTT 1 00:00:01.212 --> 00:00:03.090 Hey, so in this video, 2 00:00:03.090 --> 00:00:04.259 I'm going to show you how to set up 3 00:00:04.259 --> 00:00:07.842 the database table for our JSF application. 4 00:00:12.141 --> 00:00:13.631 I've provided two database scripts 5 00:00:13.631 --> 00:00:15.329 to help us set everything up. 6 00:00:15.329 --> 00:00:17.523 It's in the folder called sql-scripts. 7 00:00:17.523 --> 00:00:20.619 There's two files, 01-create-user.sql 8 00:00:20.619 --> 00:00:23.180 and also 02-student-tracker.sql, 9 00:00:23.180 --> 00:00:24.817 and I'll walk through each of these scripts, 10 00:00:24.817 --> 00:00:28.567 and explain them to you with some good demos. 11 00:00:32.799 --> 00:00:36.115 So, the first script here, 01-create-user.sql. 12 00:00:36.115 --> 00:00:37.293 What this is going to do 13 00:00:37.293 --> 00:00:39.780 is it's going to create a new MySQL user 14 00:00:39.780 --> 00:00:41.447 for our application. 15 00:00:42.487 --> 00:00:44.996 Our application is going to have a user name of demo, 16 00:00:44.996 --> 00:00:46.430 and password of demo. 17 00:00:46.430 --> 00:00:49.730 We want to have a unique MySQL user for our application, 18 00:00:49.730 --> 00:00:51.897 instead of using the root. 19 00:00:57.273 --> 00:00:59.287 So let's go ahead and run MySQL Workbench. 20 00:00:59.287 --> 00:01:00.689 So on your operating system, 21 00:01:00.689 --> 00:01:02.265 just go ahead and select the option 22 00:01:02.265 --> 00:01:04.515 to run the MySQL Workbench. 23 00:01:07.309 --> 00:01:08.306 Once it starts up, 24 00:01:08.306 --> 00:01:10.625 then we'll go ahead and we'll connect to the database. 25 00:01:10.625 --> 00:01:14.226 We'll start off with the root, localhost, here. 26 00:01:14.226 --> 00:01:17.873 line:15% That's the only account that we have at the moment. 27 00:01:17.873 --> 00:01:19.715 line:15% And then what I'm going to do is open that file, 28 00:01:19.715 --> 00:01:21.759 line:15% so it's the sql scripts directory, 29 00:01:21.759 --> 00:01:25.129 line:15% and the file is 01-create-user.sql. 30 00:01:25.129 --> 00:01:26.796 line:15% So I'll select open, 31 00:01:29.382 --> 00:01:31.794 line:15% and again, this is part of the jdbc source code 32 00:01:31.794 --> 00:01:33.379 line:15% from the JSF course. 33 00:01:33.379 --> 00:01:35.526 line:15% There's a top level directory called sql-scripts. 34 00:01:35.526 --> 00:01:37.483 line:15% You can simply open that directory. 35 00:01:37.483 --> 00:01:39.201 line:15% There's two files in there. 36 00:01:39.201 --> 00:01:42.886 line:15% 01-create-user.sql and 02-student-tracker. 37 00:01:42.886 --> 00:01:44.235 line:15% So let's just start with the first one here, 38 00:01:44.235 --> 00:01:45.818 line:15% 01-create-user.sql. 39 00:01:46.905 --> 00:01:48.905 And then we'll hit open. 40 00:01:50.284 --> 00:01:52.827 Alright, so this is just a very basic SQL file. 41 00:01:52.827 --> 00:01:53.761 So what we're going to do here is 42 00:01:53.761 --> 00:01:55.325 we're going to create a user. 43 00:01:55.325 --> 00:01:57.075 The username is demo, 44 00:02:00.222 --> 00:02:02.494 and the password is demo. 45 00:02:02.494 --> 00:02:04.779 And then we'll grant all the privileges to this account, 46 00:02:04.779 --> 00:02:07.005 just so that this account can create tables 47 00:02:07.005 --> 00:02:09.731 and do all kinds of other really cool stuff. 48 00:02:09.731 --> 00:02:12.303 I'll go ahead and hit the lower execute button here. 49 00:02:12.303 --> 00:02:13.687 It'll create that user, 50 00:02:13.687 --> 00:02:16.856 and we should see green check marks at the bottom 51 00:02:16.856 --> 00:02:19.326 that this operation was successful. 52 00:02:19.326 --> 00:02:20.159 So that's great. 53 00:02:20.159 --> 00:02:24.326 So now we have a user created that our application can use. 54 00:02:29.258 --> 00:02:32.105 Alright, so now let's take a look at this second file here, 55 00:02:32.105 --> 00:02:34.315 02-student-tracker.sql. 56 00:02:34.315 --> 00:02:35.794 So the purpose of this file 57 00:02:35.794 --> 00:02:37.907 is that it's going to create a new database table 58 00:02:37.907 --> 00:02:40.339 for our application, called student. 59 00:02:40.339 --> 00:02:42.915 This table's going to have four columns: 60 00:02:42.915 --> 00:02:45.748 ID, first name, and email address. 61 00:02:47.397 --> 00:02:51.104 We'll also make use of this script to load sample data, 62 00:02:51.104 --> 00:02:52.290 so they'll actually go through 63 00:02:52.290 --> 00:02:55.330 and load up five sample students for us, 64 00:02:55.330 --> 00:02:58.193 just so our application will already have data 65 00:02:58.193 --> 00:03:01.110 once we start with our development. 66 00:03:04.236 --> 00:03:06.232 line:15% Okay, so let's go ahead and take a look at this. 67 00:03:06.232 --> 00:03:08.689 line:15% So in the MySQL Workbench, I'm going to go ahead 68 00:03:08.689 --> 00:03:10.710 line:15% and open this new file here. 69 00:03:10.710 --> 00:03:15.199 line:15% It's in sql-scripts/02-student-tracker.sql. 70 00:03:15.199 --> 00:03:19.181 line:15% And I'll go ahead and hit the open button. 71 00:03:19.181 --> 00:03:21.326 Alright, so now this file has a lot of stuff in it, 72 00:03:21.326 --> 00:03:23.267 and this file was actually generated earlier 73 00:03:23.267 --> 00:03:25.410 when I created a dump for this course. 74 00:03:25.410 --> 00:03:26.355 But at the beginning here, 75 00:03:26.355 --> 00:03:28.254 all it does is creates a database 76 00:03:28.254 --> 00:03:30.126 and then lines 20 through 22, 77 00:03:30.126 --> 00:03:33.864 it creates the table structure for our table student, 78 00:03:33.864 --> 00:03:37.936 and on lines 36 38, it handles the actual inserts 79 00:03:37.936 --> 00:03:40.700 of the actual data for our sample students. 80 00:03:40.700 --> 00:03:42.926 Again, this file was generated, 81 00:03:42.926 --> 00:03:47.080 but we can use this file to actually populate our system. 82 00:03:47.080 --> 00:03:49.528 So I'll go ahead and hit the lower execute button, 83 00:03:49.528 --> 00:03:52.109 and we'll have the green check marks at the bottom. 84 00:03:52.109 --> 00:03:54.504 Now over on the left hand side, 85 00:03:54.504 --> 00:03:57.098 what I want to do is do a right click, 86 00:03:57.098 --> 00:03:59.148 and select Refresh All, 87 00:03:59.148 --> 00:04:02.796 just so I can see this new schema displayed. 88 00:04:02.796 --> 00:04:03.864 So great, there we go. 89 00:04:03.864 --> 00:04:06.128 So now we have student_tracker, 90 00:04:06.128 --> 00:04:07.204 so that's the new table 91 00:04:07.204 --> 00:04:09.939 that was just created by this script. 92 00:04:09.939 --> 00:04:12.661 I can expand the windows here, 93 00:04:12.661 --> 00:04:14.864 and I have tables, and again, really simple, 94 00:04:14.864 --> 00:04:17.659 only one table called student. 95 00:04:17.659 --> 00:04:20.158 I can expand this a little bit further 96 00:04:20.158 --> 00:04:22.777 to take a look at the columns, four columns, 97 00:04:22.777 --> 00:04:26.587 id, first name, last name, and email, 98 00:04:26.587 --> 00:04:28.323 and then I can actually query this table 99 00:04:28.323 --> 00:04:30.183 to look at some of the sample data. 100 00:04:30.183 --> 00:04:32.683 So I'll just do a right click. 101 00:04:33.763 --> 00:04:35.513 I'll say Select Rows. 102 00:04:40.208 --> 00:04:41.278 And if everything's successful, 103 00:04:41.278 --> 00:04:42.526 we should see sample data, 104 00:04:42.526 --> 00:04:44.038 so wow, good stuff. 105 00:04:44.038 --> 00:04:47.177 So we have our sample students here, our five students. 106 00:04:47.177 --> 00:04:48.641 They're in our database. 107 00:04:48.641 --> 00:04:50.582 They have first name, last name, and email. 108 00:04:50.582 --> 00:04:51.665 So we're in good shape here, 109 00:04:51.665 --> 00:04:55.100 so our scripts ran successfully. 110 00:04:55.100 --> 00:04:57.416 We've created the tables, 111 00:04:57.416 --> 00:04:59.416 we've loaded it with sample data, 112 00:04:59.416 --> 00:05:01.146 and now we're ready to go. 113 00:05:01.146 --> 00:05:01.979 Great. 114 00:05:01.979 --> 00:05:03.332 So in the following videos here, 115 00:05:03.332 --> 00:05:04.532 I'm going to show you how to set up 116 00:05:04.532 --> 00:05:06.048 a connection to this database, 117 00:05:06.048 --> 00:05:07.872 and then how to write the JSF code 118 00:05:07.872 --> 00:05:10.539 to actually query this database.