為什么SQLSELECT陳述句不會在JavaSpring啟動專案中回傳COUNT()結果？-有解無憂

這是我在這里的第一個問題，所以請詢問您是否需要更多資訊。我正在做一個個人專案。我有一個相對復雜的關系資料庫結構。我在 Spring Boot 專案上創建了 schema.sql，并使用示例資料創建了 data.sql。我嘗試為模擬健身中心網頁創建一個網路應用程式。我嘗試顯示用戶的位置名稱和訪問次數。我創建了一個 userLocation bean，用于將結果集保留為選擇查詢的串列。我可以測驗 H2 資料庫上的陳述句及其作業。但是，在我的代碼中，我無法從 select 陳述句中獲取訪問次數。

這是我的用戶定位 bean，

@Data
@NoArgsConstructor
public class UserLocation {
    
    private String locName;
    private int numOfVisit;

}

控制器類 getMapping 方法

    @GetMapping("/secure/userLocation")
    public String myLocation(Model model, Authentication authentication) {
        
        String email = authentication.getName();
        User currentUser = da.findUserAccount(email);
        model.addAttribute("myLocationList", da.getUserLocationList(currentUser.getUserId()));
        
        return "/secure/userLocation";
    }

這里的資料庫訪問方法；

public List<UserLocation> getUserLocationList(Long userId) {
        MapSqlParameterSource namedParameters = new MapSqlParameterSource();

        String query = "SELECT l.locName, COUNT(ul.dayOfVisit) FROM location l "
                  "INNER JOIN userLocation ul ON l.locId = ul.locId "
                  "INNER JOIN sec_user sc ON ul.userId = sc.userId "
                  "WHERE sc.userId = :userId AND ul.locId = 1"
                  "GROUP BY l.locName";

        namedParameters.addValue("userId", userId);
        
        return jdbc.query(query, namedParameters, new BeanPropertyRowMapper<UserLocation>(UserLocation.class));

    }

這里 schema.sql

CREATE TABLE location (
  locId         BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  locName       VARCHAR(75),
  locAddress    VARCHAR(255),
  locPhone      VARCHAR(25),
  locEmail      VARCHAR(75)
);

CREATE TABLE sec_user (
  userId            BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name              VARCHAR(75),
  lastName          VARCHAR(75),
  adress            VARCHAR(255),
  phone             VARCHAR(10),
  email             VARCHAR(75) NOT NULL UNIQUE,
  encryptedPassword VARCHAR(128) NOT NULL,
  enabled           BIT NOT NULL
);

CREATE TABLE coach (

    coachId     BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    coachName   VARCHAR(75),
    coachLevel  BIGINT,
    coachRating BIGINT,
    aboutMe     VARCHAR(255)
);

CREATE TABLE fitnessClass (

    classId     BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    className   VARCHAR(75),
    classPrice  DOUBLE
);

CREATE TABLE generalCert (
    
    certId      BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    certName    VARCHAR(75)
);

CREATE TABLE certCoach (
    
    certId      BIGINT NOT NULL,
    coachId     BIGINT NOT NULL
);

ALTER TABLE certCoach
  ADD CONSTRAINT certCoach_FK1 FOREIGN KEY (certId)
  REFERENCES generalCert (certId);

ALTER TABLE certCoach
  ADD CONSTRAINT certCoach_FK2 FOREIGN KEY (coachId)
  REFERENCES coach (coachId);

CREATE TABLE userLocation (
    
    userLocId   BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    locId       BIGINT NOT NULL,
    userId      BIGINT NOT NULL,
    isHomeLoc   BIT,
    dayOfVisit  DATE
);

ALTER TABLE userLocation
  ADD CONSTRAINT userLocation_FK1 FOREIGN KEY (locId)
  REFERENCES location (locId);

ALTER TABLE userLocation
  ADD CONSTRAINT userLocation_FK2 FOREIGN KEY (userId)
  REFERENCES sec_user (userId);  

CREATE TABLE amenity (
    amenityId   BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    amenityName VARCHAR(75),
    locId       BIGINT
);

ALTER TABLE amenity
  ADD CONSTRAINT amenity_FK FOREIGN KEY (locId)
  REFERENCES location (locId);
  
CREATE TABLE room (
    roomId      BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    roomName    VARCHAR(75),
    locId       BIGINT
);

ALTER TABLE room
  ADD CONSTRAINT room_FK FOREIGN KEY (locId)
  REFERENCES location (locId);

CREATE TABLE classCoach (
    classId  BIGINT NOT NULL,
    coachId  BIGINT NOT NULL
);

ALTER TABLE classCoach
  ADD CONSTRAINT classCoachFK1   FOREIGN KEY (classId)
    REFERENCES fitnessClass(classId);

ALTER TABLE classCoach
  ADD CONSTRAINT classCoachFK2    FOREIGN KEY (coachId)
    REFERENCES coach(coachId);

