java - 查询两张表的外键

Question

如何查询 route在routes我数据库中的表取决于 lat, longi的 stops表和 arrivaltime在arrivaltimes table ？因此，如果找到停靠点表中的纬度和经度，我想首先检查是否有这个 stop_id 的 arrivaltime (这里当前时间必须等于 arrivaltime )在 arrivaltimes 表中然后依赖于它我想从这个 stop_id 的路由表中检索所有路由？

 CREATE TABLE IF NOT EXISTS stops
    (stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
     name varchar(30) NOT NULL, 
     lat double(10,6) NOT NULL, 
     longi double(10,6)NOT NULL) 

  INSERT INTO stops(name,  lat, longi) values
      ('ABC', '63.838039', '18.700440' ),
      ('DEF', '63.840642', '18.701246' ),             
      ('HIG', '63.868863', '18.665438' )

   CREATE TABLE IF NOT EXISTS arrivaltimes(arrivaltimes_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
      weekday VARCHAR(20) NOT NULL,
      arrivaltime time NOT NULL,
      stop_id INT, FOREIGN KEY fk_stop_id(stop_id) REFERENCES stops(stop_id) )

 INSERT INTO arrivaltimes(stop_id, arrivaltime, weekday) values
       ('1', 'mon-fri', '05:30' ),
       ('1', 'mon-fri', '06:07' )

  CREATE TABLE IF NOT EXISTS routes
    (routes_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     route INT(11) NOT NULL, 
     stop_id INT, FOREIGN KEY fk_stop_id(stop_id) REFERENCES stops(stop_id) )

  INSERT INTO routes(route) values
     ('1', '1'),
     ('1', '9')

JDBC代码:

        Calendar now = Calendar.getInstance();
        int day = now.get(Calendar.DAY_OF_WEEK);
        int hour = now.get(Calendar.HOUR_OF_DAY);
        int minute = now.get(Calendar.MINUTE);
        String time =  hour+":"+minute;

        Statement stt = con.createStatement();

        PreparedStatement preparedLatLong = con
                .prepareStatement("SELECT lat, longi from stops");
        ResultSet rsLatLong = preparedLatLong.executeQuery();
        while (rsLatLong.next()) {
            double lat_stop = rsLatLong.getDouble("lat");
            double lon_stop = rsLatLong.getDouble("longi");
            double distStops = haversineDistance(latD, longD, lat_stop, lon_stop);
            if(distStops <= 10){
// Here how can I query the arrivaltime and the route?
                PreparedStatement preparedTime = con.prepareStatement("SELECT arrivaltime from arrivaltimes WHERE arrivaltime=time"
                        + "values(?)");
                ResultSet rsArrivaletime = preparedTime.executeQuery();
                while(rsArrivaletime.next()){

                }
            }   
        }

如果第一条记录在stops表是位置然后我想检查到达时间表中的到达时间如果当前时间 = 13:30 那么我想得到 1和 9来自 routes 的路线编号 table 。我希望现在更清楚了。

Answer 1

您可以使用带有 where 子句的联接将所有内容加载在一起。

SELECT a.route FROM routes a 
LEFT JOIN arrivaltimes b ON a.stop_id = b.stop_id
LEFT JOIN stops c ON a.stop_id = c.stop_id 
WHERE b.arrivaltime = ?