Persistenta datelor
This presentation is the property of its rightful owner.
Sponsored Links
1 / 43

Persistenta datelor PowerPoint PPT Presentation


  • 79 Views
  • Uploaded on
  • Presentation posted in: General

Metode/Tehnologii: Fisiere Serializare Baze de date. Probleme: Decuplarea componentelor care fac accesul la date(specifice unei anumite tehnologii) de celelalte parti ale aplicatiei Abstractizarea accesului la date

Download Presentation

Persistenta datelor

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Persistenta datelor

Metode/Tehnologii:

Fisiere

Serializare

Baze de date

Probleme:

Decuplarea componentelor care fac accesul la date(specifice unei anumite tehnologii) de celelalte parti ale aplicatiei

Abstractizarea accesului la date

Transformarea datelor intre OO domain model si un model de persistenta non-OO (BD relationale, fisiere diferite structuri)

Persistenta datelor

  • Multe aplicatii doresc sa asigure persistenta datelor


Baze de date review bd

Baze de date(review BD)

  • Baza de date: reprezinta o modalitate de stocare a datelor, astfel incat acestea sa poata fi regasite

  • Baza de date relationala (Relational Database):

    • Informatiile sunt organizate in una sau mai multe tabele (Table)

    • O tabela are un nume si una sau mai multe coloane (Columns)

    • Fiecare coloana are un nume si un tip de date

    • Fiecare data elementara este stocata pe o linie (Row) din taabela

    • O tabela = o relatie = o colectie de obiecte de acelasi tip (liniile)


Exemplu

Exemplu

Database: “Company”

Table: “Employees”

Table: “Cars”


Exemplu1

Exemplu

Table Employees


Exemplu2

Exemplu

Table Cars


Reguli de integritate

Reguli de integritate

  • Intr-o tabela nu pot exista 2 linii identice (DBMS verifica aceasta)

  • Valorile unei coloane nu pot fi tablouri/colectii de valori

  • Valori nule: valoare speciala prin care se marcheaza o valoare lipsa (nu e zero sau blanc !)

  • Cheie primara (primary key): una sau mai multe coloane care identifica (diferentiaza) liniile. Coloanele care fac parte din cheia primara nu pot avea valori nule

  • Cheie straina (foreign key):

    • Exemplu: Car_Number: primary key in table Cars, foreign key in table Employees


Database management systems

Sisteme de gestiune a bazelor de date (Data Base Management System – DBMS)

DataBase Management Systems

DBMS

DB Query Language

DBMS Engine

Database files


Jdbc java relational databases

JDBC: Java <-> Relational Databases

DBMS

SQL

DB Query Language

Java Application

DBMS Engine

JDBC API

JDBC Driver

Database files


Jdbc java database connectivity

JDBC(Java Database Connectivity) (?)


Persistenta datelor

JDBC

Bibliografie:

Sun tutorial on JDBC:

http://download.oracle.com/javase/tutorial/jdbc/index.html

Laborator: Java DB = Sun’s supported distribution of the open source Apache Derby database

http://developers.sun.com/javadb/

Exemple cod (folosind JDBC cu Derby Embedded): http://www.cs.utt.ro/~ioana/arhit/exemple_jdbc.html

Apache Derby tutorial: http://db.apache.org/derby/papers/DerbyTut/index.html


Ce este jdbc

Ce este JDBC

  • JDBC: o tehnologie care permite programelor Java programs sa interactioneze cu baze de date relationale (folosind limbajul de interogare standard SQL).

  • Folosind JDBC, un program poate:

    • Stabili conexiuni cu diferite sisteme de baze de date

    • Executa comenzi (SQL) pentru a crea, actualiza, manipula datele si a receptiona rezultate

    • Inspecta si manuipula meta-datele bazei de date

  • JDBC este doar un API care permite programului sa interactioneze cu un sistem de gestiune a bazelor de date – acesta (un server de baze de date) trebuie sa existe separat !

    • Java DB (fost Apache Derby)

    • MySQL


Sql prezentare generala

SQL: Prezentare generala

  • SQL (Structured Query Language): un limbaj standard de interogare a bazelor de date

  • Categorii de comenzi SQL:

    • Data manipulation – realizeaza adaugarea, regasirea, modificarea sau stergerea datelor; aceste comenzi utilizeaza baza de date.

      • SELECT, UPDATE, DELETE, INSERT

    • Data definition – realizeaza crearea, configurarea sau stergerea tabelelor, bazelor de date, views, sau indexes; aceste comenzi administreaza baza de date.

      • CREATE, DROP


Sql comanda select

