CODEWITHSUNDEEP
androidandroid-sqliteandroid-room
Asim
Asim

Reputation: 7114

Room only inserting 1 row every time (replacing previous)

I've set up some FK constraints and they're most probably the culprit but I can't figure it out after a lot of trial and error.

Problem: Every time I insert a new university, it replaces the last one. Every time I insert a semester into university, it replaces the old one. Every time I insert a course into a semester, it replaces the old one.

Classes are given below:

User.class

@Entity
public class User
{
    @PrimaryKey
    @NotNull
    private String userEmail;
    private String userName;

    public User(String userName, String userEmail)
    {
        this.userName = userName;
        this.userEmail = userEmail;
    }
}

University.class

@Entity(foreignKeys = @ForeignKey(entity = User.class, parentColumns = "userEmail", childColumns = "userEmail", onDelete = CASCADE, onUpdate = CASCADE),
        indices = {@Index(value = {"userEmail"}, unique = true)})
public class University
{
    @PrimaryKey
    @NotNull
    private String universityName;
    private String userEmail;

    public University(String universityName, String userEmail)
    {
        this.universityName = universityName;
        this.userEmail = userEmail;
    }
}

Semester.class

@Entity(foreignKeys = @ForeignKey(entity = University.class, parentColumns = "universityName", childColumns = "universityName", onDelete = CASCADE, onUpdate = CASCADE),
        indices = {@Index(value = {"universityName"}, unique = true)})
public class Semester
{
    @PrimaryKey(autoGenerate = true)
    private Long semesterId;
    private String name;
    private String universityName;

    public Semester(Long semesterId, String name, String universityName)
    {
        this.semesterId = semesterId;
        this.name = name;
        this.universityName = universityName;
    }

    @Ignore
    public Semester(String name, String universityName)
    {
        this.name = name;
        this.universityName = universityName;
    }
}

Course.class

@Entity(foreignKeys = @ForeignKey(entity = Semester.class, parentColumns = "semesterId", childColumns = "semesterId", onDelete = CASCADE, onUpdate = CASCADE),
        indices = {@Index(value = {"semesterId"}, unique = true)})
public class Course
{
    @PrimaryKey(autoGenerate = true)
    private Long courseId;
    private String name;
    private String code;
    private Long semesterId;

    public Course(Long courseId, String name, String code, Long semesterId)
    {
        this.courseId = courseId;
        this.name = name;
        this.code = code;
        this.semesterId = semesterId;
    }

    @Ignore
    public Course(String name, String code, Long semesterId)
    {
        this.name = name;
        this.code = code;
        this.semesterId = semesterId;
    }
}

DAO

@Dao
public interface UserDao
{
    @Query("SELECT * FROM User LIMIT 1")
    User getUser();

    @Query("SELECT * FROM University WHERE userEmail = :userEmail")
    List<University> getUniversitiesByUser(String userEmail);

    @Query("SELECT * FROM University WHERE universityName = :universityName LIMIT 1")
    University getUniversityByName(String universityName);

    @Query("SELECT * FROM Semester WHERE name = :semesterName AND universityName = :universityName LIMIT 1")
    Semester getSemesterByNameAndUniversity(String semesterName, String universityName);

    @Query("SELECT * FROM Semester WHERE universityName = :universityName")
    List<Semester> getSemestersByUniversity(String universityName);

    @Query("SELECT * FROM Course WHERE semesterId = :semesterId")
    List<Course> getCoursesBySemester(long semesterId);

    /**
     * @param userEmail - The Email of the selected user
     * @return - A list of all incidents submitted by the user
     */
    @Query("SELECT * FROM Incident WHERE submitterEmail = :userEmail")
    List<Incident> getAllIncidentsByUser(String userEmail);

    // Updates
    @Update(onConflict = REPLACE) void updateUser(User user);
    @Update(onConflict = REPLACE) void updateUniversity(University university);
    @Update(onConflict = REPLACE) void updateSemester(Semester semester);

    // Inserts
    @Insert(onConflict = REPLACE) void insertUser(User user);
    @Insert(onConflict = REPLACE) void insertUniversity(University university);
    @Insert(onConflict = REPLACE) void insertSemester(Semester semester);
    @Insert(onConflict = REPLACE) void insertCourse(Course course);

    // Deletes
    @Delete void deleteUser(User user);
    @Delete void deleteUniversity(University university);
    @Delete void deleteSemester(Semester semester);
    @Query("DELETE FROM User")
    void deleteAllUsers();
}

Upvotes: 1

Views: 88

Answers (1)

MikeT
MikeT

Reputation: 56943

By using unique indexes on the relationship to the parent you are restricting the child to a 1-1 relationship.

So there can only be 1 course per semester, 1 semester per University and 1 University per useremail.

Remove all the unique = true from the indexes.

You can also only have 1 University by Name due to the name being the Primary Key so a University can only have 1 user. I would suggest NOT having a user as the parent of a university.

To allow a user to attend a single university and for many users to attend the same university you could reverse the relationship.

However you may wish for a user to attend many universities in which case you would use an intermediate (associative/reference/mapping plus other names) table.

Upvotes: 1

Related Questions