|
1
33 package smallsql.junit;
34
35 import junit.framework.*;
36 import java.sql.*;
37 import java.math.*;
38
39 public class TestOperatoren extends BasicTestCase {
40
41 private TestValue testValue;
42
43 private static final String  table = "table_functions";
44
45 private static final TestValue[] TESTS = new TestValue[]{
46 a("tinyint" , new Byte ( (byte)3), new Byte ( (byte)4)),
47 a("byte" , new Byte ( (byte)3), new Byte ( (byte)4)),
48 a("smallint" , new Short ( (short)3), new Short ( (short)4)),
49 a("int" , new Integer (3), new Integer (4)),
50 a("bigint" , new Long (3), new Long (4)),
51 a("real" , new Float (3.45), new Float (4.56)),
52 a("float" , new Float (3.45), new Float (4.56)),
53 a("double" , new Double (3.45), new Double (4.56)),
54 a("smallmoney" , new Float (3.45), new Float (4.56)),
55 a("money" , new Float (3.45), new Float (4.56)),
56 a("money" , new Double (3.45), new Double (4.56)),
57 a("numeric(19,2)" , new BigDecimal("3.45"), new BigDecimal("4.56")),
58 a("decimal(19,2)" , new BigDecimal("3.45"), new BigDecimal("4.56")),
59 a("varnum(28,2)" , new BigDecimal("2.34"), new BigDecimal("3.45")),
60 a("number(28,2)" , new BigDecimal("2.34"), new BigDecimal("3.45")),
61 a("varchar(100)" , new String ("abc"), new String ("qwert")),
62 a("nvarchar(100)" , new String ("abc"), new String ("qwert")),
63 a("varchar2(100)" , new String ("abc"), new String ("qwert")),
64 a("nvarchar2(100)" , new String ("abc"), new String ("qwert")),
65 a("character(100)" , new String ("abc"), new String ("qwert")),
66 a("char(100)" , new String ("abc"), new String ("qwert")),
67 a("nchar(100)" , new String ("abc"), new String ("qwert")),
68 a("text" , new String ("abc"), new String ("qwert")),
69 a("ntext" , new String ("abc"), new String ("qwert")),
70 a("date" , new Date(99, 1,1), new Date(99, 2,2)),
71 a("time" , new Time(9, 1,1), new Time(9, 2,2)),
72 a("timestamp" , new Timestamp(99, 1,1,0,0,0,0), new Timestamp(99, 2,2,0,0,0,0)),
73 a("datetime" , new Timestamp(99, 1,1,0,0,0,0), new Timestamp(99, 2,2,0,0,0,0)),
74 a("smalldatetime" , new Timestamp(99, 1,1,0,0,0,0), new Timestamp(99, 2,2,0,0,0,0)),
75 a("binary(100)" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
76 a("varbinary(100)" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
77 a("raw(100)" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
78 a("long raw" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
79 a("longvarbinary" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
80 a("blob" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
81 a("image" , new byte[]{12, 45, 1}, new byte[]{12, 45, 2, 56, 89}),
82 a("boolean" , Boolean.FALSE, Boolean.TRUE),
83 a("bit" , Boolean.FALSE, Boolean.TRUE),
84 a("uniqueidentifier" , "12345678-3445-3445-3445-1234567890ab", "12345679-3445-3445-3445-1234567890ac"),
85 };
86
87
88 TestOperatoren(TestValue testValue){
89 super(testValue.dataType);
90 this.testValue = testValue;
91 }
92
93 public void tearDown(){
94 try{
95 Connection con = AllTests.getConnection();
96 Statement st = con.createStatement();
97 st.execute("drop table " + table);
98 st.close();
99 }catch(Throwable e){
100 }
102 }
103
104 public void setUp(){
105 tearDown();
106 try{
107 Connection con = AllTests.getConnection();
108 Statement st = con.createStatement();
109 st.execute("create table " + table + "(a " + testValue.dataType +", b " + testValue.dataType + ")");
110 st.close();
111 PreparedStatement pr = con.prepareStatement("INSERT into " + table + "(a,b) Values(?,?)");
112
113 pr.setObject( 1, testValue.small);
114 pr.setObject( 2, testValue.large);
115 pr.execute();
116
117 pr.setObject( 1, testValue.small);
118 pr.setObject( 2, testValue.small);
119 pr.execute();
120
121 pr.setObject( 1, testValue.large);
122 pr.setObject( 2, testValue.large);
123 pr.execute();
124
125 pr.setObject( 1, testValue.large);
126 pr.setObject( 2, testValue.small);
127 pr.execute();
128
129 pr.setObject( 1, null);
130 pr.setObject( 2, testValue.small);
131 pr.execute();
132
133 pr.setObject( 1, testValue.small);
134 pr.setObject( 2, null);
135 pr.execute();
136
137 pr.setObject( 1, null);
138 pr.setObject( 2, null);
139 pr.execute();
140 pr.close();
141 }catch(Throwable e){
142 e.printStackTrace();
143 }
144 }
145
146
147 public void runTest() throws Exception {
148 Connection con = AllTests.getConnection();
149 Statement st = con.createStatement();
150 ResultSet rs;
151
152 rs = st.executeQuery("Select * from " + table + " where 1 = 0");
153 assertFalse( "To many rows", rs.next() );
154 boolean needTrim;
155 switch(rs.getMetaData().getColumnType(1)){
156 case Types.CHAR:
157 case Types.BINARY:
158 needTrim = true;
159 break;
160 default: needTrim = false;
161 }
162
163 rs = st.executeQuery("Select * from " + table + " where a = b");
164 assertTrue( "To few rows", rs.next() );
165 assertEqualsObject( "Values not equals", rs.getObject(1), rs.getObject(2), false);
166 assertTrue( "To few rows", rs.next() );
167 assertEqualsObject( "Values not equals", rs.getObject(1), rs.getObject(2), false);
168 assertFalse( "To many rows", rs.next() );
169
170 rs = st.executeQuery("Select * from " + table + " where a <= b and b <= a");
171 assertTrue( "To few rows", rs.next() );
172 assertEqualsObject( "Values not equals", rs.getObject(1), rs.getObject(2), false);
173 assertTrue( "To few rows", rs.next() );
174 assertEqualsObject( "Values not equals", rs.getObject(1), rs.getObject(2), false);
175 assertFalse( "To many rows", rs.next() );
176
177 rs = st.executeQuery("Select * from " + table + " where (a > (b))");
178 assertTrue( "To few rows", rs.next() );
179 assertFalse( "To many rows", rs.next() );
180
181 rs = st.executeQuery("Select * from " + table + " where a >= b");
182 assertTrue( "To few rows", rs.next() );
183 assertTrue( "To few rows", rs.next() );
184 assertTrue( "To few rows", rs.next() );
185 assertFalse( "To many rows", rs.next() );
186
187 rs = st.executeQuery("Select * from " + table + " where not (a >= b)");
188 assertTrue( "To few rows", rs.next() );
189 assertTrue( "To few rows", rs.next() );
190 assertTrue( "To few rows", rs.next() );
191 assertTrue( "To few rows", rs.next() );
192 assertFalse( "To many rows", rs.next() );
193
194 rs = st.executeQuery("Select * from " + table + " where a < b");
195 assertTrue( "To few rows", rs.next() );
196 assertFalse( "To many rows", rs.next() );
197
198 rs = st.executeQuery("Select * from " + table + " where a < b or a>b");
199 assertTrue( "To few rows", rs.next() );
200 assertTrue( "To few rows", rs.next() );
201 assertFalse( "To many rows", rs.next() );
202
203 rs = st.executeQuery("Select * from " + table + " where a <= b");
204 assertTrue( "To few rows", rs.next() );
205 assertTrue( "To few rows", rs.next() );
206 assertTrue( "To few rows", rs.next() );
207 assertFalse( "To many rows", rs.next() );
208
209 rs = st.executeQuery("Select * from " + table + " where a <> b");
210 assertTrue( "To few rows", rs.next() );
211 assertTrue( "To few rows", rs.next() );
212 assertFalse( "To many rows", rs.next() );
213
214 PreparedStatement pr = con.prepareStatement("Select * from " + table + " where a between ? and ?");
215 pr.setObject( 1, testValue.small);
216 pr.setObject( 2, testValue.large);
217 rs = pr.executeQuery();
218 assertTrue( "To few rows", rs.next() );
219 assertTrue( "To few rows", rs.next() );
220 assertTrue( "To few rows", rs.next() );
221 assertTrue( "To few rows", rs.next() || needTrim);
222 assertTrue( "To few rows", rs.next() || needTrim);
223 assertFalse( "To many rows", rs.next() );
224 pr.close();
225
226 pr = con.prepareStatement("Select * from " + table + " where a not between ? and ?");
227 pr.setObject( 1, testValue.small);
228 pr.setObject( 2, testValue.large);
229 rs = pr.executeQuery();
230 assertTrue( "To few rows", rs.next() );
231 assertTrue( "To few rows", rs.next() );
232 if(needTrim){
233 assertTrue( "To few rows", rs.next());
234 assertTrue( "To few rows", rs.next());
235 }
236 assertFalse( "To many rows", rs.next() );
237 pr.close();
238
239 pr = con.prepareStatement("Select * from " + table + " where a in(?,?)");
240 pr.setObject( 1, testValue.small);
241 pr.setObject( 2, testValue.large);
242 rs = pr.executeQuery();
243 assertTrue( "To few rows", rs.next() || needTrim);
244 assertTrue( "To few rows", rs.next() || needTrim);
245 assertTrue( "To few rows", rs.next() || needTrim);
246 assertTrue( "To few rows", rs.next() || needTrim);
247 assertTrue( "To few rows", rs.next() || needTrim);
248 assertFalse( "To many rows", rs.next() );
249 pr.close();
250
251 pr = con.prepareStatement("Select * from " + table + " where a not in(?,?)");
252 pr.setObject( 1, testValue.small);
253 pr.setObject( 2, testValue.large);
254 rs = pr.executeQuery();
255 assertTrue( "To few rows", rs.next());
256 assertTrue( "To few rows", rs.next());
257 if(needTrim){
258 assertTrue( "To few rows", rs.next());
259 assertTrue( "To few rows", rs.next());
260 assertTrue( "To few rows", rs.next());
261 assertTrue( "To few rows", rs.next());
262 assertTrue( "To few rows", rs.next());
263 }
264 assertFalse( "To many rows", rs.next() );
265 pr.close();
266
267 st.close();
268 }
269
270 public static Test suite() throws Exception {
271 TestSuite theSuite = new TestSuite("Operatoren");
272 for(int i=0; i<TESTS.length; i++){
273 theSuite.addTest(new TestOperatoren( TESTS[i] ) );
274 }
275 return theSuite;
276 }
277
278 public static void main(String [] argv) {
279 junit.swingui.TestRunner.main(new String []{TestOperatoren.class.getName()});
280 }
281
282
283
284 private static TestValue a(String dataType, Object small, Object large){
285 TestValue value = new TestValue();
286 value.dataType = dataType;
287 value.small = small;
288 value.large = large;
289 return value;
290 }
291
292 private static class TestValue{
293 String dataType;
294 Object small;
295 Object large;
296 }
297
298 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.