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 186 Views

Recommend Documents

JDBC ( JAVA Database Connectivity )
the column to update and the new value to put in that column. As with the ResultSet.getXXX methods, the parameter designating the column may be either the column name or the column number. There is a different. updateXXX method for updating each data

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.

Java Data Access—JDBC, JNDI, and JAXP
For general information on Hungry Minds' products and services please ...... and retrieve Java objects from a B−tree storage structure stored on a hard drive.

Java Data Access—JDBC, JNDI, and JAXP
Bachelor of Science degree from James Madison University in 1990, and his Master of Science ...... Companies also build online stores using live inventory levels that sell directly ..... Rows represent entries like patient medical records or customer

Java Programming with Oracle JDBC By GiantDino
software for free. • Better yet, sign up for a technology track or two. Technology tracks cost $200 each. For your $200, you get four updates a year on a CD of all the ..... has security issues. It's common for programmers to have problems establis

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
When you're on the road, you can stay connected with a 3G connectivity package from. Verizon Wireless. Chromebook that have built-in 3G include up to 100MB ...

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.

Java Database Programming with JDBC:Introduction
Oct 1, 1996 - Chapter 6—SQL Data Types In Java And ORM. Mapping SQL Data To Java ...... proper place on your computer's hard disk. The method shown ...

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.

MySQL-Dan-Java-Database-Connectivity.pdf
MySQL-Dan-Java-Database-Connectivity.pdf. MySQL-Dan-Java-Database-Connectivity.pdf. Open. Extract. Open with. Sign In. Main menu. Displaying ...

LP-JDBC-Clase 1.pdf
Sign in. Loading… Whoops! There was a problem loading more pages. Retrying... Whoops! There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. LP-JDBC-Clase 1.pdf.

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.

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.

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.

Rudi Hartanto - Java Database Netbeans - Add Edit Del.pdf ...
There was a problem loading more pages. Retrying... Whoops! There was a problem previewing this document. Retrying... Download. Connect more apps... Try one of the apps below to open or edit this item. Rudi Hartanto - Java Database Netbeans - Add Edi

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.

PDF Download JDBC Pocket Reference Full Books
Book synopsis. Paperback. Pub Date: 2003 Pages: 152 Publisher: OReilly Media JDBC - the Java Database Connectivity specification - is a complex set of ...

COMMITTEE CONNECTIVITY IN THE UNITED ... - Semantic Scholar
random graph theory, statistics and computer science, we discuss what con- .... At the start of each two-year term, Representatives deliver written requests to ..... We call this Model A. Figure 4 shows that these fixed degree assignments ...