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.

Figura Obrir un projecte

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.

Figura Seleccionar el projecte

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.

Figura Estructura del projecte

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.

  1. <dependencies>
  2. <dependency>
  3. <groupId>com.h2database</groupId>
  4. <artifactId>h2</artifactId>
  5. <version>1.4.190</version>
  6. </dependency>
  7. <!-- testing -->
  8. <dependency>
  9. <groupId>junit</groupId>
  10. <artifactId>junit</artifactId>
  11. <version>4.12</version>
  12. </dependency>
  13. </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.

  1. public Connection getConnection() {
  2. Connection con = null;
  3. try {
  4. Class.forName("org.h2.Driver");
  5. con = DriverManager.getConnection("jdbc:h2:mem:socioc_db", "usuari" , "passwd");
  6. e.printStackTrace();
  7. }
  8. return con;
  9. }

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:

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

  1. con = DriverManager.getConnection("jdbc:h2:mem:socioc_db");

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, el DriverManager 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.

Figura Arquitectura 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ó.

  1. public Connection getConnection() throws SQLException, IOException {
  2. Properties props = new Properties();
  3. InputStream resourceAsStream = null;
  4. Connection con = null;
  5. try {
  6. ClassLoader classLoader = getClass().getClassLoader();
  7. URL urlResource = classLoader.getResource("db.properties");
  8. if(urlResource != null){
  9. resourceAsStream = urlResource.openStream();
  10. props.load(resourceAsStream);
  11. con = DriverManager.getConnection(props.getProperty("DB_URL"),
  12. props.getProperty("DB_USERNAME"),
  13. props.getProperty("DB_PASSWORD"));
  14. }
  15. e.printStackTrace();
  16. } finally {
  17. if (resourceAsStream != null) {
  18. resourceAsStream.close();
  19. }
  20. }
  21. return con;
  22. }

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:

  1. URL urlResource = classLoader.getResource("db.properties");

La classe java.util.Properties permet recuperar el valor de les propietats:

  1. props.getProperty(NOM_DE_LA_PROPIETAT)

Un cop tenim la classe, escriurem el test unitari.

  1. public class DBConnectionTest {
  2. DBConnection dBConnection;
  3. Connection connection;
  4.  
  5. @Before
  6. public void setUp(){
  7. dBConnection = new DBConnection();
  8. }
  9.  
  10. @After
  11. public void cleanUp() throws SQLException {
  12. connection.close();
  13. }
  14.  
  15. @Test
  16. public void connectarAmbLaBaseDeDades() throws IOException, SQLException {
  17. connection = dBConnection.getConnection();
  18. Assert.assertEquals("H2 JDBC Driver", connection.getMetaData().getDriverName());
  19. Assert.assertEquals("SOCIOC_DB", connection.getCatalog());
  20. }
  21. }

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.

  1. Assert.assertEquals("H2 JDBC Driver", connection.getMetaData().getDriverName());
  2. 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.

Figura Execució dels tests d’una classe

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.

Figura Selecció del projecte principal

Figura Selecció del projecte principal

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.

Figura Finestra de resultat de tests

Figura Mostrar finestra de resultat de tests

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:

  1. public class DBConnectionTest {
  2. DBConnection dBConnection;
  3. Connection connection;
  4.  
  5. @Before
  6. public void setUp(){
  7. dBConnection = new DBConnection();
  8. }
  9.  
  10. @After
  11. public void cleanUp() throws SQLException {
  12. connection.close();
  13. }
  14.  
  15. @Test
  16. public void connectarAmbLaBaseDeDades() throws IOException, SQLException {
  17. connection = dBConnection.getConnection();
  18. Assert.assertEquals("H2 JDBC Driver", connection.getMetaData().getDriverName());
  19. Assert.assertEquals("SOCIOC_DB", connection.getCatalog());
  20. }
  21. }

