JavaScript 到 Java 到 MYSQL 查询

Question

我有一个 Java API Crud，使用像“获取单个项目或项目列表”这样的简单查询效果很好，问题是当我需要进行更复杂的查询并将值列表传递给 api 时对它从 mysql 表返回的数据应用更复杂的过滤器。

我有一个名为“小时”的表，其中包含如下数据:

[{"id":1,"hour":"08:00"},{"id":2,"hour":"08:05"},{"id":3,"hour":"08:10"},{"hour":4,"hora":"08:15"},{"id":5,"hour":"08:20"}]

还有一个名为“turns”的表，其中包含如下数据:

 [{"id":1,"solicitante":"asdasdas","telefono":null,"tipoDocumento":{"id":1,"tipodoc":"Dni"},"numeroDocumento":"32821535","email":null,"hours":{"id":1,"hour":"08:00"},"numeroTurno":"A1001","fecha":"2016-02-02","controlFecha":"2016-02-11 18:27:52.0","formatFecha":"2016-02-02"},{"id":10,"solicitante":"asddasdas asdasdas","telefono":null,"tipoDocumento":{"id":1,"tipodoc":"Dni"},"numeroDocumento":"2331312","email":null,"hours":{"id":2,"hour":"08:05"},"numeroTurno":"A1002","fecha":"2016-02-02","controlFecha":"2016-02-11 18:27:52.0","formatFecha":"2016-02-02"}]

我正在尝试做的是查询以返回特定日期未使用的“小时数”。不太确定如何执行此操作，所以我尝试了这样的操作:

@RequestMapping(value = "/hours/{takenHours}", method = RequestMethod.GET)
@ResponseBody
public List<Hours> queryHours(@RequestParam(value = "takenHours") List<String> takenHours) {
    List<Hours> hours = hoursService.getHours(takenHours);
    return hours;
}   

 @Override
public List<Hours> getHours(List<String> takenHours) {
    List<Hours> hours = hoursRepository.findByHourNotIn(takenHours);
    return hours;
}

所以我针对特定的一天进行了查询，返回了所有的小时数，并尝试将数组发送到此方法。我稍后会添加日期过滤器 (findByHoraNotInAndDate)。

不确定这是否是最好的方法，很乐意采纳任何想法。

我正在使用 AngularJS 来处理前端。

Answer 1

修复了改变我处理问题的方式。

制作一个以分钟为单位的小时列表，然后对其进行格式化并使用简单的 for 循环将其丢给 Controller 。

@Entity
@Table(name = "horario")
public class Horario implements Serializable {

private static final long serialVersionUID = 1L;
private Long id;
private Integer dia;
private Integer horarioInicio;
private Integer horarioFin;
private Integer duration;

@Id
@GeneratedValue
@Column(name = "id")
public Long getId() {
    return id;
}

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

@Column(name = "dia")
public Integer getDia() {
    return dia;
}

public void setDia(Integer dia) {
    this.dia = dia;
}

@Column(name = "horario_inicio")
public Integer getHorarioInicio() {
    return horarioInicio;
}

public void setHorarioInicio(Integer horarioInicio) {
    this.horarioInicio = horarioInicio;
}

@Column(name = "horario_fin")
public Integer getHorarioFin() {
    return horarioFin;
}

public void setHorarioFin(Integer horarioFin) {
    this.horarioFin = horarioFin;
}

@Transient
public List<Integer> getHorarios(){
    List<Integer> horarios = new ArrayList<Integer>();
    for(Integer i = horarioInicio; i <= horarioFin; i+=duration){
        horarios.add(i);
    }
    return horarios;
}

@Column(name = "duration")
public Integer getDuration() {
    return duration;
}

public void setDuration(Integer duration) {
    this.duration = duration;
}

@Transient
public List<String> getHorariosLibres(List<Integer> horariosTomados){

    List<Integer> horariosTemporal = getHorarios();
    horariosTemporal.removeAll(horariosTomados);
    List<String> horariosLibres = new ArrayList<String>();
    for (Integer horario : horariosTemporal){
        int hours = (horario / 60);
        int minutes = (horario % 60);
    String hours1 = null;
    String minutes1 = null;
        if(hours == 7 || hours == 8 || hours == 9){
            hours1 = "0" + hours + ":";
        }else{
            hours1 = hours + ":";
        }
        if(minutes == 5 || minutes == 0){
            minutes1 = "0" + minutes;
        }else{
            minutes1 = String.valueOf(minutes);
        }

        horariosLibres.add(hours1 + minutes1);

    }

    return horariosLibres;
}

@Override
public int hashCode() {
    final int prime = 31;
    int result = 1;
    result = prime * result + ((id == null) ? 0 : id.hashCode());
    return result;
}

@Override
public boolean equals(Object obj) {
    if (this == obj)
        return true;
    if (obj == null)
        return false;
    if (getClass() != obj.getClass())
        return false;
    Horario other = (Horario) obj;
    if (id == null) {
        if (other.id != null)
            return false;
    } else if (!id.equals(other.id))
        return false;
    return true;
}

}

