使用jdbc创建MySQL的trigger出现错误

Question

如下所示，我用jdbc来创建一个MySQL触发器，自己在navicat里面实验可以成功，但是程序跑起来就会提示创建触发器的语句有语法错误。求指教

scaladef creatTable(tbname:String): Unit = {
    val conn: Connection = getConn
    if (conn != null) {
      val stmt: Statement = conn.createStatement
      val ctbsql: String = "create table if not exists " + tbname + "(" +
        "id bigint(20) not null primary key," +
        "name varchar(255) not null," +
        "price double," +
        "pricechangetoday double," +
        "category varchar(20));"
      val tbtrigger = "drop trigger if exists "+tbname+"pricechange" +
        " create trigger "+tbname+"pricechange before update on " + tbname + " for each row" +
        " begin" +
        " set new.pricechangetoday=new.price-old.price;" +
        " end "
      stmt.execute(ctbsql)
      stmt.execute(tbtrigger)
    }
  }

Answer 1

试试这样呢

stmt.executeUpdate(ctbsql);
stmt.executeUpdate(tbtrigger);

另外确认当前连接mysql的账号是否有create、drop等权限。

Answer 2

貌似使用jdbc有些DDL语句是不能提交到服务器端执行的, 比如create table, drop 之类的.
在jdbc的底层限制了这种所谓特权语句的传递.
我建议你调用一个存储过程, 在其中完成这类操作.