Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

Web Services // NetBeans

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
pete420
Asked:
pete420
  • 4
  • 4
1 Solution
 
petiexCommented:
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
 
pete420Author Commented:
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
 
petiexCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
pete420Author Commented:
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
 
petiexCommented:
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
 
pete420Author Commented:
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
 
pete420Author Commented:
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
 
petiexCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now