Advertisement

04.30.2008 at 01:34PM PDT, ID: 23366812
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

Implementing resultSet to create another object

Tags: JDBC, Java,J2EE
Hi,
I am using JDBC connection pooling, I have created a custom class with singleton instance for database access, every caller needs to get that instance and can execute queries. I am returning result set instance and caller classes are closing the connections, however if some error occurs or multiple methods parallel access my executeSQL method, pool get exhausted. So I would like to create and implementation of ResultSet so that I can populate whole result set object in to that object and close the connection and return it to the pool in the method itself. How can I do something like this:

public MyRowSet executeSQL(String query){
ResultSet rs = ds.getConnection.createStatement.executeQuery(query);
MyRowset rowset = new rowset();
rowset.populate(rs);
}

I don't know how to implement MyRowSet class and populate method, please guide me for appropriate solution. Any code snippet or article would help.

Thanks for all the help and support.
Start your free trial to view this solution
Question Stats
Zone: Programming
Question Asked By: meena_r
Solution Provided By: CEHJ
Participating Experts: 2
Solution Grade: B
Views: 0
Translate:
Loading Advertisement...
04.30.2008 at 01:37PM PDT, ID: 21474356

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 01:45PM PDT, ID: 21474404

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 02:10PM PDT, ID: 21474577

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 02:17PM PDT, ID: 21474618

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 02:37PM PDT, ID: 21474774

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 04:25PM PDT, ID: 21475409

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 04:29PM PDT, ID: 21475427

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 04:48PM PDT, ID: 21475492

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 04:57PM PDT, ID: 21475526

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 04:59PM PDT, ID: 21475534

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 05:01PM PDT, ID: 21475546

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 05:07PM PDT, ID: 21475562

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 05:14PM PDT, ID: 21475592

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 05:31PM PDT, ID: 21475670

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 05:37PM PDT, ID: 21475704

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.30.2008 at 11:28PM PDT, ID: 21476819

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.16.2008 at 11:36AM PDT, ID: 21585288

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.17.2008 at 04:11AM PDT, ID: 21588734

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.17.2008 at 06:54AM PDT, ID: 21589335

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.27.2008 at 05:05PM PDT, ID: 21656683

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.27.2008 at 05:13PM PDT, ID: 21656719

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.28.2008 at 12:16AM PDT, ID: 21658323

Rank: Genius

All comments and solutions are available to Premium Service Members only.

Start your 7-day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • Automotive
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Displays / Monitors
  • Handhelds / PDAs
  • Components
  • Peripherals
  • Laptops/Notebooks
  • Servers
  • Misc
  • Apple
  • Embedded Hardware
  • Networking Hardware
  • Storage
  • Desktops
  • New Users
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMware
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Virtualization
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • Web Computing
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Consulting
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMware
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Automation
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Web Services
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Web Computing
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Lounge
  • Business Travel
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
  • Automotive
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
04.30.2008 at 01:37PM PDT, ID: 21474356

Rank: Genius

You could either contain the query in nested collection or you could use a CachedRowSet
 
04.30.2008 at 01:45PM PDT, ID: 21474404
Thanks for response, could you please explain it more?
I am reading CachedRowSet and planning to implement it in my class however I am not sure about managing the cache time , memory requirement and freeing the memory when I want to? I am new to JDBC advance concept so please help me in understanding this.

Thanks.
 
04.30.2008 at 02:10PM PDT, ID: 21474577

Rank: Genius

Any particular reason why you're going for a CachedRowSet rather than a collection? The former is harder ;-)
 
04.30.2008 at 02:17PM PDT, ID: 21474618
Because I didn't know that :) When I saw CachedRowSet interface then I realized how hard it is to provide implementation for all these methods. I am wondering how easy it would be to use a nested collection and make it generic for all the queries?
Thanks
 
04.30.2008 at 02:37PM PDT, ID: 21474774

Rank: Genius

>>I am wondering how easy it would be to use a nested collection and make it generic for all the queries?

