Después de haber estudiado las formas de conexión a la base de datos vamos a aprender a realizar consultas a las tablas. Este capítulo da por supuesto que el lector ya tiene nociones básicas de modelo relacional de datos y de SQL. Pero no nos preocupemos, la mecánica para poder realizar consultas es bastante sencilla.

La estructura de la base de datos

Lo primero es conocer la estructura de la base de datos 'prueba':

• La primera tabla se llama cliente, representa la información esencial de cada cliente, en donde el campo 'código' es la clave primaria. Ha sido creada con la siguiente sentencia SQL:

  
  CREATE TABLE `cliente` (
  	`codigo` char(10) NOT NULL default '',
  	`nombre` char(20) default '',
  	`ape1` char(20) default '',
  	`ape2` char(20) default '',
  	`edad` int(11) default '0',
  	PRIMARY KEY  (`codigo`)
  ) TYPE=InnoDB; 
  

  
  
  
• La segunda tabla representa las ventas o facturación de cada cliente. La relación entre la entidad 'cliente' y la entidad 'venta' es del tipo 1:N, es decir, a un cliente se le puede asignar N ventas, pero una venta se refiere sólo a un cliente. Por tanto, el campo 'código' es una clave externa sobre la clave primaria 'cliente.codigo'. La tabla ha sido creada por medio de:

  
  CREATE TABLE `venta` (
  	`codigo` char(10) NOT NULL default '',
  	`precio` float default '0',
  	`coste` float default '0',
  	INDEX(`codigo`),
  	FOREIGN KEY (`codigo`) REFERENCES `cliente` (`codigo`) ON DELETE CASCADE ON UPDATE CASCADE
  ) TYPE=InnoDB; 
  

  Con 'ON DELETE CASCADE' le ordenamos al gestor de base de datos que si se borra un cliente, entonces se borrarán los registros de ventas de ese cliente. Con ON UPDATE CASCADE indicamos que un cambio en el código del cliente ('cliente.codigo') implica reflejar el cambio en 'venta.codigo' con la finalidad de mantener la integridad referencial.

Nota sobre MySQL: para manejar claves externas (foreign key) en MySQL por debajo de versiones 5.1 es necesario que las tablas sean del tipo InnoDB. A partir de la versión 5.1 esta característica es soportada por todos los tipos de tablas.

Los datos que tenemos en las tablas reflejan que hay tres clientes y hemos realizado cinco ventas con ellos:

Definir y ejecutar sentencias SQL conlleva un proceso:

1. Lo primero es escribir una sentencia, almacenándola en un String. En nuestro siguiente ejemplo es un sencillo listado de clientes, ordenado por el nombre:

   
   	String orden_SQL = "SELECT cliente.codigo, cliente.nombre, cliente.edad FROM cliente ORDER BY cliente.nombre";
   

2. En segundo lugar, debemos crear un objeto de la clase Statement, por medio de una llamada a createStatement() de la clase Connection (recuérdese que el objeto de la clase Connection lo obtuvimos al conectarnos a la base de datos):

   
   	Statement sentencia = con.createStatement();
   

3. A continuación se llama al método executeQuery( String ) de la clase Statement. Si nuestra sentencia implicase una modificación de datos (INSERT, UPDATE o DELETE) el método sería executeUpdate( String ):

   
   	ResultSet rs = sentencia.executeQuery( orden_SQL );
   

   Respecto a executeQuery( String ) conviene resaltar que nos devuelve un objeto de la clase ResultSet, es decir, un objeto que "encapsula" los datos resultantes de la consulta. Nos permite manejar fila por fila el resultado de la consulta.
   
   

