sqlite添加和rawquery问题

debugcn 发表于 Dev

阿米尔·巴尔克斯代尔（Amir Barksdale）

你好，我正在为dailly开发一个应用程序来扩展我的问题是数据库操作。

我需要在sqlite db中进行查询的帮助。
解决我的数据库中的任何问题。
方法是获取当前用户ID的方法。
查找是查看登录时用户是否存在。

这是源代码：

package com.example.i.projet;



import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class BaseDeDonee extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "Platforme.db";

public static final String TABLE_PERSONNE = "UsersTable";
public static final String COL_1 = "ID";
public static final String COL_2 = "nom";
public static final String COL_3 = "prenom";
public static final String COL_4 = "tel";
public static final String COL_5 = "profile";
public static final String COL_s = "password";
public static final String COL_k = "etat";


public static final String TABLE_COMPTE = "CompteTable";
public static final String COL_6 = "numero_compte";
public static final String COL_7 = "cle_compte";
public static final String COL_8 = "solde_courante";
public static final String COL_9 = "type_compte";

public static final String TABLE_OPPERATIONS = "OpperationsTable";
public static final String COL_10 = "num_opp";
public static final String COL_11 = "type_opp";
public static final String COL_12 = "date_opp";
public static final String COL_13 = "montant_opp";
public static final String COL_14 = "solde_courante";
//+etat de l'opp pour avoir est que personel ou du busness

public static final String TABLE_PRODUITS = "ProductsTable";

public static final String COL_15 = "nom_prod";
public static final String COL_16 = "type_prod";
public static final String COL_17 = "PrixUnit_prod";
public static final String COL_18 = "quantite_prod";
public static final String COL_19 = "PrixTotal_prod";

public static final String TABLE_FACTURES = "FacturesTable";

public static final String COL_20 = "num_fact";
public static final String COL_21 = "type_fact";
public static final String COL_22 = "Montant_fact";
public static final String COL_23 = "date_fact";

public static final String TABLE_IDENTIFICATION = "IdentificationTable";

public static final String COL_24 = "profile";
public static final String COL_25 = "password";

public static final String TABLE_DECAISSEMENT = "DecaissementTable";

public static final String COL_26 = "num_opp";
public static final String COL_27 = "type_compte";
public static final String COL_28 = "piecejustificatif";

public static final String TABLE_Temp = "tempTable";



public BaseDeDonee(Context context ) {
    super(context, DATABASE_NAME, null, 1);



}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table " + TABLE_PERSONNE +" (ID INTEGER             PRIMARY     KEY AUTOINCREMENT,nom TEXT,prenom TEXT,numero_tele INTEGER,profile TEXT,password Text,etat Text)");
    db.execSQL("create table " + TABLE_COMPTE +" (numero_compte INTEGER PRIMARY KEY,cle_compte INTEGER,solde_courante DOUBLE,type_compte TEXT,ID INTEGER REFERENCES TABLE_PERSONNE)");
    db.execSQL("create table " + TABLE_PRODUITS +" (nom_prod TEXT PRIMARY KEY,type_prod TEXT,PrixUnit_prod DOUBLE,quantite_prod INTEGER,PrixTotal_prod DOUBLE,num_opp INTEGER REFERENCES TABLE_OPPERATIONS)");
    db.execSQL("create table " + TABLE_FACTURES +" (num_fact INTEGER PRIMARY KEY AUTOINCREMENT,type_fact TEXT,Montant_fact DOUBLE,date_fact DATE,num_opp INTEGER REFERENCES TABLE_OPPERATIONS,ID INTEGER REFERENCES TABLE_PERSONNE)");
    db.execSQL("create table " + TABLE_OPPERATIONS +" (num_opp INTEGER PRIMARY KEY AUTOINCREMENT,type_opp TEXT,montant_opp DOUBLE,date_opp DATE,ID INTEGER REFERENCES TABLE_PERSONNE ,solde_courante DOUBLE REFERENCES TABLE_COMPTE)");
    db.execSQL("create table " + TABLE_IDENTIFICATION +" (profile TEXT REFERENCES TABLE_PERSONNE PRIMARY KEY ,password TEXT REFERENCES TABLE_PERSONNE)");
    db.execSQL("create table " + TABLE_DECAISSEMENT + " (piecejustificatif TEXT PRIMARY KEY,num_opp INTEGER REFERENCES TABLE_OPPERATIONS,type_compte TEXT REFERENCES TABLE_COMPTE)");
    db.execSQL("create table " + TABLE_Temp + " (ID INTEGER PRIMARY KEY REFERENCES TABLE_PERSONNE )");
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS "+TABLE_PERSONNE+TABLE_COMPTE+TABLE_PRODUITS+TABLE_FACTURES+TABLE_OPPERATIONS+TABLE_IDENTIFICATION+TABLE_DECAISSEMENT);
    onCreate(db);
}

