Accés a dades amb JDBC
Comencem a desenvolupar un projecte que anomenarem “SocIoc” (SOCial IOC) i que consisteix en una xarxa social de preguntes i respostes per a estudiants de l’IOC. La idea està basada en stackoverflow.com, una xarxa social on els desenvolupadors de programari poden fer preguntes de desenvolupament que són contestades per altres desenvolupadors. La funcionalitat és simple: els estudiants de l’IOC poden fer i contestar preguntes i votar positiva o negativament les respostes a cada pregunta. Els estudiants que rebin vots positius a les respostes aniran guanyant punts.
En aquesta unitat posem les bases de l’aprenentatge configurant una aplicació Java per establir connexions amb la BD, executar consultes SQL (Structured Query Language) i tancar la connexió. Explicarem:
- Com fer connexions a la base de dades H2 utilitzant drivers JDBC.
- Com escriure tests unitaris per comprovar que el codi funciona correctament.
- Com fer operacions de lectura, escriptura, actualització i esborrat de base de dades.
- Com preveure injeccions malicioses de codi SQL quan fem consultes a les bases de dades.
Importar un projecte de Maven a Netbeans
El projecte amb el qual treballareu el podeu trobar a l’apartat d’annexos de la unitat. Un cop descarregat el fitxer i descomprimit ja el podeu importar.
En la figura es mostra on és l’opció per obrir un projecte existent.
Navegueu per les carpetes fins on teniu la carpeta amb el projecte. Com podeu veure en la figura, Netbeans reconeixerà que és un projecte Maven.
Finalment, un cop obert el projecte, Maven començarà a descarregar les dependències de JUnit i el driver d’H2. Hauríeu de veure un projecte amb l’estructura que es mostra en la figura.
La primera connexió a una base de dades
Apache Maven permet obtenir les llibreries necessàries per al projecte que veureu a continuació, on simplement fareu una connexió a la BD en memòria H2.
En el fitxer pom.xml de Maven, les úniques dependències que us fan falta són la del driver de la BD H2 i de JUnit, que és un conjunt de llibreries que utilitzareu per escriure els tests unitaris.
<dependencies> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.4.190</version> </dependency> <!-- testing --> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies>
Un cop Maven descarrega les dependències i les afegeix al classpath, ja podeu escriure la primera classe, que simplement establirà una connexió amb una base de dades.
try { Class.forName("org.h2.Driver"); e.printStackTrace(); } return con; }
Cada driver JDBC té una classe que s’encarrega d’inicialitzar el driver quan es carrega a memòria. En el cas d’una BD H2:
Class.forName("org.h2.Driver");
Carregar el driver en memòria no és necessari, ja que des de la versió 6 de Java es fa automàticament. Això implica que com que estem utilitzant una versió de Java superioir a la 6, no fa falta que carreguem el driver explícitament. Quan, més tard, refactoritzarem el codi, podrem eliminar Class.forName(“org.h2.Driver”);.
Un cop el driver està carregat en la memòria es pot procedir a connectar amb la BD. Per obrir una connexió amb la BD s’utilitza la classe java.sql.DriverManager
.
Fixeu-vos en el paràmetre que s’ha utilitzat per establir la connexió:
jdbc:h2:mem:socioc_db
Intenteu contestar a les següents preguntes:
- Quina és l’estructura d’aquest paràmetre de connexió?
- Què representa el paràmetre
socioc_db
?
Algunes de les bases de dades compatibles amb JDBC són: DB2, H2, Informix, JavaDB/Derby, Microsoft SQL Server, Mimer SQL, MySQL, NuoDB, Oracle, PostgreSQL, SQLite, Sybase i Vertica.
La resposta està relacionada amb el disseny de JDBC i amb la seva arquitectura, que té tres components principals que podeu veure en la figura:
- Gestor de drivers (
DriverManager
): és el responsable de trobar el driver que l’aplicació necessita. Quan es sol·licita una connexió amb la BD es fa mitjançant un URL (Uniform Resource Locator), que descriu com ha de ser la connexió. - Driver JDBC: cada driver present al sistema es carrega en la màquina virtual de Java (JVM Java Virtual Machine) i es registra amb el
DriverManager
. Quan una aplicació sol·licita una connexió amb una BD, elDriverManager
s’encarrega de preguntar a cada driver present si pot connectar amb la BD amb l’URL de connexió especificat. - Base de dades compatibles amb JDBC.
L’URL de connexió amb una BD JDBC té el següent format:
jdbc:Tipus_de_base_de_dades://<Host>:<Port>/<Base_de_dades>
Per tant, la URL de connexió jdbc:h2:mem:socioc_db indica el següent:
- jdbc: indica que s’utilitzarà JDBC per fer la connexió.
- h2: és el tipus de servidor de BD amb el qual volem connectar.
- mem: en aquest cas, com que és una BD en memòria no s’ha d’especificar ni la IP ni el port on està corrent la BD.
- socioc_db: és el nom de l’esquema que s’utilitzarà.
Millorant el codi: fitxers de propietats i tests unitaris
Hi ha dues coses al codi de l’apartat 1.2 que podeu millorar. La primera és que no és necessari carregar el driver explícitament. La segona és que mai és una bona idea posar l’URL de connexió amb la BD directament a una classe. La gràcia d’utilitzar JDBC és que l’aplicació serà independent de la BD que utilitzem; si posem el valor de l’URL de connexió estarem trencant això, ja que en canviar de BD haurem de modificar la classe. Això es pot evitar creant un fitxer de propietats on es configurin els paràmetres de connexió amb la BD. El format del fitxer és PROPIETAT=VALOR, com podeu veure:
DB_DRIVER_CLASS=org.h2.Driver DB_URL=jdbc:h2:mem:socioc_db DB_USERNAME=usuari DB_PASSWORD=passwd
El fitxer de propietats s’anomena db.properties i es troba a src/main/resources. Un cop el fitxer de propietats està definit ja el podeu utilitzar per llegir les propietats de configuració.
try { if(urlResource != null){ resourceAsStream = urlResource.openStream(); props.load(resourceAsStream); props.getProperty("DB_USERNAME"), props.getProperty("DB_PASSWORD")); } e.printStackTrace(); } finally { if (resourceAsStream != null) { resourceAsStream.close(); } } return con; }
Si volguéssiu canviar la BD, l’únic que s’hauria de fer és canviar el contingut del fitxer de propietats. D’aquesta manera aconseguireu que el codi estigui feblement acoblat (loosely coupled) amb la configuració.
Un cop s’ha llegit el fitxer que conté les propietats amb el codi:
La classe java.util.Properties
permet recuperar el valor de les propietats:
props.getProperty(NOM_DE_LA_PROPIETAT)
Un cop tenim la classe, escriurem el test unitari.
public class DBConnectionTest { DBConnection dBConnection; Connection connection; @Before public void setUp(){ dBConnection = new DBConnection(); } @After connection.close(); } @Test connection = dBConnection.getConnection(); Assert.assertEquals("H2 JDBC Driver", connection.getMetaData().getDriverName()); Assert.assertEquals("SOCIOC_DB", connection.getCatalog()); } }
En aquest primer test només esteu comprovant dues coses: que el nom de l’esquema que hem definit a l’URL de connexió és SOCIOC_DB i que el driver que esteu utilitzant és el driver JDBC d’H2. El mètode assertEquals
de JUnit permet comprovar que l’objecte esperat (primer argument) és igual que el que s’obté quan s’executa el nostre codi.
Assert.assertEquals("H2 JDBC Driver", connection.getMetaData().getDriverName()); Assert.assertEquals("SOCIOC_DB", connection.getCatalog());
A Netbeans, els tests es poden executar de forma individual, és a dir, classe per classe o tots els del projecte. Tal com podeu veure en la figura, si feu clic amb el botó dret sobre una classe dins de la carpeta Test Packages podreu executar els tests per a la classe seleccionada.
Per executar tots els tests d’un projecte, primer heu de designar com a projecte principal el projecte UDF4-A1-01, tal com podeu veure en la figura. A continuació podreu executar tots els tests del projecte, tal com s’indica en la figura.
Quan executeu els tests de JUnit a Netbeans, els resultats es mostren a la finestra de resultats de la figura. Per mostrar aquesta finestra seguiu els passos mostrats en figura.
Tests unitaris amb JUnit
Els tests unitaris o proves de components són un tipus de proves de programari que consisteixen a fer proves sobre els components o unitats més petits del codi font d’una aplicació o d’un sistema. Això dóna la capacitat de verificar que les vostres funcions funcionen com s’esperava. És a dir, que per a qualsevol funció, i donat un conjunt d’entrades, podeu determinar si la funció retorna els valors adients i tracta correctament els errors.
Això ajuda a identificar les falles en els nostres algoritmes i/o lògica i ajuda a millorar la qualitat del codi que comprèn una funció determinada. Absolutament tots els components d’una aplicació han de tenir tests unitaris, fet que permet, durant qualsevol moment durant el desenvolupament, verificar la qualitat del treball.
Un segon avantatge de desenvolupar codi pensant sempre que s’ha de poder testejar és que el codi resultant és més fàcil de testejar. Com a resultat s’acaba estructurant el codi millor i es creen un major nombre de funcions més petites i especialitzades.
Un tercer avantatge de tenir un conjunt de tests unitaris sòlids és que preveuen que futurs canvis al codi trenquin la funcionalitat. Si en fer un canvi i executar els tests hi ha un error és clar que els canvis han introduït un error a la part específica que el test unitari està comprovant. Finalment, els tests unitaris proporcionen la millor documentació del sistema, ja que reflecteix exactament què s’espera que faci el codi. Els desenvolupadors que vulguin aprendre quina funcionalitat proporciona cada un dels components del sistema només han de llegir els tests unitaris.
El 'framework' de test JUnit
JUnit en la versió 4.x és un framework de test que utilitza anotacions per identificar els mètodes que especifiquen un test. Un test unitari és un mètode que s’especifica en una classe que només s’utilitza per al test. Això s’anomena classe de test. Per definir un mètode de test amb el framework JUnit 4.x es fa amb l’anotació @org.junit.Test
. En aquest mètode s’utilitza un mètode d’asserció en el qual es comprova el resultat esperat de l’execució de codi en comparació del resultat real.
Vegeu amb detall el test unitari del punt 1.3:
public class DBConnectionTest { DBConnection dBConnection; Connection connection; @Before public void setUp(){ dBConnection = new DBConnection(); } @After connection.close(); } @Test connection = dBConnection.getConnection(); Assert.assertEquals("H2 JDBC Driver", connection.getMetaData().getDriverName()); Assert.assertEquals("SOCIOC_DB", connection.getCatalog()); } }
A la línies 5-9 hi ha el següent codi:
@Before public void setUp(){ dBConnection = new DBConnection(); }
L’anotació @Before
serveix per marcar una funció que s’executarà abans de l’execució de cada test i serveix per assegurar que tots els tests sempre parteixen de les mateixes condicions per ser executats. De manera similar, les línies 10-13:
@After connection.close(); }
Sempre s’executaran en acabar cada un dels tests unitaris. En el vostre cas us assegurareu que en iniciar un test s’establirà connexió amb la base de dades, i en acabar es tancarà la connexió.
Cobertura dels tests unitaris
Una cosa fonamental és saber quina part del vostre codi està cobert pels tests unitaris. Idealment s’ha d’intentar aconseguir un 100%, però normalment es considera com a acceptable una cobertura de 80-90%. La versió 7 de Netbeans ja té incorporat un plugin que permet veure la cobertura de projectes Maven (MavenCodeCoverage
). Afegiu el següent al final del fitxer pom.xml.
<plugin> <groupId>org.jacoco</groupId> <artifactId>jacoco-maven-plugin</artifactId> <version>0.7.5.201505241946</version> <executions> <execution> <goals> <goal>prepare-agent</goal> </goals> </execution> <execution> <id>report</id> <phase>prepare-package</phase> <goals> <goal>report</goal> </goals> </execution> </executions> </plugin>
A continuació només fa falta dir a Netbeans que torni a carregar el pom.xml, com podeu veure en la figura.
Un cop fet això ja podeu accedir a la cobertura dels tests unitaris que teniu al projecte, tal com podeu veure en la figura i seleccionant Show report.
Inicialment no hi ha informació, així que haureu d’executar tots els tests prenent el botó Run All Tests (vegeu la figura).
Per veure el resultat heu de tancar i tornar a obrir la pestanya on es mostren els resultats (Code Coverage report).
Un cop el vostre test s’ha executat podeu veure que tenim una cobertura d’un 88.89% (vegeu la figura).
Si feu clic a la classe que hem testejat (DBConnection
) podeu veure les parts del codi que estan testejades i les que no (vegeu la figura).
Podeu veure que la part de codi sense cobertura dels nostres tests és la que fa referència a com tractar les excepcions.
Augmentant la cobertura del codi
El vostre següent objectiu és aconseguir un 100% de cobertura als tests unitaris. Per aconseguir això heu de canviar el codi, ja que heu de ser capaços de poder crear de manera controlada errors i assegurar-vos que es tracten correctament.
El codi del qual partireu el teniu disponible a l’apartat d’annexos de la unitat.
En el vostre cas, una forma fàcil de testejar les excepcions és, per exemple, crear un fitxer de propietats que contingui un nom de driver de base de dades incorrecte. Creeu el següent fitxer als recursos de test (figura) amb el contingut següent i amb el nom db_wrong_driver.properties:
#BD en memoria H2 DB_DRIVER_CLASS=org.h2.WrongDriver DB_URL=jdbc:h2:mem:socioc_db DB_USERNAME=usuari DB_PASSWORD=passws
En aquest exemple, la classe org.h2.WrongDriver
no existeix, fet que provocarà un error quan s’intenti carregar el driver en memòria amb Class.forName(props.getProperty(“DB_DRIVER_CLASS”));
.
Això presenta un problema: a la classe DBConnection
no hi ha manera de seleccionar quin fitxer de propietats s’ha d’utilitzar. El primer que haureu de fer, doncs, és refactoritzar el mètode getConnection
perquè accepti un paràmetre que us serveixi per passar a quin fitxer de propietats utilitzar.
Refactorització és el procés de reestructurar el codi d’una aplicació sense canviar la seva funcionalitat per tal de millorar la seva eficiència, estructura, llegibilitat o reutilització.
try { if (urlResource != null) { resourceAsStream = urlResource.openStream(); props.load(resourceAsStream); Class.forName(props.getProperty("DB_DRIVER_CLASS")); props.getProperty("DB_USERNAME"), props.getProperty("DB_PASSWORD")); } e.printStackTrace(); } finally { if (resourceAsStream != null) { resourceAsStream.close(); } } return con; }
Vegeu aquí un dels avantatges de fer tests unitaris, el fet de pensar com testejar el codi millora el diseny de la nostra API. En aquest cas, afegir aquest paràmetre us permetrà utilitzar el mateix codi amb diferents bases de dades, ja que l’únic que farà falta serà passar el fitxer de propietats adient. Ara afegireu un test que carregui el fitxer amb la configuració errònia:
@Test connection = dBConnection.getConnection("db_wrong_driver.properties"); Assert.assertNull(connection); }
Com que l’objecte Connection
pot ser ara null, s’ha de canviar el que es fa després d’executar el test.
@After if(connection != null){ connection.close(); } }
Comproveu vosaltres mateixos que ara tenim una cobertura dels tests unitaris del 100%.
Fent consultes a la base de dades
A continuació començareu a treballar amb la base de dades que utilitzareu per construir l’aplicació que anomenareu “SocIoc”. La idea general és desenvolupar una xarxa social on els estudiants pugueu preguntar i resoldre preguntes relacionades amb les assignatures que esteu cursant. Les millors respostes es votaran i anireu guanyant punts i creant-vos una reputació. Començareu per obtenir un llistat dels alumnes que hi ha al sistema.
Intenteu contestar a les següents preguntes:
- Quines classes haurem d’afegir al sistema?
- Com obtindrem les dades de la base de dades?
Les bases de dades s’estructuren en taules que tenen una sèrie de registres amb informació. Quan vulgueu extraure una part d’aquesta informació fareu una consulta que us retornarà els registres adients. La vostra aplicació no entén de registres de bases de dades, i necessitareu expressar la informació que voleu guardar a la base de dades com una entitat que pugueu utilitzar a l’aplicació. Llavors, el primer que fareu serà definir una classe que representi un usuari del sistema. Com podeu veure en la figura, teniu un paquet org.iow.daw
que és molt general, així que ara que afegireu més classes és necessari que comenceu a estructurar les classes en diferents paquets.
Hi ha moltes formes de fer aquesta organització, i no n’hi cap de correcta o incorrecta mentre siguin consistents i ben organitzades. La millor estructura és organitzar els paquets en funció de les entitats de domini que hi hagi a la nostra aplicació. Per exemple, el paquet org.ioc.daw.user
contindrà totes les classes amb funcionalitat relacionada amb els usuaris. Creeu aquest paquet i el paquet org.ioc.daw.db
tal com es mostra en la figura i en la figura.
A partir del codi de partida del present apartat, creeu la classe User
amb el contingut que es mostra a continuació i moveu la classe DBConnection
al paquet org.ioc.daw.db
.
Trobareu el codi de partida per al present apartat als annexos de la unitat.
public class User { private int userId; private int rank; private boolean active; this.userId = userId; this.username = username; this.name = name; this.email = email; this.rank = rank; this.createdOn = createdOn; this.active = active; } public int getUserId() { return userId; } return username; } return name; } return email; } public int getRank() { return rank; } return createdOn; } public boolean isActive() { return active; } }
Com que aquesta classe no té cap lògica, no fa falta escriure un test unitari. Seguidament definireu una classe que s’encarregui de fer les operacions amb la base de dades. Normalment, a aquest tipus de classes se les anomena Data Access Objects (DAO, objectes d’accés a dades), per la qual cosa anomenareu la vostra classe UserDAO
.
public class UserDAO { public List<User> findAllUsers() { DBConnection dBConnection = new DBConnection(); List<User> users = new ArrayList<>(); try ( while (rs.next()) { int userId = rs.getInt("user_id"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); User user = new User(userId, username, name, email, rank, timestamp, active); users.add(user); } e.printStackTrace(); } return users; } }
Una de les operacions fetes més comunament contra una base de dades és una consulta. Fer consultes de bases de dades utilitzant JDBC és bastant fàcil, encara que hi ha una mica de codi repetitiu que s’ha d’utilitzar cada vegada que s’executa una consulta. En primer lloc, es necessita obtenir un objecte de connexió amb la BD. Després es crea una una consulta i es guarda a una variable de tipus string. La línia 3 defineix la consulta que es farà a la base de dades. En aquesta estem demanant a la BD que retorni tots els registres de la taula “Users”.
SELECT user_id, username, name, email, rank, active, created_on FROM users;
A continuació s’utilitza una clàusula try-with-resources
per crear els objectes que són necessaris per fer la consulta de la base de dades. Això farà que si hi ha qualsevol problema i el programa llença una excepció, tots els recursos es tancaran automàticament.
try (
En aquest cas, si hi ha cap problema creant la connexió amb la BD, creant o executant la consulta, tancarem automàticament la connexió amb la BD.
Com es pot veure, el mètode executeQuery
accepta un string i retorna un objecte ResultSet
. L’objecte ResultSet
fa que sigui fàcil treballar amb els resultats de la consulta. Si observeu la següent línia de codi (línia 9), un bucle while es crea amb rs.next()
. Aquest bucle recorrerà els continguts de l’objecte ResultSet
, obtenint la següent fila que es retorna des de la consulta amb cada iteració. Una vegada totes les files retornades han estat processades, rs.next()
retornarà false per indicar que no hi ha més resultats que processar.
Dins del bucle while, l’objecte ResultSet
s’utilitza per obtenir els valors dels noms de les columnes indicades amb cada passada. Observeu que si s’espera que la columna retorni un string heu d’utilitzar el mètode ResultSet.getString
, passant el nom de la columna en format string. De la mateixa manera, si s’espera que la columna retorni un int haureu d’utilitzar el mètode ResultSet.getInt
. Un cop tenim tota la informació necessària, es creen els objectes de tipus User
i s’afegeixen a la llista de tots els usuaris (línies 18-19).
A continuació necessiteu fer un test unitari per assegurar-vos que la classe UserDAO
té el funcionament desitjat. Teniu un problema, però: el fitxer db.properties conté les dades d’accés a la BD principal o de producció. Per fer els tests voleu poder configurar la BD que utilitzareu, ja que això permetrà usar una BD específica per executar-los. Refactoritzareu el codi per tal que l’objecte DBConnection
no es creï dintre de la classe UserDAO
, sinó que es passi com a paràmetre. D’aquesta forma aconseguireu el desacoblament de la classe que fa consultes a la BD amb la qual s’encarrega de fer la connexió.
public class UserDAO { private DBConnection dBConnection; public UserDAO(DBConnection dBConnection){ this.dBConnection = dBConnection; } public List<User> findAllUsers() { List<User> users = new ArrayList<>(); try ( while (rs.next()) { int userId = rs.getInt("user_id"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); User user = new User(userId, username, name, email, rank, timestamp, active); users.add(user); } e.printStackTrace(); } return users; } public getDBConnection(){ this.dBConnection = dBConnection; } }
Una forma de fer això és declarar DBConnection dBConnection
com un atribut de la classe UserDAO
i fer que sigui necessari per a la creació de l’objecte.
public class UserDAO { private DBConnection dBConnection; public UserDAO(DBConnection dBConnection){ this.dBConnection = dBConnection; }
A la classe DBConnection
, el fitxer amb els paràmetres de connexió amb la base de dades és imprescindible, així que també refactoritzareu aquesta classe.
public class DBConnection { this.connectionFile = connectionFile; }
Aquest últim canvi trencarà el test unitari, així que haureu de canviar la classe DBConnectionTest
.
Ara ja podeu crear el test unitari per testejar la classe UserDAO
.
public class UserDAOTest { private DBConnection dBConnection; @Before public void setUp(){ dBConnection = new DBConnection(connectionProperties); } @Test public void findAllUsers(){ UserDAO userDAO = new UserDAO(dBConnection); List<User> users = userDAO.findAllUsers(); Assert.assertEquals("Hauriem de tenir 2 usuaris a la base de dades", 2, users.size()); } }
Abans de poder executar el test, creareu el fitxer de connexió amb la BD de test.
DB_DRIVER_CLASS=org.h2.Driver DB_URL=jdbc:h2:mem:socioc_db;INIT=runscript from 'classpath:init.sql'; DB_USERNAME=usuari DB_PASSWORD=passwd
Com que en aquest cas és una BD en memòria, no necessiteu canviar el nom de la BD. La part més important d’aquest fitxer és la segona línia, que executarà una sèrie d’instruccions SQL que asseguraran que la BD de test sempre estarà en el mateix estat. En la figura es mostren els fitxers que heu de tenir abans d’executar els tests unitaris.
CREATE TABLE users(user_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, username VARCHAR(10) NOT NULL, name VARCHAR(20) NOT NULL, email VARCHAR(50) NOT NULL, rank INT DEFAULT 0, active BOOLEAN DEFAULT TRUE, created_on TIMESTAMP AS CURRENT_TIMESTAMP NOT NULL); INSERT INTO users (username, name, email) VALUES ('user1', 'John Test', 'john@email.com'); INSERT INTO users (username, name, email) VALUES ('user2', 'Paul Test', 'paul@email.com');
Operacions CRUD
CRUD correspon a l’acrònim Create Read Update Delete: crear, recuperar , actualitzar i eliminar, i es refereix a les quatre funcions principals que implementareu quan desenvolupeu aplicacions de bases de dades. Les funcions CRUD són les que donen la capacitat a les aplicacions de comportar-se d’una manera dinàmica, ja que les dades podran ser canviades pels usuaris. Aquests podran crear, visualitzar, modificar i alterar les dades. Les operacions CRUD permeten accedir i manipular les entitats definides a les bases de dades.
Per exemple, a la taula d’usuaris definida a l’apartat 1.5, Create implicarà afegir un nou usuari; Read, accedir a les dades d’un o diversos estudiants; Update modificarà les dades dels usuaris i Delete eliminarà un o diversos registres de la base de dades.
El primer que farem serà modificar la classe UserDAO
per permetre recuperar les dades de la base de dades. Recordeu que l’objectiu és construir una aplicació que permeti preguntar i resoldre preguntes relacionades amb les assignatures que esteu cursant. Les millors respostes es votaran i anireu guanyant punts i creant-vos una reputació. Quines creieu, doncs, que seran el tipus d’informació que necessitareu extreure de la base de dades? Intenteu contestar a les següents preguntes:
- En quines situacions necessitareu obtenir la informació d’un usuari?
- Per mostrar un llistat amb els alumnes que mostri un ranking dels alumnes més ben classificats, quina informació necessitareu?
Les operacions de lectura que haureu de dissenyar per a la vostra aplicació estaran totalment determinades per la seva funcionalitat. Tot i així, veureu amb la vostra experiència que hi haurà unes operacions que es repetiran i seran comuns per a moltes aplicacions. Per exemple, en el cas que us ocupa, una aplicació amb usuaris que es poden registrar i donar-se de baixa, moltes de les operacions que definim serviran per a la majoria d’aplicacions que us trobareu, des d’una botiga amb comerç electrònic, un joc online o una xarxa social.
Operacions de lectura
Primer de tot, quan un usuari es registri en el sistema haurà d’introduir el seu nom, el nom d’usuari que desitja i el seu correu electrònic. Què passa si el nom d’usuari ja està en ús per part d’un altre usuari? I el correu electrònic? En aquests casos hauríem d’avisar l’usuari que ha d’escollir un altre nom d’usuari o correu electrònic. Ja tenim dues operacions de lectura que haurem d’implementar: trobar usuaris a partir del correu electrònic i a partir del nom d’usuari. Implementem-les:
User user = null; try ( while (rs.next()) { int userId = rs.getInt("user_id"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); user = new User(userId, username, name, email, rank, timestamp, active); } e.printStackTrace(); } return user;
La part més important és on es defineix la consulta que es farà a la base de dades:
Aquesta és la consulta SQL que retornarà un usuari a partir del seu correu electrònic. Si no hi ha cap usuari registrat amb el correu electrònic retornarà null. Això es pot expressar amb el següent test unitari:
@Test public void findUserByEmail(){ UserDAO userDAO = new UserDAO(dBConnection); User user = userDAO.findUserByEmail(existingEmail); Assert.assertNotNull(user); user = userDAO.findUserByEmail(unknownEmail); Assert.assertNull(user); }
Assert.assertNotNull
verifica que un objecte no és null, és a dir, s’ha trobat un resultat a la base de dades. Al contrari, Assert.assertNull
verifica que un objecte és null; en el vostre cas, que no s’ha trobat cap usuari a la base de dades amb un cert correu electrònic.
A continuació implementareu un mètode que us permeti trobar usuaris a partir del nom d’usuari.
User user = null; try ( while (rs.next()) { int userId = rs.getInt("user_id"); username = rs.getString("username"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); user = new User(userId, username, name, email, rank, timestamp, active); } e.printStackTrace(); } return user; }
I el test unitari:
@Test public void findUserByUsername(){ UserDAO userDAO = new UserDAO(dBConnection); User user = userDAO.findUserByUsername(existingUsername); Assert.assertNotNull(user); user = userDAO.findUserByUsername(unknownUsername); Assert.assertNull(user); }
Doneu una ullada a la classe sencera, ja que hi ha codi repetit; símptoma inequívoc que és hora de refactoritzar el codi.
public class UserDAO { private DBConnection dBConnection; public UserDAO(DBConnection dBConnection){ this.dBConnection = dBConnection; } public List<User> findAllUsers() { List<User> users = new ArrayList<>(); try ( while (rs.next()) { int userId = rs.getInt("user_id"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); User user = new User(userId, username, name, email, rank, timestamp, active); users.add(user); } e.printStackTrace(); } return users; } User user = null; try ( while (rs.next()) { int userId = rs.getInt("user_id"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); user = new User(userId, username, name, email, rank, timestamp, active); } e.printStackTrace(); } return user; } User user = null; try ( while (rs.next()) { int userId = rs.getInt("user_id"); username = rs.getString("username"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); user = new User(userId, username, name, email, rank, timestamp, active); } e.printStackTrace(); } return user; } }
Si us hi fixeu, la part del codi que s’encarrega de fer la connexió amb la base de dades i extreure els resultats es repeteix, millor posar-la a un mètode. Per què? Imagineu la següent situació: més endavant, durant el desenvolupament de l’aplicació, volem afegir un altre atribut a la classe User
, com per exemple la data de naixement. Tal com tenim el codi ara mateix, fer aquest canvi implicarà canviar tres mètodes: findAllUsers
, findUserByEmail
i findUserByUsername
. Si aquest codi comú el poseu en un mètode separat, el canvi només l’haureu de fer en un lloc.
public class UserDAO { private DBConnection dBConnection; public UserDAO(DBConnection dBConnection){ this.dBConnection = dBConnection; } public List<User> findAllUsers() { List<User> users = new ArrayList<>(); try ( while (rs.next()) { User user = buildUserFromResultSet(rs); users.add(user); } e.printStackTrace(); } return users; } User user = null; try ( while (rs.next()) { user = buildUserFromResultSet(rs); } e.printStackTrace(); } return user; } User user = null; try ( while (rs.next()) { user = buildUserFromResultSet(rs); } e.printStackTrace(); } return user; } int userId = rs.getInt("user_id"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); User user = new User(userId, username, name, email, rank, timestamp, active); return user; } }
Hem creat la funció buildUserFromResultSet
, que s’encarrega de construir un objecte usuari a partir del resultat d’executar la consulta SQL. Un dels avantatges de tenir tests unitaris és que podeu assegurar-vos que la refactorització no ha trencat la funcionalitat del vostre codi. Si correu els tests a la classe UserDAOTest
ha de continuar passant.
Encara es pot refactoritzar més, ja que totes les funcions tenen la mateixa estructura:
- Establir connexió amb la base de dades
- Executar una consulta
- Iterar sobre resultats i construir un o diversos objectes
User
L’única diferència és que hi haurà funcions que retornaran només un usuari o diversos. Això ho podem solucionar creant les següents funcions:
List<User> users = executeQuery(query); if(users.isEmpty()){ return null; } if(users.size() > 1){ } return users.get(0); } List<User> users = new ArrayList<>(); try ( while (rs.next()) { User user = buildUserFromResultSet(rs); if(user != null){ users.add(user); } } e.printStackTrace(); } return users; }
La funció executeQuery
s’encarrega d’establir la connexió amb la base de dades i retornar tots els usuaris que resultin d’executar la consulta. En aquest cas no sabem si retornarà un, diversos o cap resultat. Això es pot expressar amb el tipus de dades List; si la llista està buida voldrà dir que no hi ha cap resultat, i si n’hi ha, n’hi haurà tants com elements contingui la llista. Per altra part, la funció findUniqueResult
examinarà la llista retornada per executeQuery
i retornarà un objecte null si no hi ha cap resultat per a la consulta; un objecte User
, si hi és, troba un resultat i es llençarà una excepció si hi ha un error. Per avisar la vostra aplicació que alguna cosa ha anat malament llençareu una excepció.
if(users.size() > 1){ }
Aquesta excepció la propagareu a capes superiors de l’aplicació, on decidireu què fer. Les funcions que permeten obtenir informació sobre els usuaris quedaran molt més simplificades.
public List<User> findAllUsers() { List<User> users = executeQuery(qry); return users; } return findUniqueResult(qry); } return findUniqueResult(qry); }
Operacions d'escriptura
Hi ha dues formes diferents d’escriure dades: crear una nova entrada a la base de dades o actualitzar un registre existent.
Començareu a treballar a partir del codi que teniu disponible a l’apartat d’annexos de la unitat.
Contesteu a les següents preguntes:
- Quines operacions necessitareu de creació de dades?
- Quines faran falta d’actualització?
Com podeu veure al codi, en aquest moment només heu definit els usuaris, llavors només podreu crear usuaris. Per altra part, respecte a l’actualització hi ha diverses operacions que caldrà fer:
- Crear un usuari.
- Canviar el correu electrònic.
- Actualitzar el ranking.
- Actualitzar el nom.
La creació es fa mitjançant l’operació SQL INSERT i l’actualització amb UPDATE. Voleu crear un usuari a partir del nom, nom d’usuari i correu electrònic. Què passarà amb la resta d’atributs de l’objecte User
(userId
, rank
, createdOn
i active
)? Les principals diferències amb el codi per llegir dades seran dues: la sentència SQL, que ara contindrà la paraula clau INSERT, i l’operació que cridarem en l’objecte Statement
, que serà executeUpdate
en lloc de executeQuery
.
+ username + "', '" + name + "', '" + email + "'" + ");"; try ( int result = stmt.executeUpdate(qry); if (result == 0) { } return findUserByUsername(username); e.printStackTrace(); return null; } }
El mètode executeUpdate
(línia 11) retornarà la quantitat de registres que s’han inserit en la base de dades. Per aquest motiu, si el nombre de registres inserits és 0 voldrà dir que hi ha hagut un error afegint l’usuari a la base de dades (línies 12-14). Finalment (línia 15), un cop creat el nou usuari voldreu retornar l’objecte User
amb tots els seus atributs. Aprofiteu la funció findUserByUsername
per recuperar l’usuari recentment creat de la base de dades. A continuació podeu veure el test corresponent:
@Test UserDAO userDAO = new UserDAO(dBConnection); User createdUser = userDAO.createUser(username, name, email); Assert.assertNotNull(createdUser); Assert.assertEquals(username, createdUser.getUsername()); Assert.assertNotEquals(0, createdUser.getUserId()); }
Primer comproveu que l’usuari creat no és null, que el nom d’usuari correspon al nom que hem passat a la funció createUser
i que l’atribut userId
del nou usuari no és 0.
Quan es va definir la taula, el camp “user_id” es va definir com user_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL; llavors, si el nou usuari s’ha afegit correctament a la base de dades, es pot afirmar que el seu valor no serà null.
Quan executeu aquest test us trobareu que hi ha un error:
org.h2.jdbc.JdbcSQLException: Table "USERS" already exists; SQL statement: CREATE TABLE users(user_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, username VARCHAR(10) NOT NULL, name VARCHAR(20) NOT NULL, email VARCHAR(50) NOT NULL, rank INT DEFAULT 0, active BOOLEAN DEFAULT true, created_on TIMESTAMP AS CURRENT_TIMESTAMP NOT NULL) [42101-190] at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
Què ha passat? A la funció createUser
, després de definir la consulta a la base de dades, el que feu és establir una connexió amb la base de dades:
Quan després crideu la funció findUserByUsername
, s’executarà la mateixa funció. Recordeu que aquest mètode crea una connexió amb la base de dades de la següent manera:
con = DriverManager.getConnection(props.getProperty("DB_URL"), props.getProperty("DB_USERNAME"), props.getProperty("DB_PASSWORD"));
Aquestes propietats estan definides al fitxer de propietats de la base dades, en el cas dels tests (db_test.properties):
DB_URL=jdbc:h2:mem:socioc_db;INIT=runscript from 'classpath:init.sql'; DB_USERNAME=usuari DB_PASSWORD=passwd
La primera vegada que s’executa dBConnection.getConnection()
s’executarà el fitxer init.sql, que crearà la taula “Users”. Afegireu l’usuari a la base de dades, i en executar findUserByUsername
s’intentarà executar de nou init.sq, però com que ja està creada l’aplicació llençarà aquest error.
Això posa de rellevància un problema del vostre codi: no s’estan reutilitzant les connexions amb la base de dades. Fer una connexió amb la bases de dades és una operació molt cara en termes de recursos: primer s’ha de fer la connexió a través de la xarxa amb la base de dades; s’ha d’inicialitzar una sessió de connexió, que sovint requereix molt de temps de processament per fer l’autenticació d’usuari, establir contextos transaccionals i definir altres aspectes de la sessió que es requereixen per a l’ús de bases de dades subsegüent. En properes seccions veurem com solucionar aquest problema utilitzant pools de connexió. La idea és crear una sèrie de connexions amb la base de dades a l’inici de l’aplicació que es compartiran per a totes les operacions que es facin. De moment, però, refactoritzarem el nostre codi de UserDAO
per permetre reutilitzar les connexions establertes amb la base de dades. Això implica afegir una nova propietat i modificar el mètode executeQuery
.
public class UserDAO { private DBConnection dBConnection; List<User> users = new ArrayList<>(); if (getConnection() == null) { try { setConnection(dBConnection.getConnection()); e.printStackTrace(); } } try ( while (rs.next()) { User user = buildUserFromResultSet(rs); users.add(user); } e.printStackTrace(); } return users; } return connection; } this.connection = connection; } }
El que heu fet per solucionar el problema és afegir un atribut a la classe UserDAO
i, abans de crear-ne un de nou, comprovar si hi ha objecte Connection
vàlid; si és el cas, el reutilitzeu. Abans d’executar el test heu d’assegurar-vos que tanquem la connexió amb la base de dades per tal de a cada test hi hagi només les dades definides al fitxer init.sql. Un cop executat, us assegurareu que la connexió amb la base de dades es tanca de manera que cada vegada la base de dades es torni a crear de nou.
@Before public void setUp() { dBConnection = new DBConnection(connectionProperties); userDAO = new UserDAO(dBConnection); } @After userDAO.getConnection().close(); }
Afegiu ara un altre test per comprovar què passaria en cas que hi hagi un error creant l’usuari. En aquest cas provoquem l’error introduint un nom d’usuari incorrecte:
User createdUser = userDAO.createUser(username, name, email); Assert.assertNull(createdUser); }
Per actualitzar un registre, l’únic que canvia respecte a l’escriptura és que s’utilitza la clàusula SQL UPDATE enlloc d’INSERT. Vegeu com podeu modificar el correu electrònic d’un usuari: afegireu la funció executeUpdateQuery
a UserDAO
i escriureu el corresponent test unitari.
int result = 0; if (getConnection() == null) { try { setConnection(dBConnection.getConnection()); e.printStackTrace(); } } try ( result = stmt.executeUpdate(query); e.printStackTrace(); } return result; } </java> <code java> + "SET email = '" + newEmail + "' " + "WHERE username = '" + username + "' " + ";"; int result = executeUpdateQuery(qry); if (result == 0) { } return findUserByUsername(username); }
I el test: primer creareu un usuari, modificareu el correu electrònic i comprovareu que és l’única informació que ha canviat:
@Test User createdUser = userDAO.createUser(username, name, email); Assert.assertNotNull(createdUser); Assert.assertEquals(email, createdUser.getEmail()); User updatedUser = userDAO.updateUserEmail(createdUser.getUsername(), "new@email.com"); Assert.assertEquals(createdUser.getUserId(), updatedUser.getUserId()); Assert.assertEquals("new@email.com", updatedUser.getEmail()); }
Si doneu una ullada a les funcions updateUserEmail
i createUser
veureu que són molt similars; l’únic que canvia és la consulta a la base de dades. Això vol dir que aquestes dues funcions són candidates per a la refactorització.
+ username + "', '" + name + "', '" + email + "'" + ");"; return createOrUpdateUser(username, qry); } + "SET email = '" + newEmail + "' " + "WHERE username = '" + username + "' " + ";"; return createOrUpdateUser(username, qry); } int result = executeUpdateQuery(query); if (result == 0) { } return findUserByUsername(username); }
Eliminació de dades
L’eliminació de dades també es considera una actualització de la base de dades; en aquest cas, modificar-la per eliminar informació.
La modificació de l’actualització de la base de dades per eliminar la informació es farà utilitzant el mètode executeUpdate
de l’objecte Statement
, per a la qual cosa es pot reutilitzar la funció createOrUpdateUser
que heu creat en l’apartat “Operacions d’escriptura” i que podeu trobar a l’apartat d’annexos de la unitat.
createOrUpdateUser(user.getUsername(), query); }
A continuació creeu el test:
@Test User createdUser = userDAO.createUser(username, name, email); Assert.assertNotNull(createdUser); userDAO.deleteUser(createdUser); User deletedUser = userDAO.findUserByUsername(username); Assert.assertNull(deletedUser); }
Injecció SQL
Un atac d’injecció SQL és exactament el que el seu nom indica: és quan algú intenta “injectar” el seu codi SQL maliciós a la base de dades d’una altra persona, obligant aquesta base de dades a executar SQL que no estava previst. Això podria arruïnar les seves taules de bases de dades i fins i tot extreure informació valuosa o privada. Vegem-ne un exemple: aquesta és la consulta que hem utilitzat per trobar un usuari a partir del correu electrònic:
És a dir, que per al correu electrònic test@email.com utilitzaríeu la crida a la funció findUserByEmail(“test@email.com”)
, amb la qual cosa la consulta SQL quedaria:
SELECT * FROM users WHERE email ='test@email.com';
Un atac d’injecció SQL consistiria a modificar aquest SQL. Com es pot fer? N’hi hauria prou amb modificar l’argument que s’utilitza en cridar la funció. Si executem findUserByEmail(“test@email.com ' OR 1=1”)
, la consulta SQL que s’executaria seria:
SELECT * FROM users WHERE email ='test@email.com' OR '1'='1;
La segona condició sempre es complirà; per tant, en lloc d’efectuar una consulta que retorni els usuaris amb el correu, retornarà un llistat de tots els usuaris. En aquest cas estaríeu exposant dades sense voler, però el codi SQL inserit podria ser fàcilment modificat per esborrar totes les dades.
Seguint l’exemple de trobar un usuari a partir del correu electrònic:
stmt.setString(1, userEmail);
Per poder utilitzar PreparedStatements
refactoritzem el codi per tal de que executeQuery
, executeupdateQuery
i findUniqueResult
no usin més una cadena de caràcters com a paràmetre. Fixeu-vos també que he introduït un nou mètode getPreparedStatement
que s’encarrega d’obtenir un objecte PreparedStatement
ja sigui reutilitzant una connexió existent o creant-ne una de nova:
public class UserDAO { private DBConnection dBConnection; public UserDAO(DBConnection dBConnection) { this.dBConnection = dBConnection; } List<User> users = executeQuery(preparedStatement); return users; } preparedStatement.setString(1, userEmail); return findUniqueResult(preparedStatement); } preparedStatement.setString(1, username); return findUniqueResult(preparedStatement); } preparedStatement.setString(1, username); preparedStatement.setString(2, name); preparedStatement.setString(3, email); return createOrUpdateUser(username, preparedStatement); } preparedStatement.setString(1, newEmail); preparedStatement.setInt(2, user.getUserId()); return createOrUpdateUser(user.getUsername(), preparedStatement); } private User createOrUpdateUser(String username, PreparedStatement preparedStatement) throws Exception { int result = executeUpdateQuery(preparedStatement); if (result == 0) { } return findUserByUsername(username); } preparedStatement.setInt(1, user.getUserId()); createOrUpdateUser(user.getUsername(), preparedStatement); } List<User> users = executeQuery(preparedStatement); if (users.isEmpty()) { return null; } if (users.size() > 1) { } return users.get(0); } List<User> users = new ArrayList<>(); try ( while (rs.next()) { User user = buildUserFromResultSet(rs); users.add(user); } e.printStackTrace(); } return users; } if (getConnection() == null) { try { setConnection(dBConnection.getConnection()); e.printStackTrace(); } } return getConnection().prepareStatement(query); } int result = 0; if (getConnection() == null) { try { setConnection(dBConnection.getConnection()); e.printStackTrace(); } } try { result = preparedStatement.executeUpdate(); e.printStackTrace(); } return result; } int userId = rs.getInt("user_id"); int rank = rs.getInt("rank"); boolean active = rs.getBoolean("active"); User user = new User(userId, username, name, email, rank, timestamp, active); return user; } return connection; } this.connection = connection; } }
A l’apartat d’annexos de la unitat podeu trobar el codi després de la refactorització.
Què s'ha après?
Heu après que JDBC és la tecnologia que permet a una aplicació Java connectar-se a diferents bases de dades utilitzant una única interfície de programació.
Resumint, heu après a:
- Conèixer la utilitat i l’arquitectura de JDBC
- Usar una base de dades en memòria per fer tests unitaris
- Escriure el codi Java per poder fer operacions CRUD amb la base de dades
Ja esteu preparats per començar les activitats proposades en aquest apartat per tal de poder endinsar-vos en el món de la programació amb Java i bases de dades.