|
1
30
31
32 package org.hsqldb.test;
33
34
39 import java.io.File  ;
40 import java.sql.Connection ;
41 import java.sql.DriverManager ;
42 import java.sql.ResultSet ;
43 import java.sql.SQLException ;
44 import java.sql.Statement ;
45
46 import junit.framework.TestCase;
47
48
54 public class TestCascade extends TestCase {
55
56 Connection con;
57
58 public TestCascade(String name) {
59 super(name);
60 }
61
62 protected void setUp() {
63
64 try {
65 Class.forName("org.hsqldb.jdbcDriver");
66 createDatabase();
67
68 con = DriverManager.getConnection("jdbc:hsqldb:testdb", "sa", "");
69 } catch (Exception e) {
70 e.printStackTrace();
71 System.out.println(this + ".setUp() error: " + e.getMessage());
72 }
73 }
74
75 protected void tearDown() {
76
77 try {
78 con.close();
79 } catch (SQLException e) {}
80 }
81
82 public void testDelete() {
83
84 try {
85 insertData(con);
86
87 Statement stmt = con.createStatement();
88 ResultSet rs =
89 stmt.executeQuery("SELECT COUNT(EIACODXA) FROM CA");
90
91 rs.next();
92
93 int origCount = rs.getInt(1);
94
95 rs.close();
96 deleteXBRecord(con);
97
98 rs = stmt.executeQuery("SELECT COUNT(EIACODXA) FROM CA");
99
100 rs.next();
101
102 int newCount = rs.getInt(1);
103
104 rs.close();
105 stmt.close();
106 assertEquals(9, newCount);
107 } catch (SQLException e) {
108 this.assertTrue("SQLException thrown", false);
109 }
110 }
111
112 private static void createDatabase() throws SQLException {
113
114 new File ("testdb.backup").delete();
115 new File ("testdb.data").delete();
116 new File ("testdb.properties").delete();
117 new File ("testdb.script").delete();
118
119 Connection con = DriverManager.getConnection("jdbc:hsqldb:testdb",
120 "sa", "");
121 String [] saDDL = {
122 "CREATE CACHED TABLE XB (EIACODXA VARCHAR(10) NOT NULL, LSACONXB VARCHAR(18) NOT NULL, ALTLCNXB VARCHAR(2) NOT NULL, LCNTYPXB VARCHAR(1) NOT NULL, LCNINDXB VARCHAR(1), LCNAMEXB VARCHAR(19), UPDT_BY VARCHAR(32), LST_UPDT TIMESTAMP, CONSTRAINT XPKXB PRIMARY KEY (EIACODXA, LSACONXB, ALTLCNXB, LCNTYPXB));",
123
124 "CREATE CACHED TABLE CA ( EIACODXA VARCHAR(10) NOT NULL, LSACONXB VARCHAR(18) NOT NULL, ALTLCNXB VARCHAR(2) NOT NULL, LCNTYPXB VARCHAR(1) NOT NULL, TASKCDCA VARCHAR(7) NOT NULL, TSKFRQCA NUMERIC(7,4), UPDT_BY VARCHAR(32), LST_UPDT TIMESTAMP, CONSTRAINT XPKCA PRIMARY KEY (EIACODXA, LSACONXB, ALTLCNXB, LCNTYPXB, TASKCDCA), CONSTRAINT R_XB_CA FOREIGN KEY (EIACODXA, LSACONXB, ALTLCNXB, LCNTYPXB) REFERENCES XB ON DELETE CASCADE);",
126
127 };
129 Statement stmt = con.createStatement();
130
131 for (int index = 0; index < saDDL.length; index++) {
132 stmt.executeUpdate(saDDL[index]);
133 }
134
135 stmt.execute("SHUTDOWN");
136 con.close();
137 }
139
144 private static void deleteXBRecord(Connection con) throws SQLException {
145
146 Statement stmt = con.createStatement();
147
148 stmt.executeUpdate(
149 "DELETE FROM XB WHERE LSACONXB = 'LEAA' AND EIACODXA = 'T850' AND LCNTYPXB = 'P' AND ALTLCNXB = '00'");
150 stmt.close();
151 }
153 private static void insertData(Connection con) throws SQLException {
154
155 String [] saData = {
156 "INSERT INTO XB VALUES('T850','LEAA','00','P',NULL,'LCN NAME','sa',NOW)",
157 "INSERT INTO XB VALUES('T850','LEAA01','00','P',NULL,'LCN NAME','sa',NOW)",
158 "INSERT INTO XB VALUES('T850','LEAA02','00','P',NULL,'LCN NAME','sa',NOW)",
159 "INSERT INTO XB VALUES('T850','LEAA03','00','P',NULL,'LCN NAME','sa',NOW)",
160 "INSERT INTO CA VALUES('T850','LEAA','00','P','ABCDEFG',3.14,'sa',NOW)",
161 "INSERT INTO CA VALUES('T850','LEAA','00','P','QRSTUJV',3.14,'sa',NOW)",
162 "INSERT INTO CA VALUES('T850','LEAA','00','P','ZZZZZZZ',3.14,'sa',NOW)",
163 "INSERT INTO CA VALUES('T850','LEAA01','00','P','ABCDEFG',3.14,'sa',NOW)",
164 "INSERT INTO CA VALUES('T850','LEAA01','00','P','QRSTUJV',3.14,'sa',NOW)",
165 "INSERT INTO CA VALUES('T850','LEAA01','00','P','ZZZZZZZ',3.14,'sa',NOW)",
166 "INSERT INTO CA VALUES('T850','LEAA02','00','P','ABCDEFG',3.14,'sa',NOW)",
167 "INSERT INTO CA VALUES('T850','LEAA02','00','P','QRSTUJV',3.14,'sa',NOW)",
168 "INSERT INTO CA VALUES('T850','LEAA02','00','P','ZZZZZZZ',3.14,'sa',NOW)",
169 "INSERT INTO CA VALUES('T850','LEAA03','00','P','ABCDEFG',3.14,'sa',NOW)",
170 "INSERT INTO CA VALUES('T850','LEAA03','00','P','QRSTUJV',3.14,'sa',NOW)",
171 "INSERT INTO CA VALUES('T850','LEAA03','00','P','ZZZZZZZ',3.14,'sa',NOW)"
172 };
173 Statement stmt = con.createStatement();
174
175 for (int index = 0; index < saData.length; index++) {
176 stmt.executeUpdate(saData[index]);
177 }
178 } }
180 |
Popular Tags |
Java API By Example, From Geeks To Geeks.