|
1
5 package org.h2.test.db;
6
7 import java.math.BigDecimal  ;
8 import java.sql.*;
9
10 import org.h2.test.TestBase;
11
12 public class TestLinkedTable extends TestBase {
13
14 public void test() throws Exception {
15 deleteDb("linked1");
16 deleteDb("linked2");
17 Class.forName("org.h2.Driver");
18
19 Connection conn = DriverManager.getConnection("jdbc:h2:"+BASE_DIR+"/linked1", "sa1", "abc");
20 Statement stat = conn.createStatement();
21 stat.execute("CREATE TEMP TABLE TEST_TEMP(ID INT PRIMARY KEY)");
22 stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255), XT TINYINT, XD DECIMAL(10,2), XTS TIMESTAMP, XBY BINARY(255), XBO BIT, XSM SMALLINT, XBI BIGINT, XBL BLOB, XDA DATE, XTI TIME, XCL CLOB, XDO DOUBLE)");
23 stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
24 stat.execute("INSERT INTO TEST VALUES(0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)");
25 stat.execute("INSERT INTO TEST VALUES(1, 'Hello', -1, 10.30, '2001-02-03 11:22:33.4455', X'FF0102', TRUE, 3000, 1234567890123456789, X'1122AA', DATE '0002-01-01', TIME '00:00:00', 'J\u00fcrg', 2.25)");
26 testRow(stat, "TEST");
27 stat.execute("INSERT INTO TEST VALUES(2, 'World', 30, 100.05, '2005-12-31 12:34:56.789', X'FFEECC33', FALSE, 1, -1234567890123456789, X'4455FF', DATE '9999-12-31', TIME '23:59:59', 'George', -2.5)");
28 testRow(stat, "TEST");
29 stat.execute("SELECT * FROM TEST_TEMP");
30 conn.close();
31
32 conn = DriverManager.getConnection("jdbc:h2:"+BASE_DIR+"/linked1", "sa1", "abc");
33 stat = conn.createStatement();
34 testRow(stat, "TEST");
35 try {
36 stat.execute("SELECT * FROM TEST_TEMP");
37 error("temp table must not be persistent");
38 } catch(SQLException e) {
39 checkNotGeneralException(e);
40 }
41 conn.close();
42
43 conn = DriverManager.getConnection("jdbc:h2:"+BASE_DIR+"/linked2", "sa2", "def");
44 stat = conn.createStatement();
45 stat.execute("CREATE LINKED TABLE IF NOT EXISTS LINK_TEST('org.h2.Driver', 'jdbc:h2:"+BASE_DIR+"/linked1', 'sa1', 'abc', 'TEST')");
46 stat.execute("CREATE LINKED TABLE IF NOT EXISTS LINK_TEST('org.h2.Driver', 'jdbc:h2:"+BASE_DIR+"/linked1', 'sa1', 'abc', 'TEST')");
47 testRow(stat, "LINK_TEST");
48
49 conn.close();
50 conn = DriverManager.getConnection("jdbc:h2:"+BASE_DIR+"/linked2", "sa2", "def");
51 stat = conn.createStatement();
52
53 stat.execute("INSERT INTO LINK_TEST VALUES(3, 'Link Test', 30, 100.05, '2005-12-31 12:34:56.789', X'FFEECC33', FALSE, 1, -1234567890123456789, X'4455FF', DATE '9999-12-31', TIME '23:59:59', 'George', -2.5)");
54
55 ResultSet rs = stat.executeQuery("SELECT COUNT(*) FROM LINK_TEST");
56 rs.next();
57 check(rs.getInt(1), 4);
58
59 rs = stat.executeQuery("SELECT COUNT(*) FROM LINK_TEST WHERE NAME='Link Test'");
60 rs.next();
61 check(rs.getInt(1), 1);
62
63 int uc = stat.executeUpdate("DELETE FROM LINK_TEST WHERE ID=3");
64 check(uc, 1);
65
66 rs = stat.executeQuery("SELECT COUNT(*) FROM LINK_TEST");
67 rs.next();
68 check(rs.getInt(1), 3);
69
70 rs = stat.executeQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='LINK_TEST'");
71 rs.next();
72 check(rs.getString("TABLE_TYPE"), "TABLE LINK");
73
74 rs.next();
75 rs = stat.executeQuery("SELECT * FROM LINK_TEST WHERE ID=0");
76 rs.next();
77 check(rs.getString("NAME")==null && rs.wasNull());
78 check(rs.getString("XT")==null && rs.wasNull());
79 check(rs.getInt("ID")==0 && !rs.wasNull());
80 check(rs.getBigDecimal("XD")==null && rs.wasNull());
81 check(rs.getTimestamp("XTS")==null && rs.wasNull());
82 check(rs.getBytes("XBY")==null && rs.wasNull());
83 check(rs.getBoolean("XBO")==false && rs.wasNull());
84 check(rs.getShort("XSM")==0 && rs.wasNull());
85 check(rs.getLong("XBI")==0 && rs.wasNull());
86 check(rs.getString("XBL")==null && rs.wasNull());
87 check(rs.getString("XDA")==null && rs.wasNull());
88 check(rs.getString("XTI")==null && rs.wasNull());
89 check(rs.getString("XCL")==null && rs.wasNull());
90 check(rs.getString("XDO")==null && rs.wasNull());
91 checkFalse(rs.next());
92
93 stat.execute("DROP TABLE LINK_TEST");
94
95 stat.execute("CREATE LINKED TABLE LINK_TEST('org.h2.Driver', 'jdbc:h2:"+BASE_DIR+"/linked1', 'sa1', 'abc', '(SELECT COUNT(*) FROM TEST)')");
96 rs = stat.executeQuery("SELECT * FROM LINK_TEST");
97 rs.next();
98 check(rs.getInt(1), 3);
99 checkFalse(rs.next());
100
101 conn.close();
102
103 deleteDb("linked1");
104 deleteDb("linked2");
105 }
106
107 void testRow(Statement stat, String name) throws Exception {
108 ResultSet rs = stat.executeQuery("SELECT * FROM "+name+" WHERE ID=1");
109 rs.next();
110 check(rs.getString("NAME"), "Hello");
111 check(rs.getByte("XT"), -1);
112 BigDecimal bd = rs.getBigDecimal("XD");
113 check(bd.equals(new BigDecimal ("10.30")));
114 Timestamp ts = rs.getTimestamp("XTS");
115 String s = ts.toString();
116 check(s, "2001-02-03 11:22:33.4455");
117 check(ts.equals(Timestamp.valueOf("2001-02-03 11:22:33.4455")));
118 check(rs.getBytes("XBY"), new byte[]{(byte)255, (byte)1, (byte)2});
119 check(rs.getBoolean("XBO"));
120 check(rs.getShort("XSM"), 3000);
121 check(rs.getLong("XBI"), 1234567890123456789L);
122 check(rs.getString("XBL"), "1122aa");
123 check(rs.getString("XDA"), "0002-01-01");
124 check(rs.getString("XTI"), "00:00:00");
125 check(rs.getString("XCL"), "J\u00fcrg");
126 check(rs.getString("XDO"), "2.25");
127
128 }
129
130 }
131 |
Popular Tags |
Java API By Example, From Geeks To Geeks.