JDBC ( JAVA Database Connectivity )

What is JDBC ?  The JDBC (JAVA Database Connectivity) API is a

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

Why JDBC ?  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.

A Database Overview

 A database is a means of storing information in such

a way that information can be retrieved from it. In simplest terms, a relational database is one that presents information in tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). 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.

Database Management System  A Database Management System (DBMS) handles

the way data is stored, maintained, and retrieved. In the case of a relational database, a Relational Database Management System (RDBMS) performs these tasks. DBMS as used in this book is a general term that includes RDBMS.

Integrity Rules  First, the rows in a relational table should all be

distinct. If there are duplicate rows, there can be problems resolving which of two possible selections is the correct one. For most DBMSs, the user can specify that duplicate rows are not allowed, and if that is done, the DBMS will prevent the addition of any rows that duplicate an existing row.

Integrity Rules(cont.)  A second integrity rule of the traditional relational

model is that column values must not be repeating groups or arrays.

Integrity Rules (cont.)  A third aspect of data integrity involves the concept

of a null value. A database takes care of situations where data may not be available by using a null value to indicate that a value is missing. It does not equate to a blank or zero. A blank is considered equal to another blank, a zero is equal to another zero, but two null values are not considered equal.

Primary key  When each row in a table is different, it is possible to

use one or more columns to identify a particular row. This unique column or group of columns is called a primary key. 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. This rule is referred to as entity integrity.

SQL  SQL is a language designed to be used with

relational databases.

SQL Commands  SQL commands are divided into these categories :

1- Data Manipulation Language (DML) : which deals with data, either retrieving it or modifying it to keep it up-to-date. Example: SELECT , DELETE , INSERT & UPDATE

SQL Commands 2- Data Definition Language (DDL) : Its commands create or change tables and other database objects such as views and indexes. Example : CREATE TABLE , DROP TABLE

Common SQL commands  A SELECT statement :

also called a query, is used to get information from a table. It specifies one or more column headings, one or more tables from which to select, and some criteria for selection. The RDBMS returns rows of the column entries that satisfy the stated requirements. A SELECT statement such as the following will fetch the first and last names of employees who have company cars: SELECT First_Name, Last_Name FROM Employees WHERE Car_Number IS NOT NULL

Common SQL commands (cont.)  FIRST_NAME

--------Axel Florence

LAST_NAME ----------Washington Wojokowski

Common SQL commands (cont.)  The code fragment below has a WHERE clause that

uses the equal sign (=) to compare numbers. It selects the first and last name of the employee who is assigned car 12. SELECT First_Name, Last_Name FROM Employees WHERE Car_Number = 12

Common SQL commands (cont.)  LIKE statement :

The keyword LIKE is used to compare strings, and it offers the feature that patterns containing wildcards can be used. SELECT First_Name, Last_Name FROM Employees WHERE Last_Name LIKE 'Washington%'

Common SQL commands (cont.)  WHERE clauses can get rather elaborate, with multiple

conditions and, in some DBMSs, nested conditions. This overview will not cover complicated WHERE clauses, but the following code fragment has a WHERE clause with two conditions; this query selects the first and last names of employees whose employee number is less than 10100 and who do not have a company car. SELECT First_Name, Last_Name FROM Employees WHERE Employee_Number < 10100 and Car_Number IS NULL

Establishing a connection  What are drivers ?  Loading a driver :

Loading the driver you want to use is very simple. It involves just one line of code in your program. To use the Java DB driver, add the following line of code: Class.forName("org.apache.derby.jdbc. EmbeddedDriver");

DriverManager Class  The DriverManager class works with the Driver

interface to manage the set of drivers available to a JDBC client.  When the client requests a connection and provides a URL, the DriverManager is responsible for finding a driver that recognizes the URL and for using it to connect to the corresponding data source.

Connecting to a database  Connection URLs have the following form:

String url = "jdbc:derby:Fred"; Connection con= DriverManager.getConnection(url, “User_Name", “Password");

JDBC

