个人中心
gpt4 book ai didi

java - 如何使用 Spring Data JPA 按日期和时间进行搜索？

转载 作者：行者123 更新时间：2023-12-01 17:44:22 26 4
gpt4 key购买 nike

我倾向于 Spring Data JPA，我有一个应用程序，我尝试按日期搜索，但它不起作用。

问题是当我尝试在数据库中搜索日期时。我有一个 MySQL 数据库，如果我直接在 MySQL Workbench 中搜索是有效的，但如果我尝试从我的应用程序中搜索则不起作用。我什么也没得到。如果我尝试搜索其他内容，我会得到结果。所以我确信当我尝试搜索日期时存在问题。我不知道出了什么问题。任何反馈将不胜感激。谢谢!

更新

一开始，该应用程序运行良好。我可以按数据库中的日期进行搜索。之后，我添加了 Spring Security 和更多实体，之后我无法按数据库中的日期进行搜索，并且我没有触及搜索方法。这很奇怪。现在我有了应用程序的第一个版本并且它可以工作，而第二个版本则无法工作。并且两个应用程序都针对相同的数据库实例。但问题是，只有当我尝试按日期搜索时，如果我按出发城市和到达城市搜索，它就可以正常工作，当我尝试按日期搜索时，我什么也得不到，列表是空的。

这是无法运行的应用程序版本。这是 Github 链接 -> https://github.com/eveningstar33/flightreservationapp

这是另一个运行良好的应用程序:https://github.com/eveningstar33/flightreservation

实体:

抽象实体类:

package com.dgs.flightreservationapp.entities;

import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;

@MappedSuperclass
public class AbstractEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    public Long getId() {
        return id;
    }

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

}

航类等级:

package com.dgs.flightreservationapp.entities;

import java.sql.Timestamp;
import java.time.LocalDate;

import javax.persistence.Entity;

@Entity
public class Flight extends AbstractEntity {

    private String flightNumber;
    private String operatingAirlines;
    private String departureCity;
    private String arrivalCity;

    private LocalDate dateOfDeparture;
    private Timestamp estimatedDepartureTime;

    public String getFlightNumber() {
        return flightNumber;
    }

    public void setFlightNumber(String flightNumber) {
        this.flightNumber = flightNumber;
    }

    public String getOperatingAirlines() {
        return operatingAirlines;
    }

    public void setOperatingAirlines(String operatingAirlines) {
        this.operatingAirlines = operatingAirlines;
    }

    public String getDepartureCity() {
        return departureCity;
    }

    public void setDepartureCity(String departureCity) {
        this.departureCity = departureCity;
    }

    public String getArrivalCity() {
        return arrivalCity;
    }

    public void setArrivalCity(String arrivalCity) {
        this.arrivalCity = arrivalCity;
    }

    public LocalDate getDateOfDeparture() {
        return dateOfDeparture;
    }

    public void setDateOfDeparture(LocalDate dateOfDeparture) {
        this.dateOfDeparture = dateOfDeparture;
    }

    public Timestamp getEstimatedDepartureTime() {
        return estimatedDepartureTime;
    }

    public void setEstimatedDepartureTime(Timestamp estimatedDepartureTime) {
        this.estimatedDepartureTime = estimatedDepartureTime;
    }

}

乘客舱位:

package com.dgs.flightreservationapp.entities;

import javax.persistence.Entity;

@Entity
public class Passenger extends AbstractEntity {

    private String firstName;
    private String lastName;
    private String middleName;
    private String email;
    private String phone;

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getMiddleName() {
        return middleName;
    }

    public void setMiddleName(String middleName) {
        this.middleName = middleName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

}

预订舱位:

package com.dgs.flightreservationapp.entities;

import javax.persistence.Entity;
import javax.persistence.OneToOne;

@Entity
public class Reservation extends AbstractEntity {

    private Boolean checkedIn;
    private int numberOfBags;
    @OneToOne
    private Passenger passenger;
    @OneToOne
    private Flight flight;

    public Boolean getCheckedIn() {
        return checkedIn;
    }

    public void setCheckedIn(Boolean checkedIn) {
        this.checkedIn = checkedIn;
    }

    public int getNumberOfBags() {
        return numberOfBags;
    }

    public void setNumberOfBags(int numberOfBags) {
        this.numberOfBags = numberOfBags;
    }

    public Passenger getPassenger() {
        return passenger;
    }

    public void setPassenger(Passenger passenger) {
        this.passenger = passenger;
    }

