前面介绍了ormlite操作单张表以及多表之间的一对多（多对一）的关系（请参考android对象关系映射框架ormlite学习之单表操作和android关系映射框架ormlite学习之OneToMany），但是我们还经常会遇到多对多的数据库表关系，在处理多对多的关系表时，ormlite官方给出的解决方案是通过建立第三张关联表来解决的。这里通过这个解决方案来解决我们最后一个关于多对多关系。
我假设的场景是项目（Project）和用户(User)，即一个用户参与多个项目，而一个项目又由多个人员进行开发，构成多对多的关系。同样的对于中间重要的方法类注释在代码中。首先同样给出运行效果图：
实体用户类User.java
@DatabaseTable(tableName = "tb_user")
public class User {
//这里用一个常量来设置属性的名字，这样我们在中就可以直接使用该名字
public final static String ID_FIELD_NAME = "user_id";
//用户编号
@DatabaseField(generatedId=true,columnName=ID_FIELD_NAME)
private int userId;
//用户名
@DatabaseField
private String userName;
public User() {
//提供无参构造函数，这样查询的时候可以返回查询出来的对象
}
public User( int userId,String userName) {
this.userId = userId;
this.userName = userName;
}
get/set方法
}实体项目类Project.java
/**
* 项目类（假设一个用户参与多个项目，一个项目又由多个用户参与负责）
* @author leox
*
*/
@DatabaseTable(tableName="tb_project")
public class Project {
public final static String ID_FIELD_NAME = "project_id";
//项目编号
@DatabaseField(generatedId=true,columnName=ID_FIELD_NAME)
private int projectId;
//项目名
@DatabaseField
private String projectName;
public Project() {}
public Project(int projectId, String projectName) {
this.projectId = projectId;
this.projectName = projectName;
}
get/set方法
}
用户项目关联类
/**
* 用户项目关联类
*（用ormlite实现ManyToMany需要在数据库中建立一张关联表）
* @author leox
*
*/
@DatabaseTable(tableName="tb_user_project")
public class UserProject {
public final static String USER_ID_FIELD_NAME = "user_id";
public final static String PROJECT_ID_FIELD_NAME = "project_id";
//用户项目编号
@DatabaseField(generatedId=true)
private int id;
//关联用户表
@DatabaseField(foreign = true,columnName=USER_ID_FIELD_NAME)
private User user;
//关联项目表
@DatabaseField(foreign = true,columnName=PROJECT_ID_FIELD_NAME)
private Project project;
public UserProject(){}
public UserProject(int id, User user, Project project) {
this.id = id;
this.user = user;
this.project = project;
}
get/set方法
}SQLLiteHelper类：
public class DatabaseHelper extends OrmLiteSqliteOpenHelper{
// 数据库名称
private static final String DATABASE_NAME = "helloAndroid.db";
// 数据库version
private static final int DATABASE_VERSION = 1;
private PreparedQuery<Project> projectsForUserQuery = null;
private PreparedQuery<User> usersForProjectQuery = null;
/**
* 包含两个泛型:
* 第一个泛型表DAO操作的类
* 第二个表示操作类的主键类型
*/
private RuntimeExceptionDao<User, Integer> simpleRuntimeUserDao = null;
private RuntimeExceptionDao<Project, Integer> simpleRuntimeProjectDao = null;
private RuntimeExceptionDao<UserProject, Integer> simpleRuntimeUserProjectDao = null;
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource) {
try {
Log.i(DatabaseHelper.class.getName(), "onCreate");
//创建用户、项目、用户项目关联三张表
TableUtils.createTable(connectionSource, UserProject.class);
TableUtils.createTable(connectionSource, User.class);
TableUtils.createTable(connectionSource, Project.class);
} catch (SQLException e) {
Log.e(DatabaseHelper.class.getName(), "Can‘t create database", e);
throw new RuntimeException(e);
}
}
/**
* 插入一条用户数据
*/
public void insert(User user){
RuntimeExceptionDao<User, Integer> dao = getSimpleDataUserDao();
//通过实体对象创建在数据库中创建一条数据，成功返回1，说明插入了一条数据
Log.i("test", "dao = " + dao+" user= "+user);
int returnValue = dao.create(user);
Log.i("test", "插入数据后返回值："+returnValue);
}
/**
* 查询所有的用户信息
* @return
*/
public List<User> findAllUser(){
RuntimeExceptionDao<User, Integer> dao = getSimpleDataUserDao();
return dao.queryForAll();
}
public RuntimeExceptionDao<User, Integer> getSimpleDataUserDao() {
if (simpleRuntimeUserDao == null) {
simpleRuntimeUserDao = getRuntimeExceptionDao(User.class);
}
Log.i("test", "simpleRuntimeDao ======= "+simpleRuntimeUserDao);
return simpleRuntimeUserDao;
}
/**
* 这个方法在你的应用升级以及它有一个更高的版本号时调用。所以需要你调整各种数据来适应新的版本
*/
@Override
public void onUpgrade(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource, int oldVersion,
int newVersion) {
Log.i("test", "更新....");
try {
Log.i(DatabaseHelper.class.getName(), "onUpgrade");
//删掉旧版本的数据
TableUtils.dropTable(connectionSource, User.class, true);
TableUtils.dropTable(connectionSource, UserProject.class, true);
//创建一个新的版本
onCreate(sqliteDatabase, connectionSource);
} catch (SQLException e) {
Log.e(DatabaseHelper.class.getName(), "Can‘t drop databases", e);
throw new RuntimeException(e);
}
}
/***************************************以下为用户项目关联操作******************************************/
public RuntimeExceptionDao<UserProject, Integer> getSimpleDataUserProjectDao() {
if (simpleRuntimeUserProjectDao == null) {
simpleRuntimeUserProjectDao = getRuntimeExceptionDao(UserProject.class);
}
Log.i("test", "simpleRuntimeDaodeptdept ======= "+simpleRuntimeUserProjectDao);
return simpleRuntimeUserProjectDao;
}
/**
* 插入一条用户项目关联数据
*/
public void insertDept(UserProject dept){
RuntimeExceptionDao<UserProject, Integer> dao = getSimpleDataUserProjectDao();
//通过实体对象创建在数据库中创建一条数据，成功返回1，说明插入了一条数据
int returnValue = dao.create(dept);
Log.i("test", "插入数据后返回值："+returnValue);
}
/****************以下为对项目的操作*********************/
public RuntimeExceptionDao<Project, Integer> getSimpleDataProjectDao() {
if (simpleRuntimeProjectDao == null) {
simpleRuntimeProjectDao = getRuntimeExceptionDao(Project.class);
}
Log.i("test", "simpleRuntimeDaodeptdept ======= "+simpleRuntimeProjectDao);
return simpleRuntimeProjectDao;
}
public Project findByDeptId(int projectId){
RuntimeExceptionDao<Project, Integer> dao = getSimpleDataProjectDao();
return dao.queryForId(projectId);
}
/**
* 插入一条项目数据
*/
public void insertProject(Project project){
RuntimeExceptionDao<Project, Integer> dao = getSimpleDataProjectDao();
//通过实体对象创建在数据库中创建一条数据，成功返回1，说明插入了一条数据
int returnValue = dao.create(project);
Log.i("test", "插入数据后返回值："+returnValue);
}
public List<Project> lookupProjectsForUser(User user) throws SQLException {
RuntimeExceptionDao<Project, Integer> dao = getSimpleDataProjectDao();
if (projectsForUserQuery == null) {
projectsForUserQuery = makePostsForUserQuery();
}
projectsForUserQuery.setArgumentHolderValue(0, user);
return dao.query(projectsForUserQuery);
}
/**
* 查询某个用户所对应的项目
*/
private PreparedQuery<Project> makePostsForUserQuery() throws SQLException {
RuntimeExceptionDao<UserProject, Integer> userProjectDao = getSimpleDataUserProjectDao();
RuntimeExceptionDao<Project, Integer> projectDao = getSimpleDataProjectDao();
//创建一个内关联查询用户项目表
QueryBuilder<UserProject, Integer> userProject = userProjectDao.queryBuilder();
//查询关联表tb_user_project时返回“project_id”如果没有该语句，即返回该表所有字段，相当于“select * from 表名”
//拼成sql语句：select project_id from tb_user_project
userProject.selectColumns(UserProject.PROJECT_ID_FIELD_NAME);
//这相当于一个可变的参数，相当于SQL语句中的“？”,这个参数会在后面的操作中指明
SelectArg userSelectArg = new SelectArg();
//设置条件语句（where user_id=?）
userProject.where().eq(UserProject.USER_ID_FIELD_NAME, userSelectArg);
//创建外部查询项目表
QueryBuilder<Project, Integer> postQb = projectDao.queryBuilder();
//设置查询条件（where project_id in()）;
postQb.where().in(Project.ID_FIELD_NAME, userProject);
/**
* 这里返回时完整的sql语句为
* "SELECT * FROM `tb_project`
* WHERE `project_id` IN (
* SELECT `project_id` FROM `tb_user_project` WHERE `user_id` = ?
* ) "
*/
return postQb.prepare();
}
public List<User> lookupUsersForProject(Project project) throws SQLException {
RuntimeExceptionDao<User, Integer> dao = getSimpleDataUserDao();
if (usersForProjectQuery == null) {
usersForProjectQuery = makeUsersForProjectQuery();
}
usersForProjectQuery.setArgumentHolderValue(0, project);
return dao.query(usersForProjectQuery);
}
/**
* 查询某个项目的所有负责人
*/
private PreparedQuery<User> makeUsersForProjectQuery() throws SQLException {
RuntimeExceptionDao<UserProject, Integer> userProjectDao = getSimpleDataUserProjectDao();
RuntimeExceptionDao<User, Integer> userDao = getSimpleDataUserDao();
QueryBuilder<UserProject, Integer> userProject = userProjectDao.queryBuilder();
userProject.selectColumns(UserProject.USER_ID_FIELD_NAME);
SelectArg userSelectArg = new SelectArg();
userProject.where().eq(UserProject.PROJECT_ID_FIELD_NAME, userSelectArg);
QueryBuilder<User, Integer> postQb = userDao.queryBuilder();
postQb.where().in(User.ID_FIELD_NAME, userProject);
return postQb.prepare();
}
}
Activity类：
public class MainActivity extends Activity {
Button button1;//数据初始化按钮
Button button2;//显示某人参与的项目
Button button3;//显示某项目参与的人员
TextView textView;//用来显示查询到的用户信息
DatabaseHelper helper = new DatabaseHelper(this);
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
button1 = (Button)this.findViewById(R.id.main_btn_inputinfo);
button2 = (Button)this.findViewById(R.id.main_project_show);
button3 = (Button)this.findViewById(R.id.main_user_show);
textView = (TextView)this.findViewById(R.id.main_show_user);
//点击注册按钮跳转到注册页面
button1.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
//创建第一个用户
User user1 = new User();
user1.setUserName("张三");
helper.insert(user1);
//创建第一个项目
Project project1 = new Project();
project1.setProjectName("项目一");
helper.insertProject(project1);
//将用户一和项目一关联起来
UserProject up1 = new UserProject();
up1.setProject(project1);
up1.setUser(user1);
helper.insertDept(up1);
//创建第二个项目
Project project2 = new Project();
project2.setProjectName("项目二");
helper.insertProject(project2);
//将用户一和项目二关联起来(即用户一参与了项目一和项目二)
UserProject up2 = new UserProject();
up2.setProject(project2);
up2.setUser(user1);
helper.insertDept(up2);
//创建第二个用户
User user2 = new User();
user2.setUserName("李四");
helper.insert(user2);
//将用户二和项目二关联起来(即项目二由用户一和用户二共同开发)
UserProject up3 = new UserProject();
up3.setProject(project2);
up3.setUser(user2);
helper.insertDept(up3);
Toast.makeText(MainActivity.this, "初始化成功！", Toast.LENGTH_LONG);
}
});
//点击显示某个人负责的项目按钮时的操作（将前面添加的用户一所对应的项目显示出来）
button2.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
List<User> users = helper.findAllUser();
if(users.size()>0){
String str = users.get(0).getUserName()+" 参与的项目：";
try {
List<Project> proList = helper.lookupProjectsForUser(users.get(0));
if(proList.size()>0){
for(Project p:proList){
//Log.i("test", "项目名："+p.getProjectName());
str+=p.getProjectName()+",";
}
}
textView.setText(str);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
//点击显示某项目负责人按钮时的操作（将项目二的负责人信息显示出来）
button3.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
Project project = helper.findByDeptId(2);
if(project!=null){
String str = project.getProjectName()+" 参与的人员有：";
try {
List<User> userList = helper.lookupUsersForProject(project);
if(userList.size()>0){
for(User u:userList){
//Log.i("test", "项目名："+p.getProjectName());
str+=u.getUserName()+",";
}
}
textView.setText(str);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
});
}
@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.main, menu);
return true;
}
}