Finding and Traversing Rows using NetBeans IDE

This section explains the code listed in the previous section, part by part. 

           if (this.txtEmpno.getText().trim().length()==0) {
                this.lblMsg.setText("Provide Employee Number");
                return;
            }

The above code tests whether the user has given an employee number to search or not.  If nothing is given, it simply displays a message and returns.  Proceeding further, we have the following:

            Connection conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://serverjag:1433","sa","");

The above statement connects to the default SQL Server instance available at the server named "serverjag," at the default port "1433," with the user name "sa" and with a blank password.  Proceeding further,  we have the following:

            Statement sql_stmt = conn.createStatement();

The above statement simply creates an object named "sql_stmt" based on the class "Statement" from the connection, which was created previously.

ResultSet rset = sql_stmt.executeQuery( "SELECT ename, sal,
deptno " +
                            " FROM Northwind..emp " +
                            " WHERE empno=" +
this.txtEmpno.getText() +
                            " ORDER BY ename");

The above statement executes the SELECT statement provided. The rows will be fetched into a "ResultSet" object "rset."  Now, we need to check whether it has returned any rows or not using a conditional statement.

if (rset.next()) {
                this.txtEname.setText(rset.getString(1));
                this.txtSal.setText(rset.getString(2));
                this.txtDeptno.setText(rset.getString(3));
                this.lblMsg.setText("");
            }

The above checks whether the result set has any rows to display or not. If it does, we retrieve the values available in columns and assign the same to the text boxes. Here I used positional notation (or index) rather than "named" notation (or column name).  I prefer the latter.

If no row exists, I need to display some message to the user.  The following code accomplishes this: 

else {
     this.lblMsg.setText("Not Found");
}

Once everything is done, I need to close, flush and release all memory resources by issuing the following statements:

            rset.close();
            sql_stmt.close();
            conn.close();

Remarks

In my previous code, I was able to show only one record at any time.  To deal with multiple rows, we generally need to use a grid type of control like "jTable" or implement a "previous,next,first,last" type of user interface.  I shall explain how to implement a better user interface in my next article.

Meanwhile, I can give you a small tip for getting how many records have been fetched (along with showing the first record).  Modify the "if" condition in your "ActionPerformed" event as follows:

int count=0;
      if (rset.next()) {
        this.txtEname.setText(rset.getString(1));
        this.txtSal.setText(rset.getString(2));
        this.txtDeptno.setText(rset.getString(3));
        count++;
        while(rset.next()) count++;
        this.lblMsg.setText("No. of rows found: " +
count);               

            }

To find out the number of records, I simply used a counter to count the number of rows found.  I used a loop to go through all the records fetched and finally count them. The code may be inefficient at this moment, but I shall introduce several other special concepts, such as JDO, in my future articles (which are a bit too complicated to cover at this early moment).

The entire code for this article is freely available in the form of a zip file.  That downloadable solution was developed using NetBeans 4.1 IDE and tested with Microsoft SQL Server 2000 database Enterprise Edition (with Service Pack 3) together with Microsoft Windows 2003 Standard Edition.  I didn't really test it in any other version or platform.  Please follow the respective platform documentation to get it working.

The final issue you need to be aware of is that none of my articles in this series are optimized for performance. Tuning/improving the performance of a Java application is beyond the scope of this article.  I simply wanted to explain the concepts a bit more clearly. 

Any doubts, bugs, errors, suggestions, feedback etc. are highly appreciated at jag_chat@yahoo.com.