这里是 Controller

    @Entity
    @Table(name = "horario")
    public class Horario implements Serializable {

        private static final long serialVersionUID = 1L;
        private Long id;
        private Integer dia;
        private Integer horarioInicio;
        private Integer horarioFin;
        private Integer duration;

        @Id
        @GeneratedValue
        @Column(name = "id")
        public Long getId() {
            return id;
        }

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

        @Column(name = "dia")
        public Integer getDia() {
            return dia;
        }

        public void setDia(Integer dia) {
            this.dia = dia;
        }

        @Column(name = "horario_inicio")
        public Integer getHorarioInicio() {
            return horarioInicio;
        }

        public void setHorarioInicio(Integer horarioInicio) {
            this.horarioInicio = horarioInicio;
        }

        @Column(name = "horario_fin")
        public Integer getHorarioFin() {
            return horarioFin;
        }

        public void setHorarioFin(Integer horarioFin) {
            this.horarioFin = horarioFin;
        }

        @Transient
        public List<Integer> getHorarios(){
            List<Integer> horarios = new ArrayList<Integer>();
            for(Integer i = horarioInicio; i <= horarioFin; i+=duration){
                horarios.add(i);
            }
            return horarios;
        }

        @Column(name = "duration")
        public Integer getDuration() {
            return duration;
        }

        public void setDuration(Integer duration) {
            this.duration = duration;
        }

        @Transient
        public List<String> getHorariosLibres(List<Integer> horariosTomados){

            List<Integer> horariosTemporal = getHorarios();
            horariosTemporal.removeAll(horariosTomados);
            List<String> horariosLibres = new ArrayList<String>();
            for (Integer horario : horariosTemporal){
                int hours = (horario / 60);
                int minutes = (horario % 60);
            String hours1 = null;
            String minutes1 = null;
                if(hours == 7 || hours == 8 || hours == 9){
                    hours1 = "0" + hours + ":";
                }else{
                    hours1 = hours + ":";
                }
                if(minutes == 5 || minutes == 0){
                    minutes1 = "0" + minutes;
                }else{
                    minutes1 = String.valueOf(minutes);
                }

                horariosLibres.add(hours1 + minutes1);

            }

            return horariosLibres;
        }

        @Override
        public int hashCode() {
            final int prime = 31;
            int result = 1;
            result = prime * result + ((id == null) ? 0 : id.hashCode());
            return result;
        }

        @Override
        public boolean equals(Object obj) {
            if (this == obj)
                return true;
            if (obj == null)
                return false;
            if (getClass() != obj.getClass())
                return false;
            Horario other = (Horario) obj;
            if (id == null) {
                if (other.id != null)
                    return false;
            } else if (!id.equals(other.id))
                return false;
            return true;
        }

    }