|
1
5 package org.h2.test.cases;
6
7 import java.sql.*;
8
9 import org.h2.tools.DeleteDbFiles;
10
11 public class TestCollation {
12
13 private static final int TOTAL = 100000;
14 private static final int ROOT = 1000;
15
16 private Connection conn;
17
18 public static void main(String  [] args) throws Exception {
19 new TestCollation().test();
20 new TestCollation().test();
21 }
22
23 public void test() throws Exception {
24 DeleteDbFiles.execute(null, "test", true);
25 Class.forName("org.h2.Driver");
26 Class.forName("org.postgresql.Driver");
27 Class.forName("com.mysql.jdbc.Driver");
28 Class.forName("org.hsqldb.jdbcDriver");
29 conn = DriverManager.getConnection("jdbc:h2:testColl", "sa", "sa");
31
35 try {
36 conn.createStatement().execute("DROP TABLE TEST");
37 } catch(Exception e) {
38 }
40 conn.createStatement().execute("CREATE TABLE TEST(ID INT)");
41 conn.createStatement().execute("INSERT INTO TEST VALUES(1)");
42 PreparedStatement prep = conn.prepareStatement("Select Case When ? >= ? Then 'Yes' Else 'No' End FROM TEST");
43 prep.setObject(1, new Integer (26));
44 prep.setObject(2, new Integer (26));
45
46 ResultSet rs = prep.executeQuery();
47 while(rs.next()) {
48 System.out.println("a:"+rs.getObject(1));
49 }
50
51
53 prep = conn.prepareStatement("Select Case When ? >= Cast(? As Int) Then 'Yes' Else 'No' End FROM TEST");
55 prep.setObject(1, new Integer (26));
56 prep.setObject(2, new Integer (26));
57
58 rs = prep.executeQuery();
59 while (rs.next()) {
60 System.out.println("b:"+rs.getObject(1));
61 }
62
63
65
66
68 Statement stm = conn.createStatement();
69 stm.executeUpdate(
70 "DROP TABLE IF EXISTS test");
71 stm.executeUpdate(
72 "SET COLLATION OFF");
73 stm.executeUpdate(
74 "CREATE TABLE test (id INT IDENTITY, code VARCHAR(20) NOT NULL, parentId INT)");
75 stm.executeUpdate(
76 "CREATE INDEX test_code ON test(code)");
77 PreparedStatement pstm = conn.prepareStatement(
78 "INSERT INTO test (code,parentId) VALUES (?,NULL)");
79 PreparedStatement pstm2 = conn.prepareStatement(
80 "INSERT INTO test (code,parentId) SELECT ?,id FROM test WHERE code=?");
81
82 long time = System.currentTimeMillis();
83 for (int i = 1; i < TOTAL; ++i) {
84 if (i < ROOT) {
85 pstm.setString(1, Integer.toBinaryString(i));
86 pstm.executeUpdate();
87 } else {
88 pstm2.setString(1, Integer.toBinaryString(i));
89 pstm2.setString(2, Integer.toBinaryString(i % 100 + 1));
90 pstm2.executeUpdate();
91 }
92 }
93 System.out.println("INSERT w/o Collation: " + (System.currentTimeMillis()-time));
94
95 testWithCollation();
96
97 }
98
99 public void testWithCollation() throws Exception {
100
101 Statement stm = conn.createStatement();
102 stm.executeUpdate(
103 "DROP TABLE IF EXISTS test");
104 stm.executeUpdate(
105 "SET COLLATION ENGLISH STRENGTH PRIMARY");
106 stm.executeUpdate(
107 "CREATE TABLE test (id INT IDENTITY, code VARCHAR(20) NOT NULL, parentId INT)");
108 stm.executeUpdate(
109 "CREATE INDEX test_code ON test(code)");
110 PreparedStatement pstm = conn.prepareStatement(
111 "INSERT INTO test (code,parentId) VALUES (?,NULL)");
112 PreparedStatement pstm2 = conn.prepareStatement(
113 "INSERT INTO test (code,parentId) SELECT ?,id FROM test WHERE code=?");
114
115 long time = System.currentTimeMillis();
116 for (int i = 1; i < TOTAL; ++i) {
117 if (i < ROOT) {
118 pstm.setString(1, Integer.toBinaryString(i));
119 pstm.executeUpdate();
120 } else {
121 pstm2.setString(1, Integer.toBinaryString(i));
122 pstm2.setString(2, Integer.toBinaryString(i % 100 + 1));
123 pstm2.executeUpdate();
124 }
125 }
126 System.out.println("INSERT with Collation: " + (System.currentTimeMillis()-time));
127 }
128
129 }
130 |
Popular Tags |
Java API By Example, From Geeks To Geeks.