    public Flight getFlight() {
        return flight;
    }
    public void setFlight(Flight flight) {
        this.flight = flight;
    }

}

角色类别:

package com.dgs.flightreservationapp.entities;

import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinTable;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToMany;

@Entity
public class Role extends AbstractEntity {

    private String name;

    @ManyToMany(fetch=FetchType.EAGER,
            cascade= {CascadeType.PERSIST, CascadeType.MERGE,
             CascadeType.DETACH, CascadeType.REFRESH})
    @JoinTable(
            name="user_role",
            joinColumns=@JoinColumn(name="role_id"),
            inverseJoinColumns=@JoinColumn(name="user_id")
            )
    private Set<User> users;

    public String getName() {
        return name;
    }

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

    public Set<User> getUsers() {
        return users;
    }

    public void setUsers(Set<User> users) {
        this.users = users;
    }

}

用户类别:

package com.dgs.flightreservationapp.entities;

import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.JoinColumn;

@Entity
public class User extends AbstractEntity {

    private String firstName;
    private String lastName;

    @Column(name = "email")
    private String username;
    private String password;

    @ManyToMany(fetch=FetchType.EAGER,
            cascade= {CascadeType.PERSIST, CascadeType.MERGE,
             CascadeType.DETACH, CascadeType.REFRESH})
    @JoinTable(name = "user_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
    private Set<Role> roles;

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Set<Role> getRoles() {
        return roles;
    }

    public void setRoles(Set<Role> roles) {
        this.roles = roles;
    }

}

FlightController 类:

package com.dgs.flightreservationapp.controllers;

import java.time.LocalDate;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.dgs.flightreservationapp.entities.Flight;
import com.dgs.flightreservationapp.repos.FlightRepository;

@Controller
public class FlightController {

    @Autowired
    private FlightRepository flightRepository;

    @GetMapping("/findFlights")
    public String findFlights() {

        return "findFlights";
    }

    @PostMapping("processFlights")
    public String processFlights(@RequestParam("from") String from, @RequestParam("to") String to,
            @RequestParam("departureDate") @DateTimeFormat(pattern = "MM-dd-yyyy") LocalDate departureDate,
            ModelMap modelMap) {

        System.out.println(">>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<< " + departureDate);

        List<Flight> flights = flightRepository.findByDepartureCityAndArrivalCityAndDateOfDeparture(from, to, departureDate);
        modelMap.addAttribute("flights", flights);
        return "displayFlights";
    }

}

用户 Controller 类:

package com.dgs.flightreservationapp.controllers;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class UserController {

    @GetMapping("/showMyLoginPage")
    public String showMyLoginPage() {

        return "login/fancy-login";

    }

}

航类存储库:

package com.dgs.flightreservationapp.repos;

import java.time.LocalDate;
import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.dgs.flightreservationapp.entities.Flight;

public interface FlightRepository extends JpaRepository<Flight, Long>  {

//  @Query("from Flight where departureCity=:departureCity and arrivalCity=:arrivalCity and dateOfDeparture=:dateOfDeparture")
//  List<Flight> findFlights(@Param("departureCity") String from, 
//                           @Param("arrivalCity") String to,
//                           @Param("dateOfDeparture") LocalDate departureDate);

    List<Flight> findByDepartureCityAndArrivalCityAndDateOfDeparture(String from, String to, LocalDate departureDate);

}

用户存储库:

package com.dgs.flightreservationapp.repos;

import org.springframework.data.jpa.repository.JpaRepository;

import com.dgs.flightreservationapp.entities.User;

public interface UserRepository extends JpaRepository<User, Long> {

    User findByUsername(String username);
}

网络安全配置:

package com.dgs.flightreservationapp.security;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

@Configuration
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private CustomAuthenticationSuccessHandler customAuthenticationSuccessHandler;

    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

    @Bean
    public UserDetailsService userDetailsService() {
        return new UserDetailsServiceImpl();
    }

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(userDetailsService()).passwordEncoder(passwordEncoder());
    }

    @Override
    public void configure(HttpSecurity http) throws Exception {

        http.authorizeRequests()
                .anyRequest().authenticated()
            .and()
            .formLogin()
                .loginPage("/showMyLoginPage")
                .loginProcessingUrl("/authenticateTheUser")
                .successHandler(customAuthenticationSuccessHandler)
                .permitAll();
    }

}

用户详细信息服务Impl:

package com.dgs.flightreservationapp.security;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Service;

import com.dgs.flightreservationapp.entities.User;
import com.dgs.flightreservationapp.repos.UserRepository;

