Instructor: Hey, so in this video,
I'm going to show you how to set up
the database table for our JSF application.
I've provided two database scripts
to help us set everything up.
It's in the folder called sql-scripts.
There's two files, 01-create-user.sql
and also 02-student-tracker.sql,
and I'll walk through each of these scripts,
and explain them to you with some good demos.
So, the first script here, 01-create-user.sql.
What this is going to do
is it's going to create a new MySQL user
for our application.
Our application is going to have a user name of demo,
and password of demo.
We want to have a unique MySQL user for our application,
instead of using the root.
So let's go ahead and run MySQL Workbench.
So on your operating system,
just go ahead and select the option
to run the MySQL Workbench.
Once it starts up,
then we'll go ahead and we'll connect to the database.
We'll start off with the root, localhost, here.
That's the only account that we have at the moment.
And then what I'm going to do is open that file,
so it's the sql scripts directory,
and the file is 01-create-user.sql.
So I'll select open,
and again, this is part of the jdbc source code
from the JSF course.
There's a top level directory called sql-scripts.
You can simply open that directory.
There's two files in there.
01-create-user.sql and 02-student-tracker.
So let's just start with the first one here,
01-create-user.sql.
And then we'll hit open.
Alright, so this is just a very basic SQL file.
So what we're going to do here is
we're going to create a user.
The username is demo,
and the password is demo.
And then we'll grant all the privileges to this account,
just so that this account can create tables
and do all kinds of other really cool stuff.
I'll go ahead and hit the lower execute button here.
It'll create that user,
and we should see green check marks at the bottom
that this operation was successful.
So that's great.
So now we have a user created that our application can use.
Alright, so now let's take a look at this second file here,
02-student-tracker.sql.
So the purpose of this file
is that it's going to create a new database table
for our application, called student.
This table's going to have four columns:
ID, first name, and email address.
We'll also make use of this script to load sample data,
so they'll actually go through
and load up five sample students for us,
just so our application will already have data
once we start with our development.
Okay, so let's go ahead and take a look at this.
So in the MySQL Workbench, I'm going to go ahead
and open this new file here.
It's in sql-scripts/02-student-tracker.sql.
And I'll go ahead and hit the open button.
Alright, so now this file has a lot of stuff in it,
and this file was actually generated earlier
when I created a dump for this course.
But at the beginning here,
all it does is creates a database
and then lines 20 through 22,
it creates the table structure for our table student,
and on lines 36 38, it handles the actual inserts
of the actual data for our sample students.
Again, this file was generated,
but we can use this file to actually populate our system.
So I'll go ahead and hit the lower execute button,
and we'll have the green check marks at the bottom.
Now over on the left hand side,
what I want to do is do a right click,
and select Refresh All,
just so I can see this new schema displayed.
So great, there we go.
So now we have student_tracker,
so that's the new table
that was just created by this script.
I can expand the windows here,
and I have tables, and again, really simple,
only one table called student.
I can expand this a little bit further
to take a look at the columns, four columns,
id, first name, last name, and email,
and then I can actually query this table
to look at some of the sample data.
So I'll just do a right click.
I'll say Select Rows.
And if everything's successful,
we should see sample data,
so wow, good stuff.
So we have our sample students here, our five students.
They're in our database.
They have first name, last name, and email.
So we're in good shape here,
so our scripts ran successfully.
We've created the tables,
we've loaded it with sample data,
and now we're ready to go.
Great.
So in the following videos here,
I'm going to show you how to set up
a connection to this database,
and then how to write the JSF code
to actually query this database.