Sql Query to retrieve a particular data from particular column and row in android?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I want to fetch phone number linked to particular email in the database. I am not able to find the query for it or how
public String getContactNumber(String email){
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
//What to put here to extract the data.
String contact = cursor.getString(get);
cursor.close();
return contact;
}
to extract the data. Completely a beginner
android sql sqlite
add a comment |
I want to fetch phone number linked to particular email in the database. I am not able to find the query for it or how
public String getContactNumber(String email){
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
//What to put here to extract the data.
String contact = cursor.getString(get);
cursor.close();
return contact;
}
to extract the data. Completely a beginner
android sql sqlite
just pass some SQLite tutorial for Android. Don't be lazy, it should not take much time, but will elliminate most part of problems.
– Vladyslav Matviienko
Nov 22 '18 at 6:04
add a comment |
I want to fetch phone number linked to particular email in the database. I am not able to find the query for it or how
public String getContactNumber(String email){
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
//What to put here to extract the data.
String contact = cursor.getString(get);
cursor.close();
return contact;
}
to extract the data. Completely a beginner
android sql sqlite
I want to fetch phone number linked to particular email in the database. I am not able to find the query for it or how
public String getContactNumber(String email){
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
//What to put here to extract the data.
String contact = cursor.getString(get);
cursor.close();
return contact;
}
to extract the data. Completely a beginner
android sql sqlite
android sql sqlite
edited Nov 22 '18 at 7:07
MikeT
18.3k112844
18.3k112844
asked Nov 22 '18 at 6:00
Priyanshu VayaPriyanshu Vaya
15
15
just pass some SQLite tutorial for Android. Don't be lazy, it should not take much time, but will elliminate most part of problems.
– Vladyslav Matviienko
Nov 22 '18 at 6:04
add a comment |
just pass some SQLite tutorial for Android. Don't be lazy, it should not take much time, but will elliminate most part of problems.
– Vladyslav Matviienko
Nov 22 '18 at 6:04
just pass some SQLite tutorial for Android. Don't be lazy, it should not take much time, but will elliminate most part of problems.
– Vladyslav Matviienko
Nov 22 '18 at 6:04
just pass some SQLite tutorial for Android. Don't be lazy, it should not take much time, but will elliminate most part of problems.
– Vladyslav Matviienko
Nov 22 '18 at 6:04
add a comment |
3 Answers
3
active
oldest
votes
Try this ..
public List<String> getMyItemsD(String emailData) {
List<String> stringList = new ArrayList<>();
SQLiteDatabase db = this.getReadableDatabase();
String selectQuery = "SELECT COLUMN_USER_MOBILE_NUMBER FROM " + USER_TABLE_NAME + " WHERE email= " + emailData;
Cursor c = db.rawQuery(selectQuery, null);
if (c != null) {
c.moveToFirst();
while (c.isAfterLast() == false) {
String name = (c.getString(c.getColumnIndex("Item_Name")));
stringList.add(name);
c.moveToNext();
}
}
return stringList;
}
Will this give a particular value as I am confused as it will provide value stored in List and then how to pass those value in another function.
– Priyanshu Vaya
Nov 22 '18 at 6:30
1. Checking a Cursor returned from an SQLiteDatabase method for null is useless, it will not be null. 2. traversing a Cursor does not need to be so complicated you can simply utilise the return code from moveToNext usingwhile (c.moveToNext()) { stringList.add(c.getString(c.getColumnIndex("Item_Name")))}
. 3. You should ALWAYS close a Cursor when done with it, so beforereturn stringList
you should havec.close();
– MikeT
Nov 22 '18 at 6:58
add a comment |
public String getContactNumber(String email){
String contact = "";
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
if(cursor.getCount()>0) {
cursor.moveToNext();
contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
//What to put here to extract the data.
cursor.close();
return contact;
}
From this method you get phone number value of that email which you pass any other method easily.
if (cursor.moveToFirst()) { contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));}
would be simpler as most of the moveTo???? methods return true if the move were made else false if the move could not be made.
– MikeT
Nov 22 '18 at 7:03
add a comment |
I'd suggest the following :-
public String getContactNumber(String email){
String contact = "NO CONTACT FOUND"; //<<<<<<<<<< Default in case no row is found.
SQLiteDatabase db = this.getWritableDatabase(); //<<<<<<<<<< Generally getReadable gets a writable database
String columns_to_get = new String{COLUMN_USER_MOBILE_NUMBER};
String whereclause = COLUMN_USER_EMAIL + "=?";
String whereargs = new String{email};
Cursor cursor = db.query(TABLE_USER,columns_to_get,whereclause,whereargs,null,null,null);
//What to put here to extract the data.
if (cursor.moveToFirst()) {
contact = csr.getString(csr.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
cursor.close();
return contact;
}
- The above does assumes that there will only be 1 row per email (which is most likely).
Explanations
A default value is set so that you can easily tell if an invalid/non-existent email is passed (you'd check the return value if need be (might be easier to simply have "" and check the length as a check)).
getReadableDatabase has been replaced with getWritableDatabase as unless there are issues with the database a writable database will be returned, as per :-
Create and/or open a database. This will be the same object returned
by getWritableDatabase() unless some problem, such as a full disk,
requires the database to be opened read-only. In that case, a
read-only database object will be returned. If the problem is fixed, a
future call to getWritableDatabase() may succeed, in which case the
read-only database object will be closed and the read/write object
will be returned in the future.
getReadableDatabase
- Note no real problem either way;
The recommended query method has been used instead of the rawQuery method. This has distinct advantages, it builds the underlying SQL and also offers protection against SQL injection (just in case the email passed is input by a user).
- this version of the method takes 7 parameters :-
- The table name as a string
- The columns to be extracted as an array of Strings (aka String array). null can be all columns.
- The where clause less the WHERE keyword with ?'s to represent arguments (see next). null if no WHERE clause.
- The arguments to be applied (replace ?'s 1 for 1) as a String array. null if none or no WHERE clause.
- The GROUP BY clause, less the GROUP BY keywords. null if no GROUP BY clause.
- The HAVING clause, less the HAVING keyword. null if no HAVING clause.
- The ORDER BY clause, less the ORDER BY keywords. null if no ORDER BY clause.
SQLiteDatabase - query
- Note there are 4 query methods (see link for the subtle difference, I believe this is the most commonly used)
The data extraction is the new code. When a Cursor is returned it is at a position BEFORE THE FIRST ROW, so you need to move to a valid row. So the moveToFirst* method is suitable (note that if a move cannot be made by a move method that it will return false, hence how you can say if (cursor.moveToFirst())
). The data is then extracted from the appropriate column use the **getString method, which takes an int as an argumnet for the column offset (0 in this case). However, using hard coded values can lead to issues so the getColumnIndex method is used to get the offset according to the column name (-1 is returned if the named column is not in the Cursor).
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424731%2fsql-query-to-retrieve-a-particular-data-from-particular-column-and-row-in-androi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try this ..
public List<String> getMyItemsD(String emailData) {
List<String> stringList = new ArrayList<>();
SQLiteDatabase db = this.getReadableDatabase();
String selectQuery = "SELECT COLUMN_USER_MOBILE_NUMBER FROM " + USER_TABLE_NAME + " WHERE email= " + emailData;
Cursor c = db.rawQuery(selectQuery, null);
if (c != null) {
c.moveToFirst();
while (c.isAfterLast() == false) {
String name = (c.getString(c.getColumnIndex("Item_Name")));
stringList.add(name);
c.moveToNext();
}
}
return stringList;
}
Will this give a particular value as I am confused as it will provide value stored in List and then how to pass those value in another function.
– Priyanshu Vaya
Nov 22 '18 at 6:30
1. Checking a Cursor returned from an SQLiteDatabase method for null is useless, it will not be null. 2. traversing a Cursor does not need to be so complicated you can simply utilise the return code from moveToNext usingwhile (c.moveToNext()) { stringList.add(c.getString(c.getColumnIndex("Item_Name")))}
. 3. You should ALWAYS close a Cursor when done with it, so beforereturn stringList
you should havec.close();
– MikeT
Nov 22 '18 at 6:58
add a comment |
Try this ..
public List<String> getMyItemsD(String emailData) {
List<String> stringList = new ArrayList<>();
SQLiteDatabase db = this.getReadableDatabase();
String selectQuery = "SELECT COLUMN_USER_MOBILE_NUMBER FROM " + USER_TABLE_NAME + " WHERE email= " + emailData;
Cursor c = db.rawQuery(selectQuery, null);
if (c != null) {
c.moveToFirst();
while (c.isAfterLast() == false) {
String name = (c.getString(c.getColumnIndex("Item_Name")));
stringList.add(name);
c.moveToNext();
}
}
return stringList;
}
Will this give a particular value as I am confused as it will provide value stored in List and then how to pass those value in another function.
– Priyanshu Vaya
Nov 22 '18 at 6:30
1. Checking a Cursor returned from an SQLiteDatabase method for null is useless, it will not be null. 2. traversing a Cursor does not need to be so complicated you can simply utilise the return code from moveToNext usingwhile (c.moveToNext()) { stringList.add(c.getString(c.getColumnIndex("Item_Name")))}
. 3. You should ALWAYS close a Cursor when done with it, so beforereturn stringList
you should havec.close();
– MikeT
Nov 22 '18 at 6:58
add a comment |
Try this ..
public List<String> getMyItemsD(String emailData) {
List<String> stringList = new ArrayList<>();
SQLiteDatabase db = this.getReadableDatabase();
String selectQuery = "SELECT COLUMN_USER_MOBILE_NUMBER FROM " + USER_TABLE_NAME + " WHERE email= " + emailData;
Cursor c = db.rawQuery(selectQuery, null);
if (c != null) {
c.moveToFirst();
while (c.isAfterLast() == false) {
String name = (c.getString(c.getColumnIndex("Item_Name")));
stringList.add(name);
c.moveToNext();
}
}
return stringList;
}
Try this ..
public List<String> getMyItemsD(String emailData) {
List<String> stringList = new ArrayList<>();
SQLiteDatabase db = this.getReadableDatabase();
String selectQuery = "SELECT COLUMN_USER_MOBILE_NUMBER FROM " + USER_TABLE_NAME + " WHERE email= " + emailData;
Cursor c = db.rawQuery(selectQuery, null);
if (c != null) {
c.moveToFirst();
while (c.isAfterLast() == false) {
String name = (c.getString(c.getColumnIndex("Item_Name")));
stringList.add(name);
c.moveToNext();
}
}
return stringList;
}
answered Nov 22 '18 at 6:02
Android TeamAndroid Team
8,11311437
8,11311437
Will this give a particular value as I am confused as it will provide value stored in List and then how to pass those value in another function.
– Priyanshu Vaya
Nov 22 '18 at 6:30
1. Checking a Cursor returned from an SQLiteDatabase method for null is useless, it will not be null. 2. traversing a Cursor does not need to be so complicated you can simply utilise the return code from moveToNext usingwhile (c.moveToNext()) { stringList.add(c.getString(c.getColumnIndex("Item_Name")))}
. 3. You should ALWAYS close a Cursor when done with it, so beforereturn stringList
you should havec.close();
– MikeT
Nov 22 '18 at 6:58
add a comment |
Will this give a particular value as I am confused as it will provide value stored in List and then how to pass those value in another function.
– Priyanshu Vaya
Nov 22 '18 at 6:30
1. Checking a Cursor returned from an SQLiteDatabase method for null is useless, it will not be null. 2. traversing a Cursor does not need to be so complicated you can simply utilise the return code from moveToNext usingwhile (c.moveToNext()) { stringList.add(c.getString(c.getColumnIndex("Item_Name")))}
. 3. You should ALWAYS close a Cursor when done with it, so beforereturn stringList
you should havec.close();
– MikeT
Nov 22 '18 at 6:58
Will this give a particular value as I am confused as it will provide value stored in List and then how to pass those value in another function.
– Priyanshu Vaya
Nov 22 '18 at 6:30
Will this give a particular value as I am confused as it will provide value stored in List and then how to pass those value in another function.
– Priyanshu Vaya
Nov 22 '18 at 6:30
1. Checking a Cursor returned from an SQLiteDatabase method for null is useless, it will not be null. 2. traversing a Cursor does not need to be so complicated you can simply utilise the return code from moveToNext using
while (c.moveToNext()) { stringList.add(c.getString(c.getColumnIndex("Item_Name")))}
. 3. You should ALWAYS close a Cursor when done with it, so before return stringList
you should have c.close();
– MikeT
Nov 22 '18 at 6:58
1. Checking a Cursor returned from an SQLiteDatabase method for null is useless, it will not be null. 2. traversing a Cursor does not need to be so complicated you can simply utilise the return code from moveToNext using
while (c.moveToNext()) { stringList.add(c.getString(c.getColumnIndex("Item_Name")))}
. 3. You should ALWAYS close a Cursor when done with it, so before return stringList
you should have c.close();
– MikeT
Nov 22 '18 at 6:58
add a comment |
public String getContactNumber(String email){
String contact = "";
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
if(cursor.getCount()>0) {
cursor.moveToNext();
contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
//What to put here to extract the data.
cursor.close();
return contact;
}
From this method you get phone number value of that email which you pass any other method easily.
if (cursor.moveToFirst()) { contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));}
would be simpler as most of the moveTo???? methods return true if the move were made else false if the move could not be made.
– MikeT
Nov 22 '18 at 7:03
add a comment |
public String getContactNumber(String email){
String contact = "";
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
if(cursor.getCount()>0) {
cursor.moveToNext();
contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
//What to put here to extract the data.
cursor.close();
return contact;
}
From this method you get phone number value of that email which you pass any other method easily.
if (cursor.moveToFirst()) { contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));}
would be simpler as most of the moveTo???? methods return true if the move were made else false if the move could not be made.
– MikeT
Nov 22 '18 at 7:03
add a comment |
public String getContactNumber(String email){
String contact = "";
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
if(cursor.getCount()>0) {
cursor.moveToNext();
contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
//What to put here to extract the data.
cursor.close();
return contact;
}
From this method you get phone number value of that email which you pass any other method easily.
public String getContactNumber(String email){
String contact = "";
SQLiteDatabase db = this.getReadableDatabase();
String query = "SELECT " + COLUMN_USER_MOBILE_NUMBER + " FROM " + TABLE_USER + " WHERE " + email + " = " + COLUMN_USER_EMAIL;
Cursor cursor = db.rawQuery(query,null);
if(cursor.getCount()>0) {
cursor.moveToNext();
contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
//What to put here to extract the data.
cursor.close();
return contact;
}
From this method you get phone number value of that email which you pass any other method easily.
edited Nov 22 '18 at 7:05
MikeT
18.3k112844
18.3k112844
answered Nov 22 '18 at 6:51
ankit028ankit028
11
11
if (cursor.moveToFirst()) { contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));}
would be simpler as most of the moveTo???? methods return true if the move were made else false if the move could not be made.
– MikeT
Nov 22 '18 at 7:03
add a comment |
if (cursor.moveToFirst()) { contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));}
would be simpler as most of the moveTo???? methods return true if the move were made else false if the move could not be made.
– MikeT
Nov 22 '18 at 7:03
if (cursor.moveToFirst()) { contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));}
would be simpler as most of the moveTo???? methods return true if the move were made else false if the move could not be made.– MikeT
Nov 22 '18 at 7:03
if (cursor.moveToFirst()) { contact = cursor.getString(cursor.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));}
would be simpler as most of the moveTo???? methods return true if the move were made else false if the move could not be made.– MikeT
Nov 22 '18 at 7:03
add a comment |
I'd suggest the following :-
public String getContactNumber(String email){
String contact = "NO CONTACT FOUND"; //<<<<<<<<<< Default in case no row is found.
SQLiteDatabase db = this.getWritableDatabase(); //<<<<<<<<<< Generally getReadable gets a writable database
String columns_to_get = new String{COLUMN_USER_MOBILE_NUMBER};
String whereclause = COLUMN_USER_EMAIL + "=?";
String whereargs = new String{email};
Cursor cursor = db.query(TABLE_USER,columns_to_get,whereclause,whereargs,null,null,null);
//What to put here to extract the data.
if (cursor.moveToFirst()) {
contact = csr.getString(csr.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
cursor.close();
return contact;
}
- The above does assumes that there will only be 1 row per email (which is most likely).
Explanations
A default value is set so that you can easily tell if an invalid/non-existent email is passed (you'd check the return value if need be (might be easier to simply have "" and check the length as a check)).
getReadableDatabase has been replaced with getWritableDatabase as unless there are issues with the database a writable database will be returned, as per :-
Create and/or open a database. This will be the same object returned
by getWritableDatabase() unless some problem, such as a full disk,
requires the database to be opened read-only. In that case, a
read-only database object will be returned. If the problem is fixed, a
future call to getWritableDatabase() may succeed, in which case the
read-only database object will be closed and the read/write object
will be returned in the future.
getReadableDatabase
- Note no real problem either way;
The recommended query method has been used instead of the rawQuery method. This has distinct advantages, it builds the underlying SQL and also offers protection against SQL injection (just in case the email passed is input by a user).
- this version of the method takes 7 parameters :-
- The table name as a string
- The columns to be extracted as an array of Strings (aka String array). null can be all columns.
- The where clause less the WHERE keyword with ?'s to represent arguments (see next). null if no WHERE clause.
- The arguments to be applied (replace ?'s 1 for 1) as a String array. null if none or no WHERE clause.
- The GROUP BY clause, less the GROUP BY keywords. null if no GROUP BY clause.
- The HAVING clause, less the HAVING keyword. null if no HAVING clause.
- The ORDER BY clause, less the ORDER BY keywords. null if no ORDER BY clause.
SQLiteDatabase - query
- Note there are 4 query methods (see link for the subtle difference, I believe this is the most commonly used)
The data extraction is the new code. When a Cursor is returned it is at a position BEFORE THE FIRST ROW, so you need to move to a valid row. So the moveToFirst* method is suitable (note that if a move cannot be made by a move method that it will return false, hence how you can say if (cursor.moveToFirst())
). The data is then extracted from the appropriate column use the **getString method, which takes an int as an argumnet for the column offset (0 in this case). However, using hard coded values can lead to issues so the getColumnIndex method is used to get the offset according to the column name (-1 is returned if the named column is not in the Cursor).
add a comment |
I'd suggest the following :-
public String getContactNumber(String email){
String contact = "NO CONTACT FOUND"; //<<<<<<<<<< Default in case no row is found.
SQLiteDatabase db = this.getWritableDatabase(); //<<<<<<<<<< Generally getReadable gets a writable database
String columns_to_get = new String{COLUMN_USER_MOBILE_NUMBER};
String whereclause = COLUMN_USER_EMAIL + "=?";
String whereargs = new String{email};
Cursor cursor = db.query(TABLE_USER,columns_to_get,whereclause,whereargs,null,null,null);
//What to put here to extract the data.
if (cursor.moveToFirst()) {
contact = csr.getString(csr.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
cursor.close();
return contact;
}
- The above does assumes that there will only be 1 row per email (which is most likely).
Explanations
A default value is set so that you can easily tell if an invalid/non-existent email is passed (you'd check the return value if need be (might be easier to simply have "" and check the length as a check)).
getReadableDatabase has been replaced with getWritableDatabase as unless there are issues with the database a writable database will be returned, as per :-
Create and/or open a database. This will be the same object returned
by getWritableDatabase() unless some problem, such as a full disk,
requires the database to be opened read-only. In that case, a
read-only database object will be returned. If the problem is fixed, a
future call to getWritableDatabase() may succeed, in which case the
read-only database object will be closed and the read/write object
will be returned in the future.
getReadableDatabase
- Note no real problem either way;
The recommended query method has been used instead of the rawQuery method. This has distinct advantages, it builds the underlying SQL and also offers protection against SQL injection (just in case the email passed is input by a user).
- this version of the method takes 7 parameters :-
- The table name as a string
- The columns to be extracted as an array of Strings (aka String array). null can be all columns.
- The where clause less the WHERE keyword with ?'s to represent arguments (see next). null if no WHERE clause.
- The arguments to be applied (replace ?'s 1 for 1) as a String array. null if none or no WHERE clause.
- The GROUP BY clause, less the GROUP BY keywords. null if no GROUP BY clause.
- The HAVING clause, less the HAVING keyword. null if no HAVING clause.
- The ORDER BY clause, less the ORDER BY keywords. null if no ORDER BY clause.
SQLiteDatabase - query
- Note there are 4 query methods (see link for the subtle difference, I believe this is the most commonly used)
The data extraction is the new code. When a Cursor is returned it is at a position BEFORE THE FIRST ROW, so you need to move to a valid row. So the moveToFirst* method is suitable (note that if a move cannot be made by a move method that it will return false, hence how you can say if (cursor.moveToFirst())
). The data is then extracted from the appropriate column use the **getString method, which takes an int as an argumnet for the column offset (0 in this case). However, using hard coded values can lead to issues so the getColumnIndex method is used to get the offset according to the column name (-1 is returned if the named column is not in the Cursor).
add a comment |
I'd suggest the following :-
public String getContactNumber(String email){
String contact = "NO CONTACT FOUND"; //<<<<<<<<<< Default in case no row is found.
SQLiteDatabase db = this.getWritableDatabase(); //<<<<<<<<<< Generally getReadable gets a writable database
String columns_to_get = new String{COLUMN_USER_MOBILE_NUMBER};
String whereclause = COLUMN_USER_EMAIL + "=?";
String whereargs = new String{email};
Cursor cursor = db.query(TABLE_USER,columns_to_get,whereclause,whereargs,null,null,null);
//What to put here to extract the data.
if (cursor.moveToFirst()) {
contact = csr.getString(csr.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
cursor.close();
return contact;
}
- The above does assumes that there will only be 1 row per email (which is most likely).
Explanations
A default value is set so that you can easily tell if an invalid/non-existent email is passed (you'd check the return value if need be (might be easier to simply have "" and check the length as a check)).
getReadableDatabase has been replaced with getWritableDatabase as unless there are issues with the database a writable database will be returned, as per :-
Create and/or open a database. This will be the same object returned
by getWritableDatabase() unless some problem, such as a full disk,
requires the database to be opened read-only. In that case, a
read-only database object will be returned. If the problem is fixed, a
future call to getWritableDatabase() may succeed, in which case the
read-only database object will be closed and the read/write object
will be returned in the future.
getReadableDatabase
- Note no real problem either way;
The recommended query method has been used instead of the rawQuery method. This has distinct advantages, it builds the underlying SQL and also offers protection against SQL injection (just in case the email passed is input by a user).
- this version of the method takes 7 parameters :-
- The table name as a string
- The columns to be extracted as an array of Strings (aka String array). null can be all columns.
- The where clause less the WHERE keyword with ?'s to represent arguments (see next). null if no WHERE clause.
- The arguments to be applied (replace ?'s 1 for 1) as a String array. null if none or no WHERE clause.
- The GROUP BY clause, less the GROUP BY keywords. null if no GROUP BY clause.
- The HAVING clause, less the HAVING keyword. null if no HAVING clause.
- The ORDER BY clause, less the ORDER BY keywords. null if no ORDER BY clause.
SQLiteDatabase - query
- Note there are 4 query methods (see link for the subtle difference, I believe this is the most commonly used)
The data extraction is the new code. When a Cursor is returned it is at a position BEFORE THE FIRST ROW, so you need to move to a valid row. So the moveToFirst* method is suitable (note that if a move cannot be made by a move method that it will return false, hence how you can say if (cursor.moveToFirst())
). The data is then extracted from the appropriate column use the **getString method, which takes an int as an argumnet for the column offset (0 in this case). However, using hard coded values can lead to issues so the getColumnIndex method is used to get the offset according to the column name (-1 is returned if the named column is not in the Cursor).
I'd suggest the following :-
public String getContactNumber(String email){
String contact = "NO CONTACT FOUND"; //<<<<<<<<<< Default in case no row is found.
SQLiteDatabase db = this.getWritableDatabase(); //<<<<<<<<<< Generally getReadable gets a writable database
String columns_to_get = new String{COLUMN_USER_MOBILE_NUMBER};
String whereclause = COLUMN_USER_EMAIL + "=?";
String whereargs = new String{email};
Cursor cursor = db.query(TABLE_USER,columns_to_get,whereclause,whereargs,null,null,null);
//What to put here to extract the data.
if (cursor.moveToFirst()) {
contact = csr.getString(csr.getColumnIndex(COLUMN_USER_MOBILE_NUMBER));
}
cursor.close();
return contact;
}
- The above does assumes that there will only be 1 row per email (which is most likely).
Explanations
A default value is set so that you can easily tell if an invalid/non-existent email is passed (you'd check the return value if need be (might be easier to simply have "" and check the length as a check)).
getReadableDatabase has been replaced with getWritableDatabase as unless there are issues with the database a writable database will be returned, as per :-
Create and/or open a database. This will be the same object returned
by getWritableDatabase() unless some problem, such as a full disk,
requires the database to be opened read-only. In that case, a
read-only database object will be returned. If the problem is fixed, a
future call to getWritableDatabase() may succeed, in which case the
read-only database object will be closed and the read/write object
will be returned in the future.
getReadableDatabase
- Note no real problem either way;
The recommended query method has been used instead of the rawQuery method. This has distinct advantages, it builds the underlying SQL and also offers protection against SQL injection (just in case the email passed is input by a user).
- this version of the method takes 7 parameters :-
- The table name as a string
- The columns to be extracted as an array of Strings (aka String array). null can be all columns.
- The where clause less the WHERE keyword with ?'s to represent arguments (see next). null if no WHERE clause.
- The arguments to be applied (replace ?'s 1 for 1) as a String array. null if none or no WHERE clause.
- The GROUP BY clause, less the GROUP BY keywords. null if no GROUP BY clause.
- The HAVING clause, less the HAVING keyword. null if no HAVING clause.
- The ORDER BY clause, less the ORDER BY keywords. null if no ORDER BY clause.
SQLiteDatabase - query
- Note there are 4 query methods (see link for the subtle difference, I believe this is the most commonly used)
The data extraction is the new code. When a Cursor is returned it is at a position BEFORE THE FIRST ROW, so you need to move to a valid row. So the moveToFirst* method is suitable (note that if a move cannot be made by a move method that it will return false, hence how you can say if (cursor.moveToFirst())
). The data is then extracted from the appropriate column use the **getString method, which takes an int as an argumnet for the column offset (0 in this case). However, using hard coded values can lead to issues so the getColumnIndex method is used to get the offset according to the column name (-1 is returned if the named column is not in the Cursor).
answered Nov 22 '18 at 7:40
MikeTMikeT
18.3k112844
18.3k112844
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424731%2fsql-query-to-retrieve-a-particular-data-from-particular-column-and-row-in-androi%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
just pass some SQLite tutorial for Android. Don't be lazy, it should not take much time, but will elliminate most part of problems.
– Vladyslav Matviienko
Nov 22 '18 at 6:04