Transcript JDBC and Sql

1302383
Web
Programming
MySql JDBC
Web Programming
Course Content
 SQL
Command
 JDBC Statement
Web Programming
::JDBC Driver
 Database
specific implemention of
JDBC interfaces
 Every
database server has
corresponding JDBC driver(s)
 You
can see the list of available
drivers from
 http://industry.java.sun.com/products/j
dbc/drivers
Web Programming
::Database URL
 Used
to make a connection to the
database

Can contain server, port, protocol etc…
 jdbc:subprotocol_name:driver_dependan
t_databasename



Oracle thin driver
jdbc:oracle:thin:@machinename:1521:dbname
Derby
jdbc:derby://localhost:1527/sample
Pointbase
jdbc:pointbase:server://localhost/sample
Web Programming
:Steps of Using JDBC
1.
2.
3.
4.
5.
6.
7.
Load DB-specific JDBC driver
Get a Connection object
Get a Statement object
Execute queries and/or updates
Read results
Read Meta-data (optional step)
Close Statement and Connection
objects
Web Programming
::1. Load DB-Specific
Database
Driver
 To
manually load the database driver and
register it with the DriverManager, load its
class file

Class.forName(<database-driver>)
Web Programming
::2. Get a Connection Object
 DriverManager
class is responsible
for selecting the database and and
creating the database connection
 Using
DataSource is a prefered means
of getting a conection object (we will
talk about this later)
 Create
the database connection
as follows:
Web Programming
::DriverManager & Connection

java.sql.DriverManager


getConnection(String url, String user, String
password) throws SQLException
java.sql.Connection





Statement createStatement() throws
SQLException
void close() throws SQLException
void setAutoCommit(boolean b) throws
SQLException
void commit() throws SQLException
void rollback() throws SQLException
Web Programming
::3. Get a Statement Object
•
Create a Statement Object from Connection object
–
java.sql.Statement
•
•
•
•
ResultSet executeQuery(string sql)
int executeUpdate(String sql)
Example:
Statement statement =
connection.createStatement();
The same Statement object can be used for many,
unrelated queries
Web Programming
::4. Executing Query or Update
 From
the Statement object, the 2 most used
commands are

(a) QUERY (SELECT)
 ResultSet
