9.5 Consultas (Query)

1. Navegación de un ResultSet

Como ya se ha visto, en un objeto ResultSet se encuentran los resultados de la ejecución de una sentencia SQL. Por lo tanto, un objeto ResultSet contiene las filas que satisfacen las condiciones de una sentencia SQL, y ofrece métodos de navegación por los registros como next() que desplaza el cursos al siguiente registro del ResultSet.

Además de este método de desplazamiento básico, existen otros de desplazamiento libre que podremos utilizar siempre y cuando el ResultSet sea de tipo ResultSet.TYPE_SCROLL_INSENSITIVE o ResultSet.TYPE_SCROLL_SENSITIVE como se ha dicho antes.

Algunos de estos métodos son:

• void beforeFirst(): mueve el cursor antes de la primera fila.

• boolean first(): mueve el cursor a la primera fila.

• boolean next(): mueve el cursor a la siguiente fila. Permitido en todos los tipos de ResultSet.

• boolean previous(): mueve el cursor a la fila anterior.

• boolean last(): mueve el cursor a la última fila.

• void afterLast(): mover el cursor después de la última fila.

• boolean absolute(int row): posiciona el cursor en el número de registro indicado. Hay que tener en cuenta que el primer registro es el 1, no el cero.

Ejemplo absolute(n)

absolute(7) desplazará el cursor al séptimo registro. Si valor es negativo se posiciona en el número de registro indicado pero empezando a contar desde el final (el último es el -1). Por ejemplo si tiene 10 registros y llamamos absolute(-2) se desplazará al registro número 9.

• boolean relative(int registros): desplaza el cursor un número relativo de registros, que puede ser positivo o negativo.

Ejemplo relative(n)

Si el cursor está en el registro 5 y llamamos a relative(10) se desplazará al registro número 15. Si luego llamamos a relative(-4) se desplazará al registro 11.

Los métodos que devuelven un tipo boolean devolverán true si ha sido posible mover el cursor a un registro válido, y false en caso contrario, por ejemplo si no tiene ningún registro o hemos saltado a un número de registro que no existe.

Todos estos métodos pueden producir una excepción de tipo SQLException.

También existen otros métodos relacionados con la posición del cursor.

• int getRow(): devuelve el número de registro actual. Cero si no hay registro actual.
• boolean isBeforeFirst(): devuelve true si el cursor está antes del primer registro.
• boolean isFirst(): devuelve true si el cursor está en el primer registro.
• boolean isLast(): devuelve true si el cursor está en el último registro.
• boolean isAfterLast(): devuelve true si el cursor está después del último registro.

2. Obteniendo datos del ResultSet

Los métodos getXXX() ofrecen los medios para recuperar los valores de las columnas (campos) de la fila (registro) actual del ResultSet. No es necesario que las columnas sean obtenidas utilizando un orden determinado.

Para designar una columna podemos utilizar su nombre o bien su número (empezando por 1).

Por ejemplo si la segunda columna de un objeto ResultSet se llama título y almacena datos de tipo String, se podrá recuperar su valor de las dos formas siguientes:

// rs es un objeto ResultSet
String valor = rs.getString(2);
String valor = rs.getString("titulo");

Es importante tener en cuenta que las columnas se numeran de izquierda a derecha y que la primera es la número 1, no la cero.
También que las columnas no son case sensitive, es decir, no distinguen entre mayúsculas y minúsculas.

A tener en cuenta

La información referente a las columnas de un ResultSet se puede obtener llamando al método getMetaData() que devolverá un objeto ResultSetMetaData que contendrá el número, tipo y propiedades de las columnas del ResultSet. EJEMPLO:

import java.sql.*;

public class EjemploResultSetMetaData {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/pr_tuNombre";
    private static final String USUARIO = "pr_tuNombre";
    private static final String PASSWD = "1234";

    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection(JDBC_URL, USUARIO, PASSWD);
             Statement stmt = con.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT id, nombre, fecha_ingreso, salario FROM proveedores")) {

            // Obtener metadata del ResulSet
            ResultSetMetaData rsmd = rs.getMetaData();

            // Obtener el número de columnas
            int columnCount = rsmd.getColumnCount();
            System.out.println("Número de columnas: " + columnCount);

            // Listar las columnas de detalles
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmd.getColumnName(i);
                String columnType = rsmd.getColumnTypeName(i);
                int columnDisplaySize = rsmd.getColumnDisplaySize(i);
                boolean isNullable = rsmd.isNullable(i) == ResultSetMetaData.columnNullable;

                System.out.println("Columna " + i + ":");
                System.out.println("  Nombre: " + columnName);
                System.out.println("  Tipo: " + columnType);
                System.out.println("  Tamaño display: " + columnDisplaySize);
                System.out.println("  Nullable: " + isNullable);
            }

            // Iterar sobre el conjunto de resultados
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(rs.getString(i) + " ");
                }
                System.out.println();
            }

        } catch (SQLException ex) {
            System.out.println("Error de SQL: " + ex.getMessage());
        }
    }
}   