A la línies 5-9 hi ha el següent codi:

  1. @Before
  2. public void setUp(){
  3. dBConnection = new DBConnection();
  4. }

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:

  1. @After
  2. public void cleanUp() throws SQLException {
  3. connection.close();
  4. }

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.

  1. <plugin>
  2. <groupId>org.jacoco</groupId>
  3. <artifactId>jacoco-maven-plugin</artifactId>
  4. <version>0.7.5.201505241946</version>
  5. <executions>
  6. <execution>
  7. <goals>
  8. <goal>prepare-agent</goal>
  9. </goals>
  10. </execution>
  11. <execution>
  12. <id>report</id>
  13. <phase>prepare-package</phase>
  14. <goals>
  15. <goal>report</goal>
  16. </goals>
  17. </execution>
  18. </executions>
  19. </plugin>

A continuació només fa falta dir a Netbeans que torni a carregar el pom.xml, com podeu veure en la figura.

Figura Recarregar pom.xml

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.

Figura Cobertura

Inicialment no hi ha informació, així que haureu d’executar tots els tests prenent el botó Run All Tests (vegeu la figura).

Figura Report de cobertura inicial

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

Figura Report de cobertura inicial

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

Figura Report de cobertura inicial

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

Figura Fitxer de connexió amb la BD

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

  1. public Connection getConnection(String dbProperties) throws SQLException, IOException {
  2. Properties props = new Properties();
  3. InputStream resourceAsStream = null;
  4. Connection con = null;
  5. try {
  6. ClassLoader classLoader = getClass().getClassLoader();
  7. URL urlResource = classLoader.getResource(dbProperties);
  8. if (urlResource != null) {
  9. resourceAsStream = urlResource.openStream();
  10. props.load(resourceAsStream);
  11. Class.forName(props.getProperty("DB_DRIVER_CLASS"));
  12. con = DriverManager.getConnection(props.getProperty("DB_URL"),
  13. props.getProperty("DB_USERNAME"),
  14. props.getProperty("DB_PASSWORD"));
  15. }
  16. e.printStackTrace();
  17. } finally {
  18. if (resourceAsStream != null) {
  19. resourceAsStream.close();
  20. }
  21. }
  22. return con;
  23. }

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:

  1. @Test
  2. public void dbConnectionWrongDriver() throws IOException, SQLException {
  3. connection = dBConnection.getConnection("db_wrong_driver.properties");
  4. Assert.assertNull(connection);
  5. }

Com que l’objecte Connection pot ser ara null, s’ha de canviar el que es fa després d’executar el test.

  1. @After
  2. public void cleanUp() throws SQLException {
  3. if(connection != null){
  4. connection.close();
  5. }
  6. }

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.

Figura Paquet org.ioc.daw

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.

Figura Paquet org.ioc.daw

