name: portada
class: portada-slide, center, middle

# Persistència en BDR-BDOR-BDOO

.footnote[Mateu Yábar Valles]

---

# Problemes d'enmagatzematge en fitxers

---
# Problemes d'enmagatzematge en fitxers
- Velocitat
- Concurrencia
- ...

---
# Pregunta

Quina tecnologia ens permet enmagatzemar dades de forma estructurada, i ràpida?

---
# BD
- La majoria d'aplicacions utilitzen bases de dades per enmagatzemar l'informació.
- Necessitem poder accedir a una BD desde un programa

---
# BD
- __Postgresql__
- Mysql
- MariaDB
- __H2__
- Sqlite
- ...

---
# Driver
- Llibreria que ens permet conectar-nos a una BD
- H2
- Postgresql

---
# Llibreries
- Kotlin
  - [__Exposed__](https://github.com/JetBrains/Exposed)
  - Sqldelight
- Java
  - JDBC
  - JPA
  - Hibernate

---
# Exposed

Mireu-vos la documentació d'Exposed i comenceu a fer els exercicis.

Tips:
- Veureu que Exposed pot usar dues formes de connexió amb la BD (DSL i DAO). Per ara useu DAO
- Per connectar-vos a una BD necessitareu els drivers. Mireu la doc aquí: https://github.com/JetBrains/Exposed/wiki/DataBase-and-DataSource#datasource

---
# Exposed API - exemple1

```
object Persons: IntIdTable() {
    val name = varchar("name", 50)
    val surname = varchar("surname", 50)
    val birthYear = integer("birthYear)")
}
```

---
# Exposed API - exemple1

```
fun main() {
    Database.connect("jdbc:h2:mem:regular", "org.h2.Driver")

transaction{
        addLogger(StdOutSqlLogger)
        SchemaUtils.create (Persons)
        Persons.insert {
            it[name] = "Joan"
            it[surname] = "Pujol"
            it[birthYear] = 2001
        }

Persons.selectAll().forEach{
            println(it[name])
        }
    }
}
```

---
# Dao
- Data acces object
- Separació de la logica d'accés a dades

---
# Dao - Exemple 1

```
data class  Person(val name : String, val surname :String, val birthYear: Int)

class PersonDao{
    fun list() : List<Person> = transaction {
        Teams.selectAll().map { Person(it[name], it[surname], it[birthYear]) }
    }

fun find(id : Int) = transaction {
        Teams.select { Persons.id eq id }.map { Person(it[name], it[surname], it[birthYear]) }.firstOrNull()
    }
}
```

---
# Dao - Exemple 1

```
fun main() {
    Database.connect("jdbc:postgresql://lucky.db.elephantsql.com/lhscvhte", driver = "org.postgresql.Driver",
        user = "lhscvhte", password = "XXVwU3_8myH75iUKFj4S3ndEBuMeuTUJ")
    val personDao = PersonDao()
    transaction {
        addLogger(StdOutSqlLogger)
        SchemaUtils.create(Persons)
    }

personDao.list().forEach { println(it.name) }
}
```

---
# Dao - Exemple 2

```
class PersonDaoV2{
    fun list() : List<Person> = transaction {
        Teams.selectAll().map(::bdToModel)
    }

fun find(id : Int) = transaction {
        Teams.select { Persons.id eq id }.map(::bdToModel).firstOrNull()
    }

private fun bdToModel(resultRow: ResultRow): Person =
        Person(resultRow[name], resultRow[surname], resultRow[birthYear])
}
```

---
# Exemple projecte

https://gitlab.com/mateuyabar_at_itb/projecte-uf2-m06-m09?authuser=0

---
```
package cat.itb.mateuyabar.m06.uf2

import org.jetbrains.exposed.dao.Entity
import org.jetbrains.exposed.dao.EntityClass
import org.jetbrains.exposed.dao.IntEntity
import org.jetbrains.exposed.dao.IntEntityClass
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IdTable
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

object Persons: IdTable<String>(){
    val name = varchar("name", 100).uniqueIndex()
    val surname = varchar("surname", 100)
    override val id : Column<EntityID<String>> = name.entityId()
}
object Cars : IntIdTable(){
    val plateNumber = varchar("plateNumber", 100)
    val owner = reference("ownerId", Persons.id)
}
class Person(id: EntityID<String>) : Entity<String>(id) {
    companion object : EntityClass<String, Person>(Persons)
    var name by Persons.id
    var surname by Persons.surname
}

class Car(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Car>(Cars)
    var plateNumber by Cars.plateNumber
    var owner by Person referencedOn Cars.owner
}

fun main() {

Database.connect("jdbc:h2:mem:regular", "org.h2.Driver")
    transaction {
        addLogger(StdOutSqlLogger)
        SchemaUtils.create(Persons, Cars)

val person = Person.new("Ot") {
            surname = "Puig"
        }
        Car.new {
            plateNumber = "45"
            owner = person
        }

val persons = Person.all()
        persons.forEach {
            println(it.name.value+" "+ it.surname)
        }

Car.all().forEach {car ->
            println("plate: ${car.plateNumber}, ownerSurname: ${car.owner.surname}")
        }

}

}
```