3

I'm new to Java and also to Postgres.

I have a little project about restaurant and i have a struk(eng: bill) table like this:

enter image description here

and I have a method to inserting information into that table like this:

public int insertBill(int id_karyawan, String tanggal, String waktu, int total) {
    String SQL = "INSERT INTO struk(kode, id_karyawan, tanggal, waktu, total) VALUES (?,?,?,?,?)";
    int id = 0;

    try(Connection conn = connect(); PreparedStatement pstmt = conn.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS)) {
        pstmt.setInt(1, 1);
        pstmt.setInt(2, id_karyawan);
        pstmt.setString(3, tanggal);
        pstmt.setString(4, waktu);
        pstmt.setInt(5, total);

        int affectedRows = pstmt.executeUpdate();
        if(affectedRows > 0) {
            try(ResultSet rs = pstmt.getGeneratedKeys()) {
                if(rs.next()) {
                    id = rs.getInt(1);
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
    return id;
}

later, that method will called with this:

int billID = app.insertBill(1, "2017-09-24", "08:00:00", 150000);

The problem is, i dont have any idea about the Date and the time, what should i pass as a parameter? what kind of variable so that the query work well? I've searched about it got some clue to use string. I use string on a query method for now. Any advice?

Improve this question
6
  • Use an actual Date/Time object and let the JDBC driver deal with it Commented Jul 20, 2017 at 6:31
  • convert String to Date (SimpleDateFormat) and then use setDate Commented Jul 20, 2017 at 6:31
  • 1
    If you're on Java 8, and you have the latest JDBC drivers, you should use the java.time objects, like LocalDate and LocalTime, and use setObject. Commented Jul 20, 2017 at 6:39
  • 1
    Why don't you all write this as answer? I thought comments are for questions. Commented Jul 20, 2017 at 6:55
  • use pstmt.setTime(4, ...) and pstmt.setDate(3, ...)` and use proper java.sql.Date or java.sql.Time values Commented Jul 20, 2017 at 7:01

2 Answers 2

Reset to default
11

Since you have said you are new to java, I assume that you would be using Java 8 and The PostgreSQL JDBC driver provides native support for the Java 8 Date and Time API using JDBC 4.2.

Following datatypes for Date/Time are supported by PostgreSQL :

DATE
TIME [ WITHOUT TIMEZONE ]
TIMESTAMP [ WITHOUT TIMEZONE ]
TIMESTAMP WITH TIMEZONE

You can use the LocalDate, LocalTime, LocalDateTime or OffsetDateTime (OffsetDateTime is an immutable representation of a date-time with an offset eg:the value "20th July 2017 at 10:45.24.123456789 +04:00" can be stored in an OffsetDateTime). These classes comes in the java.time package, which was introduced in Java 8, as a new Date-Time API.(It covers several cons of old date-time API like design issues, thread safety, time zone handling issues etc.) However, ZonedDateTime, Instant and OffsetTime / TIME [ WITHOUT TIMEZONE ] are currently not supported in PostgreSQl (as in JDBC 4.2). Now comming to programming part based on you code, you can do it like this.

public int insertBill(int id_karyawan, String tanggal, String waktu, int total) {

LocalDate localDate = LocalDate.now(); //or whatever date you want to use, for passing parameter as string you could use 

                        /*public static LocalDate parse(CharSequence text,
                          DateTimeFormatter formatter)*/

 //   LocalDate ld = LocalDate.of(2017, Month.JULY, 20);
 LocalTime lt = LocalTime.of(8, 0, 0); 

String SQL = "INSERT INTO struk(kode, id_karyawan, tanggal, waktu, total) VALUES (?,?,?,?,?)";
int id = 0;

try(Connection conn = connect(); PreparedStatement pstmt = conn.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setInt(1, 1);
pstmt.setInt(2, id_karyawan);
pstmt.setObject(3, localDate);
pstmt.setObject(4, lt);
pstmt.setInt(5, total);

pstmt.executeUpdate(); 
pstmt.close();

Hope this helped you. In case you are using Java versions below 8, you could use joda time.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Actually the Joda-Time project is in maintenance mode. The team advises moving to the Java.time classes. As for Java 6 and Java 7, much of the java.time functionality is back-ported in the ThreeTen-Backport project.
0

Hope this will help you:

public int insertBill(int id_karyawan, String tanggal, String waktu, int total) {
String SQL = "INSERT INTO struk(kode, id_karyawan, tanggal, waktu, total) VALUES (?,?,?,?,?)";
int id = 0;

try(Connection conn = connect(); PreparedStatement pstmt = conn.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS)) {
    pstmt.setInt(1, 1);
    pstmt.setInt(2, id_karyawan);
    pstmt.setDate(3, java.sql.Date.valueof("2017-09-24")); 
    pstmt.setTime(4, java.sql.Time.valueof("08:00:00")); 
    pstmt.setInt(5, total);

    int affectedRows = pstmt.executeUpdate();
    if(affectedRows > 0) {
        try(ResultSet rs = pstmt.getGeneratedKeys()) {
            if(rs.next()) {
                id = rs.getInt(1);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
} catch (SQLException ex) {
    System.out.println(ex.getMessage());
}
return id;

}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.