玄羽飞
  • 3
  • 新人请关照

jdbc创建MySQL的trigger错误?

如下所示,我用jdbc来创建一个MySQL触发器,navicat里面创建成功,把sql2输出后,在navicat中运行还是成功。但是程序跑起来就会提示创建触发器的语句有语法错误。求指教。
代码:

    Connection conn = JDBCDao.getConnection();
    String sql2 = "DROP TRIGGER IF EXISTS tb_fieldInsert; "
            +"CREATE TRIGGER tb_fieldInsert "
            +"BEFORE INSERT ON tb_table_fields "
            +"FOR EACH ROW "
            +"BEGIN "
            +"DECLARE aInsert VARCHAR(200); "
            +"SET aInsert = new.name; "
            +"INSERT INTO tb_modify_history (tb_id,field,content,time) VALUES (1,aInsert,CONCAT('新增\"',aInsert,'\"字段'),now()); " 
            +"END; ";
    Statement state;
    //PreparedStatement ps;
    try {
        //创建触发器
        state = conn.createStatement();
        state.executeUpdate(sql2);
        ...
    }catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCDao.closeConnection(conn);
    }

报错:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'CREATE TRIGGER tb_fieldInsert BEFORE INSERT ON tb_table_fields FOR EACH ROW' at line 1

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
阅读 616
评论 更新于 2018-04-03
    1 个回答
    玄羽飞
    • 3
    • 新人请关照

    解决了。在数据库配置文件后加:?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true即可。例:

    spring.datasource.url=jdbc:数据库地址?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    评论 赞赏 2019-10-27
      撰写回答

      登录后参与交流、获取后续更新提醒