android對象關系映射框架ormlite學習之多對多(ManyToMany)

前面介紹瞭ormlite操作單張表以及多表之間的一對多(多對一)的關系(請參考android對象關系映射框架ormlite學習之單表操作和android關系映射框架ormlite學習之OneToMany),但是我們還經常會遇到多對多的數據庫表關系,在處理多對多的關系表時,ormlite官方給出的解決方案是通過建立第三張關聯表來解決的。這裡通過這個解決方案來解決我們最後一個關於多對多關系。

我假設的場景是項目(Project)和用戶(User),即一個用戶參與多個項目,而一個項目又由多個人員進行開發,構成多對多的關系。同樣的對於中間重要的方法類註釋在代碼中。首先同樣給出運行效果圖:

實體用戶類User.java

vcD4KPHByZSBjbGFzcz0=”brush: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 projectsForUserQuery = null;
	private PreparedQuery usersForProjectQuery = null;
	
	/**
	 * 包含兩個泛型:
	 * 第一個泛型表DAO操作的類
	 * 第二個表示操作類的主鍵類型
	 */
	private RuntimeExceptionDao simpleRuntimeUserDao = null;
	private RuntimeExceptionDao simpleRuntimeProjectDao = null;
	private RuntimeExceptionDao 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 dao = getSimpleDataUserDao();
		//通過實體對象創建在數據庫中創建一條數據,成功返回1,說明插入瞭一條數據
		Log.i("test", "dao = " + dao+"  user= "+user);
		int returnValue = dao.create(user);
		Log.i("test", "插入數據後返回值:"+returnValue);
	}
	/**
	 * 查詢所有的用戶信息
	 * @return
	 */
	public List findAllUser(){
		RuntimeExceptionDao dao = getSimpleDataUserDao();
		return dao.queryForAll();
	}
	
	public RuntimeExceptionDao 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 getSimpleDataUserProjectDao() {
		if (simpleRuntimeUserProjectDao == null) {
			simpleRuntimeUserProjectDao = getRuntimeExceptionDao(UserProject.class);
		}
		Log.i("test", "simpleRuntimeDaodeptdept ======= "+simpleRuntimeUserProjectDao);
		return simpleRuntimeUserProjectDao;
	}
	
	/**
	 * 插入一條用戶項目關聯數據
	 */
	public void insertDept(UserProject dept){
		RuntimeExceptionDao dao = getSimpleDataUserProjectDao();
		//通過實體對象創建在數據庫中創建一條數據,成功返回1,說明插入瞭一條數據
		int returnValue = dao.create(dept);
		Log.i("test", "插入數據後返回值:"+returnValue);
	}
	
	/****************以下為對項目的操作*********************/
	
	public RuntimeExceptionDao getSimpleDataProjectDao() {
		if (simpleRuntimeProjectDao == null) {
			simpleRuntimeProjectDao = getRuntimeExceptionDao(Project.class);
		}
		Log.i("test", "simpleRuntimeDaodeptdept ======= "+simpleRuntimeProjectDao);
		return simpleRuntimeProjectDao;
	}
	
	public Project findByDeptId(int projectId){
		RuntimeExceptionDao dao = getSimpleDataProjectDao();
		return dao.queryForId(projectId);
	}
	
	/**
	 * 插入一條項目數據
	 */
	public void insertProject(Project project){
		RuntimeExceptionDao dao = getSimpleDataProjectDao();
		//通過實體對象創建在數據庫中創建一條數據,成功返回1,說明插入瞭一條數據
		int returnValue = dao.create(project);
		Log.i("test", "插入數據後返回值:"+returnValue);
	}
	
	public List lookupProjectsForUser(User user) throws SQLException {
		RuntimeExceptionDao dao = getSimpleDataProjectDao();
		if (projectsForUserQuery == null) {
			projectsForUserQuery = makePostsForUserQuery();
		}
		projectsForUserQuery.setArgumentHolderValue(0, user);
		return dao.query(projectsForUserQuery);
	}
	
	/**
	 * 查詢某個用戶所對應的項目
	 */
	private PreparedQuery makePostsForUserQuery() throws SQLException {
		RuntimeExceptionDao userProjectDao = getSimpleDataUserProjectDao();
		RuntimeExceptionDao projectDao = getSimpleDataProjectDao();
		//創建一個內關聯查詢用戶項目表
		QueryBuilder 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 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 lookupUsersForProject(Project project) throws SQLException {
		RuntimeExceptionDao dao = getSimpleDataUserDao();
		if (usersForProjectQuery == null) {
			usersForProjectQuery = makeUsersForProjectQuery();
		}
		usersForProjectQuery.setArgumentHolderValue(0, project);
		return dao.query(usersForProjectQuery);
	}
	/**
	 * 查詢某個項目的所有負責人
	 */
	private PreparedQuery makeUsersForProjectQuery() throws SQLException {
		RuntimeExceptionDao userProjectDao = getSimpleDataUserProjectDao();
		RuntimeExceptionDao userDao = getSimpleDataUserDao();
		QueryBuilder userProject = userProjectDao.queryBuilder();
		userProject.selectColumns(UserProject.USER_ID_FIELD_NAME);
		SelectArg userSelectArg = new SelectArg();
		userProject.where().eq(UserProject.PROJECT_ID_FIELD_NAME, userSelectArg);
		QueryBuilder 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 users = helper.findAllUser();
				if(users.size()>0){
					String str = users.get(0).getUserName()+" 參與的項目:";
					try {
						List 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 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;
	}

}

發佈留言