SQL: Comanda SELECT

  • Comanda SELECT este folosita pentru interogari (queries); selecteaza valorile de pe coloanele specificate pentru toate liniile care corespund criteriului specificat de clauza WHERE

  • Exemple:

    SELECT First_Name, Last_Name FROM Employees WHERE Car_Number IS NOT NULL

    SELECT * FROM Employees


Sql clauza where

SQL: Clauza WHERE

  • Clauza WHERE poate pune conditii privitoare la valorile anumitor coloane

  • Clauza WHERE poate insoti comenzile SELECT, UPDATE, DELETE

  • Exemple:

    SELECT First_Name, Last_Name FROM Employees WHERE Last_Name LIKE 'Washington%'

    SELECT First_Name, Last_Name FROM Employees WHERE Last_Name LIKE ‘Ba_man‘

    SELECT First_Name, Last_Name FROM Employees WHERE Employee_Number < 10100 and Car_Number IS NULL


Sql join

SQL: Join

  • Se refera la posibilitatea de a regasi date din mai mult de o tabela la o interogare

  • Exemplu:

  • Gaseste angajatii care au masini de servici si afiseaza numele angajatului (din tabela employees), numarul masinii, marca, modelul si anul fabricatiei (din tabela cars)

  • SELECT Employees.First_Name, Employees.Last_Name, Cars.Make, Cars.Model, Cars.Year FROM Employees, Cars WHERE Employees.Car_Number = Cars.Car_Number


Sql comanda insert

SQL: Comanda INSERT

  • Comanda INSERT se utilizeaza pentru a adauga noi linii de date intr-un tabel

  • Exemple

    insert into SUPPLIERS values(49, 'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460');

    insert into COFFEES values('Colombian', 00101, 7.99, 0, 0);


Sql comanda update

SQL: Comanda UPDATE

  • Comanda UPDATE se foloseste pentru a modifica valori din tabele

  • Exemple:

    UPDATE suppliers SET zip = ‘99999’ WHERE zip = ‘95460’

    UPDATE coffees SET price = 9.99WHERE price<=5.0


Sql comanda delete

SQL:Comanda DELETE

  • Comanda DELETE se foloseste pentru a sterge linii din tabele

    DELETE FROM coffees WHERE price > 100


Sql comanda create

SQL: Comanda CREATE

create table SUPPLIERS (

SUP_ID integer NOT NULL, SUP_NAME varchar(40) NOT NULL, STREET varchar(40) NOT NULL, CITY varchar(20) NOT NULL, STATE char(2) NOT NULL, ZIP char(5), PRIMARY KEY (SUP_ID));

create table COFFEES (

COF_NAME varchar(32) NOT NULL, SUP_ID int NOT NULL, PRICE numeric(10,2) NOT NULL, SALES integer NOT NULL, TOTAL integer NOT NULL, PRIMARY KEY (COF_NAME), FOREIGN KEY (SUP_ID) REFERENCES SUPPLIERS (SUP_ID));


Arhitectura aplicatiilor jdbc

Arhitectura aplicatiilor JDBC

  • API-ul JDBC suporta atat arhitecturi 2-tiers si 3-tiers

From: http://download.oracle.com/javase/tutorial/jdbc/overview/index.html


Arhitectura de baza jdbc

Arhitectura de baza JDBC

  • JDBC Driver: asigura comunicatia cu un anumit server de baze de date, cu particularitatile lui

  • JDBC Driver poate fi realizat dupa mai multe tipuri

Java Application

Application

Java Core

APIs

JDBC API

Database

Server

JDBC Driver

JVM

Database


Tipuri de drivere jdbc 1

Tipuri de drivere JDBC (1)

Type 1:ODBCBridge

Java

App

JDBC

API

JDBCBridge

Driver

MSODBC

Database

Server

Database

Type 1 JDBC drivers: ODBC Bridge: Drivers that implement the JDBC API as a mapping to another data access API, such as ODBC (Open Database Connectivity). Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge is an example of a Type 1 driver.


Tipuri de drivere jdbc 2

Tipuri de drivere JDBC (2)

Java

App

JDBC

API

JDBC

Driver

Type 2:NativeDB API

Native APILibrary

NativeAPI

Database

Type 2 JDBC drivers: Native DB API:Drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited. Oracle's OCI (Oracle Call Interface) client-side driver is an example of a Type 2 driver.


Tipuri de drivere jdbc 3

Tipuri de drivere JDBC (3)

Type 3:Pure

Java

Java

App

JDBC

API

JDBCDriver

Database

Translator

Database

Server

Database

Type 3 JDBC drivers: Pure Java: Drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client's requests to the data source.


Tipuri de drivere jdbc 4

Tipuri de drivere JDBC (4)

Type 4:PureJava

Direct

Java

App

JDBC

API

JDBCDriver

Database

Server

Database

