Query dinámico por filtros en PL-SQL. Por Criterios Oracle.

Query dinámico por filtros en PL/SQL Oracle y MySQL.

---

Supongamos que tenemos una aplicación Java en donde gestionamos un colegio y hay ciertas GUI en donde aplicamos criterios de búsqueda para obtener información de la base de datos, en este caso la base de datos esta en Oracle PL/SQL y hay una GUI para buscar alumnos mediante criterios o filtros de búsqueda, ya sea por nombre, apellido, sexo, edad, o solo por nombre y apellido, o por sexo y edad, en fin puedes buscar los alumnos de distintas formas. Ya sabiendo eso debemos hacer el Stored Procedure en Oracle PL/SQL en el cual vamos a crear un query dinámico, lo primero que tenemos que saber, es que para manejar la lógica del query tenemos que diferenciar los parámetros por los que el usuario desea buscar un alumno o no, así que desde el aplicativo al momento de hacer el CallableStatement dependiendo de los criterios de búsqueda que haya elegido el usuario en la GUI, vamos a pasar en los parámetros el valor del mismo o un valor null, cuando pasamos null indicamos que no vamos a buscar por ese parámetro, así que cuando armamos el query en Oracle vamos a ir preguntando si el parámetro recibido es distinto de null y de ser el caso vamos agregarlo en el query si no lo ignoramos, ya que en medio del armado del query no podemos utilizar IF ELSE tenemos que hacerlo de la siguiente manera:

Metodos

 
PROCEDURE SP_OBT_ALUMNOS(
i_sexo IN VARCHAR2,
i_nombres IN VARCHAR2,
i_apellidos IN VARCHAR2,
i_edad IN NUMBER,
o_numero_mensaje OUT NUMBER,
o_mensaje OUT VARCHAR2,
o_result_set OUT RESULT_SET
)IS
BEGIN
OPEN o_result_set FOR
  SELECT ALUMNO.ID AS ID_ALUMNO,
   ALUMNO.NOMBRES AS NOMBRES
   ALUMNO.APELLIDOS AS APELLIDOS,
   ALUMNO.EDAD AS EDAD_ALUMNO
FROM USER_DATOJAVA.ALUMNOS ALUMNO
   WHERE ALUMNO.SEXO = i_sexo
   
  --COMIENZAN LOS FILTROS
  --SI TODOS LOS CAMPOS SON DISTINTOS DE NULL, BUSCAR POR TODOS LOS FILTROS
  AND(((i_nombres IS NOT NULL
  AND i_apellidos IS NOT NULL
  AND i_edad IS NOT NULL)
   AND ALUMNO.NOMBRES = i_nombres
   AND ALUMNO.APELLIDOS = i_apellidos
   AND ALUMNO.EDAD = i_edad)
  
 
 -- BUSCAR SOLO POR SEXO Y EDAD
 OR((i_nombres IS NULL
  AND i_apellidos IS NULL
  AND i_edad IS NOT NULL)
   AND ALUMNO.EDAD = i_edad)
 
 -- BUSCAR SOLO SEXO, EDAD Y NOMBRES
 OR((i_nombres IS NOT NULL
  AND i_apellidos IS NULL
  AND i_edad IS NOT NULL)
   AND ALUMNO.EDAD = i_edad
   AND ALUMNO.NOMBRES = i_nombres)
 -- BUSCAR SOLO POR SEXO, EDAD Y APELLIDOS
 OR((i_nombres IS NULL
  AND i_apellidos IS NOT NULL
  AND i_edad IS NOT NULL))
   AND ALUMNO.EDAD = i_edad
   AND ALUMNO.APELLIDOS = i_apellidos
  )
   ORDER BY ALUMNO.SEXO;
o_numero_mensaje := 0;
o_mensaje := 'EXITO';
EXCEPTION
 WHEN OTHERS THEN
   o_numero_mensaje:=-1;
   o_mensaje:=dbms_utility.format_error_backtrace || sqlerrm;
END SP_OBT_ALUMNOS;
    

Como se ve en el SP anterior todo depende de cómo recibimos el parámetro en Oracle PL/SQL, ya de este dato java ustedes se extienden a su manera pero con eso es suficiente para que armen su súper query en Oracle.

Para hacer un query dinámico en MySQL desde Java es un poco distinto pero también es fácil, suponiendo el mismo caso anterior del colegio y el alumno se podría hacer de esta manera:

 public List<Alumno> obtenerAlumnos(String nombres, String apellidos, int edad, String sexo) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();

            StringBuilder query = new StringBuilder();
            query.append("SELECT * FROM ALUMNOS WHERE 1 = 1 ");
            
            //CREAR EL QUERY DINAMICO DEPENDIENDO DE LO QUE NO ES NULL
            if(nombres != null){
             query.append(" AND nombres = ?");
            }
            if (apellidos != null) {
                query.append(" AND apellidos = ?");
            }
            if(edad != 0){
             query.append(" AND edad = ?");
            }
            if (sexo != null) {
                query.append(" AND sexo = ?");
            }
            
            preparedStatement = connection.prepareStatement(query.toString());
            
            int count = 0;
            if(nombres != null){
             count++;
             preparedStatement.setString(count, nombres);
            }
            if (apellidos != null) {
             count++;
             preparedStatement.setString(count, apellidos);
            }
            if(edad != 0){
             count++;
             preparedStatement.setInt(count, edad);
            }
            if (sexo != null) {
             count++;
             preparedStatement.setString(count, sexo);
            }
            resultSet = preparedStatement.executeQuery();
                    
            // .......
  

El truco esta en pasar los parámetros en null y en el contador que se va incrementando para pasar el parámetro correcto en la posición correcta en el PreparedStatement.
Suerte, cualquier cosa comenta, no olvides compartir la entrada.

Temas Relacionados:

• Concurrentmodificationexception. Solucionado.
• CallableStatement con parámetros IN/OUT desde java..
• Tutorial de Aplicación Java Swing, ejemplo simple y sencillo.
• Tutorial de Java con HSQLDB.