4. Hay que definir un bucle para obtener el resultado (fila a fila) de la consulta. Para ello utilizamos la función ResulSet.next(). Ojo: el cursor del conjunto de resultados se situa en la posición anterior a la primera fila, por tanto habrá que llamar a esta función para obtener la primera fila:

   
   	while ( rs.next() ) {
   		String res = rs.getString( "codigo" ) + ", " + rs.getString( "nombre" ) + ", " + rs.getInt( "edad" );
   		System.out.println( res );
   	}
   

   Dentro del bucle obtenemos los resultados de cada columna, por medio de funciones getXXX( String ) de la clase ResulSet. El parámetro String representa el nombre de la columna. En función del tipo de dato habrá que escoger el nombre de la función: getInt() para enteros, getString para String, etc. En nuestro ejemplo, los datos obtenidos se muestran por pantalla usando println().
   
   

5. Es necesario cerrar el objeto de la clase Statement: sentencia.close(). El cierre del objeto Statement genera automáticamente el cierre del objeto ResultSet. El objeto ResultSet también se cierra automáticamente cuando se va a producir su recolección como basura (garbage collection).
   
   
6. No hay que olvidar el sempiterno manejo de escepciones:

   
   	catch (SQLException e) { e.printStackTrace();  }
   

   
   
   
7. Nos aseguramos del cierre de la conexión: con.close(). En nuestro caso, hemos tomado precauciones. Puede ocurrir que la conexión haya sido correcta pero se produce un error en una sentencia. Ante este hecho, hay que asegurarse que se produce el cierre de la conexión. Para ello situamos en finally una llamada a nuestro método cerrar_conexion():

   
       public static void cerrar_conexion( Connection con ) {
   	try {
   	    if ( con != null )
   		if ( !con.isClosed() )    // Si no está cerrada, la cierro
   		    con.close();
   	}
   	catch (SQLException e) { e.printStackTrace();  }
       }
   

Para nuestro ejemplo hemos creado una clase que llamamos 'consulta', cuyo constructor recibe la conexión con la base de datos. La clase 'consulta' está especializada en la definición y ejecución de las consultas SQL. El código completo del ejemplo lo puedes ver más abajo. En la función ver_cliente() se implementa la sencilla consulta SELECT que acabamos de ver:

package jdbc01;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.lang.ClassNotFoundException;
import java.io.FileOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
/************************************************************************
 Ejemplo de manejo de sentencias SELECT
*************************************************************************/
public class jdbc01_conexion4 {
    public static void main(String[] args) {
	Connection con = null;
	try {
	    /*** Registro de driver ****/
	    Class.forName("com.mysql.jdbc.Driver");

	    /*** Crear conexión con base de datos ***/
	    con = DriverManager.getConnection( "jdbc:mysql://localhost/prueba", "root", "palabra" );

	    /*** Con el objeto c ejecutamos sentencias SQL y visualizamos resultados ***/
	    consulta c = new consulta( con );
	    System.out.println( "Listado de todos los clientes:");
	    c.ver_cliente();
	    System.out.println( "Listado de los clientes entre 21 y 30 años:");
	    c.ver_cliente( 21, 30);
	    System.out.println( "Listado de los clientes entre 21 y 30 años con sus ventas correspondientes:");
	    c.ver_cliente_venta( 21, 30);
	    System.out.println( "Listado de ingresos, costes y beneficio de clientes:");
	    c.ver_beneficio_cliente();
	}
	/*** Capturo excepciones ***/
	catch( ClassNotFoundException e ) { e.printStackTrace();  }
	catch (SQLException e) { e.printStackTrace();  }

	/*** Haya excepción o no, tengo que cerrar la conexión ***/
	finally {
	    cerrar_conexion( con );
	}
    }

    /********************************************************************
     Me aseguro de que se cierra la conexión
    ********************************************************************/
    public static void cerrar_conexion( Connection con ) {
	try {
	    if ( con != null )
		if ( !con.isClosed() )    // Si no está cerrada, la cierro
		    con.close();
	}
	catch (SQLException e) { e.printStackTrace();  }
    }
}

/*********************************************************
 Clase en la que definimos y ejecutamos las sentencias SQL
**********************************************************/
class consulta {
    Connection con;
    consulta( Connection con ) {
	this.con = con;
    }