Type 4 JDBC drivers: Pure Java Direct: Drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source.

Java DB (Derby) comes with two Type 4 drivers, an Embedded driver and a Network Client Driver. MySQL Connector/J is a Type 4 driver.


Embedded derby network derby

Embedded Derby - Network Derby

The Derby engine does not run in a separate process, and there are no separate database processes to start up and shut down.

The Derby database engine runs inside the same Java Virtual Machine (JVM) as the application.

An application can also access a Derby database using the client/server mode. This is achieved via a framework (Derby Network Server) that embeds Derby and handles database requests from applications, including applications running in different JVMs on the same machine or on remote machines.


Structura generala a unui program utilizand jdbc

Structura generala a unui program utilizand JDBC

  • Secventa tipica de operatii pentru interactiunea cu o baza de date via JDBC:

    • Incarca driver-ul JDBC potrivit sistemului de baze de date utilizat (prin classname)

    • Deschide o conexiune (Connection) catre baza de date (folosind URL)

    • Creaza instructiuni (Statement) (folosind Connection-ul care a fost deschis)

    • Executa instructiunile

    • Eventual proceseaza rezultatele, daca exista (printr-un ResultSet returnat)

    • Inchide Connection

http://download.oracle.com/javase/tutorial/jdbc/basics/index.html


Establishing a connection

Establishing a Connection

  • First, you need to establish a connection with the data source you want to use.

    • A data source can be: a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver.

  • DriverManager:

    • This fully implemented class connects an application to a data source, which is specified by a database URL.

    • When this class first attempts to establish a connection, it automatically loads any JDBC 4.0 drivers found within the class path. Note that your application must manually load any JDBC drivers prior to version 4.0.

    • Connecting to your DBMS with the DriverManager class involves calling the method DriverManager.getConnection.

    • The method DriverManager.getConnection establishes a database connection. This method requires a database URL, which varies depending on your DBMS


Specifying connection url s

Specifying Connection URL’s

  • A database connection URL is a string that your DBMS JDBC driver uses to connect to a database.

  • It can contain information such as:

    • where to search for the database

    • the name of the database to connect to

    • configuration properties.

  • The exact syntax of a database connection URL is specified by your DBMS !

  • See examples for Java DB and MySQL


Java db database connection urls

Java DB Database Connection URLs

Java DB Database Connection URLs format:

  • jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]*

  • subsubprotocol specifies where Java DB should search for the database, either in a directory, in memory, in a class path, or in a JAR file. It is typically omitted.

  • databaseName is the name of the database to connect to.

  • attribute=value represents an optional, semicolon-separated list of attributes. These attributes enable you to instruct Java DB to perform various tasks, including the following:

    • Create the database specified in the connection URL.

    • Encrypt the database specified in the connection URL.

    • Specify directories to store logging and trace information.

    • Specify a user name and password to connect to the database.

  • Examples:

    • jdbc:derby:testdb;create=true


Mysql database connection urls

MySQL Database Connection URLs

MySQL Connector/J Database URL syntax:

  • jdbc:mysql://[host][,failoverhost...][:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...

  • host:port is the host name and port number of the computer hosting your database. If not specified, the default values of host and port are 127.0.0.1 and 3306, respectively.

  • database is the name of the database to connect to. If not specified, a connection is made with no default database.

  • failover is the name of a standby database (MySQL Connector/J supports failover).

  • propertyName=propertyValue represents an optional, ampersand-separated list of properties. These attributes enable you to instruct MySQL Connector/J to perform various tasks.

  • Example:

    • jdbc:mysql://localhost:3306/


Establishing connection example

Establishing Connection Example

Connection conn = null;

Properties connectionProps = new Properties();

connectionProps.put("user", this.userName);

connectionProps.put("password", this.password);

if (this.dbms.equals("mysql")) {

conn = DriverManager. getConnection("jdbc:" + this.dbms +

"://" + this.serverName + ":" + this.portNumber + "/",

connectionProps);

}

else if (this.dbms.equals("derby")) {

conn = DriverManager. getConnection("jdbc:" + this.dbms + ":" +

this.dbName + ";create=true", connectionProps);

}

System.out.println("Connected to database");

From JDBCTutorialUtilities :

http://download.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html


The jdbc connection class

The JDBC Connection Class

  • There are many methods a program can call on its valid Connection object.

    • The createStatement() method will create a Statement object that can be used to assemble and run SQL commands. The preparedStatement() creates an object that is associated with a predefined SQL command (the Statement object can be used for arbitrary statements – and can be reused for other SQL commands)

    • The getMetaData() method will return metadata associated with the database, including descriptions of all of the tables in the DB.

    • The prepareCall() method is used to call stored procedures in the SQL database.


Creating statements

