Solved

Web Services // NetBeans

Posted on 2006-11-14
8
569 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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

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

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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:
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now