android 多表联合查询语句,GreenDao建立多表之间的关联

上一篇中介绍了如何使用GreenDao保存List和实体类嵌套类型的数据， 但是在实际开发中经常会遇到List和嵌套的实体类也是需要保存的表。

比如，在上一篇文章中的User代表一个用户，但是在每一个用户中可能会保存一个订单的集合–List. 那么这种情况就需要创建多个表格，并建立关系了。

这篇文章主要以介绍如何使用ToMany来建立两张表格之间的关系

废话不多说，直接上代码

先创建MyOrder.java

import org.greenrobot.greendao.annotation.Entity;

import org.greenrobot.greendao.annotation.Id;

import org.greenrobot.greendao.annotation.Property;

import org.greenrobot.greendao.annotation.Generated;

@Entity

public class MyOrder {

@Id(autoincrement = true)

private Long id;

@Property(nameInDb = "date")

private String date;

@Property(nameInDb = "customId")

private Long customId;

@Generated(hash = 1011091970)

public MyOrder(Long id, String date, Long customId) {

this.id = id;

this.date = date;

this.customId = customId;

}

@Generated(hash = 191667629)

public MyOrder() {

}

public Long getId() {

return this.id;

}

public void setId(Long id) {

this.id = id;

}

public String getDate() {

return this.date;

}

public void setDate(String date) {

this.date = date;

}

public Long getCustomId() {

return this.customId;

}

public void setCustomId(Long customId) {

this.customId = customId;

}

其中customId，会用User的主键来当做此表的外键

然后再修改一下之前的User.java, 如下所示：

import com.example.extdannyjiang.greendaodemo.utils.StringConverter;

import org.greenrobot.greendao.annotation.Convert;

import org.greenrobot.greendao.annotation.Entity;

import org.greenrobot.greendao.annotation.Id;

import org.greenrobot.greendao.annotation.NotNull;

import org.greenrobot.greendao.annotation.Property;

import org.greenrobot.greendao.annotation.ToMany;

import org.greenrobot.greendao.annotation.Transient;

import org.greenrobot.greendao.annotation.Generated;

import java.util.List;

import org.greenrobot.greendao.DaoException;

import com.example.extdannyjiang.greendaodemo.dao.DaoSession;

import com.example.extdannyjiang.greendaodemo.dao.UserDao;

import com.example.extdannyjiang.greendaodemo.dao.MyOrderDao;

/**

* Created by Danny.姜 on 17/6/20.

*

* @Entity 用于标识这是一个需要Greendao帮我们生成代码的bean

* @Id 标明主键，括号里可以指定是否自增

* @Property 用于设置属性在数据库中的列名(默认不写就是保持一致)

* @NotNull 非空

*/

@Entity // 用于标识这是一个需要Greendao帮我们生成代码的bean

public class User {

@Id(autoincrement = true)

private Long id;

@Property(nameInDb = "user_name")

@NotNull

private String name;

@Convert(columnType = String.class, converter = StringConverter.class)

private List nickNames;

@Transient

private int temp;

@ToMany(referencedJoinProperty = "customId")

private List orders;

/** Used for active entity operations. */

@Generated(hash = 1507654846)

private transient UserDao myDao;

/** Used to resolve relations */

@Generated(hash = 2040040024)

private transient DaoSession daoSession;

public String getName() {

return this.name;

}

public void setName(String name) {

this.name = name;

}

public Long getId() {

return this.id;

}

public void setId(Long id) {

this.id = id;

}

public List getNickNames() {

return this.nickNames;

}

public void setNickNames(List nickNames) {

this.nickNames = nickNames;

}

/**

* Convenient call for {@link org.greenrobot.greendao.AbstractDao#refresh(Object)}.

* Entity must attached to an entity context.

*/

@Generated(hash = 1942392019)

public void refresh() {

if (myDao == null) {

throw new DaoException("Entity is detached from DAO context");

}

myDao.refresh(this);

}

/**

* Convenient call for {@link org.greenrobot.greendao.AbstractDao#update(Object)}.

* Entity must attached to an entity context.

*/

@Generated(hash = 713229351)

public void update() {

if (myDao == null) {

throw new DaoException("Entity is detached from DAO context");

}

myDao.update(this);

}

/**

* Convenient call for {@link org.greenrobot.greendao.AbstractDao#delete(Object)}.

* Entity must attached to an entity context.

*/

@Generated(hash = 128553479)

public void delete() {

if (myDao == null) {

throw new DaoException("Entity is detached from DAO context");

}

myDao.delete(this);

}

/** Resets a to-many relationship, making the next get call to query for a fresh result. */

@Generated(hash = 1446109810)

public synchronized void resetOrders() {

orders = null;

}

/**

* To-many relationship, resolved on first access (and after reset).

* Changes to to-many relations are not persisted, make changes to the target entity.

*/

@Generated(hash = 1892052450)

public List getOrders() {

if (orders == null) {

final DaoSession daoSession = this.daoSession;

if (daoSession == null) {

throw new DaoException("Entity is detached from DAO context");

}

MyOrderDao targetDao = daoSession.getMyOrderDao();

List ordersNew = targetDao._queryUser_Orders(id);

synchronized (this) {

if(orders == null) {

orders = ordersNew;

}

}

}

return orders;

}

/** called by internal mechanisms, do not call yourself. */

@Generated(hash = 2059241980)

public void __setDaoSession(DaoSession daoSession) {

this.daoSession = daoSession;

myDao = daoSession != null ? daoSession.getUserDao() : null;

}

@Generated(hash = 411979277)

public User(Long id, @NotNull String name, List nickNames) {

this.id = id;

this.name = name;

this.nickNames = nickNames;

}

@Generated(hash = 586692638)

public User() {

}

}

其中orders是List< MyOrder>类型，因此需要建立oMany的关系。

使用@ToMany(referencedJoinProperty = “customId”)表示在MyOrder表中可以创建多条记录，同时customId是同样的

具体使用

重新整理布局文件：

xmlns:android="http://schemas.android.com/apk/res/android"

android:layout_width="match_parent"

android:layout_height="match_parent">

android:layout_width="match_parent"

android:layout_height="match_parent"

android:orientation="vertical">

android:id="@+id/editUserName"

android:hint="please input user name"

android:layout_width="match_parent"

android:layout_height="wrap_content" />

android:id="@+id/editUserNickName1"

android:hint="please input user name"

android:layout_width="match_parent"

android:layout_height="wrap_content" />

android:id="@+id/editUserNickName2"

android:hint="please input user name"

android:layout_width="match_parent"

android:layout_height="wrap_content" />

android:text="创建数据库"

android:onClick="createDatabase"

android:layout_width="match_parent"

android:layout_height="wrap_content" />

android:text="插入数据"

android:onClick="insert"

android:layout_width="match_parent"

android:layout_height="wrap_content" />

android:text="查询数据"

android:onClick="query"

android:layout_width="match_parent"

android:layout_height="wrap_content" />

android:text="查询所有"

android:onClick="queryAllOrders"

android:layout_width="match_parent"

android:layout_height="wrap_content" />

android:id="@+id/listView"

android:layout_width="match_parent"

android:layout_height="match_parent">

显示效果如下：

修改MainActivity.java如下：

import android.support.v7.app.AppCompatActivity;

import android.os.Bundle;

import android.util.Log;

import android.view.View;

import android.widget.ArrayAdapter;

import android.widget.EditText;

import android.widget.ListView;

import android.widget.Toast;

import com.example.extdannyjiang.greendaodemo.dao.DaoMaster;

import com.example.extdannyjiang.greendaodemo.dao.DaoSession;

import com.example.extdannyjiang.greendaodemo.dao.MyOrderDao;

import com.example.extdannyjiang.greendaodemo.dao.UserDao;

import com.example.extdannyjiang.greendaodemo.dao_bean.MyOrder;

import com.example.extdannyjiang.greendaodemo.dao_bean.User;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

public class MainActivity extends AppCompatActivity {

private DaoMaster daoMaster;

private DaoSession daoSession;

private UserDao userDao;

private EditText editName;

private ListView listView;

private ArrayAdapter adapter;

private List list = new ArrayList<>();

private EditText editNickName1;

private EditText editNickName2;

private MyOrderDao orderDao;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

editName = ((EditText) findViewById(R.id.editUserName));

editNickName1 = ((EditText) findViewById(R.id.editUserNickName1));

editNickName2 = ((EditText) findViewById(R.id.editUserNickName2));

listView = ((ListView) findViewById(R.id.listView));

adapter = new ArrayAdapter(this,

android.R.layout.simple_list_item_1, list);

listView.setAdapter(adapter);

}

public void createDatabase(View view) {

DaoMaster.DevOpenHelper helper = new DaoMaster.DevOpenHelper(this, "user.db");

daoMaster = new DaoMaster(helper.getWritableDb());

daoSession = daoMaster.newSession();

userDao = daoSession.getUserDao();

orderDao = daoSession.getMyOrderDao();

}

public void insert(View view) {

User user = new User();

user.setName(editName.getText().toString());

List nickNames = new ArrayList<>();

String nickName1 = editNickName1.getText().toString();

String nickName2 = editNickName2.getText().toString();

nickNames.add(nickName1);

nickNames.add(nickName2);

user.setNickNames(nickNames);

long insertID = userDao.insert(user);

if (insertID >= 0) {

Toast.makeText(this, "插入 User 成功", Toast.LENGTH_SHORT).show();

insertOrders(insertID);

} else {

Toast.makeText(this, "插入User 失败", Toast.LENGTH_SHORT).show();

}

}

private void insertOrders(long userId) {

MyOrder myOrder1 = new MyOrder(null, new Date() + "", userId);

MyOrder myOrder2 = new MyOrder(null, new Date() + "", userId);

long orderId1 = orderDao.insert(myOrder1);

long orderId2 = orderDao.insert(myOrder2);

if (orderId1 >= 0 && orderId2 >= 0) {

Toast.makeText(this, "插入 MyOrder 成功", Toast.LENGTH_SHORT).show();

}

}

public void query(View view) {

List users = userDao.queryBuilder().list();

list.clear();

for (User user : users) {

String name = user.getName();

List nickNames = user.getNickNames();

for (String nickName : nickNames) {

Log.e("TAG", "query " + name + " : " + nickName);

}

list.add(user.getName());

}

adapter.notifyDataSetChanged();

}

public void queryAllOrders(View view) {

List users = userDao.loadAll();

for (User user : users) {

List orders = user.getOrders();

for (MyOrder order : orders) {

Log.e("TAG", "get user " + user.getName() + " order " + order.getDate());

}

}

}

}

解释一下：在insert方法中当插入User数据成功之后会拿到一个插入的ID，然后拿着这个ID，想MyOrder表格中插入两条MyOrder的数据。

运行成功之后，看一下数据库中的数据内容如下：

20170622102114679.jpeg

可以看到在user.db中会创建USER和MY_ORDER两张表格

USER表格内容如下：

20170622102303271.jpeg

在USER表格中存在两条记录，ID分别是1，2 名字依次是danny， jiang

MY_ORDER表格内容如下：

20170622102511000.jpeg

因为有两条User的记录，每一个User又有两条Order的记录。 因此在MY_ORDER表格中存在4条记录主键是1,2,3,4 但是customId只有1和2

GreenDao另外一个强大的地方就在于建立多表关联之后， 查询数据也会方便很多。

我们知道在User中保存了List< MyOrder>的集合， 那我在查处所有的User集合之后，在这些User数据集合中会有MyOrder的数据吗？？ 答案是肯定的

在queryAllOrders方法中我查询的事UserDao中的数据，但是通过此UserDao还是能获取MyOrder的信息，打印log如下所示：

20170622104122156.jpeg