mysql and java

[Playagood]

Hi,



My mysql table ''Compagnie'' contains a company list. Each company is define this way: name, field, size, latitude, longitude, distance . I wants to be able to input my current location so java would classify the companies by the nearest.


**The value "Distance" is not define. It will change depending of the current location.**

Not looking for the code...I have been able to query, delete, select and update information from/to mysql through Wamp server(myphpadmin). Just need a clue.


This is where I get stuck.

-Input my current location
-Query the latitude and longitude of each company.
-Calculate the distance between my location and the companies location
-Update the "distance" value for each company in mysql.

It does calculate the distance but applies the result of the last company on the table to all the company...so when I come back to mysql, each company seems to be located at the same distance from my current location. Need some advices.

Here is the code:


import java.sql.*;
import java.lang.Math;


public class update2 {
public static void main(String[] args) {
System.out.println("Updates Records Example through Prepared Statement!");
Connection con = null;
Statement stmt = null;

try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/company","root","");



try{


stmt = con.createStatement();

String sql1 = "SELECT Longitude, Latitude FROM companyinfo" ;
ResultSet rs = stmt.executeQuery(sql1);




while(rs.next()){


String sql = "UPDATE companyinfo SET Distance = (?) ";
PreparedStatement prest = con.prepareStatement(sql);

float currentlatitude= 50;
float currentlongitude= 100;

float Latitude = rs.getFloat("Latitude");
float Longitude = rs.getFloat("Longitude");



float currentdistance = (float) Math.sqrt(((currentlatitude-Latitude)*(currentlatitude-Latitude))+((currentlongitude-Longitude)*(currentlongitude-Longitude)));
prest.setFloat(1, currentdistance);
prest.executeUpdate();


}



rs.close();

System.out.println(" Updating Successfully!");
con.close();
}
catch (SQLException s){
System.out.println("SQL statement is not executed!");
}
}
catch (Exception e){
e.printStackTrace();
}

}
}



Thank you. Merci à vous.

[Tolls]

What does your code look like?
Specifically how you do the update and how that update code is called.
Sounds (and this is a guess) like you're simply updating the entire table each time, rather than specific rows.

[Playagood]

I just added the code.