0

I'm trying to code multiple SQL queries statements in Java. The following SQL Statement Run without a problem in MySQL command line.

SELECT
    @dispositivoID := MAX(self_id)
FROM
    dispositivo;

SELECT
    @pessoaID := MAX(self_id)
FROM
    pessoa;

INSERT
    INTO
    Utilizador ( nome ,
    email ,
    telefone ,
    pessoa_id ,
    device_id ,
    dt_criacao )
VALUES (?,
?,
?,
@pessoaID,
@dispositivoID,
now());

When I try to code the above SQL statement in Java, I encounter an error.

StringBuilder cmd = new StringBuilder();
cmd.append("SELECT @dispositivoID := MAX(self_id) FROM dispositivo; ");
cmd.append("SELECT @pessoaID := MAX(self_id) FROM pessoa; ");
cmd.append(INSERT);
cmd.append(getSimpleName() + OPEN);
cmd.append( NOME + COMMA + EMAIL + COMMA + TELEFONE + COMMA + PESSOA_ID + COMMA + DISPOSITIVO_ID );
cmd.append(COMMA + DATA_CRIACAO + CLOSE);
cmd.append("values (?, ?, ?, @pessoaID, @dispositivoID, now());");

System.out.println(cmd.toString());


try (PreparedStatement pStmt = DAO.getConnection().prepareStatement(cmd.toString())) {
    pStmt.setString(1, ((modelo.utilizador.Utilizador)dataObject).getNome());
    pStmt.setString(2, ((modelo.utilizador.Utilizador)dataObject).getEmail());
    pStmt.setString(3, ((modelo.utilizador.Utilizador)dataObject).getTelefone());

    pStmt.execute();
    pStmt.close();
    } catch (Exception e) {
        // TODO: handle exception
        e.printStackTrace();
        return false;
    }

The following is the error which is encountered during the run:


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 'SELECT @pessoaID := MAX(self_id) FROM pessoa; INSERT INTO Utilizador ( nome , em' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995)
    at controlo.utilizador.Utilizador.insertObject(Utilizador.java:78)
    at controlo.Run.tableInsert(Run.java:34)
    at controlo.Run.main(Run.java:15)
false

Can someone help me figure out the error?

backdoor
  • 891
  • 1
  • 6
  • 18
Luis Mhula Junior
  • 1
  • Can you debug and check at pStmt.execute(); and see what is the difference between both the statement that may help you to find the error. – backdoor Apr 18 '20 at 04:38
  • 1
    Does this answer your question? [Multiple queries executed in java in single statement](https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement) – Šimon Kocúrek Apr 18 '20 at 08:35
  • yes it answers my question, just applying ***allowMultiQueries=true*** to the data base url connection - https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement – Luis Mhula Junior Apr 18 '20 at 14:12

0 Answers0