将时间间隔与 JDBC 配合使用
JDBC 标准不包含用于时间间隔（两个时间点之间的持续时间）的数据类型。若要处理 Vertica 的 INTERVAL 数据类型，您必须使用 JDBC 特定于数据库的对象类型。
从结果集中读取时间间隔值时，使用 ResultSet.getObject() 方法检索该值，然后将其强制转换为 Vertica 时间间隔类之一： VerticaDayTimeInterval （代表所有十种日期/时间的时间间隔）或 VerticaYearMonthInterval（代表所有三种年/月时间间隔）。
注意
单位间隔样式不受支持。不要使用 SET INTERVALSTYLE 语句更改客户端应用程序中的时间间隔样式。在批量插入中使用时间间隔
将多个批插入到包含时间间隔数据的表时，您必须创建 VerticaDayTimeInterval 或 VerticaYearMonthInterval 类的实例以用于存放要插入的数据。您可以在调用类的构造函数时设置值，或者也可以在使用 setter 之后设置值。然后，可以使用 PreparedStatement.setObject() 方法插入时间间隔值。您还可以使用 .setString() 方法，并向其传递采用 "DD ``HH:MM:SS" 或 "YY-MM" 格式的字符串。
以下示例演示了将数据插入到包含日/时间间隔和年/月时间间隔的表：
import java.sql.*;
import java.util.Properties;
// You need to import the Vertica JDBC classes to be able to instantiate
// the interval classes.
import com.vertica.jdbc.*;
public class IntervalDemo {
public static void main(String[] args) {
// If running under a Java 5 JVM, use you need to load the JDBC driver
// using Class.forname here
Properties myProp = new Properties();
myProp.put("user", "ExampleUser");
myProp.put("password", "password123");
Connection conn;
try {
conn = DriverManager.getConnection(
"jdbc:vertica://VerticaHost:5433/VMart", myProp);
// Create table for interval values
Statement stmt = conn.createStatement();
stmt.execute("DROP TABLE IF EXISTS interval_demo");
stmt.executeUpdate("CREATE TABLE interval_demo("
+ "DayInt INTERVAL DAY TO SECOND, "
+ "MonthInt INTERVAL YEAR TO MONTH)");
// Insert data into interval columns using
// VerticaDayTimeInterval and VerticaYearMonthInterval
// classes.
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO interval_demo VALUES(?,?)");
// Create instances of the Vertica classes that represent
// intervals.
VerticaDayTimeInterval dayInt = new VerticaDayTimeInterval(10, 0,
5, 40, 0, 0, false);
VerticaYearMonthInterval monthInt = new VerticaYearMonthInterval(
10, 6, false);
// These objects can also be manipulated using setters.
dayInt.setHour(7);
// Add the interval values to the batch
((VerticaPreparedStatement) pstmt).setObject(1, dayInt);
((VerticaPreparedStatement) pstmt).setObject(2, monthInt);
pstmt.addBatch();
// Set another row from strings.
// Set day interval in "days HH:MM:SS" format
pstmt.setString(1, "10 10:10:10");
// Set year to month value in "MM-YY" format
pstmt.setString(2, "12-09");
pstmt.addBatch();
// Execute the batch to insert the values.
try {
pstmt.executeBatch();
} catch (SQLException e) {
System.out.println("Error message: " + e.getMessage());
}
读取时间间隔值
可以使用 ResultSet.getObject() 方法从结果集读取时间间隔值，然后将对象转换为适当的 Vertica 对象类： VerticaDayTimeInterval （代表日期/时间的时间间隔）或 VerticaYearMonthInterval（代表年/月时间间隔）。如果您知道列包含时间间隔并且知道该时间间隔的类型，则可以轻松完成此操作。如果应用程序无法确定从中读取的结果集的数据结构，您可以测试列是否包含特定于数据库的对象类型，如果是的话，则可以确定该对象属于 VerticaDayTimeInterval 类还是 VerticaYearMonthInterval 类。
// Retrieve the interval values inserted by previous demo.
// Query the table to get the row back as a result set.
ResultSet rs = stmt.executeQuery("SELECT * FROM interval_demo");
// If you do not know the types of data contained in the result set,
// you can read its metadata to determine the type, and use
// additional information to determine the interval type.
ResultSetMetaData md = rs.getMetaData();
while (rs.next()) {
for (int x = 1; x <= md.getColumnCount(); x++) {
// Get data type from metadata
int colDataType = md.getColumnType(x);
// You can get the type in a string:
System.out.println("Column " + x + " is a "
+ md.getColumnTypeName(x));
// Normally, you'd have a switch statement here to
// handle all sorts of column types, but this example is
// simplified to just handle database-specific types
if (colDataType == Types.OTHER) {
// Column contains a database-specific type. Determine
// what type of interval it is. Assuming it is an
// interval...
Object columnVal = rs.getObject(x);
if (columnVal instanceof VerticaDayTimeInterval) {
// We know it is a date time interval
VerticaDayTimeInterval interval =
(VerticaDayTimeInterval) columnVal;
// You can use the getters to access the interval's
// data
System.out.print("Column " + x + "'s value is ");
System.out.print(interval.getDay() + " Days ");
System.out.print(interval.getHour() + " Hours ");
System.out.println(interval.getMinute()
+ " Minutes");
} else if (columnVal instanceof VerticaYearMonthInterval) {
VerticaYearMonthInterval interval =
(VerticaYearMonthInterval) columnVal;
System.out.print("Column " + x + "'s value is ");
System.out.print(interval.getYear() + " Years ");
System.out.println(interval.getMonth() + " Months");
} else {
System.out.println("Not an interval.");
}
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
该示例将在控制台上输出以下内容：
Column 1 is a INTERVAL DAY TO SECOND
Column 1's value is 10 Days 7 Hours 5 Minutes
Column 2 is a INTERVAL YEAR TO MONTH
Column 2's value is 10 Years 6 Months
Column 1 is a INTERVAL DAY TO SECOND
Column 1's value is 10 Days 10 Hours 10 Minutes
Column 2 is a INTERVAL YEAR TO MONTH
Column 2's value is 12 Years 9 Months
另一个选项是使用数据库元数据来查找包含时间间隔的列。
// Determine the interval data types by examining the database
// metadata.
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet dbMeta = dbmd.getColumns(null, null, "interval_demo", null);
int colcount = 0;
while (dbMeta.next()) {
// Get the metadata type for a column.
int javaType = dbMeta.getInt("DATA_TYPE");
System.out.println("Column " + ++colcount + " Type name is " +
dbMeta.getString("TYPE_NAME"));
if(javaType == Types.OTHER) {
// The SQL_DATETIME_SUB column in the metadata tells you
// Specifically which subtype of interval you have.
// The VerticaDayTimeInterval.isDayTimeInterval()
// methods tells you if that value is a day time.
//
int intervalType = dbMeta.getInt("SQL_DATETIME_SUB");
if(VerticaDayTimeInterval.isDayTimeInterval(intervalType)) {
// Now you know it is one of the 10 day/time interval types.
// When you select this column you can cast to
// VerticaDayTimeInterval.
// You can get more specific by checking intervalType
// against each of the 10 constants directly, but
// they all are represented by the same object.
System.out.println("column " + colcount + " is a " +
"VerticaDayTimeInterval intervalType = "
+ intervalType);
} else if(VerticaYearMonthInterval.isYearMonthInterval(
intervalType)) {
//now you know it is one of the 3 year/month intervals,
//and you can select the column and cast to
// VerticaYearMonthInterval
System.out.println("column " + colcount + " is a " +
"VerticaDayTimeInterval intervalType = "
+ intervalType);
} else {
System.out.println("Not an interval type.");
}
}
}