|
1
33 package smallsql.junit;
34
35 import junit.framework.*;
36
37 import java.math.BigDecimal  ;
38 import java.sql.*;
39
40 public class TestJoins extends BasicTestCase {
41
42 private TestValue testValue;
43
44 private static final String table = "table_joins";
45 private static final String table2= "table_joins2";
46 private static final String table3= "table_joins3";
47
48 private static final TestValue[] TESTS = new TestValue[]{
49 a("tinyint" , new Byte ( (byte)3), new Byte ( (byte)4)),
50 a("byte" , new Byte ( (byte)3), new Byte ( (byte)4)),
51 a("smallint" , new Short ( (short)3), new Short ( (short)4)),
52 a("int" , new Integer (3), new Integer (4)),
53 a("bigint" , new Long (3), new Long (4)),
54 a("real" , new Float (3.45), new Float (4.56)),
55 a("float" , new Float (3.45), new Float (4.56)),
56 a("double" , new Double (3.45), new Double (4.56)),
57 a("smallmoney" , new Float (3.45), new Float (4.56)),
58 a("money" , new Float (3.45), new Float (4.56)),
59 a("money" , new Double (3.45), new Double (4.56)),
60 a("numeric(19,2)" , new BigDecimal ("3.45"), new BigDecimal ("4.56")),
61 a("decimal(19,2)" , new BigDecimal ("3.45"), new BigDecimal ("4.56")),
62 a("varnum(28,2)" , new BigDecimal (3.45), new BigDecimal (4.56)),
63 a("number(28,2)" , new BigDecimal (3.45), new BigDecimal (4.56)),
64 a("varchar(100)" , new String ("abc"), new String ("qwert")),
65 a("nvarchar(100)" , new String ("abc"), new String ("qwert")),
66 a("varchar2(100)" , new String ("abc"), new String ("qwert")),
67 a("nvarchar2(100)" , new String ("abc"), new String ("qwert")),
68 a("character(100)" , new String ("abc"), new String ("qwert")),
69 a("char(100)" , new String ("abc"), new String ("qwert")),
70 a("nchar(100)" , new String ("abc"), new String ("qwert")),
71 a("text" , new String ("abc"), new String ("qwert")),
72 a("ntext" , new String ("abc"), new String ("qwert")),
73 a("date" , new Date(99, 1,1), new Date(99, 2,2)),
74 a("time" , new Time(9, 1,1), new Time(9, 2,2)),
75 a("timestamp" , new Timestamp(99, 1,1,0,0,0,0), new Timestamp(99, 2,2,0,0,0,0)),
76 a("datetime" , new Timestamp(99, 1,1,0,0,0,0), new Timestamp(99, 2,2,0,0,0,0)),
77 a("smalldatetime" , new Timestamp(99, 1,1,0,0,0,0), new Timestamp(99, 2,2,0,0,0,0)),
78 a("binary(100)" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
79 a("varbinary(100)" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
80 a("raw(100)" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
81 a("long raw" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
82 a("longvarbinary" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
83 a("blob" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
84 a("image" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
85 a("boolean" , Boolean.FALSE, Boolean.TRUE),
86 a("bit" , Boolean.FALSE, Boolean.TRUE),
87 a("uniqueidentifier" , "12345678-3445-3445-3445-1234567890ab", "12345679-3445-3445-3445-1234567890ab"),
88 };
89
90
91 TestJoins(TestValue testValue){
92 super(testValue.dataType);
93 this.testValue = testValue;
94 }
95
96
97 private void clear() throws SQLException{
98 Connection con = AllTests.getConnection();
99 dropTable( con, table );
100 dropTable( con, table2 );
101 dropTable( con, table3 );
102 }
103
104
105 public void tearDown() throws SQLException{
106 clear();
107 }
108
109 public void setUp() throws Exception {
110 clear();
111 Connection con = AllTests.getConnection();
112 Statement st = con.createStatement();
113 st.execute("create table " + table + "(a " + testValue.dataType +" PRIMARY KEY, b " + testValue.dataType + ")");
114 st.execute("create table " + table2+ "(c " + testValue.dataType +" PRIMARY KEY, d " + testValue.dataType + ")");
115 st.execute("create table " + table3+ "(c " + testValue.dataType +" PRIMARY KEY, d " + testValue.dataType + ")");
116 st.close();
117 con.close();
118 con = AllTests.getConnection();
119 PreparedStatement pr = con.prepareStatement("INSERT into " + table + "(a,b) Values(?,?)");
120 insertValues( pr );
121 pr.close();
122
123 pr = con.prepareStatement("INSERT into " + table2 + " Values(?,?)");
124 insertValues( pr );
125 pr.close();
126 }
127
128 private void insertValues(PreparedStatement pr ) throws Exception {
129 pr.setObject( 1, testValue.small);
130 pr.setObject( 2, testValue.large);
131 pr.execute();
132
133 pr.setObject( 1, testValue.small);
134 pr.setObject( 2, testValue.small);
135 pr.execute();
136
137 pr.setObject( 1, testValue.large);
138 pr.setObject( 2, testValue.large);
139 pr.execute();
140
141 pr.setObject( 1, testValue.large);
142 pr.setObject( 2, testValue.small);
143 pr.execute();
144
145 pr.setObject( 1, null);
146 pr.setObject( 2, testValue.small);
147 pr.execute();
148
149 pr.setObject( 1, testValue.small);
150 pr.setObject( 2, null);
151 pr.execute();
152
153 pr.setObject( 1, null);
154 pr.setObject( 2, null);
155 pr.execute();
156 }
157
158 public void runTest() throws Exception {
159 Connection con = AllTests.getConnection();
160 Statement st = con.createStatement();
161 ResultSet rs;
162
163 rs = st.executeQuery("Select * from " + table + " where 1 = 0");
164 assertFalse( "To many rows", rs.next() );
165
166 assertRowCount( 7, "Select * from " + table);
167 assertRowCount( 49, "Select * from " + table + ", " + table2);
168 assertRowCount( 0, "Select * from " + table + ", " + table3);
169 assertRowCount( 49, "Select * from ("+ table +"), " + table2);
170 assertRowCount( 49, "Select * from " + table + " Cross Join " + table2);
171 assertRowCount( 13, "Select * from " + table + " INNER JOIN " + table2 + " ON " + table + ".a = " + table2 + ".c");
172 assertRowCount( 13, "Select * from {oj " + table + " INNER JOIN " + table2 + " ON " + table + ".a = " + table2 + ".c}");
173 assertRowCount( 13, "Select * from " + table + " AS t1 INNER JOIN " + table2 + " t2 ON t1.a = t2.c");
174 assertRowCount( 13, "Select * from {oj " + table + " t1 INNER JOIN " + table2 + " t2 ON t1.a = t2.c}");
175 assertRowCount( 4, "Select * from " + table + " t1 INNER JOIN " + table2 + " t2 ON t1.a = t2.c and t1.b=t2.d");
176 assertRowCount( 7, "Select * from " + table + " t1 LEFT OUTER JOIN " + table2 + " t2 ON t1.a = t2.c and t1.b=t2.d");
177 assertRowCount( 15, "Select * from " + table + " t1 LEFT OUTER JOIN " + table2 + " t2 ON t1.a = t2.c");
178 assertRowCount( 7, "Select * from " + table + " t1 LEFT OUTER JOIN " + table3 + " t2 ON t1.a = t2.c");
179 assertRowCount( 7, "Select * from " + table + " t1 RIGHT OUTER JOIN " + table2 + " t2 ON t1.a = t2.c and t1.b=t2.d");
180 assertRowCount( 7, "Select * from " + table + " t1 RIGHT OUTER JOIN " + table2 + " t2 ON false");
181 assertRowCount( 15, "Select * from " + table + " t1 RIGHT OUTER JOIN " + table2 + " t2 ON t1.a = t2.c");
182 assertRowCount( 0, "Select * from " + table + " t1 RIGHT OUTER JOIN " + table3 + " t2 ON t1.a = t2.c");
183 assertRowCount( 14, "Select * from " + table + " t1 FULL OUTER JOIN " + table2 + " t2 ON 1=0");
184 assertRowCount( 17, "Select * from " + table + " t1 FULL OUTER JOIN " + table2 + " t2 ON t1.a = t2.c");
185 assertRowCount( 7, "Select * from " + table + " t1 FULL OUTER JOIN " + table3 + " t2 ON t1.a = t2.c");
186 assertRowCount( 7, "Select * from " + table3 + " t1 FULL OUTER JOIN " + table + " t2 ON t1.c = t2.a");
187 assertRowCount( 5, "Select * from " + table + " INNER JOIN (SELECT DISTINCT c FROM " + table2 + ") t1 ON " + table + ".a = t1.c");
188
189 st.close();
190 }
191
192 public static Test suite() throws Exception {
193 TestSuite theSuite = new TestSuite("Joins");
194 for(int i=0; i<TESTS.length; i++){
195 theSuite.addTest(new TestJoins( TESTS[i] ) );
196 }
197 return theSuite;
198 }
199
200 public static void main(String [] argv) {
201 junit.swingui.TestRunner.main(new String []{TestJoins.class.getName()});
202 }
203
204
205
206 private static TestValue a(String dataType, Object small, Object large){
207 TestValue value = new TestValue();
208 value.dataType = dataType;
209 value.small = small;
210 value.large = large;
211 return value;
212 }
213
214 private static class TestValue{
215 String dataType;
216 Object small;
217 Object large;
218 }
219
220 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.