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)
阅读 2.4k
1 个回答
新手上路,请多包涵

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

spring.datasource.url=jdbc:数据库地址?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题