lundi 7 mars 2016

How to join my two tables in android SQLite?

I have created these two table contacts and user_log now I want to join these two tables; how can I do this?

package com.example.usman.loginregistet;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DatabaseHelper extends SQLiteOpenHelper{



    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "contacts.db";
    private static final String TABLE_NAME = "contacts";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_EMAIL = "email";
    private static final String COLUMN_UNAME = "uname";
    private static final String COLUMN_PASS = "pass";

    private static final String USER_LOG_TABLE_NAME = "user_log"; //
    private static final String USER_LOG_COLUMN_ID = "user_id"; //
    private static final String COLUMN_DATE_TIME = "date_time"; //
    private static final String COLUMN_IP = "ip"; //
    private static final String COLUMN_DEVICE = "device"; //


    SQLiteDatabase db;

    private static final String TABLE_CREATE = "CREATE TABLE "+ TABLE_NAME +" (ID INTEGER PRIMARY KEY NOT NULL , " +
    " NAME TEXT NOT NULL, EMAIL TEXT NOT NULL, UNAME TEXT NOT NULL, PASS TEXT NOT NULL, DATE_TIME TEXT NOT NULL, IP TEXT NOT NULL, DEVICE TEXT NOT NULL, USER_ID INTEGER FOREIGN KEY REFERENCES user_log )";


    private static final String TABLE_CREATE_USER_LOG = " CREATE TABLE " + USER_LOG_TABLE_NAME +"(USER_ID INTEGER PRIMARY KEY, " +
            "DATE_TIME NOT NULL, IP NOT NULL, DEVICE NOT NULL)"; //


    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        if(db.isReadOnly()){
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
    }

    public DatabaseHelper(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        Log.e("DATABASE OPERATIONS", "DATABASE CREATED" );
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(TABLE_CREATE);
        db.execSQL(TABLE_CREATE_USER_LOG);

        this.db = db;


    }
    public void insertContact(Contact c){
        db = this.getWritableDatabase();
        ContentValues values = new ContentValues();


        String query = " SELECT contacts.id, contacts.uname, user_log.user_id, user_log.date_time, user_log.ip, user_log.device FROM contacts INNER JOIN user_log ON contcts.user_id= user_log.user_id";
        Log.e("DATABASE OPERATIONS", "Query runs" );
        Cursor cursor = db.rawQuery(query, null );

        int count = cursor.getCount();




        values.put(COLUMN_ID, count);
        values.put(COLUMN_NAME , c.getName());
        values.put(COLUMN_EMAIL , c.getEmail());
        values.put(COLUMN_UNAME, c.getUname());
        values.put(COLUMN_PASS, c.getPass());
        values.put(COLUMN_DATE_TIME, c.getDate_time());//
        values.put(COLUMN_IP, c.getIp());//
        values.put(COLUMN_DEVICE, c.getDevice());//
        long res = db.insert(TABLE_NAME, null, values);
        Log.e("Insert","Value of result "+res);
        db.close();

    }

    public String searchPass(String uname) {
        db = this.getReadableDatabase();
        String query = "SELECT UNAME, PASS FROM CONTACTS";
        Cursor cursor = db.rawQuery(query, null);
        String a, b; //a=username and b=password
        b = "not found";

        if (cursor.moveToFirst()) {
            do {
                a = cursor.getString(0);
                if (a.equals(uname)) {
                    b = cursor.getString(1);
                    break;
                }
            }
            while (cursor.moveToNext());
        }
        return b;
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        String query = "DROP TABLE IF EXISTS" + TABLE_NAME;
        db.execSQL(query);
        this.onCreate(db);

    }
}

Aucun commentaire:

Enregistrer un commentaire