Very easy ;-) See the following. It creates a TableModel but you could just as easily create a List of List:

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
public TableModel resultSetToTableModel(ResultSet rs) {
          try {
               // The below just for searches
               ResultSetMetaData metaData = rs.getMetaData();
               int numberOfColumns = metaData.getColumnCount();
               Vector columnNames = new Vector();
               // Get the column names
               for (int column = 0; column < numberOfColumns; column++) {
                    columnNames.addElement(metaData.getColumnLabel(column + 1));
               }
               // Get all rows.
               Vector rows = new Vector();
               while (rs.next()) {
                    Vector newRow = new Vector();
                    for (int i = 1; i <= numberOfColumns; i++) {
                         newRow.addElement(rs.getObject(i));
                    }
                    rows.addElement(newRow);
               }
               return new DefaultTableModel(rows, columnNames);
          }
          catch (Exception e) {
               e.printStackTrace();
               return null;
          }
     }
Open in New Window
Accepted Solution
 
04.30.2008 at 04:25PM PDT, ID: 21475409

Rank: Genius

> When I saw CachedRowSet interface then I realized how hard it is to provide implementation for all these methods.

implementations already exist, you don't need to build your own :)
and loading your entire result set into a collection is a tad dangerous.
what exactly do u want to achieve by this?

Assisted Solution
 
04.30.2008 at 04:29PM PDT, ID: 21475427

Rank: Genius

the follwoing give a good coverage of what u need

http://www.onjava.com/pub/a/onjava/2004/06/23/cachedrowset.html

 
04.30.2008 at 04:48PM PDT, ID: 21475492
> and loading your entire result set into a collection is a tad dangerous.
I guess CachedRowSet implementation would do the same thing, isn't it? It would load entire resultset while we will call populate(rs) method? or am I missing something?
 
04.30.2008 at 04:57PM PDT, ID: 21475526
I tried to follow CHEJ approach on very intial stage its looking fine, however as OBJECT said I am confused now, below is my implementaiton for simple login, please correct me, as I have to fetch large amount of data as application proceed.
DAC to get connection
SQLRowSet to store collection objects (a java bean) //with getter and setter methods for private fields
SQLQueryUtil to define utility method, it has getSQLRowSet method
Login for logging in.

Please let me know if this is the efficient way of doing things.

Thanks for all the help and support.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
public class SQLRowSet {
 
    private List _rows;
    private List _columns;
    private boolean _hasRows = false;
    private int _columnCount = 0;
    private int _rowCount = 0;
 
    public SQLRowSet(){
        
    }
    
    public SQLRowSet(List rows, List columns,int columnCount,int rowCount){
        this.setRows(rows);
        this.setColumns(columns);
        this.setColumnCount(columnCount);
        this.setRowCount(rowCount);
        if(_rowCount != 0){
            _hasRows = true;
        }
        
    }
//SQLQuery.getSQLRowSET()
public SQLRowSet getSQLRowSet(ResultSet rs) throws DACException, {
          try {
               // The below just for searches
               ResultSetMetaData metaData = rs.getMetaData();
               int numberOfColumns = metaData.getColumnCount();
               Vector columnNames = new Vector();
               // Get the column names
               for (int column = 0; column < numberOfColumns; column++) {
                    columnNames.addElement(metaData.getColumnLabel(column + 1));
               }
               // Get all rows.
               Vector rows = new Vector();
               int rowCount = 0;
               while (rs.next()) {
                    Vector newRow = new Vector();
                    for (int i = 1; i <= numberOfColumns; i++) {
                         newRow.addElement(rs.getObject(i));
                    }
                    rows.addElement(newRow);
                    rowCount++;
               }
              return new SQLRowSet(rows,columnNames,numberOfColumns,rowCount);
          }
          catch (SQLException se) {
              throw new DACException(NMSMnemonics.RS_EXCEPTION,se);
          }           
     }
// DAC would call this method and return rowset
//Login
 private void validateUserLoginInfo(LoginVO loginInfo) throws DACException, NMSException{
        String query = "Select user_domain, user_privilege, is_active from user_profile " +
                "where user_id ='"+loginInfo.getUserID()+"' and user_pwd='"
                +loginInfo.getPassword()+"'";
        int privilege = 0;
        int domain = 0;
        int is_active = 0 ;
        DataAccessCommand dac = DataAccessCommand.getDataAccessCommand();
        SQLRowSet rowSet = dac.executeSQL(query);
        if(rowSet.hasRows()){
            System.out.println("Rowset has rows" + rowSet.getRowCount());
            List rows =  (List) rowSet.getRows().get(0);
            if(rows.isEmpty()){
                System.out.println("ArrayList is Empty");
                throw new NMSException("No Records for the user ID");
            }
            else {
               List columnName = (List) rowSet.getColumns();
               domain = (Integer)rows.get(columnName.indexOf("USER_DOMAIN"));
               privilege = (Integer)rows.get(columnName.indexOf("USER_PRIVILEGE"));
               is_active = (Integer)rows.get(columnName.indexOf("IS_ACTIVE"));
               System.out.println("domain" + domain +"privilege" +privilege +"is_active" + is_active);
            }
        }
     }
Open in New Window
 
04.30.2008 at 04:59PM PDT, ID: 21475534

Rank: Genius

depends on the implementation, but it certainly saves the hassles and errors associated with building it from scratch. No need to reinvent the wheel :)

 
04.30.2008 at 05:01PM PDT, ID: 21475546

