提问者:小点点

如何将包含'的字符串保存到PostgreSQL中


我在使用Java的应用程序中使用PostgreSQL作为数据库。当我尝试将字符串插入text类型的字段时,如果字符串包含',则会引发异常。我得到的异常是:

Hibernate: update public.slider_group set slid_grou_title=?, slid_grou_short_desc=?, site_id=?, status=?, update_date=?, no_slide=?, pos_type=?, widget_type=?, custom_html=? where slid_grou_id=?
20:47:12.476 [http-nio-8080-exec-7] ERROR o.h.util.JDBCExceptionReporter - Batch entry 0 update public.slider_group set slid_grou_title= was aborted. Call getNextException() to see the cause.
20:47:12.476 [http-nio-8080-exec-7] ERROR o.h.util.JDBCExceptionReporter - ERROR: syntax error at or near "\"

20:47:12.522 [http-nio-8080-exec-7] ERROR o.h.e.d.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) ~[hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) ~[hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) ~[hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) ~[hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:180) ~[hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) ~[hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51) [hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206) [hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375) [hibernate-core-3.5.1-Final.jar:3.5.1-Final]
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137) [hibernate-core-3.5.1-Final.jar:3.5.1-Final]

我没有在这里添加整个异常消息。有什么方法可以将字符串保存到数据库中,即使它包含'

这是Hibernate查询,对象“widgetContentTo”具有包含要保存到DB相应字段中的字符串的字段

public void saveOrUpdateWidgetContent(WidgetContentTo widgetContentTo) throws HibernateException {
    try {
        getCurrentSession().saveOrUpdate(widgetContentTo);
    } catch (Exception ex) {
        System.out.println(ex);
    }
}

与我尝试保存到数据库的对象对应的类:

  public class WidgetContentTo
    {
        private int             widgetContentId;
        private int     siteId;
        private String      widgetContentName;
        private String      widgetContentShortDesc;
        private int             noWebcontents;
        private String      status;
        private Timestamp   timeCreated;
        private Timestamp   updateDate;
        private String      posType;
        private String      widgetType;
        private String      customHtml;
    }

如果最后一个字段CustHtml包含单引号,我得到一个异常。我尝试替换单引号,如下所示:

   String data = widgetContentTo.getCustomHtml();
    data = data.replace("'", "\'");
    widgetContentTo.setCustomHtml(data);

数据库中表的名称为slider_groupCustHtml对应的字段为custom_html和datatypetext


共1个答案

匿名用户

如果您不依赖任何东西来为您转义,您要查找的转义字符是:\

示例:

SELECT  trequests.reqid, 
FROM lngprtl.tbranches
WHERE tbranches.branchid = '\'test\''

上面的选择将返回分支ID等于'test'的结果