IntroductionWelcome| 00:04 | My name is David Gassner, and I would like to
welcome you to Java Database Integration with JDBC.
| | 00:11 | JDBC is a set of Java interfaces and classes
that will connect to your application to nearly
| | 00:16 | any relational database management system.
| | 00:19 | I'll show you how to write Java code to
connect two different database management systems.
| | 00:24 | Once Connected, I'll describe how to loop through
rows of retrieve data and how to display their contents.
| | 00:30 | I'll describe how to use preparedStatement
object to create parameterized SQL statements,
| | 00:36 | I'll then show you how to insert, update,
and delete data and how to dynamically acquire
| | 00:41 | information about your database structure.
| | 00:44 | I hope that this course will help you get
started with this important Java API and get
| | 00:49 | ready to build data-aware
Java-based applications.
| | 00:54 |
| | Collapse this transcript |
| What you should know before starting this course| 00:00 | This course is designed for software developers who
already have some experience programming with Java.
| | 00:06 | You don't need to have worked in any particular
application environment, such as Java enterprise
| | 00:11 | edition, web applications,
or Android app development.
| | 00:14 | You just need to understand the basics of
Java syntax, how to create simple console
| | 00:19 | applications, and how to use Eclipse to create,
compile, run, and debug. If you're brand-new
| | 00:25 | to Java, I recommend first watching my course
Java Essential Training. You'll get an introduction
| | 00:30 | to the Java programming language and some
valuable information about how the language
| | 00:34 | and virtual machine work
during compilation and at runtime.
| | 00:38 | You'll learn how to install Eclipse, how to
create Java projects, packages, and classes,
| | 00:43 | and you'll get an introduction to the
fundamental concepts of object-oriented programming.
| | 00:48 | You should then be ready for this course and for
other lynda.com courses that put Java to specific use.
| | 00:54 | When working with databases and JDBC, you'll need to use
at least a little bit of SQL or Structured Query Language.
| | 01:01 | In this course I'll show you all the SQL you need, including
statements to select, insert, update, and delete data.
| | 01:08 | But if you want to learn more about SQL, check
out the lynda.com course, SQL Essential Training.
| | 01:14 | So let's get started learning about
working with Java and Databases with JDBC.
| | 01:19 |
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you're a Premium Member of the lynda.com
online training library, or you're watching
| | 00:05 | this video series on a disc, you'll have access
to the exercise files that accompany this course.
| | 00:11 | I have copied the exercise files to my Desktop,
| | 00:13 | but you can place them
anywhere on your hard disk.
| | 00:16 | The main exercise files for the course are
divided into chapters, and within each chapter
| | 00:22 | there are folders that are designed as
projects that can be imported into Eclipse.
| | 00:26 | They were created with Eclipse 4.2 or Juno but
should be importable in older versions as well.
| | 00:33 | You will need Java 7 installed
to use any of these projects.
| | 00:37 | To import any Project go in to Eclipse.
| | 00:40 | From the menu, select File > Import, then
from the Import dialog under General, select
| | 00:47 | Existing Projects into Workspace > Browse and choose
the Root folder for the project you want to Import.
| | 00:53 | I'll start with my Desktop where I have
installed the exercise files, then I'll go to
| | 00:58 | Exercise Files/03_Connections for chapter 3,
and I'll choose the project JDBCExceptions.
| | 01:06 | I'll then follow the rest of the
buttons to import the project into Eclipse.
| | 01:11 | You should then be able to open the project
in the Package Explorer view open the Source
| | 01:15 | folder and its packages
and open any of the classes.
| | 01:18 | I have opened the Main class from this
particular application with either the main class or
| | 01:23 | the project selected, you should then be
able to run the application by clicking the run
| | 01:27 | button and the result will
show up in the console view.
| | 01:30 | If you have the editor maximized like this
then the Console view will appear on the right.
| | 01:35 | To get rid of it, click into the
Console view and back on the editor.
| | 01:39 | You can also debug by
clicking an icon on the toolbar.
| | 01:43 | And if there are any errors or breakpoints,
you'll go into the debug perspective, and
| | 01:47 | you might be prompted with a
dialog before that happens.
| | 01:51 | You can also run or debug using keyboard
shortcuts. To find out about these keyboard shortcuts
| | 01:56 | go to the Run menu. The keyboard shortcuts
differ between operating systems, look here
| | 02:01 | to find out the keyboard
shortcut for your operating system.
| | 02:05 | I have also provided some free exercise files that are
available to everybody, regardless of membership status.
| | 02:11 | These exercise files contain code,
database scripts, and libraries.
| | 02:17 | The code folder includes a single
project that I'll describe later in the course.
| | 02:21 | The database folder includes scripts for both
MySQL and HSQLDB and one text file that contains
| | 02:29 | to MySQL stored procedures
that I'll use later in the course.
| | 02:34 | The libs folder contains to
open source Java libraries.
| | 02:39 | These are drivers, one for
HSQLDB, and one for MySQL.
| | 02:44 | You can also download the latest version
of these JAR files from the vendor sites.
| | 02:49 | But to simplify things, I have
included them in these Free exercise files.
| | 02:53 | So that's a look at the exercise
files that accompany the course.
| | 02:56 | The Free exercise files are available to
all, and the Main exercise files are available
| | 03:00 | to Premium Members or those
who have purchased the DVD.
| | 03:04 | If you don't have access to these Main exercise files
you can follow along with your own Java code.
| | 03:09 |
| | Collapse this transcript |
|
|
1. Installing SoftwareTesting your Java development environment| 00:00 | The exercises in this course are designed
to work with Java 7, the most recent version
| | 00:05 | of the Java Programming Language
as of the time of this recording.
| | 00:09 | If you haven't programmed with Java 7 before, you want
to install it on your computer and make sure it's working.
| | 00:15 | If you need to install Java 7, you can get
the JDK or the Java Developers Kit from Oracle.
| | 00:21 | Go to the URL oracle.com/Java, that
will take you to the main Java page,
| | 00:27 | then go to the Downloads menu
and select Java for Developers.
| | 00:31 | On this screen you'll see that there are two
downloads available, the JDK or Java Developers Kit,
| | 00:36 | and the JRE or Java Runtime
Environment. You'll want the JDK.
| | 00:41 | I'll click that link, and I'll see a listing of all the
different installations for various Operating Systems.
| | 00:47 | You can choose from the Mac version or the
Windows version, and notice that there are
| | 00:52 | two versions for Windows, one for
64 bit and one for X86 or 32-bit.
| | 00:58 | After following the rest of the instructions
to install the JDK on your system, if you're
| | 01:02 | working on a Mac, make sure that you have
set the Java 7 Runtime as your default.
| | 01:08 | To do this, go to the Java Preferences application,
which you can find through Spotlight or from
| | 01:14 | Finder by going to the
Applications/Utilities folder.
| | 01:18 | I'll open Java Preferences and
show you the state it should be in.
| | 01:22 | You should have Java 7 installed, and Java SE7 should
be at the top of the list that makes it your default.
| | 01:29 | If you're working on Windows, all
you need to do is install the JDK.
| | 01:34 | It'll be fine if it's installed side by side
with Java 6 or an older version of the JDK.
| | 01:39 | It just has to be present on your system.
| | 01:42 | Once you have installed Java 7 on your system,
the next step is to make sure that you can
| | 01:46 | create and run a Java 7 project in Eclipse.
| | 01:49 | I have already installed Eclipse, and I'll
go to the Eclipse Preferences dialog.
| | 01:54 | You can get the Preferences either through Eclipse
Preferences on Mac or from the Window menu on Windows.
| | 02:00 | In the Preferences dialog, go to the Java
section and then to the Compiler and make sure your
| | 02:05 | Compiler compliance level is set to 1.7,
which is the actual underlying version number
| | 02:10 | for what's called in marketing Java 7.
| | 02:13 | If you don't see 1.7 as a possible option, make
sure that you're running at least Juno Eclipse 4.2.
| | 02:20 | If all that is set, now you're ready to create a
simple project and make sure that some code will work.
| | 02:26 | That would only work if
you're running Java 7 or later.
| | 02:29 | I'll close the Preferences dialog
and create a new Java project.
| | 02:33 | I'll name the project Java7Test, and I'll
make sure that my execution environment JRE
| | 02:39 | is set to JavaSE-1.7, and I'll click Finish.
| | 02:44 | I'll open up the project and go to the
source folder, and I'll create a New Class.
| | 02:48 | I will name my class Main.
| | 02:50 | I'll add it to a package called com.lynda.javatraining,
and I'll make sure that I have checked the option
| | 02:57 | to create a main method, and I'll click Finish.
| | 03:00 | Now I'm going to execute a little bit of code that will
only work correctly if I have Java 7 or later installed.
| | 03:06 | I'll go to the class declaration and delete
these comments, and within the class declaration
| | 03:11 | but outside the main method
I'll declare a static string field.
| | 03:15 | I'll make it private and static, and I'll set its
type as String, and I'll give it a value of one.
| | 03:21 | I'll go to the main method,
and I'll add a switch statement.
| | 03:24 | I'll type the word Switch and press Ctrl+Space,
and I'll use this switch code template.
| | 03:29 | I'll set the key to value, and
here's why: this is a Java 7 test.
| | 03:33 | Java 7 adds the ability to use switch
statements with string values, something you couldn't
| | 03:39 | do with older versions of Java.
| | 03:41 | So if this code works, then I
know the Java 7 is working.
| | 03:44 | I'll press Tab to go to my first case,
and I look for a value of one as a string.
| | 03:50 | I'll tab again, and I'll use the sysout code
template, typing sysout, and then pressing Ctrl+Space,
| | 03:55 | and I'll out put
the string, the value is one.
| | 03:59 | Now I'll go down to my default case, and
I'll add another system output block, and I'll
| | 04:04 | output the string, The value isn't one.
| | 04:07 | I'll save my changes and run my application and in
my Console, I'll see the string, the value is one.
| | 04:13 | So if this code is working on your
system then you know JDK 7 is working.
| | 04:18 | Finally, I'm going to make a couple of
other changes to my Eclipse environment and then
| | 04:22 | I'll be ready to launch into
the exercises in this course.
| | 04:26 | I'm going to reset the current Java perspective.
| | 04:29 | The Java perspective is installed
as part of the Eclipse installation.
| | 04:32 | I'll go to window and Reset Perspective
and click Yes, and then I'm going to close the
| | 04:37 | Task List view, which I
won't need for this course.
| | 04:41 | Then I'll save this reconfigure
perspective as a custom perspective named Java DB.
| | 04:46 | I'll go to the menu and choose Window > Save
Prospective As, and I'll name this Java DB and click OK.
| | 04:54 | Now at any time during the exercises, I'll
be able to return to this custom perspective,
| | 04:59 | and this will be my starting perspective in each of
the exercises of this course. So Java 7 is tested.
| | 05:05 | I know I'll be able to run Custom Java 7 code
on this system, and I have customized my Eclipse
| | 05:10 | installation, and I'm
ready to get started with JDBC.
| | 05:15 |
| | Collapse this transcript |
| Importing a MySQL database| 00:00 | To follow along with the exercises in this
course, you'll need a local installation of
| | 00:05 | MySQL and a copy of phpMyAdmin, a web-based
management system that lets you manage a MySQL Database.
| | 00:12 | You can select from a number of approaches
to installing this software on your computer.
| | 00:16 | I describe how to install this software in
the course installing Apache, MySQL, and PHP,
| | 00:22 | which is a completely free course
available in the lynda.com online training library.
| | 00:28 | In this course, I will describe how to install
separate components on either Mac or Windows,
| | 00:32 | or you can select from one of the many available software
bundles which include Apache, MySQL, PHP, and phpMyAdmin.
| | 00:41 | These include WampServer for Windows, MAMP
for Mac OS X, or XAMPP or BitNami, which are
| | 00:47 | both available for both Windows and Mac.
| | 00:50 | Throughout this course I'll be using MAMP, a
software bundle specifically designed for use on Mac OS X.
| | 00:57 | If you're using MAMP, there's one
preferences thing you need to take care of.
| | 01:01 | When you first install MAMP, you'll see that it's
set ports of 8888 for Apache and 8889 for MySQL.
| | 01:09 | It does this so you can run the copy of
Apache included with the MAMP side by side with
| | 01:14 | a copy of Apache included with the Mac.
| | 01:17 | But for this course, you'll need to run
Apache and MySQL on their standard default ports.
| | 01:22 | To do this, go to this Preferences dialog
to the Ports tab and click Set to default
| | 01:27 | Apache and MySQL ports, and you'll see that the
my SQL port is set to the standard port of 3306.
| | 01:34 | That's what Java and JDBC will expect.
| | 01:37 | I'll click OK, wait for the servers to
restart, and then click Open start page, and here I'll
| | 01:43 | see that MySQL is running
on the correct port of 3306.
| | 01:47 | If you're using any of the other configurations of Apache,
MySQL, and PHP, this port should already be set for you.
| | 01:55 | MAMP is the only software bundle that I'm
aware of where you need to make this change.
| | 01:59 | Now the next step, once you have confirmed that
MySQL is running is to import a database that
| | 02:04 | I'll be using throughout this course.
I'll go to phpMyAdmin.
| | 02:07 | If you're not sure where to find phpMyAdmin on your
configuration, check the particular server bundle's documentation.
| | 02:14 | Within phpMyAdmin, I'm
going to create a database.
| | 02:17 | I'll go to the Databases tab, I'll click
into the text box, and I'll type the name of the
| | 02:22 | database I want to create, explorecalifornia.
| | 02:25 | If you're following along, make sure that
you spell the name of the database exactly
| | 02:29 | this way, all lowercase and all one word, because all
of the exercises throughout the course will expect it.
| | 02:36 | Click Create, and now your
database has been created.
| | 02:39 | Now, I'm going to import an SQL script that's provided
as part of the Free exercise files that accompany this course.
| | 02:46 | I have copied by Free exercise files to the desktop,
but you can find them wherever you installed them.
| | 02:52 | Within the Free exercise files there is a
database folder, and within that folder there's
| | 02:57 | an SQL file called exploreCalifornia.sql.
| | 03:01 | This is a simple text file, and
you can open it in any TextEditor.
| | 03:05 | Within this SQL file there's a command to use
explorecalifornia, the database that I have just created.
| | 03:11 | And then the five tables that are a part of
this database are dropped if they already exist.
| | 03:17 | So if you want to refresh your database, you
can just run this SQL script in the future,
| | 03:22 | and it will delete all of the existing data and
refresh the database table structure and content.
| | 03:28 | Little further down in the script, for each
table there's a create statement that describes
| | 03:33 | the structure of the database and a series of
Insert statements that populate a table with data.
| | 03:38 | There are tables named Admin,
explorers, packages, tours, and states.
| | 03:44 | And the states table has 50 rows.
| | 03:46 | I'll close my TextEditor,
and I'll return to phpMyAdmin.
| | 03:50 | Now I'm ready to import my database.
| | 03:53 | I'll click into the
database and click the Import tab.
| | 03:56 | I'll browse to select my SQL file.
| | 03:58 | I'll be sure to select the
file with the file extension .sql.
| | 04:03 | There are also files with extensions of .script
and .properties, but those are for use with
| | 04:08 | another Database Management System.
| | 04:10 | I'll select the file and click Open, and then I'll
scroll down to the bottom of the screen and click Go.
| | 04:15 | If everything goes well, I'll see a message
that the import has been successfully finished,
| | 04:20 | and if I click into the database again
I'll see my five tables have been created.
| | 04:24 | For any of these tables, I can
click into the name of the table.
| | 04:27 | For example, I'll click into states,
and I'll see the listing of all the data.
| | 04:31 | Just see the structure of the database table,
including the names of the columns which is
| | 04:36 | important to know when programming with JDBC.
| | 04:39 | I'll click this Structure tab, and I can also
click any of the other tabs to run SQL statements,
| | 04:45 | Search, Insert data, Export,
Import, and Execute other operations.
| | 04:50 | So if you successfully followed through
the steps, you'll have a working MySQL Server
| | 04:55 | installation, and you'll have imported the sample
database that I'll be using throughout this course.
| | 04:59 |
| | Collapse this transcript |
| Creating a database user in MySQL| 00:00 | In order to make the coding of our exercise
files consistent, I'm going to show you how
| | 00:04 | to create a database user that has access
to the MySQL database server, and we'll use
| | 00:10 | the credentials for that
user throughout the course.
| | 00:13 | In phpMyAdmin go to the home-screen, from
there click on the Users tab, depending on
| | 00:20 | how you installed MySQL and what software
bundle you used, you might see a variety of
| | 00:26 | user identities in this list.
| | 00:28 | Minimally, you should see one named root, which is the
super user or administrator of the entire database server.
| | 00:35 | It's typically a good idea to
create your own database users.
| | 00:39 | So on this screen I'll click Add user.
| | 00:42 | I'll set my username and password to
some very simple easy to remember values.
| | 00:47 | I'll set the User name to dbuser, all lowercase.
| | 00:52 | For the Host, I'll pull down the list and
choose Local, and that sets the host to localhost.
| | 00:59 | I'll click into the Password, and I'll type a
simple string of dbpassword, again, all lowercase,
| | 01:05 | and I'll type it twice to
make sure I got it right.
| | 01:09 | Now I'll scroll a little further down, and
I'll click the Check All link, and that adds
| | 01:14 | all privileges to this user.
| | 01:16 | dbuser will be able to access all databases on the
server and do anything that the database server allows.
| | 01:23 | I'll go down to the bottom of the screen and click Add user,
and I should see that my database user has been created.
| | 01:30 | So that's the user identity that I'll
use throughout all of these exercises.
| | 01:35 | If you have any trouble connecting, you
might try deleting the user and then re-creating
| | 01:40 | it again and make sure that you type in the password
carefully so that it's something you'll easily remember.
| | 01:50 |
| | Collapse this transcript |
|
|
2. Getting Started with JDBCWhat is JDBC?| 00:00 | In this video series, I describe how to
integrate applications built with the Java programming
| | 00:05 | language with relational databases using JDBC,
and I'm going to start by describing the nature
| | 00:11 | and history of JDBC and how it
fits into the larger Java World.
| | 00:15 | JDBC is an API, a set of interfaces and
classes that let you easily connect to relational
| | 00:22 | databases, such as Oracle, SQL
Server, MySQL, and many others.
| | 00:27 | It was originally known as Java Database Connectivity, but
more recently it's been known by simply the acronym JDBC.
| | 00:34 | JDBC was introduced into the Java
programming language very early.
| | 00:38 | It was included in JDK1.1 in 1997, and has been a part
of all releases of Java Standard Edition ever since.
| | 00:46 | The history of JDBC is a history of
added features and improved performance.
| | 00:52 | The original release in 1997 included the
main classes and interfaces that you'll find
| | 00:57 | yourself using all the time, including the
Connection interface which lets you make your
| | 01:01 | initial connection to a database, the
Statement which encapsulate SQL code, and ResultSet
| | 01:07 | which returns data from the server.
| | 01:10 | Later versions of JDBC improved the features
of the API, including the ability to update
| | 01:15 | your data without SQL, improved performance,
pooled connections, scrolling, and more data types.
| | 01:22 | More recent releases have included more data
types, the ability to work with stored procedures,
| | 01:28 | and the ability to get metadata, or lists of
tables and column information, from your database.
| | 01:34 | In the most recent versions of Java, JDBC 4.0
was released with Java 6, and it included
| | 01:40 | the ability to load drivers automatically.
| | 01:43 | I'll describe the benefit of this
feature in an early video of this series.
| | 01:47 | And in the most recent version of JDBC,
JDBC 4.1 which is included in Java 7, there are
| | 01:53 | features that let you reduce the amount of code it
takes to work with your databases. So who uses JDBC?
| | 01:59 | It's most commonly used in web-based applications that
are hosted in JEE or Java Enterprise Edition servers.
| | 02:07 | These include J Boss,
Tomcat, WebSphere, and others.
| | 02:11 | Developers also commonly use JDBC when
they're working on desktop applications or applets
| | 02:16 | that are working either with local databases
stored on the client computer or with remote
| | 02:21 | databases accessed over the Internet.
| | 02:24 | Less common uses include
JDBC in Android applications.
| | 02:28 | Android has its own API for working with
local databases, specifically SQLite, and so
| | 02:34 | developers typically don't use JDBC there.
| | 02:37 | And when you're working with larger databases
making calls from Android applications, it's
| | 02:41 | more common to make calls to those databases
through web services hosted by middleware servers.
| | 02:47 | But if you're a Java programmer, it is
important to understand what JDBC is and how it works,
| | 02:53 | because even if you're working through
Android or through web services, someone somewhere
| | 02:57 | is probably using JDBC somewhere in your
calling chain, and it's useful to know how it works.
| | 03:03 | There are other ways of getting to
databases without doing direct JDBC programming.
| | 03:08 | There are higher-level abstractions that are
delivered as part of the large application frameworks.
| | 03:13 | For example, the Spring application
framework includes something called JDBC Template.
| | 03:18 | It simplifies the amount of code you have
to write, but in the background it's using
| | 03:23 | JDBC to talk to the database.
| | 03:25 | There's a similar but perhaps less popular templating
library called RIFE, which does basically the same thing.
| | 03:32 | And then there are data mapping
APIs, the most popular is Hibernate.
| | 03:36 | Hibernate is something called
an object-relational mapping API.
| | 03:40 | It represents your database
structure with Java classes and objects.
| | 03:44 | In the background it's still using JDBC to
communicate with the database, but it simplifies
| | 03:49 | the amount of code you have to
write in your own application.
| | 03:53 | And there are other mapping libraries, such
as iBATIS from Apache, and the Java Persistence
| | 03:58 | API or JPA, which is actually a
part of the Java EE platform.
| | 04:03 | Again, regardless of whether you use JDBC
directly, or you use one of these higher-level
| | 04:08 | application frameworks or data mapping
APIs in the background, JDBC is at work.
| | 04:14 | So by working through this video series,
you'll be able to gain a greater understanding of
| | 04:19 | how the Java programming language connects to databases
and what are some of the pitfalls and benefits of using JDBC.
| | 04:25 |
| | Collapse this transcript |
| Choosing a JDBC driver| 00:00 | Applications that use the
JDBC API require drivers.
| | 00:05 | A JDBC driver is a software library that
encapsulates the logic you need to communicate between
| | 00:11 | your application and a database management system
or a database file stored on the local hard disk.
| | 00:17 | All JDBC drivers follow the same set of rules that
is the API, that's defined in Java Standard Edition.
| | 00:24 | A driver library package will contain
specific implementations of these Java interfaces.
| | 00:30 | Connection, which lets you connect to the
database, ResultSet, which encapsulates data returned
| | 00:35 | from the database, Statement, PreparedStatement,
and CallableStatement, which represent requests
| | 00:41 | to the database, and many more.
| | 00:44 | Not all JDBC drivers will support all
features of the JDBC spec. Check the documentation
| | 00:50 | for your particular database and your
particular driver to see what it's able to do.
| | 00:55 | But nearly all JDBC drivers
will support these five interfaces.
| | 00:59 | Typically, you'll get your driver packages
from the database vendors themselves, a MySQL
| | 01:05 | driver from MySQL an Oracle
driver for Oracle, and so on,
| | 01:08 | but there are also third-party
drivers available both free and commercial.
| | 01:12 | There are four distinct types of drivers,
distinguished by their architecture.
| | 01:17 | The oldest type of driver is called the
Type 1 driver or the JDBC-ODBC Bridge.
| | 01:23 | In the mid to late '90s when JDBC got started,
ODBC or the Open Database Connectivity protocol
| | 01:30 | was the dominant model for
communicating with the database.
| | 01:33 | And so Java first communicated
with databases through ODBC.
| | 01:37 | You would use the JDBC-ODBC Bridge driver,
and then you would also use a separate ODBC
| | 01:43 | driver that was designed
for your specific database.
| | 01:46 | At runtime, requests would go from the
application through the JDBC API to the Bridge driver
| | 01:52 | from there to the ODBC
driver and then to the database.
| | 01:56 | This wasn't particularly fast, but it was
dependable, and one of the pros was that you
| | 02:01 | could talk to any database for which an ODBC driver existed,
and that meant pretty much every relational database.
| | 02:08 | The downsides of the ODBC Bridge driver
are that it's not 100% Java and therefore not
| | 02:13 | portable between operating systems.
| | 02:15 | Also you're working with two drivers not
just one, and they both have to be on the same
| | 02:19 | computer as the application, so
you have increased maintenance.
| | 02:23 | And finally, the ODBC driver has to match
the database version, and so if you updated
| | 02:28 | your database on the server, you'd have to go around
to all the client applications and update those as well.
| | 02:34 | The Type 2 driver combines a
native API driver and a Java driver.
| | 02:40 | They're both installed on the client system
just like with the Bridge driver and the ODBC
| | 02:44 | driver, but because you're working primarily
with native APIs, the Type 2 driver tends to
| | 02:49 | be very fast and can give you
the best possible performance.
| | 02:53 | Once again, just like the Bridge driver, you're
not working in 100% Java, and therefore, your
| | 02:58 | applications aren't portable
between operating systems.
| | 03:01 | The native API driver has to be installed
on the application client and maintained,
| | 03:06 | and once again, if the database is updated,
the client software has to be updated as well,
| | 03:10 | but if you want the best possible
performance, a Type 2 driver might be the way to go.
| | 03:15 | The Type 3 driver actually is installed in
multiple locations. You'll have 100% Java
| | 03:21 | driver that's installed in the client along
with the application, but then with the Type 3
| | 03:26 | driver architecture, you'll have a middleware
server which hosts its own application, requests
| | 03:32 | go at runtime from the application to the
Type 3 driver that's installed on the client
| | 03:36 | and from there over the network to the
middleware server and from there to the database.
| | 03:41 | With a Type 3 driver, the middleware driver
can be native, and so the communication between
| | 03:46 | the middleware and the database can be
very fast, but once again, you have maintenance
| | 03:51 | challenges because you have
more than one driver to maintain.
| | 03:54 | The fourth type of driver, and one of the most
common, is called the Type 4, or the 100% Java thin driver.
| | 04:02 | With a 100% Java driver, there is only one
driver package, not two, and the driver is
| | 04:07 | packaged with the Java application itself,
whether on a client computer, or in a web environment
| | 04:13 | on a Java Enterprise Edition application server.
| | 04:16 | Requests go from your application to the driver
that's on the client and then your communications
| | 04:21 | go from the application through JDBC
through the thin driver to the database server if
| | 04:26 | it's out on the web, or to the
database file if it's on the local hard disk.
| | 04:31 | With the Java thin driver, you're communicating
directly from the application to the database.
| | 04:35 | There are no additional layers to install or
maintain, and so maintenance is greatly simplified.
| | 04:41 | The only downside is that you need a different
driver package for each database you're working with.
| | 04:47 | Most applications will only use a single
database type, but if you're working with more than
| | 04:50 | one database management system,
you'll need to provide multiple drivers.
| | 04:55 | In this course I'll be doing all of my
demonstrations with the two Type 4 drivers.
| | 05:00 | One will be for the MySQL database server,
and with this driver I'll show you how to
| | 05:05 | communicate with a database
server that's accessed over the web.
| | 05:08 | I'll be actually working with a local copy
of MySQL and addressing it as local host,
| | 05:14 | but the code I'll be using would work just as well
if the MySQL server were hosted out in the Cloud.
| | 05:20 | The other database I'll be working with in
this course is called HyperSQL, or HSQLDB.
| | 05:25 | HyperSQL is one of many pure Java databases
that are used by Java developers, others include
| | 05:32 | the Apache Derby database and H2.
These databases run as in-memory databases.
| | 05:38 | But the database is initialized with a local file, but
then it runs in the same Java process as the application.
| | 05:45 | So with these two drivers and these two
database management systems, I'll be able to show you
| | 05:49 | how to handle differences between databases
while making your code as portable as possible,
| | 05:55 | and I'll also be emphasizing the use of
these Type 4 drivers, which are pure Java and can
| | 06:00 | be encapsulated into your applications.
| | 06:02 | There are other types of drivers out there
that aren't directly recognized by the JDBC
| | 06:07 | API but can also be useful.
| | 06:09 | For example, in 2010 there
was talk of a Type 5 driver.
| | 06:14 | A Type 5 driver has the same architecture
as Type 4 that is it's 100% Java and Thin,
| | 06:20 | but it gives you better
performance than a pure Type 4 driver.
| | 06:23 | And then there are other drivers that don't
have specific type numbers that can run in
| | 06:27 | the same Java process as the application
just like the HyperSQL driver I'll show you.
| | 06:32 | But if you stick with learning the four
main types of drivers that are defined by JDBC,
| | 06:38 | Types 1, 2, 3, and 4, those will cover the vast majority
of JDBC drivers that are available to Java developers.
| | 06:46 |
| | Collapse this transcript |
| Connecting to a MySQL database server| 00:00 | The first thing to learn in working
with JDBC is how to connect to a database.
| | 00:05 | I'm going to start with MySQL.
| | 00:07 | To connect to MySQL, you'll need a Java driver, and
you can get a free driver from mysql.com/downloads.
| | 00:13 | On this screen, scroll down to the
Connector section and look for Connector/J.
| | 00:21 | You can get the most recent
version of Connector/J from this website.
| | 00:24 | But to make this very easy, I have included a copy of the
driver in the free exercise files that accompany the course.
| | 00:31 | Go to the Free exercise files, to the libs
folder, and locate them mysql jar file and
| | 00:36 | copy it to the clipboard.
| | 00:38 | Now go to Eclipse and open this project,
ConnectMySQL from the exercise files.
| | 00:44 | If you don't have access to the exercise
files, create a new Java project, add this class
| | 00:49 | to the package com.lynda.javatraining.db
and type in the code that you see here.
| | 00:56 | That creates three constants named
USERNAME, PASSWORD, and CONN_STRING.
| | 01:00 | Now I'm going to add the
MySQL driver to my project.
| | 01:04 | I'll right-click on the project in the
Package Explorer, and I'll create a new folder.
| | 01:08 | You can name the Folder anything you like.
| | 01:10 | I'm going to name it libs for libraries,
then I'll Paste the jar file into the folder.
| | 01:17 | Now, just because this jar file is a part of
the project, doesn't make its code automatically
| | 01:22 | available to the code in my application.
| | 01:24 | I have to add the JAR file
to the project's Build Path.
| | 01:28 | To do that, I'll right-click on the JAR file,
I'll choose Build Path > Add to Build Path.
| | 01:33 | Later on I can configure the Build Path by
right-clicking again, and once again going
| | 01:38 | to Build Path, and this time selecting
Configure Build Path, and that will take me to the Java
| | 01:43 | Build Path Category of my Properties panel.
| | 01:46 | I have added the JAR file to the Build Path,
and I should now be able to call its code.
| | 01:50 | To connect to MySQL database, you need these
three string values, the USERNAME, the PASSWORD,
| | 01:56 | and the CONN_STRING or URL.
| | 01:59 | The USERNAME and PASSWORD are
determined by the users in your database.
| | 02:02 | I created the USERNAME, dbuser, and assigned
it this password in a previous video of this
| | 02:08 | series, and if you don't have that
user set up, you should set it up now.
| | 02:12 | So I'm going to uncomment those two
lines of code, and they're ready to use.
| | 02:16 | This last line of code creating the CONN_STRING
has a format that's determined by the driver.
| | 02:21 | The MySQL driver requires a CONN_STRING
that starts with jdbc in lower case, then MySQL
| | 02:28 | also in lowercase separated with colons.
| | 02:30 | After MySQL, there's ://, then the DNS
name or IP address of the host server.
| | 02:38 | I'm working with MySQL on my local system,
so the address of my server is localhost.
| | 02:43 | But if I were working with a remote server, I
might put in the IP address or that server's name.
| | 02:48 | Finally, the last part of the string is
the name of the database I'm connecting to.
| | 02:53 | So again, if you don't have access to the
exercise files that accompany the course,
| | 02:57 | you can create this code easily.
| | 02:59 | Now I'll move the cursor
to within the main method.
| | 03:01 | In Java 6 and Java 7 you can jump
directly to connecting to the database.
| | 03:06 | But in previous versions of Java, you first
have to load the driver class into memory,
| | 03:11 | and you do this with code that looks like
this, Class.forName, and then you pass in the
| | 03:16 | fully qualified name of
your driver class as a string.
| | 03:20 | The name of the MySQL
driver is com.mysql.jdbc.Driver.
| | 03:26 | If you type that code in, you'll see that
a warning is displayed, and if you move the
| | 03:30 | cursor over the Warning indicator you will see an
Unhandled exception type of ClassNotFoundException.
| | 03:37 | I'll click on the warning, and I'll add
a throws declaration to the main method.
| | 03:41 | So here's what this code is doing.
| | 03:43 | Class.forName is used in Java
to load a class dynamically.
| | 03:47 | That is by loading its fully
qualified name as a string.
| | 03:51 | It loads the class into memory, but it doesn't create
an instance of the class, that is it doesn't instantiate it.
| | 03:57 | Now in Java 6 and Java 7, you don't need this code, and
I'm working in Java 7, so I am going to comment it out.
| | 04:04 | Now the next step is to create an
instance of a class called Connection.
| | 04:08 | I'll type in the name of the class, and it's
actually an interface, and I'll press Ctrl+Space.
| | 04:14 | For MySQL, you'll see that there are two
interfaces available, one in the packagejava.sql and
| | 04:20 | one in com.mysql.jdbc.
| | 04:23 | You should always choose a
version of an interface from java.sql.
| | 04:27 | This will make your code more portable so
you'll be able to take the same code that
| | 04:30 | you wrote for MySQL and point it
instead at another database like SQL Server.
| | 04:35 | I'll select the Connection interface from
java.sql, and that adds the import statement.
| | 04:40 | Then I'll name the new object conn, and
I'll assign it an initial value of null.
| | 04:45 | The next step is to connect to the database.
| | 04:48 | I'll type in conn =, and I'll call a
method called DriverManager.getConnection.
| | 04:53 | I'll type in the name of the class, DriverManager,
and press Ctrl+Space, and that adds an import
| | 04:58 | statement for that class.
| | 05:00 | After I have added the import statement, I'll
type in a dot and press Ctrl+Space, and I'll
| | 05:04 | see that there are three
versions of the getConnection method.
| | 05:07 | I'm going to use the third version of the
method that looks for a url, a user, and a password.
| | 05:13 | For the url ,I'll choose CONN_STRING, I'll set the
user to the USERNAME, and the password to the PASSWORD.
| | 05:20 | I'll complete the code, and I'll expand my
editors so we can see all of it, and that's
| | 05:24 | all the code that I need
to connect to my database.
| | 05:27 | Now once again, I'm seeing the warning indicator,
so I'll move the cursor over the warning indicator,
| | 05:32 | and I'll see that I have an
Unhandled exception type of SQLException.
| | 05:36 | When you call to getConnection method if it
succeeds everything will be fine, but if it
| | 05:41 | fails in some way it will throw an
exception, and you'll want to handle it explicitly.
| | 05:46 | So I'll click on the warning indicator, and this
time I'll surround the code with a try-catch block.
| | 05:52 | Within the catch block which is catching
SQLException, I'll delete the comment and the call to print
| | 05:57 | stack trace, and then I'll add error output,
I'll type in syserr, a code template and press
| | 06:03 | Ctrl+Space, and I'll output
the error to my error output.
| | 06:07 | The final step is to close the connection.
| | 06:10 | For all JDBC resources, including connections,
statements, and results sets, if you open
| | 06:17 | them you have to close them. There are a
few different ways of doing this, but I'm going
| | 06:21 | to show you the most explicit approach here.
| | 06:23 | I let a finally clause after the catch, and
within a finally clause I'll first find out
| | 06:28 | whether the connection object is null.
| | 06:30 | I'll use if conn not equal to null, and if
that condition is true, I will call conn.close.
| | 06:38 | Now once again, you will see that a warning
is been generated I'll move the cursor over
| | 06:42 | that warning, and I'll see that the call to
the close method can also throw SQLException.
| | 06:47 | Now you can write a whole bunch of complex
code wrapping this if clause inside another
| | 06:52 | try catch block, but a much simpler approach is to
set your main method so that it throws SQLException.
| | 06:59 | So I'll click on that warning indicator, and
I'll Add the throws declaration, I'll go to
| | 07:03 | the Throws Declaration, and because I
commented out Class.forName, I no longer need to look
| | 07:08 | for ClassNotFoundException,
and that's my completed code.
| | 07:12 | Now to verify that I have connected correctly
I'll move my cursor within the try block, after
| | 07:17 | the call to make the connection, and I'll
use system output and an output Connected.
| | 07:23 | Later on I'll show you how to get a lot more
information from the database, including retrieving data,
| | 07:27 | but for now this will be adequate.
| | 07:30 | I'll save my changes, and I'll run my
application, and I get the message connected.
| | 07:35 | Now to verify that this code works the
way I expect, I'll make an intentional error.
| | 07:39 | I'll change my PASSWORD from
dbpassword to dbpassword123.
| | 07:44 | I'll save and run the code again, and this time I
get an SQLException with a message of access denied.
| | 07:50 | And so I know that my code is correctly structured, and
I'm creating the connection if my credentials are correct.
| | 07:56 | I'll change my code back so that it's correct,
I'll test it again, and I'm connected and
| | 08:01 | so now I know I'm ready to move on to the next step,
which is retrieving data from my MySQL database.
| | 08:06 |
| | Collapse this transcript |
| Connecting to a HyperSQL database file| 00:00 | In order to provide a contrast between
different database management systems, I'm going to be
| | 00:05 | showing how to connect
both to MySQL and to HyperSQL.
| | 00:10 | HyperSQL is one of a number of 100% Java-based
databases. Others include Apache Derby and H2.
| | 00:16 | I have chosen a HyperSQL because its databases
are defined in pure text files, which makes
| | 00:22 | them very easy to use in a training environment.
| | 00:25 | You can get the Drivers and all the documentation
for HyperSQL from hsqldb.org. You'll see hyperSQL
| | 00:32 | preferred to both by the name HyperSQL and
HSQLDB. Just as with MySQL, you can download
| | 00:39 | the Drivers for free, but I have provided the
driver for HSQLdb as part of the free exercise
| | 00:45 | files that accompany the course.
| | 00:47 | I'll go to my libs folder, and I'll copy the file
hsqlbd.jar to the clipboard, then I will return to Eclipse.
| | 00:55 | I'm working with a copy of my first project,
this one is called ConnectHSQLDB, and it's
| | 01:00 | available in the exercise files, this
starting version of the project is already set up for
| | 01:05 | use with MySQL, and I'm going to make some
changes so that it works with HSQLDB instead.
| | 01:11 | I'll go to the libs folder and then I'll add
the jar file and then just as I did with MySql,
| | 01:17 | I'll add the Driver to the Build Path.
Now I need a database.
| | 01:22 | With MySQL the database was defined in the
server environment, with HSQLDB you define
| | 01:28 | a database in a text file, a script, and
you accompany that script with a properties file.
| | 01:34 | And these are both pure text files, so
you can edit them with any text editor.
| | 01:38 | I have provided a starting sample
database as part of the free exercise files.
| | 01:43 | I'll go to the exercise files root, I'll
go to the database folder, and I'll select
| | 01:47 | and copy these two files explorecalifornia
.properties and explorecalifornia.script.
| | 01:54 | Now I'll go back to Eclipse, and I'm going
to create a new folder in the project, and
| | 01:59 | I'll name that folder data, all lowercase.
And then I'll paste my script files into place.
| | 02:07 | I'm going to open the script file in a text
editor, I'll right-click and select Open With >
| | 02:13 | Text Editor, and then I'll expand
the editor to full screen.
| | 02:16 | This is a database script for HSQLDB, it
has some configuration commands at the top, and
| | 02:21 | then if you scroll down, you'll find
commands that are defining the database.
| | 02:26 | Here there is a command to create a user
named SA when you create a brand-new database for
| | 02:32 | HSQLDB SA, or System Administrator is the
default root, or system administrator ID, and down
| | 02:39 | here there is a command to grant
the DBA permissions to the SA user.
| | 02:44 | Here there is a set of commands that are
defining the structures of the databases.
| | 02:48 | Just as with my MySQL database, their tables are named
Admin, Explorers, Packages, States, and Tours.
| | 02:55 | They have the same basic column names, Datatypes
and Configurations, including Auto Incrementing
| | 03:02 | Primary Keys on four of the five tables.
| | 03:05 | Scroll down further and you'll see the insert
statements that are adding the data to the database.
| | 03:10 | The other file that I pasted into place is
called a properties file, I'll open that one
| | 03:15 | also with a Text Editor, and I'll see that
it just has some information about the nature
| | 03:19 | of the database, which version of the HSQL
database engine was used, when the database
| | 03:24 | was created, the version number,
and whether it's been modified.
| | 03:29 | HSQLDB works at runtime by loading these
files into memory when you first initialize the
| | 03:34 | connection and then the
database is maintained in memory.
| | 03:38 | You can run HSQLDB in either embedded mode or
in server mode. I'll be using it an embedded
| | 03:44 | mode so that the HSQLDB engine is running
in the same Java process as my application.
| | 03:50 | So now I have my HSQLDB driver, and I have
my database, and I'm ready to connect to it.
| | 03:55 | I'll go to the Main.class, and
I'm going to change a few things.
| | 03:59 | First of all, notice that my USERNAME and
PASSWORD are still set to dbuser and dbpassword.
| | 04:05 | I'll go back to my explorecalifornia.script
file, and I'll add that user so that I can
| | 04:10 | make exactly the same kind
of call I did with MySQL.
| | 04:13 | I'll scroll up to the section where the
users are being created, I'll copy and paste the
| | 04:18 | create user command for SA, and I'll change
the name of the new user from SA to dbuser.
| | 04:24 | To make sure that the USERNAME and
PASSWORD stay lowercase and match what I have in
| | 04:29 | MySQL, I'll wrap them both in quotes.
| | 04:31 | Now I'll add my new user to the DBA Permissions,
I'll go down here and locate GRANT DBA to SA
| | 04:38 | I once again copy and paste, I'll change the New Command so
that I'm granting those permissions to dbuser, I'll save my changes
| | 04:45 | to the script, and I'll
go back to my Main.class.
| | 04:48 | Now in my Main.class, I do have to make a
change to my CONN_STRING or URL. Just as with MySQL,
| | 04:55 | you start the CONN_STRING with jdbc, but
then instead of mysql, you type in hsqldb.
| | 05:02 | Instead of localhost, you will be loading this
database from the local disk so type in data
| | 05:08 | the name of the folder, which is a
subfolder of the project, and then you refer to your
| | 05:12 | database by the name of the database as the first part of
the two filenames explorecalifornia.script and .properties.
| | 05:20 | Don't include the file extensions ,only
include the beginning part of the name.
| | 05:25 | So now I have made my changes to the code, I'm
using the same USERNAME and the same PASSWORD
| | 05:30 | but a different connection string.
| | 05:31 | I'll save my changes, and I'll run my application,
and just as with MySQL, I see a message indicating
| | 05:37 | that I have successfully connected.
| | 05:39 | Now just like with MySQL, I'll make
sure that I'm getting a message that's true.
| | 05:43 | So I'll change my USERNAME to something that
doesn't exist, and I'll try to connect again.
| | 05:48 | And I get an exception named SQLInvalidAuthorizationSpecException,
a different exception that I got with MySQL.
| | 05:57 | And this is an indication that when you're
working with different databases, what's happening
| | 06:01 | in the background with Java might
differ in some very interesting ways.
| | 06:06 | But I know that I connected successfully,
so I'll return my code back to that state
| | 06:10 | with the correct USERNAME, the correct
PASSWORD, and the correct CONN_STRING.
| | 06:14 | I'll connect again, and now I know that that
code is working, and I'm able to successfully
| | 06:19 | connect the HyperSQL or HSQLDB.
| | 06:24 |
| | Collapse this transcript |
| Executing a static SQL statement| 00:00 | Once you have connected to a database, the next
step is typically to send a request, and the
| | 00:05 | most common sort of request is a simple SQL
statement that retrieves data from the database.
| | 00:11 | I'll show how to do this using the Statement
interface and another interface named ResultSet,
| | 00:16 | which encapsulates the data
that's returned from the database.
| | 00:19 | I'm working in a project called StaticSQL.
This project's main class already has the
| | 00:24 | code to connect to the database.
| | 00:26 | It has the three strings, the User
name, Password, and Connection string.
| | 00:30 | And I'm working with an HSQLDB database.
| | 00:33 | In addition to the connection, I also need
an instance of Statement and an instance of ResultSet.
| | 00:39 | When you call methods of these interfaces,
just as with the methods of the
| | 00:43 | Connection class, they
might throw an SQL exception.
| | 00:46 | So typically, you'll declare the objects outside
of the try block and then execute their methods
| | 00:52 | within the try block, and that will make them
have the lifetime invisibility that you need.
| | 00:57 | I'll move my cursor after the Connection
declaration, and I'll declare a Statement.
| | 01:01 | I'll type in the name of the interface and
press Ctrl+Space, and I'll choose the version
| | 01:05 | of statement from java.sql.
| | 01:08 | Again, my goal is to make this code as
portable between databases as possible.
| | 01:12 | I'll name this object
stmt and set it to null.
| | 01:15 | Next, I'll declare my ResultSet, and again,
this is the interface that will contain the
| | 01:20 | data returned from the database.
| | 01:22 | I'll name that RS, and again,
I'll set it initially to null.
| | 01:26 | Now I'm ready to create my statement.
| | 01:28 | I will move the cursor after the code that's connecting
to the database, and I will make a little bit of space.
| | 01:34 | I'll instantiate the statement using this code, stmt,
the name of the object, equals conn.create statement.
| | 01:42 | Notice that there are a few different
versions of the create statement method.
| | 01:45 | A default Statement object is returned
when you don't pass in any arguments.
| | 01:50 | There's also a version of the method where
you can set the type of the ResultSet and
| | 01:54 | its concurrency, and that's the
version that I'm going to use.
| | 01:57 | The default ResultSet type will
differ from one database to another.
| | 02:01 | With MySQL, the default ResultSet object is
scrollable. That means that you can move the
| | 02:06 | cursor up and down, going to the first row,
the last row, and moving around as you like.
| | 02:12 | But with many databases, including HSQLDB,
the default ResultSet type is forward only,
| | 02:19 | meaning that the cursor will start before
the data, and you'll be able to move forward
| | 02:22 | to the end, but you won't
be able to move back again.
| | 02:25 | I'd like to set up my code to override that
default and get a ResultSet that's scrollable.
| | 02:31 | So I'm going to set the ResultSet
type to a constant that looks like this.
| | 02:35 | ResultSet, and I'll choose the constant
TYPE_SCROLL_INSENSITIVE. That means that it's scrollable,
| | 02:42 | but it won't be sensitive to any
changes in the underlying data.
| | 02:45 | Essentially, it's a read-only ResultSet.
| | 02:48 | Now I also need to set something called
resultSetConcurrency, and this will indicate whether I'm working
| | 02:54 | with a read-only ResultSet or an updatable
ResultSet, a ResultSet that has a live connection
| | 02:59 | to the underlying database.
| | 03:01 | For the best performance, if I'm only going
to be reading the data, I'll set the ResultSet
| | 03:06 | to read-only, and I'll do that with another
constant of ResultSet called CONCUR_READ_ONLY.
| | 03:12 | So now I have created my statement object, and
I'm ready to send the request to the database.
| | 03:17 | I'll instantiate my rs or ResultSet object, and I'll get
data back by calling the Statement object, executeQuery method.
| | 03:25 | When you call executeQuery, you
can pass in any StaticSQL string.
| | 03:30 | The nature of the SQL command is
determined by the database you're working with.
| | 03:34 | There's a common set of SQL commands that
work across all databases, but there are many
| | 03:39 | variations from one database to another.
| | 03:41 | So again, for portability,
I'm going to keep it simple.
| | 03:44 | I'll call the executeQuery method, and I'll execute
the SQL statement select asterisk from states.
| | 03:51 | States is the name of a database table
that exists both in the MySQL database and the
| | 03:56 | HyperSQL database that I'm working with.
| | 03:59 | Now, to test the results, I'm going to
find out how many rows are returned.
| | 04:03 | In JDBC, there isn't a single property of
the ResultSet object that you can look at.
| | 04:08 | So instead, you move the cursor to the last
row of the ResultSet and then find out what
| | 04:14 | row number you're on.
I'll use this code rs.last.
| | 04:18 | The last method means move the cursor to the
end of the ResultSet, and then I'll do a little
| | 04:23 | bit of system output, and I'll output a
string of number of rows, and I'll append to that
| | 04:29 | the result of the method getRow,
which looks like this, rs.getRow.
| | 04:34 | The getRow method returns an integer value,
which represents the current row number.
| | 04:39 | The JDBC ResultSet starts
numbering its rows from one.
| | 04:43 | So if I have a table of three rows, the
getRow method would return one for the first row,
| | 04:48 | two for the second, and three for the third.
| | 04:50 | That's different than a Java array
which starts counting at zero.
| | 04:54 | And as long as you know the difference between
the two, the numbering system for arrays versus
| | 04:59 | the numbering system for
databases, you'll be in good shape.
| | 05:02 | I'll comment out the remaining System out
Command, saying I'm connected, because now
| | 05:07 | I'm interested in finding out
how many rows of data I have.
| | 05:10 | So that's it. I'll save my
changes, and I'll run the code.
| | 05:13 | And I get back the message number of rows
50, and that's correct. There are 50 states
| | 05:19 | and 50 rows in the states table in my database.
| | 05:22 | I'll clean up my code, I'll delete unneeded
comments and close up some extra white space,
| | 05:27 | and then I'll test it one last time, and
I'll see that I'm successfully connecting,
| | 05:32 | that I'm getting back a scrollable ResultSet
where I can move the cursor up down and back and
| | 05:36 | forth as much as I need to, and I'm
able to get the data back that I need.
| | 05:41 | Finally, I'll make sure
that I have closed my resources.
| | 05:44 | I'll return to my code, and I move down to
my finally block where I am checking for the
| | 05:49 | connection, making sure it's
not null, and then closing it.
| | 05:52 | Just like the Connection object, you need to explicitly
close your Statement object and your ResultSet object.
| | 05:58 | I'm going to copy this code and
paste it in a couple of times.
| | 06:02 | You should close objects in
reverse order for how they were created.
| | 06:07 | So the Connection was created first, then
the Statement, and then the ResultSet, so
| | 06:11 | I'm going to close them in reverse order.
| | 06:13 | I'll change my first if clause to rs,
and if it's not null I'll close it, then I'll
| | 06:19 | change the second block to the Statement,
changing both the conditional logic and the
| | 06:23 | call to the close method.
| | 06:25 | All three of these interfaces have this close
method, and again, it's up to you as the developer
| | 06:30 | to make sure that you're closing
these resources when you're done.
| | 06:33 | If you forget to close a resource, with a file-based
database you'll probably be fine, but with
| | 06:39 | a network environment you might end up
leaving a connection open to your database server,
| | 06:44 | and that can cause problems
at the server level later on.
| | 06:47 | Just make sure you have closed all of your resources
when you're finished, and you'll be in great shape.
| | 06:52 | Having made some more changes, I'll run the
code one last time. I'm seeing I'm connecting
| | 06:56 | and getting the data back, and
so all this code is complete.
| | 06:59 | And this can be used as a good model for
how you execute any StaticSQL statement.
| | 07:04 |
| | Collapse this transcript |
|
|
3. Managing Database ResourcesConnecting to multiple databases| 00:00 | To make it easier to switch between
databases. I'm going to re-factor the code that I have
| | 00:05 | written so far, so I can easily switch from MySQL to
HSQL by simply passing in a single argument to a method.
| | 00:13 | My goal is to show that once you have a
connection, the code you use to make a request to the
| | 00:17 | database and process the results is almost
exactly the same from one database platform to another.
| | 00:24 | I'm working in a project called MultiConnect
where I have versions of the Main class that
| | 00:28 | I have already coded in other projects.
| | 00:30 | I have one class that connects to MySQL, and
I'll test that and make sure that it's working,
| | 00:35 | and I have one that's connecting
to HSQL, and I'll test that as well.
| | 00:39 | Now, I have created a separate Utility class called DBUtil
which only currently has some commented out code.
| | 00:46 | I'm going to uncomment that code, and
you'll see that there are four private strings,
| | 00:51 | a username and a password, which will be
usable on both databases because I have set it up so
| | 00:56 | that the same user name, and the same password
are available on both, and then two versions
| | 01:01 | of my Connection string, one
for HSQLDB and one for MySQL.
| | 01:07 | This class will have static methods that
either return objects or processed results, and it's
| | 01:13 | first job will be to have a static method
that returns a particular kind of connection
| | 01:19 | whatever kind of connection I request.
| | 01:21 | I will place the cursor after the static
final string declarations, and I'll declare a new
| | 01:27 | public static method that returns an
instance of the Connection interface.
| | 01:32 | As I have done in other classes, I'll be sure to
include the import statement for the Connection
| | 01:37 | interface from java.sql.
I'll name the static method getConnection.
| | 01:42 | Now, in order for my application to be able
to request the type of database it wants to
| | 01:47 | talk to, I'm going to create an enumeration,
| | 01:50 | a set of possible values, and they
will match the two types of databases.
| | 01:54 | I will go to my package again, and
I'll right-click and select New > Enum.
| | 01:59 | I'll name my enumerator DBType.
| | 02:03 | I'll create it, and then within the enum declaration, I'll
type in constants representing each of the two databases,
| | 02:11 | HSQLDB and MySQL, and that's all I need.
| | 02:14 | The entire purpose of this enumerator is to
guarantee that I'm requesting a database that I know exists.
| | 02:20 | I'll save those changes and
return to my Utility class.
| | 02:24 | Now when getConnection is called, I require that the
database type be passed in, so I'll add an argument.
| | 02:31 | Its data type will be DBType, the name of
the enum I just created, and its name will
| | 02:36 | be dbType with a lowercase D-B.
| | 02:39 | I'll expand the editor to full screen, and
now within the call to the method, I'm going
| | 02:44 | to inspect that arguments value and find out
which type of database I'm supposed to connect to.
| | 02:49 | I'll add a switch statement.
| | 02:52 | I'll type in switch and press Ctrl+Space,
and choose the switch case structure, and
| | 02:56 | for the Key I will look at dbType,
the argument that was passed in.
| | 03:00 | The first case I'll inspect will be MySQL,
the type from the enum class, and then before
| | 03:07 | I fill in the code for this
case I'm going to duplicate it.
| | 03:10 | I'll select these three lines and do a duplication,
and I'll change the case statement for the
| | 03:15 | second case, for MySQL to HSQLDB.
| | 03:19 | Now I'm ready to make and
return my Connection object.
| | 03:23 | For MySQL, I'll use a return statement, and
I'll call a driver manager, making sure, again,
| | 03:28 | to add the import statement, then I'll
call the getConnection method. Once again, as I
| | 03:33 | have previously, I'll use the three arguments
version of the getConnection method, and for
| | 03:38 | MySQL I'll pass in the Connection string M_CONN_STRING,
and then the USERNAME and the PASSWORD.
| | 03:45 | I'm not going to need my Break command here
because I'm immediately returning the Connection object.
| | 03:51 | Now I'll copy that code and paste it into
the next case, I'll once again delete the
| | 03:56 | break statement that I don't need, and I'll
go to the second call and change the Connection
| | 04:01 | string from M to H.
And for the default, I'll return null.
| | 04:06 | You'll see that there are warnings being
displayed in Eclipse, I'll move the cursor, and I'll
| | 04:10 | see that I have an unhandled
exception of SQL exception.
| | 04:14 | To keep this simple, I'm just going to add a
throws declaration to my method signature.
| | 04:19 | I'll click the warning and
choose Add throws declaration.
| | 04:22 | I save the change, and now
my DBUtil class is complete.
| | 04:26 | Its entire purpose is to receive a
type and return a connection object.
| | 04:31 | Now I'll put this utility class to use.
I'll go back to the Package Explorer.
| | 04:36 | And I'm going to use this class, ConnectHSQL.
| | 04:39 | I'm going to duplicate this class, I'll copy
and paste, and I'll name the new copy Main,
| | 04:46 | and this will be my new
Main class for my application.
| | 04:49 | I'm going to close all the other tabs
and expand the editor for this new class.
| | 04:53 | Now for this version of the class I no
longer need to store the Username, Password, and
| | 04:58 | Connection string in the Main class, that's
all being handled now by the database utility.
| | 05:03 | So I'll delete those lines of code, then
I'll go down to the try block where I'm getting
| | 05:07 | the Connection, and I'm
going to comment out that call.
| | 05:11 | So I'm no longer getting the Connection
directly from the driver manager, and I'll replace
| | 05:15 | it with this code, conn = DBUtil, my new
utility class, and I'll call getConnection, and I'll
| | 05:23 | pass in the type of database
I want from the enum class.
| | 05:26 | I'll start with HSQLDB, and so now I'm making a
connection to the HSQL database and getting data back.
| | 05:33 | I'll save my changes, and I'll run the code,
and I get back 50 rows from the states table
| | 05:38 | that seems to be working.
| | 05:40 | I'll clear the console, and I'll come back to my
call to getConnection, and I'll replace it with MySQL.
| | 05:45 | I have saved my changes and run the code again,
and once again, I get back 50 rows because
| | 05:51 | these two databases have exactly the same
number of rows in the states table and,
| | 05:56 | in fact, the exact same data.
| | 05:57 | So now I have a re-factored application
where I have moved all of the logic for how I get
| | 06:02 | a connection to my database into Utility class,
and I have also hidden the Username and Password
| | 06:08 | and the Connection strings.
| | 06:10 | Now as I start building out my application,
I have a single place in my application to
| | 06:14 | go to get a connection.
| | 06:16 | And if I need to change the way I'm getting
my credentials or how I'm connecting to the
| | 06:20 | database, I have a single
set of code to maintain.
| | 06:25 |
| | Collapse this transcript |
| Handling JDBC exceptions| 00:00 | Just as with all Java programming, when
something goes wrong in JDBC, you'll get an exception.
| | 00:06 | In JDBC, most methods of JDBC objects
throw a class called SQLException.
| | 00:13 | SQLException is extended from the Exception
class, and it inherits all of the tools that
| | 00:17 | are a part of that class, such as getMessage
and getLocalizedMessage, string representations
| | 00:23 | of an error message,
getStackTrace, and many other tools.
| | 00:27 | But JDBC is all about databases, and so its
special exception class SQLException is specifically
| | 00:34 | designed to give you information
about what's going on in the database.
| | 00:38 | SQLException adds these methods, getErrorCode returns an
integer value which tells you what might have gone wrong.
| | 00:45 | The Error code is unique to the particular
database, so a numeric error code for MySQL
| | 00:51 | won't be the same as for HSQLDB, and so on.
| | 00:54 | And there's also a method called getSQLState
which returns a five-character string.
| | 00:59 | It's just like the error code.
| | 01:01 | It's specific to the particular vendor and the particular
database, but it's a string instead of an integer.
| | 01:07 | To find out what these values mean, you'll
need to look at the documentation for your
| | 01:11 | particular database, MySQL,
HyperSQL, SQL Server Oracle, and so on.
| | 01:18 | I'm going to be showing you how to look at
the Exception object and get these values
| | 01:22 | out, but you'll need to look at the
documentation to find out what they mean.
| | 01:26 | There are also methods called
getNextException, setNextException, and iterator.
| | 01:31 | When something goes wrong at the database
level, many times it'll be a cascading set
| | 01:35 | of exceptions, and these methods let you
deal with those stacks of Exception information.
| | 01:41 | Let's take a look at some code.
| | 01:42 | I'm working in a project called JDBCExceptions,
which is just like the projects that I have
| | 01:47 | created previously in this video series.
| | 01:50 | It has the Utility class that lets me
choose which database I want to work with, HSQLDB
| | 01:55 | or MySQL, and then it executes a
single statement and brings back a result.
| | 02:01 | Before I run this code, I'm
going to modify the SQL statement.
| | 02:05 | So I'm more specific about the names of the
columns that I'm getting from the database.
| | 02:10 | I'll get rid of the asterisk and replace it
with stateId and stateName, separated with a comma.
| | 02:16 | These are the names of the actual
columns in my states table in both databases.
| | 02:21 | I'll test the code and make
sure that I have got it correct.
| | 02:24 | I'm working with HSQLDB first, and I get
back to my data, and then I'll change from HSQL
| | 02:30 | to MySQL, and I'll run the code again,
and I see that it works there too.
| | 02:35 | So now I'm ready to
introduce an intentional error.
| | 02:38 | I'll start by getting the structure of my
database wrong. Instead of states, I'll look
| | 02:43 | for data from a nonexistent table named state.
| | 02:46 | I'll run the application,
and I get back an error object.
| | 02:50 | Notice that this error
object is specific to MySQL.
| | 02:53 | It has a pretty lengthy name, but it has the
message Table 'explorecalifornia.state' doesn't exist.
| | 02:59 | Now let's throw the same error for HSQL.
| | 03:02 | I'll change my database type and run the code,
and this time I get back a different error
| | 03:07 | object, and it means the same thing, but the message
is different, user lacks privilege or object not found.
| | 03:14 | Now, to get more information out of this Exception object,
I'm going to add some more detailed exception handling.
| | 03:20 | I'll go to my Database Utility class, DBUtil, and I'm
going to add a new method that I'll call processException.
| | 03:27 | I'll use the keywords public, static, and void,
I'll name the new method processException,
| | 03:33 | and I'll have it receive a single argument data typed
as SQLException, and I'll name the object simply e.
| | 03:40 | For now my only goal is to output the
detailed information about the exception.
| | 03:45 | So I'm going to add three error output lines.
| | 03:47 | I'll use the System.err code template, I'll
expand it, and then I'll duplicate it a couple of times.
| | 03:54 | For the first line I'll
output the error message.
| | 03:57 | I start with a label Error message,
and then I'll call e.getMessage.
| | 04:02 | Now this line of code would be the same
regardless of what kind of Java exception I get.
| | 04:07 | But now I'm going to process the
methods that are unique to SQLException.
| | 04:11 | I'll move the cursor to the second line,
and this time the label will be Error code,
| | 04:18 | and I'll append to that e.getErrorCode.
| | 04:21 | And finally, I'll output the SQL state
the string representation of the error.
| | 04:28 | I'll save my changes to the DBUtil class,
I'll go back to my Main class, and now within
| | 04:34 | the catch block instead of outputting the
error object in its raw form, I'll use my new
| | 04:38 | utility method, DBUtil.processException,
and I'll pass in the Exception object.
| | 04:44 | I'll run the code now, and I see my
error message, my code, and MySQL state.
| | 04:50 | So my goal here is to start showing you
some of the differences between databases.
| | 04:54 | I'm currently working with HSQLDB, notice the Error
code and the SQL state -5501 and SQL state of 42501.
| | 05:04 | I'm going to spell my table correctly,
but now I'm going to misspell a column name.
| | 05:08 | I save the changes and run the code, and I
get back exactly the same Error code and SQL
| | 05:14 | state that I did before.
| | 05:15 | So for HSQLDB, misidentifying a column
or table returns the same exception.
| | 05:22 | Now let's switch back to MySQL.
| | 05:24 | I'll change my database type, I'll leave the
code that's misidentifying the column name,
| | 05:29 | I'll run the application, and this time I get back
a different Error code and a different SQL state.
| | 05:33 | That's expected because now I'm
working with another database, and notice
| | 05:37 | the SQL state is 42S22. Let's remember that.
| | 05:42 | I'll go back to the code, I'll fix the column name so
it's correct, and now I'll get the table name wrong.
| | 05:48 | I'll run the code again, and I get a
different Error code and a different SQL state, 42S02.
| | 05:54 | Now let's explore some other errors.
| | 05:57 | One of the most common errors
is a user authentication error.
| | 06:00 | I'll fix my column names and my table name.
| | 06:03 | I'll go back to my DBUtil class where I'm
storing my user credentials, and this time
| | 06:07 | I'll get my username wrong.
| | 06:09 | I'm working with MySQL, and I get an
Error code of 1045 and an SQL state of 28,000.
| | 06:15 | Now let's switch to HSQLDB, and I get back
another Error code and SQL state, -4001 for
| | 06:22 | the Error code and 28501 for the SQL state.
| | 06:26 | What I'm trying to get across here is that
the Error codes and SQL states are going to
| | 06:30 | differ both from one database to another
but also in their patterns within a database.
| | 06:36 | One database like HSQL might use the same SQL
state for two different but similar conditions,
| | 06:43 | whereas another database might have
distinct SQL states and distinct Error codes.
| | 06:48 | The only way you'll know what these codes
mean--as I have mentioned previously--is to look
| | 06:53 | at the documentation for your particular
database management system, but once you know what
| | 06:58 | these error codes and SQL states mean, you'll
be able to write more detailed and more useful
| | 07:03 | exception handling logic
in your own application.
| | 07:08 |
| | Collapse this transcript |
| Closing database resources in Java 7| 00:00 | As I have previously described in this video series, JDBC
resources need to be closed when you're done with them.
| | 00:06 | The Connection, the Statement, and the
ResultSet are examples of these resources.
| | 00:12 | In class of JDBC code, which I show in this
project Java7Closeable, you declare your resources
| | 00:18 | first and initially set them to null, then
you actually instantiate them within a try
| | 00:23 | block, as I have done here.
| | 00:24 | And then you close them in a finally block
first, making sure that they're not null so
| | 00:29 | that you don't cause yet another error.
| | 00:31 | Now all of this code can feel a little bit
cumbersome, but in Java 6 and prior it's simply required.
| | 00:37 | Java 7, however, gives us a new syntax called
try-with-resources, which when you apply it
| | 00:43 | to these JDBC resources, can
clean up your code significantly.
| | 00:47 | The try-with-resources syntax looks like this.
| | 00:50 | You add code after the try keyword and
before the code block, create a set of parentheses,
| | 00:56 | and then you can instantiate your JDBC resources
within those parentheses, and they'll be closed
| | 01:02 | automatically for you when
the try catch block is executed.
| | 01:06 | You won't need the finally block at all.
| | 01:08 | This works because the kinds of objects
that you can place into the try-with-resources
| | 01:12 | section include any object that implements
interfaces called Closeable or AutoCloseable.
| | 01:18 | Let's take a look at the documentation
for the Connection interface as an example.
| | 01:22 | I'll click on the word Connection, then
I'll go to Dynamic Help, I'll go into the Java
| | 01:27 | docs for the Connection interface, and I'll show that
this connection extends an interface called AutoCloseable.
| | 01:33 | The AutoCloseable interface, which has many
sub interfaces and many implementing classes,
| | 01:39 | has just a single required method
called close, which returns void.
| | 01:43 | So any object that implements this
interface or implements closeable which has the same
| | 01:48 | requirements can be added to a try-with-resources
block, and if you take a look at the Java
| | 01:53 | docs for the ResultSet, the Statement, and
other similar JDBC classes and the interfaces,
| | 01:59 | you'll find that they all have this close
method, and that's because they all implement
| | 02:03 | the interface AutoCloseable or
Closeable. So, let's go back to the code.
| | 02:07 | Here's how I'm going to clean this up. In Java 7,
I'll expand my editor, and then I'm going
| | 02:12 | to move these three lines of code that are
declaring my objects into the try-with-resources section.
| | 02:18 | I'll cut and paste them, and
place them within the parentheses.
| | 02:22 | Now I'm going to take the logic that's
instantiating these objects and move it up as well.
| | 02:27 | First, I'll do with the Connection. I'll
grab the code that's calling the getConnection
| | 02:31 | method, I'll cut it, and I'll replace the
null value with that call, then I'll make
| | 02:36 | the same sorts of changes for the statement.
| | 02:38 | I will move the call to create statement
to where the statement is being created, and
| | 02:44 | I'll move the call to execute query to
where the ResultSet is being created, then I'll
| | 02:49 | delete these three lines of
code which are no longer needed.
| | 02:52 | Now, because these objects have been placed
within the try-with-resources section, when
| | 02:56 | the application is finished executing the
try catch block, their close methods will be
| | 03:01 | called automatically, and if they're null--
that is if they haven't been created for some
| | 03:05 | reason--they won't throw errors.
| | 03:07 | And that means that I
don't need this finally block.
| | 03:10 | I'll select that code, and I'll delete it.
| | 03:12 | I'll select all of my code and go to the
Source menu and choose Correct Indentation.
| | 03:17 | Because I have made so many changes, I want to
make sure that everything is formatted well.
| | 03:21 | I'll save my changes, and I'll run the application,
and I'll see that just as before I'm successfully
| | 03:26 | connecting to the database, creating the statement and
executing the query and getting back data from the database.
| | 03:33 | So, if you're working with Java 7, you can
significantly reduce the amount of code that's required
| | 03:38 | to connect to your database
and execute SQL statements.
| | 03:42 | If you're working with Java 6 and previous
versions, you'll still need to use the older
| | 03:46 | syntax, declaring the objects first and
setting them to null and then instantiating them
| | 03:51 | in a try block and closing
them in the finally block.
| | 03:54 | And if you're working in Android, as of
the date of this recording, Android still
| | 03:58 | follows the rules for Java 6, and so you won't
be able to use this try-with-resources syntax
| | 04:03 | there either, but in Java 7 things have been
improved significantly, and I encourage you
| | 04:08 | to try it in your own code.
| | 04:13 |
| | Collapse this transcript |
|
|
4. Reading DataLooping through result sets| 00:00 | When data is returned from the database server,
it's encapsulated within the ResultSet object.
| | 00:06 | The ResultSet has a cursor and in-memory
pointer that goes from row to row.
| | 00:11 | When the ResultSet is first returned, the
starting cursor position is before the first row of
| | 00:17 | data, that is, it's not
pointing to any data at first.
| | 00:20 | You have to explicitly move the
cursor to the data in order to read it.
| | 00:24 | The cursor can either be before the first row of
data, on a row of data, or after the last row of data.
| | 00:30 | All ResultSets give you the ability to move
the cursor forward, so the cursor starts before
| | 00:35 | the first row, and the first thing you
need to do is move it to the first row.
| | 00:39 | If you're working with a scrollable ResultSet,
you can explicitly move to a particular row,
| | 00:44 | but if you're working with what's called a
forward only ResultSet, that is a result set
| | 00:48 | where the cursor can only move forward, then
you move the cursor one row at a time by calling
| | 00:53 | a method called Next.
The Next method returns a Boolean value.
| | 00:57 | When you call it, if it succeeds in moving
the cursor to a row that has data, it returns
| | 01:01 | true, and if it moves toward the row that
doesn't have any data that would be after
| | 01:05 | the last row, then it returns false.
| | 01:08 | And so, when working with a forward only cursor,
the most common approach is to use a while loop.
| | 01:14 | Within the conditional block for the
while loop, you call the Next method.
| | 01:17 | And if it returns true, you know you're on a
row that has data, and you process the data.
| | 01:22 | And if it returns false, then you know you have
gone passed the available data, and you can
| | 01:27 | exit the while loop and
continue executing your program.
| | 01:30 | Within the while loop, while the cursor is
on a row with data, you call various methods
| | 01:35 | to retrieve the data.
| | 01:36 | If a particular column has string values,
you can call a method called getString.
| | 01:41 | You can pass in either the name of the column as a
string if you know it or the position of the
| | 01:46 | column as an integer and the
position start numbering at one.
| | 01:50 | So let's take a look at some code.
I'm working in a project called LoopingRows.
| | 01:54 | In this project, Main method, I'm executing a
query to retrieve all data from a table named Tours.
| | 02:00 | Let's take a look at the
structure of the Tours table.
| | 02:03 | The Tours table has a number of columns.
| | 02:04 | There is a primary key called tourId, a foreign
key called packageId, a number of string columns,
| | 02:11 | including tourName, blurb,
description, and so on.
| | 02:14 | A double-column called price, an
integer column called length, and many others.
| | 02:19 | I'm going to write some code that assumes
that we're getting back a record set based
| | 02:24 | on the structure of this table, and it's
going to loop through the rows that are returned
| | 02:28 | and output some content for each row.
I will go to Eclipse.
| | 02:32 | Now I'm not going to put all of this output
code in the Main method. Instead, I'm going
| | 02:36 | to create a Utility class that I can
call from anywhere in my application.
| | 02:40 | I'll go to my DBPackage, and
I'm going to create a sub package.
| | 02:44 | I'll right-click and choose New >
Package, and I'll name my new package Tables.
| | 02:49 | This new package eventually would contain
one class for each table in my database.
| | 02:55 | So I have created a new package, and now
I'll create a new class for that table.
| | 02:59 | I'll right-click and choose New > Class, and
I'll name my class the same as the table, Tours.
| | 03:05 | This is going to be Utility class.
| | 03:07 | It won't be the start up class of my application, so
I won't include a Main method, and I'll click Finish.
| | 03:13 | Now I'm going to create static methods to
process data. Within the class, I'll create
| | 03:18 | a new public static void method, so I'll put in
those keywords, and I'll call my method displayData.
| | 03:25 | I'll set this method up to receive a
single argument, data typed as a JDBC ResultSet.
| | 03:30 | I'll type in ResultSet, press Ctrl+Space,
and choose ResultSet from java.sql, I'll name
| | 03:36 | this argument rs, and
I'll create the code block.
| | 03:39 | Now, for the purpose of this exercise, I'm going to
assume that the cursor is starting before the first row.
| | 03:45 | I could add some additional code to make sure
of that, but for the way I'm going to use this
| | 03:49 | code in this application, I'm
going to know where the cursor is.
| | 03:52 | So I'm going to start with a while loop.
| | 03:54 | I'll type in while, and I'll
choose a while loop with a condition.
| | 03:58 | For the condition, I'll make it call to rs.next.
| | 04:02 | So now as long as there's
available data, I'll just keep on looping.
| | 04:06 | I'm going to collect data from a number of columns
and append it altogether to create a string for display.
| | 04:12 | For this purpose I'll use a string
buffer, I'll name this object simply buffer.
| | 04:16 | And I'll instantiate it
with the class's constructor.
| | 04:21 | I'll expand my editor to full screen
so I can see everything I'm typing.
| | 04:25 | Now I'm going to get the current row
number and append that as the tour number.
| | 04:29 | I'll use buffer.append, and I'll first add
in a label of the Tour space then I'll
| | 04:35 | append to that the value of rs.getRow.
Now this is not a static value.
| | 04:41 | The number will be whatever is first in
this record set, and so if you re-sort the data,
| | 04:46 | in some way these values
won't stick with the data.
| | 04:50 | If you prefer, you could instead call
getInt and pass in either the name or the column
| | 04:55 | index of the column
containing the primary key value.
| | 04:58 | So for example, I could
instead pass in getInt("tourId").
| | 05:03 | So, after I have appended the primary key value,
I'll then append a colon and a space.
| | 05:08 | Now before we add more content,
let's see how we're doing.
| | 05:11 | I'll add some system output, and I'll
output the value of buffer.toString.
| | 05:15 | Notice that there are some warning icons
when you call the next method or any of the
| | 05:19 | methods that get data from the record
set you might be throwing an SQLException.
| | 05:24 | So I'm going to mark this method with the throws declaration,
and indicate that it might throw that exception.
| | 05:31 | I'll save my changes, and
I'll go back to the main method.
| | 05:34 | I'll place the cursor within the try block,
and within the try block, I'll call this new
| | 05:38 | static method that I have just created.
| | 05:40 | I'll type in the name of my new class, and I
press Ctrl+Space to make sure I have imported
| | 05:44 | the class, and then I'll call the
display data method and pass in the ResultSet.
| | 05:49 | I'll save my changes and run the code, and I see
that I'm correctly getting back 26 rows of data.
| | 05:55 | So now I'm ready to fill in
the rest of my display method.
| | 05:58 | I'll go back to my Utility class,
and I'll add some more code.
| | 06:02 | After I have gotten the primary key, the
next step will be to output the tour name.
| | 06:06 | So I'll place the cursor after the call to
the append method, and I'll append again.
| | 06:10 | And this time I'll output the value of rs.getString,
and I'll pass in the name of the column I'm
| | 06:15 | interested in, tourName.
| | 06:17 | Now the column names are not case-sensitive,
and that's true for almost every database
| | 06:22 | you might work with.
It's certainly true with the MySQL and HSQLDB.
| | 06:27 | So even though HSQLDB marks these column names
as all uppercase and MySQL maintains the column
| | 06:33 | names however you typed them in, you can type
them however you like here in your Java code.
| | 06:38 | Next, I'll get the price of the tour.
| | 06:40 | I'll create a new double variable that I'll
name Price, and I'll get its value from rs,
| | 06:46 | and this time I'll call the method getDouble,
and I'll pass in the name of the column price.
| | 06:51 | Before I output it, I'd like to format it,
so I'll use an instance of the NumberFormat
| | 06:56 | class, I'll type in the name of the class,
and import it from java.txt, and I'll name
| | 07:01 | the object nf for NumberFormat, and I'll get
its reference by calling the static method,
| | 07:08 | NumberFormat.getCurrencyInstance.
| | 07:09 | Notice that there are many methods that
I could call from the NumberFormat class.
| | 07:14 | This one's going to format
the price as a dollar value.
| | 07:17 | Then I'll format the price, I'll create a
string called formattedPrice, and I'll get
| | 07:21 | its value from calling the format method of
the NumberFormat object with nf.format, and
| | 07:27 | I'll pass in the price.
| | 07:29 | And finally, I'll append that result to the
buffer, using buffer.append, and I'll pass
| | 07:34 | in a space and in an opening parenthesis, and then
the formattedPrice, and then a closing parenthesis.
| | 07:40 | I already have my code to
output the buffer's value.
| | 07:43 | So I'll save my changes, I'll go back to
the main method, and I'll run the code again,
| | 07:48 | and there is the result.
| | 07:49 | I'm looping through the data that's returned
from the database, and I'm outputting information
| | 07:54 | from the rows one at a time.
| | 07:56 | When I finished with the last row, the next
time I call the next method, it returns false
| | 08:01 | because there is no more data,
and I exit the loop, and I'm done.
| | 08:05 | So that's the most classic way of
working with data returned from the database.
| | 08:09 | The cursor starts before the first row, you
call the Next method and each time it returns
| | 08:14 | true you're on the next row of data.
| | 08:16 | And you use the various get methods of the ResultSet
to get a value from a column and process it in some way.
| | 08:23 |
| | Collapse this transcript |
| Moving the cursor in scrollable result sets| 00:00 | By default, result sets are forward only,
that is, the cursor starts before the first
| | 00:05 | row of data and can only move forward once.
| | 00:08 | But a scrollable ResultSet gives you the ability
to move back and forth in the result set as needed.
| | 00:14 | You can make the ResultSet scrollable with
most database management systems and different
| | 00:18 | database management
systems have different rules.
| | 00:21 | For example, MySQL using the Connector/J
returns a result set that scrollable by default.
| | 00:27 | But HSQLDB can do a scrollable
ResultSet, but you have to say you want it.
| | 00:33 | You select the ability to scroll the data
set when you create the Statement object.
| | 00:37 | In this code I'm creating the Statement with
the CreateStatement method, and I'm passing
| | 00:42 | in a type of TYPE_SCROLL_INSENSITIVE.
| | 00:45 | The ResultSet type can be scrollable or
forward only, it can also be READ_ONLY or updatable,
| | 00:51 | and it is possible to have a scrollable ResultSet that also
updatable if you combine these properties in the right way.
| | 00:57 | Once you have gotten a scrollable ResultSet
into memory you can move the cursor that is
| | 01:01 | the pointer to the current row using these
methods, beforeFirst(), and first(), move
| | 01:07 | to the position before the first row, and
the first row itself, last(), and afterLast(),
| | 01:12 | do the same thing, but for the end of the
table and the absolute method receives an
| | 01:16 | integer argument and
moves to that particular row.
| | 01:19 | As I previously mentioned, your number
ResultSet rows starting with 1, not with 0.
| | 01:24 | So if your ResultSet has say 50 rows, they
are numbered from 1 to 50, not from 0 to 49.
| | 01:30 | There are also methods that return boolean values
that you can use to check the current cursor position.
| | 01:36 | isBeforeFirst(), isFirst(), isLast(), and
isAfterLast(), and the ResultSet object has
| | 01:42 | other available methods to manage inserting new data
and moving the cursor to particular rows after insertions.
| | 01:49 | Take a look at the documentation
for more details about that.
| | 01:52 | But let's go to the code. I'm
working in a project called scrollable.
| | 01:55 | In this version of my application, I'm executing a
query that's retrieving data from the state's table.
| | 02:01 | Instead of using an asterisk for the columns,
I'm explicitly naming the columns I want retrieved,
| | 02:07 | which is typically a better practice
than using the asterisk wildcard.
| | 02:10 | I'll get back to result set with those two
columns, and I have created a new class called
| | 02:15 | states that in the db table's
package, which has a displayData method,
| | 02:20 | the displayData method is looping through
the ResultSet, outputting first the stateId,
| | 02:25 | the abbreviation, and then the stateName.
| | 02:28 | I'll run the code in its existing
state, and I get back the list of States.
| | 02:32 | Now I'm going to demonstrate moving the
cursor from row to row, notice that when I created
| | 02:37 | my statement, I set the type
as TYPE_SCROLL_INSENSITIVE.
| | 02:41 | So I should get back a scrollable result set,
regardless of whether I'm working with HSQLDB or MySQL.
| | 02:48 | I'll place the cursor after the call to the
displayData method, and first I'll move the
| | 02:53 | cursor to the last row of the
ResultSet, I'll call rs.Last,
| | 02:57 | then I'll do some System.output, and I'll
output (Number of rows), and I'll append to
| | 03:02 | that the current row number
using the ResultSet getRow method.
| | 03:06 | I'll save and run that code, and at the end
of the display, I see that the number of rows
| | 03:11 | is 50, and that's correct.
| | 03:12 | Now I'll add some code to
move back to the first row,
| | 03:16 | rs.First, and then once again I'll use some
System.output, and I'll output the string,
| | 03:22 | The first state is, and I'll append to that the
value of the state name column using rs.getString,
| | 03:29 | and I'll pass in the name
of the column stateName.
| | 03:32 | I'll save and run that code, and I see
that the first state is Alaska, this table has
| | 03:37 | the states in alphabetical order, now we'll add some code
to move the cursor to the last row, rs.Last, and I'll make
| | 03:44 | a copy of this output, and I'll paste it here,
and I'll change from the first state to the
| | 03:49 | last state, and I'll run that code, and I see
that the last state alphabetically is Wyoming.
| | 03:54 | Finally, I'll move the cursor to a specific
row using rs.absolute, and I'll pass the value
| | 04:00 | of 10, and once again I'll pasted my
output code, and I'll change from first to 10th,
| | 04:06 | and I'll run that code.
| | 04:07 | And I'll see that I can successfully
move the cursor as much as I need to.
| | 04:11 | In this code, I'm scrolling first forward
through all the data, then I'm moving to the end to
| | 04:16 | get the number of rows, back to the beginning,
back to the end again, and then to a specific
| | 04:21 | row by its integer value.
| | 04:23 | So that's a look at how you
can use scrollable result sets.
| | 04:26 | Now let's take a look at what happens if you
don't specify that you want a scrollable ResultSet.
| | 04:31 | I'm going to make a copy of this line of
code that creates the Statement, and I am going
| | 04:35 | to comment those new lines out, those are my
backup, because I'm going to make some changes
| | 04:39 | to the original code.
| | 04:40 | Now I'll go to my CreateStatement method call, and
I'll get rid of the options that I was passing in.
| | 04:46 | Now, notice I'm working with HSQLDB, and I'll
run the code, and this time I'm able to look
| | 04:52 | forward through the data, but then I get an
exception SQLFeaturedNonSupportedException,
| | 04:58 | and that's because with HSQLDB the
ResultSet by default isn't scrollable.
| | 05:03 | It's a forward-only ResultSet.
| | 05:05 | Now I'll change my databaseType from HSQLDB
to MySQL, and I'll run the code again, and
| | 05:12 | this time it works, and that's because with
MySQL, you get a scrollable ResultSet automatically.
| | 05:18 | To fix this and make sure that this code
will work fine with either database, I'll always
| | 05:23 | explicitly say that I
want a scrollable ResultSet.
| | 05:27 | So I'll go back to the code again, I'll
delete these two lines, and I'll uncomment these,
| | 05:32 | I'll save and run the code, and I see that
it works fine with MySQL, and I'll switch
| | 05:36 | back to HSQLDB, and I'll save and run
again, and I'll see it works there as well.
| | 05:41 | So for maximum portability, be explicit about
the type of the ResultSet that you want, and
| | 05:47 | you set those options when
you create the Statement object.
| | 05:52 |
| | Collapse this transcript |
| Limiting the number of fetched rows| 00:00 | If you're working with a database where the
tables have a lot of data, there will be many
| | 00:04 | times when you need to retrieve only a
certain amount of data from the table.
| | 00:08 | You can limit the number of
rows in a couple of different ways.
| | 00:11 | One approach is to use a method of the
statement interface called setMaxRows.
| | 00:16 | There are some complications in trying to
use this, especially with Java 7, where you're
| | 00:20 | using to try-with-resources
block to instantiate your objects.
| | 00:24 | I'm working in a project called LimitingRows,
and I'm starting off with the ResultSet that
| | 00:30 | contains all of the data from the Tours table.
| | 00:32 | I retrieve the data, and then use this method of the
Tours class which I have created in a previous video.
| | 00:39 | The displayData method receives the ResultSet
object as an argument, loops through its data,
| | 00:44 | and displays its data in the console.
| | 00:46 | I'll run the code and show that I'm
retrieving all of the data from the Tours table.
| | 00:51 | I'll also switch from MySQL
where I started to HSQLDB,
| | 00:55 | and then I'll run the code again
and show that it works there as well.
| | 00:58 | Now, I'm going to limit the
number of rows that I'm retrieving.
| | 01:02 | If you want to do this using pure JDBC and
not SQL, you do it with a setMaxRows method.
| | 01:07 | This is a method of the Statement object, and
you might be tempted to try this, stmt.setMaxRows,
| | 01:14 | and I'll pass in a value of five.
| | 01:16 | Meaning I only want to work with five rows, but when I
save that change, I'll see all kinds of errors appear.
| | 01:21 | The problem is that you can't modify an
object within a try-with-resources block.
| | 01:27 | This is a Java 7-specific issue.
| | 01:29 | If you're working with Java 6 or prior
versions, you would have declared your Statement object
| | 01:34 | outside the try block, then instantiated
within it, and then it would have worked fine.
| | 01:39 | But if you're working with Java 7, and you want to take
advantage of try-with-resources, this is the complication.
| | 01:45 | You can only setMaxRows within the try
block, not in the try-with-resources section.
| | 01:50 | So I'll move that code down a few rows.
| | 01:52 | I did that on Mac by holding the Option key
down and pressing the Down Arrow, and if you're
| | 01:57 | on Windows, you can do the
same thing with Alt and the Down Arrow.
| | 02:01 | Now I'll save my changes again, but now I'm
calling executeQuery before I call the setMaxRows method.
| | 02:07 | And when I run the code, I
still get back all of the data.
| | 02:11 | So I'll make another adjustment.
| | 02:13 | I'll take the code that's creating the ResultSet,
and move that down so it's below setMaxRows.
| | 02:19 | But now I also have to close the ResultSet,
so I'll add a finally block, and I'll call
| | 02:25 | rs.close, and to make that possible, I'll need
to declare the ResultSet outside the try block
| | 02:32 | so that it's visible in the finally block.
| | 02:34 | I'll move the cursor above the try, I'll
declare the object, and then I'll remove the data
| | 02:41 | type from the line of code
that's instantiating the object.
| | 02:44 | I'll test my code, and now it runs correctly.
| | 02:47 | I'll switch back to my other database,
MySQL, and test it there as well.
| | 02:51 | And I'll see that it works there as well.
| | 02:53 | So if you want to use the
setMaxRows method, you can.
| | 02:57 | But using it in combination with Java 7's
try-with-resources syntax is complicated.
| | 03:03 | There is one other issue though with setMaxRows.
| | 03:05 | Let's say you're dealing with a database table
that has 500 rows, and you say you only want 5.
| | 03:11 | When you execute the SQL statement, you'll
actually be retrieving all 500 rows from the
| | 03:17 | database into your Java client, and then when
you say setMaxRows with the statement, you're
| | 03:22 | saying discard the unused rows.
| | 03:25 | You're not saving any memory or any network
bandwidth if you're dealing with the database server.
| | 03:30 | So I'm going to get rid of all
the code that I just generated.
| | 03:34 | So instead of using setMaxRows, I'm going to use pure
SQL to limit the number of rows that I want to retrieve.
| | 03:41 | I'm going to put all my code back the way it was
and just comment out the stuff that I'm not using.
| | 03:46 | I'll comment out my declaration outside
the try block, I'll take my two lines of code
| | 03:50 | that are executing the query, and I'll
move them back to within try-with-resources.
| | 03:55 | I'll add the data type back to the
executeQuery method, and I'll comment out setMaxRows.
| | 04:00 | Now here's how you can
limit your data using Pure SQL.
| | 04:03 | I will use something called the LIMIT clause.
| | 04:07 | The LIMIT clause can be placed in a couple
of different places in your select statement,
| | 04:11 | depending on what database you're using.
| | 04:13 | But if you want to use the LIMIT clause in
a way that's broadly compatible across many
| | 04:17 | database platforms, place
it after the FROM clause.
| | 04:21 | I'll place the cursor inside the SQL string
before the closing quote, and I'll add a space
| | 04:27 | then I'll append to that
another bit of code LIMIT 5.
| | 04:31 | You can use the LIMIT clause
with either one or two numbers.
| | 04:34 | If you only put a single number in, you're
saying this is how many of rows I want, and
| | 04:39 | I want the first set of rows that
are available from the database.
| | 04:43 | So here I'll get the first five rows.
| | 04:46 | I'll clean up my warning at the end because I'm now
declaring the ResultSet within the try-with-resources.
| | 04:51 | I don't need this finally
code anymore so I'll delete it.
| | 04:55 | And now I'll run the code, and I
once again get back five rows of data.
| | 04:59 | Now let's say that instead of the first five
rows you wanted five rows from an arbitrary
| | 05:04 | segment of the ResultSet.
| | 05:06 | You can do this, too, by adding
another numeric value to the LIMIT clause.
| | 05:11 | You separate the two numbers with a comma.
I'll start by using 0, 5.
| | 05:16 | That means starting with row zero, the
first row of the ResultSet give me five rows.
| | 05:22 | I'll save the change and run the code, and
it looks exactly the same as it did before
| | 05:26 | when I was using setMaxRows, but now I'm
only retrieving those rows from the database.
| | 05:32 | And particularly when working with a network
database server this is clearly a better approach
| | 05:37 | because I'm not chewing up network bandwidth,
and I'm not using memory in my client that
| | 05:42 | I just don't need to use.
| | 05:43 | I'll once again switch databases, and I'll test it in
HSQLDB, and I'll see that it works exactly the same there.
| | 05:50 | One last note about the LIMIT clause,
the first value is the starting row.
| | 05:55 | You might remember I said that with
ResultSet columns you start numbering at one, so if
| | 06:00 | you want to refer to the first column of the
ResultSet, it's column one and not column zero.
| | 06:05 | But in SQL, when you're using the Select statement
with the LIMIT clause, you always start numbering
| | 06:11 | at zero for the number of rows, so you start
numbering at one for columns in a Java ResultSet
| | 06:17 | and zero for rows in SQL. And just keep those
two things straight, and you'll be in great shape.
| | 06:23 | Now I'll test this a little bit further.
| | 06:25 | I'll change the numeric value representing the
first row I want to retrieve from zero to five,
| | 06:31 | and now I get back rows six through ten.
| | 06:34 | And if you want to develop a paging interface,
that is a client that pages through data
| | 06:39 | one set of rows at a time, this
is clearly the best way to do it.
| | 06:43 | By putting your request directly into your
SQL statement, you limit the amount of memory
| | 06:48 | you're using in your client, you limit the
amount of network bandwidth you're using,
| | 06:52 | and in general you limit the amount of
data that you're retrieving from the database.
| | 06:57 |
| | Collapse this transcript |
| Filtering data with prepared statements| 00:00 | So far in this video series, I have shown you how
to work with static SQL statements, statements
| | 00:05 | that are the same when programmed,
and when executed at runtime.
| | 00:09 | But in many applications, you'll need to use
parameterized SQL statements, statements where
| | 00:15 | there are placeholders that you fill
in with variable values at runtime.
| | 00:18 | In JDBC, you do this with a
class called PreparedStatement.
| | 00:23 | The PreparedStatement class lets you set up an
SQL statement as a string with these placeholders
| | 00:28 | and then fill them in with variable values.
| | 00:30 | I'll demonstrate this in the
project Prepared Statements.
| | 00:33 | In this version of my console application, I
have a static string representing the SQL statement.
| | 00:39 | Right now, I'm retrieving all
of the data from the Tours table.
| | 00:42 | And then, when I display the data using this
version of the displayData method, I'm first
| | 00:48 | moving the ResultSet cursor to the end of
the ResultSet, then getting the row to find
| | 00:52 | out how many rows I got back.
| | 00:54 | And then, if I got back zero, I
say there were no tours found.
| | 00:57 | And if I got back more than zero, I output the
number and loop through and display the actual data.
| | 01:03 | I'll test this version of the
application before I make any changes.
| | 01:07 | And I see that I'm displaying
all of the data from the database.
| | 01:10 | Now let's change the
requirements of our application.
| | 01:12 | Let's say that the user is allowed to enter a
numeric value representing the largest amount
| | 01:17 | of money that they want to spend on a tour,
and we want to retrieve only those tours where
| | 01:22 | the price is less than or equal to
the value that the user provides.
| | 01:26 | The first step, I'll use a class named
inputHelper that I have added to this project.
| | 01:31 | In this version, in addition to the getInput method which
returns a string, I have a method called getDoubleInput.
| | 01:37 | It calls the getInput method and then
parses it and turns it into a double value.
| | 01:43 | And if it fails because the end user
enters a value that can't be parsed as a double,
| | 01:47 | it throws an instance of
number format exception.
| | 01:50 | So going back to my main class, I'll place
the cursor inside the main method right at
| | 01:55 | the top, and I'll declare a
new variable called maxPrice.
| | 01:59 | Then I'll set up a try catch block.
| | 02:01 | In the try block, I'll set the value of maxPrice
using the inputHelper's static method getDoubleInput,
| | 02:08 | and I'll display a prompt
of enter a maximum price.
| | 02:11 | Within the catch section, if an error is
thrown, I'll use a little bit of Error output, and
| | 02:17 | I'll output a static string of Error:
invalid number, and then I'll just clean up this
| | 02:22 | try catch block so that instead of looking
for the Exception object, I'll look for an
| | 02:26 | instance of NumberFormatException.
| | 02:29 | So now I have a value that I
can plug into my SQL statement.
| | 02:33 | I need to prepare the SQL
statement to accept this variable value.
| | 02:37 | I'll go up to the string where I'm setting
the SQL statement, and I'll add a WHERE clause,
| | 02:42 | and I'll use this syntax, WHERE price <= ?
and then I'll put in a question mark.
| | 02:49 | Each time you add in a question mark,
that's a placeholder for a variable value.
| | 02:53 | In order to process this SQL
statement, I need to add another class.
| | 02:58 | Instead of statement, I'm going to use
something called a PreparedStatement.
| | 03:03 | The PreparedStatement is the JDBC class that knows
how to process SQL statements with variable parameters.
| | 03:10 | I'll place the cursor down within this try
catch block, and I'll add Prepared to the
| | 03:14 | beginning of the class name.
| | 03:16 | Then to make sure that I have imported PreparedStatement,
I'll move my cursor to the end of the name
| | 03:21 | and press Ctrl+Space, and I'll choose the
version of PreparedStatement that's a member
| | 03:26 | of the java.sql package.
| | 03:28 | That adds an import
statement at the top of the code.
| | 03:31 | I'll open up my imports, and
show that that was added correctly.
| | 03:34 | Then I'll collapse them again.
| | 03:36 | In order to instantiate a PreparedStatement
object, instead of calling create statement,
| | 03:41 | you call a method of the
connection object, called prepareStatement.
| | 03:45 | So, I'm going to change that code here.
I'll call the prepareStatement method,
| | 03:49 | and then in order to use this properly,
before I pass in the type and the read-only setting,
| | 03:55 | I'll pass in the SQL
string as the first argument.
| | 03:59 | Then just as with a static statement, I'm
setting the type so I can scroll, and I'm
| | 04:04 | setting it to read-only.
So now I have my PreparedStatement object.
| | 04:08 | Before I execute the query, I have to
fill in the placeholders or parameters.
| | 04:13 | To do that, you call one of the
many methods of the Statement object.
| | 04:17 | For example, I'm filling in a double value,
a numeric value that could have fractions.
| | 04:22 | So, I'm going to set the value this way, stmt.set,
and notice how many different Set methods there are.
| | 04:30 | Each of these methods takes an integer
value and then the value you want to set.
| | 04:35 | The parameters are indexed
starting at one, not zero.
| | 04:38 | I only have one parameter in
MySQL statement, the price.
| | 04:42 | So I'll choose the right method for my data
type which is Double. I'll call setDouble.
| | 04:47 | I'll pass in a value of 1, meaning I'm
setting the first parameter, and then I'll pass in
| | 04:53 | the maxPrice value as the
value that I want to set.
| | 04:57 | Notice that I'm getting a warning.
| | 04:58 | I'll move the cursor over to the warning indicator,
and it tells me that The local variable maxPrice
| | 05:03 | may not have been initialized.
| | 05:05 | To fix that, I'll place the
cursor within my catch block.
| | 05:08 | And after I output the message that the
user entered an invalid string that couldn't be
| | 05:12 | parsed with a number, I'll return, and that
will fix the flow of this code, so that now
| | 05:18 | by the time I get to the code that's setting the
double value, I'll know that the maxPrice is valid.
| | 05:24 | Finally, I have one more change to make.
| | 05:26 | When you use a PreparedStatement, you have already
passed in the SQL string when you prepared the statement.
| | 05:31 | So, you don't pass it in
again when you execute it.
| | 05:35 | So I'm going to remove SQL as an
argument of the executeQuery method.
| | 05:40 | When I call executeQuery now, I'm working
with the statement that already has the SQL,
| | 05:45 | and that has already set its parameters.
I'll save and run the code.
| | 05:49 | When prompted, I'll click into the
console, and I'll enter a value of 500.
| | 05:54 | That means I want to see tours
that cost less than or equal to $500.
| | 05:59 | I get back 11 tours. I'll run the code again.
This time, I'll pass in a value of $300.
| | 06:06 | And I get fewer tours than I did
the last time. I'll run it again.
| | 06:10 | This time I'll pass in $100. And I get back just one tour.
And I'll run it one last time.
| | 06:16 | And this time, I'll look for a really
cheap tour, one that only costs $50.
| | 06:21 | And I get back the message, no tours were found.
| | 06:23 | You can set up your PreparedStatements with
as many placeholders as you like and as many
| | 06:28 | data types as you need to.
| | 06:30 | Remember that there are many versions of
the set method, one for each data type,
| | 06:34 | so if you're working with an integer value
in the database, use the setInt method, if
| | 06:38 | you're working with a string, use
setString, and so on and so forth.
| | 06:43 | The PreparedStatement interface is available for
pretty much every database that has a JDBC driver.
| | 06:49 | It lets you use parameterized SQL statements
and greatly simplifies the process of creating
| | 06:54 | applications that work
with a database dynamically.
| | 06:59 |
| | Collapse this transcript |
| Calling stored procedures| 00:00 | In previous videos of this series I have
described how to work with static SQL statements using
| | 00:05 | the Statement interface and with
parameterized statements using PreparedStatement.
| | 00:10 | There is a third interface that's available
in JDBC called CallableStatement which can
| | 00:16 | be used with stored procedures and
functions that are defined at the database level.
| | 00:20 | Let's begin by talking about
the nature of a stored procedure.
| | 00:24 | In this project, StoredProcs, I have a main
class that's using PreparedStatement and a
| | 00:29 | parameterized SQL statement as a String,
and I'm filling in the parameter at runtime.
| | 00:34 | I am going to take this parameterized SQL
and move it to the database level as a stored
| | 00:40 | procedure and then show you how to call that
stored procedure from your JDBC application.
| | 00:45 | I'll start by minimizing Eclipse and my browser,
and I'll get to this file MySQL StoredProcs.txt.
| | 00:52 | The syntax for defining stored procedures
can vary widely from one database to another,
| | 00:58 | so I'm going to focus on just one database
for this exercise, MySQL, and I have provided
| | 01:03 | all the code you need for MySQL in this file,
which is available in the free exercise files
| | 01:09 | that accompany the course. You can find it
in the database folder as MySQL StoredProcs.txt.
| | 01:15 | I have the code here for a number of
different stored procedures. I'm going to use this one
| | 01:20 | called GetToursByPrice. Just as in the previous
exercise, this SQL has a parameterized value,
| | 01:27 | what MySQL calls an in parameter,
a value that's passed in at runtime.
| | 01:31 | I am going to select all the code starting with the
DROP command and going all the way through DELIMITER.
| | 01:37 | Now, if you're curious about what those
Delimiter commands are doing, the default end of line
| | 01:42 | delimiter for MySQL is a Semicolon,
but SQL uses that Semicolon as well.
| | 01:47 | So in this code, I temporarily set the
DELIMITER for MySQL to a double slash, and then
| | 01:54 | the semicolon will be taken as the
DELIMITER only for the SQL statement.
| | 01:58 | The double slash is used here to say
I'm finished creating the procedure, and then I
| | 02:02 | reset MySQL DELIMITER back to a semicolon.
| | 02:06 | So I'm going to select all of this code
starting with the DROP command and going all the way
| | 02:10 | through the call to the DELIMITER
command, and I'll copy that to the clipboard.
| | 02:14 | Next, I'll go to my browser where I have already
opened phpMyAdmin, and I'll go to my explorecalifornia
| | 02:21 | database and then click on SQL, and this is
where I can execute arbitrary SQL statements,
| | 02:27 | I'll paste in that code.
| | 02:28 | I'll scroll up to make sure I have got
all of it starting with the DROP command.
| | 02:32 | This makes sure that if I already have a stored
procedure of this name that I'll drop it first
| | 02:36 | before I re-create it and then I'll click Go.
| | 02:39 | I confirmed that want to drop any procedure that
might already exist with this name and click OK.
| | 02:45 | And I get back messages indicating that the
code was executed but no data was returned,
| | 02:50 | and that's what I expect.
| | 02:52 | Now I'm going to test the procedure for MySQL,
I'll delete all that SQL, and in order to call
| | 02:57 | the stored procedure directly in phpMyAdmin,
I'll start with the word call, then the name
| | 03:02 | of my stored procedure, then a parameter
that matches the required data type, I will pass
| | 03:07 | in 300, and I close the statement with a
semicolon, I'll click Go, and I get back
| | 03:12 | a total of seven rows
just what would I expected.
| | 03:16 | I'll go back to MySQL
statement, and I'll try it again.
| | 03:18 | I will once again call GetToursByPrice, and
this time I'll pass in 100, and I'll click Go, and
| | 03:26 | once again as expected, I get back a single row.
| | 03:29 | So now I know that my stored procedure
is working as expected, and I can go and try
| | 03:34 | to call it from Java.
| | 03:35 | I'll close the Browser, and I'll close
the Text Editor, and I'll return to Eclipse.
| | 03:40 | Because MySQL logic is now part of my stored
procedure I don't need it in the application itself.
| | 03:46 | So I'm going to delete this SQL code and
replace it with the same call I used in phpMyAdmin
| | 03:52 | but I'm going to wrap the call
inside a pair of braces.
| | 03:55 | So I'll start with an opening brace, then
once again call, then the name of the stored
| | 04:00 | procedure GetToursByPrice, and then a place
holder, which is exactly the same as in a
| | 04:05 | standard SQL statement a question mark,
I don't need all that with my codes, so I'll
| | 04:10 | close it up a little bit, and
now that SQL code is ready to use.
| | 04:14 | Now I'll move down to where I have
declared my prepared statement object.
| | 04:18 | When you're recalling a stored procedure or
function from the database instead of using
| | 04:22 | the prepared statement
interface, use the CallableStatement.
| | 04:26 | I'll start with word the Callable, I'll press
Ctrl+Space, and I'll choose the CallableStatement
| | 04:31 | interface from java.sql, and instead of calling the method
to PreparedStatement, I'll call the method PrepareCall.
| | 04:39 | Just as with PrepareStatement when you call
the PrepareCall method, you can pass in the
| | 04:44 | SQL String and the type and whether you want
your result set to be read-only or updatable.
| | 04:50 | Everything else is exactly the same just like
with PreparedStatement the CallableStatement
| | 04:54 | has set methods for various data types.
| | 04:57 | I'm still passing in a double value so
I'll call setDouble, and I'll pass in maxPrice
| | 05:02 | as the first value.
And I am ready to test my code.
| | 05:05 | I run the application, I'll type in a value of
300, and I get back my expected number of rows.
| | 05:11 | I'll run the application again, I'll type
in a value of 100, and I'll get back one row,
| | 05:17 | and I'll run it one more time, and this
time I'll type in 50 and I get back no rows.
| | 05:21 | So all I have done here is to move MySQL logic
from my client application into the database itself.
| | 05:29 | You can do this with any database
management system that supports stored procedures and
| | 05:34 | functions, take a look at the documentation
for your database management system to learn
| | 05:39 | how to define the stored procedures and functions,
but when you're in JDBC the syntax for calling
| | 05:44 | the stored procedures and
functions is always pretty much the same.
| | 05:49 |
| | Collapse this transcript |
| Handling multiple values from stored procedures| 00:00 | Many Database Management Systems let you
create stored procedures that return more than one
| | 00:05 | value, for example, in MySQL.
| | 00:08 | You can create a stored procedure that returns both a
result set and one or more of what are called Out Parameters.
| | 00:15 | Here's the syntax in MySQL, I have provided
this code in the file MySQLStoredProcs.txt,
| | 00:21 | which is part of the free exercise
files that accompany this course.
| | 00:25 | As with any stored procedure in MySQL, you
start by setting the DELIMITER to a double
| | 00:29 | slash or some other String
that won't be in conflict with SQL.
| | 00:33 | When you create the procedure, you define arguments.
Here I am declaring an IN parameter, the same
| | 00:39 | parameters as I used in a previous exercise,
but I'm also defining an OUT parameter, a
| | 00:44 | value that can be passed
back from the stored procedure.
| | 00:47 | In this stored procedure, I'm executing two
separate select statements, the first one
| | 00:52 | selects the count of
rows from the tours table.
| | 00:56 | It selects the count, but then
instead of naming that return value as a part
| | 01:01 | of the result set as you
might do in conventional SQL,
| | 01:04 | I'm taking the value of count, and I am
passing it to the OUT parameter by its name, total.
| | 01:10 | Then I'm executing a second SELECT statement.
This one will return a conventional result
| | 01:15 | set, I am returning all columns, but I
could just as easily put it on this column names
| | 01:20 | that I want to return.
| | 01:22 | It's essential that the select statement
that returning the result set be executed last.
| | 01:27 | That's how you'll get that
data out of the StoredProcedure.
| | 01:30 | The SELECT statements that are simply setting
the value of OUT parameters such as my first
| | 01:35 | select statement can be
placed anywhere in the chain.
| | 01:38 | So I'm going to take this code and
import it into my MySQL database.
| | 01:42 | I'll select everything starting with a DROP
command and ending with the DELIMITETR command,
| | 01:47 | and I'll copy it to the clipboard.
| | 01:49 | Then I'll go to phpMyAdmin, I'll go to my database, and
to the SQL panel I'll paste in that code and click Go.
| | 01:56 | I'll confirm and I'll check and make
sure that everything was executed correctly.
| | 02:00 | Now I'm ready to run this
code for my Java application.
| | 02:04 | I'll switch back to Eclipse where I am working
with a project named StoredProcOut, this version
| | 02:09 | of my project is using the
CallableStatement interface already.
| | 02:13 | And right now it's calling the stored procedure
GetToursByPrize, which receives a single argument.
| | 02:19 | In this version of my application I'll change
MySQL String from the original stored procedure
| | 02:24 | GetToursByPrice to the new stored
procedure GetToursWithCountByPrice.
| | 02:30 | And I'll match the arguments in the original
stored procedure in MySQL. I had two arguments,
| | 02:35 | so I'll set two arguments here.
| | 02:37 | I'm still calling a stored procedure, so I'll
use the CallableStatement just like I did before.
| | 02:42 | But now I have a couple more bits of
code to add to deal with that OUT parameter.
| | 02:47 | I'll place the cursor after the code that's setting the
IN parameter, which is the double value of the Max price,
| | 02:53 | then I'll add another line here, and I'll
call another method called registerOutParameter.
| | 02:58 | Just as with the set methods, you can identify
your parameter either by its ordinal position,
| | 03:04 | that is 1, 2 and so on in
the SQL String, or by its name.
| | 03:09 | I'm going to use a version of registerOutParameter
where I pass in the name of the parameter
| | 03:13 | as a String, but again I
could also pass the value of 2.
| | 03:17 | Next, I indicate the data type of the value.
I'm expecting that you set the data Type
| | 03:22 | by using a constant that's the part of a class
called Types, and I'm going to set this to Types.INTEGER.
| | 03:28 | So I have set the IN value with setDouble, and I have
registered the OUT value with registerOutParameter.
| | 03:35 | Now I execute the query. After I execute the query,
the results will be returned automatically.
| | 03:41 | It always returns the result set resulting from
the last SQL statement in the stored procedure.
| | 03:46 | But to get the OUT parameter, I
have to do a little bit more work.
| | 03:50 | I'll declare an integer variable that I'll
name nRows, and I'll get its value by calling
| | 03:54 | a method of this statement object not of the
result set. It will look like this stmt.getint
| | 04:01 | you'll find that there are other methods
called getdouble, getstring, and so on.
| | 04:05 | And as before, I can reference the parameter
either by its position in the argument list
| | 04:10 | or by its name. I'll choose the version
where I reference by Name, and I'll type in the
| | 04:14 | name of the parameter, and now I have
that value returned as the variable nRows.
| | 04:19 | The code in my displayData method in the Tours
class already has code to figure out how many
| | 04:25 | rows there are, but I'm not going to need
that anymore. That value is already being
| | 04:29 | returned by the stored procedure.
| | 04:31 | So I'll go into the displayData method and
comment out those two lines of code, and I'll
| | 04:36 | re-factor this so that nRows
is passed in as an argument.
| | 04:41 | I'll save those changes, I'll go back to my
main class, and I'll change the way that I'm
| | 04:46 | calling displayData, and I'll pass in nRows.
| | 04:49 | So I have moved some of the actual work from
the client application back to the database.
| | 04:54 | In fact, I no longer even need a scrollable
result set because now I'm not moving the
| | 05:00 | cursor forward and back, I'm only moving it
forward looping through the returned rows.
| | 05:05 | I'll test my application, I enter a value of
300, and there is a result the Number of tours
| | 05:11 | is correct, and there is the data.
| | 05:13 | And I'll run it once again, and this time
I'll pass in a value that gets back no rows,
| | 05:17 | and there's the result.
| | 05:18 | From the user's point of view it's all working
exactly like it did before, but now I have moved
| | 05:23 | as much of the logic as I
can back to the database.
| | 05:26 | This lightens the load on the client,
reduces the amount of work you have to do and the
| | 05:31 | amount of memory you have to use, and
eliminates the need for a scrollable result sets, which
| | 05:36 | do take a little bit more memory and
resources than forward only result sets.
| | 05:40 | You can do it either way moving the logic into your
client or defining it and keeping it on the database.
| | 05:46 | As a JDBC developer, the more you know about
what your database management system can do,
| | 05:51 | the more choices you'll have.
| | 05:54 |
| | Collapse this transcript |
| Using generic getter methods in Java SE 7| 00:00 | So far in this chapter, I have been
describing how to retrieve data from ResultSet using
| | 00:05 | specifically-typed Getter methods.
| | 00:07 | For example, in this version of my project,
GenericGetters, in my Tours class, I have
| | 00:12 | calls to the methods getInt, getString, and
getDouble, and I'm selecting the method to
| | 00:18 | match the data type of the column.
| | 00:20 | So, the tour ID is an integer, so I call getInt, tour
name is a string column, so I'll call getString, and so on.
| | 00:27 | In Java 7, there is a new approach, a
Generic Getter method called getObject that you can
| | 00:33 | use on every column of ResultSet, and you explicitly data
type the return value using a different syntax.
| | 00:40 | Now again, this new Generic Getter
method is only available in Java 7.
| | 00:45 | So if you're using an older version of Java, you should
stick with these explicitly data typed method calls.
| | 00:50 | But if you are working with Java 7 and
therefore JDBC 4.1, this new approach is just another
| | 00:57 | option, another tool in your toolkit.
| | 00:59 | I'll start this exercise by
re-factoring this code a bit.
| | 01:02 | I'm going to take the expression that's
retrieving the tourId, and I'll extract it to a local
| | 01:07 | variable of the method.
| | 01:09 | I'll select the expression, then I'll right-click,
scroll down and choose Refactor, and then Extract Local Variable.
| | 01:16 | I'll name my new variable the
same as the column name, tourId.
| | 01:20 | So that creates a local variable here, and
then I'm using that value on the next line
| | 01:25 | to build my string buffer.
| | 01:27 | I'll do the same sort of
re-factoring for the tourName.
| | 01:30 | This is all just to make the
code a little bit more readable.
| | 01:33 | I'll select the expression, right-click,
choose Refactor > Extract Local Variable, and again,
| | 01:38 | I'll name the new variable to
match the column name, tour name.
| | 01:42 | I'll group these two variable declarations
together, and then I'll take another that
| | 01:46 | already exists, the declaration of the
price variable, and I'll move that up.
| | 01:50 | So now I have one section of code where I'm
getting all the values I need from the current row.
| | 01:56 | I'm going to reformat the code a little bit,
and make it even easier to read, and now I'm
| | 02:01 | ready to use the new Generic Getter method.
| | 02:03 | I'm going to keep a copy of this code around so I
can compare the two styles of coding to each other.
| | 02:09 | I'll select and copy and then paste in,
and then I'll comment out the second version,
| | 02:14 | and I'll make the changes to the first version.
Here's how you use the Generic Getter.
| | 02:18 | I'll start with the integer.
| | 02:20 | Instead of calling getInt and passing in either
the name of the column or the ordinal position
| | 02:25 | of the column as an integer, you call getObject.
| | 02:27 | So, I'll get rid of that, and
instead of getInt, I'll call getObject.
| | 02:32 | You'll see that there are a few versions of
this method, but I'm using this version, the
| | 02:36 | one that accepts a column label as
the first argument and then a class.
| | 02:41 | The class will determine the data type
that will be returned from the method.
| | 02:45 | You'll see that we're using the diamond
operator, and the T in the middle means type.
| | 02:50 | What is the data type that we're looking for?
| | 02:53 | So I'll call getObject, and just as before,
I'll pass in the name of the column that I'm
| | 02:57 | retrieving from, and then, I'll pass in a
class and its class field, and it looks like
| | 03:04 | this for an integer, Integer.class.
| | 03:07 | That means that I want to retrieve the tourId
column, and I want to data type it as a class.
| | 03:13 | Now, you might think that I would have to
explicitly cast the value as I'm passing it back.
| | 03:18 | For example, adding
casting syntax at the beginning.
| | 03:21 | But because of the way the getObject method
is written, when I say that I'm passing in
| | 03:25 | an integer class here, that's the class that's
going to be returned, and so it will automatically
| | 03:30 | be returned as my primitive integer,
and I don't need this casting syntax.
| | 03:35 | Now, let's do the same thing with the string.
| | 03:37 | I'll get rid of the call to the getString
method, and instead I'll call getObject,
| | 03:42 | once again, I'll call the version that
accepts the name of the column and the type.
| | 03:45 | I'll pass in the name of the column as
tour name, and the type will be string.class.
| | 03:50 | Finally, I'll do the same thing with the price.
I'm retrieving a double value.
| | 03:56 | So I'll call the getObject method, and I'll
pass in the name of the column, price, and
| | 04:00 | the data type which I'm going
to set initially as Double.class.
| | 04:03 | So, take a look at these
two versions of the code.
| | 04:07 | In the first one, you're calling the same
method over and over again, getObject, and
| | 04:11 | you're setting the data type
using that second argument.
| | 04:14 | In the second version, which is the older version,
you're setting the data type with the actual method names.
| | 04:19 | I'll run this application, and I'll see that
it works exactly the same as it has in previous
| | 04:24 | versions of the project.
| | 04:26 | If you see a pop-up dialog asking you whether you want to
run an application or an applet, choose Java application.
| | 04:32 | But here's a little problem
with this version of the code.
| | 04:35 | I'll go back to my Main class and show that
I have started off working against MySQL, and
| | 04:40 | this code works with MySQL.
| | 04:42 | But now I'm going to switch to HSQLDB
or HyperSQL, and I'll run the code again.
| | 04:48 | And this time I get an error.
| | 04:50 | Depending on the state of your installation
of Eclipse, you might not see this dialog.
| | 04:54 | Instead, you might go
right to the debug perspective.
| | 04:58 | I'll see that I'm getting a ClassCastException
which was not caught by MySQL Exception catch clause.
| | 05:04 | I need to add a little bit more try
catch code to see what's happening.
| | 05:08 | So, I'll terminate the project and go back to my
code, and I'm going to add in another catch section.
| | 05:13 | And this time, instead of SQLException, I'll
catch the global exception object, Exception.
| | 05:19 | And here, I'll just do a little bit of system
error output, and I'll pass in the Error object.
| | 05:24 | I'll run the code again, and this time I can see the
explicit error, BigDecimal cannot be cast to double.
| | 05:31 | So what's going on here?
| | 05:32 | When you call the getObject method with a
double column or a float, the specification
| | 05:37 | for JDBC says that a BigDecimal will
be returned, not a double or a float.
| | 05:43 | Now, if you have watched my Java Essential
Training course, you might remember that I described
| | 05:48 | why the Big Decimal class exists.
| | 05:50 | The double and float objects can't be guaranteed
to be absolutely precise because of the nature
| | 05:56 | of how floating-point arithmetic happens on
the computer, you'll frequently have additional
| | 06:01 | decimal values that you don't want.
| | 06:03 | Whereas the BigDecimal object can
always return a very specific value.
| | 06:08 | With some database drivers, the BigDecimal can be
converted to the Double, and that was the case with MySQL.
| | 06:14 | But with other database drivers, such as
HyperSQLs, you have to always use the BigDecimal.
| | 06:20 | So, for portability between databases, if
you're working with columns that are doubles,
| | 06:25 | floats, currency, or anything else that might have fractional
values, I recommend always returning a BigDecimal.
| | 06:32 | So I'm going to change the data type of
the value that I'm returning into, the price,
| | 06:37 | and I'll change it to BigDecimal.
| | 06:39 | I'll make sure to choose it so that I
get an import statement for the class.
| | 06:42 | Then I'll go to my getObject call, and I'll
change that from Double.class to BigDecimal.class.
| | 06:49 | I'll save and run the code again.
| | 06:51 | And with HyperSQL, it now
works, where it broke before.
| | 06:54 | I'll go back to my Main class and change
back to MySQL, and I'll run the code again.
| | 07:00 | And I'll see that it works there as well.
| | 07:02 | If you use the BigDecimal to contain your
doubles and floats, you'll pretty much guarantee
| | 07:07 | that things work fine
across different databases.
| | 07:10 | But if you try to stuff things into a double
or a float value using this new Generic Getter
| | 07:14 | method, it will work on some
databases but not on others.
| | 07:18 | So that's a look at how you can
use the new Generic Getter method.
| | 07:21 | In the rest of the code throughout this course,
I'm going to continue to use the older style
| | 07:26 | because it is compatible
with older versions of Java.
| | 07:29 | But if you're working with Java 7, you might
want to upgrade to this new version of the code.
| | 07:34 |
| | Collapse this transcript |
|
|
5. Managing DataManaging data entities with JavaBean classes| 00:00 | My focus so far has been on weeding and
displaying existing data from our backend databases.
| | 00:05 | Now, I'm going to move to how to manage your
data, how to insert new rows, and how to update
| | 00:11 | and delete existing rows.
| | 00:12 | I'm working in a version of my project called
JavaBeans, and I have made some changes to the
| | 00:17 | classes and how they're implemented.
| | 00:19 | In this version of the project, in the
Tables package there are now three classes named
| | 00:24 | AdminManager, StatesManager, and ToursManager.
| | 00:27 | These classes are the manager classes for each
of the named tables, Admin, States, and Tours.
| | 00:33 | Each of them now implements a
method called Display All Rows.
| | 00:36 | I'll show you how this is
implemented in each of the classes.
| | 00:39 | The Display All Rows method now contains all
of the code needed to retrieve the data and
| | 00:44 | display it in the console.
| | 00:45 | There is an SQL variable that's local to
the method, then within the try-with-resources
| | 00:50 | block, a Connection, a Statement,
and ResultSet are being created.
| | 00:54 | Then there's some looping code to
output the data from that table.
| | 00:57 | You'll see the same sort of implementation is
available in the AdminManager, the StatesManager,
| | 01:03 | and the ToursManager.
| | 01:04 | In order to manage data, you
need a good way to encapsulate rows.
| | 01:09 | Each row is a data entity, and in Java, we represent
data entities with a type of class called a JavaBean.
| | 01:16 | The syntax of a JavaBean
is fairly straightforward.
| | 01:19 | A JavaBean class has a name, it has private
properties to represent its values, and then
| | 01:25 | public Setter and Getter methods that allow the rest
of the application to access and modify those values.
| | 01:31 | I'll show you first how to create a JavaBean class
and then how to use it in a couple of different ways.
| | 01:36 | I'll go back to the Package Explorer,
and I'm going to create a new package.
| | 01:41 | I'll right-click on the DB
package and choose New > Package,
| | 01:45 | and my new package will
have an extension of .beans.
| | 01:49 | In a complete application, I would have
one JavaBean class for each database table.
| | 01:54 | For now, I'm just going to create
one JavaBean class for the Admin table.
| | 01:58 | I'll right-click on the new package and
choose New > Class, and I'll name my class exactly
| | 02:04 | the same as the table, but if the table
uses a plural value such as tours or packages,
| | 02:10 | the bean will have a name that's singular,
because it's going to represent a single row
| | 02:14 | of the database table, not all of them.
| | 02:17 | For the Admin table, I'll
just use the word Admin.
| | 02:20 | I'll make sure I'm not creating a main method.
| | 02:22 | This class is designed to be instantiated
to represent a single row of data and not
| | 02:27 | to launch a full application.
| | 02:29 | Once the class has been generated, I'll create
one private field for each column in the table.
| | 02:34 | In my Admin table, the first column is an
integer column, and it is named adminId.
| | 02:39 | Next, I'll create a string,
and I'll name it username.
| | 02:44 | And finally, I'll create another
string, and I'll name it password.
| | 02:47 | I'm setting the names of my fields exactly
the same as my database column names, but
| | 02:52 | you don't necessarily have to do that.
| | 02:54 | Especially in an environment where your
database tables might have very long names, you can
| | 02:59 | use shorter field names to make your
Java programming a little bit easier.
| | 03:03 | Now, I'm going to generate public
methods called Setters and Getters.
| | 03:07 | I'll go to the menu and choose Source,
then Generate Getters and Setters.
| | 03:11 | I'll click Select All to indicate that I
want methods for all three private fields.
| | 03:16 | I'll set the insertion point for this new code
to after the Last member, whatever it might be.
| | 03:21 | I'll leave the Access modifier
set to public, and I'll click OK.
| | 03:25 | And that creates all the
methods I need for my JavaBean.
| | 03:28 | I'll clean up my code a little bit, adding a
little bit of extra white space and deleting
| | 03:32 | it where it's not needed.
And this JavaBean class is now ready to use.
| | 03:36 | One more word about JavaBeans, if you look
at the specification for JavaBeans, you'll
| | 03:40 | find that a true JavaBean is
supposed to be serializable.
| | 03:44 | You do this by adding the
implements keyword and then Serializable.
| | 03:48 | Make sure to include the import
statement for the Serializable interface.
| | 03:52 | The serializable interface doesn't
have any methods you have to implement.
| | 03:56 | It's a marker interface.
| | 03:57 | It's just a way of saying to the rest of your
environment that this class can be serialized.
| | 04:02 | That is saved to a local persistent
environment such as a database or to a local disk.
| | 04:07 | When you mark this as
Serializable, you'll see a warning.
| | 04:10 | I'll move my cursor over the warning indicator,
and I'll see that it's asking me to create
| | 04:14 | something called a serialVersionUID field.
| | 04:18 | I'll go ahead and create this by clicking on the
warning indicator, and adding a generated serial version ID.
| | 04:24 | This is a long value, and it's used to
uniquely identify the class when it's been serialized.
| | 04:29 | Now, the truth is you don't need this code
for the kind of application we're building here,
| | 04:34 | And even though the JavaBean spec says that
all JavaBeans should do this, if you don't
| | 04:39 | need the functionality for your particular
application, you don't need to add it in.
| | 04:42 | So I'm going to delete that, and I'm also
going to take away the implements marker and
| | 04:47 | remove the import statement.
| | 04:49 | So, now I have a JavaBean that can represent
a single row of my Admin table, and I'll be
| | 04:54 | able to use this JavaBean class to represent
data that I want to update and delete in my application.
| | 04:59 |
| | Collapse this transcript |
| Retrieving a single row as a JavaBean object| 00:00 | In a previous video I described how to create a JavaBean
class to represent the structure of the database table.
| | 00:06 | The Admin class has one private field for
each column of the Admin table and the datatypes
| | 00:12 | of the class matched the
datatypes of the columns.
| | 00:15 | There are private fields and
public getters and setters.
| | 00:18 | The names match the JavaBean convention
where the getters start with the word get and the
| | 00:23 | setters with the words set and then the rest
of the names of the methods match the fields.
| | 00:27 | The fields can match the database column
names, and they do here, but they don't have to.
| | 00:32 | Now I'm going to show how to use this JavaBean class to
represent a single row of data retrieved from a table.
| | 00:38 | I am working in a version of my project called
RetrieveBean, and in this version of the project
| | 00:44 | my AdminManager class has
a new method called getRow.
| | 00:48 | The getRow method receives a
single argument datatype as an integer.
| | 00:52 | I have named it to match the database column
name Admin ID, and that's the primary key
| | 00:56 | column of the database table.
| | 00:59 | Then there is an SQL variable, a string that
has the statement I'm going to execute with
| | 01:04 | a question mark for the placeholder.
That value will be provided by the end user.
| | 01:08 | Then I'm creating a result set, a
connection, and a prepared statement.
| | 01:12 | I'm taking the value passed in, in the
argument and setting it for the placeholder and then
| | 01:17 | executing the query.
But for the moment I'm just returning null.
| | 01:21 | Now, I'm going to replace the return
statement with the logic that takes the data received
| | 01:26 | from the query and wraps it
in an instance of my JavaBean.
| | 01:30 | I will move the cursor after the call to execute query,
and I'll add logic to test whether I got back data.
| | 01:36 | If the query returns one row of data, I
will be able to move the cursor from its initial
| | 01:41 | position before the first row to the first
row by calling the result sets next method.
| | 01:47 | So I will use an if statement, if rs.next.
If this returns true, it means I got data back.
| | 01:54 | But if it returns false, that means that the
user provided a value that doesn't match anything
| | 01:59 | in the database table.
| | 02:00 | I'll move my return statement to within the
else clause, and I'll also output an error,
| | 02:05 | No rows were found, now I will add logic to wrap
up the data that was found inside the if statement.
| | 02:12 | So if the next method returns
true, then I have data to work with.
| | 02:16 | I will create an instance of my Admin class.
| | 02:19 | Remember that it's in the beans
package, and you do need an import for it.
| | 02:23 | I will name this new object bean, and I will instantiate
it with the classes No arguments constructor method.
| | 02:29 | Now I will set its fields
using its setter methods.
| | 02:34 | First, I will set the primary key bean.setAdminId, and I
will just use the value that was passed in as the argument.
| | 02:41 | Next, I will set the two string fields, username and
password, and I'll use the values from the result set.
| | 02:46 | I will call bean.set username, and I will
pass in rs.getString, and I will identify
| | 02:53 | the column I want by its name, userName.
Then I'll set the password field.
| | 03:02 | Once again, I'm using the setString method.
| | 03:06 | For a database table that has more columns,
simply call as many versions of the set method
| | 03:10 | as you need to and use the appropriate methods of
the result set to get the datatypes that you need.
| | 03:16 | Get string for strings, get double and
get int for integers and doubles, and so on.
| | 03:21 | Now my bean object is complete.
| | 03:23 | I have instantiated it, and I have set its
values, and I will return it to the calling context.
| | 03:29 | And that's the complete method.
I will review all of the code.
| | 03:33 | The primary key value is passed in as an
argument, the SQL statement has a placeholder for that
| | 03:38 | primary key value, I am using a prepared
statement object so I can pass the value
| | 03:42 | in using one of the setter methods,
and I am executing the query.
| | 03:46 | If I get data back, I wrap that data in
an instance of my JavaBean class and return
| | 03:50 | it, if I don't get data back, I return null.
| | 03:53 | Now, let's go to the main
class and call this method.
| | 03:56 | I will place the cursor after the call to
the AdminManger's displayAllRows method, and
| | 04:01 | I will add code that lets the user
indicate what row they want to work with.
| | 04:05 | I will declare a new variable called AdminId,
and I will get its value from a new method
| | 04:09 | of the InputHelper class
called getInteger input.
| | 04:13 | I will set a prompt of Select a row.
| | 04:17 | Now I will use that value to get a
JavaBean object representing the matching data.
| | 04:22 | I will declare a new JavaBean object, and
I will use the Admin data type, and I will
| | 04:27 | set the name of the object as bean, and I
will instantiate it using AdminManager.getRow,
| | 04:33 | and I will pass in the AdminId.
| | 04:35 | Now I will test whether I
got data back from the query.
| | 04:37 | I will use an if else clause.
| | 04:39 | I will set my condition to if,
bean has a value of null.
| | 04:44 | So if the bean is null, that
means I didn't get any data back.
| | 04:48 | In my output manager's method, I am
already outputting an error message.
| | 04:52 | I am going to move that to the main class.
| | 04:54 | I will cut it from here and paste it
into my main class in my if clause.
| | 05:00 | But then in the else clause, if I did get
data back, I will display that data's information.
| | 05:05 | I will use a series of system outputs.
| | 05:08 | The first one will have a label of AdminId and a
call to the getAdminId method of the JavaBean object.
| | 05:14 | I will duplicate that line of code a couple
of times and then I will change the labels
| | 05:19 | and the methods that I'm calling.
| | 05:21 | For the second one, I will use UserName
as the label, and I will call getUserName.
| | 05:25 | And for the third one, I will set the
label to Password, and I'll call getPassword.
| | 05:30 | So now I am ready to test my application.
| | 05:32 | I'll make sure that I saved all my classes,
then I will go back to my main class and run
| | 05:36 | the application, I'll type in a value of 2,
which won't match row, and I'll get back the
| | 05:42 | error message, No rows were found.
| | 05:44 | But then I'll run the code again, and I'll pass in
the correct value of one, and I get back the data.
| | 05:50 | So now I have an object that I
can pass around the application.
| | 05:53 | I can get the data object from one method
and pass it into another and in the next set
| | 05:58 | of videos I'll show you how to use this object to
manage updates and deletes of existing data.
| | 06:03 |
| | Collapse this transcript |
| Inserting rows with prepared statements| 00:00 | Once you have created a JavaBean class to
represent the structure of a table, you can start adding
| | 00:05 | code to your application to
insert update and delete rows.
| | 00:09 | I'll start with inserting data.
| | 00:11 | In this version of the project InsertSQL, my
AdminManager class has a new method called Insert.
| | 00:17 | It accepts an instance of my JavaBean class as
an argument, and it already has an SQL command.
| | 00:22 | An SQL command to insert data looks like this.
| | 00:25 | It starts with the INSERT command,
then into, and the name of the table.
| | 00:29 | In this admin and then a common delimited list of the
column names you are inserting into inside parentheses.
| | 00:36 | You follow that with the keyword values and
then the values you are inserting also comma
| | 00:40 | delimited and also in parentheses.
| | 00:43 | For JDBC, I'm representing those values with
the question mark placeholder, and this
| | 00:48 | method already has the code to create the
connection object and a prepared statement.
| | 00:53 | I'm going to add the code to
populate the placeholders with values.
| | 00:56 | I will move the cursor into the code block
after the opening brace and make a little
| | 01:00 | bit of extra space, and I'll call this set
string method of the statement object twice
| | 01:06 | once for each placeholder.
| | 01:07 | I'll start with stmt.setString, and I will
pass in a parameter index of one for the first
| | 01:13 | parameter, and I will pass in the
bean objects getUserName method.
| | 01:18 | Then I will do the same thing for the password.
| | 01:20 | I will once again call setString, I will pass
in 2 this time, and now I will call the bean
| | 01:25 | objects get password method.
| | 01:27 | Once you have populated the SQL statement, you can
execute the update, and I'll call stmt.executeUpdate
| | 01:33 | to pass those changes to the database.
| | 01:35 | Now that's all you need to do if
you're providing the primary key value.
| | 01:39 | But if you're working with a database table
where the primary key column uses an integer
| | 01:44 | primary key that auto increments, that is where
the next available value is assigned automatically
| | 01:50 | by the database then JDBC gives you the ability to
get that new value right after you execute the update.
| | 01:56 | Here's how it works.
| | 01:58 | First you need to find out whether
the insert statement was successful.
| | 02:01 | The executeUpdate method always returns an
integer value, the integer is the number of
| | 02:06 | rows that were affected by the insert and for
this kind of insert statement if the execution
| | 02:11 | was successful it'll always be one.
| | 02:14 | So I'm going to place the cursor before the
call to execute update, and I will declare
| | 02:18 | a new variable called affected data typed
as an int, and I will populate its value from
| | 02:22 | the executeUpdate method.
| | 02:24 | Then after the call to the method, I will
test whether the execution was successful.
| | 02:28 | I will use an if else clause.
| | 02:31 | I will set the condition to
affected has a value of 1.
| | 02:35 | Now if the value is 1, that means
that a row is inserted into the database.
| | 02:40 | In order to get back that value, you're
going to need to declare a result set object, and
| | 02:45 | because of the structure of the try catch
block, you will want to declare the result
| | 02:50 | set object before the try block.
| | 02:52 | I will move the cursor to above the try,
I'll declare a result set object, and I'll name
| | 02:57 | it keys, and I will initially set it to null,
then I'll move the cursor back to the if block.
| | 03:02 | To find out what the generated primary key
value is, call a method called get generated keys.
| | 03:08 | It'll return that result set object.
| | 03:11 | So I will type in keys equals, and I will
get the results set by calling the statement
| | 03:15 | objects, getGeneratedKeys method.
| | 03:18 | It's a result set, and as with all result sets,
the cursor starts before the first row of data.
| | 03:24 | So then I will call keys.next to move
the cursor to the one and only row of data.
| | 03:29 | Now I am ready to get the value.
| | 03:31 | When you call getGeneratedKeys, you will always get
back a result set with a single column and a single row.
| | 03:37 | So to get the value out, I will declare a
variable named newKey, and I will call keyes.getint,
| | 03:43 | and I will pass in a value
of 1 for the first column.
| | 03:47 | You might be tempted to find out what
the name of column is and use that instead.
| | 03:51 | But the name of that column will
differ from one database to the next.
| | 03:55 | So for maximum portability, just
say you are getting it from column 1.
| | 03:59 | Then I will take that value, and I will pass
it to the bean object using bean.setAdminId,
| | 04:03 | and I will pass in newKey.
| | 04:07 | Now I am not going to return the bean object,
instead I am just setting this property of
| | 04:12 | the bean object which will also be
accessible from the calling context.
| | 04:16 | So I am going to place the value there, and then I'll
get it back later after I have called the insert method.
| | 04:22 | I'm not done with this method yet, though.
First, let's do with the else condition.
| | 04:26 | If I get into this bit of code, that means
that my insert statement was unsuccessful.
| | 04:31 | So I will do some system error output and
output a static string of No rows affected,
| | 04:37 | and then I will return false which according
to the logic of this method, the insert method,
| | 04:42 | means that this was not successful.
| | 04:44 | I also need to deal with the keys result set
as with all results sets. You need to explicitly
| | 04:49 | close it when you're done.
| | 04:51 | So I'll move the cursor down to within the
finally block, I will test the keys object
| | 04:55 | and make sure it's not null,
and if it isn't, I will close it.
| | 04:59 | Notice I'm putting all of this code on a
single line because I don't have multiple lines of
| | 05:03 | code, I'm not using braces, and
I am keeping it nice and simple.
| | 05:07 | This method is now complete, but before I
leave it I will point out one little bit of
| | 05:11 | code that I added previously.
| | 05:13 | When I call the prepared statement method, I'm explicitly
passing in these option statement.return generated keys.
| | 05:20 | For some database management systems, the
statement object will do this automatically,
| | 05:25 | but for others it won't.
| | 05:26 | As with all such options where database behaviors
will differ, I recommend being explicit in the options.
| | 05:32 | Here I'm saying no matter what database
management system I'm working on, if you're able to return
| | 05:37 | the generated keys, please do it.
So that's the entire insert method.
| | 05:41 | Now let's add code in the
main class to call the method.
| | 05:44 | I will go to Main.java and with the cursor
after the call to display all rows, I'll get
| | 05:49 | information from the user
and pass it to a bean object.
| | 05:53 | First, I'll create an
instance of the Admin bean object.
| | 05:56 | I will name it bean, and I will instantiate
it with the bean class's constructor method.
| | 06:01 | Now I will set the two properties
that I want to add my new database row.
| | 06:06 | First, I'll call the bean object setUserName
method, and I will directly pass in the result
| | 06:10 | of calling my InputHelpers, get input method.
| | 06:13 | I will type InputHelper and press Ctrl+Space
to add the import then get input, and I will
| | 06:19 | display a prompt of User name,
then I'll do the same thing again.
| | 06:22 | This time for the password, I will call the
setPassword method of the bean object, and
| | 06:27 | I'll pass in the inputHelpers getInput method
again, and this time I'll use a prompt of Password.
| | 06:33 | Now I'm ready to insert
the data into the database.
| | 06:36 | Remember that the insert method will return
a boolean value, so I will declare a variable
| | 06:41 | that I will name result, and I will call
AdminManager.insert, and I will pass in the bean object.
| | 06:47 | Then I will test the result.
| | 06:48 | If the result is true, I will do a little
bit of system output, and I will output the
| | 06:53 | string New row with primary key, and
then I will append to that bean.getAdminId.
| | 06:58 | Remember, I populated that value from the
inset method and then I will complete the
| | 07:03 | output with was inserted.
And now I am ready to test my application.
| | 07:07 | I will make sure I have saved all my
code, and I will run the application.
| | 07:11 | I will type in a new user of New Admin, and
I will type in a password of New Password.
| | 07:17 | And I get back the message that the New
row with primary key of 2 was inserted.
| | 07:21 | So to check to make sure that the data was
correctly inserted, I will go to my browser,
| | 07:26 | and I will open up phpMyAdmin.
| | 07:27 | I will go to my explorecalifornia database
to the admin table and browse, and I see my
| | 07:34 | new row was successfully inserted.
So that's how you insert data using JDBC.
| | 07:40 | You don't have to use JavaBean classes, but
as you can see here, to help keep your code
| | 07:44 | clean and easily maintainable, you encapsulate
the data that you want to add into the database
| | 07:50 | inside an instance of your JavaBean, you
pass the bean object from one method to the next,
| | 07:55 | and then if you need to restructure your database table,
you can restructure your JavaBean class as well.
| | 08:00 |
| | Collapse this transcript |
| Updating rows with prepared statements| 00:00 | So far in this chapter I have described how
to create the JavaBean class to represent
| | 00:04 | a single row of data and how the
insert new data into a database table.
| | 00:09 | Now I'll show how to update existing data.
| | 00:12 | Working in a version of my project called
UpdateSQL, I have added a new method to the
| | 00:17 | AdminManager class.
The method is named Update.
| | 00:20 | It receives a single argument, data typed as
the Admin JavaBean, and it has an SQL statement.
| | 00:26 | The SQL statement follows the
standard for all updates in SQL.
| | 00:30 | It starts with the keyword UPDATE and
then the table name admin and then SET,
| | 00:35 | then there's a series of
column name value pairs.
| | 00:38 | I'll be updating two of the values, the
userName and password, and then there is a WHERE clause
| | 00:44 | that filters the statement so that these
changes are only applied to a single row.
| | 00:48 | This method already has code to create
the connection and the prepared statement.
| | 00:52 | I'll add code now to pass the values from
the Bean argument into the prepared statement.
| | 00:58 | I'll place the cursor after the opening
brace and before the return statement and make a
| | 01:02 | little bit of extra space.
| | 01:04 | The SQL statement has three
parameters, two strings and one integer.
| | 01:08 | So I'll call the setString method
twice and the set int method once.
| | 01:12 | I'll start with the strings.
| | 01:13 | I'll call the statement object setString method,
I'll pass in a parameter index of 1, and I'll
| | 01:19 | get the value of the username from the
JavaBean object calling bean.getUserName.
| | 01:25 | Now I'll duplicate that line of code, and for
the second version I'll change the parameter
| | 01:29 | index to 2, and I'll change the method I'm calling
to get the value from get.UserName to getPassword.
| | 01:35 | I'll duplicate it one more time.
| | 01:37 | And this time I'm going to
call setInt instead of setString.
| | 01:41 | I'm going to be updating this parameter for the
adminID, and that's an integer column in the database.
| | 01:47 | I'll set the parameter index to 3, and I'll
call the method getAdminID to get the primary
| | 01:52 | key value from the JavaBean object.
So now my prepared statement is ready to go.
| | 01:57 | As I did in the previous video with the
Insert statement, when I call execute update, I'll
| | 02:02 | get back in the integer
indicating how many rows are affected.
| | 02:05 | With an update statement where you're filtering on a
primary key you should always get back a value of one.
| | 02:11 | So I'll create a variable called affected, and
I will call the statement object executeUpdate
| | 02:15 | method without passing any arguments.
Then I'll check the affected value.
| | 02:20 | If it has a value of one, I'll return true
indicating to the calling scope that the update
| | 02:25 | was successful, and I'll put in an else
clause and return false, because if I get back a
| | 02:30 | value other than one that
means something went wrong.
| | 02:33 | Now that I have handled all possible cases both
the execute update method working and either
| | 02:38 | updating or not updating a row, my final
return true is no longer needed, so I'll delete it.
| | 02:43 | And that's my completed update method.
| | 02:45 | So again, to review it, I'm
receiving an object a JavaBean object.
| | 02:49 | I have an SQL statement with placeholders
for the values I'm updating and the primary
| | 02:54 | key I'm filtering on and creating a
connection and a prepared statement, I'm setting each
| | 02:59 | of the values from the bean to the
statement and then executing the update,
| | 03:03 | and then evaluating whether it was
successful and returning true or false.
| | 03:08 | Now I'll go back to my main class, and
I'll add code to update an existing row.
| | 03:12 | In this version of my main class I'm allowing
the user to enter a primary key value in AdminID,
| | 03:18 | and I'm retrieving that single row from the
database using the existing method get row.
| | 03:23 | I'll place the cursor after this if clause.
| | 03:26 | If I get to this point, then I
know I'm working with a valid row.
| | 03:29 | I'll ask the user to enter a new password value.
| | 03:32 | I'll use the InputHelper's getInput
method, and I'll prompt Enter new password.
| | 03:37 | Notice I'm only doing this after I have
verified that they selected an existing row.
| | 03:42 | I don't do this before I get the row,
because I might not have valid data to work with.
| | 03:47 | Now I'll take the value that the user entered,
and I'll pass it into the bean object using
| | 03:51 | bean.setPassword, and I'll
pass in the new password value.
| | 03:56 | Then I'll execute the update and check the
success of the operation all at the same time
| | 04:00 | using if AdminManager.update,
and I'll pass in the bean object.
| | 04:05 | And if I get back the true value, I'll do a little
bit of system output, and I'll put the line Success.
| | 04:11 | Then I'll add an else clause and within
that section, I'll output an error of whoops.
| | 04:16 | And now I'm ready to test my code.
| | 04:18 | I'll run the application, and I'll see
that I have an existing row with a primary key
| | 04:23 | of 2 and a username of New Admin
and a password of New Password.
| | 04:27 | I'll enter 2 as the row I want to update.
| | 04:30 | I get the prompt to enter the new
password, and I'll enter just pwd.
| | 04:34 | And I'll press Enter, and I
get back the message Success.
| | 04:37 | I'll run the application again, and this
will show me the existing data now coming from
| | 04:41 | the database table, and I see that
new value was successfully updated.
| | 04:46 | I'll try it again, typing in the number of 2,
and I'll return it back to New Password,
| | 04:50 | and I could go over to phpMyAdmin and
check it there, but if you're seeing it updated
| | 04:55 | and displayed each time you run the
application here, then you'll have confidence that the
| | 04:59 | data is being successfully
updated in the backend database.
| | 05:04 |
| | Collapse this transcript |
| Deleting rows with prepared statements| 00:00 | After inserting and updating, the third
operation you need to know how to do is deleting.
| | 00:06 | In this version of the project DeleteSQL,
my main class is collecting an adminID an
| | 00:11 | integer from the user.
| | 00:13 | I'll run the code so far and show that it
starts by displaying the data and then I can
| | 00:17 | enter a value and then nothing happens.
| | 00:20 | I'm going to add code to
delete a row from the existing data.
| | 00:24 | I'll go to my AdminManager class
in this version of the project.
| | 00:27 | I have created the delete method.
| | 00:29 | The delete method only
receives an integer argument.
| | 00:33 | It doesn't need an entire JavaBean object,
because all it needs to know is the primary
| | 00:37 | key of the row you want to delete.
| | 00:40 | As with previous exercises I have seeded this
file with the SQL command I'll need, DELETE
| | 00:45 | FROM and in the name of the table, WHERE and
then the primary key column and a placeholder,
| | 00:51 | which for this table will be an integer.
| | 00:53 | I'll complete the code by
setting the placeholder value.
| | 00:56 | I'll place the cursor before the return statement,
and I'll call this statement objects setInt method.
| | 01:02 | I'll pass in the parameter index of 1.
This SQL statement only has one parameter.
| | 01:07 | And then I'll pass in the adminId as the
value I want to filter on, and that's all I need.
| | 01:12 | I'll execute the update, but as with the
update statement I'll look at the value returned
| | 01:17 | by the execute update method and integer to
find out whether the operation was successful.
| | 01:22 | I will declare the variable named affected,
and I'll get its value from the statement
| | 01:26 | object's executeUpdate method.
| | 01:28 | Now remember, the value that's returned
from this method represents the number of rows
| | 01:33 | that are affected by the SQL command.
| | 01:35 | So if it's 1, that means that a row was
successfully deleted, and if it's not 1 that means that
| | 01:41 | probably the row just didn't exist.
So I'll do a little bit of if else programming.
| | 01:46 | I'll set the condition to
affected has a value of 1.
| | 01:49 | And if that's true, I'll return true,
and if it isn't true I'll return false.
| | 01:54 | And I no longer need to return statement
at the end of the method, so I'll delete it.
| | 01:58 | So that's the entire delete method.
I'm receiving a primary key argument in integer.
| | 02:02 | I'm taking that value and passing into the
SQL statement through the prepared statement
| | 02:07 | object, and I'm executing the command.
| | 02:09 | And then I'm evaluating whether it was
successful and returning either true or false.
| | 02:14 | I'll save those changes and go to my main class.
| | 02:16 | I'm already getting the value of the
primary key of the row I want to delete.
| | 02:21 | So all I have to do is
call my new delete method.
| | 02:24 | I'll create an if else block,
and I'll call AdminManager.delete.
| | 02:28 | I'll pass in the adminId variable, the value
the user provided, and if that value is true,
| | 02:34 | I'll output a Success message.
| | 02:38 | And if it's false, I'll output
an error of nothing to delete.
| | 02:43 | And now I'm ready to test my application.
| | 02:46 | I'll run, I see the existing data has two rows,
the original row that I seeded the database
| | 02:51 | with and a row that I
inserted in a previous exercise.
| | 02:55 | I'll pass in the primary key of the row I want to
delete, the second row that I just inserted previously.
| | 03:00 | I'll press Enter or Return, and
I get back the message Success.
| | 03:04 | Now I'll run the code again right away, and
I'll see that that row is gone, but I'll try
| | 03:08 | to do it again just to make sure that
the logic of my application makes sense.
| | 03:13 | And now because the affected value returned
from execute update is zero, that is, no rows
| | 03:19 | were affected, I returned false from that
method, and in the main code I output the error
| | 03:24 | message Nothing to delete.
| | 03:25 | So the process of creating, updating, and deleting
data with SQL statements is all pretty much the same.
| | 03:32 | You can manipulate your database with whatever SQL
commands you know and that your database supports.
| | 03:38 | I have kept things really simple in these
demonstrations doing plain vanilla inserts, updates, and
| | 03:43 | deletes that would be accepted by all databases.
| | 03:46 | But again, you can use what ever SQL
Syntax your Database Management System supports.
| | 03:51 | That SQL is passed directly from your application through
the JDBC driver to the database where it's actually processed.
| | 03:59 | And as I have previously shown, you can choose
from either SQL statements embedded into code
| | 04:04 | like this or stored procedures that
are built directly into your database.
| | 04:09 |
| | Collapse this transcript |
| Managing data with updatable result sets| 00:00 | I have shown so far how to do inserts, updates, and deletes
using SQL statements and the prepared statement object.
| | 00:08 | There's an alternative in JDBC the updatable
result set, where you get a result set from the
| | 00:13 | database and market as updatable, then
change the data directly in the result set.
| | 00:18 | You can do inserts, updates,
and deletes with this approach.
| | 00:21 | Whether it's a good idea depends on your
circumstances and your database management system.
| | 00:26 | Not all database management system supports
updatable result sets, and if you're working
| | 00:31 | with a very large result set,
it can cause performance issues.
| | 00:35 | But in a small-scale application, or when you're
only dealing with one row at a time, an updatable
| | 00:40 | result set programming approach can be useful,
I'll demonstrate how to update a row of data
| | 00:45 | using an updatable result set.
I'm working in a project called UpdatableRS.
| | 00:50 | The state of this project is similar to the
update exercise that I did previously, there
| | 00:54 | is an update method with an SQL statement,
it has three placeholders, two strings for
| | 00:59 | the username and password, and one integer
for the admin ID, a prepared statement is
| | 01:04 | being used and executed to make the update.
| | 01:07 | I'm going to change this code to
use an updatable result set instead.
| | 01:12 | First, I'll change the SQL statement.
| | 01:14 | Instead of an update statement, I'm going to use
a select, it'll look like this SELECT * FROM
| | 01:19 | admin and then I'll filter on the primary
key that's been passed in as part of the bean object.
| | 01:27 | In my SQL statement I'll
create a placeholder for that value.
| | 01:31 | Next, I'll go to my prepareStatement method, this is
where you'll mark the result set as being updatable.
| | 01:36 | I'll make a little bit of extra space by
moving the SQL parameter down to the next line, then
| | 01:41 | I'll add two more arguments.
| | 01:43 | First, ResultSet.TYPE_SCROLL_INSENSITIVE
and then ResultSet.CONCUR_UPDATABLE.
| | 01:50 | This is called the concurrency setting of
the result set object, because I have set it
| | 01:55 | as updatable I can now make direct
changes to the result set object.
| | 01:59 | My SQL string now only has one placeholder, so I no
longer need these two strings, I'll comment them out.
| | 02:06 | And I'll change the parameter index for my
final setting to one, so now I have prepared
| | 02:11 | the statement, and I'm going to
retrieve the data from the database.
| | 02:15 | Instead of executeUpdate, I'll be using executeQuery, the
same method that I have used previously to retrieve data.
| | 02:21 | And instead of returning an integer, it's going to
return a result set. Because I'll be manipulating
| | 02:27 | this result set within my try block, I'll first
declare it outside the try block and then close
| | 02:32 | it in a finally block.
| | 02:33 | I'll move the cursor up to above the try
block and declared the result set, making sure to
| | 02:38 | import the version from java.sql, I'll
name it RS and set it initially to null.
| | 02:43 | Then I'll move down to below the try catch
block and add a finally block and within the
| | 02:48 | finally block I'll check to see if it's
still null, and if it isn't, I'll close it.
| | 02:53 | Then I'll go back to the call to statement. executeQuery, and instead
of getting an integer, I'll be getting back the result set.
| | 02:59 | I'll delete these remaining three lines of code,
they no longer apply, and now I'll add an if else clause.
| | 03:06 | In order to modify the data that I just retrieved, I
have to move the result set cursor onto the row of data.
| | 03:13 | So I'll add an if and an else block, and I'll
set the condition to rs.next, moving the cursor
| | 03:19 | to the only available row of data.
| | 03:22 | Now I'll update the
columns that I want to change.
| | 03:24 | Even though my application is only going to
update the password for the current row, this
| | 03:30 | code doesn't know it. This code should be
written so that it updates every column in
| | 03:34 | the database row other than the primary key.
| | 03:36 | So I'm going to call the update
string method a couple of times.
| | 03:40 | You'll see that there are update methods
for all the various data types that a database
| | 03:44 | supports, I'll call updateString, and as
with all of the other methods the sets and the
| | 03:49 | gets and similarly to the get methods that
are a part of result set, you can reference
| | 03:54 | a column either by its index or by its name.
| | 03:57 | I'll choose the version that looks
for the name, and I'll pass in username,
| | 04:01 | and then I'll set the value to bean.getUserName.
| | 04:04 | I'll duplicate that line, and for the second
version, I'll set the column name to password,
| | 04:09 | and I'll call the bean
objects getPassword method.
| | 04:12 | Once I have set those values, I have to commit
them by calling a method called updateRow.
| | 04:17 | Notice that there are a few different versions.
Because I have already made sure that the cursor
| | 04:22 | of the result set is on the right row, I
can use the version of this method that's the
| | 04:26 | simplest, the one with no arguments.
| | 04:28 | Then because I have gotten through all this
code without throwing an exception, I can assume
| | 04:32 | that the update was successful and return true.
| | 04:36 | And if the next method return false, that is if
there was no data to update, I'll return false.
| | 04:42 | So this is the final version of the
update method using an updatable result set.
| | 04:46 | You'll see that you aren't saving any code by
going with this approach, but some developers
| | 04:50 | prefer this to doing update statements.
It's really up to you, and whether it works well
| | 04:55 | within your database management system
is a matter of a lot of different factors.
| | 04:59 | If you're not sure whether this is a good
idea with your database management system,
| | 05:03 | chat with other developers who have used both the
updatable result set and classic SQL statements
| | 05:09 | with your database.
But let's complete the exercise.
| | 05:12 | I'll save the changes, and
I'll go back to my main class.
| | 05:15 | I'm not going to change
anything about the main class.
| | 05:18 | From the main classes point of view,
everything should work exactly the same.
| | 05:21 | I'm accepting a primary key value from the
user, I'm retrieving a row of data as a Java
| | 05:26 | bean object, I'm applying a new value to the
password, and then calling the update method
| | 05:32 | of the admin manager class.
| | 05:33 | I'll run the code, I see that I only have
one row in the database right now, so I'll
| | 05:38 | type in the primary column value of one,
I'll enter a new password of new pwd, and I'll
| | 05:44 | press Enter or Return, and I
get back the message Success.
| | 05:47 | And if I run the application again, I'll see that
the value is correctly updated in the backend database.
| | 05:53 | So it's your choice, updatable
result sets or classic SQL statements.
| | 05:58 | Either way you'll be using the prepared
statement object to put your code together, and it's
| | 06:03 | really up to you which style you prefer.
| | 06:08 |
| | Collapse this transcript |
| Using a persistent database connection| 00:00 | If you have been working through the exercises
in this video series, you might have noticed
| | 00:04 | that we're opening a lot
of database connections.
| | 00:07 | I'm working in a version of my project called ConnectionManager
that has the code for updating a database table.
| | 00:13 | And I'll go to the tables
package and open AdminManager.java.
| | 00:17 | The way this application is currently architected
every time I call my DBUtil class's getConnection
| | 00:23 | method, I'm opening a new connection, and then I am
closing that connection when that method is finished.
| | 00:29 | And if you count up all the times that we
are using the get connection method, you'll
| | 00:32 | see that we're opening and
closing connections many, many times.
| | 00:35 | In JDBC this is into great practice, and
that's because connections are expensive.
| | 00:40 | They take time, memory, and resources, and so
to the extent possible you should minimize
| | 00:46 | the opening and closing of
connections by reusing connections.
| | 00:50 | When you work in a Java Enterprise Edition
Server environment such as JBoss, the server
| | 00:54 | typically will provide connection pooling.
| | 00:57 | It's a multi-user environment, and so it's
vital that multiple users reuse those connections.
| | 01:03 | If you're building your own Java application,
though, either as a console application like
| | 01:07 | this or a desktop application, you
might need to write your own logic.
| | 01:12 | In this project, I have provided a bit
of code called the ConnectionManager.
| | 01:16 | The ConnectionManager class is a singleton,
a class that can only be instantiated once.
| | 01:22 | This is a common design pattern in Java, and
it's used whenever you want to make sure that
| | 01:26 | your entire application
is using the same object.
| | 01:30 | The ConnectionManager starts with a
private static instance of itself, and the name of
| | 01:35 | that field is simply instance.
It's initially set to null.
| | 01:39 | It then contains String fields for all the
credentials we'll need to connect to databases
| | 01:43 | and a DB type field which is not static.
| | 01:46 | So it can be changed after
this class is instantiated.
| | 01:49 | There is a private field for the connection object,
and there's only one, and it's initially set to null.
| | 01:55 | Now here is the code that manages
the singleton aspect of the class.
| | 01:59 | The goal of a singleton is to make sure
that the entire application can get a reference
| | 02:04 | to it, but it can only be
instantiated once from within the class itself.
| | 02:08 | And so we control that
with a private constructor.
| | 02:12 | When you mark the one and only constructor
method as private, that means that it can
| | 02:16 | only be constructed from within this class.
| | 02:19 | And then there is a static method called
getInstance which checks to see whether the one instance
| | 02:24 | is null, and if it isn't, it instantiates
it, and then either way, it returns it.
| | 02:31 | So to get a reference to the one and only
ConnectionManager object for the whole application,
| | 02:35 | other parts of the application will call the
static method ConnectionManager.getInstance.
| | 02:41 | Let's take look at the rest of the code.
| | 02:42 | There is a public method called setDBType
that lets the application decide what type
| | 02:48 | of database we're going to
use either MySQL or HSQLDB.
| | 02:52 | There is a private method called openConnection.
| | 02:56 | We'll only call this method if the connection
isn't already open, but it looks exactly the
| | 03:01 | same as it did in our DBUtill class.
| | 03:03 | It checks the database type and then uses the
appropriate logic to open either MySQL or HSQLDB.
| | 03:09 | There is public method named getConnection.
| | 03:13 | This method will be called from the one
and only instance of the ConnectionManager.
| | 03:17 | It checks to see whether the one and only
connection is null, and if it is it opens
| | 03:21 | it and then it returns the reference.
| | 03:23 | And if the code gets all the way down to this
line, it returns the already open reference.
| | 03:29 | Finally, there's a close method. Remember that you
should always explicitly close your connections,
| | 03:34 | and using a singleton
manager makes no difference.
| | 03:37 | So now the singleton manager is
responsible for closing the connection.
| | 03:40 | And in this case, it both closes it and sent
it to null so that we can explicitly manage
| | 03:45 | the opening and closing of the connection
from anywhere in the application, but only
| | 03:50 | by calling this method.
| | 03:51 | This code is fairly intensive, and if you
don't have access to the exercise files, I
| | 03:56 | don't want to make you type it out.
| | 03:57 | So I have provided this project in the
code section of the free exercise files.
| | 04:02 | Now let's see how we can use
the ConnectionManager class.
| | 04:06 | I'll go to my main class.
| | 04:07 | The first thing I'll do is set the
database type, either HSQLDB or MySQL.
| | 04:13 | I'll place the cursor after the comment.
| | 04:16 | Now I'll get a reference to the
ConnectionManager object and set the database type.
| | 04:20 | I'll say ConnectionManager.getInstance, and that
gives me the reference to the ConnectionManager,
| | 04:26 | and from there I'll set the
database type with DBType.MYSQL.
| | 04:31 | I'll also add code at the end of the method
to ensure that the connection has been closed,
| | 04:36 | and that'll look like this
ConnectionManager.getInstance().close.
| | 04:41 | So that's all I have to do from the main
class, I set the database type and make sure the
| | 04:45 | connection is closed.
Now let's go to the AdminManager class.
| | 04:49 | The AdminManager class is where we have all
these calls to the getConnection method, and
| | 04:54 | we're opening and closing
the connection over and over.
| | 04:57 | I'm going to change the model for this.
| | 04:59 | I'm going to create my connection as a
static field of the AdminManager class.
| | 05:04 | I'll place the cursor inside the class declaration,
but before any of the methods, and I'll declare
| | 05:09 | a private static Connection, and I'll
name it conn, as it's been named previously.
| | 05:14 | And I'll get its reference by calling
ConnectionManager.getInstance().getConnection.
| | 05:20 | So now the first time any of these static
methods are called, this static connection
| | 05:24 | object will get its reference automatically.
| | 05:27 | And I can go through all the rest of the code,
and I can comment out or delete all of the
| | 05:31 | other code that's getting the
referenced from the DBUtil class.
| | 05:35 | I'll delete it from the getRow method, I'll
keep on scrolling down, and I'll delete it
| | 05:40 | from the Insert method, and
also from the Update method.
| | 05:44 | I'll save my changes, and I'll go back to
my main class, and I'll run the code again.
| | 05:48 | And because I have system output scattered
throughout the application, I'm able to see
| | 05:53 | the order in which things are happening.
| | 05:55 | First, I get the message that the application is
starting then the message that the connection is opened.
| | 05:59 | Now, notice I still have input that I have
to provide, and if things are working,
| | 06:04 | the connection is still open.
| | 06:06 | So I went to the number one and a new
password, and I get back the message Success.
| | 06:11 | And then I see the message that the
connection is being closed, because I get to the code
| | 06:15 | in the main method that calls the
close method of the ConnectionManager.
| | 06:19 | So, now I'm using a single
connection to my database.
| | 06:23 | I'm saving memory, time, and resources.
| | 06:26 | Again, if you're working in a Java Enterprise
Edition Server environment, you'll have a connection
| | 06:31 | pooling architecture that the server provides.
| | 06:34 | But if you're working on a Console or a
desktop application, it's up to you to write this
| | 06:38 | code, and this is one
possible model that you can try out.
| | 06:43 |
| | Collapse this transcript |
| Committing and rolling back transactions| 00:00 | A database transaction is one or more actions
that are making changes to data in a database
| | 00:05 | server or local database file.
| | 00:08 | Whenever you make changes such as inserts,
updates, or deletes, you're creating transactions.
| | 00:13 | And in JDBC, these transactions
are what we call auto-committed.
| | 00:17 | That is, if you don't see anything else, the
changes that you request are made and committed
| | 00:22 | to the database immediately.
| | 00:23 | But most database management systems give you the
ability to explicitly commit and roll back transactions.
| | 00:29 | It depends on what database
you're using, and how you're using it.
| | 00:33 | For example, with MySQL, it depends on
what backend database engine you are using.
| | 00:38 | I'm going to go to phpMyAdmin to my Explore
California database, and I'll choose one of
| | 00:43 | my tables, the admin table, and
I'll go to the Operations tab.
| | 00:47 | Notice that the storage engine for my database
tables is innoDB, and I have specifically selected
| | 00:53 | that because innoDB supports transactions, row-level locking
foreign keys, and other advanced database functionality.
| | 01:01 | If I had chosen the default database type
MyISAM, I would not have gotten many of those
| | 01:06 | features, including transactions.
| | 01:08 | But innoDB does support transactions, and so
I'll be able to demonstrate how JDBC handles
| | 01:13 | transactions with this sample database.
| | 01:16 | I'll go back to Eclipse where I'm working
with a version of my project called Transactions.
| | 01:21 | This is a version of my update application
where I'm asking the user for a primary key
| | 01:26 | value, and then a new password, and I'm
updating the database at the server level.
| | 01:32 | I'll expand my editing window
and then run the application.
| | 01:35 | I'll type in a number of an
existing row and a new password.
| | 01:39 | I get back a message saying I have succeeded
and that the connection has been closed.
| | 01:43 | Then I'll run the application again and show
that the new password was successfully updated,
| | 01:48 | and I'll stop the application.
| | 01:50 | Now, again, the reason the change was made
is because my transaction is auto-committing.
| | 01:55 | But here's how you can change your
connection so that it doesn't auto-commit.
| | 01:59 | I'll need a reference to my connection,
and right now my connection is being managed
| | 02:02 | by my Singleton ConnectionManager class.
| | 02:05 | I'll place the cursor before the call to
the update method, and I'll create a reference
| | 02:09 | to my connection object with Connection conn =
ConnectionManager.getInstance().getConnection().
| | 02:16 | I will make sure I have included an import
for the connection interface in Java.SQL.
| | 02:20 | Now I'm going to set the autocommit
property to false with conn.setAutoCommit, and I'll
| | 02:27 | pass in a value of false. So far, so good.
| | 02:31 | Now when I go to the update method, this is where
the SQL statement is being processed and executed.
| | 02:37 | And previously, when the connection
object's AutoCommit property was set to the default
| | 02:42 | of true, the change was made and committed to the
database when the executeUpdate method was called.
| | 02:47 | But now that I have set AutoCommit to false, that's no
longer the case, and I have to explicitly commit the change.
| | 02:53 | So I'll go back to my main class, and down
here I'll call conn.commit, and then I'll
| | 02:59 | do a little bit of system output, and I'll
output the message Transaction committed,
| | 03:04 | and I'll test my code again.
| | 03:05 | Once again, I'll type in the row
number and another new password.
| | 03:09 | This time I'll just use pwd.
| | 03:10 | I get back the message, Success,
and that it was committed.
| | 03:13 | Then I'll run the application again to see
the data, and I'll see the change was made.
| | 03:17 | But now let's change that logic so
that we're rolling back the connection.
| | 03:21 | I will change from commit to rollback, and
I'll change the message to indicate what I did.
| | 03:27 | I'll run the code again,
I'll type in the row number and new password,
| | 03:32 | and I get back the message that the update method call
was successful but that I have rolled back the transaction.
| | 03:37 | So, now I'll run the application again,
and I'll see that the change was not made.
| | 03:41 | I still have the old password of pwd.
| | 03:44 | So, once you have set the connection object's
AutoCommit property to false, you can then
| | 03:49 | explicitly commit or roll back
your transactions as you need to.
| | 03:52 | The specific logic of your application will
differ depending on the type of application
| | 03:57 | and your reasons for rolling back transactions.
But the code is pretty simple to use.
| | 04:02 | Just make sure that you have set AutoCommit to
false, that you're using a database management
| | 04:06 | system that supports transactions, and that
you're calling the commit or rollback methods
| | 04:11 | in the appropriate places of your application.
| | 04:16 |
| | Collapse this transcript |
|
|
6. Using MetadataGetting the DatabaseMetaData object| 00:00 | In most database application environments,
you already know the database structure, the
| | 00:05 | names of the tables, the number,
and types of the columns, and so on,
| | 00:09 | but there are times when you have to discover
this information dynamically, and JDBC gives
| | 00:14 | you that ability through a
function called metadata.
| | 00:17 | The term metadata in the context of
databases describes exactly the sort of information,
| | 00:23 | and you can get the metadata from
a database connection quite easily.
| | 00:26 | I'll do an initial demonstration of this
functionality in a project named Table List.
| | 00:31 | I'm using a Singleton Connection Manager which
I have described how to build in a previous exercise.
| | 00:37 | I have gotten a reference to my connection,
and then within the try catch block, I'm going
| | 00:41 | to add some data to get a list
of tables that's in my database.
| | 00:46 | Within the try block, I'll first declare
an object instantiated as DatabaseMetaData.
| | 00:52 | As with many other objects in JDBC,
you might see more than one version.
| | 00:56 | Always be sure to choose the version
from java.sql for maximum portability.
| | 01:01 | I'll name this object metadata.
| | 01:03 | Then I'll get its reference from the
connection object's getMetaData method.
| | 01:07 | You'll see a couple of versions
listed, but in fact, there is only one.
| | 01:11 | The getMetaData method takes no arguments.
| | 01:14 | I'm going to interrogate this
object to get a list of tables.
| | 01:17 | There are actually many
different types of tables in a database.
| | 01:21 | But the only ones I'm interested in
are the ones that contain my data.
| | 01:24 | And there's a series of
database types that I can select from.
| | 01:28 | To control which database table names are
returned, I'm going to create an array of
| | 01:33 | strings, and I'll name it tableTypes, and
I'll declare the array as a pair of braces,
| | 01:39 | and I will only have it contain one item.
| | 01:41 | All database management systems
should support this table type,
| | 01:46 | but the number and names of the different
table types will then vary from one database
| | 01:50 | management system to the next.
| | 01:52 | Now, I'm going to get the list of tables.
It's going to come back as a result set.
| | 01:56 | I'm going to name the result set RS Tables,
and to properly manage it, first I will declare
| | 02:01 | the result set outside of the try catch block.
| | 02:04 | I'll declare it with a data type of ResultSet,
then rsTables as the object name, and I'll
| | 02:10 | initially set it to null.
| | 02:12 | Then within the try block, I'll
instantiate it with rsTables=metadata.getTables.
| | 02:19 | The getTables method takes four arguments,
the catalog which is the name of the database
| | 02:25 | which I have already selected in my connection
string, so I don't need to pass it in again.
| | 02:29 | I'll set that to null, the schemaPattern
which I'll set as a wildcard of a percent sign,
| | 02:35 | the tableNamePattern which I'll also set
with a percent sign wildcard, and the tableTypes
| | 02:40 | which will be my array of
strings that I already created.
| | 02:44 | The result set will come back
with a column named table_name.
| | 02:48 | So now, to print out the list of tables, I'll
create a while loop, and I will iterate with a condition.
| | 02:54 | The condition will be rsTables.next.
| | 02:57 | So I am going to be moving forward through the
ResultSet and working with each row one at a time.
| | 03:02 | Then within the while loop, I'll use system output,
and I'll output the value of rsTables.getString,
| | 03:09 | and I'll pass in the name
of the column TABLE_NAME.
| | 03:12 | When I'm done, I'll need
to close the result set.
| | 03:15 | As with all result sets, you will
typically do this in a finally block.
| | 03:19 | So I'll move the cursor
there and call rsTables.close.
| | 03:23 | I'll save my changes and run the code, and
there is my list of tables in my backend database.
| | 03:28 | You're able to do a lot more than this with
the database metadata, including finding out
| | 03:33 | about columns, their names, and their datatypes.
| | 03:36 | I'll show you how to do some of
these other tasks in another video.
| | 03:41 |
| | Collapse this transcript |
| Getting column and data type information| 00:00 | I have previously described how to use the database
metadata class to get a list of tables from a database.
| | 00:06 | I'm working in a new version of that project
named TablesAndColumns where I already have
| | 00:11 | the code to get the list of tables.
| | 00:13 | I'll test it and make sure that I'm
successfully retrieving that information.
| | 00:17 | Now I'm going to show you how to get more
information from the metadata object, including
| | 00:21 | the names and data types
of the database columns.
| | 00:25 | I'll start by re-factoring this code.
| | 00:27 | Instead of immediately outputting the name
of each table, I'm going to store the names
| | 00:31 | of the tables in an array list.
| | 00:34 | I'll start by declaring an array list at the
top of the code, I will place my cursor before
| | 00:38 | the try catch block, and I will declare my
ArrayList, and I will set its data type to
| | 00:44 | String, that is that's the data
type of the items it will contain.
| | 00:47 | And I will name it tables.
| | 00:49 | I will instantiate it with the
ArrayList class's no arguments constructor.
| | 00:53 | Now I'll go down to the while loop, where I am
looping through the result set of the tables.
| | 00:58 | I'm still going to need the names of the tables,
so I'll select that code and copy it to the
| | 01:03 | clipboard, and then I'll delete that line.
| | 01:06 | Then I'll replace that code within the while
loop with a call to the array list add method
| | 01:11 | using tables.add, and I will paste in that
code that was getting the table name already.
| | 01:17 | So now I have a resizable array, an array
list containing the names of the tables, and
| | 01:22 | I can loop through that and get the
columns for the tables one at a time.
| | 01:26 | To contain that information, I'm
going to need another result set.
| | 01:30 | So I will move back up to where I'm
declaring my local variables within the main method,
| | 01:35 | and I'll declare another result set, this one
called rsColumns, and I will initially set it to null.
| | 01:40 | Now I will move back to after the while
loop, and I'm ready to process the tables.
| | 01:45 | I will make some extra space.
| | 01:46 | And to loop through the array list,
I'll use a foreach code template.
| | 01:51 | I'll type in foreach and press Ctrl+Space,
and I'll choose that code template, and I
| | 01:56 | will name the variable that's going to be
generated each time through the loop, tableName.
| | 02:01 | I'm going to be looping
through the tables collection.
| | 02:03 | So I will make that change as well.
| | 02:06 | On each time through the table, I
will first output the name of the table.
| | 02:10 | I'm going to need three system output lines.
| | 02:13 | So I will type in System.out and press Ctrl+Space,
and then I will duplicate that line a couple of times.
| | 02:18 | For the first system output, I will output
the name of the table prefixed with a label.
| | 02:23 | I will type a label of Table: and a space,
and I will append to that the tableName.
| | 02:29 | The second output will be a string of dashes,
that will separate the tableName from the
| | 02:34 | columns, and the third one will just be a blank
string, and I will output in the column names
| | 02:39 | before that blank string, so I will make
some extra space there and here is where we will
| | 02:44 | put the code to get the list of the columns.
| | 02:46 | I have already declared my result set
where I will contain the list of the columns.
| | 02:50 | I named it rsColumns.
So now I will get its reference.
| | 02:54 | I will use rsColumns =, then just as I did for the
tables, I will use a method of the metadata object.
| | 03:01 | It will be metadata.getColumns.
| | 03:04 | The getColumns method is very
similar to the getTables method.
| | 03:07 | It asks for four arguments.
| | 03:09 | As with getTables, the catalog can be left
to null because I have already designated
| | 03:14 | the catalog or database I am
working with in my connection string.
| | 03:18 | The schemaPattern can be set to a wildcard.
| | 03:21 | The tableNamePattern should be the
specific table that you are querying.
| | 03:25 | So I'm going to pass in my variable
tableName and the columnNamePattern can be a wildcard
| | 03:31 | because I'm asking for all of
the columns for the current table.
| | 03:35 | So now my result set will contain all of
the information I need about my columns.
| | 03:40 | The result set will contain a bunch of columns,
and you should take a look at the documentation
| | 03:44 | for this method to see what the column names are,
but the particular column names I'm interested
| | 03:49 | in are called column_name and type_name.
| | 03:55 | I'm going to loop through the result
set using a while loop with a condition.
| | 03:59 | I will set my condition to a
call to the rsColumn.next method.
| | 04:04 | That will be rsColumns.next.
Within the loop I will create a StringBuffer.
| | 04:08 | I will name it buffer, and I will
instantiate it with the no arguments constructor.
| | 04:13 | I'll start by appending the name of the column,
I'll use rsColumns.getString, and I will pass
| | 04:19 | in the name of the column
which will be COLUMN_NAME.
| | 04:24 | I'll duplicate that line of code, and I'll
change the second one from COLUMN_NAME to TYPE_NAME.
| | 04:30 | And in between, I will separate these
bits of output with a colon and a space.
| | 04:35 | Finally, I'll add some system output, and I'll output
the value of the two string method of the buffer object.
| | 04:41 | I will clean up the extra white space, and
now I'm ready to test my application again.
| | 04:46 | Let's review the code.
I'm looping through the array list of tables.
| | 04:50 | On each time through the loop I am outputting
the name of the table and a separator string.
| | 04:54 | Then I'm getting the list of columns just
for that table and then I'm doing another
| | 04:58 | loop, this time through
the result set of columns.
| | 05:00 | I create a string buffer, and I append to
that the name of the column, a separator and
| | 05:05 | the name of the datatype and
then I output that information.
| | 05:09 | I'll move the cursor down to the finally
block and do one last little bit of cleanup.
| | 05:13 | I will close the rsColumns object, and now
I'll run my code, and I get back exactly the
| | 05:18 | list I am looking for.
| | 05:20 | For each table, I'm displaying the name of
the table and then after the separator the
| | 05:24 | names and data types of each of the columns.
| | 05:27 | As I have mentioned previously, look at the
documentation for the getColumns method, you'll
| | 05:31 | see a listing of all of the names of the
columns that are returned in this result set.
| | 05:36 | You'll find that you can get the names of
the columns, the lengths of the columns, and
| | 05:40 | all of the other information you might need
and look thoroughly at what else is available
| | 05:45 | in the database metadata object, it will return
information about stored procedures, functions,
| | 05:51 | and pretty much everything that
you have stored in your database.
| | 05:56 |
| | Collapse this transcript |
|
|
ConclusionNext steps| 00:00 | Thanks for sitting with me through this
video series Java Database Integration with JDBC.
| | 00:05 | In this series, I described how to work with
relational database management systems using the JDBC API.
| | 00:12 | JDBC is at the core of nearly every database
interaction you might need in Java, so regardless
| | 00:17 | of whether you're building desktop
applications or web applications that are hosted on Java
| | 00:22 | enterprise edition servers, JDBC will be
one of the foundations of those applications.
| | 00:27 | To learn more about other aspects of Java,
check out the course, Java Advanced Training.
| | 00:33 | And to learn more about object-oriented
programming in general, I recommend watching
| | 00:37 | Foundations of Programming: Object-Oriented Design.
| | 00:40 | I hope this course has helped you get ready to
build your own Java-based data driven applications.
| | 00:46 | Thanks again for watching and happy programming.
| | 00:51 |
| | Collapse this transcript |
|
|
