CODEWITHSUNDEEP
javadatetimejdbcsql-timestamp
Krittapak Khundilokchock
Krittapak Khundilokchock

Reputation: 11

Java SQL Date Conversion Issue: Year Displays Incorrectly

I am experiencing an issue with retrieving dates from my SQL database using Java. The year returned is showing as 2024 in the database, but when I fetch it through my DAO, the year is converted to 2024 - 543 = 1481. I have confirmed that the SQL query returns the correct year (2024).

Here's a snippet of my DAO and Service code:

@Override
public List<Receipt> getAll() {
    ArrayList<Receipt> list = new ArrayList<>();
    String sql = "SELECT * FROM RECEIPT";
    Connection conn = DatabaseHelper.getConnect();
    try {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        
        while (rs.next()) {
            Receipt receipt = Receipt.fromResultSet(rs);
            list.add(receipt);
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    return list;
}

public static Receipt fromResultSet(ResultSet rs) {
    Receipt receipt = new Receipt();
    try {
        receipt.setId(rs.getInt("RECEIPT_ID"));
        receipt.setCreatedDate(rs.getTimestamp("R_DATETIME"));
        receipt.setTotal(rs.getDouble("R_TOTAL_PRICE"));
        receipt.setReceived(rs.getDouble("R_AMOUNT_RECEIVED"));
        receipt.setChange(rs.getDouble("R_CHANGE"));
        receipt.setTotalQuantity(rs.getInt("R_QTY"));
        receipt.setBranchId(rs.getInt("BRANCH_ID"));
        receipt.setUserId(rs.getInt("USER_ID"));
        receipt.setCustomerId(rs.getInt("CUSTOMER_ID"));
        receipt.setPaymentCode(rs.getInt("PAYMENT_CODE"));
        receipt.setPromotionID(rs.getInt("PROMOTION_ID"));
    } catch (SQLException ex) {
        Logger.getLogger(Receipt.class.getName()).log(Level.SEVERE, null, ex);
        return null;
    }
    return receipt;
}

public List<Receipt> getReceipts() {
    ReceiptDao receiptDao = new ReceiptDao();
    return receiptDao.getAll();
}

Additional Information:

Question: Why is the year being displayed incorrectly when it is retrieved in my Java application? How can I ensure that it displays as 2024 instead of being converted to 1481?

  1. Value and Type Returned by rs.getTimestamp("R_DATETIME"):
    The rs.getTimestamp("R_DATETIME") method returns a java.sql.Timestamp object. For example, if the R_DATETIME in the database is "2024-09-30 12:34:56", this is the value that should be retrieved.

  2. Function of setCreatedDate():
    The setCreatedDate(Timestamp createdDate) method in the Receipt class is used to set the createdDate property of the Receipt object. It stores the Timestamp object without any modification.

Upvotes: 0

Views: 99

Answers (1)

Arvind Kumar Avinash
Arvind Kumar Avinash

Reputation: 79620

java.time

In March 2014, Java 8 introduced the modern, java.time date-time API which supplanted the error-prone legacy, java.util date-time API. Any new code should use the java.time API. If you are receiving an instance of java.util.Date, convert it tojava.time.Instant, using Date#toInstant and derive other date-time classes of java.time from it as per your requirement.

TIMESTAMP maps with LocalDateTime as shown in this table:

ANSI SQL Java SE 8
DATE LocalDate
TIME LocalTime
TIMESTAMP LocalDateTime
TIME WITH TIMEZONE OffsetTime
TIMESTAMP WITH TIMEZONE OffsetDateTime

How to use LocalDateTime in JDBC?

Given below is a sample code to insert a LocalDateTime into columnfoo (which is of TIMESTAMP type):

LocalDateTime ldt = LocalDateTime.now();
PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, ldt);
st.executeUpdate();
st.close();

Given below is a sample code to retrieve a LocalDateTime from columnfoo:

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE <some condition>");
while (rs.next()) {
    // Assuming the column index of columnfoo is 1
    LocalDateTime ldt = rs.getObject(1, LocalDateTime.class));
    System.out.println(ldt);
}
rs.close();
st.close();

Learn more about the modern Date-Time API from Trail: Date Time.

Upvotes: 2

Related Questions