JDBC and RDBMS

JDBC Test Suite

Tests that help you to determine that JDBC drivers will run your program.

What is a 'lock'?

a mechanism that prohibits two transactions from manipulating the same data at the same time.

What is a JNDI?

a naming service to establish a connection with a data source.

What is a cursor?

a pointer that allows the user to access the data in a result set one row at a time. has the ability to keep track of which row is currently being accessed and may be used for iterative processing.

What is a table? Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database

a relation in the sense that it is a collection of rows.

What is a transaction in a db?

a set of one or more SQL statements that make up a logical unit of work. A transaction ends with either a commit or a rollback.

What is a 'java stored procedure'?

a static Java method that contains normal JDBC code.

primary key

a unique column or group of columns used to identify a particular row. May not have a value of null.

What does the INSERT statement do?

adds new rows to a table. INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.

ALTER TABLE

adds or removes a column from a table. It also adds or drops table constraints and alters column attributes.

UPDATE

changes an existing value in a column or group of columns in a table

JDBC Driver Manager

class that defines objects which can connect Java applications to a JDBC driver. DriverManager has traditionally been the backbone of the JDBC architecture. It is quite small and simple.

two-tier processing model

client/server configuration, with the user's machine as the client, and the machine housing the data source as the server.

What is structural metadata?

the design and specification of data structures "data about the containers of data".

What is a result set?

the set of rows that satisfy the requirement in a sql query.

What are transactions used for?

to maintain data in a consistent state (data consistency) while allowing more than one user to access a database at the same time (data concurrency).

JDBC helps you to write Java applications that manage these three programming activities:

1. Connect to a data source, like a database. 2. Send queries and update statements to the database. 3. Retrieve and process the results received from the database in answer to your query.

What are the 4 JDBC Product Components?

1. The JDBC API 2. JDBC Driver Manager 3. JDBC Test Suite 4. JDBC-ODBC Bridge

What is referential integrity?

A foreign key must either be null or equal to an existing primary key value of the table to which it refers.

what is a join?

A keyword used to get data from more than one table.

What does JNDI stand for?

Java Naming and Directory Interface

What is entity integrity?

Any column that is part of a primary key cannot be null; if it were, the primary key containing it would no longer be a complete identifier.

What does DBMS stand for?

Database Management System

JDBC-ODBC Bridge

Java Software bridge that provides JDBC access via ODBC drivers. you need to load ODBC binary code onto each client machine that uses this driver

What does a DBMS do?

Handles the way data is stored, maintained, and retrieved.

What does a RDBMS do?

Handles the way data is stored, maintained, and retrieved.

explain jdbc code fragment

Instantiates a DriverManager object to connect to a database driver and log into the database, instantiates a Statement object that carries your SQL language query to the database; instantiates a ResultSet object that retrieves the results of your query, and executes a while loop, which retrieves and displays those results.

Which driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.

ODBC

What does RDBMS stand for?

Relational Database Management System

What does the third aspect of data integrity involve?

The concept of a null (a value is missing). It does not equate to a blank or zero. A blank equals another blank, a zero equals another zero, but two null values are not considered equal.

What's attractive about the 3-tier processing?

The middle tier makes it possible to maintain control over access and the kinds of updates that can be made to corporate data. Another advantage is that it simplifies the deployment of applications.Finally, it can provide performance advantages.

What is the first integrity rule?

The rows in a relational table should all be distinct. There should be no duplicate rows.

What is the JDBC API?

a Java API that can access any kind of tabular data, especially data stored in a Relational Database.

What is a relational database?

a db that presents information in tables with rows and columns.

What is a foreign key?

a field in a relational table that matches the primary key column of another table.

What is a 'stored procedure'?

a group of SQL statements that can be called by name. Executable code, a mini-program, that performs a particular task that can be invoked the same way one can call a function or method.

What is a database?

a means of storing information in such a way that information can be retrieved from it.

three-tier processing model

commands are sent to a "middle tier" of services, which then sends the commands to the data source. The data source processes the commands and sends the results back to the middle tier, which then sends them to the use.

CREATE TABLE

creates a table with the column names the user provides. A type for the data in each column also needs to be specified.

DROP TABLE

deletes all rows and removes the table definition from the database. A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level. However, support for the CASCADE and RESTRICT options of DROP TABLE is optional. In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.

What does a 'java stored procedure' look like?

import java.sql.*; public class UpdateCar { public static void UpdateCarNum(int carNo, int empNo) throws SQLException { Connection con = null; PreparedStatement pstmt = null; try { con = DriverManager.getConnection( "jdbc:default:connection"); pstmt = con.prepareStatement( "UPDATE EMPLOYEES " + "SET CAR_NUMBER = ? " + "WHERE EMPLOYEE_NUMBER = ?"); pstmt.setInt(1, carNo); pstmt.setInt(2, empNo); pstmt.executeUpdate(); } finally { if (pstmt != null) pstmt.close(); } }}

Descriptive metadata

individual instances of application data, the data content. "data about data".

What are some of the features that make JDBC a server technology?

its support for connection pooling, distributed transactions, and disconnected rowsets. The JDBC API is also what allows access to a data source from a Java middle tier.

What are the names of the 2 packages the JDBC API is divided into?

java.sql javax.sql

The Standard Extension packages let you use a DataSource object registered with a JNDI naming service to establish a connection with a data source?

javax.naming javax.sql

What is a 'commit'?

makes SQL changes permanent.

row

objects of the same type.

What is a 'table lock'?

prevents a table from being dropped if there is an uncommitted transaction on that table. In some DBMSs, a table lock also locks all of the rows in a table.

What is a 'row lock'?

prevents two transactions from modifying the same row, or it prevents one transaction from selecting a row while another transaction is still modifying it.

JDBC API

provides programmatic access to relational data from the Java™ programming language. Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source. The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.

What does the WHERE clause in a SELECT statement do?

provides the criteria for selecting values.

Code fragment example of 3 jdbc programming activity steps:

public void connectToAndQueryDatabase(String username, String password) { Connection con = DriverManager.getConnection( "jdbc:myDriver:myDatabase", username, password); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); while (rs.next()) { int x = rs.getInt("a"); String s = rs.getString("b"); float f = rs.getFloat("c"); } }

DELETE

removes a specified row or set of rows from a table

integrity rules

rules to ensure that the data they contain stay accurate and are always accessible.

What is a second integrity rule of the traditional relational model?

that column values must not be repeating groups or arrays.

The JDBC API supports which processing models for database access?

two-tier and three-tier processing models

What is a 'rollback'?

undoes all SQL changes.

what is the keyword LIKE used for?

used to compare strings, and it offers the feature that patterns containing wildcards can be used.

What is a SELECT statement?

used to query and display data from a database. The SELECT statement specifies which columns to include in the result set.