将日期插入数据库Postgres JDBC [英] Insert Date Into Database Postgres JDBC
问题描述
我是Java和Postgres的新手。
I'm new to Java and also to Postgres.
我有一个关于餐厅的小项目，我有一个像这样的struk（eng：bill）表：
I have a little project about restaurant and i have a struk(eng: bill) table like this:
我有一种插入信息的方法进入该表如下：
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;
}
稍后，该方法将调用：
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?
推荐答案
既然你说过你是java的新手，我认为你会使用Java 8和PostgreSQL JDBC驱动程序使用JDBC 4.2为Java 8 Date和Time API提供本机支持。
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.
PostgreSQL支持以下数据类型的日期/时间：
Following datatypes for Date/Time are supported by PostgreSQL :
DATE
TIME [WITHOUT TIMEZONE]
TIMESTAMP [WITHOUT TIMEZONE]
TIMESTAMP WITH TIMEZONE
您可以使用LocalDate，LocalTime，LocalDateTime或OffsetDateTime（OffsetDateTime是具有偏移量的日期时间的不可变表示，例如：值2017年7月20日10：45.24.123456789 +04：00可以存储在OffsetDateTime中。这些类来自java.time包，它是在Java 8中引入的，作为一个新的Date-Time API。（它涵盖了几个旧的日期时间API，如设计问题，线程安全，时区处理问题等）。但是，PostgreSQl当前不支持ZonedDateTime，Instant和OffsetTime / TIME [WITHOUT TIMEZONE]（如JDBC 4.2中所示）。现在开始根据你的代码编写部分，你可以这样做。
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();
希望这有助于你。如果您使用的是低于8的Java版本，则可以使用joda时间。
Hope this helped you. In case you are using Java versions below 8, you could use joda time.
这篇关于将日期插入数据库Postgres JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
