android离开房间的联接查询 [英] android left join query with room

问题描述

我正在尝试使用会议室资料库更改sqlite数据库.我对左联接查询有点困惑.

我已经用sqlite实现了它，但是不知道如何在房间里实现相同的效果?

这是我的表创建:

第一张表:通知

db.execSQL("CREATE TABLE IF NOT EXISTS $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY, $ICON TEXT, $TITLE INTEGER," +
                " $DATE INTEGER, $TYPE INTEGER,$URL TEXT, $MESSAGE INTEGER, FOREIGN KEY($TITLE) REFERENCES ${TableNotificationsTrans.getTableName(this)}(id)," +
                "FOREIGN KEY($MESSAGE) REFERENCES ${TableNotificationsTrans.getTableName(this)}(id))")

第二张表:Notification_Trans

db.execSQL("CREATE TABLE IF NOT EXISTS $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, $COLUMN_EN TEXT, $COLUMN_GU TEXT, $COLUMN_HI TEXT)")

我正在做的是将通知存储在 notification 表中，但是其名称和描述将以特定的语言存储在 notification_trans 中.

要实现的查询

DatabaseHelper.database!!.rawQuery("SELECT A.$COLUMN_ID, A.$ICON, N.${language.toLowerCase()} $TITLE, A.$DATE, A.$TYPE, A.$URL, M.${language.toLowerCase()} $MESSAGE FROM $TABLE_NAME A LEFT JOIN NotificationsTrans N ON A.$TITLE = N.id LEFT JOIN NotificationsTrans M ON A.$MESSAGE = M.id ORDER BY $DATE DESC LIMIT $pageNum*10, 10", null)

问题

How can I achieve same with room?

修改

我的应用程序是多语言应用程序，在该应用程序中我会获得带有特定语言(如印地语或古吉拉特语)的通知标题.我将通知详细信息存储在 notification 表中，标题存储在 notification_trans 中.

NotificationTrans具有ID，英语，印地语，古吉拉特语的列.

当用户要求使用古吉拉特语时，我正在从notificationTrans的古吉拉特语栏中检索通知标题.

在sqlite中，我能够做到.

但是现在我要和房间一起使用

解决方案

首先，您必须为两者都创建模型类，您可能已经声明了它们，如果它们已经存在，则只需进行一些更改即可.

   @Entity
public class Notification {
    @PrimaryKey
    int id;
    String icon;
    @ForeignKey(entity = Notification_Trans.class, parentColumns = "col_id", childColumns = "id")
    String title;
    int date;
    int type;
    String url;
    int msg;

}

    @Entity
