MappedCollection One to One bug

[githubyong]

spring-data-jdbc 2.3.0

@Data
public class User {
  @Id
  private Long id;
  private Date createdTime;
  private Date updatedTime;
  @Column("DOB")
  private Date dateofBirth;

  @MappedCollection(idColumn = "CREDS_ID")
  private Credentials credentials;
}

@Table("USER_CREDENTIALS")
@Data // lombok
public class Credentials {
  @Id
  private Long credsId;
  private String userName;
  private String password;
}

public interface UserRepository extends PagingAndSortingRepository<User, Integer> {
}

//test
 Optional<User> user =   userRepository.findById(1);

expect sql:

SELECT `user`.`id` AS `id`, `user`.`DOB` AS `DOB`, `user`.`created_time` AS `created_time`, `user`.`updated_time` AS `updated_time`, `credentials`.`creds_id` AS `credentials_creds_id`, `credentials`.`password` AS `credentials_password`, `credentials`.`user_name` AS `credentials_user_name` FROM `user` LEFT OUTER JOIN `USER_CREDENTIALS` `credentials` ON `credentials`.`CREDS_ID` = `user`.`CREDS_ID` WHERE `user`.`id` = :id

actual sql:

SELECT `user`.`id` AS `id`, `user`.`DOB` AS `DOB`, `user`.`created_time` AS `created_time`, `user`.`updated_time` AS `updated_time`, `credentials`.`creds_id` AS `credentials_creds_id`, `credentials`.`password` AS `credentials_password`, `credentials`.`user_name` AS `credentials_user_name` FROM `user` LEFT OUTER JOIN `USER_CREDENTIALS` `credentials` ON `credentials`.`CREDS_ID` = `user`.`id` WHERE `user`.`CREDS_ID` = :id

[schauder]

The statement that I expect and which according to my test is generated ends with

FROM `user` LEFT OUTER JOIN `USER_CREDENTIALS` `credentials` ON `credentials`.`CREDS_ID` = `user`.`id` WHERE `user`.`id` = :id

The @MappedCollection should not affect columns on the user table.

If you are actually seeing the statement from your original question that would be a bug and I would need a reproducer for it, because I currently can't reproduce it.

You might be looking for having the reference work in the opposite direction, i.e. from the user table to the user_credentials table.
In that case this would be a duplicate of #360

[githubyong]

@see https://javabydeveloper.com/spring-data-jdbc-one-to-one-example/
USER.CREDS_ID reference USER_CREDENTIALS.CREDS_ID
the db like

CREATE TABLE `USER` (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `CREATED_TIME` datetime NOT NULL,
    `UPDATED_TIME` datetime DEFAULT NULL,
    `USER_TYPE` varchar(45) NOT NULL,
    `DOB` date NOT NULL,
    `CREDS_ID` NULL,
    PRIMARY KEY (`ID`),
    CONSTRAINT `CREDS_ID_FK` 
     FOREIGN KEY (`CREDS_ID`) REFERENCES `USER_CREDENTIALS` (`CREDS_ID`)
);
CREATE TABLE `USER_CREDENTIALS` (
  `CREDS_ID` INT NOT NULL AUTO_INCREMENT,
  `USER_NAME` VARCHAR(20) NOT NULL,
  `PASSWORD` VARCHAR(45) NOT NULL,
   UNIQUE KEY `USERNAME_UNIQUE` (`USER_NAME`),
   PRIMARY KEY (`CREDS_ID`)
);

[githubyong]

another sample example

@Data
@Table("Emp")
public class Emp {
    @Id
    private int id;
    private String name;
    private int age;
    private int dept_id;

    @MappedCollection(idColumn = "dept_id",keyColumn = "dept_id")
    private Dept dept;
}

@Data
@Table("dept")
public class Dept {
    @Id
    private int dept_id;
    private String name;
}

public interface EmpRepository extends PagingAndSortingRepository<Emp, Integer> {
}

//test
   @Test
    public void testFindEmp(){
        Optional<Emp> emp = empRepository.findById(1);
        log.info("emp="+emp);
    }

expect sql:

SELECT `Emp`.`id` AS `id`, `Emp`.`age` AS `age`, `Emp`.`name` AS `name`, `Emp`.`dept_id` AS `dept_id`, `dept`.`name` AS `dept_name`, `dept`.`dept_id` AS `dept_dept_id` FROM `Emp` LEFT OUTER JOIN `dept` `dept` ON `dept`.`dept_id` = `Emp`.`dept_id` WHERE `Emp`.`id` = ?

actual sql:

SELECT `Emp`.`id` AS `id`, `Emp`.`age` AS `age`, `Emp`.`name` AS `name`, `Emp`.`dept_id` AS `dept_id`, `dept`.`name` AS `dept_name`, `dept`.`dept_id` AS `dept_dept_id` FROM `Emp` LEFT OUTER JOIN `dept` `dept` ON `dept`.`dept_id` = `Emp`.`id` WHERE `Emp`.`id` = ?
sql```

[schauder]

keyColumn doesn't have an effect on one to one mappings. Otherwise the same explanation holds as before.

[githubyong]

keyColumn doesn't have an effect on one to one mappings. Otherwise the same explanation holds as before.

so, how to support one-to-one ?
https://docs.spring.io/spring-data/jdbc/docs/current/reference/html/#reference

[schauder]

so, how to support one-to-one ?

Any object reference between two entities is considered a one-to-one relation ship.
It is expected to be represented in the database by a foreign key from the child entity to the primary key of the table to which the parent entity is mapped. (See #360).

The column name of that reference is derived from the type of the parent entity.
You can change that name by using the @Column annotation or the @MappedCollection.idColumn

@see https://javabydeveloper.com/spring-data-jdbc-one-to-one-example/
USER.CREDS_ID reference USER_CREDENTIALS.CREDS_ID

That example is broken. I'll comment there as well.

[githubyong]

#30 seems no matter with this.

[githubyong]

In other words, how to support many-to-one ?

[schauder]

#30 seems no matter with this.

Yeah, its #360. No idea how I mixed that up.