SQL 查询中的 java.lang.NullPointerException

Question

我有一个 SQL 查询，它将数据插入到 Oracle 行中。当我运行该语句时，我得到了 NPE。

public int saveData(int result) throws SQLException, java.text.ParseException, NoSuchAlgorithmException {

    String SqlStatement = null;

    if (ds == null) {
        throw new SQLException();
    }

    Connection conn = ds.getConnection();
    if (conn == null) {
        throw new SQLException();
    }

    PreparedStatement ps = null;

    /*

    CREATE TABLE USERS(
        USERID INTEGER NOT NULL,
        GROUPID INTEGER,
        SPECIALNUMBER VARCHAR2(60 ),
        USERNAME VARCHAR2(50 ),
        PASSWD VARCHAR2(50 ),
        DATETOCHANGEPASSWD DATE,
        ADDRESS VARCHAR2(60 ),
        STATEREGION VARCHAR2(50 ),
        COUNTRY VARCHAR2(50 ),
        USERSTATUS VARCHAR2(30 ),
        TELEPHONE VARCHAR2(50 ),
        DATEUSERADDED DATE,
        USEREXPIREDATE DATE,
        DATEUSERLOCKED CHAR(20 ),
        CITY VARCHAR2(50 ),
        EMAIL VARCHAR2(50 ),
        DESCRIPTION CLOB
    )
   */

    try {
        conn.setAutoCommit(false);
        boolean committed = false;
        try {           
            String getTimeStampTemplateSQL = "(select (select settingvalue from globalsettings where settingname = 'DateFormat') || ' ' ||"
                                             + "(select settingvalue from globalsettings where settingname = 'TimeFormat') from dual)";

            /* insert into Oracle the default system(Linux) time */
            SqlStatement = "INSERT INTO USERS VALUES (?, ?, ?, ?, ?, to_timestamp(?, " 
                    + getTimeStampTemplateSQL + "), ?, ?, ?, ?, ?, SYSTIMESTAMP, to_timestamp(?, " + getTimeStampTemplateSQL +
                    "), ?, ?, ?, ?)";

            ps = conn.prepareStatement(SqlStatement);

            ps.setString(1, formMap.get("USERID"));
            ps.setString(2, formMap.get("GROUPID"));
            ps.setString(3, formMap.get("SPECIALNUMBER"));
            ps.setString(4, formMap.get("USERNAME"));
            ps.setString(5, passwdConvert(formMap.get("PASSWD")));
            ps.setString(6, formMap.get("DATETOCHANGEPASSWD").trim().length() == 0 ? null : formMap.get("DATETOCHANGEPASSWD").trim() + " " + formMap.get("HOURTOCHANGEPASSWD").trim());
            ps.setString(7, formMap.get("ADDRESS"));
            ps.setString(8, formMap.get("STATEREGION"));
            ps.setString(9, formMap.get("COUNTRY"));
            ps.setString(10, formMap.get("USERSTATUS"));

            // If the user set Account Status to Blocked insert SYSTIMESTAMP into DATEUSERLOCKED
            if ((formMap.get("USERSTATUS")!=null) && (formMap.get("USERSTATUS")).equals("Blocked")){
                formMap.put("DATEUSERLOCKED", "SYSTIMESTAMP");
            }               

            ps.setString(11, formMap.get("TELEPHONE"));
            ps.setString(12, formMap.get("USEREXPIREDATE").trim().length() == 0 ? null : formMap.get("USEREXPIREDATE").trim() + " " + formMap.get("USEREXPIREHOUR").trim());

            //ps.setString(13, formMap.get("DATEUSERLOCKED").trim().length() == 0 ? null : formMap.get("DATEUSERLOCKED"));

            ps.setString(13, ((formMap.get("DATEUSERLOCKED")==null) || ((formMap.get("DATEUSERLOCKED")!=null) && (formMap.get("DATEUSERLOCKED").trim().length()==0)))? null : formMap.get("DATEUSERLOCKED"));

            ps.setString(14, formMap.get("CITY"));
            ps.setString(15, formMap.get("EMAIL"));
            ps.setString(16, formMap.get("DESCRIPTION"));


            ps.executeUpdate();

            conn.commit();
            committed = true;
        }
        finally 
        {
            if (!committed) {
                conn.rollback();
            }
        }
    } finally {
        /* Release the resources */
        ps.close();
        conn.close();
    }
    // For JGrown message
    FacesContext context = FacesContext.getCurrentInstance();
context.addMessage(null, new FacesMessage("Successful", "User " + formMap.get("USERID") + " is created!"));

    return result;
}

我收到此错误:

javax.faces.FacesException: #{AddAccountController.saveData(1)}: java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

    at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:118)
    at javax.faces.component.UICommand.broadcast(UICommand.java:315)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1259)
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:593)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
    at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
    at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
    at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
    at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
    at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
    at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
    at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
    at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
    at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
    at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
    at java.lang.Thread.run(Thread.java:722)
Caused by: javax.faces.el.EvaluationException: java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:102)
    at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
    ... 31 more
Caused by: java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1044)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3665)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1352)
    at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
    at com.DX_57.AC_57.AddAccount.saveData(AddAccount.java:207)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at javax.el.BeanELResolver.invokeMethod(BeanELResolver.java:779)
    at javax.el.BeanELResolver.invoke(BeanELResolver.java:528)
    at javax.el.CompositeELResolver.invoke(CompositeELResolver.java:257)
    at com.sun.el.parser.AstValue.invoke(AstValue.java:248)
    at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:302)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
    ... 32 more
|#]

你能帮我解决这个问题吗？也许准备好的语句的顺序不正确？

P.S问题在这里:

ps.setString(13, formMap.get("DATEUSERLOCKED").trim().length() == 0 ? null : formMap.get("DATEUSERLOCKED"));

Answer 1

(作为一般规则)，您可以重写以下行以更安全:

if ((formMap.get("USERSTATUS")).equals("Blocked")) {

假设formMap != null，最好这样写:

if ("Blocked".equals(formMap.get("USERSTATUS"))) {

在这种情况下，即使 formMap 不包含键 USERSTATUS，您也是安全的。