JDBC uses SQL commands in Strings and execute them using Statements Objects using methods like :executeUpdate() executeQuery(), …etc

Creating table example in SQL

CREATE TABLE Employees( Employee_Number INTEGER, First_Name varchar(50), Last_Name varchar(50), Date_Of_Birth varchar(50), Car_Number INTEGER)

JAVA code (java.sql.* package) Class.forName("net.sourceforge.jtds.jdbc.Driver"); Connection conn = DriverManager.getConnection("org.apache.derby.jdbc. EmbeddedDriver"); Statement stmt; String query = “CREATE TABLE Employees” +

“(Employee_Number INTEGER , “ + “First_Name varchar(50),” + “Last_Name varchar(50),” + “Date_Of_Birth varchar(50),” + “Car_Number INTEGER)“; stmt = conn.createStatement(); stmt.executeUpdate(query); stmt.close(); conn.close();

Deleting row from table (SQL)  DELETE FROM TABLE_NAME

WHERE CONDITION  Example :

DELETE FROM Employees WHERE Employee_Number = 1

Deleting a row in a specific table (JAVA) Statement st = conn.createStatement(); String sql = "DELETE FROM Employees WHERE Employee_Number = 10001"; int delete = st.executeUpdate(sql); if(delete == 1) System.out.println("Row is deleted."); else System.out.println("Row is not deleted.");

Dropping a table example Connection conn = DriverManager.getConnection("org.apache.dery. jdbc.EmbeddedDriver"); Statement sta = conn.createStatement(); sta.executeUpdate("DROP TABLE Employees"); System.out.println("Table dropped."); sta.close(); conn.close();

Inserting Values into tables  Inserting Values into tables example:String insertString1, insertString2, insertString3, insertString4; insertString1 = "INSERT INTO Employees (Employee_Number, First_Name) VALUES(6323, 'Heath')"; insertString2 = "INSERT INTO Employees (Employee_Number, First_Name) VALUES(5768, 'Bob')"; insertString3 = "INSERT INTO Employees (Employee_Number, First_Name) VALUES(1234, 'Shawn')"; stmt = con.createStatement();  stmt.executeUpdate(insertString1);  stmt.executeUpdate(insertString2);  stmt.executeUpdate(insertString3); 

Retrieving Values from Result Sets : TYPE_FORWARD_ONLY — The result set is not

scrollable; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database materializes the results.  TYPE_SCROLL_INSENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.  TYPE_SCROLL_SENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.

Retrieving Values from Result Sets :Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SE NSITIVE, ResultSet.CONCUR_READ_ONLY);  ResultSet srs = stmt.executeQuery("SELECT First_Name, Car_Number FROM Employees");  OR we can use ResultSet.CONCUR_UPDATABLE to

update the results back to the database

ResultSet Object  When a ResultSet object is first created, the cursor is

positioned before the first row. To move the cursor, you can use the following methods:  next() - moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.  previous() - moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.  first() - moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.

ResultSet Object last() - moves the cursor to the last row in the ResultSet object.  Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows. beforeFirst() - positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect. afterLast() - positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect. relative(int rows) - moves the cursor relative to its current position. absolute(int row) - positions the cursor on the row-th row of the ResultSet object.

getXXX methods Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet srs = stmt.executeQuery( "SELECT First_NAME, Car_Number FROM COFFEES"); while (srs.next()) { String name = srs.getString(“First_Name"); int carNumber = srs.getInt(“Car_Number"); System.out.println(name + " " + carNumber); }

getXXX methods Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet srs = stmt.executeQuery("SELECT First_Name, Car_Number FROM COFFEES"); srs.afterLast(); while (srs.previous()) { String name = srs.getString("COF_NAME"); float price = srs.getFloat("PRICE"); System.out.println(name + " " + price); }

Navigation through ResultSet  The following line of code moves the cursor to the

fourth row of srs:  srs.absolute(4);  If srs has 500 rows, the following line of code moves

the cursor to row 497:  srs.absolute(-4);

Navigation through ResultSet

