You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Mohammed Zabin <jo...@gmail.com> on 2007/06/21 12:45:12 UTC
Null
Hi All
Anyone knows how to deal with null values in JDBC ResultSet??
I am trying to render a table in jsp page that read its value from the
database, sometimes, the database returns null values, and so, the whole
table couldn't be rendered. Is there any way to deal with null values.
Thanks
Re: Null
Posted by Reinhardt Christiansen <rh...@sympatico.ca>.
If you check the Java API, wasNull() _is_ a method of the interface
ResultSet. (Sorry, I mistakenly referred to it as isNull() before I started
giving you the example.) If your program contains:
import java.sql.ResultSet;
you should be able to see the wasNull() method without any difficulties.
The example I gave was adapted from a program that I created a while back as
a JDBC example for use in database courses. I've run the program
successfully many times. If your rs.next() compiles, I see no reason why
rs.wasNull() would fail to compile.
--
Rhino
----- Original Message -----
From: "Mohammed Zabin" <jo...@gmail.com>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Sunday, June 24, 2007 7:24 AM
Subject: Re: Null
> Thank you very much for your detailed illustration. But the compiler
> complies that there is no such methods isNull() and wasNull()
> ???
>
>
> On 6/21/07, Reinhardt Christiansen <rh...@sympatico.ca> wrote:
>>
>>
>> ----- Original Message -----
>> From: "Mohammed Zabin" <jo...@gmail.com>
>> To: "Tomcat Users List" <us...@tomcat.apache.org>
>> Sent: Thursday, June 21, 2007 6:45 AM
>> Subject: Null
>>
>>
>> > Hi All
>> >
>> > Anyone knows how to deal with null values in JDBC ResultSet??
>> >
>> > I am trying to render a table in jsp page that read its value from the
>> > database, sometimes, the database returns null values, and so, the
>> > whole
>> > table couldn't be rendered. Is there any way to deal with null values.
>> >
>>
>> The correct way to detect nulls in a JDBC ResultSet is rather different
>> from
>> the replies you've had so far. You should detect the presence of nulls in
>> a
>> given result set row by using the ResultSet.isNull() method.
>>
>> So, let's say that we have a result set called 'rs' (you will probably
>> have
>> used a Statement or PreparedStatement or some other technique to get your
>> result set from the database):
>>
>> Statement stmt = ____________; //an arbitrary Statement object
>> ResultSet rs = stmt.executeQuery("select employee_number, last_name,
>> first_name, middle_initial from employee_table");
>>
>> Now, on to the matter of nulls. Let's say that you are working your way
>> through your ResultSet. As you know, you get the rows of the ResultSet
>> one
>> row at a time, using the next() method, as follows:
>>
>> while (rs.next()) { //as long as there are rows in the result set
>> //handle one row of the result set
>> }
>>
>> Within the while loop, each iteration of the loop will handle one
>> complete
>> row of the result set.
>>
>> Let's assume you don't know which columns of the result set are null and
>> you
>> want to display the values in each column of the result set exactly as
>> they
>> appear in the result set, except that when the value is null, you want to
>> display the word "unknown". Here's what you would need to do:
>>
>>
>> String empno = null;
>> String lastname = null;
>> String firstname = null;
>> String midinit = null;
>>
>> while (rs.next()) {
>>
>> empno = rs.getString("employee_number"); //get the employee number
>> from this row of the result set
>> if (rs.wasNull()) { //if the employee number is null
>> System.out.println("Employee Number is unknown");
>> }
>> else {
>> System.out.println("Employee Number is " + empno);
>> }
>>
>> lastname = rs.getString("last_name"); //get the last name from
>> this
>> row of the result set
>> if (rs.wasNull()) { //if the last name is null
>> System.out.println("Last Name is unknown");
>> }
>> else {
>> System.out.println("Last Name is " + lastname);
>> }
>>
>> firstname = rs.getString("first_name"); //get the first name from
>> this row of the result set
>> if (rs.wasNull()) { //if the first name is null
>> System.out.println("First Name is unknown");
>> }
>> else {
>> System.out.println("First Name is " + firstname);
>> }
>>
>>
>> midinit = rs.getString("middle_initial"); //get the middle initial
>> from this row of the result set
>> if (rs.wasNull()) { //if the middle initial is null
>> System.out.println("Middle Initial is unknown");
>> }
>> else {
>> System.out.println("Middle Initial is " + midinit);
>> }
>>
>> }
>>
>> Although this example uses only result set columns that are being stored
>> in
>> Strings, the technique differs very little for non-text data. Let's say
>> that
>> we had a column called annual_salary in our result set, where the
>> annual_salary was an integer containing the number of dollars that the
>> person earned per year. Let's assume that some people are still
>> negotiating
>> their salaries so that the salary is stored as null for those people
>> until
>> the salary has been fully agreed.. The technique to handle the salary
>> would
>> look like this:
>>
>> int salary = null;
>>
>> while (rs.next()) {
>> salary = getInt("annual_salary"); //get the salary from this row
>> of
>> the result set
>> if (rs.isNull()) { //if the salary is null
>> System.out.println("Annual Salary is unknown");
>> }
>> else {
>> System.out.println("Annual Salary is " + salary);
>> }
>>
>>
>> I hope this helps you with your null handling!
>>
>> --
>> Rhino
>>
>>
>> ---------------------------------------------------------------------
>> To start a new topic, e-mail: users@tomcat.apache.org
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>>
>
--------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.472 / Virus Database: 269.9.1/854 - Release Date: 19/06/2007
1:12 PM
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Null
Posted by Mohammed Zabin <jo...@gmail.com>.
Thank you very much for your detailed illustration. But the compiler
complies that there is no such methods isNull() and wasNull()
???
On 6/21/07, Reinhardt Christiansen <rh...@sympatico.ca> wrote:
>
>
> ----- Original Message -----
> From: "Mohammed Zabin" <jo...@gmail.com>
> To: "Tomcat Users List" <us...@tomcat.apache.org>
> Sent: Thursday, June 21, 2007 6:45 AM
> Subject: Null
>
>
> > Hi All
> >
> > Anyone knows how to deal with null values in JDBC ResultSet??
> >
> > I am trying to render a table in jsp page that read its value from the
> > database, sometimes, the database returns null values, and so, the whole
> > table couldn't be rendered. Is there any way to deal with null values.
> >
>
> The correct way to detect nulls in a JDBC ResultSet is rather different
> from
> the replies you've had so far. You should detect the presence of nulls in
> a
> given result set row by using the ResultSet.isNull() method.
>
> So, let's say that we have a result set called 'rs' (you will probably
> have
> used a Statement or PreparedStatement or some other technique to get your
> result set from the database):
>
> Statement stmt = ____________; //an arbitrary Statement object
> ResultSet rs = stmt.executeQuery("select employee_number, last_name,
> first_name, middle_initial from employee_table");
>
> Now, on to the matter of nulls. Let's say that you are working your way
> through your ResultSet. As you know, you get the rows of the ResultSet one
> row at a time, using the next() method, as follows:
>
> while (rs.next()) { //as long as there are rows in the result set
> //handle one row of the result set
> }
>
> Within the while loop, each iteration of the loop will handle one complete
> row of the result set.
>
> Let's assume you don't know which columns of the result set are null and
> you
> want to display the values in each column of the result set exactly as
> they
> appear in the result set, except that when the value is null, you want to
> display the word "unknown". Here's what you would need to do:
>
>
> String empno = null;
> String lastname = null;
> String firstname = null;
> String midinit = null;
>
> while (rs.next()) {
>
> empno = rs.getString("employee_number"); //get the employee number
> from this row of the result set
> if (rs.wasNull()) { //if the employee number is null
> System.out.println("Employee Number is unknown");
> }
> else {
> System.out.println("Employee Number is " + empno);
> }
>
> lastname = rs.getString("last_name"); //get the last name from this
> row of the result set
> if (rs.wasNull()) { //if the last name is null
> System.out.println("Last Name is unknown");
> }
> else {
> System.out.println("Last Name is " + lastname);
> }
>
> firstname = rs.getString("first_name"); //get the first name from
> this row of the result set
> if (rs.wasNull()) { //if the first name is null
> System.out.println("First Name is unknown");
> }
> else {
> System.out.println("First Name is " + firstname);
> }
>
>
> midinit = rs.getString("middle_initial"); //get the middle initial
> from this row of the result set
> if (rs.wasNull()) { //if the middle initial is null
> System.out.println("Middle Initial is unknown");
> }
> else {
> System.out.println("Middle Initial is " + midinit);
> }
>
> }
>
> Although this example uses only result set columns that are being stored
> in
> Strings, the technique differs very little for non-text data. Let's say
> that
> we had a column called annual_salary in our result set, where the
> annual_salary was an integer containing the number of dollars that the
> person earned per year. Let's assume that some people are still
> negotiating
> their salaries so that the salary is stored as null for those people until
> the salary has been fully agreed.. The technique to handle the salary
> would
> look like this:
>
> int salary = null;
>
> while (rs.next()) {
> salary = getInt("annual_salary"); //get the salary from this row of
> the result set
> if (rs.isNull()) { //if the salary is null
> System.out.println("Annual Salary is unknown");
> }
> else {
> System.out.println("Annual Salary is " + salary);
> }
>
>
> I hope this helps you with your null handling!
>
> --
> Rhino
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: Null
Posted by Reinhardt Christiansen <rh...@sympatico.ca>.
----- Original Message -----
From: "Mohammed Zabin" <jo...@gmail.com>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Thursday, June 21, 2007 6:45 AM
Subject: Null
> Hi All
>
> Anyone knows how to deal with null values in JDBC ResultSet??
>
> I am trying to render a table in jsp page that read its value from the
> database, sometimes, the database returns null values, and so, the whole
> table couldn't be rendered. Is there any way to deal with null values.
>
The correct way to detect nulls in a JDBC ResultSet is rather different from
the replies you've had so far. You should detect the presence of nulls in a
given result set row by using the ResultSet.isNull() method.
So, let's say that we have a result set called 'rs' (you will probably have
used a Statement or PreparedStatement or some other technique to get your
result set from the database):
Statement stmt = ____________; //an arbitrary Statement object
ResultSet rs = stmt.executeQuery("select employee_number, last_name,
first_name, middle_initial from employee_table");
Now, on to the matter of nulls. Let's say that you are working your way
through your ResultSet. As you know, you get the rows of the ResultSet one
row at a time, using the next() method, as follows:
while (rs.next()) { //as long as there are rows in the result set
//handle one row of the result set
}
Within the while loop, each iteration of the loop will handle one complete
row of the result set.
Let's assume you don't know which columns of the result set are null and you
want to display the values in each column of the result set exactly as they
appear in the result set, except that when the value is null, you want to
display the word "unknown". Here's what you would need to do:
String empno = null;
String lastname = null;
String firstname = null;
String midinit = null;
while (rs.next()) {
empno = rs.getString("employee_number"); //get the employee number
from this row of the result set
if (rs.wasNull()) { //if the employee number is null
System.out.println("Employee Number is unknown");
}
else {
System.out.println("Employee Number is " + empno);
}
lastname = rs.getString("last_name"); //get the last name from this
row of the result set
if (rs.wasNull()) { //if the last name is null
System.out.println("Last Name is unknown");
}
else {
System.out.println("Last Name is " + lastname);
}
firstname = rs.getString("first_name"); //get the first name from
this row of the result set
if (rs.wasNull()) { //if the first name is null
System.out.println("First Name is unknown");
}
else {
System.out.println("First Name is " + firstname);
}
midinit = rs.getString("middle_initial"); //get the middle initial
from this row of the result set
if (rs.wasNull()) { //if the middle initial is null
System.out.println("Middle Initial is unknown");
}
else {
System.out.println("Middle Initial is " + midinit);
}
}
Although this example uses only result set columns that are being stored in
Strings, the technique differs very little for non-text data. Let's say that
we had a column called annual_salary in our result set, where the
annual_salary was an integer containing the number of dollars that the
person earned per year. Let's assume that some people are still negotiating
their salaries so that the salary is stored as null for those people until
the salary has been fully agreed.. The technique to handle the salary would
look like this:
int salary = null;
while (rs.next()) {
salary = getInt("annual_salary"); //get the salary from this row of
the result set
if (rs.isNull()) { //if the salary is null
System.out.println("Annual Salary is unknown");
}
else {
System.out.println("Annual Salary is " + salary);
}
I hope this helps you with your null handling!
--
Rhino
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Null
Posted by Hassan Schroeder <ha...@gmail.com>.
On 6/21/07, Mohammed Zabin <jo...@gmail.com> wrote:
> I am trying to render a table in jsp page that read its value from the
> database, sometimes, the database returns null values, and so, the whole
> table couldn't be rendered. Is there any way to deal with null values.
Pass your ResultSet into the request, and use JSTL to display -- that
handles null values as empty strings by default, not to mention giving
you a much cleaner presentation layer overall...
FWIW,
--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Null
Posted by Brantley Hobbs <sb...@uga.edu>.
Mohammed,
While it may not work in all browsers, you can try the "empty-cells" CSS
property in the table's style.
Render the table tag thusly: <table style="empty-cells: show;">...</table>
An alternative is what the other guy said; simply check for nulls before
rendering, and render a non-breaking space if you have a null. I'd
shoot for using EL rather than pure java for that though.
Good luck!
Brantley
Mohammed Zabin wrote:
> Hi All
>
> Anyone knows how to deal with null values in JDBC ResultSet??
>
> I am trying to render a table in jsp page that read its value from the
> database, sometimes, the database returns null values, and so, the whole
> table couldn't be rendered. Is there any way to deal with null values.
>
> Thanks
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Null
Posted by Steve Ochani <oc...@ncc.edu>.
> options:
> define null as empty string e.g.
> String null = "";
null is a reserved word in java, you can't redfine it.
> OR
> change compare to == ""
>
??
if (null == rs.getString("col_foo"))
works just fine.
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html#getString(int)
Returns:
the column value; if the value is SQL NULL, the value returned is null
> HTH
> M--
> This email message and any files transmitted with it contain
> confidential information intended only for the person(s) to whom this
> email message is addressed. If you have received this email message
> in error, please notify the sender immediately by telephone or email
> and destroy the original message without making a copy. Thank you.
>
> ----- Original Message -----
> From: "Tim Funk" <fu...@joedog.org>
> To: "Tomcat Users List" <us...@tomcat.apache.org>
> Sent: Thursday, June 21, 2007 7:24 AM
> Subject: Re: Null
>
>
> > The syntax below is correct. There must be something else
> > syntactically incorrect in your jsp causing your woes.
> >
> > -Tim
> >
> > Mohammed Zabin wrote:
> >> I tried it the other way, if( rs.getString("field") == null ) but
> >> the compiler plames that null can't be compared to string....
> >>
> >> On 6/21/07, Tim Funk <fu...@joedog.org> wrote:
> >>>
> >>>
> >>> if (null == rs.getString("col_foo")) {
> >>> out.println("<td> </td>");
> >>> } else {
> >>> // Evil since this doesn't escape the xml - for edutainment
> >>> only out.println("<td>" + rs.getString("col_foo") + "</td>");
> >>> }
> >>>
> >>> -Tim
> >>>
> >>> Mohammed Zabin wrote:
> >>> > Hi All
> >>> >
> >>> > Anyone knows how to deal with null values in JDBC ResultSet??
> >>> >
> >>> > I am trying to render a table in jsp page that read its value
> >>> > from the database, sometimes, the database returns null values,
> >>> > and so, the
> >>> whole
> >>> > table couldn't be rendered. Is there any way to deal with null
> >>> > values.
> >>> >
> >
> > --------------------------------------------------------------------
> > - To start a new topic, e-mail: users@tomcat.apache.org To
> > unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org For
> > additional commands, e-mail: users-help@tomcat.apache.org
> >
> >
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org To unsubscribe,
> e-mail: users-unsubscribe@tomcat.apache.org For additional commands,
> e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Null
Posted by Martin Gainty <mg...@hotmail.com>.
options:
define null as empty string e.g.
String null = "";
OR
change compare to == ""
HTH
M--
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed. If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy. Thank you.
----- Original Message -----
From: "Tim Funk" <fu...@joedog.org>
To: "Tomcat Users List" <us...@tomcat.apache.org>
Sent: Thursday, June 21, 2007 7:24 AM
Subject: Re: Null
> The syntax below is correct. There must be something else syntactically
> incorrect in your jsp causing your woes.
>
> -Tim
>
> Mohammed Zabin wrote:
>> I tried it the other way, if( rs.getString("field") == null ) but the
>> compiler plames that null can't be compared to string....
>>
>> On 6/21/07, Tim Funk <fu...@joedog.org> wrote:
>>>
>>>
>>> if (null == rs.getString("col_foo")) {
>>> out.println("<td> </td>");
>>> } else {
>>> // Evil since this doesn't escape the xml - for edutainment only
>>> out.println("<td>" + rs.getString("col_foo") + "</td>");
>>> }
>>>
>>> -Tim
>>>
>>> Mohammed Zabin wrote:
>>> > Hi All
>>> >
>>> > Anyone knows how to deal with null values in JDBC ResultSet??
>>> >
>>> > I am trying to render a table in jsp page that read its value from the
>>> > database, sometimes, the database returns null values, and so, the
>>> whole
>>> > table couldn't be rendered. Is there any way to deal with null values.
>>> >
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Null
Posted by Tim Funk <fu...@joedog.org>.
The syntax below is correct. There must be something else syntactically
incorrect in your jsp causing your woes.
-Tim
Mohammed Zabin wrote:
> I tried it the other way, if( rs.getString("field") == null ) but the
> compiler plames that null can't be compared to string....
>
> On 6/21/07, Tim Funk <fu...@joedog.org> wrote:
>>
>>
>> if (null == rs.getString("col_foo")) {
>> out.println("<td> </td>");
>> } else {
>> // Evil since this doesn't escape the xml - for edutainment only
>> out.println("<td>" + rs.getString("col_foo") + "</td>");
>> }
>>
>> -Tim
>>
>> Mohammed Zabin wrote:
>> > Hi All
>> >
>> > Anyone knows how to deal with null values in JDBC ResultSet??
>> >
>> > I am trying to render a table in jsp page that read its value from the
>> > database, sometimes, the database returns null values, and so, the
>> whole
>> > table couldn't be rendered. Is there any way to deal with null values.
>> >
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Null
Posted by Mohammed Zabin <jo...@gmail.com>.
I tried it the other way, if( rs.getString("field") == null ) but the
compiler plames that null can't be compared to string....
On 6/21/07, Tim Funk <fu...@joedog.org> wrote:
>
>
> if (null == rs.getString("col_foo")) {
> out.println("<td> </td>");
> } else {
> // Evil since this doesn't escape the xml - for edutainment only
> out.println("<td>" + rs.getString("col_foo") + "</td>");
> }
>
> -Tim
>
> Mohammed Zabin wrote:
> > Hi All
> >
> > Anyone knows how to deal with null values in JDBC ResultSet??
> >
> > I am trying to render a table in jsp page that read its value from the
> > database, sometimes, the database returns null values, and so, the whole
> > table couldn't be rendered. Is there any way to deal with null values.
> >
> > Thanks
> >
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
Re: Null
Posted by Tim Funk <fu...@joedog.org>.
if (null == rs.getString("col_foo")) {
out.println("<td> </td>");
} else {
// Evil since this doesn't escape the xml - for edutainment only
out.println("<td>" + rs.getString("col_foo") + "</td>");
}
-Tim
Mohammed Zabin wrote:
> Hi All
>
> Anyone knows how to deal with null values in JDBC ResultSet??
>
> I am trying to render a table in jsp page that read its value from the
> database, sometimes, the database returns null values, and so, the whole
> table couldn't be rendered. Is there any way to deal with null values.
>
> Thanks
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Null
Posted by Richard Sayre <ri...@gmail.com>.
There is an ISNULL function in Transact SQL and a NVL function in
PL/SQL that will return a default value if the data is null. That is
if you don't mind writing separate SQL for each database.
On 6/21/07, Mohammed Zabin <jo...@gmail.com> wrote:
> Hi All
>
> Anyone knows how to deal with null values in JDBC ResultSet??
>
> I am trying to render a table in jsp page that read its value from the
> database, sometimes, the database returns null values, and so, the whole
> table couldn't be rendered. Is there any way to deal with null values.
>
> Thanks
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org