java 从 SQLite 数据库-ANDROID-SQLite 数据库中选择特定行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30690857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-11-02 17:30:51 来源:igfitidea点击:
Selecting Specific rows from SQLite Database-ANDROID-SQLite Database
提问by Rohma Shakeel
I am working on databases for my project and I have columns for primary key (rowid), number (contact number) and name. I am adding two different entries with same number in my database and i need to extract both of them from the database. Code for extracting is
我正在为我的项目处理数据库，我有主键 ( rowid)、号码（联系电话）和姓名列。我在我的数据库中添加了两个具有相同编号的不同条目，我需要从数据库中提取它们。提取代码是
public Cursor SelectList(String number) throws SQLException {
String query = "SELECT FROM " + DATABASE_TABLE + " WHERE " + KEY_NUMBER + "='" + number.trim()+"'";
Cursor mcursor = db.rawQuery(query, null);
if(mcursor != null) {
mcursor.moveToFirst();
}
return mcursor;
}
But it is showing SQLite exception at this line
但它在这一行显示 SQLite 异常
Cursor mcursor = db.rawQuery(query, null);
Code for DatabaseHandler
DatabaseHandler 的代码
package com.example.gul.databasealvie;
/**
* Created by gul on 6/6/15.
*/
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
public class DBAdapter {
static final String KEY_ROWID = "_id";
static final String KEY_NAME = "name";
static final String KEY_NUMBER = "number";
static final String KEY_ID="listid";
static final String TAG = "DBAdapter";
static final String DATABASE_NAME = "MyDB20";
static final String DATABASE_TABLE = "contacts5";
static final int DATABASE_VERSION = 1;
static final String DATABASE_CREATE= "create table contacts5(_id integer primary key , "
+ "name text not null, number text not null, listid text not null);";
final Context context;
DatabaseHelper DBHelper;
SQLiteDatabase db;
public DBAdapter(Context ctx)
{
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}
private static class DatabaseHelper extends SQLiteOpenHelper
{
DatabaseHelper(Context context)
{
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db)
{
try {
db.execSQL(DATABASE_CREATE);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS contacts");
onCreate(db);
}
}
//---opens the database---
public DBAdapter open() throws SQLException
{
db = DBHelper.getWritableDatabase();
return this;
}
public boolean DeleteList(String number){
db.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE "+KEY_NUMBER+"="+number);
return true;
}
public void DropTable(){
db.execSQL("Delete From " + DATABASE_TABLE);
}
public Cursor SelectList(String number) throws SQLException {
String query = "SELECT FROM " + DATABASE_TABLE + " WHERE " + KEY_NUMBER + "='" + number.trim()+"'";
Cursor mcursor = db.rawQuery(query,null);
if (mcursor != null) {
mcursor.moveToFirst();
}
return mcursor;
}
//---closes the database---
public void close()
{
DBHelper.close();
}
//---insert a contact into the database---
public long insertContact(TableData contact, String id )
{
long myid=Long.parseLong(id);
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_NAME, contact.getName());
initialValues.put(KEY_NUMBER,contact.getPhoneNumber());
initialValues.put(KEY_ID, id);
return db.insert(DATABASE_TABLE, null, initialValues);
}
//---deletes a particular contact---
public boolean DeletContact(String number)throws SQLException{
return db.delete(DATABASE_TABLE, KEY_NUMBER + "=" + number, null) > 0;
}
public long insertContact(Anonymous contact, String id )
{
ContentValues initialValues = new ContentValues();
initialValues.put(KEY_NAME, contact.getName());
initialValues.put(KEY_NUMBER, contact.getPhoneNumber());
initialValues.put(KEY_ID, id);
// Log.d("Contact", contact.getName() + contact.getPhoneNumber());
return db.insert(DATABASE_TABLE, null, initialValues);
}
public boolean deleteContact(String number)
{
return db.delete(DATABASE_TABLE, KEY_NUMBER + " = ?",
new String[] { number }) > 0;
}
public Cursor getAllContacts()
{
return db.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME,
KEY_NUMBER}, null, null, null, null, null);
}
//---retrieves a particular contact---
public Cursor getContact(long rowId) throws SQLException
{
Cursor mCursor =
db.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
KEY_NAME, KEY_NUMBER}, KEY_ROWID + "=" + rowId, null,
null, null, null, null);
if (mCursor != null) {
mCursor.moveToFirst();
}
return mCursor;
}
public boolean iskey(long rowid)throws SQLException
{
Cursor mCursor=db.query(true,DATABASE_TABLE, new String[]{KEY_ROWID,KEY_NAME,KEY_NUMBER },KEY_ROWID+"="+rowid,null,null
,null,null,null);
if(mCursor!=null && mCursor.moveToFirst()){
return true;
}
else
return false;
}
//---updates a contact---
public boolean updateContact(long rowId, String name, String email)
{
ContentValues args = new ContentValues();
args.put(KEY_NAME, name);
args.put(KEY_NUMBER, email);
return db.update(
DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}
}
Code for testing purposes
用于测试目的的代码
package com.example.gul.databasealvie;
import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.widget.Toast;
public class MainActivity extends Activity {
DBAdapter db;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = new DBAdapter(this);
AddContact();
PrintingList();
GetContacts();
GetContact();
//UpdateContact();
DeleteContact();
}
public void AddContact() {
Anonymous a= new Anonymous(34,"Wei-Meng Lee", "12345");
Anonymous b= new Anonymous(2,"Wejhkjh Lee", "12234");
//---add a contact---
db.open();
if (db.insertContact(a,"22") >= 0){
Toast.makeText(this, "Add successful.", Toast.LENGTH_LONG).show();
}
if (db.insertContact(b,"21") >= 0) {
Toast.makeText(this, "Add successful.", Toast.LENGTH_LONG).show();
}
if (db.insertContact(b,"21") >= 0) {
Toast.makeText(this, "Add successful.", Toast.LENGTH_LONG).show();
}
db.close();
}
public void PrintingList(){
db.open();
Cursor mCursor=db.SelectList("12234");
if(mCursor.moveToFirst()){
do {
displaylist(mCursor);
}while(mCursor.moveToNext());
}
db.close();
}
public void GetContacts() {
//--get all contacts---
db.open();
// db.DeleteList("12345");
// if(db.DeletContact("12234")) {
// Log.i("Deleted contact", "");
//}
Cursor c = db.getAllContacts();
if (c.moveToFirst())
{
do {
DisplayContact(c);
} while (c.moveToNext());
}
db.close();
}
public void GetContact() {
//---get a contact---
db.open();
Cursor c = db.getContact(2);
if (c.moveToFirst())
DisplayContact(c);
else
Toast.makeText(this, "No contact found", Toast.LENGTH_LONG).show();
db.close();
}
public void UpdateContact() {
//---update a contact---
db.open();
if (db.updateContact(1, "Wei-Meng Lee", "[email protected]"))
Toast.makeText(this, "Update successful.", Toast.LENGTH_LONG).show();
else
Toast.makeText(this, "Update failed.", Toast.LENGTH_LONG).show();
db.close();
}
public void DeleteContact() {
db.open();
//if (db.deleteContact(1))
// Toast.makeText(this, "Delete successful.", Toast.LENGTH_LONG).show();
//else
// Toast.makeText(this, "Delete failed.", Toast.LENGTH_LONG).show();
db.close();
}
public void DisplayContact(Cursor c)
{
Log.i(
"contacts", "id: " + c.getString(0) + "\n" +
"Name: " + c.getString(1) + "\n" +
"Number: " + c.getString(2)
);
db.open();
if((db.iskey(2))){
Log.i("Yay ", "it's working");
}
db.close();
}
public void displaylist(Cursor c){
Log.i(
"List","listid:"+c.getString(0)+ "\n"+
"NAMElist: " + c.getString(1)+ "\n" +
"list: "+c.getString(2)
);
}
}
Code For Anonymous.java
匿名代码.java
package com.example.gul.databasealvie;
/**
* Created by gul on 6/6/15.
*/
/**
* Created by Noor Zia on 5/26/2015.
*/
public class Anonymous {
public long id;
public String name;
public String number;
public Anonymous(){
name="Unknown";
}
public Anonymous(String no){
name="Unknown";
number = no;
}
public Anonymous(long id, String name, String number){
name=name;
this.number=number;
this.name=name;
this.id=id;
}
public long getID(){
return this.id;
}
// setting id
// getting name
public String getName(){
return this.name;
}
// setting name
// getting phone number
public String getPhoneNumber(){
return this.number;
}
// setting phone number
}
回答by Manikanta Reddy
dbHelper = new DBHelper(getApplicationContext());
SQLiteDatabase db = dbHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("select * from centuaryTbl where email='"+email+"'",null);
if (cursor.moveToFirst())
{
do
{
String s1 = cursor.getString(cursor.getColumnIndex("s1"));
String s2 = cursor.getString(cursor.getColumnIndex("s2"));
String s3 = cursor.getString(cursor.getColumnIndex("s3"));
}while (cursor.moveToNext());
}
回答by Omar Mainegra
You need to specify the columns to retrieve. For instance:
您需要指定要检索的列。例如：
String query = "SELECT name, number FROM " + DATABASE_TABLE + " WHERE " + KEY_NUMBER + "='" + number.trim() + "'";
More information about Select clause
有关Select 子句的更多信息
回答by Damodhar Meshram
Or you can fetch all columns using '*'
或者您可以使用“*”获取所有列
Your query will be
您的查询将是
String query = "SELECT * FROM " + DATABASE_TABLE + " WHERE " + KEY_NUMBER + "='" + number.trim()+"'";
回答by abc
public String select_data(String email)
{
db=this.getReadableDatabase();
String query="select email,password from "+Table_name;
Cursor cursor=db.rawQuery(query,null);
String a,b;
b="not found";
if (cursor.moveToFirst()) {
do {
a=cursor.getString(0);
if(a.equals(email))
{
b=cursor.getString(1);
break;
}
} while(cursor.moveToNext());
}
return b;
}
