Solved

Web Services // NetBeans

Posted on 2006-11-14
8
570 Views
Last Modified: 2008-03-17
Hi,

I am trying to develop a simple Web Service which connects to a JDBC database.

In netbeans 5.5,  I right click in the source code window and select 'Enterprise Resources, Use Database'. This outputs the code:

private DataSource getJdbcDb1() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/jdbc/db1");

However with this code I am unsure as to how to do a simple select in a database.
The database IS created and there is information in it. I can connect and view this information from the netbeans GUI.

Any help would be very much appreciated.

Thanks,
Pete
0
Comment
Question by:pete420
  • 4
  • 4
8 Comments
 
LVL 11

Expert Comment

by:petiex
ID: 17942898
try{
   DataSource ds = getJdbcDb1();
   Connection conn = ds.getConnection();
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.execute("Select * From Monkeys where Vicious=0");
} catch (SQLException sqle){
  //handle it
}
0
 

Author Comment

by:pete420
ID: 17948718
Hi,

Thanks for the reply. Much appreciated.

Receiving an error with the above code:

Incompatibile types.  
found   : boolean
required: java.sql.ResultSet
           ResultSet rs = stmt.execute("Select * From TABLE1");


My program now looks like this:
@WebService()
public class databaseCon
{
     
        // TODO implement operation
        //return null;
    /* Web service operation*/
    @WebMethod
    public void simpleSelect()
    {
       try{
           DataSource ds = getJdbcDb1();
           Connection conn = ds.getConnection();
           Statement stmt = conn.createStatement();
           ResultSet rs = stmt.execute("Select * From TABLE1");
            } catch (SQLException sqle)
            {
                System.out.println("Error");
                //handle it
            }
    }//end of simpleSelect

    private DataSource getJdbcDb1() throws NamingException
    {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/jdbc/db1");

    }//end of DataSource
   
}//program end
0
 
LVL 11

Expert Comment

by:petiex
ID: 18000461
Sorry, I forgot, Statement.execute() returns the condition that executing the query resulted in a non-empty ResultSet. After you do that, you need to invoke getResultSet() on the statement to get the ResultSet, if there is one. So once you have your statement from the connection, you'll want something like:
ResultSet rs = null;
if (stmt.execute("SELECT * FROM Table1")){
   rs = stmt.getResultSet();
} else {
   System.out.println("I got nothing.");
}

By the way, since you are using NetBeans 5.5, I'm guessing you haven't installed javadocs yet for context-sensitive help. It's not entirely intuitive. First download the J2SE 5.0 Documentation zip file from here: http://java.sun.com/javase/downloads/index.jsp#docs then, from the Tools menu, choose "Java Platform Manager." Click the Javadoc tab in the dialog that opens, click the "Add Zip/Folder" button, browse to the zip file you downloaded, and you're good to go. Once that's done, if you cntrl + space while the cursor is over any object or method, you get a popup with the relevant javadoc info.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:pete420
ID: 18003388
Hi,

Thanks again for the reply.

I have now changed my code to:

    @WebMethod
    public void simpleSelect()
    {
       try{
           DataSource ds = getJdbcDb1();
           Connection conn = ds.getConnection();
           Statement stmt = conn.createStatement();
           //ResultSet rs = stmt.execute("Select * From TABLE1");
           ResultSet rs = null;
            if (stmt.execute("SELECT * FROM Table1")){
               rs = stmt.getResultSet();
            } else {
               System.out.println("I got nothing.");
            }

            } catch (SQLException sqle)
            {
                System.out.println("Error");
                //handle it
            }
    }//end of simpleSelect


However it now produces an error: unreported exception javax.naming.NamingException; must be caught or declared to be thrown on DataSource ds = getJdbcDb1();


Is my code change correct to what you meant?

Thanks again,
0
 
LVL 11

Accepted Solution

by:
petiex earned 250 total points
ID: 18004144
Looks like it should work, provided you catch the naming exception thrown by your getJdbcDb1 method. All you need to do is add a second catch for NamingException right after the catch for SQLException. Like so:
           try{
              DataSource ds = getJdbcDb1();
              Connection conn = ds.getConnection();
              Statement stmt = conn.createStatement();
              //ResultSet rs = stmt.execute("Select * From TABLE1");
              ResultSet rs = null;
               if (stmt.execute("SELECT * FROM Table1")){
                  rs = stmt.getResultSet();
               } else {
                  System.out.println("I got nothing.");
               }

            } catch (SQLException sqle)
            {
                System.out.println("Error");
                //handle it
            } catch (javax.naming.NamingException ne) {
                System.out.println("curses!");
            }

Then, of course, you will want to do something with the ResultSet object you got back, but that's another ball of wax.
0
 

Author Comment

by:pete420
ID: 18004165
Hi,

Ah I see, no errors showing up now. Could you provide a simple bit of code to output the returned value to the screen so that I can see the query is working?

Thanks,
Pete
0
 

Author Comment

by:pete420
ID: 18004181
Would this work?

        public void simpleSelect()
    {
     try{
              DataSource ds = getJdbcDb1();
              Connection conn = ds.getConnection();
              Statement stmt = conn.createStatement();
              //ResultSet rs = stmt.execute("Select * From TABLE1");
              ResultSet rs = null;
               if (stmt.execute("SELECT * FROM Table1")){
                  rs = stmt.getResultSet();
                  while(rs.next())
                              {
                                    System.out.println(rs.getString(1) +" "
                                    + rs.getString(2) +" "
                                    + rs.getString(3) +" "
                                    + rs.getString(4) +" "
                                    + rs.getString(5));
                              }
               } else {
                  System.out.println("I got nothing.");
               }

            } catch (SQLException sqle)
            {
                System.out.println("Error");
                //handle it
            } catch (javax.naming.NamingException ne) {
                System.out.println("curses!");
            }
     
     
     
        }// end of simple Seletc
0
 
LVL 11

Expert Comment

by:petiex
ID: 18005721
The compiler in my head says that should work fine, but I'm still waiting for that thing to come out of beta.

Remember to free up all your jdbc resources once you are done with them by invoking the close( ) method on the ResultSet, Statement, and Connection objects, in that order. That throws a SQLException as well, so you will want to wrap those invocations in a try/catch block to deal with that.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Summernote and form validation 10 133
C# GridRow get Old/New Value 1 62
Not seen Link button 5 38
how to see all occupied ports on windows 10 laptop 15 65
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question