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