CODEWITHSUNDEEP
javamysqlsqldatabasesql-order-by
Alex Yánez
Alex Yánez

Reputation: 41

I am trying to extract the last value from a relational table according to a parameter

I am trying to extract the last value from a relational table according to a parameter and it gives me this error

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1 * FROM version WHERE codeProject = '7' ORDER BY idVersion DESC' at line 1

public class VersionDAO {
    ConexionSQL conectar = new ConexionSQL();
    Connection con;
    PreparedStatement ps;
    ResultSet rs;
    String changeVersion;

    public Version buscarVer(int bus, Proyecto p) {
        String sql = "SELECT TOP 1 id, name FROM version WHERE codeProject = '"+ bus +"' ORDER BY idVersion DESC";

        try {
            con = conectar.getConexionSQL();
            ps=con.prepareStatement(sql);
            rs=ps.executeQuery();
            Version ver = new Version();
            while (rs.next()) {
                ver.setIdversion(rs.getInt(1));
                ver.setNameVersion(rs.getString(2));
                changeVersion = rs.getString(2);
            }       
            return ver;
        } catch(Exception e){
            e.printStackTrace();
        }
        return null;
    }   

}

Upvotes: 1

Views: 36

Answers (1)

GMB
GMB

Reputation: 222582

MySQL does not support TOP 1, you need LIMIT 1 instead - and it goes at the end of the query rather, after the ORDER BY clause, than after the SELECT keyword.

SELECT id, name 
FROM version 
WHERE codeProject = ? 
ORDER BY idVersion DESC LIMIT 1

Note that you should be using paramaterized statements rather than munging the variable into the query string: this makes the query more efficient, handles escaping if necessary (that is, if your parameter contains an embedded quote for example), and protects your code from SQL injection.

Upvotes: 1

Related Questions