Click here to Skip to main content
15,882,388 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have problem in storing the records in database. my task is.
i have real estate site with the different properties details. i have two links buy and sale. if client click on buy link then all properties related to buy will be display,in that page client can select specific city,min price,max price, number of bed rooms.so i need to capture all information what client selected inside my website and store it in database.
for that i have written the following code.

Java
ResultSet rs1=st1.executeQuery("select * from getactiondetails where ip_address='"+ip+"' and property_type='"+status+"' and (city='"+city+"' or city is NULL)");
System.out.println("after first select statement");
while(rs1.next())
{  
    System.out.println("after while loop");
    dip=rs1.getString("ip_address");
    dproperty_type=rs1.getString("property_type");
    System.out.println("dprprty_type............"+dproperty_type);
    dcity=rs1.getString("city");
    System.out.println("dcity......................"+dcity);
    dminprice=rs1.getString("minprice");
    dmaxprice=rs1.getString("maxprice");
    dbhk=rs1.getInt("bhk");
    dbaths=rs1.getInt("baths");
    hits=rs1.getInt("no_hits");

 if(dcity==null  && dminprice==null && dmaxprice==null && dbhk==0 && dbaths==0)
{  
    System.out.println("in first if condition........................");
    if(i==0)
    {
    PreparedStatement ps=con.prepareStatement("update getactiondetails set city=?,minprice=?,maxprice=?,bhk=?,baths=?,date=?,time=?,no_hits=? where ip_address=? and property_type=?");
    ps.setString(6,d1);
    ps.setString(7,times);
    ps.setInt(8,1);
    ps.setString(9,ip);
    ps.setString(10,status);
    ps.setString(1,city);
    ps.setString(2,minprice1);
    ps.setString(3,maxprice1);
    ps.setInt(4,beds);
    ps.setInt(5,baths);
    ps.executeUpdate(); 
    i=i+1;
    }
 }
else if(city.equals(dcity) && minprice1.equals(dminprice) && maxprice1.equals(dmaxprice) && beds==dbhk && baths==dbaths)
{   System.out.println("i vlaueeeeeeee...."+i);
    System.out.println("in elsi if condition........................");
    int hits1=hits+1;
    if(i==0)
    {
    PreparedStatement ps=con.prepareStatement("update getactiondetails set date=?,time=?,no_hits=? where ip_address=? and property_type=? and city=? and minprice=? and maxprice=? and bhk=? and baths=?");
    ps.setString(1,d1);
    ps.setString(2,times);
    ps.setInt(3,hits1);
    ps.setInt(9,beds);
    ps.setInt(10,baths);
    ps.setString(4,ip);
    ps.setString(5,status);
    ps.setString(6,city);
    ps.setString(7,minprice1);
    ps.setString(8,maxprice1);
    ps.executeUpdate(); 
    i=i+1;
    }
    
} 
else
{
    if(i==0){
   System.out.println("in else condition......................");
    PreparedStatement ps=con.prepareStatement("insert into getactiondetails(ip_address,property_type,city,minprice,maxprice,bhk,baths,no_hits,date,time) values(?,?,?,?,?,?,?,?,?,?)");
    ps.setString(1,ip);
    ps.setString(2,status);
    ps.setString(3,city);
    ps.setString(4,minprice1);
    ps.setString(5,maxprice1);
    ps.setInt(6,beds);
    ps.setInt(7,baths);
    ps.setInt(8,1);
    ps.setString(9,d1);
    ps.setString(10,times);      
    ps.executeUpdate();
    i=i+1;
    }
}
 
}
if(!ip.equals(dip))
{
    System.out.println("-----------------------------"+dip);
    PreparedStatement ps=con.prepareStatement("insert into getactiondetails(ip_address,property_type,city,minprice,maxprice,bhk,baths,no_hits,date,time) values(?,?,?,?,?,?,?,?,?,?)");
    ps.setString(1,ip);
    ps.setString(2,status);
    ps.setString(3,city);
    ps.setString(4,minprice1);
    ps.setString(5,maxprice1);
    ps.setInt(6,beds);
    ps.setInt(7,baths);
    ps.setInt(8,1);
    ps.setString(9,d1);
    ps.setString(10,times);      
    ps.executeUpdate();
}



when client searches same properties for many times have taken one counter field in data base. just i need to increment that counter value. but my problem is with the above condition if search the same properties for second it directly go to the else part and insertion statement is excuting.

conditions should be work like this.

1) if ip address is different then insert the record.
2) if address same but property type is different(like buy\sale) then insert
3)ip address same, property type same but city/min price/ maxprice/number of bed rooms are different then insert.
4)if ip address, property type, city,min price,max price,number of bed rooms all are same then update the existing record then increment the counter value.

i have the table in database with the following fields.


SQL
getactiondetails | CREATE TABLE `getactiondetails` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip_address` varchar(40) NOT NULL,
`property_type` varchar(30) DEFAULT NULL,
`city` varchar(40) DEFAULT NULL,
`minprice` varchar(30) DEFAULT NULL,
`maxprice` varchar(30) DEFAULT NULL,
`bhk` int(11) DEFAULT NULL,
`baths` int(11) DEFAULT NULL,
`no_hits` int(11) DEFAULT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
PRIMARY KEY (`id`)
ENGINE=InnoDB DEFAULT CHARSET=latin1 |
-----------------+-------------------------------------


how can i solve my problem.


thanks in advance.
Posted

1 solution

May be there are other way to resolve the issue. But I prefer the following solution :
1. Create a HashKey with the search attributes.
i.e HashKey = (ip_address+property_type+city+.......+date+time);
2. do the check on the "HashKey" into the database
3. If the HashKey is in the database , then update the row otherwise insert the row.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900