Java > Open Source Codes > dinamica > Recordset


1 package dinamica;
2
3 import java.text.MessageFormat ;
4 import java.util.ArrayList ;
5 import java.util.Collections ;
6 import java.util.Comparator ;
7 import java.util.HashMap ;
8 import java.util.Iterator ;
9 import java.io.Serializable ;
10 import java.io.StringWriter ;
11 import java.io.PrintWriter ;
12 import java.sql.*;
13
14 /**
15  * Core-level framework class: Recordset - General purpose, tabular data structure.
16  * <br><br>
17  * Represents a disconnected recordset, a kind of multi-purpose ResultSet
18  * without connection to the database, much like
19  * the famous ADO disconnected recordset from VB6,
20  * can be created from a SQL query or manufactured
21  * by code to represent any tabular data. It is a kind of
22  * generic data container that may save a lot of work because
23  * it can be used to represent several entities avoiding the need
24  * to write custom classes to represent each specific entity.
25  * It is serializable, so it can be safely used in HTTP Sessions, JNDI, etc.
26  * <br><br>
27  * This recordset mantains all data in memory and does not need
28  * an open connection to the database, provides several constructors
29  * to isolate the programmer from the task of programming with the
30  * JDBC API, but also provides low level access if desired.
31  * <br><br>
32  * Each field in the recordset is mantained in its native data type
33  * as represented by the JDBC driver, or a java null value.
34  * <br>
35  * <br>
36  * Creation date: 10/09/2003<br>
37  * Last Update: 11/09/2003<br>
38  * (c) 2003 Martin Cordova<br>
39  * This code is released under the LGPL license<br>
40  * @author Martin Cordova (dinamica@martincordova.com)
41  */
42 public class Recordset implements Serializable
43 {
44
45     /**
46      *
47      */
48     private static final long serialVersionUID = 1L;
49
50     /** contains list of fields */
51     private HashMap _fields = new HashMap ();
52
53     /** contains list of records */
54     private ArrayList _data = new ArrayList ();
55
56     /** recordset position (0...N-1)*/
57     private int _recordNumber = -1;
58
59     /** paging support */
60     private int _pageCount = 0;
61     private int _pageSize = 0;
62     private int _currentPage = 0;
63
64     /** recordset ID */
65     private String _ID = null;
66
67     /**
68      * Create a recordset with one record containing
69      * general recordset parameters like: recordcount, pagecount, currentpage
70      * @return Recordset Object
71      * @throws Throwable
72      */
73     public Recordset getRecordsetInfo() throws Throwable
74     {
75         
76         Recordset rs = new Recordset();
77         rs.append("recordcount", Types.INTEGER);
78         rs.append("pagecount", Types.INTEGER);
79         rs.append("currentpage", Types.INTEGER);
80         rs.append(_ID + ".recordcount", Types.INTEGER);
81         
82         rs.addNew();
83         rs.setValue("recordcount", new Integer (_data.size()));
84         rs.setValue("pagecount", new Integer (_pageCount));
85         rs.setValue("currentpage", new Integer (_currentPage));
86         rs.setValue(_ID + ".recordcount", new Integer (_data.size()));
87         
88         return rs;
89         
90     }
91
92     /**
93      * Set recordset ID (an arbitrary String)
94      * @param id
95      */
96     public void setID(String id)
97     {
98         _ID = id;
99     }
100
101     /**
102      * Paging support.<br>
103      * Returns the number of record pages determined by setPageSize
104      */
105     public int getPageCount()
106     {
107         return _pageCount;
108     }
109
110     /**
111      * Paging support.<br>
112      * Returns the current page number (1...N)
113      */
114     public int getPageNumber()
115     {
116         return _currentPage;
117     }
118     
119
120     /**
121      * Paging support.<br>
122      * Define page size (records per page)
123      */
124     public void setPageSize(int p) throws Throwable
125     {
126     
127         if (p <=0 )
128         {
129             throw new Throwable ("Invalid page size, must be > 0!");
130         }
131
132         if (_data.size() == 0)
133         {
134             throw new Throwable ("Invalid page size, recordset is empty!");
135         }
136     
137         _pageSize = p;
138
139         java.math.BigDecimal b1 = new java.math.BigDecimal (_data.size());
140         java.math.BigDecimal b2 = new java.math.BigDecimal (_pageSize);
141         _pageCount = b1.divide(b2,java.math.BigDecimal.ROUND_UP).intValue();
142         
143     }
144
145
146     /**
147      * Paging support.<br>
148      * Get Recordset representing the requeste page of records
149      */
150     public Recordset getPage(int p) throws Throwable
151     {
152     
153         if (p < 1 || p > _pageCount)
154             throw new Throwable ("Invalid page number: " + p + " - the Recordset contains " + _pageCount + " pages.");
155     
156         _currentPage = p;
157     
158         /* calculate first and last row numbers for this page */
159         int row1 = (p - 1) * _pageSize;
160         int row2 = (p * _pageSize) - 1;
161         if (row2 > (_data.size()-1))
162             row2 = _data.size() - 1;
163
164         /*
165          create an identical recordset
166          containing only the records
167          for the requested page
168         */
169         ArrayList newData = new ArrayList (_pageSize);
170         for (int i=row1;i<=row2;i++)
171         {
172             newData.add(_data.get(i));
173         }
174         Recordset x = new Recordset();
175         x.setFields((HashMap )_fields.clone());
176         x.setData(newData);
177         
178         /* return "page" */
179         return x;
180     
181     }
182
183     /**
184      * Feed the recordset metadata (column structure)
185      * @param fields HashMap containing the recordset field objects
186      */
187     protected void setFields(HashMap fields)
188     {
189         this._fields = fields;
190     }
191     
192     /**
193      * Feed the recordset data
194      * @param data ArrayList containing record objects
195      */
196     protected void setData(ArrayList data)
197     {
198         this._data = data;
199     }
200
201     /**
202      * Returns the current record position (0...N-1)
203      * @return
204      */
205     public int getRecordNumber()
206     {
207         return _recordNumber;
208     }
209
210     /**
211      * Returns the number of records in recordset
212      * @return
213      */
214     public int getRecordCount()
215     {
216         return _data.size();
217     }
218
219     /**
220      * Returns number of fields in recordset
221      * @return
222      */
223     public int getFieldCount()
224     {
225         return _fields.size();
226     }
227
228     /**
229      * Returns HashMap containing RecordsetField objects
230      * representing the Recordset fields
231      * @return
232      */
233     public HashMap getFields()
234     {
235         return _fields;
236     }
237
238     /**
239      * Returns ArrayList containing the recordset data (the records)
240      * @return
241      */
242     public ArrayList getData()
243     {
244         return _data;
245     }
246
247     /**
248      * Append a field to the recordset structure.<br>
249      * It is used when creating a Recordset from a JDBC query
250      * @param fieldName Field Name
251      * @param nativeSqlType SQL native data type name
252      * @param type JDBC data type (java.sql.Types)
253      */
254     private void append(String fieldName, String nativeSqlType, int type)
255     {
256         RecordsetField f = new RecordsetField(fieldName, nativeSqlType, type);
257         _fields.put(fieldName, f);
258     }
259
260     /**
261      * Append a field to the recordset structure.<br>
262      * It is used when manufacturing a Recordset from code
263      * @param fieldName Field Name
264      * @param nativeSqlType SQL native data type name
265      * @param type JDBC data type (java.sql.Types) - only INTEGER, LONG, VARCHAR, DATE, TIMESTAMP or DOUBLE are supported
266      */
267     public void append(String fieldName, int type) throws RecordsetException
268     {
269         
270         String sqlTypeName = null;
271         
272         switch (type)
273         {
274             case Types.INTEGER:
275                 sqlTypeName = "INTEGER";
276                 break;
277                 
278             case Types.BIGINT:
279                 sqlTypeName = "LONG";
280                 break;
281
282             case Types.VARCHAR:
283                 sqlTypeName = "VARCHAR";
284                 break;
285
286             case Types.DATE:
287                 sqlTypeName = "DATE";
288                 break;
289
290             case Types.TIMESTAMP:
291                 sqlTypeName = "TIMESTAMP";
292                 break;
293
294             case Types.DOUBLE:
295                 sqlTypeName = "DOUBLE";
296                 break;
297                 
298         }
299
300         if (sqlTypeName==null)
301         {
302             String args[] = {String.valueOf(type)};
303             String msg = Errors.INVALID_DATATYPE;
304             msg = MessageFormat.format(msg, args);
305             throw new RecordsetException(msg);
306         }
307         
308         append(fieldName, sqlTypeName, type);
309         
310     }
311
312     /**
313      * Add a record to the recordset and set record number position
314      * to the new inserted record
315      *
316      */
317     public void addNew()
318     {
319         
320         HashMap values = new HashMap ();
321         Iterator i = _fields.keySet().iterator();
322         while (i.hasNext())
323         {
324             String f = (String )i.next();
325             values.put(f, null);
326         }
327         
328         _data.add(new Record(values));
329         
330         /* set record number */
331         _recordNumber++;
332                 
333     }
334
335     /**
336      * Set record position inside recordset
337      * @param recNum Record Number (0...getRecordCount()-1)
338      * @throws Throwable
339      */
340     public void setRecordNumber(int recNum) throws RecordsetException
341     {
342         
343         checkRecordPosition(recNum);
344         _recordNumber = recNum;
345         
346     }
347
348     /**
349      * Set field value for current record (determined by getRecordNumber())
350      * @param fieldName Field Name
351      * @param value Field Value (Date, String, int, double, null)
352      * @throws Throwable
353      */
354     public void setValue(String fieldName, Object value) throws RecordsetException
355     {
356
357         checkRecordPosition();
358         Record rec = (Record)_data.get(_recordNumber );
359         rec.setValue(fieldName, value);
360         
361     }
362
363     /**
364      * Return field value given a field name
365      * @param fieldName Field Name. May be reserved field names: _rowIndex (0...N-1) or _rowNumber (1...N)
366      * @return
367      * @throws Throwable
368      */
369     public Object getValue(String fieldName) throws Throwable
370     {
371
372         /* check for valid cursor position */
373         checkRecordPosition();
374                 
375         /* special treatment for reserved field names */
376         if (fieldName.equals("_rowIndex"))
377         {
378             return new Integer (_recordNumber);
379         }
380         else if (fieldName.equals("_rowNumber"))
381         {
382             return new Integer (_recordNumber + 1);
383         }
384         else
385         {
386             Record rec = (Record)_data.get(_recordNumber );
387             return rec.getFieldValue(fieldName);
388         }
389
390     }
391
392     /**
393      * Fill recordset with resultset data and metadata. It is the
394      * responsability of the caller of this method to close the resultset
395      * and other jdbc objects involved. The resultset must be positioned before
396      * the first record
397      * @param rs Resultset
398      * @throws Throwable
399      */
400     private void loadRecords(java.sql.ResultSet rs) throws Throwable
401     {
402
403         /* default fields */
404         //append("_rowIndex", "INTEGER", Types.INTEGER);
405 //append("_rowNumber", "INTEGER", Types.INTEGER);
406
407         /* load field definitions */
408         ResultSetMetaData md = rs.getMetaData();
409         int cols = md.getColumnCount();
410         for (int i=1;i<=cols;i++)
411         {
412             append(md.getColumnName(i).toLowerCase(), md.getColumnTypeName(i), md.getColumnType(i));
413         }
414         
415         /* load data */
416         while (rs.next())
417         {
418             HashMap flds = new HashMap (cols);
419             for (int i=1;i<=cols;i++)
420             {
421                 flds.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
422             }
423             _data.add(new Record(flds));
424         }
425         
426     }
427
428     /**
429      * Create a recordset given a resultset. It is the
430      * responsability of the caller of this method to close the resultset
431      * and other jdbc objects involved.
432      * @param rs ResultSet positiones before the first record
433      * @throws Throwable
434      */
435     public Recordset(ResultSet rs) throws Throwable
436     {
437         loadRecords(rs);
438     }
439
440     /**
441      * Creates a recordset given a SQL query
442      * @param conn Database Connection
443      * @param sql SQL Query that returns a Resultset
444      * @throws Throwable
445      */
446     public Recordset(Connection conn, String sql) throws Throwable
447     {
448         this(conn, sql, 0);
449     }
450
451     /**
452      * Creates a recordset given a SQL query.
453      * @param conn Database Connection
454      * @param sql SQL Query that returns a Resultset
455      * @param limit Maximum number of rows to read from the DataBase
456      * @throws Throwable
457      */
458     public Recordset(java.sql.Connection conn, String sql, int limit) throws Throwable
459     {
460         
461         ResultSet rs = null;
462         Statement stmt = null;
463
464         try
465         {
466
467             /* execute query */
468             stmt = conn.createStatement();
469             
470             if (limit > 0)
471                 stmt.setMaxRows(limit);
472             
473             rs = stmt.executeQuery(sql);
474             loadRecords(rs);
475             
476         }
477         catch (Throwable e)
478         {
479             throw e;
480         }
481         finally
482         {
483             if( rs != null ) rs.close();
484             if( stmt != null ) stmt.close();
485         }
486         
487     }
488
489     /**
490      * Default constructor used when creating
491      * the recordset from code (a "manufactured" recordset)
492      *
493      */
494     public Recordset()
495     {
496         /* default fields */
497         //append("_rowIndex", "INTEGER", Types.INTEGER);
498 //append("_rowNumber", "INTEGER", Types.INTEGER);
499 }
500
501     /**
502      * Move pointer to next record. Returns true if not EOF
503      * @return
504      */
505     public boolean next()
506     {
507         if (_recordNumber < (_data.size()-1))
508         {
509             _recordNumber++;
510             return true;
511         }
512         else
513         {
514             return false;
515         }
516     }
517
518     /**
519      * Returns a text-formatted report with the
520      * structure of the recordset, very usefull for
521      * debugging purposes
522      */
523     public String toString()
524     {
525         StringWriter sw = new StringWriter (1000);
526         PrintWriter pw = new PrintWriter (sw);
527         
528         pw.println("Recordset Information");
529         pw.println("Record Count: " + getRecordCount());
530         pw.println("Field Count: " + getFieldCount());
531         pw.println("Structure:");
532         pw.println("----------------------------------");
533         pw.println("NAME|SQL-TYPE-NAME|JDBC-TYPE-ID");
534         
535         Iterator i = _fields.values().iterator();
536         while (i.hasNext())
537         {
538             RecordsetField f = (RecordsetField)i.next();
539             
540             pw.println(f.getName() + "|" + f.getSqlTypeName() + "|" + f.getType());
541         }
542         
543         return sw.toString();
544     }
545
546     /**
547      * Set cursor position before first record,
548      * it is like a "rewind" command
549      *
550      */
551     public void top()
552     {
553         _recordNumber = -1;
554     }
555
556     /**
557      * Set cursor position on first record
558      * @throws Throwable
559      */
560     public void first() throws Throwable
561     {
562         setRecordNumber(0);
563     }
564
565     /**
566      * Set cursor position on last record
567      * @throws Throwable
568      */
569     public void last() throws Throwable
570     {
571         setRecordNumber(_data.size()-1);
572     }
573
574     /**
575      * Delete record (from memory)
576      * @param recNum Record Number (0..N-1)
577      */
578     public void delete(int recNum) throws Throwable
579     {
580         checkRecordPosition(recNum);
581         _data.remove(recNum);
582     }
583
584     /**
585      * Return a Recordset field object describing its properties
586      * @param fieldName Field name to locate the field object
587      * @return Reference to Recordset field
588      * @throws Throwable if fieldName does not exist in Recordset metadata
589      */
590     public RecordsetField getField(String fieldName) throws Throwable
591     {
592         if (_fields.containsKey(fieldName))
593             return (RecordsetField)_fields.get(fieldName);
594         else
595             throw new Throwable ("Field not found:" + fieldName);
596     }
597
598     /**
599      * Copy record values from this recordset to
600      * a destination recordset, using the current
601      * record of both recordsets. Destination recordset
602      * fields that match names with source recordset fields
603      * must be of the same type too. It does not matter if some
604      * fields from the source recordset are not defined in the destination
605      * recordset. Only name-matching fields will be considered
606      * for the operation.
607      * @param rs Destination recordsets
608      * @throws Throwable
609      */
610     public void copyValues(Recordset rs) throws Throwable
611     {
612         checkRecordPosition();
613         HashMap flds = rs.getFields();
614         Iterator i = _fields.values().iterator();
615         while (i.hasNext())
616         {
617             RecordsetField f = (RecordsetField)i.next();
618             String name = f.getName();
619             if (flds.containsKey(name))
620             {
621                 rs.setValue(name, getValue(name));
622             }
623         }
624         
625     }
626
627     /**
628      * Wrapper method for getValue() - avoids casting the data type
629      * @param colName Column name to retrieve its value from the current record
630      * @return The column value in its native data type
631      * @throws Throwable
632      */
633     public String getString(String colName) throws Throwable
634     {
635         Object obj = getValue(colName);
636         if (obj!=null)
637             return String.valueOf(obj);
638         else
639             return null;
640     }
641
642     /**
643      * Wrapper method for getValue() - avoids casting the data type
644      * @param colName Column name to retrieve its value from the current record
645      * @return The column value in its native data type
646      * @throws Throwable
647      */
648     public java.util.Date getDate(String colName) throws Throwable
649     {
650         java.util.Date d = null;
651         d = (java.util.Date )getValue(colName);
652         return d;
653     }
654
655     /**
656      * Wrapper method for getValue() - avoids casting the data type
657      * @param colName Column name to retrieve its value from the current record
658      * @return The column value in its native data type
659      * @throws Throwable
660      */
661     public double getDouble(String colName) throws Throwable
662     {
663         Double d = new Double ( String.valueOf(getValue(colName)) );
664         return d.doubleValue();
665     }
666
667     /**
668      * Wrapper method for getValue() - avoids casting the data type
669      * @param colName Column name to retrieve its value from the current record
670      * @return The column value in its native data type
671      * @throws Throwable
672      */
673     public int getInt(String colName) throws Throwable
674     {
675         Integer i = new Integer (String.valueOf(getValue(colName)));
676         return i.intValue();
677     }
678
679     /**
680      * Wrapper method for getValue() - avoids casting the data type
681      * @param colName Column name to retrieve its value from the current record
682      * @return The column value in its native data type
683      * @throws Throwable
684      */
685     public Integer getInteger(String colName) throws Throwable
686     {
687         Integer i = new Integer (String.valueOf(getValue(colName)));
688         return i;
689     }
690
691     /**
692      * Tests if the give column value is null for the
693      * current record
694      * @param colName Column name
695      * @return TRUE if the value is null
696      * @throws Throwable If record position is not valid or the column does not exist in the recordset
697      */
698     public boolean isNull(String colName) throws Throwable
699     {
700         if (getValue(colName)==null)
701             return true;
702         else
703             return false;
704     }
705
706     /**
707      * Check if recordset contains field with a given name
708      * @param name Name of the field to check its existence
709      * @return TRUE if field exists, FALSE if not
710      */
711     public boolean containsField(String name)
712     {
713         if (_fields.containsKey(name))
714             return true;
715         else
716             return false;
717     }
718
719     /**
720      * Tests if a given record number represents
721      * a valid record position in the Recordset
722      * @param recNum Record number (between 0...N-1 where N is the number of records)
723      * @throws RecordsetException If the test fails
724      */
725     private void checkRecordPosition(int recNum) throws RecordsetException
726     {
727         if (recNum < 0 || recNum > _data.size()-1)
728         {
729
730             StringBuffer errMsg = new StringBuffer ();
731             
732             errMsg.append("Invalid record position: " + recNum + "; ");
733             if (recNum==-1)
734                 errMsg.append("After creating a Recordset you must move to a valid record using next(), first(), last() or setRecordNumber() methods before attempting read/write operations with any record of this Recordset; ");
735             errMsg.append("This Recordset contains " + _data.size() + " records; Set the record position between 0 and N-1 where N is the number of records.");
736
737             throw new RecordsetException(errMsg.toString());
738
739         }
740     }
741
742     /**
743      * Tests if the current record number represents
744      * a valid record position in the Recordset. This overload
745      * reuses the checkRecordPosition(int recNum) method.
746      * @throws RecordsetException If the test fails
747      */
748     private void checkRecordPosition() throws RecordsetException
749     {
750         checkRecordPosition(this._recordNumber);
751     }
752
753     /**
754      * Set the children recordset for the current record
755      * @param rs Children recordset
756      * @throws Throwable If the record position is not valid
757      */
758     public void setChildrenRecordset(Recordset rs) throws Throwable
759     {
760         checkRecordPosition();
761         Record rec = (Record)_data.get(_recordNumber);
762         rec.setChildren(rs);
763     }
764
765     /**
766      * Retrieve current record's children recordset
767      * @return A reference to the children recordset or null if no children recordset exists
768      * @throws Throwable If the record position is not valid
769      */
770     public Recordset getChildrenRecordset() throws Throwable
771     {
772         checkRecordPosition();
773         Record rec = (Record)_data.get(_recordNumber);
774         return rec.getChildren();
775     }
776
777     /**
778      * Sort Recordset data in ascending order by the given column
779      * @param col Name of the column to be used for the sort
780      * @throws Throwable
781      */
782     public void sort(String col) throws Throwable
783     {
784         
785         Comparator comp = new Comp(col);
786         Collections.sort(_data, comp);
787         
788     }
789
790     /**
791      * Comparator inner class that provides sorting support
792      */
793     class Comp implements Serializable , Comparator
794     {
795         /**
796          *
797          */
798         private static final long serialVersionUID = 1L;
799         private String _sortCol = null;
800         
801         public Comp(String colName) throws Throwable
802         {
803             _sortCol = colName;
804             if (!containsField(colName))
805                 throw new Throwable ("Invalid column name passed to sort() method: " + colName);
806         }
807         
808         public int compare(Object o1, Object o2)
809         {
810             
811             Record r1 = (Record)o1;
812             Record r2 = (Record)o2;
813             
814             int result = 0;
815
816             try
817             {
818                 if (r1.getFieldValue(_sortCol)==null)
819                 {
820                     result = 0;
821                 }
822                 else if (r2.getFieldValue(_sortCol)==null)
823                 {
824                     result = 1;
825                 }
826                 else
827                 {
828                     Comparable x1 = (Comparable )r1.getFieldValue(_sortCol);
829                     result = x1.compareTo(r2.getFieldValue(_sortCol));
830                 }
831             }
832             catch (Throwable e)
833             {
834                 System.err.println("SORT ERROR: " + e.getMessage());
835             }
836             
837             return result;
838             
839         }
840
841     }
842
843     /**
844      * Retrieve recordset metadata. This method returns
845      * a Recordset containing the columns: name, typename, typeid.
846      * "typeid" is the java.sql.Type value<br>
847      * There will be one record for each column.
848      * @return Recordset
849      * @throws Throwable
850      */
851     public Recordset getMetaData() throws Throwable
852     {
853
854         // prepare the new Recordset structure
855 Recordset rs = new Recordset();
856         rs.append("name", java.sql.Types.VARCHAR);
857         rs.append("typename", java.sql.Types.VARCHAR);
858         rs.append("typeid", java.sql.Types.INTEGER);
859
860         //get recordset structure
861 HashMap flds = this.getFields();
862         Iterator i = flds.values().iterator();
863
864         // fill the structure with the data
865 while (i.hasNext())
866         {
867                 RecordsetField f = (RecordsetField) i.next();
868                 rs.addNew();
869                 rs.setValue("name", f.getName() );
870                 rs.setValue("typename", f.getSqlTypeName() );
871                 rs.setValue("typeid", new Integer (f.getType()) );
872         }
873                 
874         return rs;
875
876     }
877
878     /**
879      * Duplicate the Recordset structure in a new Recordset without any data
880     * @return Empty Recordset with the same structure
881     * @throws Throwable
882     */
883     public Recordset copyStructure() throws Throwable
884     {
885
886         //new recordset
887 Recordset newRS = new Recordset();
888         
889         //get metadata
890 Recordset infoRS = getMetaData();
891         infoRS.top();
892         while (infoRS.next())
893         {
894                 // obtain the column's data
895 String name = infoRS.getString("name");
896                 int jdbcTypeId = infoRS.getInt("typeid");
897
898                 //add column def
899 newRS.append(name, jdbcTypeId);
900         }
901         
902         return newRS;
903
904     }
905
906     /**
907      * Clear current record values, set every field's value to null
908      */
909     public void clear() throws Throwable
910     {
911         checkRecordPosition();
912         Iterator i = _fields.values().iterator();
913         while (i.hasNext())
914         {
915             RecordsetField f = (RecordsetField)i.next();
916             setValue(f.getName(), null);
917         }
918     }
919
920 }
921

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags