Doubt in JDBC

[ChakraPani Margani]

Hai to all members,
First of all thanks to krishnamoorthy for sending answer to my problem.

Today i got another doubt.

My aim is to insert a string in the data base.If that number is already
in the table then i have to display a message like number is already in the table.Otherwise number is to be inserted in the table.

I have developed code on my own.while running the code i got an exception.

I am sending the code with error message.please help me to solve this problem.


CODE IS
----------
import java.sql.*;
class test
{
public void test1(String UIN)
{

ResultSet rs=null;
Statement st=null;
Connection con=null;
PreparedStatement ps=null;
String name="";
boolean flag1=false;
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("DriverLoaded");
con=DriverManager.getConnection("jdbc racle:thin:@localhost:1521 racle","scott","tiger");
System.out.println("Connection established");
st=con.createStatement();
rs=st.executeQuery("select *from uin");
if(rs!=null)
{
while(rs.next())
{
name=rs.getString(1);

if(name.equalsIgnoreCase(UIN))
flag1=true;
}
}


if(!flag1)
{
ps=con.prepareStatement("insert into uin values(?)");
ps.setString(1,name);
int i=ps.executeUpdate();
if(i>0)
System.out.println("Number is Successfully Inserted");
}
else
System.out.println("Number is already inserted");

rs.close();
st.close();
ps.close();
con.close();


}//try
catch(Exception e)
{
e.printStackTrace();
}
}//test()

public static void main(String args[])
{
test t=new test();
String st="s7048223a";
t.test1(st);
}//main()
}//class




ERROR IN RUNTIME IS
----------------------
java.lang.NullPointerException
at test.test1(test.java:27)
at test.main(test.java:61)

[Jesper de Jong]

Line 27 is this line, right?

if(name.equalsIgnoreCase(UIN))

If you get a NullPointerException there, then name must be null. You can't call a method on a null reference. Since you are getting the name from the database, you are probably getting a NULL value from the database.

Check if name is null before calling a method on it:

if(name != null && name.equalsIgnoreCase(UIN))
[ August 24, 2007: Message edited by: Jesper Young ]

[Stan James]

You might "select * from uin where number = newnumber" to see if a row already exists. Once that table has a hundred million rows, that will be a bit faster.

What if another thread inserts newnumber in the time between your select and your insert? If that's a primary key you'll get a duplicate key exception of some kind. Maybe that would solve the whole problem ... just catch that exception. Or if you don't like using exceptions to control flow, you might have to put the select and the update in a transaction. I"m not sure how to form that thing ... maybe with select for update?

[Bert Bates]

sliding this over to the jdbc forum

[krishnamoorthy kitcha]

Hi ChakraPani Margani


Before inserting in the table first you should check the database

Suppose you will be inserting string like 'test' in the employee and field name like name


select * from employee where name='test'

boolean flag;
int i=0;

while (rs.next()){
if the resultset moved , there is record already there
flag =false;
i++;
}


if(i==0)
{

insert into table your record
flag=true;
}


return flag;


IN the client side , you can check with the flag

If false returns , record is already there or some exception

If the true returns , there is no such record, inserted successfully

Check and tell me your feedback.