vertica数据库测试数据和java api
测试sql
select * from "db"."vertica_12_all_types";
DROP TABLE db.vertica_12_all_types;
CREATE TABLE db.vertica_12_all_types (
-- Numeric types
integer_col INTEGER,
int_col INT,
bigint_col BIGINT,
int8_col INT8,
smallint_col SMALLINT,
tinyint_col TINYINT,
double_col DOUBLE PRECISION,
float_col FLOAT,
float3_col FLOAT(3),
float8_col FLOAT8,
real_col REAL,
numeric_col NUMERIC(15,5),
number_col NUMBER(15,5),
decimal_col DECIMAL(12,2),
-- String types
char_col CHAR,
char20_col CHAR(20),
character20_col CHARACTER(20),
varchar_col VARCHAR,
varchar100_col VARCHAR(100),
charactervaring100_col CHARACTER VARYING(100),
-- Binary types
binary_col BINARY(32),
varbinary_col VARBINARY(200),
binaryvarying_col BINARY VARYING(200),
bytea_col BYTEA(200),
raw_col RAW(200),
-- Date and time types
date_col DATE,
time_col TIME,
time_tz_col TIME WITH TIME ZONE,
timestamp_col TIMESTAMP,
timestamp_tz_col TIMESTAMP WITH TIME ZONE,
interval_ym_col INTERVAL YEAR TO MONTH,
interval_ds_col INTERVAL DAY TO SECOND,
-- Boolean type
boolean_col BOOLEAN,
-- UUID type
uuid_col UUID,
v_array_varchar_col ARRAY[VARCHAR(50)],
v_array_int_col ARRAY[INT]
);
truncate table bigdata.vertica_12_all_types;
INSERT INTO db.vertica_12_all_types (
integer_col, int_col, bigint_col, int8_col, smallint_col, tinyint_col,
double_col, float_col, float3_col, float8_col, real_col,
numeric_col, number_col, decimal_col,
char_col, char20_col, character20_col,
varchar_col, varchar100_col, charactervaring100_col,
binary_col, varbinary_col, binaryvarying_col, bytea_col, raw_col,
date_col, time_col, time_tz_col, timestamp_col, timestamp_tz_col,
interval_ym_col, interval_ds_col,
boolean_col,
uuid_col,
v_array_varchar_col,v_array_int_col
) VALUES (
-- Numeric types
127, 2000, 9223372036854775807, -128, 32767, 255,
3.141592653589793, 2.71828, 123.45, 987654321.098765, 1.61803,
123456.78901, 987654.32109, 56789.45,
-- String types
'A', 'Fixed char 20 ', 'Character 20 ',
'Unlimited varchar text', 'Varchar with 100 char limit example', 'Character varying example',
-- Binary types
X'566572746963612042696E617279', X'53616D706C652056617242696E',
X'42696E6172792056617279696E67', X'42595445412064617461', X'5241572064617461',
-- Date and time types
'2024-09-26', '14:30:45', '14:30:45+02:00',
'2024-09-26 14:30:45', '2024-09-26 14:30:45-07:00',
INTERVAL '5-3' YEAR TO MONTH, INTERVAL '10 08:45:30' DAY TO SECOND,
-- Boolean type
TRUE,
-- UUID type
'f81d4fae-7dec-11d0-a765-00a0c91e6bf6',
-- Array type
ARRAY['apple', 'banana', 'cherry', 'date'],
ARRAY[1,2,3,4,5]
);
import java.sql.ResultSet
interface RsGetter<T> {
fun get(rs: ResultSet, colName: String): T
}
import java.io.Serializable
import java.math.BigDecimal
import java.sql.Date
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Time
import java.sql.Timestamp
import java.time.LocalTime
class VerticaJdbcTest {
val DATABASE = "test"
val URL = "jdbc:vertica://yourhost:5433/${DATABASE}"
val username = "username"
val password = "password"
val getString: RsGetter<String> = object : RsGetter<String> {
override fun get(rs: ResultSet, colName: String): String {
return rs.getString(colName)
}
}
val getInt = object : RsGetter<Int> {
override fun get(rs: ResultSet, colName: String): Int {
return rs.getInt(colName)
}
}
val getLong = object : RsGetter<Long> {
override fun get(rs: ResultSet, colName: String): Long {
return rs.getLong(colName)
}
}
val getFloat = object : RsGetter<Float> {
override fun get(rs: ResultSet, colName: String): Float {
return rs.getFloat(colName)
}
}
val getDouble = object : RsGetter<Double> {
override fun get(rs: ResultSet, colName: String): Double {
return rs.getDouble(colName)
}
}
val getDecimal = object : RsGetter<BigDecimal> {
override fun get(rs: ResultSet, colName: String): BigDecimal {
return rs.getBigDecimal(colName)
}
}
val getTime = object : RsGetter<LocalTime> {
override fun get(rs: ResultSet, colName: String): LocalTime {
val milli: Time = rs.getTime(colName)
return milli.toLocalTime()
}
}
val getDate = object : RsGetter<Date> {
override fun get(rs: ResultSet, colName: String): Date {
return rs.getDate(colName)
}
}
val getTimestamp = object : RsGetter<Timestamp> {
override fun get(rs: ResultSet, colName: String): Timestamp {
return rs.getTimestamp(colName)
}
}
val getBinary = object : RsGetter<ByteArray> {
override fun get(rs: ResultSet, colName: String): ByteArray {
return rs.getBytes(colName)
}
}
val getArrayString = object : RsGetter<Array<String>> {
override fun get(rs: ResultSet, colName: String): Array<String> {
val arr = rs.getArray(colName).getArray()
val s42:Array<String> = arr as Array<String>
return s42
}
}
val getArrayLong = object : RsGetter<Array<Long>> {
override fun get(rs: ResultSet, colName: String): Array<Long> {
val arr = rs.getArray(colName).getArray()
val s42:Array<Long> = arr as Array<Long>
return s42
}
}
val getBoolean = object : RsGetter<Boolean> {
override fun get(rs: ResultSet, colName: String): Boolean {
return rs.getBoolean(colName)
}
}
val fieldList: Map<String, RsGetter<out Serializable>> = mapOf(
"integer_col" to getInt,
"int_col" to getInt,
"bigint_col" to getLong,
"int8_col" to getLong,
"smallint_col" to getInt,
"tinyint_col" to getInt,
"double_col" to getDouble,
"float_col" to getFloat,
"float3_col" to getFloat,
"float8_col" to getFloat,
"real_col" to getFloat,
"numeric_col" to getDecimal,
"number_col" to getDecimal,
"decimal_col" to getDecimal,
"char_col" to getString,
"char20_col" to getString,
"character20_col" to getString,
"varchar_col" to getString,
"varchar100_col" to getString,
"charactervaring100_col" to getString,
"binary_col" to getBinary,
"varbinary_col" to getBinary,
"binaryvarying_col" to getBinary,
"bytea_col" to getBinary,
"raw_col" to getBinary,
"date_col" to getDate,
"time_col" to getTime,
"time_tz_col" to getTime,
"timestamp_col" to getTimestamp,
"timestamp_tz_col" to getTimestamp,
"interval_ym_col" to getString,
"interval_ds_col" to getString,
"boolean_col" to getBoolean,
"uuid_col" to getString,
"v_array_varchar_col" to getArrayString,
"v_array_int_col" to getArrayLong
)
val fieldClassMap: Map<String, Class<*>> = mapOf(
"integer_col" to java.lang.Integer.TYPE,
"int_col" to java.lang.Integer.TYPE,
"bigint_col" to java.lang.Long.TYPE,
"int8_col" to java.lang.Long.TYPE,
"smallint_col" to java.lang.Integer.TYPE,
"tinyint_col" to java.lang.Integer.TYPE,
"double_col" to java.lang.Double.TYPE,
"float_col" to java.lang.Float.TYPE,
"float3_col" to java.lang.Float.TYPE,
"float8_col" to java.lang.Float.TYPE,
"real_col" to java.lang.Float.TYPE,
"numeric_col" to java.math.BigDecimal::class.java,
"number_col" to java.math.BigDecimal::class.java,
"decimal_col" to java.math.BigDecimal::class.java,
"char_col" to java.lang.String::class.java,
"char20_col" to java.lang.String::class.java,
"character20_col" to java.lang.String::class.java,
"varchar_col" to java.lang.String::class.java,
"varchar100_col" to java.lang.String::class.java,
"charactervaring100_col" to java.lang.String::class.java,
"binary_col" to ByteArray::class.java,
"varbinary_col" to ByteArray::class.java,
"binaryvarying_col" to ByteArray::class.java,
"bytea_col" to ByteArray::class.java,
"raw_col" to ByteArray::class.java,
"date_col" to java.sql.Date::class.java,
"time_col" to java.sql.Time::class.java,
"time_tz_col" to java.sql.Time::class.java,
"timestamp_col" to java.sql.Timestamp::class.java,
"timestamp_tz_col" to java.sql.Timestamp::class.java,
"interval_ym_col" to java.lang.String::class.java,
"interval_ds_col" to java.lang.String::class.java,
"boolean_col" to java.lang.Boolean.TYPE,
"uuid_col" to java.lang.String::class.java,
"v_array_varchar_col" to java.sql.Array::class.java,
"v_array_int_col" to java.sql.Array::class.java
)
@Test
fun verticaType1() {
val conn = DriverManager.getConnection(URL, username, password)
val stmt = conn.createStatement()
val rs: ResultSet = stmt.executeQuery("select * from bigdata.vertica_12_all_types")
var index = 1
rs.next()
for (kv in fieldList.entries) {
val colType = rs.metaData.getColumnTypeName(index)
val fValue = kv.value.get(rs, kv.key)
println(colType + " --> " + kv.key + ":" + fValue)
index++
}
}
@Test
fun verticaType2() {
val conn = DriverManager.getConnection(URL, username, password)
val stmt = conn.createStatement()
val rs: ResultSet = stmt.executeQuery("select * from bigdata.vertica_12_all_types")
var index = 1
rs.next()
for (kv in fieldClassMap.entries) {
val colType = rs.metaData.getColumnTypeName(index)
val fValue = rs.getObject(index,kv.value)
println(colType + " --> " + kv.key + ":" + fValue)
index++
}
}
}
结果：
Integer --> integer_col:127
Integer --> int_col:2000
Integer --> bigint_col:9223372036854775807
Integer --> int8_col:-128
Integer --> smallint_col:32767
Integer --> tinyint_col:255
Float --> double_col:3.141592653589793
Float --> float_col:2.71828
Float --> float3_col:123.45
Float --> float8_col:9.8765434E8
Float --> real_col:1.61803
Numeric --> numeric_col:123456.78901
Numeric --> number_col:987654.32109
Numeric --> decimal_col:56789.45
Char --> char_col:A
Char --> char20_col:Fixed char 20
Char --> character20_col:Character 20
Varchar --> varchar_col:Unlimited varchar text
Varchar --> varchar100_col:Varchar with 100 char limit example
Varchar --> charactervaring100_col:Character varying example
Binary --> binary_col:[B@5e4bd84a
Varbinary --> varbinary_col:[B@648c94da
Varbinary --> binaryvarying_col:[B@2a62b5bc
Varbinary --> bytea_col:[B@53de625d
Varbinary --> raw_col:[B@535779e4
Date --> date_col:2024-09-26
Time --> time_col:14:30:45
TimeTz --> time_tz_col:20:30:45
Timestamp --> timestamp_col:2024-09-26 14:30:45.0
TimestampTz --> timestamp_tz_col:2024-09-27 05:30:45.0
Interval Year to Month --> interval_ym_col:5-03
Interval Day to Second --> interval_ds_col:10 08:45:30.000000
Boolean --> boolean_col:true
Uuid --> uuid_col:f81d4fae-7dec-11d0-a765-00a0c91e6bf6
Array --> v_array_varchar_col:[Ljava.lang.String;@67304a40
Array --> v_array_int_col:[Ljava.lang.Long;@cc77f12
查询vertica，元数据表
select type_id,odbc_type,jdbc_type,type_name from TYPES;
5 -7 -7 Boolean
6 -5 -5 Integer
7 8 8 Float
8 1 1 Char
9 12 12 Varchar
17 -3 -3 Varbinary
115 -1 -1 Long Varchar
116 -4 -4 Long Varbinary
117 -2 -2 Binary
16 2 2 Numeric
114 10 10 Interval Year
114 10 10 Interval Year to Month
114 10 10 Interval Month
14 10 10 Interval Day
14 10 10 Interval Day to Hour
14 10 10 Interval Day to Minute
14 10 10 Interval Day to Second
14 10 10 Interval Hour
14 10 10 Interval Hour to Minute
14 10 10 Interval Hour to Second
14 10 10 Interval Minute
14 10 10 Interval Minute to Second
14 10 10 Interval Second
10 9 9 Date
11 9 9 Time
15 9 9 TimeTz
12 9 9 Timestamp
13 9 9 TimestampTz
20 -11 -11 Uuid
1505 0 2003 Array[Boolean]
1506 0 2003 Array[Int8]
1507 0 2003 Array[Float8]
1508 0 2003 Array[Char]
1509 0 2003 Array[Varchar]
1517 0 2003 Array[Varbinary]
1522 0 2003 Array[Binary]
1516 0 2003 Array[Numeric]
1521 0 2003 Array[Interval Year]
1521 0 2003 Array[Interval Year to Month]
1521 0 2003 Array[Interval Month]
1514 0 2003 Array[Interval Day]
1514 0 2003 Array[Interval Day to Hour]
1514 0 2003 Array[Interval Day to Minute]
1514 0 2003 Array[Interval Day to Second]
1514 0 2003 Array[Interval Hour]
1514 0 2003 Array[Interval Hour to Minute]
1514 0 2003 Array[Interval Hour to Second]
1514 0 2003 Array[Interval Minute]
1514 0 2003 Array[Interval Minute to Second]
1514 0 2003 Array[Interval Second]
1510 0 2003 Array[Date]
1511 0 2003 Array[Time]
1515 0 2003 Array[TimeTz]
1512 0 2003 Array[Timestamp]
1513 0 2003 Array[TimestampTz]
1520 0 2003 Array[Uuid]
2705 0 2003 Set[Boolean]
2706 0 2003 Set[Int8]
2707 0 2003 Set[Float8]
2708 0 2003 Set[Char]
2709 0 2003 Set[Varchar]
2717 0 2003 Set[Varbinary]
2722 0 2003 Set[Binary]
2716 0 2003 Set[Numeric]
2721 0 2003 Set[Interval Year]
2721 0 2003 Set[Interval Year to Month]
2721 0 2003 Set[Interval Month]
2714 0 2003 Set[Interval Day]
2714 0 2003 Set[Interval Day to Hour]
2714 0 2003 Set[Interval Day to Minute]
2714 0 2003 Set[Interval Day to Second]
2714 0 2003 Set[Interval Hour]
2714 0 2003 Set[Interval Hour to Minute]
2714 0 2003 Set[Interval Hour to Second]
2714 0 2003 Set[Interval Minute]
2714 0 2003 Set[Interval Minute to Second]
2714 0 2003 Set[Interval Second]
2710 0 2003 Set[Date]
2711 0 2003 Set[Time]
2715 0 2003 Set[TimeTz]
2712 0 2003 Set[Timestamp]
2713 0 2003 Set[TimestampTz]
2720 0 2003 Set[Uuid]
49539595901076042 -4 -4 geometry
49539595901076044 -4 -4 geography
300 0 2002 Row
301 0 2003 Array
302 0 2003 Map
Integer --> integer_col:127
Integer --> int_col:2000
Integer --> bigint_col:9223372036854775807
Integer --> int8_col:-128
Integer --> smallint_col:32767
Integer --> tinyint_col:255
Float --> double_col:3.141592653589793
Float --> float_col:2.71828
Float --> float3_col:123.45
Float --> float8_col:9.8765434E8
Float --> real_col:1.61803
Numeric --> numeric_col:123456.78901
Numeric --> number_col:987654.32109
Numeric --> decimal_col:56789.45
Char --> char_col:A
Char --> char20_col:Fixed char 20
Char --> character20_col:Character 20
Varchar --> varchar_col:Unlimited varchar text
Varchar --> varchar100_col:Varchar with 100 char limit example
Varchar --> charactervaring100_col:Character varying example
Binary --> binary_col:[B@24111ef1
Varbinary --> varbinary_col:[B@1f3f02ee
Varbinary --> binaryvarying_col:[B@1fde5d22
Varbinary --> bytea_col:[B@5dcb4f5f
Varbinary --> raw_col:[B@71812481
Date --> date_col:2024-09-26
Time --> time_col:14:30:45
TimeTz --> time_tz_col:20:30:45.000
Timestamp --> timestamp_col:2024-09-26 14:30:45.0
TimestampTz --> timestamp_tz_col:2024-09-27 05:30:45.0
Interval Year to Month --> interval_ym_col:5-03
Interval Day to Second --> interval_ds_col:10 08:45:30.000000
Boolean --> boolean_col:true
Uuid --> uuid_col:f81d4fae-7dec-11d0-a765-00a0c91e6bf6
Array --> v_array_varchar_col:["apple","banana","cherry","date"]
Array --> v_array_int_col:[1,2,3,4,5]
浙公网安备 33010602011771号