Figura Paquet org.ioc.daw

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.

  1. public class User {
  2. private int userId;
  3. private String username;
  4. private String name;
  5. private String email;
  6. private int rank;
  7. private Timestamp createdOn;
  8. private boolean active;
  9.  
  10. public User(int userId, String username, String name, String email, int rank, Timestamp createdOn, boolean active) {
  11. this.userId = userId;
  12. this.username = username;
  13. this.name = name;
  14. this.email = email;
  15. this.rank = rank;
  16. this.createdOn = createdOn;
  17. this.active = active;
  18. }
  19.  
  20. public int getUserId() {
  21. return userId;
  22. }
  23.  
  24. public String getUsername() {
  25. return username;
  26. }
  27.  
  28. public String getName() {
  29. return name;
  30. }
  31.  
  32. public String getEmail() {
  33. return email;
  34. }
  35.  
  36. public int getRank() {
  37. return rank;
  38. }
  39.  
  40. public Timestamp getCreatedOn() {
  41. return createdOn;
  42. }
  43.  
  44. public boolean isActive() {
  45. return active;
  46. }
  47. }

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.

  1. public class UserDAO {
  2. public List<User> findAllUsers() {
  3. String qry = "select user_id, username, name, email, rank, active, created_on from users";
  4. DBConnection dBConnection = new DBConnection();
  5. List<User> users = new ArrayList<>();
  6. try (
  7. Connection conn = dBConnection.getConnection("db.properties");
  8. Statement stmt = conn.createStatement();
  9. ResultSet rs = stmt.executeQuery(qry);) {
  10. while (rs.next()) {
  11. int userId = rs.getInt("user_id");
  12. String username = rs.getString("username");
  13. String name = rs.getString("name");
  14. String email = rs.getString("email");
  15. int rank = rs.getInt("rank");
  16. boolean active = rs.getBoolean("active");
  17. Timestamp timestamp = rs.getTimestamp("created_on");
  18. User user = new User(userId, username, name, email, rank, timestamp, active);
  19. users.add(user);
  20. }
  21. } catch (SQLException | IOException e) {
  22. e.printStackTrace();
  23. }
  24. return users;
  25. }
  26. }

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

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

  1. try (
  2. Connection conn = dBConnection.getConnection("db.properties");
  3. Statement stmt = conn.createStatement();
  4. ResultSet rs = stmt.executeQuery(qry);)

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

  1. public class UserDAO {
  2. private DBConnection dBConnection;
  3. public UserDAO(DBConnection dBConnection){
  4. this.dBConnection = dBConnection;
  5. }
  6.  
  7. public List<User> findAllUsers() {
  8. String qry = "select user_id, username, name, email, rank, active, created_on from users";
  9. List<User> users = new ArrayList<>();
  10. try (
  11. Connection conn = getDBConnection();
  12. Statement stmt = conn.createStatement();
  13. ResultSet rs = stmt.executeQuery(qry);) {
  14. while (rs.next()) {
  15. int userId = rs.getInt("user_id");
  16. String username = rs.getString("username");
  17. String name = rs.getString("name");
  18. String email = rs.getString("email");
  19. int rank = rs.getInt("rank");
  20. boolean active = rs.getBoolean("active");
  21. Timestamp timestamp = rs.getTimestamp("created_on");
  22. User user = new User(userId, username, name, email, rank, timestamp, active);
  23. users.add(user);
  24. }
  25. } catch (SQLException | IOException e) {
  26. e.printStackTrace();
  27. }
  28. return users;
  29. }
  30.  
  31. public getDBConnection(){
  32. this.dBConnection = dBConnection;
  33. }
  34. }

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.

  1. public class UserDAO {
  2. private DBConnection dBConnection;
  3. public UserDAO(DBConnection dBConnection){
  4. this.dBConnection = dBConnection;
  5. }

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.

  1. public class DBConnection {
  2. private String connectionFile;
  3. public DBConnection(String connectionFile) {
  4. this.connectionFile = connectionFile;
  5. }

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.

  1. public class UserDAOTest {
  2. private DBConnection dBConnection;
  3. private String connectionProperties = "db-test.properties";
  4. @Before
  5. public void setUp(){
  6. dBConnection = new DBConnection(connectionProperties);
  7. }
  8.  
  9. @Test
  10. public void findAllUsers(){
  11. UserDAO userDAO = new UserDAO(dBConnection);
  12. List<User> users = userDAO.findAllUsers();
  13. Assert.assertEquals("Hauriem de tenir 2 usuaris a la base de dades", 2, users.size());
  14. }
  15. }

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.

Figura Fitxers pel test unitari de UserDAO

  1. CREATE TABLE users(user_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  2. username VARCHAR(10) NOT NULL,
  3. name VARCHAR(20) NOT NULL,
  4. email VARCHAR(50) NOT NULL,
  5. rank INT DEFAULT 0,
  6. active BOOLEAN DEFAULT TRUE,
  7. created_on TIMESTAMP AS CURRENT_TIMESTAMP NOT NULL);
  8.  
  9. INSERT INTO users (username, name, email) VALUES ('user1', 'John Test', 'john@email.com');
  10. 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:

  1. public User findUserByEmail(String userEmail){
  2. String qry = "select * from users where email ='" + userEmail +"'";
  3.  
  4. User user = null;
  5. try (
  6. Connection conn = dBConnection.getConnection();
  7. Statement stmt = conn.createStatement();
  8. ResultSet rs = stmt.executeQuery(qry);) {
  9. while (rs.next()) {
  10. int userId = rs.getInt("user_id");
  11. String username = rs.getString("username");
  12. String name = rs.getString("name");
  13. String email = rs.getString("email");
  14. int rank = rs.getInt("rank");
  15. boolean active = rs.getBoolean("active");
  16. Timestamp timestamp = rs.getTimestamp("created_on");
  17. user = new User(userId, username, name, email, rank, timestamp, active);
  18. }
  19. } catch (SQLException | IOException e) {
  20. e.printStackTrace();
  21. }
  22. return user;

La part més important és on es defineix la consulta que es farà a la base de dades:

  1. String qry = "select * from users where email ='" + userEmail +"'";

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:

  1. @Test
  2. public void findUserByEmail(){
  3. String existingEmail = "john@email.com";
  4. String unknownEmail = "does.not@exist.com";
  5.  
  6. UserDAO userDAO = new UserDAO(dBConnection);
  7. User user = userDAO.findUserByEmail(existingEmail);
  8. Assert.assertNotNull(user);
  9. user = userDAO.findUserByEmail(unknownEmail);
  10. Assert.assertNull(user);
  11. }

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.

  1. public User findUserByUsername(String username){
  2. String qry = "select * from users where username ='" + username +"'";
  3. User user = null;
  4. try (
  5. Connection conn = dBConnection.getConnection();
  6. Statement stmt = conn.createStatement();
  7. ResultSet rs = stmt.executeQuery(qry);) {
  8. while (rs.next()) {
  9. int userId = rs.getInt("user_id");
  10. username = rs.getString("username");
  11. String name = rs.getString("name");
  12. String email = rs.getString("email");
  13. int rank = rs.getInt("rank");
  14. boolean active = rs.getBoolean("active");
  15. Timestamp timestamp = rs.getTimestamp("created_on");
  16. user = new User(userId, username, name, email, rank, timestamp, active);
  17. }
  18. } catch (SQLException | IOException e) {
  19. e.printStackTrace();
  20. }
  21. return user;
  22. }

I el test unitari:

  1. @Test
  2. public void findUserByUsername(){
  3. String existingUsername = "user1";
  4. String unknownUsername = "unknown";
  5.  
  6. UserDAO userDAO = new UserDAO(dBConnection);
  7. User user = userDAO.findUserByUsername(existingUsername);
  8. Assert.assertNotNull(user);
  9. user = userDAO.findUserByUsername(unknownUsername);
  10. Assert.assertNull(user);
  11. }

Doneu una ullada a la classe sencera, ja que hi ha codi repetit; símptoma inequívoc que és hora de refactoritzar el codi.

  1. public class UserDAO {
  2. private DBConnection dBConnection;
  3. public UserDAO(DBConnection dBConnection){
  4. this.dBConnection = dBConnection;
  5. }
  6.  
  7. public List<User> findAllUsers() {
  8. String qry = "select user_id, username, name, email, rank, active, created_on from users";
  9. List<User> users = new ArrayList<>();
  10. try (
  11. Connection conn = dBConnection.getConnection();
  12. Statement stmt = conn.createStatement();
  13. ResultSet rs = stmt.executeQuery(qry);) {
  14. while (rs.next()) {
  15. int userId = rs.getInt("user_id");
  16. String username = rs.getString("username");
  17. String name = rs.getString("name");
  18. String email = rs.getString("email");
  19. int rank = rs.getInt("rank");
  20. boolean active = rs.getBoolean("active");
  21. Timestamp timestamp = rs.getTimestamp("created_on");
  22. User user = new User(userId, username, name, email, rank, timestamp, active);
  23. users.add(user);
  24. }
  25. } catch (SQLException | IOException e) {
  26. e.printStackTrace();
  27. }
  28. return users;
  29. }
  30.  
  31. public User findUserByEmail(String userEmail){
  32. String qry = "select * from users where email ='" + userEmail +"'";
  33. User user = null;
  34. try (
  35. Connection conn = dBConnection.getConnection();
  36. Statement stmt = conn.createStatement();
  37. ResultSet rs = stmt.executeQuery(qry);) {
  38. while (rs.next()) {
  39. int userId = rs.getInt("user_id");
  40. String username = rs.getString("username");
  41. String name = rs.getString("name");
  42. String email = rs.getString("email");
  43. int rank = rs.getInt("rank");
  44. boolean active = rs.getBoolean("active");
  45. Timestamp timestamp = rs.getTimestamp("created_on");
  46. user = new User(userId, username, name, email, rank, timestamp, active);
  47. }
  48. } catch (SQLException | IOException e) {
  49. e.printStackTrace();
  50. }
  51. return user;
  52. }
  53.  
  54.  
  55. public User findUserByUsername(String username){
  56. String qry = "select * from users where username ='" + username +"'";
  57. User user = null;
  58. try (
  59. Connection conn = dBConnection.getConnection();
  60. Statement stmt = conn.createStatement();
  61. ResultSet rs = stmt.executeQuery(qry);) {
  62. while (rs.next()) {
  63. int userId = rs.getInt("user_id");
  64. username = rs.getString("username");
  65. String name = rs.getString("name");
  66. String email = rs.getString("email");
  67. int rank = rs.getInt("rank");
  68. boolean active = rs.getBoolean("active");
  69. Timestamp timestamp = rs.getTimestamp("created_on");
  70. user = new User(userId, username, name, email, rank, timestamp, active);
  71. }
  72. } catch (SQLException | IOException e) {
  73. e.printStackTrace();
  74. }
  75. return user;
  76. }
  77. }

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.

  1. public class UserDAO {
  2. private DBConnection dBConnection;
  3. public UserDAO(DBConnection dBConnection){
  4. this.dBConnection = dBConnection;
  5. }
  6.  
  7. public List<User> findAllUsers() {
  8. String qry = "select user_id, username, name, email, rank, active, created_on from users";
  9. List<User> users = new ArrayList<>();
  10. try (
  11. Connection conn = dBConnection.getConnection();
  12. Statement stmt = conn.createStatement();
  13. ResultSet rs = stmt.executeQuery(qry);) {
  14. while (rs.next()) {
  15. User user = buildUserFromResultSet(rs);
  16. users.add(user);
  17. }
  18. } catch (SQLException | IOException e) {
  19. e.printStackTrace();
  20. }
  21. return users;
  22. }
  23.  
  24. public User findUserByEmail(String userEmail){
  25. String qry = "select * from users where email ='" + userEmail +"'";
  26. User user = null;
  27. try (
  28. Connection conn = dBConnection.getConnection();
  29. Statement stmt = conn.createStatement();
  30. ResultSet rs = stmt.executeQuery(qry);) {
  31. while (rs.next()) {
  32. user = buildUserFromResultSet(rs);
  33. }
  34. } catch (SQLException | IOException e) {
  35. e.printStackTrace();
  36. }
  37. return user;
  38. }
  39.  
  40. public User findUserByUsername(String username){
  41. String qry = "select * from users where username ='" + username +"'";
  42. User user = null;
  43. try (
  44. Connection conn = dBConnection.getConnection();
  45. Statement stmt = conn.createStatement();
  46. ResultSet rs = stmt.executeQuery(qry);) {
  47. while (rs.next()) {
  48. user = buildUserFromResultSet(rs);
  49. }
  50. } catch (SQLException | IOException e) {
  51. e.printStackTrace();
  52. }
  53. return user;
  54. }
  55.  
  56. private User buildUserFromResultSet(ResultSet rs) throws SQLException{
  57. int userId = rs.getInt("user_id");
  58. String username = rs.getString("username");
  59. String name = rs.getString("name");
  60. String email = rs.getString("email");
  61. int rank = rs.getInt("rank");
  62. boolean active = rs.getBoolean("active");
  63. Timestamp timestamp = rs.getTimestamp("created_on");
  64. User user = new User(userId, username, name, email, rank, timestamp, active);
  65. return user;
  66. }
  67. }

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:

  1. private User findUniqueResult(String query) throws Exception{
  2. List<User> users = executeQuery(query);
  3. if(users.isEmpty()){
  4. return null;
  5. }
  6. if(users.size() > 1){
  7. throw new Exception("Only one result expected");
  8. }
  9. return users.get(0);
  10. }
  11.  
  12. private List<User> executeQuery(String query){
  13. List<User> users = new ArrayList<>();
  14. try (
  15. Connection conn = dBConnection.getConnection();
  16. Statement stmt = conn.createStatement();
  17. ResultSet rs = stmt.executeQuery(query);) {
  18. while (rs.next()) {
  19. User user = buildUserFromResultSet(rs);
  20. if(user != null){
  21. users.add(user);
  22. }
  23. }
  24. } catch (SQLException | IOException e) {
  25. e.printStackTrace();
  26. }
  27. return users;
  28. }

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

  1. if(users.size() > 1){
  2. throw new Exception("Only one result expected");
  3. }

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.

  1. public List<User> findAllUsers() {
  2. String qry = "select user_id, username, name, email, rank, active, created_on from users";
  3. List<User> users = executeQuery(qry);
  4. return users;
  5. }
  6.  
  7. public User findUserByEmail(String userEmail) throws Exception{
  8. String qry = "select * from users where email ='" + userEmail +"'";
  9. return findUniqueResult(qry);
  10. }
  11.  
  12. public User findUserByUsername(String username) throws Exception{
  13. String qry = "select * from users where username ='" + username +"'";
  14. return findUniqueResult(qry);
  15. }

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.

  1. public User createUser(String username, String name, String email) throws Exception {
  2. String qry = "INSERT INTO users (username, name, email) VALUES ('"
  3. + username + "', '"
  4. + name + "', '"
  5. + email + "'"
  6. + ");";
  7. try (
  8. Connection conn = dBConnection.getConnection();
  9. Statement stmt = conn.createStatement()) {
  10.  
  11. int result = stmt.executeUpdate(qry);
  12. if (result == 0) {
  13. throw new Exception("Error creating user");
  14. }
  15. return findUserByUsername(username);
  16.  
  17. } catch (SQLException | IOException e) {
  18. e.printStackTrace();
  19. return null;
  20. }
  21. }

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:

  1. @Test
  2. public void createUser() throws Exception {
  3. String username = "testUser";
  4. String name = "Pete Test";
  5. String email = "pete@email.com";
  6. UserDAO userDAO = new UserDAO(dBConnection);
  7. User createdUser = userDAO.createUser(username, name, email);
  8. Assert.assertNotNull(createdUser);
  9. Assert.assertEquals(username, createdUser.getUsername());
  10. Assert.assertNotEquals(0, createdUser.getUserId());
  11. }

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:

  1. Connection conn = dBConnection.getConnection();

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:

  1. con = DriverManager.getConnection(props.getProperty("DB_URL"),
  2. props.getProperty("DB_USERNAME"),
  3. props.getProperty("DB_PASSWORD"));
  4.  

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.

  1. public class UserDAO {
  2. private DBConnection dBConnection;
  3. private Connection connection;
  4.  
  5. private List<User> executeQuery(String query) {
  6. List<User> users = new ArrayList<>();
  7.  
  8. if (getConnection() == null) {
  9. try {
  10. setConnection(dBConnection.getConnection());
  11. } catch (SQLException | IOException e) {
  12. e.printStackTrace();
  13. }
  14. }
  15. try (
  16. Statement stmt = getConnection().createStatement();
  17. ResultSet rs = stmt.executeQuery(query)) {
  18. while (rs.next()) {
  19. User user = buildUserFromResultSet(rs);
  20. users.add(user);
  21. }
  22. } catch (SQLException e) {
  23. e.printStackTrace();
  24. }
  25. return users;
  26. }
  27.  
  28. public Connection getConnection() {
  29. return connection;
  30. }
  31.  
  32. public void setConnection(Connection connection) {
  33. this.connection = connection;
  34. }
  35.  
  36. }

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.

  1. @Before
  2. public void setUp() {
  3. dBConnection = new DBConnection(connectionProperties);
  4. userDAO = new UserDAO(dBConnection);
  5. }
  6. @After
  7. public void tearDown() throws IOException, SQLException {
  8. userDAO.getConnection().close();
  9. }

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:

  1. @Test(expected = Exception.class)
  2. public void createUserWithError() throws Exception {
  3. String username = "sl','sls";
  4. String name = "Pete Test";
  5. String email = "pete@email.com";
  6. User createdUser = userDAO.createUser(username, name, email);
  7. Assert.assertNull(createdUser);
  8. }

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.

  1. private int executeUpdateQuery(String query) {
  2. int result = 0;
  3. if (getConnection() == null) {
  4. try {
  5. setConnection(dBConnection.getConnection());
  6. } catch (SQLException | IOException e) {
  7. e.printStackTrace();
  8. }
  9. }
  10. try (
  11. Statement stmt = getConnection().createStatement()) {
  12. result = stmt.executeUpdate(query);
  13. } catch (SQLException e) {
  14. e.printStackTrace();
  15. }
  16. return result;
  17. }
  18. </java>
  19.  
  20. <code java>
  21. public User updateUserEmail(String username, String newEmail) throws Exception {
  22. String qry = "UPDATE users "
  23. + "SET email = '" + newEmail + "' "
  24. + "WHERE username = '" + username + "' "
  25. + ";";
  26. int result = executeUpdateQuery(qry);
  27. if (result == 0) {
  28. throw new Exception("Error udpating user");
  29. }
  30. return findUserByUsername(username);
  31. }

I el test: primer creareu un usuari, modificareu el correu electrònic i comprovareu que és l’única informació que ha canviat:

  1. @Test
  2. public void updateUserEmail() throws Exception {
  3. String username = "testUser";
  4. String name = "Pete Test";
  5. String email = "pete@email.com";
  6. User createdUser = userDAO.createUser(username, name, email);
  7. Assert.assertNotNull(createdUser);
  8. Assert.assertEquals(email, createdUser.getEmail());
  9. User updatedUser = userDAO.updateUserEmail(createdUser.getUsername(), "new@email.com");
  10. Assert.assertEquals(createdUser.getUserId(), updatedUser.getUserId());
  11. Assert.assertEquals("new@email.com", updatedUser.getEmail());
  12. }

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

  1. public User createUser(String username, String name, String email) throws Exception {
  2. String qry = "INSERT INTO users (username, name, email) VALUES ('"
  3. + username + "', '"
  4. + name + "', '"
  5. + email + "'"
  6. + ");";
  7. return createOrUpdateUser(username, qry);
  8. }
  9.  
  10. public User updateUserEmail(String username, String newEmail) throws Exception {
  11. String qry = "UPDATE users "
  12. + "SET email = '" + newEmail + "' "
  13. + "WHERE username = '" + username + "' "
  14. + ";";
  15. return createOrUpdateUser(username, qry);
  16. }
  17.  
  18. private User createOrUpdateUser(String username, String query) throws Exception {
  19. int result = executeUpdateQuery(query);
  20. if (result == 0) {
  21. throw new Exception("Error creating user");
  22. }
  23. return findUserByUsername(username);
  24. }

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.

  1. public void deleteUser(User user) throws Exception {
  2. String query = "DELETE FROM users WHERE user_id = '" + user.getUserId() + "' ";
  3. createOrUpdateUser(user.getUsername(), query);
  4. }

A continuació creeu el test:

  1. @Test
  2. public void deleteUser() throws Exception {
  3. String username = "testUser";
  4. String name = "Pete Test";
  5. String email = "pete@email.com";
  6. User createdUser = userDAO.createUser(username, name, email);
  7. Assert.assertNotNull(createdUser);
  8. userDAO.deleteUser(createdUser);
  9. User deletedUser = userDAO.findUserByUsername(username);
  10. Assert.assertNull(deletedUser);
  11. }

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:

  1. String qry = "select * from users where email ='" + userEmail + "'";

É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:

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

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

  1. PreparedStatement stmt = connection.prepareStatement("select * from users where email =?");
  2. 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:

  1. public class UserDAO {
  2. private DBConnection dBConnection;
  3. private Connection connection;
  4.  
  5. public UserDAO(DBConnection dBConnection) {
  6. this.dBConnection = dBConnection;
  7. }
  8.  
  9. public List<User> findAllUsers() throws SQLException {
  10. String qry = "select user_id, username, name, email, rank, active, created_on from users";
  11. PreparedStatement preparedStatement = getPreparedStatement(qry);
  12. List<User> users = executeQuery(preparedStatement);
  13. return users;
  14. }
  15.  
  16. public User findUserByEmail(String userEmail) throws Exception {
  17. String qry = "select * from users where email = ?";
  18. PreparedStatement preparedStatement = getPreparedStatement(qry);
  19. preparedStatement.setString(1, userEmail);
  20. return findUniqueResult(preparedStatement);
  21. }
  22.  
  23. public User findUserByUsername(String username) throws Exception {
  24. String qry = "select * from users where username =?";
  25. PreparedStatement preparedStatement = getPreparedStatement(qry);
  26. preparedStatement.setString(1, username);
  27. return findUniqueResult(preparedStatement);
  28. }
  29.  
  30. public User createUser(String username, String name, String email) throws Exception {
  31. String qry = "INSERT INTO users (username, name, email) VALUES (?, ?, ?)";
  32. PreparedStatement preparedStatement = getPreparedStatement(qry);
  33. preparedStatement.setString(1, username);
  34. preparedStatement.setString(2, name);
  35. preparedStatement.setString(3, email);
  36. return createOrUpdateUser(username, preparedStatement);
  37. }
  38.  
  39. public User updateUserEmail(User user, String newEmail) throws Exception {
  40. String qry = "UPDATE users SET email = ? WHERE user_id = ? ";
  41. PreparedStatement preparedStatement = getPreparedStatement(qry);
  42. preparedStatement.setString(1, newEmail);
  43. preparedStatement.setInt(2, user.getUserId());
  44. return createOrUpdateUser(user.getUsername(), preparedStatement);
  45. }
  46.  
  47.  
  48. private User createOrUpdateUser(String username, PreparedStatement preparedStatement) throws Exception {
  49. int result = executeUpdateQuery(preparedStatement);
  50. if (result == 0) {
  51. throw new Exception("Error creating user");
  52. }
  53. return findUserByUsername(username);
  54. }
  55.  
  56. public void deleteUser(User user) throws Exception {
  57. String qry = "DELETE FROM users WHERE user_id = ?";
  58. PreparedStatement preparedStatement = getPreparedStatement(qry);
  59. preparedStatement.setInt(1, user.getUserId());
  60. createOrUpdateUser(user.getUsername(), preparedStatement);
  61. }
  62.  
  63. private User findUniqueResult(PreparedStatement preparedStatement) throws Exception {
  64. List<User> users = executeQuery(preparedStatement);
  65. if (users.isEmpty()) {
  66. return null;
  67. }
  68. if (users.size() > 1) {
  69. throw new Exception("Only one result expected");
  70. }
  71. return users.get(0);
  72. }
  73.  
  74. private List<User> executeQuery(PreparedStatement preparedStatement) {
  75. List<User> users = new ArrayList<>();
  76.  
  77. try (
  78. ResultSet rs = preparedStatement.executeQuery()) {
  79. while (rs.next()) {
  80. User user = buildUserFromResultSet(rs);
  81. users.add(user);
  82. }
  83. } catch (SQLException e) {
  84. e.printStackTrace();
  85. }
  86. return users;
  87. }
  88.  
  89. private PreparedStatement getPreparedStatement(String query) throws SQLException {
  90. if (getConnection() == null) {
  91. try {
  92. setConnection(dBConnection.getConnection());
  93. } catch (SQLException | IOException e) {
  94. e.printStackTrace();
  95. }
  96. }
  97. return getConnection().prepareStatement(query);
  98. }
  99.  
  100.  
  101. private int executeUpdateQuery(PreparedStatement preparedStatement) {
  102. int result = 0;
  103. if (getConnection() == null) {
  104. try {
  105. setConnection(dBConnection.getConnection());
  106. } catch (SQLException | IOException e) {
  107. e.printStackTrace();
  108. }
  109. }
  110. try {
  111. result = preparedStatement.executeUpdate();
  112. } catch (SQLException e) {
  113. e.printStackTrace();
  114. }
  115. return result;
  116. }
  117.  
  118. private User buildUserFromResultSet(ResultSet rs) throws SQLException {
  119. int userId = rs.getInt("user_id");
  120. String username = rs.getString("username");
  121. String name = rs.getString("name");
  122. String email = rs.getString("email");
  123. int rank = rs.getInt("rank");
  124. boolean active = rs.getBoolean("active");
  125. Timestamp timestamp = rs.getTimestamp("created_on");
  126. User user = new User(userId, username, name, email, rank, timestamp, active);
  127. return user;
  128. }
  129.  
  130. public Connection getConnection() {
  131. return connection;
  132. }
  133.  
  134. public void setConnection(Connection connection) {
  135. this.connection = connection;
  136. }
  137. }

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.

Anar a la pàgina anterior:
Bibliografia bàsica
Anar a la pàgina següent:
Activitats