    /*******************************************************************
     Un sencillo SELECT de clientes, ordenados por nombre
    *******************************************************************/
    void ver_cliente() {
	try {
	    /***** Definir sentencia y ejecutarla ********/
	    String orden_SQL = "SELECT cliente.codigo, cliente.nombre, cliente.edad " +
			       "FROM cliente ORDER BY cliente.nombre";
	    Statement sentencia = con.createStatement();
	    ResultSet rs = sentencia.executeQuery( orden_SQL );

	    /*** Recorrer fila a fila el resultado ****/
	    while ( rs.next() ) {
		String res = rs.getString( "codigo" ) + ", " +
				 rs.getString( "nombre" ) + ", " + rs.getInt( "edad" );
		System.out.println( res );
	    }
	    sentencia.close();
	}
	catch (SQLException e) { e.printStackTrace();  }
    }


    /*******************************************************************
     Un poco más de complejidad que el anterior
     Un sencillo SELECT de clientes, ordenados por nombre y especificando en WHERE
     una edad mínima y máxima
    *******************************************************************/
    void ver_cliente( int edad_min, int edad_max ) {
	try {
	    /***** Definir sentencia y ejecutarla ********/
	    String orden_SQL = "SELECT cliente.codigo, cliente.nombre, cliente.edad " +
			       "FROM cliente " +
			       "WHERE cliente.edad BETWEEN " + edad_min + " AND " + edad_max +
			       " ORDER BY cliente.nombre";
	    Statement sentencia = con.createStatement();
	    ResultSet rs = sentencia.executeQuery( orden_SQL );

	    /*** Recorrer fila a fila el resultado ****/
	    while ( rs.next() ) {
		String res = rs.getString( "codigo" ) + ", " +
				 rs.getString( "nombre" ) + ", " + rs.getInt( "edad" );
		System.out.println( res );
	    }
	    sentencia.close();
	}
	catch (SQLException e) { e.printStackTrace();  }
    }

    /*******************************************************************
     Un poco más de complejidad que el anterior
     El SELECT anterior y además con un JOIN sobre la tabla venta para ver
     las ventas de cada cliente.
    *******************************************************************/
    void ver_cliente_venta( int edad_min, int edad_max ) {
	try {
	    /***** Definir sentencia y ejecutarla ********/
	    String orden_SQL = "SELECT cliente.codigo, cliente.nombre, cliente.edad, " +
			       "venta.precio, venta.coste " +
			       "FROM cliente, venta " +
			       "WHERE cliente.codigo = venta.codigo AND " +
			       "cliente.edad BETWEEN " + edad_min + " AND " + edad_max +
			       " ORDER BY cliente.nombre";
	    Statement sentencia = con.createStatement();
	    ResultSet rs = sentencia.executeQuery( orden_SQL );

	    /*** Recorrer fila a fila el resultado ****/
	    while ( rs.next() ) {
		String res = rs.getString( "codigo" ) + ", " + rs.getString( "nombre" ) +
			     ", " + rs.getInt( "edad" ) + ", " +
			     rs.getDouble( "precio" ) + ", " + rs.getDouble( "coste" );
		System.out.println( res );
	    }
	    sentencia.close();
	}
	catch (SQLException e) { e.printStackTrace();  }
    }

