Why a SQL SELECT statement doesn't return COUNT() result on Java Spring boot project?-CodePudding

this is my first question here so, please ask if you need more information. I am working on a personal project. I have a relatively complex relational database structure. I create schema.sql on my spring boot project as well as data.sql with sample data. I try to create a web application for simulated fitness centre web pages. I try to display the location name and number of visits for the user. I create a userLocation bean for keeping the result set as a list of the select query. I can test the statement on H2 database and its work. However, on my code, I cannot get the number of visits from the select statement.

Here is my userlocation bean,

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

}

Controller class getMapping method

    @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";
    }

Here database access method;

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));

    }

here 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);

Here the result web page

Here is EERD for the schema

CodePudding user response：

Please try:

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

(to alias numOfVisit), since we are using a BeanPropertyRowMapper (which mapps by "bean properties" (i.e. "field names"): https://www.google.com/search?q=java bean naming conventions).

Alternatively use an other/custom RowMapper.

And since even javadoc recommends:

... For best performance, consider using a custom RowMapper implementation.

Best:

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)
and since RowMapper is a/meets the requirements of a functional interface, we can write it as lambda expression.