public class Notification_Trans {
    @PrimaryKey(autoGenerate = true)
    int col_id;
    String column_en;
    String column_gu;
    String column_hi;

对于您的POJO，我不明白您的外键约束，因此，请原谅我，您可以根据需要进行更改. 您可以按照此声明您的DAO` @道

public interface DAO {

    @Query("SELECT note.id, note.title, note.description, category.name as categoryName " +
            "FROM note " +
            "LEFT JOIN category ON note.category_id = category.id")
    List getCategoryNotes();

}
`

我没有对查询进行任何更改，我在

它有助于为数据库创建一个单独的类，&跟踪对象. &您可以使用AppDatabase.getAppDatabase(context).notificationDao().yourQueryMethodName();

访问数据

您可能需要参考此以了解房间与房间之间的关系满足您的要求，

这是您的DAO的外观，`

@Insert
void insert(Notifications object);

//这将插入一个项目

 @Insert
 void insertAll(Notifications... objects);

这可以输入数据列表，

如果您调用insert()&，则可以使用数据库对象来调用此方法，例如AppDatabase.getAppDatabase(context).notificationDao().yourQueryMethodName()而不是yourQueryMethod().传递需要存储在数据库中的对象，它将完成

例如db.parcelDao().insert(parcel); 这就是我在ParcelDao中插入数据的方式，db是Database对象，&包裹是需要存储数据的对象.还有一件事，您不能在主线程上调用此方法，因此您可能需要使用Handler或AsyncTask，对不起，我忘了提到这一点. 看看Room 在Android开发人员那里进行培训房间的基本功能

I am trying to change my sqlite database with room library. I am little confuse with left join query.

I have implemented it with sqlite, but don't know how can I achieve same withh room?

Here is my table creation:

first table: Notification

db.execSQL("CREATE TABLE IF NOT EXISTS $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY, $ICON TEXT, $TITLE INTEGER," +
                " $DATE INTEGER, $TYPE INTEGER,$URL TEXT, $MESSAGE INTEGER, FOREIGN KEY($TITLE) REFERENCES ${TableNotificationsTrans.getTableName(this)}(id)," +
                "FOREIGN KEY($MESSAGE) REFERENCES ${TableNotificationsTrans.getTableName(this)}(id))")

second table: Notification_Trans

db.execSQL("CREATE TABLE IF NOT EXISTS $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, $COLUMN_EN TEXT, $COLUMN_GU TEXT, $COLUMN_HI TEXT)")

What I am doing is I am storing notification in notification table but its name and description will be stored with specific language, in notification_trans.

Query to achieve

DatabaseHelper.database!!.rawQuery("SELECT A.$COLUMN_ID, A.$ICON, N.${language.toLowerCase()} $TITLE, A.$DATE, A.$TYPE, A.$URL, M.${language.toLowerCase()} $MESSAGE FROM $TABLE_NAME A LEFT JOIN NotificationsTrans N ON A.$TITLE = N.id LEFT JOIN NotificationsTrans M ON A.$MESSAGE = M.id ORDER BY $DATE DESC LIMIT $pageNum*10, 10", null)

Question

How can I achieve same with room?

Edit

My application is multi-language application, where I am getting notification title with specific language, like Hindi or Gujarati. I am storing notification details in notification table, while title in notification_trans.

NotificationTrans have column with id, english, hindi, gujarati.

When user asked for gujarati, I am retriving notification title from notificationTrans's column gujarati.

I am able do so, in sqlite.

But now I want it with Room

解决方案

First You have to make the model classes for both, You may have already declared them , You just need to make a few changes if they already exists.

   @Entity
public class Notification {
    @PrimaryKey
    int id;
    String icon;
    @ForeignKey(entity = Notification_Trans.class, parentColumns = "col_id", childColumns = "id")
    String title;
    int date;
    int type;
    String url;
    int msg;

}

    @Entity
public class Notification_Trans {
    @PrimaryKey(autoGenerate = true)
    int col_id;
    String column_en;
    String column_gu;
    String column_hi;

This makes for your POJO, I couldn't understand your Foreign key constraints, so Pardon me for that, you can make changes as you see fit. You can Declare your DAO as per this` @Dao

public interface DAO {

    @Query("SELECT note.id, note.title, note.description, category.name as categoryName " +
            "FROM note " +
            "LEFT JOIN category ON note.category_id = category.id")
    List getCategoryNotes();

}
`

I haven't made changes in the query, which I found at Link here. As your query was a complex one, But, it'll give you an Idea about how to do that., After this, You just need to access your Dao interface from your Database class object, which will handle the creation & all other things room, like this one below`

@Database(entities = {Notification.class, NotificationTrans.class}, version = 3)
public abstract class AppDatabase extends RoomDatabase {

    private static AppDatabase instance;

    public static AppDatabase getAppDatabase(Context context) {
        if (instance == null) {
            instance =
                    Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "database_name")
                            // allow queries on the main thread.
                            // Don't do this on a real app! See PersistenceBasicSample for an example.
                            //.allowMainThreadQueries()
                            .fallbackToDestructiveMigration()
                            .build();
        }
        return instance;
    }

    public static void destroyInstance() {

        instance = null;
    }

    public abstract Dao notificationDao();

It helps creating a separate class for Database, & keeping track of object from it. & you can access your data with AppDatabase.getAppDatabase(context).notificationDao().yourQueryMethodName();

You may require to refer to this to understand the relations between room, & implement your requirement,

EDIT 1: Here's how your DAO should look like ,`

@Insert
void insert(Notifications object);

//This will insert a single Item

 @Insert
 void insertAll(Notifications... objects);

While this can enter a list of Data,

You can call this methods with your Database object, likeAppDatabase.getAppDatabase(context).notificationDao().yourQueryMethodName() here instead of yourQueryMethod(), if you call insert() & pass the object you need to store in the database, It'll do it,

For E.g.db.parcelDao().insert(parcel); this is how I insert Data in my ParcelDao, db is Database object, & parcel is the object of data need to be stored. One more thing, you can't call this method on main thread, so you may need to use Handler or AsyncTask for the purpose, Sorry I forgot to mention that. Have a look at Room Training at Android Developers for implementation of basic functionality of room

这篇关于android离开房间的联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！