    /*******************************************************************
     Un poco más de complejidad que el anterior
     Hago cálculos (sum) y utilizo la expresión AS.
     Agrupo las ventas por cliente.
    *******************************************************************/
    void ver_beneficio_cliente() {
	try {
	    /***** Definir sentencia y ejecutarla ********/
	    String orden_SQL = "SELECT cliente.nombre, cliente.codigo, sum(venta.precio) " +
		  "AS ingresos , sum(venta.coste) AS costes, sum(venta.precio)-sum(venta.coste) AS beneficio "+
		  "FROM cliente, venta WHERE cliente.codigo = venta.codigo "+
		  "GROUP BY cliente.nombre";

	     Statement sentencia = con.createStatement();
	     ResultSet rs = sentencia.executeQuery( orden_SQL );

	     /*** Recorrer fila a fila el resultado ****/
	     while ( rs.next() ) {
		 String res = rs.getString( "nombre" ) + ", " + rs.getString( "codigo" ) +
		       ", " + rs.getDouble( "ingresos" ) + ", " +rs.getDouble( "costes" ) + ", " +
		       rs.getDouble( "beneficio" );
		 System.out.println( res );
	     }
	     sentencia.close();

	 }
	 catch (SQLException e) { e.printStackTrace();  }
     }
}

El resultado obtenido es:

	Listado de todos los clientes:
	230A, Ana, 33
	55B, Fernando, 28
	105A, José, 21
	Listado de los clientes entre 21 y 30 años:
	55B, Fernando, 28
	105A, José, 21
	Listado de los clientes entre 21 y 30 años con sus ventas correspondientes:
	55B, Fernando, 28, 4090.0, 3450.0
	105A, José, 21, 3500.0, 3180.0
	105A, José, 21, 3600.0, 2980.0
	Listado de ingresos, costes y beneficio de clientes:
	Ana, 230A, 5070.0, 5275.0, -205.0
	Fernando, 55B, 4090.0, 3450.0, 640.0
	José, 105A, 7100.0, 6160.0, 940.0

Algunas consideraciones sobre el ejemplo:

• El proceso para obtener la consulta es siempre el mismo y además es sencillo: definir sentencia, ejecutarla y realizar un bucle para recorrer fila a fila el conjunto de resultados.
  
  
• ver_cliente_venta( int edad_min, int edad_max ): en esta función se listan todos los clientes cuya edad está entre edad_min y edad_max. Pero lo más interesante es que se listan las ventas de CADA cliente (recordar que un cliente puede tener N ventas). Para listar las ventas se especifica la relación que hay entre las dos tablas por medio de la clausula WHERE cliente.codigo = venta.codigo. De esta forma seleccionamos las ventas ASOCIADAS o REFERENCIADAS a su cliente correspondiente.
  
  
• ver_beneficio_cliente(): en esta función hay dos aspectos de interés. Primero, se utiliza la función SUM() para realizar sumatorios. En este SELECT las columnas no son campos de la base de datos, sino operaciones (como SUM(), pero también podriamos usar AVG(), COUNT(), etc). ¿Cómo identificamos estas columnas cuando obtenemos los resultados con getXXX( nom_columna )? Para ello usamos AS nombre_columna que nos sirve para identificar o dar nombre a una columna de la consulta, por ejemplo, con sum(venta.precio)-sum(venta.coste) AS beneficio hacemos que la operación se identifique como 'beneficio' y así podemos obtener el resultado por medio de rs.getDouble( "beneficio" ). El segundo aspecto es que utilizamos GROUP BY para agrupar los sumatorios por cada cliente, es decir, obtenemos ingresos, costes y beneficio asociado a cada cliente.
  
  
• Se puede observar que cerramos la sentencia y la conexión: sentencia.close() y con.close(). Otra opción es confiar en la recolección automática de basura de Java, que cierra el objeto antes de destruirlo.

Tengamos en cuenta que los tipos de SQL no se corresponden de forma exacta (nombre por nombre) con los de Java:

SQL	Java
INT o INTEGER	int
FLOAT	double
DOUBLE	double
CHAR(x) o VARCHAR(x)	String
BOOLEAN	boolean
DATE	Date
TIME	Time
BLOB	java.sql.Blob
CLOB	java.sql.Clob

Uso simultaneo de multiples sentencias (Statement)

