so we see how to work with a sqlite
database from the command line now
we're going to learn how to use a
database from a java application and to
do so we are going to be using the jdbc api now
jdbc stands for java database
connectivity so using jdbc we can not only
work with databases but also
spreadsheets and flat files so thinking
for jdbc as a middle man that sits
between a java application and a data
source to use a particular data source
from an application we need the jdbc
driver for the data source for example
to access sqlite database from an
application we need a sqlite jdbc
driver now the driver is simply a java
library containing classes that
implement the jdbc API now because all
jdbc drivers have to implement the same
interfaces it's not difficult to change
the data source and application users for
example if an application uses the
sqlite database and then we decide later
that we want to use a my sql database
all we have to do is use the mysql
jdbc driver instead of the sqlite
one in addition to of course migrating
the data to a mysql database
[Music]
so in reality unfortunately it's not
that simple
nothing is ever a hundred percent
portable but if we write our jdbc code
with thought in mind that we may have to
change the data source later we can make
our lives easier by avoiding the use of
database specific sql code and also
behaviors wherever possible if we wanted
to work with the spreadsheet or flat
file that's fine as long as a jdbc
driver that works with that spreadsheet
or file format if you're feeling
particularly a game you can always write
the jdbc driving yourself if necessary
now the jdbc driver has to be written in
Java but it can consist of a thin java
layer that calls code written in other
languages a current version of the jdbc
API as of shooting this video is jdbc 4.2
for backwards compatibility it contains
all the methods that were in previous
jdbc version now there have been
changes to the way that jdbc works so
make sure you're working with a driver
that supports the at least jdbc 4.0 and
if you use the one will download in this
video you'll be fine
jdbc consists of two packages java.sql
which is core jdbc and java x
.sql which is optional jdbc now the
api's in the java x sql package are
required when working with database
servers and we'll talk about those a
little bit later know all the popular
databases provide jdbc drivers the jdk
ships with the database actually called
Derby which can be used for desktop
applications or when prototyping
the Derby jdbc driver is also included
in the jdk
but we're going to start by using a sql
lite database so we're going to use a
sql light jdbc driver now the driver
that w's comes packaged with sqlite
which means we don't have to
install the database separately so we
won't be using the sqlite command
line tools that we used in a previous
video so lets spring back over now and
download the necessary driver
alright so we need to download the
sqlite jdbc driver so since we want to
add the driver to the projects we create
when you download it to make sure to
save it somewhere that you will
remember so let's just open the browser
and paste in the link the link is
in the resources section as always
once we get here going to click on
downloads over here to the left hand
side you want to download the very
latest version don't click this top
link this download repository rather
come down here and choose the latest
version you can see in this particular
case the latest version is 3.1 5.1 the
date here was added to the
repository was the 11th of november
2016 we're going to click on that
file and just save it somewhere will
know where it is so we can refer to it later
going to put in my downloads
folder it's actually very small files
you can see it's already completed the
download process now there are other sql
jdbc drivers but we're using this
one because it contains the sqlite
database and doesn't require any
configuration now because we'll add the
jar file to any projects there won't be
any conflict with the sqlite we
installed to use from the command line
so our application we use the version
that's in the jar file now will be
wanting to check the contents of that
database tables when we are coding our
application to make sure everything is
working as we expect now I guess we
could use the command line version of
sqlite to connect to the database
that we're using our application but
instead what we're going to do is we're
going to use the DB browser for sqlite
application
which is a basically a an application
it's got a GUI interface so we can
access sqlite databases for so for the
rest of this video what we're going to
do is I'm going to quickly go through
three operating systems windows mac and
linux and show you how to go about
installing that for each platform and
then in the next video will continue on
with the creating a java project using
the sqlite jdbc drivers to access
databases from within java code
alright so let's start with the windows
version
ok so lets download and
install the sqlite browser for
Windows and just a reminder that there's
following this part of the video
little bit further on i'm going to go
through the same process for mac and
then also for linux box so I'm going to
start on Windows i'm going to go to.....
....
once you get there you can see over the
right-hand side we've gotta download a
32-bit version
of the db browser or 64-bit now I've been
through around showing you how to
specify how to check rather whether your
computers running a 32-bit or 64-bit
version of windows so choose the
appropriate version in my case i'm
running a 64-bit so i'm going to select
that by clicking it and see the download
started in the bottom left-hand corner
and it's quite a small file about
15 megabytes and constantly the
downloads already finished now
i'm going to run that executable
we're going to install i'm just going to
minimize the window we no longer need
it
let's go ahead and install it....so db
browse i'm going to click on next and
click on i agree to the license i'm
going to choose to allow the default
location to be selected here for the
program installed and the leave as
default and click install and depending
on your version of windows that might
need to install this so you can see it's
installing a runtime here that might
take a little longer and click on show
details if you want to see what's
happening what it's actually doing so
just pause at any event and wait for this
to complete and will come back and just
fie up the db browser to make sure
it's working ok you can see that it's
now completed and the checkbox i'm going
to leave ticked so that will actually
run db browser for us so we click on
finish and there's our DB browser for sql
utility that we're going to be using in
the future videos so if you haven't got
a mac or linux machine at this point you
can actually move on to the next video
alright so that was the windows version
and how to go about installing that lets
go swing over and do the mac version
alright let's go ahead now and download
install the DB browser sqlite for
the mac so to do that we need to go to
the website which is sqlite browser...
....
alright so once we get there we've got
to downloads that are available for various
platforms we're going to choose the mac
dmg so let's select that we're going to
save it to my downloads folder quite a
small download as you can see under 12
megabytes will give it a moment to
complete
ok so i'm going to open it up the dmg
file we're going to install it going to
drag this into the applications folder
it's very small so you may have miss it it's
such a small file and now i can just go
to that either navigate to the
Applications folder or just do a command
space and capital D capital B
so capital D capital B
you can see that db browser sqlite
showing their so i can open it up
select open because opening for the first time
and there's our browser our db
browser for sqlite for mac alright
so that's the mac version if you haven't
got the need to install a linux
version you can actually skip to the
next video because in that video we're
going to start using the jdbc
driver that we've downloaded by creating
a new project in IntelliJ as well as
using the db browser that we
downloaded and installed as well so
see you in the next video if you decide
to do that but if you've got a linux
machine that you want this installed on
stay tuned and we'll do that right now
alright so that's how to install the mac
version of the sqlite browser and finally
let's go now and just install linux
version in this video we're going to
download compile and build and install
the sqlite database browser for linux
first thing we need to go to a web
browser and we need to go to sqlite
browser.... then we need to
right click this link this download
tar.gz file so right click
that i'm going to copy the link location
then what i'm going to do is open the
terminal window and i'm going to go in
to the location where i want to install
ultimately this product now
on my computer and I got a software folder you
can see there and I've got a subfolder
there that for two other products that
I'm using in another course my java
spring course so what I'm going to do is
create it in here
so what I'm going to do is type...
going to right click and paste
which would give us the link that we
copied from the browser press enter only
small file so it should download quite
quickly
you can see the finish now and just
close down the browser because we no
longer need that
alright so we have a look now we can see
that we've got this file and not that
the file name is a little bit
funny so what we're gonna do is change
that name so it's going to rename it's
going to type...
...
....
...so we now changed or
rename that folder going to go into that
folder...and I
should've obviously added or kept the
.tar.gz so let me do that so...
...
....
alright so now we can extract that file
...
....
...
now we want to go into the folder....
...
....and
what we want to do then is so we want to
issue a couple of commands qmake
which is a tool produced or that comes
with QT which you may be familiar with
so we may have to install it on your
computer and i'm going to show you how
to do that if necessary so we still we
need to run these tools now to build it
so first things first we try typing...
...and if you get an error like
we've got here essentially that's saying
it can't execute no such file or
directory
it means qt isn't installed on your
computer so if that's the case you need
to install it i'm going to show you how
to do that now but if you've got nothing
you've got no output at all you just
got back to the prompt that actually
means that it worked ok you can by
pass this next command and move on but in
my case i got an error so I'm going to
type....
....
...
.....this will now download and
install the various packages that
we selected
ok so the installation is now completed
I'm going to clear the screen so you can
see the commands again so now going to
type that command that we've got an
error before which was q make this time
and almost instantly returned and that's
what i was saying that if you had this
installed already that's the output which
you would have seen you wouldn't have
seen any errors at all so that's
obviously telling us that we installed
qt correctly and the last command now
we are going to type is make that's actually gonna
build the sqlite database browser
so I'll take a little while to build its
going to take a minute or so to
complete depending on the speed of your
computer once we're finished we'll end
up with an executable file that we can
run that gives us a nice GUI
interface for sqlite databases
alright so at this point now it's finished
and what happened is now there's been a
source folder that's created if I type....
...and have a look we now
got you can see green in the top right hand
corner of this terminal window sqlite
browser so we can actually launched
that...
...hat runs the background and you can see
we got a nice little db browse for
sqlite utility at this point you can add
it to the dash to do whatever you want
to do if you want to add a shortcut to
get it to start each time
alternatively you can just go into that
folder and started manually using the
command line like I have actually
shown you so we've now
install down the sqlite browser
db browser for sqlite application for
windows mac and linux in the next video
what we're going to do is fire up the d b
browser just have a bit of a look at it
and then we'll swing over and start
working on our first project in IntelliJ
that uses this jdbc sqlite driver
that so we downloaded in this video see
you in the next video