@Service
public class UserDetailsServiceImpl implements UserDetailsService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {

        User user = userRepository.findByUsername(username);


        if (user == null) {
            throw new UsernameNotFoundException("User not found for email " + username);
        }

        return new UserPrincipal(user);
    }

}

用户主体

package com.dgs.flightreservationapp.security;

import java.util.Collection;
import java.util.HashSet;
import java.util.Set;

import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UserDetails;

import com.dgs.flightreservationapp.entities.Role;
import com.dgs.flightreservationapp.entities.User;

public class UserPrincipal implements UserDetails {

    private User user;

    public UserPrincipal(User user) {
        this.user = user;
    }

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        Set<GrantedAuthority> grantedAuthorities = new HashSet<>();
        for (Role role : user.getRoles()) {
            grantedAuthorities.add(new SimpleGrantedAuthority(role.getName()));
        }

        return grantedAuthorities;
    }

    @Override
    public String getPassword() {
        return user.getPassword();
    }

    @Override
    public String getUsername() {
        return user.getUsername();
    }

    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    @Override
    public boolean isAccountNonLocked() {
        return true;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    @Override
    public boolean isEnabled() {
        return true;
    }

}

自定义身份验证成功处理程序:

package com.dgs.flightreservationapp.security;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.Authentication;
import org.springframework.security.web.authentication.AuthenticationSuccessHandler;
import org.springframework.stereotype.Component;

import com.dgs.flightreservationapp.entities.User;
import com.dgs.flightreservationapp.repos.UserRepository;

@Component
public class CustomAuthenticationSuccessHandler implements AuthenticationSuccessHandler {

    @Autowired
    private UserRepository userRepository;

    @Override
    public void onAuthenticationSuccess(HttpServletRequest request, HttpServletResponse response, Authentication authentication)
            throws IOException, ServletException {

        System.out.println("\n\nIn customAuthenticationSuccessHandler\n\n");

        String userName = authentication.getName();

        System.out.println("userName=" + userName);

        User theUser = userRepository.findByUsername(userName);

        // now place in the session
        HttpSession session = request.getSession();
        session.setAttribute("user", theUser);

        // forward to home page

        response.sendRedirect(request.getContextPath() + "/findFlights");
    }

}

MySQL 架构:

CREATE DATABASE reservation

USE reservation


CREATE TABLE user
(
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20),
last_name VARCHAR(20),
email VARCHAR(20),
password VARCHAR(256), 
PRIMARY KEY (id),
UNIQUE KEY (email)
);

CREATE TABLE flight
(
  id INT  NOT NULL AUTO_INCREMENT,
  flight_number VARCHAR(20)  NOT NULL, 
  operating_airlines VARCHAR(20)  NOT NULL,
  departure_city VARCHAR(20)  NOT NULL,
  arrival_city VARCHAR(20)  NOT NULL,
  date_of_departure DATE  NOT NULL,
  estimated_departure_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
  PRIMARY KEY (id);
)

CREATE TABLE passenger
(
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(256),
  last_name VARCHAR(256),
  middle_name VARCHAR(256),
  email VARCHAR(50),
  phone VARCHAR(10),
  PRIMARY KEY (id)
);

CREATE TABLE reservation
(
  id INT NOT NULL AUTO_INCREMENT,
  checked_in TINYINT(1),
  number_of_bags INT,
  passenger_id INT,
  flight_id INT,
  created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (passenger_id) REFERENCES passenger(id) ON DELETE CASCADE,
  FOREIGN KEY (flight_id) REFERENCES flight(id)
);

CREATE TABLE role
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
PRIMARY KEY (id)
);

create table user_role(
user_id int,
role_id int,
FOREIGN KEY (user_id)
REFERENCES user(id),
FOREIGN KEY (role_id)
REFERENCES role(id)
);

insert into flight values(1,'AA1','American Airlines','AUS',
'NYC',STR_TO_DATE('02-05-2018', '%m-%d-%Y'),'2018-02-05 03:14:07');

insert into flight values(2,'AA2','American Airlines','AUS',
'NYC',STR_TO_DATE('02-05-2018', '%m-%d-%Y'),'2018-02-05 05:14:07');

insert into flight values(3,'AA3','American Airlines','AUS',
'NYC',STR_TO_DATE('02-05-2018', '%m-%d-%Y'),'2018-02-05 06:14:07');

insert into flight values(4,'SW1','South West','AUS',
'NYC',STR_TO_DATE('02-05-2018', '%m-%d-%Y'),'2018-02-05 07:14:07');

