KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > smallsql > junit > TestDataTypes


1 /* =============================================================
2  * SmallSQL : a free Java DBMS library for the Java(tm) platform
3  * =============================================================
4  *
5  * (C) Copyright 2004-2006, by Volker Berlin.
6  *
7  * Project Info: http://www.smallsql.de/
8  *
9  * This library is free software; you can redistribute it and/or modify it
10  * under the terms of the GNU Lesser General Public License as published by
11  * the Free Software Foundation; either version 2.1 of the License, or
12  * (at your option) any later version.
13  *
14  * This library is distributed in the hope that it will be useful, but
15  * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
16  * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
17  * License for more details.
18  *
19  * You should have received a copy of the GNU Lesser General Public
20  * License along with this library; if not, write to the Free Software
21  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301,
22  * USA.
23  *
24  * [Java is a trademark or registered trademark of Sun Microsystems, Inc.
25  * in the United States and other countries.]
26  *
27  * ---------------
28  * TestDataTypes.java
29  * ---------------
30  * Author: Volker Berlin
31  *
32  */

33 package smallsql.junit;
34
35 import junit.framework.*;
36 import java.sql.*;
37 import java.math.*;
38
39 public class TestDataTypes extends BasicTestCase{
40
41     static final String JavaDoc[] DATATYPES = { "varchar(100)",
42                                                 "varchar2(130)", "nvarchar(137)", "nvarchar2(137)", "sysname",
43                                                 "char(100)", "CHARACTER(99)",
44                                                 "nchar(80)",
45                                                 "int", "smallint", "tinyint", "bigint", "byte",
46                                                 "real", "float", "double",
47                                                 "bit", "Boolean",
48                                                 "binary( 125 )", "varbinary(57)", "raw(88)",
49                                                 "java_object", "sql_variant",
50                                                 "image", "LONGvarbinary", "long raw",
51                                                 "blob", "clob","nclob",
52                                                 "text", "ntext", "LongVarchar", "long",
53                                                 "time", "date", "datetime", "timestamp", "SMALLDATETIME",
54                                                 "UNIQUEIDENTIFIER",
55                                                 "numeric(28,4)", "decimal(29,4)","number(29,4)", "varnum(29,4)",
56                                                 "COUNTER",
57                                                 "money", "smallmoney"};
58
59     private static final String JavaDoc table = "table_datatypes";
60
61     private String JavaDoc datatype;
62
63     TestDataTypes( String JavaDoc datatype ){
64         super( datatype );
65         this.datatype = datatype;
66     }
67
68     public void tearDown(){
69         try{
70             Connection con = AllTests.getConnection();
71             Statement st = con.createStatement();
72             st.execute("drop table " + table);
73             st.close();
74         }catch(Throwable JavaDoc e){
75             //e.printStackTrace();
76
}
77     }
78
79     public void setUp(){
80         tearDown();
81     }
82
83     public void runTest() throws Throwable JavaDoc {
84         Connection con = AllTests.getConnection();
85         Statement st = con.createStatement();
86         st.execute("Create Table " + table +"(abc " + datatype + ")");
87         String JavaDoc name = "abc";
88
89         Object JavaDoc[] values = null;
90         String JavaDoc quote = "";
91         String JavaDoc escape1 = "";
92         String JavaDoc escape2 = "";
93         boolean needTrim = false;
94
95         ResultSet rs = st.executeQuery("SELECT * From " + table);
96         ResultSetMetaData md = rs.getMetaData();
97         switch(md.getColumnType(1)){
98             case Types.CHAR:
99                 needTrim = true;
100             case Types.VARCHAR:
101             case Types.LONGVARCHAR:
102             case Types.CLOB:
103                 values = new Object JavaDoc[]{null,"qwert", "asdfg", "hjhjhj", "1234567890 qwertzuiop 1234567890 asdfghjklö 1234567890 yxcvbnm,.- 1234567890 "};
104                 quote = "\'";
105                 break;
106             case Types.BIGINT:
107                 values = new Object JavaDoc[]{null,new Long JavaDoc(123), new Long JavaDoc(-2123), new Long JavaDoc(392839283)};
108                 break;
109             case Types.INTEGER:
110                 values = new Object JavaDoc[]{null,new Integer JavaDoc(123), new Integer JavaDoc(-2123), new Integer JavaDoc(392839283)};
111                 break;
112             case Types.SMALLINT:
113                 values = new Object JavaDoc[]{null,new Integer JavaDoc(123), new Integer JavaDoc(-2123), new Integer JavaDoc(32000)};
114                 break;
115             case Types.TINYINT:
116                 values = new Object JavaDoc[]{null,new Integer JavaDoc(0), new Integer JavaDoc(12), new Integer JavaDoc(228)};
117                 break;
118             case Types.REAL:
119                 values = new Object JavaDoc[]{null,new Float JavaDoc(0.0), new Float JavaDoc(-12.123), new Float JavaDoc(22812345234.9)};
120                 break;
121             case Types.FLOAT:
122             case Types.DOUBLE:
123                 values = new Object JavaDoc[]{null,new Double JavaDoc(0.0), new Double JavaDoc(-12.123), new Double JavaDoc(22812345234.9)};
124                 break;
125             case Types.NUMERIC:
126             case Types.DECIMAL:
127                 needTrim = true;
128                 if(md.getPrecision(1)<16){//smallmoney
129
values = new Object JavaDoc[]{null,new BigDecimal("0.0"), new BigDecimal("-2"), new BigDecimal("-12.123")};
130                 /*if(rs.getMetaData().isCurrency(1)){
131                     values = new Object[]{null, new Money(0.0), new Money(-12.123), new Money(202812.9)};*/

132                 }else{
133                     values = new Object JavaDoc[]{null,new BigDecimal("0.0"), new BigDecimal("-2"), new BigDecimal("-12.123"), new BigDecimal("22812345234.9")};
134                 }
135                 break;
136             case Types.BIT:
137             case Types.BOOLEAN:
138                 values = new Object JavaDoc[]{null, Boolean.TRUE, Boolean.FALSE};
139                 break;
140             case Types.TIME:
141                 values = new Object JavaDoc[]{null, new Time(10,17,56), new Time(0,0,0),new Time(23,59,59)};
142                 escape1 = "{t '";
143                 escape2 = "'}";
144                 break;
145             case Types.DATE:
146                 values = new Object JavaDoc[]{null, new java.sql.Date JavaDoc(10,10,1), new java.sql.Date JavaDoc(0,0,1),new java.sql.Date JavaDoc(70,0,1)};
147                 escape1 = "{d '";
148                 escape2 = "'}";
149                 break;
150             case Types.TIMESTAMP:
151                 if(md.getPrecision(1) >16)
152                     values = new Object JavaDoc[]{null, new Timestamp(10,10,1, 10,17,56, 0), new Timestamp(0,0,1, 0,0,0, 0),new Timestamp( 120,1,1, 23,59,59, 500000000),new Timestamp(0),new Timestamp( -120,1,1, 23,59,59, 500000000)};
153                 else//smalldatetime
154
values = new Object JavaDoc[]{null, new Timestamp(10,10,1, 10,17,0, 0), new Timestamp(0,0,1, 0,0,0, 0),new Timestamp(0)};
155                 escape1 = "{ts '";
156                 escape2 = "'}";
157                 break;
158             case Types.BINARY:
159                 needTrim = true;
160             case Types.VARBINARY:
161             case Types.LONGVARBINARY:
162             case Types.BLOB:
163                 values = new Object JavaDoc[]{null, new byte[]{1, 127, -23}};
164                 break;
165             case Types.JAVA_OBJECT:
166                 values = new Object JavaDoc[]{null, new Integer JavaDoc(-123), new Double JavaDoc(1.2), new byte[]{1, 127, -23}};
167                 break;
168             case -11: //UNIQUEIDENTIFER
169
values = new Object JavaDoc[]{null, "342734E3-D9AC-408F-8724-B7A257C4529E", "342734E3-D9AC-408F-8724-B7A257C4529E"};
170                 quote = "\'";
171                 break;
172             default: fail("Unknown column type: " + rs.getMetaData().getColumnType(1));
173         }
174         rs.close();
175         
176         // remove all resource for reloading the tables from file
177
con.close();
178         con = AllTests.getConnection();
179         st = con.createStatement();
180
181         for(int i=0; i<values.length; i++){
182             Object JavaDoc val = values[i];
183             String JavaDoc q = (val == null) ? "" : quote;
184             String JavaDoc e1 = (val == null) ? "" : escape1;
185             String JavaDoc e2 = (val == null) ? "" : escape2;
186             if(val instanceof byte[]){
187                 StringBuffer JavaDoc buf = new StringBuffer JavaDoc( "0x" );
188                 for(int k=0; k<((byte[])val).length; k++){
189                     String JavaDoc digit = "0" + Integer.toHexString( ((byte[])val)[k] );
190                     buf.append( digit.substring( digit.length()-2 ) );
191                 }
192                 val = buf.toString();
193             }
194             st.execute("Insert into " + table + "(abc) Values(" + e1 + q + val + q + e2 + ")");
195         }
196         checkValues( st, values, needTrim);
197         
198         st.execute("Delete From "+ table);
199         CallableStatement cal = con.prepareCall("Insert Into " + table + "(abc) Values(?)");
200         for(int i=0; i<values.length; i++){
201             Object JavaDoc val = values[i];
202             cal.setObject( 1, val);
203             cal.execute();
204         }
205         cal.close();
206         checkValues( st, values, needTrim);
207         
208         st.execute("Delete From "+ table);
209         cal = con.prepareCall("Insert Into " + table + "(abc) Values(?)");
210         for(int i=0; i<values.length; i++){
211             Object JavaDoc val = values[i];
212             if(val == null){
213                 cal.setNull( 1, Types.NULL );
214             }else
215             if(val instanceof Time){
216                 cal.setTime( 1, (Time)val );
217             }else
218             if(val instanceof Timestamp){
219                 cal.setTimestamp( 1, (Timestamp)val );
220             }else
221             if(val instanceof Date){
222                 cal.setDate( 1, (Date)val );
223             }else
224             if(val instanceof String JavaDoc){
225                 cal.setString( 1, (String JavaDoc)val );
226             }else
227             if(val instanceof Boolean JavaDoc){
228                 cal.setBoolean( 1, ((Boolean JavaDoc)val).booleanValue() );
229             }else
230             if(val instanceof Byte JavaDoc){
231                 cal.setByte( 1, ((Byte JavaDoc)val).byteValue() );
232             }else
233             if(val instanceof Short JavaDoc){
234                 cal.setShort( 1, ((Short JavaDoc)val).shortValue() );
235             }else
236             if(val instanceof Integer JavaDoc){
237                 cal.setInt( 1, ((Integer JavaDoc)val).intValue() );
238             }else
239             if(val instanceof Long JavaDoc){
240                 cal.setLong( 1, ((Long JavaDoc)val).longValue() );
241             }else
242             if(val instanceof Float JavaDoc){
243                 cal.setFloat( 1, ((Float JavaDoc)val).floatValue() );
244             }else
245             if(val instanceof Double JavaDoc){
246                 cal.setDouble( 1, ((Double JavaDoc)val).doubleValue() );
247             }else
248             if(val instanceof BigDecimal){
249                 cal.setBigDecimal( 1, (BigDecimal)val );
250             }else
251             if(val instanceof byte[]){
252                 cal.setBytes( 1, (byte[])val );
253             }
254             cal.execute();
255         }
256         cal.close();
257         checkValues( st, values, needTrim);
258
259         
260         st.execute("Delete From "+ table);
261         Statement st2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
262         ResultSet rs2 = st2.executeQuery("SELECT * From " + table);
263         for(int i=0; i<values.length; i++){
264             rs2.moveToInsertRow();
265             Object JavaDoc val = values[i];
266             if(val == null){
267                 rs2.updateNull( name );
268             }else
269             if(val instanceof Time){
270                 rs2.updateTime( name, (Time)val );
271             }else
272             if(val instanceof Timestamp){
273                 rs2.updateTimestamp( name, (Timestamp)val );
274             }else
275             if(val instanceof Date){
276                 rs2.updateDate( name, (Date)val );
277             }else
278             if(val instanceof String JavaDoc){
279                 rs2.updateString( name, (String JavaDoc)val );
280             }else
281             if(val instanceof Boolean JavaDoc){
282                 rs2.updateBoolean( name, ((Boolean JavaDoc)val).booleanValue() );
283             }else
284             if(val instanceof Byte JavaDoc){
285                 rs2.updateByte( name, ((Byte JavaDoc)val).byteValue() );
286             }else
287             if(val instanceof Short JavaDoc){
288                 rs2.updateShort( name, ((Short JavaDoc)val).shortValue() );
289             }else
290             if(val instanceof Integer JavaDoc){
291                 rs2.updateInt( name, ((Integer JavaDoc)val).intValue() );
292             }else
293             if(val instanceof Long JavaDoc){
294                 rs2.updateLong( name, ((Long JavaDoc)val).longValue() );
295             }else
296             if(val instanceof Float JavaDoc){
297                 rs2.updateFloat( name, ((Float JavaDoc)val).floatValue() );
298             }else
299             if(val instanceof Double JavaDoc){
300                 rs2.updateDouble( name, ((Double JavaDoc)val).doubleValue() );
301             }else
302             if(val instanceof BigDecimal){
303                 rs2.updateBigDecimal( name, (BigDecimal)val );
304             }else
305             if(val instanceof byte[]){
306                 rs2.updateBytes( name, (byte[])val );
307             }
308             rs2.insertRow();
309         }
310         st2.close();
311         checkValues( st, values, needTrim);
312     }
313     
314     
315     private void checkValues(Statement st, Object JavaDoc[] values, boolean needTrim) throws Exception JavaDoc{
316         ResultSet rs = st.executeQuery("SELECT * From " + table);
317
318         int i = 0;
319         while(rs.next()){
320             assertEqualsRsValue(values[i], rs, needTrim);
321             i++;
322         }
323         rs.close();
324     }
325
326
327     public static Test suite() throws Exception JavaDoc{
328         TestSuite theSuite = new TestSuite("Data Types");
329         for(int i=0; i<DATATYPES.length; i++){
330             theSuite.addTest(new TestDataTypes( DATATYPES[i] ) );
331         }
332         return theSuite;
333     }
334
335     public static void main(String JavaDoc[] argv) {
336         junit.swingui.TestRunner.main(new String JavaDoc[]{TestDataTypes.class.getName()});
337     }
338 }
Popular Tags