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
pete420Asked:
Who is Participating?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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
 
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.

All Courses

From novice to tech pro — start learning today.