insert into flight values(5,'UA1','United Airlines','NYC',
'DAL',STR_TO_DATE('02-05-2018', '%m-%d-%Y'),'2018-02-05 10:14:07');

insert into flight values(6,'UA1','United Airlines','NYC',
'DAL',STR_TO_DATE('02-05-2018', '%m-%d-%Y'),'2018-02-05 10:14:07');

insert into flight values(7,'SW1','South West','AUS',
'NYC',STR_TO_DATE('02-06-2018', '%m-%d-%Y'),'2018-02-06 07:14:07');

insert into flight values(8,'SW2','South West','AUS',
'NYC',STR_TO_DATE('02-06-2018', '%m-%d-%Y'),'2018-02-06 08:14:07');


insert into flight values(9,'SW3','South West','NYC',
'DAL',STR_TO_DATE('02-06-2018', '%m-%d-%Y'),'2018-02-06 10:14:07');

insert into flight values(10,'UA1','United Airlines','NYC',
'DAL',STR_TO_DATE('02-06-2018', '%m-%d-%Y'),'2018-02-06 10:14:07');

insert into user values(1,'John','Doe','john@gmail.com',
'$2a$04$eFytJDGtjbThXa80FyOOBuFdK2IwjyWefYkMpiBEFlpBwDH.5PM0K');

您可以使用 john@gmail.com 登录，密码是 fun123

如果您想测试其他完美运行的应用程序，您需要将其添加到数据库中:

insert into user values(2,'Tom','Abc','tom@gmail.com','fun123');

然后使用 tom@gmail.com 和 fun123 登录，并在 Fin Flights 中输入 AUS、NYC 和 02-05-2018，您将获得此应用程序的结果。但对于其他应用程序，您不会生成任何结果。这很奇怪。

最佳答案

该问题是由于数据转换而发生的

com.mysql.cj.jdbc.ClientPreparedStatement: select flight0_.id as id1_0_, flight0_.arrival_city as arrival_2_0_, flight0_.date_of_departure as date_of_3_0_, flight0_.departure_city as departur4_0_, flight0_.estimated_departure_time as estimate5_0_, flight0_.flight_number as flight_n6_0_, flight0_.operating_airlines as operatin7_0_ from flight flight0_ where flight0_.departure_city='AUS' and flight0_.arrival_city='NYC' and flight0_.date_of_departure='2018-02-04 18:30:00.0'

正如您所看到的，日期没有以正确的格式转换，因此我们推出了自己的转换器来解决该问题。

flightreservationapp/converters/LocalDateAttributeConverter.java

package com.dgs.flightreservationapp.converters;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.time.LocalDate;

@Converter(autoApply = true)
public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, String> {

    @Override
    public String convertToDatabaseColumn(LocalDate locDate) {
        return locDate == null ? null : locDate.toString();
    }

    @Override
    public LocalDate convertToEntityAttribute(String sqlDate) {
        return sqlDate == null ? null : LocalDate.parse(sqlDate);
    }
}

添加此文件后，您将开始获取结果

Working fine

您还有其他选择。向您的 JPA 方法添加注释

    List<Flight> findByDepartureCityAndArrivalCityAndDateOfDeparture(String from, String to,
                                                                     @DateTimeFormat(iso= DateTimeFormat.ISO.DATE)
                                                                     LocalDate departureDate);

然后您需要确保将时区也设置为 UTC

public class FlightReservationAppApplication {

    public static void main(String[] args) {
        SpringApplication.run(FlightReservationAppApplication.class, args);
    }

    @PostConstruct
    void init() {
        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
    }
}

另一种选择是在 pom.xml 中使用更高版本的 hibernate

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-java8</artifactId>
            <version>5.1.0.Final</version>
        </dependency>

并设置时区

public class FlightReservationAppApplication {

    public static void main(String[] args) {
        SpringApplication.run(FlightReservationAppApplication.class, args);
    }

    @PostConstruct
    void init() {
        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
    }
}

关于java - 如何使用 Spring Data JPA 按日期和时间进行搜索？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/57327134/

26 4 0
文章推荐： iphone - 重新加载特定的uitableview单元格的数据
文章推荐： iphone - 线程 1 : EXEC_BAD_ACCESS on MFMailComposeViewController's dismissModalViewController (tried iOS 5. 1、5、4.3)
文章推荐： ios - 视网膜上的核心动画
文章推荐： vba - 在Excel VBA中，如何删除灰色分页符？
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com