Creating Statements

  • A Statement is an interface that represents a SQL statement.

  • You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set.

  • You need a Connection object to create a Statement object.

  • There are three different kinds of statements:

    • Statement: Used to implement simple SQL statements with no parameters.

    • PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters.

    • CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters.

  • Example:

    Connection con;

    Statement stmt;

    stmt=con.getStatement();


Executing statements queries

Executing Statements/Queries

  • To execute a query, call an execute method from Statement such as the following:

    • executeQuery: Returns one ResultSet object.

    • executeUpdate: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.

  • ResultSet: A ResultSet is like a database table; it has zero or more rows (zero if no data elements match the query criteria) and each row has one or more columns

  • Example:

    String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; ”;

    ResultSet rs = stmt.executeQuery(query);


Processing resultsets

Processing ResultSets

  • You access the data in a ResultSet object through a cursor. This cursor is a pointer that points to one row of data in the ResultSet object.

  • Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.

  • For example, the method ResultSet.next moves the cursor forward by one row

  • There are multiple methods of extracting data from the current row in a ResultSet.

    • The getString() method returns the value of a particular column in the current row as a String.

    • The getInt() method returns the value of a particular column in the current row as an int.

    • The getBoolean() method returns the value of a particular column in the current row as an boolean.

    • The getDouble() method returns the value of a particular column in the current row as an double.

    • The getObject() method returns the value of a particular column in the current row as an Object.


Resultset example

ResultSet Example

String query = "select COF_NAME, SUP_ID, PRICE, SALES, TOTAL from COFFEES"; ”;

stmt = con.createStatement();

ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {

String coffeeName = rs.getString("COF_NAME");

int supplierID = rs.getInt("SUP_ID");

float price = rs.getFloat("PRICE");

int sales = rs.getInt("SALES");

int total = rs.getInt("TOTAL");

System.out.println(coffeeName + "\t" + supplierID + "\t" + price + "\t" + sales + "\t" + total);


Populating tables example

Populating Tables Example

stmt = con.createStatement();

stmt.executeUpdate("insert into COFFEES” +

"values('Colombian', 00101, 7.99, 0, 0)");

stmt.executeUpdate("insert into COFFEES“ +

“values('French_Roast', 00049, 8.99, 0, 0)");


Creating tables example

Creating Tables Example

String createString = "create table " + dbName + ".COFFEES " +

"(COF_NAME varchar(32) NOT NULL, " +

"SUP_ID int NOT NULL, " +

"PRICE float NOT NULL, " +

"SALES integer NOT NULL, " +

"TOTAL integer NOT NULL, " +

"PRIMARY KEY (COF_NAME), " +

"FOREIGN KEY (SUP_ID) REFERENCES " + dbName +

".SUPPLIERS (SUP_ID))";

Statement stmt = con.createStatement();

stmt.executeUpdate(createString);


Jdbc statements types

JDBC Statements - Types

  • There are three types of statement objects in JDBC programming:

    • Statement - This allows the execution of arbitrary statements on the database.

    • PreparedStatement - This kind of statement allows the program to execute the same SQL command repeatedly, while allowing substitutions for particular words or values (input parameters).

    • CallableStatement - This kind of statement allows the program to execute SQL stored procedures, with substitutions for arguments. A stored procedure is a function that is part of, and stored inside, a SQL database


Prepared statement example

Prepared Statement Example

PreparedStatement updateSales = null;

String updateString = "update " + dbName + ".COFFEES " +

"set SALES = ? where COF_NAME = ?";

con.setAutoCommit(false);

updateSales = con.prepareStatement(updateString);

for (Map.Entry<String, Integer> e : salesForWeek.entrySet()) { updateSales.setInt(1, e.getValue().intValue());

updateSales.setString(2, e.getKey());

updateSales.executeUpdate();

con.commit(); }


Jdbc data types

JDBC Data Types

  • As SQL defines its own datatypes, it is important to understand the mapping between SQL and Java datatypes…

    SQLJava SQLJava-------------------------------------------------------------------------------BITbooleanNUMERICBigDecimalBIGINTlongREALfloatBINARYbyte[]SMALLINTshortCHARStringTIMETimeDATEDateTIMESTAMPTimestampDOUBLEdoubleTINYINTbyteFLOATfloatVARBINARYbyte[]INTEGERintVARCHARchar[]

    BLOBBlobREFRefCLOBClobSTRUCTStruct


Summary

Summary

  • JDBC is a core Java technology that supports access to relational databases and database servers.

  • JDBC uses the standard Structured Query Language (SQL) for all database interaction.

  • In order to use JDBC with a particular database, you must install a driver for the database server you are using

  • Almost all JDBC functionality is accomplished through the use of SQL statements


  • Login