Primary key

1
(No Transcript)
2
(No Transcript)
3
Primary key
Columns
Rows
4

• Description
• A relational database uses tables to store and
  manipulate data. Each table contains one or more
  records, or rows, that contain the data for a
  single entry. Each row contains one or more
  fields, or columns, with each column representing
  a single item of data.
• Most tables contain a primary key that uniquely
  identifies each row in the table.
• The software that manages a relationship database
  is called a database management system (DBMS).
  Four of the most popular database management
  systems today are Oracle, Microsoft SQL Server,
  IBM's DB2, and Microsoft Access.

5
Primary key
Foreign key
6

• Description
• The tables in a relational database are related
  to each other through their key fields. For
  example, the BookCode field is used to relate the
  Books and BookOrders tables. The BookCode field
  in the BookOrders table is called a foreign key
  because it identifies a related row in the Books
  table.
• Three types of relationships can exist between
  tables. The most common type is a one-to-many
  relationship as illustrated above. However, two
  tables can also have a one-to-one relationship or
  a many-to-many relationship.

7
(No Transcript)
8

• Description
• A database management system requires a name and
  data type for each field. Depending on the data
  type, you may be able to specify other attributes
  for the fields such as field size, a value to be
  used as a label, whether the field is required by
  the new rows or not, and so on.
• If you specify a default value for field, that
  value is used for the field in a new record when
  no other value is supplied.

9
(No Transcript)
10
CurrentRow Pointer
11

• A SELECT statement that returns all columns and
  rows
• SELECT FROM Books
• Description
• The SELECT statement is used to perform a query
  that retrieves rows and columns from a database.
• The result set (or result table) is the set of
  records that are retrieved by a query.
• The current row pointer, or cursor, identifies
  the current row in a result set. You can use this
  pointer to identify the row you want to update or
  delete from a result set. Any change to the
  result set is reflected in the table that the
  result set is based on.
• To select all of the columns in a table, you can
  code an asterisk () instead of coding field
  names.
• For efficiency, you should code your queries so
  the result set has as few rows and as few columns
  as possible.

12
(No Transcript)
13
(No Transcript)
14

• Description
• A join lets you combined data from two or more
  tables into a single results set.
• An inner join, or equi-join, returns records from
  both tables but only if their related fields
  match. An outer join returns records from one
  table in the join (the LEFT or RIGHT table) even
  if the records aren't matched by records in the
  other table.

15
(No Transcript)
16
(No Transcript)
17
(No Transcript)
18
(No Transcript)
19

• The four types of Java drivers
• Type 1. The JDBC-ODBC bridge driver converts JDBC
  calls into ODBC calls that access the DBMS
  protocol. This data access method requires that
  the ODBC drivers be installed on the client
  machines.
• Type 2. A native protocol partly Java driver
  converts JDBC calls into calls in the native DBMS
  protocol. Since this conversion takes place on
  the client, some binary code must be installed on
  the client machines.
• Type 3. A net protocol all Java driver converts
  JDBC calls into a net protocol that independent
  of any native DBMS protocol. Then, middleware
  software running on the server converts the net
  protocol to the native DBMS protocol. Since this
  conversion takes place on the server side, no
  installation is required on the client machines.
• Type 4. A native protocol all Java driver
  converts JDBC calls into a native DBMS protocol.
  Since this conversion takes place on the server
  side, no installation is required on the client
  machine.

20

• Notes
• To get information about the drivers that are
  currently available, checked the Java Web site at
  www.java.sun.com/products/jdbc and click on the
  List of Drivers Available link.
• Since type-1 and type-2 drivers requires some
  client installation, they're not a good solution
  for Internet applications.

21
(No Transcript)
22
(No Transcript)
23

• Note
• This procedure will vary slightly depending on
  the operating system and on the type of database.
  However, the general idea is the same. You must
  select the type of ODBC driver you must provide
  a name for the data source and you must locate
  the data source. That way, the client machine or
  server has all the data it needs to access the
  data source.

24
(No Transcript)
25
(No Transcript)
26

• Description
• The forName method of the Class class throws a
  ClassNotFoundException.
• The getConnection method of the DriverManager
  class throws a SQLException.
• Since the Connection object will often be used by
  more than one method in a class, its often
  declared as static instance variable.
• To learn more about the JDataConnect driver,
  check the NetDriver website (www.j-netdirect.com).
  

27
(No Transcript)
28
(No Transcript)
29

• Description
• The createStatement method of a Connection object
  creates a Statement object. The, the excuteQuery
  method of the Statement object executes a SELECT
  statement that returns a ResultSet object.
• By default, the CreateStatement method creates a
  forward-only, read-only result set. However, you
  can set the type and concurrency of s Statement
  object by coding the constants above for the two
  arguments of the createStatament method.
• Both the createStatement and executeQuery methods
  throw an exception of the SQLException type. As a
  result, any code that returns a result set will
  need to catch or throw this exception.

30
(No Transcript)
31
(No Transcript)
32
(No Transcript)
33

• Description
• When you create a result set, the cursor is
  positioned before the first record. As a result,
  the first time you call the next method, it will
  move to the first record in the results set.
• The first, previous, next, last, absolute, and
  relative methods all return a true value if the
  new row exists and a false if the new row doesnt
  exist or the result isnt valid.
• All of the methods in the figure throw an
  exception of the SQLException type.

34
(No Transcript)
35
(No Transcript)
36

• Description
• The getXXX methods can be used to return all
  eight primitive types. For example, the getInt
  method returns the int type and the getLong
  method returns the long type.
• The getXXX methods can also be used to return
  strings, dates, and times. For example, the
  getString method returns any object of the String
  class while the getDate, getTime, and
  getTimestamp methods returns objects of the Date,
  Time, Timestamp classes of the java.sql package.
• Although they arent widely used, the getBlob and
  getClob methods can be used to return BLOB
  objects (Binary Large Objects) and CLOB objects
  (Character Large Objects).

37
(No Transcript)
38
(No Transcript)
39
(No Transcript)
40

• Description
• The excuteUpdate method is an older method that
  works with most JDBC drivers. The newer methods
  may not work properly with older JDBC drivers.
• The excuteUpdate method returns an int value that
  identifies the number of records that were
  affected by the update.
• When you delete a record, the result set may
  contain an invalid row where the deleted row used
  to be. To solve this problem, you can close the
  result set and reopen it.

41
(No Transcript)
42
(No Transcript)
43

• Description
• To specify a parameter, type a question mark (?)
  in the SQL statement.
• To supply values for th oarameterrs in a prepared
  statement, use the setXXX methods of the
  PreparedStatement interface. For a complete list
  of setXXX methods, look up the PreparedStatement
  interface of the java.sql package in the
  documentation for the Java API.
• To execute a SELECT statement, use the
  excuteQuery method. To execute an INSERT INTO,
  UPDATE, or DELETE statement, use the
  executeUpdate method.

44
(No Transcript)
45
(No Transcript)
46
(No Transcript)
47
(No Transcript)
48
(No Transcript)
49
(No Transcript)
50
(No Transcript)
51
(No Transcript)
52
(No Transcript)
53
(No Transcript)
54
(No Transcript)
55
(No Transcript)
56

• Description
• When you specify an index value for one of the
  methods shown in this figure, use 1 for the first
  column, 2 for the second column, and so on.
• You can use the constants of the Types class of
  the java.sql package to specify an int value for
  a SQL data type.

57
(No Transcript)
58
(No Transcript)
59

• Description
• To get SQL data type thats used in the column of
  a result set, you can use the getColumnType and
  getColumnTypeName methods of the
  ResultSetMetaData object.