rs = statement.executeQuery("select * from
customer_tbl");

(b) ACTION COMMAND (UPDATE/DELETE)
 int
iReturnValue = statement.executeUpdate("update
manufacture_tbl set name = ‘IBM' where mfr_num = 19985678");
Web Programming
::5. Reading Results
•
Loop through ResultSet retrieving information
–
java.sql.ResultSet
•
•
•
•
•
boolean next()
xxx getXxx(int columnNumber)
xxx getXxx(String columnName)
void close()
The iterator is initialized to a position before
the first row
–
You must call next() once to move it to the first
row
Web Programming
::5. Reading Results
(Continued)
 Once
you have the ResultSet, you can
easily retrieve the data by looping
through it
Web Programming
::5. Reading Results
(Continued)
 When
retrieving data from the ResultSet,
use the appropriate getXXX() method




getString()
getInt()
getDouble()
getObject()
 There
is an appropriate getXXX method of
each java.sql.Types datatype
Web Programming
::6. Read ResultSet MetaData


Once you have the ResultSet or Connection
objects, you can obtain the Meta Data about
the database or the query
This gives valuable information about the
data that you are retrieving or the database
that you are using


ResultSetMetaData rsMeta = rs.getMetaData();
DatabaseMetaData dbmetadata =
connection.getMetaData();

There are approximately 150 methods in the
DatabaseMetaData class.
Web Programming
:::ResultSetMetaData Examp
Web Programming
:::CREATE TABLE
Web Programming
::: DROP TABLE
Web Programming
::SELECT Statement
•
distinct keyword eliminates duplicates
Web Programming
:::SELECT Statement
Select * from customer_tbl;
Web Programming
:::FROM Clause
 Comma
delimited list of tables to retrieve
data from

With or without aliases
Web Programming
:::WHERE Clause
 Determines
exactly which rows are
retrieved
 Qualifications in the where clause






Comparison operators (=,>,<, >=,<=)
Ranges (between and not between)
Character matches (like and not like)
Unknown values (is null and is not null)
Lists (in and not in)
Combinations of the above (and, or)
Web Programming
:::WHERE Clause
(Continued)
 Not
negates any Boolean expressions and
keywords such as like, null, between and
in
Web Programming
:::Example: WHERE Clause
Web Programming
:::GROUP BY Clause
•
The group by function organises data into
groups based on the contents of a column(s)
–
–
–
•
Usually used with an aggregate function in the
select list
The aggregate is calculated for each group
All null values in the group by column are
treated as one group
Grouping can be done by a column_name or
by any expression that does not contain an
aggregate function
Web Programming
:::GROUP BY Clause Cont.
 The
group by function usually contains all
columns and expressions in the select list
that are not aggregates
 In conjunction with the where clause,
rows are eliminated before they go into
groups
 Applies a condition on a table before the
groups are formed
Web Programming
::::Example: GROUP BY
Clause
Web Programming
:::HAVING Clause
 Set

conditions on groups
Restricts groups
 Applies
a condition to the groups after
they have been formed
 having is usually used in conjunction with
an aggregate function
Web Programming
::::Example: HAVING Clause
Web Programming
:::ORDER BY Clause
 The
order by clause sorts the query results
(in ascending order by default)
 Items named in an order by clause need
not appear in the select list
 When using order by, nulls are listed first
Web Programming
:::ORDER BY Clause
Example
Web Programming
::Join Statement
 Retrieves
data from two or more tables
 Combines tables by matching values from
rows in each table
 Joins are done in the where clause
 Columns in join don't have to be in select
Web Programming
:::Join Statement
(Continued)
 If
you do not specify how to join rows from
different tables, the database server
assumes you want to join every row in
each table

Cartesian Product
 Very
costly (cpu time)
 May take a while to return large result set
 Column
names that appear in more then
one table should be prefixed by table
name
Web Programming
:::Example: Join Statement
Web Programming
:::Outer Joins
 With
a join, if one row of a table is
unmatched, row is omitted from result
table.
 The outer join operations retain rows that
do not satisfy the join condition

List branches and properties that are in
same city along with any unmatched
branches.
Web Programming
::::Outer Join cont.
 There
is a LEFT JOIN and a RIGHT JOIN
Web Programming
 These can be very vendor specific (check
with your vendor for outer join
::Alias
 Use
of temporary names for tables within
a query
 Can be used anywhere in query
 Reduces amount of typing
Web Programming
:::Example: Alias
Web Programming
:::Example: Alias cont.
Web Programming
::Sub-Queries
A
select statement, used as an expression in part of
another select, update, insert or delete
 The sub-query is resolved first and the results are
substituted into the outer query's where clause


Used because it can be quicker to understand than a
join
Perform otherwise impossible tasks (i.e. using an
aggregate)
Web Programming
:::Example: Sub-Query
Web Programming
::EXISTS and NOT EXISTS
 EXISTS
and NOT EXISTS are for use only with
sub-queries.
 They produce a simple true/false result
 EXISTS is true if and only if there exists at
least one row in result table returned by
sub-query
 It is false if sub-query returns an empty
result table
 NOT EXISTS is the opposite of EXISTS
Web Programming
:::EXISTS cont.
Web Programming
::Union, Intersection and
Difference
 Can
use normal set operations of union,
intersection, and difference to combine
results of two or more queries into a single
result table.
 Union of two tables, A and B, is table
containing all rows in either A or B or both.
 Intersection is table containing all rows
common to both A and B.
Web Programming
:::Union cont.
 Difference
is table containing all rows in A
but not in B.
 Two tables must be union compatible.
Web Programming
::Comparison Operators
Web Programming
:::Character Matches
 Use
to select rows containing field that
match specified portions of
characterstring
 Use with character data only
 Can use wildcards
 Enclose
quotes
wildcards and character strings in
Web Programming
:::Numerical Expressions
 Numerical
expressions can be used in any
numeric column or in any clause that
allows an expression
Web Programming
::Aggregate Functions
 Aggregates
ignore null values (except
count(*))
 sum and avg only work with numericWeb Programming
values
:::Aggregate Functions
Cont.
 If
a group by clause is not used, only one
row is returned
 Aggregates may not be used in a where
clause
 Aggregates can be applied to all rows in
a table or to a subset of a table
 Distinct keyword eliminates duplicate
values before applying the aggregate
function
Web Programming
::NULL Values
A
null value is an unknown value
 null
is a special value which means “no
information available”
 is null should be used to match columns
contains null values
 “= null” can also be used, but is not
recommended
 One null value is never equal to another
null value
 null's sort and group together
 Some columns are defined to permitWeb Programming
null values
::INSERT
Web Programming
:::INSERT cont
 The
column list is optional. If omitted, SQL
assumes a list of all columns in their
original CREATE TABLE order.
 Any columns omitted must have been
declared as NULL when table was
created, unless DEFAULT was specified
when creating column.
Web Programming
:::INSERT cont.
 Data
value list must match column list as
follows:



Number of items in each list must be the
same.
Must be direct correspondence in position
of items in two lists.
Data type of each item in data_value_list
must be compatible with data type of
corresponding column.
Web Programming
::::INSERT Examples
Web Programming
::UPDATE
•
•
SET clause specifies names of one or more
columns that are to be updated.
Where clause is optional. If omitted, named
columns are updated for all rows in table.
Web Programming
::::Update Example
Web Programming
::DELETE
 where
is optional; if omitted, all rows are
deleted from table. This does not delete
table. If the where is specified, only those
rows that satisfy condition are deleted.
Web Programming
:::DELETE examples
Web Programming