Cuando se debe utilizar una consulta compleja, que implica numerosas subconsultas o joins, la tentación más habitual del programador es crear un objeto de la clase Statement para cada subconsulta y cruzar los datos mediante programación, no mediante una macroconsulta. Hay varias razones para no hacerlo así. Primero, hay versiones de productos que sólo admiten un Statement activo. Segundo y más importante, hazte el favor de dejar al gestor de base de datos que realize el trabajo de consultas complejas, siempre lo hará de manera más eficiente que Java.

Puede usar un objeto Statement para diversas sentencias, siempre que su ejecución sea sucesiva, no simultanea:

	Connection conn = getConnection();
	Statement stat = conn.createStatement();
	stat.execute("CREATE TABLE mi_tabla (nombre CHAR(20))");
	stat.execute("INSERT INTO mi_tabla VALUES ('Pedro Perez')");

Pero, si va a ejecutar diversas sentencias, entonces a la hora de gestionar las excepciones debe tener en cuenta que pueden ocurrir varias excepciones. En el siguiente bucle se recogen las posibles excepciones:

	catch (SQLException ex) {
		while (ex != null) {
			ex.printStackTrace();
			ex = ex.getNextException();
		}
	}

Resumiendo lo aprendido sobre la ejecución de sentencias:

• executeQuery( String ): retorna el ResultSet de la sentencia SELECT que se le pasa como argumento. No retorna Null. Si hay un error, se gestiona a través de SQLException.
• executeUpdate( String ): para sentencias que impliquen modificación de datos retorna un entero, que es el número de filas modificadas, cero si no hay cambios en filas.
• execute( String ): si desea ejecutar cualquier sentencia, por ejemplo un DROP TABLE o CREATE TABLE. Retorna un boolean, cuando es true indica que la sentencia genera un ResultSet, false en caso contrario. En el siguiente ejemplo puede ver como se utilizaría (en el capítulo dedicado a metadatos tiene el ejemplo completo):

  
  		....
  		boolean tiene_resultados = s.execute( sentencia );
  		if ( tiene_resultados )
  			ResultSet rs = s.getResultSet( );
  		....
  

Que duda cabe, todo lo que se programa de forma clara es susceptible de convertirlo en algo confuso. Por ejemplo, el siguiente código es sintácticamente correcto, pero es un ejemplo de programación espaguetti (ofuscada). Este tipo de cosas son un ejemplo de solipsismo, es decir, hábitos de programación de gentes incapaces de trabajar en equipo (e incluso consigo mismo):

	ResultSet rs = DriverManager.getConnection( "jdbc:mysql://localhost/prueba","root", 
			"palabra" ).createStatement().executeQuery( "SELECT cliente.codigo, cliente.nombre, cliente.edad " +
			"FROM cliente ORDER BY cliente.nombre" );

Os lo dice una persona que tiene experiencia en equipos de desarrollo de software: los mejores programas no son los más breves (menos líneas de código), sino los más legibles dentro de un contexto de eficiencia. Imaginemos el absurdo de un arquitecto que realiza un plano muy pequeño y fácil de transportar, pero que no puede comprender el resto del equipo (aparejador, maestro de obra, etc) ¿Es esto inteligente? Resulta evidente que la programación es una actividad colectiva, inscrita dentro de un grupo y sus métodos y procesos están destinados a favorecer la "sociabilidad" de los resultados.

Para obtener los resultados de las consultas hemos usado métodos getXXX( String nombre_columna ). Esto es bastante claro, pero que se tranquilicen todos los maniacos de la confusión, podemos hacerlo un poco menos claro, obteniendo los resultados por medio de funciones getXXX( int numero_columna ), donde el argumento indica el orden de columna dentro de la consulta (empezando desde uno, no desde cero).

Las funciones getXXX( int numero_columna ) no tienen mucho sentido cuando el programador conoce la estructura y relaciones de los datos. Sin embargo, en ocasiones hay que programar aplicaciones que accedan a diversas bases de datos y/o acceder a bases de datos desconocidas. En este contexto tiene sentido el uso de dichas funciones. También resultan útiles para obtener metadatos, como veremos en el capítulo dedicado a los metadatos.