如下所示,我用jdbc来创建一个MySQL触发器,自己在navicat里面实验可以成功,但是程序跑起来就会提示创建触发器的语句有语法错误。求指教
scala
def 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) } }
试试这样呢
stmt.executeUpdate(ctbsql);
stmt.executeUpdate(tbtrigger);
另外确认当前连接mysql的账号是否有create、drop等权限。
貌似使用jdbc有些DDL语句是不能提交到服务器端执行的, 比如create table, drop 之类的.
在jdbc的底层限制了这种所谓特权语句的传递.
我建议你调用一个存储过程, 在其中完成这类操作.