Si conocemos el nombre de una columna, pero no su índice, el método findColumn() puede ser utilizado para obtener el número de columna, pasándole como argumento un objeto String que sea el nombre de la columna correspondiente, este método nos devolverá un entero que será el índice correspondiente a la columna.

3. Tipos de datos y conversiones

Cuando se lanza un método getXXX() determinado sobre un objeto ResultSet para obtener el valor de un campo del registro actual, el driver JDBC convierte el dato que se quiere recuperar al tipo Java especificado y entonces devuelve un valor Java adecuado. Por ejemplo si utilizamos el método getString() y el tipo del dato en la base de datos es VARCHAR, el driver JDBC convertirá el dato VARCHAR de tipo SQL a un objeto String de Java.

Algo parecido sucede con otros tipos de datos SQL como por ejemplo DATE. Podremos acceder a él tanto con getDate() como con getString(). La diferencia es que el primero devolverá un objeto Java de tipo Date y el segundo devolverá un String.

Siempre que sea posible el driver JDBC convertirá el tipo de dato almacenado en la base de datos al tipo solicitado por el método getXXX(), pero hay conversiones que no se pueden realizar y lanzarán una excepción, como por ejemplo si intentamos hacer un getInt() sobre un campo que no contiene un valor numérico.

4. Sentencias que no devuelven datos

Las ejecutamos con el método executeUpdate. Serán todas las sentencias SQL excepto el SELECT, que es la de consulta. Es decir, nos servirá para las siguientes sentencias:

• Sentencias que cambian las estructuras internas de la BD donde se guardan los datos (instrucciones conocidas con las siglas DDL, del inglés Data Definition Language), como por ejemplo CREATE TABLE, CREATE VIEW, ALTER TABLE, DROP TABLE, …,
• Sentencias para otorgar permisos a los usuarios existentes o crear otros nuevos (subgrupo de instrucciones conocidas como DCL o Data Control Language), como por ejemplo GRANT.
• Y también las sentencias para modificar los datos guardados utilizando las instrucciones INSERT, UPDATE y DELETE.

Aunque se trata de sentencias muy dispares, desde el punto de vista de la comunicación con el SGBD se comportan de manera muy similar, siguiendo el siguiente patrón:

1. Instanciación del Statement a partir de una conexión activa.
2. Ejecución de una sentencia SQL pasada por parámetro al método executeUpdate.
3. Cierre del objeto Statement instanciado.

Miremos este ejemplo, en el que vamos a crear una tabla muy sencilla en la Base de Datos MySql/network.

Aquí tienes la clase DatabaseConnection

/**
 <br />* Write a description of class DatabaseConnection here.
 <br />*
 <br />* @author (Victor Ponz)
 <br />* @see <a href="https://stackoverflow.com/questions/6567839/if-i-use-a-singleton-class-for-a-database-connection-can-one-user-close-the-con">Stackoverflow Singleton</a>
 <br />* Patron Singleton
 <br />* ================
 <br />* Este patrón de diseño está diseñado para restringir la creación de objetos pertenecientes a una clase. Su intención consiste en garantizar que
 <br />* una clase sólo tenga una instancia y proporcionar un punto de acceso global a ella.
 <br />* El patrón Singleton se implementa creando en nuestra clase un método que crea una instancia del objeto sólo si todavía no existe alguna.
 <br />* Para asegurar que la clase no puede ser instanciada nuevamente se regula el alcance del constructor haciéndolo privado.
 <br />* Las situaciones más habituales de aplicación de este patrón son aquellas en las que dicha clase ofrece un conjunto de utilidades comunes
 <br />* para todas las capas (como puede ser el sistema de log, conexión a la base de datos, ...)
 <br />* o cuando cierto tipo de datos debe estar disponible para todos los demás objetos de la aplicación.
 <br />* El patrón Singleton provee una única instancia global gracias a que:
 <br />* - La propia clase es responsable de crear la única instancia.
 <br />* - Permite el acceso global a dicha instancia mediante un método de clase.
 <br />* - Declara el constructor de clase como privado para que no sea instanciable directamente.
 <br />*/
public class DatabaseConnection {
    private static DatabaseConnection dbInstance; //Variable para almacenar la unica instancia de la clase
    private static java.sql.Connection con;


    private DatabaseConnection() {
        // El Constructor es privado!!
    }

    public static DatabaseConnection getInstance(){
        //Si no hay ninguna instancia...
        if(dbInstance==null){
            dbInstance= new DatabaseConnection();
        }
        return dbInstance;
    }

    public static java.sql.Connection getConnection(){
        if(con == null){
            try {
                String host = "jdbc:mysql://localhost:3306/nombre-de-la-base-de-datos";
                String username = "root";
                String password = "sa";
                con = java.sql.DriverManager.getConnection( host, username, password );
                System.out.println("Conexión realizada");
            } catch (java.sql.SQLException ex) {
                System.out.println("Se ha producido un error al conectar: " + ex.getMessage());
            }
        }
        return con;
    }
}

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.sql.Statement; 

public class Test {
  static java.sql.Connection con = DatabaseConnection.getInstance().getConnection(); 

  public Test(){ 
      //De momento no hace nada 
  }

