Validate Java records if there is or not

Question

Validate Java records if there is or not

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

1

I need to know how to validate whether or not there is a record in a database.

This is the search code (there is no error in itself):

public void buscar(String id_usu) {
        try {
            conectar conexion = new conectar();
            Connection con = conexion.getconectar();
            String sentencia = "SELECT * FROM evaluacion_antropometrica WHERE id_usu="+id_usu+" ";
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sentencia);
            while (rs.next()) {
                for (int i = 0; i < 18; i++) {
                    row[i] = rs.getObject(i + 1);
               }
            }
              JOptionPane.showMessageDialog(null,"Identificacion: "+row[1]+"\nPeso :"+row[2]+"\nEstatura:"+row[3]+"\nmedida brazo izquierdo:"+row[4]+"\nmedida brazo derecho:"+row[5]+"\nmedida pecho:"+row[6]+"\nmedida pierna izquierda:"+row[7]+"\nmedida pierna derecha:"+row[8]+"\nmedida cintura:"+row[9]+"\npantorrilla izquierda:"+row[10]+"\npantorrilla derecha:"+row[11]+"\nimc resultado:"+row[12]+"\ncuello:"+row[13]+"\nimcverificar:"+row[14]+"\nMedida gluteos:"+row[15]+"\nMedida antebrazo izquierdo:"+row[16]+"\nMedida antebrazo derecho:"+row[17]);

         //  JOptionPane.showMessageDialog(null,"Estatura usuario: "+row[1]+"\nPeso usuario:"+row[2]+"\nmedida brazo izquierdo:"+row[3]+"\nmedida brazo derecho:"+row[4]+"\nmedida pecho:"+row[5]+"\nmedida pierna izquierda:"+row[6]+"\nmedida pierna derecha:"+row[7]+"\nmedida cintura:"+row[8]+"\npantorrilla izquierda:"+row[9]+"\npantorrilla derecha:"+row[10]+"\nimcresultado:"+row[11]+"\ncuello:"+row[12]+"\nimc verificar:"+row[13]+"\nIdentificacion:"+row[14]+"\nMedida gluteos:"+row[15]);
            conexion.desconectar();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    } // cierra buscar

mysql java

asked by DANIEL FELIPE LOPEZ VARGAS 13.06.2017 в 05:47

source

3 answers

mysql - check if a select query is greater than 0 I use a Thread Runnable or an Intent Service?

score 1 · Answer 1

At performance level, doing a SELECT * ... to verify if a record exists is a bad practice.

Suppose a table with 10, 20 or 150 columns ... you are selecting all columns only to determine if there are records!

I would create a method, which might be part of my class Connection (the one I use to connect to the database), which receives two parameters: an SQL statement and the value to be filtered. The same method can be adapted to receive SQL statements with or without data to be filtered, or to filter based on several criteria instead of just one, passing the parameters in an array. That way you would use that method every time you need to verify if a record exists in the DB .

So, in the class Connection ... or whatever it's called, you could create a method like this:

public boolean ifExists(String sSQL, int nId) {
    PreparedStatement ps = dbConn.prepareStatement(sSQL);
    ps.setString(1, id);
    ResultSet rs = ps.executeQuery();
    return rs.next();
}

We use the method next() of the Class Resultset which will return false if it does not find records or otherwise return true .

Example of use:

If (db.ifExists ("Select 1 from nombteTabla where id = ?;")){
//Existe registro
}else{
//No hay registros
}

db would be the object Conexion , the same that you would use to connect to the BD
SELECT 1 ... is one of the ways to simplify a query when you only want to evaluate if there are records. There are others such as SELECT COUNT(*) , select the id, etc ... Depending on the BD handler and the structure of the tables there may be a slight variation in performance depending on whether you use one or the other.

score 1 · Answer 2

I do it in the following way I hope it helps you is simple

public  boolean dbexisteRegistro(Connection Conn, String id_a_buscar){
         Statement oSt = null;
         ResultSet oRs = null;
         String sSQL= " ";
         boolean dbexisteRegistro= false; 

         try{
             Conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
             sSQL = "SELECT * FROM Nombre_de_tu_tabla WHERE ID_A_BUSCAR='" + id_a_buscar + "'";


             oSt = Conn.createStatement();
             oRs = oSt.executeQuery(sSQL);

             if(oRs.next()){
                if(oRs.getRow() > 0){
                    dbexisteRegistro= true;
                }
             }

             if (oSt != null) {oSt.close();oSt = null;}
             if (oRs != null) {oRs.close();oRs = null;}
         }catch(SQLException err){

             oSt = null;
             oRs = null;
             sSQL=null;
         }catch(Exception err){

             oSt = null;
             oRs = null;
             sSQL=null;  
         }finally{
             oSt = null;
             oRs = null;
             sSQL=null;
         }
         return dbexisteRegistro;
 }

score 0 · Answer 3

        Connection con = new Conexion().getConexion();
        Statement stm = con.createStatement();
        ResultSet rs = stm.executeQuery("SELECT * FROM tabla WHERE campo_id = 1");
        if(rs.last()){
            //Existe
        }
        else{
            //No Existe
        }