Persistenta datelor

1. 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

2. 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)

3. Exemplu Database: “Company” Table: “Employees” Table: “Cars”

4. Exemplu Table Employees

5. Exemplu Table Cars

6. 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

7. Sisteme de gestiune a bazelor de date (Data Base Management System – DBMS) DataBase Management Systems DBMS DB Query Language DBMS Engine Database files

8. JDBC: Java <-> Relational Databases DBMS SQL DB Query Language Java Application DBMS Engine JDBC API JDBC Driver Database files

9. JDBC(Java Database Connectivity) (?)

10. 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

11. 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

12. 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

13. 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

14. 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

15. 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

16. 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);

17. 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

18. SQL:Comanda DELETE • Comanda DELETE se foloseste pentru a sterge linii din tabele DELETE FROM coffees WHERE price > 100

19. 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));

20. 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

21. 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

22. 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.

23. 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.

24. 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.

25. 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.

26. 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.

27. 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

28. 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

29. 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

30. 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

31. 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/

32. 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

33. 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.

34. 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();

35. 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);

36. 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.

37. 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);

38. 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)");

39. 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);

40. 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

41. 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(); }

42. JDBC Data Types • As SQL defines its own datatypes, it is important to understand the mapping between SQL and Java datatypes… SQL Java SQL Java---------------------------------- ---------------------------------------------BIT boolean NUMERIC BigDecimalBIGINT long REAL floatBINARY byte[] SMALLINT shortCHAR String TIME TimeDATE Date TIMESTAMP TimestampDOUBLE double TINYINT byteFLOAT float VARBINARY byte[]INTEGER int VARCHAR char[] BLOB Blob REF Ref CLOB Clob STRUCT Struct

43. 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