Java create & update mysql table syntax error

Question

I'm not sure how to fix this. I tested the SQL statement in MySQLWorkbench and it works.

    public void updateTable() throws SQLException
    {
        String query  = "SET SQL_SAFE_UPDATES = 0; drop table if exists studentCopy; create table studentCopy select * from student;\n" +
                "update studentCopy join (select ID, sum(credits) new_tot_cred\n" +
                "from takes left join course using(course_id) where grade is not null and grade<>'F'\n" +
                "group by ID) cred using(ID) set tot_cred=cred.new_tot_cred;\n" +
                "SET SQL_SAFE_UPDATES = 1;\n" +
                "select * from studentCopy;";

        resultSet = statement.executeQuery(query);
    }

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'drop table if exists studentCopy; create table studentCopy select * from student' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1198)
    at MyQuery.updateTable(MyQuery.java:157) // resultSet = statement.executeQuery(query);
    at TestMyQuery.main(TestMyQuery.java:38) // main method call

This query worked in workbench.

SET SQL_SAFE_UPDATES = 0;
drop table studentCopy;
create table studentCopy select * from student;
update studentCopy join (select ID, sum(credits) new_tot_cred
from takes left join course using(course_id) where grade is not null and grade<>'F'
group by ID) cred using(ID) set tot_cred=cred.new_tot_cred;
SET SQL_SAFE_UPDATES = 1;
select * from studentCopy;

execute 1 sql statement at a time. Put them into an array and then loop the execution. — danblack
– danblack, Commented Mar 20, 2021 at 5:56
What do you think SQL_SAFE_UPDATES does? Why copy the table? Aren't you just after a SELECT form of your UPDATE query? — danblack
– danblack, Commented Mar 20, 2021 at 5:58
This is for an assignment. I want to demonstrate that I know how to update a table without it affecting existing tables. — Kaykay38
– Kaykay38, Commented Mar 20, 2021 at 22:40

Milind Modi · Accepted Answer · 2021-03-20 06:52:51Z

You can use executeBatch() to execute multiple queries at the same time in the JDBC.

you can create separate query string of each queries you want to execute and add those in to you batch. for example here i created the 4 queries

String sql0 = "SET SQL_SAFE_UPDATES = 0;";
String sql1 = "insert into employees(first,last,age) values('Foo','Bar',18);";
String sql2 ="create or replace table employeesCopy as select  id, first, last, age  from employees;";
String sql3 ="update employees set first='changed' where id = 1;";

you can add these queries in to your statement object

 con.setAutoCommit(false); //to make sure data doesn't update if exception  occurs
 Statement stmt =  con.createStatement();
 stmt.addBatch(sql0);
 stmt.addBatch(sql1);
 stmt.addBatch(sql2);
 stmt.addBatch(sql3);

and after adding each queries in you batch you can execute the batch

 stmt.executeBatch();
 con.commit(); // commiting all the data

Limitations : you cannot execute select queries in the batch

if there are other limitation that i have no idea

This helped. I used an array and a for loop to addBatch queries, and used a separate executeQuery to return the select values.

Nagdatt Gajjam · Accepted Answer · 2021-03-20 06:40:09Z

Do not copy as it is from workbench.insted of this execute query one by one in loop.

public void updateTable() throws SQLException {
  String query[] = {
    "SET SQL_SAFE_UPDATES = 0;",
    "drop table if exists studentCopy; ",
    "create table studentCopy select * from student;",
    "update studentCopy join (select ID, sum(credits) new_tot_cred;",
    "from takes left join course using(course_id) where grade is not null and grade<>'F';",
    "group by ID) cred using(ID) set tot_cred=cred.new_tot_cred;",
    "SET SQL_SAFE_UPDATES = 1;",
    "select * from studentCopy;"
  };
  for (int i = 0; i < query.length(); i++) {
    resultSet = statement.executeQuery(query[i]);
  }
}

  
 

<!-- begin snippet: js hide: false console: true babel: false -->

This was the error when I implemented your solution. Can not issue data manipulation statements with executeQuery(). addBatch and executeBatch worked though.

Collectives™ on Stack Overflow

Java create & update mysql table syntax error

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related