java - 如何更新 PostgreSQL 数据库中的时间戳列？

Question

我正在尝试更新 PostgreSQL 数据库列。我不知道如何解析并将其发送到与数据类型匹配的数据库？我该怎么做？

这是我的代码:

@Override
public void updateTaskStatus(String applicationNo, String prevTask, String currTask, String taskStatus,
            String user) {

        Connection conn = null;
        PreparedStatement ps = null;
        PreparedStatement ps2 = null;
        PreparedStatement ps3 = null;
        ResultSet rs = null;
        java.util.Date date = new java.util.Date();
        Timestamp timestamp = new Timestamp(date.getTime());

        try {

            conn = ConnectionManager.getConnection();

            String query = "SELECT * " + "FROM public.nt_t_task_det " + "where tsd_app_no=? ;";

            ps = conn.prepareStatement(query);
            ps.setString(1, applicationNo);
            rs = ps.executeQuery();

            if (rs.next() == true) {
                if (!(rs.getString("tsd_task_code").equals(currTask))) {

                    // insert to history table
                    String q = "INSERT INTO public.nt_h_task_his "
                            + "(tsd_seq, tsd_vehicle_no, tsd_app_no, tsd_task_code, tsd_status, created_by, created_date) "
                            + "VALUES(?, ?, ?, ?, ?, ?, ?); ";

                    ps2 = conn.prepareStatement(q);
                    ps2.setInt(1, rs.getInt("tsd_seq"));
                    ps2.setString(2, rs.getString("tsd_vehicle_no"));
                    ps2.setString(3, applicationNo);
                    ps2.setString(4, prevTask);
                    ps2.setString(5, taskStatus);
                    ps2.setString(6, rs.getString("created_by"));
                    ps2.setTimestamp(7, rs.getTimestamp("created_date"));
                    int i = ps2.executeUpdate();
                    conn.commit();
                    try {
                        if (ps2 != null)
                            ps2.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    conn.commit();

                    if (i > 0) {
                        // update task details table
                        String q2 = "UPDATE public.nt_t_task_det "
                                + "SET tsd_task_code=?, tsd_status='O', created_by=?, created_date=?  "
                                + "WHERE tsd_app_no=?";

                        ps3 = conn.prepareStatement(q2);
                        ps3.setString(1, currTask);
                        ps3.setString(2, applicationNo);
                        ps3.setString(3, user);
                        ps3.setTimestamp(4, timestamp);
                        ps3.executeUpdate();
                        conn.commit();

                        try {
                            if (ps3 != null)
                                ps3.close();
                        } catch (Exception e) {
                            e.printStackTrace();
                        }

                        String queueNumber = findQueueNumberFromApplicationNo(conn, applicationNo);
                        if (queueNumber != null && !queueNumber.isEmpty() && !queueNumber.trim().equalsIgnoreCase("")) {
                            updateQueueNumberTaskInQueueMaster(conn, queueNumber, currTask, "O");
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (ps != null)
                    ps.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (ps2 != null)
                    ps2.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                if (conn != null)
                    conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

这是我的控制台中的异常:

org.postgresql.util.PSQLException: ERROR: column "created_date" is of type timestamp without time zone but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

Answer 1

UPDATE 语句的参数与您提供的参数不匹配。

第一个参数用于第一个 ?，第二个参数用于第二个，依此类推。你把订单搞混了。