Java need timestamp in dd-MMM-YY hh:mm:ss.SSSSSSSSS for Oracle Timestamp(6)

Question

I'm trying to get the timestamp so that I can add to my Oracle DB. The DB has a TIMESTAMP(6) value. I have been able to input a value directly with a query with a test date of '12-SEP-12 10:31:19'

Now in java, I have the following:

Timestamp time_submitted = new Timestamp(System.currentTimeMillis());
DateFormat dateFormat = new SimpleDateFormat("dd-MMM-YY hh:mm:ss.SSSSSSSSS");

dateFormat.format(time_submitted);
System.out.println("time_submitted= " + time_submitted);

I keep getting something in this format:

time_submitted= 2017-07-02 22:59:54.733

And when I try to add it to my DB query with a prepared statment, I get the exception:

java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("PROJECT1"."ERS_REIMBURSEMENTS"."REBS_SUBMITTED")

Any idea?

EDIT:

Thank you all for your feedback. Here's the rest of my code along with what I was trying to accomplish:

FrontController.java file
// rebs_id IS AUTO INCREMENTING
int user_id = Integer.parseInt(request.getParameter("author_id"));  // must cast string to int
//int man_id
int rebs_type = Integer.parseInt(request.getParameter("type"));
int rebs_status = Integer.parseInt(request.getParameter("status"));
double rebs_amount = Double.parseDouble(request.getParameter("amount"));
String rebs_description = request.getParameter("description");
//Blob rebs_attachments = getBlob(request.getPart("attachments"));
Timestamp time_submitted = new Timestamp(System.currentTimeMillis());
// Timestamp time_resolved

DateFormat dateFormat = new SimpleDateFormat("dd-MMM-YY hh:mm:ss.SSSSSSSSS");
dateFormat.format(time_submitted);

RebsObj newReb = new RebsObj(user_id, rebs_type, rebs_status, rebs_amount, rebs_description, time_submitted);
RebsDAO dao4 = new RebsDAOImpl();

dao4.createReimbursement(newReb);  // sending newEmp object to RebsDAOImpl for creating

session = request.getSession(); // grabs the session from request
session.setAttribute("rebs_id", newReb.getRebsId());
session.setAttribute("rebs_user_id", newReb.getUserId());
session.setAttribute("rebs_type", newReb.getRebsType());
session.setAttribute("rebs_status", newReb.getRebsStatus());
session.setAttribute("rebs_amount", newReb.getRebsAmount());
session.setAttribute("rebs_description", newReb.getRebsDescription());
session.setAttribute("time_submitted", newReb.getTimeSubmitted());

RebsDAOImpl.java :

public void createReimbursement(RebsObj reb) {

    // creating PS which will run queries
    PreparedStatement ps = null;

    // looks in util/ConnectionUtil.java and saves the url, username and password to "conn"
    try(Connection conn = ConnectionUtil.getConnection();){ 

        int rebs_id = 0; // REBS_ID IS AUTO INCREMENTING
        int user_id = reb.getUserId();
        //int man_id = reb.getManagerId(); // not needed
        int rebs_type = reb.getRebsType();
        int rebs_status = reb.getRebsStatus();
        double rebs_amount = reb.getRebsAmount();
        String rebs_description = reb.getRebsDescription();
        // Blob rebs_attachments
        Timestamp time_submitted = reb.getTimeSubmitted();      
        // Timestamp time_resolved

        System.out.println("TIME STAMP IN DAO: " + time_submitted);

        // you can put this string 'sql' into multiple lines by adding +, and having everything within ""
        // this sql line will be ran on SQL
        String sql = "INSERT INTO ERS_REIMBURSEMENTS(rebs_id, user_id_author, user_id_resolver, "
                + "rebs_type, rebs_status, rebs_amount, rebs_description, rebs_receipt, "
                + "rebs_submitted, rebs_resolved) " 
                + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
                + "RETURNING rebs_id INTO ?";

        // creating prepared statement
        ps = conn.prepareStatement(sql);  // uses connection to send string as a prepared statement
        ps.setString(1, null);   // REBS_ID IS AUTO INCREMENTING
        ps.setInt(2, user_id);
        ps.setString(3, null);
        ps.setInt(4, rebs_type);
        ps.setInt(5, rebs_status);
        ps.setDouble(6, rebs_amount);
        ps.setString(7, rebs_description);
        ps.setString(8, null);
        ps.setTimestamp(9, time_submitted);
        ps.setString(10, null);
        ps.setInt(11, rebs_id);

        reb.setRebsId(rebs_id);
        System.out.println("in DAO, rebs_id: " + rebs_id);
        // rows affected
        int affected = ps.executeUpdate();
        System.out.println("Rows inserted: " + affected);

Also bonus question (which I'm already trying to resolve), I'm trying to "RETURN rebs_id" (because it's being auto incremented) but I can't seem to get that

You don't need to format the `java.sql.Timestamp` object in order to pass it to Oracle. However, you have not shown how you actually are attempting to do this, so the question is off-topic until you show the code where you populate the `PreparedStatement` variables. — Jim Garrison, Jul 03 '17 at 03:11
You cannot put nine decimal places into a time stamp defined with limit of six. — Basil Bourque, Jul 03 '17 at 03:31
The error message `cannot insert NULL into` … hardly comes from an incorrect format. — Ole V.V., Jul 03 '17 at 04:25
A `Timestamp` doesn’t have and cannot have a format. Neither does it need one, as @JimGarrison already said. — Ole V.V., Jul 03 '17 at 04:26
Possible duplicate of [How to get Date in the same format as String](https://stackoverflow.com/questions/30360089/how-to-get-date-in-the-same-format-as-string) — Ole V.V., Jul 03 '17 at 04:27
As an aside, for inserting a timestamp into your database you should prefer the modern class `Instant` over the not-good old `Timestamp`. — Ole V.V., Jul 03 '17 at 04:29
The others are correct. That error message is **not** the result of a wrong "format". You have to show us the Java code that inserts the row into the database. **[Edit]** your question - do **not** post code or additional information in comments. — , Jul 03 '17 at 09:40
Thank you everyone. I have added my code so you guys can see it — Lucas Costa, Jul 03 '17 at 17:38
Have you checked that `time_submitted` is non-null when `createReimbursement` gets called? Also note that your code is not how you'd retrieve a generated id in Java/JDBC. — Mark Rotteveel, Jul 04 '17 at 14:00

score 1 · Answer 1 · answered Jul 03 '17 at 03:21

1

If you do not need the timestamp value for anything other than populating a column in a row, then you do not need to call System.currentTimeMillis(). Instead, all you need is to use the SYSTIMESTAMP pseudocolumn. But even if you do need the value you can use the RETURNING clause of the DML command and you can get back what Oracle used.

answered Jul 03 '17 at 03:21

Jeff Holt

2,940
3
22
29

It’s certainly an option. In case the computers where the database and the Java client run are just slightly out of synch, it will not give the same result, though. – Ole V.V. Jul 03 '17 at 05:58
@Ole But you can easily convert from the db's timestamp to a local timestamp even if the two machines are not in the same timezone (so long long as the timestamp has a timezone). But any architect would discourage separating the db server from the app server farm. They need to be in the same data center with a disaster recover site elsewhere that includes its own db server and app server farm. Be that as it may, I would always want to prefer the db server's timestamp over any other's. – Jeff Holt Jul 03 '17 at 13:41
Thank you everyone. I have added my code in case you could check that out – Lucas Costa Jul 03 '17 at 17:39

Java need timestamp in dd-MMM-YY hh:mm:ss.SSSSSSSSS for Oracle Timestamp(6)

1 Answers1