CREATE TABLE schedule (
    ScheduleId      BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ScheduleDate    DATE,
    ScheduleTime    TIME,
    RoomId          BIGINT,
    ClassId         BIGINT NOT NULL,
    LocId           BIGINT NOT NULL
);
    
ALTER TABLE schedule
  ADD CONSTRAINT scheduleFK1  FOREIGN KEY (roomId)
    REFERENCES room(RoomId);
    
ALTER TABLE schedule
  ADD CONSTRAINT scheduleFK2  FOREIGN KEY (classId)
    REFERENCES fitnessClass(classId);
    
ALTER TABLE schedule
  ADD CONSTRAINT ScheduleFK3  FOREIGN KEY (LocId)
    REFERENCES location(LocId);

CREATE TABLE reservation (
    ClassId     BIGINT NOT NULL,
    userId      BIGINT NOT NULL
);

ALTER TABLE reservation
  ADD CONSTRAINT reservationFK1  FOREIGN KEY (classId)
    REFERENCES fitnessClass(classId);
    
ALTER TABLE reservation
  ADD CONSTRAINT reservationFK2  FOREIGN KEY (userId)
    REFERENCES sec_user(userId);

CREATE TABLE workFrom (
    coachId     BIGINT NOT NULL,
    locId       BIGINT NOT NULL
);

ALTER TABLE workFrom
  ADD CONSTRAINT workFromFK1  FOREIGN KEY (coachId)
    REFERENCES coach(coachId);
    
ALTER TABLE workFrom
  ADD CONSTRAINT workFromFK2  FOREIGN KEY (locId)
    REFERENCES location(locId);

CREATE TABLE review (
    ReviewId    BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    CoachId     BIGINT NOT NULL,
    userId    BIGINT NOT NULL,
    ReviewDate  DATE,
    ComScore    CHAR(1),
    EnthScore   CHAR(1),
    PunctScore  CHAR(1),
    ReviewText  VARCHAR(500)
);

ALTER TABLE review
  ADD CONSTRAINT reviewFK1  FOREIGN KEY (coachId)
    REFERENCES coach(coachId);
ALTER TABLE review
  ADD CONSTRAINT reviewFK2  FOREIGN KEY (userId)
    REFERENCES sec_user(userId);

CREATE TABLE Reference (
    CoachId     BIGINT NOT NULL,
    userId    BIGINT NOT NULL
);
ALTER TABLE Reference
  ADD CONSTRAINT ReferenceFK1  FOREIGN KEY (coachId)
    REFERENCES coach(coachId);
ALTER TABLE review
  ADD CONSTRAINT ReferenceFK2  FOREIGN KEY (userId)
    REFERENCES sec_user(userId);

CREATE TABLE ClientCoach (
    coachId     BIGINT NOT NULL,
    userId    BIGINT NOT NULL,
    myCoach     BIT
);

ALTER TABLE ClientCoach
  ADD CONSTRAINT ClientCoachFK1  FOREIGN KEY (coachId)
    REFERENCES coach(coachId);
ALTER TABLE ClientCoach
  ADD CONSTRAINT ClientCoachFK2  FOREIGN KEY (userId)
    REFERENCES sec_user(userId);


CREATE TABLE sec_role(
  roleId   BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  roleName VARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE user_role
(
  id     BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  userId BIGINT NOT NULL,
  roleId BIGINT NOT NULL
);


ALTER TABLE user_role
  ADD CONSTRAINT user_role_uk UNIQUE (userId, roleId);

ALTER TABLE user_role
  ADD CONSTRAINT user_role_fk1 FOREIGN KEY (userId)
  REFERENCES sec_user (userId);
 
ALTER TABLE user_role
  ADD CONSTRAINT user_role_fk2 FOREIGN KEY (roleId)
  REFERENCES sec_role (roleId);

這是結果網頁

這是模式的 EERD

uj5u.com熱心網友回復：

請試試：

SELECT l.locName, COUNT(ul.dayOfVisit) AS numOfVisit -- ...

（別名 numOfVisit），因為我們使用的是BeanPropertyRowMapper（通過“bean 屬性”（即“欄位名稱”）映射：https : //www.google.com/search? q=java bean naming conventions ）。

或者使用 other/custom RowMapper。

因為即使是 javadoc 也推薦：

...為了獲得最佳性能，請考慮使用自定義 RowMapper實作。

最好的：

return jdbc.query(query, namedParameters, 
  (ResultSet rs, int rowNum) -> { // one ResultSet per row:
      // column indices start with 1(!):
      return new UserLocation(rs.getString(1), rs.getInt(2));
      // alternatively (name based): rs.getString("locName")...
    }
);

;)

RowMapper javadoc (spring-jdbc:current)
ResultSet javadoc (jdk17)
由于RowMapperis a/滿足函式式介面的要求，我們可以將其寫為lambda 運算式。

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/397698.html

標籤：爪哇 sql 弹簧靴数数

上一篇：如何從BigquerySchema中洗掉未使用的列名

下一篇：CASE陳述句SQL中回傳多列