public boolean insertData(String nom ,String prenom ,String password ,String tele,String profile,String etat){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues ContentVlues = new ContentValues();
    Cursor res = db.rawQuery("select id  from "+TABLE_PERSONNE+"where profile="+profile,null );

    ContentVlues.put(COL_2,nom);
    ContentVlues.put(COL_3,prenom);
    ContentVlues.put(COL_s,password);
    ContentVlues.put(COL_4,tele);
    ContentVlues.put(COL_5, profile);
    ContentVlues.put(COL_k, etat);

    if (res.getCount()==0){
        long  result =db.insert(TABLE_PERSONNE,null,ContentVlues);
        if (result==-1)
            return false;
        else return true ;


    }else return false;


}
public boolean insertl(String profile ,String password ){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues ContentVlues = new ContentValues();
    ContentVlues.put(COL_24,profile);
    ContentVlues.put(COL_25,password);



    long  result =db.insert(TABLE_IDENTIFICATION,null,ContentVlues);
    if (result==-1)
        return false;
    else return true ;

}


public boolean insertfacture(String type ,String Montantfact, String date){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues ContentVlues = new ContentValues();
    ContentVlues.put(COL_21,type);
    ContentVlues.put(COL_22,Montantfact);
    ContentVlues.put(COL_23,date);
    long  result =db.insert(TABLE_FACTURES,null,ContentVlues);
    if (result==-1)
        return false;
    else return true ;

}


public boolean insertCompte(String numero_compte ,String cle_compte, String solde_courante,String type_compte ){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues ContentVlues = new ContentValues();
    ContentVlues.put(COL_6,numero_compte);
    ContentVlues.put(COL_7,cle_compte);
    ContentVlues.put(COL_8,solde_courante);
    ContentVlues.put(COL_9,type_compte);
    long  result =db.insert(TABLE_COMPTE,null,ContentVlues);
    if (result==-1)
        return false;
    else return true ;

}
public boolean insertProduit(String nom_prod ,String type_prod, String PrixUnit_prod,String  quantite_prod,String PrixTotal_prod){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues ContentVlues = new ContentValues();

    ContentVlues.put(COL_15,nom_prod);
    ContentVlues.put(COL_16,type_prod);
    ContentVlues.put(COL_17,PrixUnit_prod);
    ContentVlues.put(COL_18,quantite_prod);
    ContentVlues.put(COL_19,PrixTotal_prod);


    long  result =db.insert(TABLE_PRODUITS,null,ContentVlues);

    if (result==-1)
        return false;
    else return true ;

}
public boolean inseloginid(String id  ){
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues ContentVlues = new ContentValues();
    ContentVlues.put(COL_1,id);




    long  result =db.insert(TABLE_Temp,null,ContentVlues);
    if (result==-1)
        return false;
    else return true ;

}

public Cursor afficherinfoP (){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from "+TABLE_PERSONNE,null );

    return res;
}
public Boolean finde (String lemail,String lpass){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from "+TABLE_IDENTIFICATION+"where profile="+lemail+"and password="+lpass,null );

    if (res.getCount()==0){

        return false;
    }else return true;
}
public String findID (String lemail){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select 'id'  from "+TABLE_PERSONNE+"where 'profile'="+lemail,null );

    String k = res.getString(res.getColumnIndex("id"));
    return k;
}
public String tempID (){
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("select * from "+TABLE_Temp,null);
    String k =res.getString(res.getColumnIndex("id"));
    return k ;

}
public double rapportJ(String date,String id ){
    Double k= Double.valueOf(0);
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor res = db.rawQuery("Select * from"+TABLE_FACTURES+"where date_fact="+date+"and id="+id,null);
    while (res.moveToNext()){
        k=k+ res.getDouble(2);
    }
    Cursor ress=db.rawQuery("Select * from"+TABLE_OPPERATIONS+"where date_opp="+date+"and id="+id,null);
    while (ress.moveToNext()){
        k=k+ ress.getDouble(2);
    }
    return k;
}

}

马里斯·拉克珊

尝试将查询更改为此

//here idColumnName is the colum you want to select
//comparatorColumn is the column you want to check with the WHERE clause
//valueToBeChecked is the string you use to check for availablility
//Use db.query() rather than db.rawQuery() as your valueToBeChecked might contain special characters which can produce an SQL exception
Cursor res = db.query(tableName,new String[]{idColumnName},"comparatorColumn =?",new String[]{valueToBeChecked},null,null,null);
//check the cursor like this
if(res!=null && res.moveToFirst(){
    //cursor contains data
}else{
    //no valid entry in the table
}

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。