Search between dates JDateChoser java mysql

Assuming that the values of the variables are filled in correctly, it is possible that you are missing spaces in the statement and that is why it is malformed, try this:

String sSQL = "SELECT medicamento.codigo, medicamento.nombreMed, categoria.nombre, detalle_compra.cantidad, detalle_compra.totalFinal, compra.totalC"
                + " FROM detalle_compra INNER JOIN medicamento ON detalle_compra.idMedicamento = medicamento.idMedicamento"
                + " INNER JOIN compra ON detalle_compra.idCompra = compra.idCompra INNER JOIN categoria ON medicamento.idcategoria = categoria.idCategoria"
                + " WHERE compra.fechaCompra BETWEEN '%" + p_fecha_Desde + "%' AND '%" + p_fecha_Hasta + "%' ";

In these cases you can print the sSQL variable for example by console and check that the statement works in the database.

I see three problems in the code:

I allow myself to abound on the third problem because it is the most serious of all and in the end I provide a solution to the three problems.

The documentation recommends using prepared queries when handling data that may come from the outside , to avoid SQL injection in this way.

If for example the value of p_fecha_Desde or p_fecha_Hasta are collected in a form and you pass the query as you are doing now you could have an injection:

• If in p_fecha_Desde the malicious user writes: 2017-07-01 AND 2017-07-20'; ALTER TABLE detalle_compra; ALTER TABLE medicamento; -- The query passed thus would delete the tables detalle_compra and medicamento .

• Or if in p_fecha_Hasta the malicious user writes: 2017-07-20'; ALTER TABLE detalle_compra; ALTER TABLE medicamento; UPDATE usuarios SET password='adminhackeado' WHERE usuario='admin' -- Not only would the previous tables be deleted, but the malicious user would change the user's key admin if it existed.

• Worse things could happen if they inject you with code, like sending commands to the command line from SQL.

In this way, any type of query to the BD is possible, since you have a wide and open door to enter your system.

To prevent that, use prepared queries, which also help you to correctly send data according to its type.

Let's see a solution to the three problems:

String sSQL = "SELECT medicamento.codigo, medicamento.nombreMed, categoria.nombre, detalle_compra.cantidad, detalle_compra.totalFinal, compra.totalC"
            + " FROM detalle_compra INNER JOIN medicamento ON detalle_compra.idMedicamento = medicamento.idMedicamento"
            + " INNER JOIN compra ON detalle_compra.idCompra = compra.idCompra INNER JOIN categoria ON medicamento.idcategoria = categoria.idCategoria"
            + " WHERE compra.fechaCompra BETWEEN ? AND ?";
    try {

        PreparedStatement ps = cn.prepareStatement(sSQL);
        ps.setDate(1, java.sql.Date.valueOf(p_fecha_Desde));
        ps.setDate(2, java.sql.Date.valueOf(p_fecha_Hasta));

        try (ResultSet rs = ps.executeQuery(sSQL)) {
            while (rs.next()) {
                registro[0] = rs.getString("codigo");
                registro[1] = rs.getString("nombreMed");
                registro[2] = rs.getString("nombre");
                registro[3] = rs.getString("cantidad");
                registro[4] = rs.getString("totalFinal");
                registro[5] = rs.getString("totalC");

                totalRegistro = totalRegistro + 1;
                modelo.addRow(registro);
            }
        }

    } catch (SQLException e) {
        JOptionPane.showMessageDialog(null, e, "Advertencia", JOptionPane.PLAIN_MESSAGE, icono);
        return null;
    }
    return modelo;
}