Rank: Genius

yes its very inefficient :) What exactly is it you are trying to achieve?
If you're dealing with lots of data you'd be better off processing one row at a time if possible

 
04.30.2008 at 05:07PM PDT, ID: 21475562
I know it is inefficient, just wanted to confirm and know the efficient way :)

For conversion from resultSet to CachedRowSet I could use populate method (if I implement standerd implementation) And In that case I do not have any control over the way it is impelmented, however I have to convert my resultset in to java bean and send it to client either in object format or in XML (through web service) So I think I have to process one row at a time from Rowset and store them in the bean, or is there any other way of doing this?

Thanks
 
04.30.2008 at 05:14PM PDT, ID: 21475592

Rank: Genius

> So I think I have to process one row at a time from Rowset and store them in the bean, or is there any other way of doing this?

jusyt create your beans directly from result set, theres no need for an intermediate class. Thats just inefficient and unnecessary.
 
04.30.2008 at 05:31PM PDT, ID: 21475670
As I mentioned, I have one DAC intance on which I am calling method executeSQL, it takes the query and create connections, earlier it was returning resultset and I was doing same as you suggested, however if two or more methods call executeSQL for different queries so my method creates that many connection, and after some time I get pool exhausted error as I don't have connections until a method completes its processing, so I was thinking that if one method calls executeSQL the method should return some object like resultset and close the connection. This method is generic so I can not set a generic bean as I would need specific ones in result. That's the reason I thought about intermediate class.
Any suggestions?
 
04.30.2008 at 05:37PM PDT, ID: 21475704

Rank: Genius

If you need more connections then increase your pool size, and make sure you are returing connection to the pool once done.
Reading the result set into an intermediate structure will be no faster than reading it into your beans so is not going to help you, its just going to increase memory usage (and probably be a little slower).
If you can't avoid using an intermediate structure then CachedRowSet is what you're after.

Assisted Solution
 
04.30.2008 at 11:28PM PDT, ID: 21476819

Rank: Genius

>>I guess CachedRowSet implementation would do the same thing, isn't it?

Correct. A CachedRowSet in your case will be quite a bit heavier than using a collection
 
05.16.2008 at 11:36AM PDT, ID: 21585288
I did not respond to this questions, as I am totally confused with whether I should use a custom (or open source) implementation of rowset or go ahead with resulset...so trying to prototype with result set first and then with rowset (if resultset still has some problem with pooling) as it is easy to test and check...and will post the conclusion. Thanks for all the help and support.
 
05.17.2008 at 04:11AM PDT, ID: 21588734

Rank: Genius

Why reinvent the wheel? the jdk already comes with a perfectly good rowset implementation. Writing your own is not only a waste of time but almost certainly error prone.


 
05.17.2008 at 06:54AM PDT, ID: 21589335

Rank: Genius

>>however I have to convert my resultset in to java bean and send it to client either in object format or in XML

All you need to do (if the client is expecting the full result set) is to convert the code you have to a bean. You'd have a bean that encapsulates a List<List<Object>> (the result set).

And no, it's not efficient. In fact it's the most efficient way of carrying out your requirements if i've understood them correctly
Assisted Solution
 
05.27.2008 at 05:05PM PDT, ID: 21656683
closing the question for now by partially dividing the points as I am still confused with both the advices, I guess CEHJ advice is more appropriate (not sure though).
Thanks to both of you for your time.
 
05.27.2008 at 05:13PM PDT, ID: 21656719

Rank: Genius

>  I guess CEHJ advice is more appropriate (not sure though).

you'll find its not, theres no need to reinvent what is already provided by the jdk.
You'll just waste time and introduce bugs

 
05.28.2008 at 12:16AM PDT, ID: 21658323

Rank: Genius

:-)
 
 
20080236-EE-VQP-29 / EE_QW_2_20070628