android - 从 Inner Joins SQLite 获取单条记录

Question

我是 android 编程的新手。我在使用内部联接时遇到如何检索单个记录的问题。通过研究，我知道如何用一张 table 做到这一点，例如

public Shop getShop(int id) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_SHOPS, new String[] { KEY_ID,
KEY_NAME, KEY_SH_ADDR }, KEY_ID + "=?",
new String[] { String.valueOf(id) }, null, null, null, null);
if (cursor != null)
cursor.moveToFirst();
Shop contact = new Shop(Integer.parseInt(cursor.getString(0)),
cursor.getString(1), cursor.getString(2));
// return shop
return contact;
}

但是当我不得不考虑三个不同的表时，我不知所措。我已经能够检索所有记录，目前我检索单个记录的代码如下。我已经能够用相应的 id 替换 ? 并且它有效，但我显然不希望有那个静态。

getTeam 方法

//Getting Single Team
public List < Team > getTeam() {
 List < Team > teamList = new ArrayList < Team > ();
 // Select All Query
 String selectQuery = "SELECT teams.id, teams.team_name, teams.image, teams_vs_leagues.points, leagues.league_name " +
  "FROM teams_vs_leagues " +
  "INNER JOIN teams " +
  "ON teams_vs_leagues.team_id = teams.id " +
  "INNER JOIN leagues " +
  "ON teams_vs_leagues.league_id = leagues.id " +
  "WHERE teams.id = ?";

 SQLiteDatabase db = this.getWritableDatabase();
 Cursor cursor = db.rawQuery(selectQuery, null);
 // looping through all rows and adding to list
 if (cursor.moveToFirst()) {
  do {
   Team team = new Team();
   team.setId(Integer.parseInt(cursor.getString(0)));
   team.setTeamName(cursor.getString(1));
   team.setPath(cursor.getString(2));
   team.setPoints(Integer.parseInt(cursor.getString(3)));
   team.setLeagueName(cursor.getString(4));
   // Adding team to list
   teamList.add(team);
  } while (cursor.moveToNext());
 }
 // close inserting data from database
 db.close();
 // return team list
 return teamList;

}

团队课

public class Team {
 String team_name, path, league_name;
 int id, league_id, points;

 public Team(int keyId, String team_name, String path,
  int points, String league_name) {
  this.id = keyId;
  this.team_name = team_name;
  this.path = path;
  this.points = points;
  this.league_name = league_name;
 }

 public Team() {}

 public Team(int keyId) {
  this.id = keyId;

 }

 public int getId() {
  return id;
 }

 public void setId(int keyId) {
  this.id = keyId;
 }


 public int getLeagueId() {
  return league_id;
 }


 public String getTeamName() {
  return team_name;
 }

 public String getLeagueName() {
  return league_name;
 }

 public void setLeagueName(String league_name) {
  this.league_name = league_name;
 }

 public int getPoints() {
  return points;
 }

 public void setPoints(int points) {
  this.points = points;
 }


 public void setTeamName(String team_name) {
  this.team_name = team_name;
 }

 public void setLeague_id(int league_id) {
  this.league_id = league_id;
 }

 public void setPath(String path) {
  this.path = path;
 }

 public String getPath() {
  return path;
 }
}

Answer 1

因为你正在使用占位符

"WHERE teams.id = ?";

在您的查询中，您需要在 rawQuery() 中传递选择参数，以便在执行查询期间，占位符将被替换为实际值。

Cursor cursor = db.rawQuery(selectQuery, new String[]{id});//“id”是您要传递的值，而不是“?”。您可以对其进行硬编码，也可以将其一直传递给 getTeam()

检查 this .