Databaser

1. Databaser Arkitektur Embedded SQL Tema Persistens

2. Databasearkitektur • Basis valg (constraints): • En objektorienterettilgangsvinkel • Data gemmesi en relationel RDBMS • Spørgsmåleteriforbindelse med database arkitekturen: • Hvordankan vi på den ene side adskille implementations detaljervedr. databasen (sql’en) fraforretningskoden • OGstadigvækbrugefaciliteterne I databasen

3. Problemet Employee Stringfname; Stringlname; Stringssn; Stringadress; ……. Employeesuperssn; Department dno; Objekt model / relationel model

4. Architecture When mapping between an object model and a relational model – it has to be implemented in the application. There are 3 fundamental strategies: • Brute Force • Data Access Objects • Persistence Framework

5. Brute Force • The fundamental strategy is that the business objects access directly to the data source. • It is used when no database layer is available. • It is simple an presumes that the programmer has full control of how the business objects interact with the database. • This access is the right when the demand for access are straight forward. • Will give code with low cohesion and high coupling (difficult to maintain)

6. Data access objects • Data access objects encloses the required database logic from the business objects. • One data access class for each business object • The class consist of the code, which is necessary for accessing the database (sql code). • The advantage is that all sql code is in the data access objects (high cohesion, low coupling) • You have to write a DAO for each business class

7. Persistence framework • Seal the database access completely from you business object. • In stead of writing the logic of the database access, you define the Meta data which represents the mapping. • PF generate the necessary code which enables the business objects to become persistent. • You will have to buy a framework

8. Architecture Layered architecture, Data access objects GUI - Layer Control Layer Model Layer DB Layer

9. DB layer • Consist of : • A connection class (implemented as a singelton) • An interface for each business class (Interface) • One class for each business class

10. Connection class • Implemented using the singleton pattern • The singleton pattern ensures that only one instance of a class is created. All objects that uses an instance of that class use the same instance. • The purpose is to get a connection to the database

11. private DbConnection() // private implemented as a Singleton { String url = driver + databaseName; try{ //load af driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); } catch(Exception e){ System.out.println("Can not find the driver"); System.out.println(e.getMessage()); }//end catch try{ //connection to the database con = DriverManager.getConnection(url, userName,password); //set autocommit con.setAutoCommit(true); dma = con.getMetaData(); // get meta data System.out.println("Connection to " + dma.getURL()); System.out.println("Driver " + dma.getDriverName()); System.out.println("Database product name " + dma.getDatabaseProductName()); }//end try catch(Exception e){ System.out.println("Problems with the connection to the database"); System.out.println(e.getMessage()); }//end catch }//end constructor

12. //DBConnection cont.. //this method is used to get the instance of the connection public static DbConnection getInstance() { if (instance == null) { instance = new DbConnection(); } return instance; }

13. Interface ……… public interface IFDBEmp { // get all employees public ArrayList getAllEmployees(boolean retriveAssociation); //get one employee having the ssn public Employee findEmployee(String empssn, boolean retriveAssociation); //find one employee having the name public Employee searchEmployeeFname( String fname, boolean retriveAssociation); public Employee searchEmployeeLname( String fname, boolean retriveAssociation); public Employee searchEmployeeSsn( String fname, boolean retriveAssociation); //insert a new employee public void inserEmployee(Employee emp); //update information about an employee public int updateEmployee(Employee emp); }

14. Company Example • DB classes – • Private methods • singleWhere (is used to retrieve a singel tuple from the database) • michWhere ( is used to retrieve multiple tuples from the database) • buildquery (build the sql select-statement) • build<class> (build the object, with information from the database)

15. Databaser Tilgang til databasen fra Java

16. Database tilgang fra java • Database tilgang tilbydes af pakken sql - Vi anvender følgende klasser DriverManager Connection Statement ResultSet DatabaseMetaData ResultSetMetaData

17. Fremgangsmåde • Åben forbindelsen til databasen • Udfører SQL for at opdaterer/læse fra databasen • Opbyg et string objekt der indeholder sql statment der skal udføres • Lav et Statement objekt • Udfør statement • Behandling af resultatet • Luk forbindelsen

18. Åben forbindelsen • Load af driver // mySQL Class.forName("org.gjt.mm.mysql.Driver"); //SQL Server Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); • Skaf connection object con = DriverManager.getConnection(url,bruger,password); //SQL Server url = "jdbc:odbc:company” bruger og password som oprettet under installering Eksempel: klassen DbConnection Databasenavnet

19. Udfør SQL Opbyg SQL-statement i et String objekt: String query="SELECT * FROM Employee WHERE ssn = " + id; Lav Statement objekt: Statement stmt = con.createStatement(); stmt.setQueryTimeout(5); Udfør ResultSetresults = stmt.executeQuery(query); executeUpdate, ved delete, update og insert

20. Behandling af resultatet • Objektet opbygges på baggrund af resultatet af søgningen if( results.next() ){ emp.setFname(results.getString(2)); emp.setAdresse(results.getString(4)); } stmt.close(); results er af typen ResultSet

21. Luk forbindelsen con.close(); Eksempel: Company

22. Opsætning af Driver Inden vi kan anvende en database fra Java skal driveren være tilgængelig