数据库值回溯问题 [英] Database values retrival Problem
本文介绍了数据库值回溯问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我是android的新手，我试图在单击列表视图时从sqlite数据库中检索值，如果我在查询中发布了某个名字，我会得到答案，如果我打算用变量做的话我也不能这样做我得到了下面的答案
I am new to android , I am trying to retrieve values from sqlite database on clicking the list-view,I am getting answer if I post someone name in the query, I cant do the same if I plan to do with variables.I am getting answer for below one
Cursor c1 = newDB.rawQuery("SELECT DISTINCT AppNo, AppName, FHcode, Area FROM "
+tableName +" where AppName ='sarah'" ,null);
如果我用变量替换我没有得到答案
I am not getting answer if I replace with variable
Cursor c1 = newDB.rawQuery("SELECT DISTINCT AppNo, AppName, FHcode, Area FROM "
+tableName +" where AppName ="+data+"" ,null);
以下我发布完整的代码请任何人检查这个并在这方面帮助我。
提前感谢所有人......
DBhelper
Following this I post entire code Please anyone check this and help me in this regard.
Thanks in advance for everyone......
DBhelper
public class DBHelper extends SQLiteOpenHelper{
public SQLiteDatabase DB;
public String DBPath;
public static String DBName = "VERIFY ME1.sqlite3";
public static final int version = '1';
public static Context currentContext;
public static String tableName = "FORM2";
public DBHelper(Context context) {
super(context, DBName, null, version);
currentContext = context;
DBPath = "/data/data/" + context.getPackageName() + "/databases";
createDatabase();
Oninsert(DB);
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
}
private void createDatabase() {
boolean dbExists = checkDbExists();
if (dbExists) {
// do nothing
}
else {
DB = currentContext.openOrCreateDatabase(DBName, 0, null);
DB.execSQL("CREATE TABLE IF NOT EXISTS " +tableName +" (AppNo VARCHAR, AppName VARCHAR," +
" Area VARCHAR, FHcode INT(3));");
}}
private boolean checkDbExists() {
SQLiteDatabase checkDB = null;
try {
String myPath = DBPath + DBName;
checkDB = SQLiteDatabase.openDatabase(myPath, null,
SQLiteDatabase.OPEN_READONLY);
} catch (SQLiteException e) {
// database does't exist yet.
}
if (checkDB != null) {
checkDB.close();
}
return checkDB != null ? true : false;
}
private void Oninsert(SQLiteDatabase dB2) {
// TODO Auto-generated method stub
DB = currentContext.openOrCreateDatabase(DBName, 0, null);
DB.execSQL("INSERT INTO " +
tableName +
" Values ('M001','shumi','India',250);");
DB.execSQL("INSERT INTO " +
tableName +
" Values ('C002','sarah','India',251);");
DB.execSQL("INSERT INTO " +
tableName +
" Values ('D003','Lavya','USA',252);");
DB.execSQL("INSERT INTO " +
tableName +
" Values ('V004','Avi','EU',253);");
DB.execSQL("INSERT INTO " +
tableName +
" Values ('T005','Shenoi','Bangla',254);");
DB.execSQL("INSERT INTO " +
tableName +
" Values ('L006','Lamha','Australia',255);");
DB.close();
}
}
列表活动
Listactivity
public class login2 extends ListActivity implements OnItemClickListener {
private static final login2 ListActivity = null;
private static final AdapterView<?> parent = null;
private static int mPosition = 0;
private static final long id = 0;
private ArrayList<String> results = new ArrayList<String>();
private String tableName = DBHelper.tableName;
private SQLiteDatabase newDB;
private String AppName1,ApplID,FHcode,Area;
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
openAndQueryDatabase();
displayResultList();
}
private void displayResultList() {
setListAdapter(new ArrayAdapter<String>(this,
android.R.layout.simple_list_item_1, results));
getListView().setTextFilterEnabled(true);
getListView().setOnItemClickListener(this);
}
private String openAndQueryDatabase() {
try {
DBHelper dbHelper = new DBHelper(this.getApplicationContext());
newDB = dbHelper.getWritableDatabase();
Cursor c = newDB.rawQuery("SELECT * FROM " +tableName +"", null);
if (c != null ) {
if (c.moveToFirst()) {
do {
AppName1 = c.getString(c.getColumnIndex("AppName"));
results.add(AppName1 );
}while (c.moveToNext());
}
}
c.close();
} catch (SQLiteException se ) {
Log.e(getClass().getSimpleName(), "Could not create or Open the database");
} finally {
//if (newDB == null)
// newDB.execSQL("DELETE FROM " + tableName);
//newDB.close();
}
return AppName1;
}
public void onClick(View arg0) {
login2 det = (login2)ListActivity;
det.onItemClick(parent, arg0, mPosition, id);
}
@Override
public void onItemClick(AdapterView<?> parent, View view, int position,
long id) {
// TODO Auto-generated method stub
String data=(String)parent.getItemAtPosition(position);
//showMessage("Successfully", data);
if (data != null ) {
try {
DBHelper dbHelper = new DBHelper(this.getApplicationContext());
newDB = dbHelper.getWritableDatabase();
Cursor c1 = newDB.rawQuery("SELECT DISTINCT AppNo, AppName, FHcode, Area FROM "
+tableName +" where AppName ="+data+"" ,null);
if (c1 != null ) {
if (c1.moveToFirst()) {
do {
ApplID= c1.getString(c1.getColumnIndex("AppNo"));
String AppName =c1.getString(c1.getColumnIndex("AppName"));
Area = c1.getString(c1.getColumnIndex("Area"));
FHcode =c1.getString(c1.getColumnIndex("FHcode"));
Intent intent = new Intent(getApplicationContext(), form.class);
//Create a bundle object
Bundle b = new Bundle();
//Inserts a String value into the mapping of this Bundle
b.putString("AppName", AppName.toString());
b.putString("Apprefno", ApplID.toString());
b.putString("FHcode", FHcode.toString());
b.putString("Area", Area.toString());
//Add the bundle to the intent.
intent.putExtras(b);
//start the DisplayActivity
startActivity(intent);
}
while (c1.moveToNext());
}
}
}
catch (SQLiteException se ) {
Log.e(getClass().getSimpleName(), "Could not create or Open the database");
} finally {
//if (newDB == null)
//newDB.execSQL("DELETE FROM " + tableName);
newDB.close();
}
}
}
推荐答案
除了糟糕的设计/导致sql注入，当然这个
apart from bad design/leading to sql injection, surely this
Cursor c1 = newDB.rawQuery("SELECT DISTINCT AppNo, AppName, FHcode, Area FROM "
+tableName +" where AppName ="+data+"" ,null);
与你的硬编码示例并不完全相同 - 你没有围绕数据值的单引号，但在sql的末尾有一个双重'双引号'。如果我要做这样一种令人讨厌的方式，我可能会使用
isn't exactely the same as your hard-coded example - you don't have single quotes around the data value but do have a double 'double-quote' on the end of the sql. If I was going to do it such a loathsome way, I'd likely use
Cursor c1 = newDB.rawQuery("SELECT DISTINCT AppNo, AppName, FHcode, Area FROM "
+tableName +" where AppName ='"+data+"'" ,null);
..无论如何都是这个意图 - 你可能需要更多地弄乱它
.. that's the intent anyway - you may need to 'fiddle' with it more
这篇关于数据库值回溯问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
