今日总结（数据库搭建 SQl部分完善）

实体类

package com.example.app_t.pojo;

public class Route {
    private String id;
    private String site;
    private String name;
    private String city;

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getSite() {
        return site;
    }

    public void setSite(String site) {
        this.site = site;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

SQl部分

package com.example.app_t.Dao;

import com.example.app_t.pojo.Route;
import com.example.app_t.pojo.Site;
import com.example.app_t.unilt.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class RouteDao {
    public static List<Route> queryAll(String City) {

        String sql = "select * from route where city = ?";

        Connection con = JDBCUtils.getConn();
        List<Route> routes =new ArrayList<>();
        try {
            PreparedStatement pst = con.prepareStatement(sql);

            //sql ?
            pst.setString(1, City);

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {

                int id = rs.getInt(1);
                String site = rs.getString(2);
                String name = rs.getString(3);
                String city = rs.getString(4);
                Route route1 = new Route();
                route1.setId(String.valueOf(id));
                route1.setSite(site);
                route1.setName(name);
                route1.setCity(city);
                routes.add(route1);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con);
        }

        return routes;
    }

    public static List<Route> RouteByName(String Name,String City) {

        String sql = "select * from route where name = ? and city = ?";

        Connection con = JDBCUtils.getConn();
        List<Route> routes =new ArrayList<>();
        try {
            PreparedStatement pst = con.prepareStatement(sql);
            //sql ?
            pst.setString(1, Name);
            pst.setString(2, City);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String site = rs.getString(2);
                String name = rs.getString(3);
                String city = rs.getString(4);
                Route route1 = new Route();
                route1.setId(String.valueOf(id));
                route1.setSite(site);
                route1.setName(name);
                route1.setCity(city);
                routes.add(route1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con);
        }

        return routes;
    }

    public static List<Route> RouteByid(String Id,String Site,String City) {

        String sql = "select * from route where id = ? and site = ? and city = ?";

        Connection con = JDBCUtils.getConn();
        List<Route> routes =new ArrayList<>();
        try {
            PreparedStatement pst = con.prepareStatement(sql);

            //sql ?
            pst.setString(1, Id);
            pst.setString(2, Site);
            pst.setString(3, City);

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                int id = rs.getInt(1);
                String site = rs.getString(2);
                String name = rs.getString(3);
                String city = rs.getString(4);
                Route route1 = new Route();
                route1.setId(String.valueOf(id));
                route1.setSite(site);
                route1.setName(name);
                route1.setCity(city);
                routes.add(route1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con);
        }
        return routes;
    }

    public static List<Site> queryByName(String Name,String Chance) {

        String sql = "select * from site where chance = ? and name = ?";

        Connection con = JDBCUtils.getConn();
        List<Site> sites =new ArrayList<>();
        try {
            PreparedStatement pst = con.prepareStatement(sql);

            //sql ?
            pst.setString(1, Chance);
            pst.setString(2, Name);

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {

                int id = rs.getInt(1);
                String chance = rs.getString(2);
                String route1 = rs.getString(3);
                String name = rs.getString(4);
                String route2 = rs.getString(5);

                Site site1 = new Site();
                site1.setId(id);
                site1.setChance(chance);
                site1.setRoute1(route1);
                site1.setName(name);
                site1.setRoute2(route2);

                sites.add(site1);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con);
        }

        return sites;
    }

    public static List<Site> queryByRoute(String Route,String Chance) {

        String sql = "select * from site where chance = ? and route2 = ? ";

        Connection con = JDBCUtils.getConn();
        List<Site> sites =new ArrayList<>();
        try {
            PreparedStatement pst = con.prepareStatement(sql);

            //sql ?
            pst.setString(1, Chance);
            pst.setString(2, Route);

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {

                int id = rs.getInt(1);
                String chance = rs.getString(2);
                String route1 = rs.getString(3);
                String name = rs.getString(4);
                String route2 = rs.getString(5);

                Site site1 = new Site();
                site1.setId(id);
                site1.setChance(chance);
                site1.setRoute1(route1);
                site1.setName(name);
                site1.setRoute2(route2);

                sites.add(site1);

            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.close(con);
        }

        return sites;
    }
}