  public void createTable() throws SQLException{ 
      Statement st = con.createStatement(); 
      st.executeUpdate("CREATE TABLE T1 (c1 varchar(50))"); 
      st.close(); 
  }
}

5. Sentencias que devuelven datos

Las ejecutamos con el método executeQuery. Servirá para la sentencia SELECT (sentencia de consulta). Los datos que nos devuelva esta sentencia las tendremos que guardar en un objeto de la clase java.sql.ResultSet, es decir conjunto de resultado. Por lo tanto, la ejecución de las consultas tendrá una forma similar a la siguiente:

ResultSet rs = st.executeQuery(sentenciaSQL);

• El objeto ResultSet contiene el resultado de la consulta organizado por filas, por lo que en cada momento se puede consultar una fila.
• Para ir visitando todas las filas de una a una, iremos llamando el método next() del objeto ResultSet, ya que cada vez que se ejecute next avanzará a la siguiente fila.
• Inmediatamente después de una ejecución, el ResultSet se encuentra posicionado justo antes de la primera fila, por lo tanto para acceder a la primera fila será necesario ejecutar next una vez.
• Cuando las filas se acaban, el método next devolverá falso.

Desde cada fila se podrá acceder al valor de sus columnas con ayuda de varios métodos getXXX disponibles según el tipo de datos a devolver y pasando por parámetro el número de columna que deseamos obtener. El nombre de los métodos comienza por get seguido del nombre del tipo de datos. Así, si queremos recuperar la segunda columna, sabiendo que es un dato de tipo String habrá que ejecutar:

rs.getString(2);

Las columnas se empiezan a contar a partir del valor 1 (no cero). La mayor parte de los SGDB soportan la posibilidad de pasar por parámetro el nombre de la columna, pero no todos, así que normalmente se opta por el parámetro numérico.

Por ejemplo MySql sí que deja acceder por nombre, por tanto, suponiendo que el campo 1 se llama id, también se puede hacer:

rs.getInt("id");

En este ejemplo accedemos a la tabla usuarios y mostramos todos sus registros

public void getAllUsers() throws SQLException{ 
    Statement st = con.createStatement(); 
    ResultSet rs = st.executeQuery("SELECT FROM usuarios"); 
    while (rs.next()){ 
        System.out.print(rs.getInt(1) + "\t"); 
        system.out.print(rs.getString(2) + "\t"); 
        system.out.println(rs.getString(3)); 
    }
    //Siempre se debe cerrar
    st.close(); 
    rs.close(); 
}

6. Asegurar la liberación de recursos

Las instancias de Connection y las de Statement guardan, en memoria, mucha información relacionada con las ejecuciones realizadas. Además, mientras continúan activas mantienen en el SGBD una sesión abierta, que supondrá un conjunto importante de recursos abiertos, destinados a servir de forma eficiente las peticiones de los clientes. Es importante cerrar estos objetos para liberar recursos tanto del cliente como del servidor.

Si en un mismo método debemos cerrar un objeto Statement y el Connection a partir del cual la hemos creado, se deberá cerrar primero el Statement y después el Connection. Si lo hacemos al revés, cuando intentamos cerrar el Statement nos saltará una excepción de tipo SQLException, ya que el cierre de la conexión le habría dejado inaccesible.

Además de respetar el orden, asegurar la liberación de los recursos situando las operaciones de cierre dentro de un bloque finally. De este modo, aunque se produzcan errores, no se dejarán de ejecutar las instrucciones de cierre.

Hay que tener en cuenta todavía un detalle más cuando sea necesario realizar el cierre de varios objetos a la vez. En este caso, aunque las situamos una tras otra, todas las instrucciones de cierre dentro del bloque finally, no sería suficiente garantía para asegurar la ejecución de todos los cierres, ya que, si mientras se produce el cierre de uno de los objetos se lanza una excepción, los objetos invocados en una posición posterior a la del que se ha producido el error no se cerrarán.

La solución de este problema pasa por evitar el lanzamiento de cualquier excepción durante el proceso de cierre. Una posible forma es encapsular cada cierre entre sentencias try-catch dentro del finally.

Aquí tienes un ejemplo

private void getAllUsers() {
    Statement st = null;
    ResultSet rs = null; 

    try { 
        st = con.createStatement(); 
        rs = st.executeQuery("SELECT * FROM usuarios"); 

        while (rs.next()){ 
            System.out.print(rs.getInt(1) + "\t"); 
            system.out.print(rs.getString(2) + "\t"); 
            System.out.println(rs.getString(3)); 
        }

    } catch(SQLException e){ 
        System.out.println "ERROR: " + e.getMessage());

    } finally { 
        try{ 
            //Siempre se debe cerrar todo lo abierto
            if (rs != null) {
                rs.close();
            }
        } catch (java.sql.SQLException ex){ 
            System.out+.printIn("ERROR: " + ex.getMessage()); 
        }

        try{ 
            //Siempre se debe cerrar todo lo abierto 
            if (st != null) {
                st.close();
            }
        } catch (java.sql.SQLException ex){ 
            System.out.printIn("ERROR: " + ex.getMessage()); 
        }
    }
}