更新mysql数据库表中的记录 [英] update the records in mysql database table
问题描述
我在将记录存储在数据库中时遇到问题。我的任务是。
我有不同房产细节的房地产网站。我有两个购买和销售链接。如果客户点击购买链接，那么将显示与购买相关的所有属性，在该页面客户可以选择特定的城市，最低价格，最高价格，卧室数量。所以我需要捕获客户在我的网站内选择的所有信息和将它存储在数据库中。
为此我编写了以下代码。
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.
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();
}
当客户多次搜索同一属性时，已经占用了一个计数器字段数据库。只是我需要递增该计数器值。但我的问题是上述条件，如果搜索相同的属性第二个它直接转到else部分和插入语句是激励。
条件应该像这样工作。
1）如果ip地址不同，则插入记录。
2）如果地址相同但属性类型不同（如买入） \ sale）然后插入
3）ip地址相同，属性类型相同但是城市/分钟价格/ maxprice /床房数量不同然后插入。
4）如果IP地址，房产类型，城市，最低价格，最高价格，卧室数量都相同，则更新现有记录，然后递增计数器值。
i将数据库中的表格包含在以下字段中。
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.
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.
推荐答案
可能还有其他方法可以解决这个问题。但我更喜欢以下解决方案：
1.使用搜索属性创建一个HashKey。
即HashKey =（ip_address + property_type + city + ....... +日期+时间）;
2.对数据库中的HashKey进行检查
3.如果HashKey在数据库中，则更新行，否则插入这一行。
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.
这篇关于更新mysql数据库表中的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
