Spring Boot中如何实现登录用户仅查看个人预约数据

我正在开发一个基于Spring Boot + MySQL的Web应用，目标是让用户登录后查看自己的预约记录。目前登录与注册功能已完成，但用户登录后会看到所有用户的预约记录，需要实现仅展示当前登录用户个人预约的功能。曾在StudRepository中尝试编写查询语句但未生效。

数据库包含三个表：学生表studenti、预约表prenotazioni、考试场次表appelli。

现有代码 #

Repository代码

package springuniversita;

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 org.springframework.stereotype.Repository;

@Repository
public interface StudRepository extends JpaRepository<Studente, Integer> {

    Studente findByCodfiscAndPassword(String codfisc, String password);
//Query tried below:

//  @Query("SELECT data, materia FROM prenotazioni JOIN studenti WHERE idstudente=studenti_idstudente AND codfisc=:codfisc") 
//  List<Studente> findByName(@Param("codfisc") String codfisc);
    
}

Controller代码

package springuniversita;

import java.util.Objects;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;



@Controller
public class LoginController {
    
    @Autowired
    private DBService studservice;
    
    
    
    @GetMapping("/")
    public String home() {
        return "homepage";
    }
    
    
    @GetMapping("/login")

    public ModelAndView login() {
        ModelAndView mav = new ModelAndView("login");
        mav.addObject("studenti", new Studente());
        return mav;
    }

    @PostMapping("/login")
    public String login(@ModelAttribute("studenti") Studente studenti) {

        Studente oauthUser = studservice.loginform(studenti.getCodfisc(), studenti.getPassword());

        System.out.print(oauthUser);
        if (Objects.nonNull(oauthUser)) {

            return "redirect:/profilo";

        } else {
            return "redirect:/login";

        }
}
}

Entity Studenti代码

package springuniversita;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="studenti")
public class Studente {

    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public int idstudente;

    private @Column(name="nome") String nome;
    private @Column(name="cognome") String cognome;
    private @Column(name="telefono") String telefono;
    private @Column(name="email") String email;
    private @Column(name="password") String password;
    private @Column(name="codfisc") String codfisc;
    
    public Studente(Integer idstudente, String nome, String cognome, String telefono, String email, String password, String codfisc) {
        this.idstudente=idstudente;
        this.nome=nome;
        this.cognome=cognome;
        this.telefono=telefono;
        this.email=email;
        this.password=password;
        this.codfisc=codfisc;
                
    }
    
    @Override
    public String toString() {
        return "Studente [idstudente=" + idstudente + ", nome=" + nome + ", cognome=" + cognome + ", telefono="
                + telefono + ", email=" + email + ", password=" + password + ", codfisc=" + codfisc + "]";
    }

    public Studente() {
        
    }
//... getters and setters

Entity Prenotazioni代码

package springuniversita;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Data;

    @Entity
    @Data
    @Table(name="prenotazioni")
    public class Prenotazioni {
    
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int idprenotazione;
    private String data;
    private String materia;
    private int studenti_idstudente;
    private int appelli_idappello;
    
    public Prenotazioni() {
        
    }

    public Prenotazioni(int idprenotazione, String data, String materia, int studenti_idstudente,
            int appelli_idappello) {
        
        this.idprenotazione = idprenotazione;
        this.data = data;
        this.materia = materia;
        this.studenti_idstudente = studenti_idstudente;
        this.appelli_idappello = appelli_idappello;
    }
//... getters and setters

解决方案 #

1. 修正实体类关联关系 #

在Studente和Prenotazioni之间建立JPA关联，便于通过学生对象直接获取预约记录：

• 修改Studente实体，添加一对多关联：

@Entity
@Table(name="studenti")
public class Studente {
    // 原有字段...
    
    @OneToMany(mappedBy = "studente", fetch = FetchType.LAZY)
    private List<Prenotazioni> prenotazioniList;
    
    // 补充对应构造方法、getter/setter
}

• 修改Prenotazioni实体，替换原外键字段为多对一关联：

@Entity
@Data
@Table(name="prenotazioni")
public class Prenotazioni {
    // 原有字段...
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "studenti_idstudente")
    private Studente studente;
    
    // 调整构造方法，移除studenti_idstudente参数，补充getter/setter
}

2. 创建预约专属Repository #

单独创建PrenotazioniRepository，避免与学生Repository混写：

@Repository
public interface PrenotazioniRepository extends JpaRepository<Prenotazioni, Integer> {
    // 根据学生ID查询预约
    List<Prenotazioni> findByStudente_Idstudente(Integer studenteId);
}

3. 存储登录用户信息到Session #

修改登录接口，将当前用户存入Session，方便后续接口获取：

@PostMapping("/login")
public String login(@ModelAttribute("studenti") Studente studenti, HttpSession session) {
    Studente oauthUser = studservice.loginform(studenti.getCodfisc(), studenti.getPassword());
    if (Objects.nonNull(oauthUser)) {
        session.setAttribute("currentUser", oauthUser);
        return "redirect:/profilo";
    } else {
        return "redirect:/login";
    }
}

4. 实现个人预约查询接口 #

编写/profilo接口，从Session获取当前用户并查询其专属预约：

@Controller
public class ProfiloController {
    @Autowired
    private PrenotazioniRepository prenotazioniRepo;
    
    @GetMapping("/profilo")
    public String getProfilo(Model model, HttpSession session) {
        Studente currentUser = (Studente) session.getAttribute("currentUser");
        if (currentUser == null) {
            return "redirect:/login";
        }
        List<Prenotazioni> userPrenotazioni = prenotazioniRepo.findByStudente_Idstudente(currentUser.getIdstudente());
        model.addAttribute("prenotazioni", userPrenotazioni);
        return "profilo";
    }
}

5. 修复原查询（可选） #

如果坚持使用原生查询，原查询的错误在于返回类型不匹配，正确写法如下：

@Query("SELECT p FROM Prenotazioni p JOIN p.studente s WHERE s.codfisc = :codfisc")
List<Prenotazioni> findPrenotazioniByStudenteCodfisc(@Param("codfisc") String codfisc);

内容的提问来源于stack exchange，提问作者SteDS