Solved

Web Services // NetBeans

Posted on 2006-11-14
8
568 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Uploading files to the web server has become common part of almost any kind of web application. People use different technologies to solve this, but regardless of the technology used, it is always useful to have some kind of progress indicator shown…
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

706 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

21 Experts available now in Live!

Get 1:1 Help Now