If I execute my query through phpMyAdmin it works while it doesn't in my java program

Question

I'm new to java and for an exam, I have to make an app that's a store for a book shop but I'm having problems with the database part of the app: my prepared statement isn't working while if I copy the query generated from it and paste it into phpMyAdmin it works perfectly.

The error the app is giving me is:

java.sql.SQLSyntaxErrorException: 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 'SET @address = LAST_INSERT_ID(); INSERT INTO utenti (email, password, nome, cogn' at line 1

The method that generates the error

public static void createUser(String email, String password, String nome, String cognome, long telefono, String indirizzo, int cap, String città) throws Exception{
        Connection conn = MysqlConnection.getConnection();

        try{
            st = conn.prepareStatement("INSERT INTO indirizzi (indirizzo, cap, città) "
                    + "VALUES (?,?,?); "
                    + "SET @address = LAST_INSERT_ID(); "
                    + "INSERT INTO utenti (email, password, nome, cognome, telefono, idindirizzo) "
                    + "VALUES (?, ?, ?, ?, ?, @address);");
            st.setString(1, indirizzo);
            st.setInt(2, cap);
            st.setString(3, città);
            st.setString(4, email);
            st.setString(5, password);
            st.setString(6, nome);
            st.setString(7, cognome);
            st.setLong(8, telefono);

            System.out.println(st);

            st.executeUpdate();
        }
        catch(Exception e){
            throw e;
        }
        finally{
            try{
                conn.close();
            }
            catch(SQLException e){
                throw e;
            }
        }
    }

The method where it's used (just a test method to see if the other one works)

    private static void createUser()
    {
        try {
            ModelUser.createUser("a@a.com", "qwertyasdfg", "Aldo", "Simone", 391234567890l, "via dietro 1/g", 37051, "Sì");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

The generated sql

INSERT INTO indirizzi (indirizzo, cap, città) VALUES ('via dietro 1/g',37051,'Sì'); SET @address = LAST_INSERT_ID(); INSERT INTO utenti (email, password, nome, cognome, telefono, idindirizzo) VALUES ('a@a.com', 'qwertyasdfg', 'Aldo', 'Simone', 391234567890, @address);

A little edited for readability

INSERT INTO indirizzi (indirizzo, cap, città)
VALUES ('via dietro 1/g',37051,'Sì');
SET @address = LAST_INSERT_ID();
INSERT INTO utenti (email, password, nome, cognome, telefono, idindirizzo)
VALUES ('a@a.com', 'qwertyasdfg', 'Aldo', 'Simone', 391234567890, @address);

I think that this should be working since the query itself is working when it's put into phpMyAdmin

Check https://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement ... if you want to use multiple queries you need to configure your jdbc driver correctly. — second, Jul 20 '19 at 09:44
Thank you very much! I spent all morning searching for an answer and couldn't find it. It worked right away! — Davide Pozzani, Jul 20 '19 at 09:52
However you should consider using only one query per statement as that is generally more readable. (I think instead of using @address you should be able to call LAST_INSERT_ID() directly in the 2nd query). — second, Jul 20 '19 at 09:53
I thought the LAST_INSERT_ID() would output the last id of "utenti" instead. I'll try that also! Thank you very much! — Davide Pozzani, Jul 20 '19 at 09:59

Gro · Accepted Answer · 2019-07-21T02:04:26.127

You are actually trying to execute two queries with one prepared statement. change your method like following

public static void createUser(String email, String password,
          String nome, String cognome, long telefono, String indirizzo,
          int cap, String città) throws Exception{

    Connection conn = MysqlConnection.getConnection();

    try{
        PreparedStatement st = conn.prepareStatement("INSERT INTO indirizzi
                (indirizzo, cap, città) VALUES (?,?,?)");
        st.setString(1, indirizzo);
        st.setInt(2, cap);
        st.setString(3, città);
        st.executeUpdate();

        /* You may want to close PreparedStatement here */

        st = conn.prepareStatement("INSERT INTO utenti
              (email, password, nome, cognome, telefono, idindirizzo)
              VALUES (?, ?, ?, ?, ?, LAST_INSERT_ID())");        
        st.setString(1, email);
        st.setString(2, password);
        st.setString(3, nome);
        st.setString(4, cognome);
        st.setLong(5, telefono);
        st.executeUpdate();
         /* You may want to close PreparedStatement here */
    }catch(Exception e){
        throw e;
    }finally{
        try{
            conn.close();
        }
        catch(SQLException e){
            throw e;
        }
    }
}

If I execute my query through phpMyAdmin it works while it doesn't in my java program

1 Answers1