srs.absolute(4); int rowNum = srs.getRow(); // rowNum should be 4 srs.relative(-3); int rowNum = srs.getRow(); // rowNum should be 1 srs.relative(2); int rowNum = srs.getRow(); // rowNum should be 3

MetaData  Metadata is "DATA ABOUT DATA !!!!“

Metadata has a lot of kind such as : Relational database metadata  FileSystem metadata  Image metadata  Meta-metadata

Relational database metadata  Each relational database system has its own mechanisms

for storing metadata. Examples of relational-database metadata include:  Tables of all tables in a database, their names, sizes, number of rows in each table, primary keys, foreign keys, stored procedures, and so forth.  Tables of columns in each database, what tables they are used in, and the type of data stored in each column.  Each DBMS has its own functions for getting information about table layouts and database features

RDB Metadata  In database terminology, this set of metadata is

referred to as the catalog.  The SQL standard specifies a uniform means to access the catalog, called the INFORMATION_SCHEMA, but not all databases implement it, even if they implement other aspects of the SQL standard.

RDB metadata  JDBC provides the interface DatabaseMetaData,

which a driver writer must implement so that its methods return information about the driver and/or DBMS for which the driver is written. For example, a large number of methods return whether or not the driver supports a particular functionality. This interface gives users and tools a standardized way to get metadata.  In general, developers writing tools and drivers are the ones most likely to be concerned with metadata.

To be continued …

Karim El-sayed Khamis Mohamed Abd-elsalam Nour-eldin Mohamed Yasser Ahmed Nour Mostafa Mahmoud Mahmoud Eweda

Example

Back

JDBC ( JAVA Database Connectivity )

Java API that can access any kind of tabular data, especially data stored in a Relational Database. ... 1- Data Manipulation Language (DML) : which deals with ...

421KB Sizes 1 Downloads 58 Views

Recommend Documents

Java JDBC CAI 25.04.2013.pdf
... its own API (JDBC API). that uses JDBC driver written in Java language. Page 3 of 45. Java JDBC CAI 25.04.2013.pdf. Java JDBC CAI 25.04.2013.pdf. Open.

JDBC Driver.pdf
There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. JDBC Driver.pdf.

Connectivity
Free data available for 2 years from the time you first activate your 3G service. 2. A day pass offers unlimited data access for 24 hours from the time of data purchase. 3. Any purchase of additional data expires after 30 days from the date of data p

Connectivity
Broadband service for 2 years, provided by Verizon Wireless. Also available are an unlimited day pass for just $9.99 and pay-as-you-go rates that are.

types of jdbc drivers pdf
Page 1. Whoops! There was a problem loading more pages. types of jdbc drivers pdf. types of jdbc drivers pdf. Open. Extract. Open with. Sign In. Main menu.

Modul Praktikum Java Database Programming.pdf
Whoops! There was a problem loading this page. Whoops! There was a problem loading this page. Modul Praktikum Java Database Programming.pdf.

Corridors and connectivity
George Street, Brisbane 4001, Australia; *Author for correspondence (e-mail: [email protected]). Received 22 ... a means for detecting gene flow, direct methods. (i.e. trapping) are ..... (Compliance no. ISO 11794, Veterinary Marketing.

O'Reilly - Java Database Programming with JDBC.pdf
Oct 1, 1996 - O'Reilly - Java Database Programming with JDBC.pdf. O'Reilly - Java Database Programming with JDBC.pdf. Open. Extract. Open with. Sign In.

Rudi Hartanto - Java Database Netbeans - Add Edit Del.pdf ...
Rudi Hartanto - Java Database Netbeans - Add Edit Del.pdf. Rudi Hartanto - Java Database Netbeans - Add Edit Del.pdf. Open. Extract. Open with. Sign In.

Effective connectivity: Influence, causality and ...
Available online xxxx. Keywords: .... whether some causal link is likely to be present (by comparing models with and ...... This simple model has proven to be a useful tool in many fields, including ...... Human Brain Mapping annual Meeting.