Getting specific data by column using sqlite database

Question

I couldn't give a good title to the problem I'm having, but basically I have two columns Name and MaxNum string and int respectively! I need int method that makes a query which retrieves the MaxNum for specific Name that I give in parameter!

.. so if I have this data

ID | Name | MaxNum

0 | Mike | 50

1 | John | 40

2 | Jess | 30

..when I put Jess in as parameter it will return 30 !

My method so far.. but I can't use it since it doesn't return int value !

public void  maxFromName(String name){

  SQLiteDatabase db = this.getWritableDatabase();
  String query = "SELECT " + COLUMN_MAX + " FROM "+ TABLE_AZKAR+" WHERE " +COLUMN_NAME+ "=" + name ;
  db.execSQL(query); 
}

well, I can't just return db.execSQL(query); it's void !? can u give me an answer so that I can try it..!? — Alaa AbuZarifa
– Alaa AbuZarifa, Commented May 30, 2017 at 7:41
Instead of using raw SQLite, I'd suggest you go for ORM like GreenDao. A little difficult to begin with, but it'll make your life much much simpler once you get used to it. Also, it makes db handling easy when your application starts to get more complex. See link for details. — Kunal Chawla
– Kunal Chawla, Commented May 30, 2017 at 7:46

Amy · Accepted Answer · 2017-05-30 08:07:03Z

4

You have to get the result using Cursor

String query = "SELECT " + COLUMN_MAX + " FROM "+ TABLE_AZKAR+" WHERE " +COLUMN_NAME+ "=" + name ;
Cursor c = db.rawQuery(query, null);
c.moveToFirst();   
return c.getInt(c.getColumnIndex("MaxNum"));

Update

Since you are facing the problem, I suggest you use the below query:

String query = Select MaxNum from azkar WHERE Name ='Jess';

after this use my code from Cursor c line, this will work.

edited May 30, 2017 at 8:07

Amy

4,0301 gold badge22 silver badges34 bronze badges

answered May 30, 2017 at 7:42

Neeraj Jain

7,7308 gold badges36 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Alaa AbuZarifa Over a year ago

gives me this error > android.database.sqlite.SQLiteException: near "Table": syntax error (code 1): , while compiling: SELECT * FROM Table

Neeraj Jain Over a year ago

@SamZar Try now!

Scary Wombat Over a year ago

why not c.getInt

M. Haverbier Over a year ago

Look at Amys query. There are inverted commas around name.

M. Haverbier Over a year ago

@NeerajJain The column iin the question s called 'Name' not ''name_col'.

|

Amy · Accepted Answer · 2017-05-30 07:57:24Z

3

Use rawQuery for getting data from SQLite. And from the cursor get integer value using getInt function.

Change your maxFromName function with the following.

public int maxFromName(String name){
        SQLiteDatabase db = this.getReadableDatabase();
        String query = "SELECT " + COLUMN_MAX + " FROM "+ TABLE_AZKAR+" WHERE " +COLUMN_NAME+ "= '" + name +"'" ;
        Cursor c = db.rawQuery(query, null);
        if(c.getCount()!=0){
            c.moveToFirst();
            return c.getInt(c.getColumnIndex("MaxNum"))
          }
    return 0;
}

edited May 30, 2017 at 7:57

answered May 30, 2017 at 7:44

Amy

4,0301 gold badge22 silver badges34 bronze badges

7 Comments

Ali Ahsan Over a year ago

what the selectQuery?

Amy Over a year ago

@AliAhsan sorry for mistake in the race forgotten to change the variable name

Ali Ahsan Over a year ago

hehehe it happens :)

Alaa AbuZarifa Over a year ago

same error as @Neeraj method >

android.database.sqlite.SQLiteException: no such column: Jess (code 1): , while compiling: SELECT _max as maxValue FROM azkar WHERE name_col=Jess

Amy Over a year ago

I have update the query copy and paste it into your project

|

Headline · Accepted Answer · 2017-05-30 07:45:16Z

2

In the API it says the following in the description of execSQL():

Execute a single SQL statement that is NOT a SELECT or any other SQL statement that returns data.

Use a query method that returns a Cursor type in order to retrieve results

answered May 30, 2017 at 7:45

Headline

1,5671 gold badge10 silver badges13 bronze badges

Comments

CL. · Accepted Answer · 2017-05-30 11:14:23Z

2

The easiest way to get a single value from a database is with the stringForQuery() or longForQuery() helper functions:

public long maxFromName(String name) {
    SQLiteDatabase db = this.getWritableDatabase();
    String query = "SELECT "+COLUMN_MAX+" FROM "+TABLE_AZKAR+" WHERE "+COLUMN_NAME+" = ?";
    return DatabaseUtils.longForQuery(db, query, new String[]{ name });
}

(To prevent string formatting problems and SQL injection attacks, always use parameters instead of inserting string values directly into the query.)

answered May 30, 2017 at 11:14

CL.

182k18 gold badges241 silver badges282 bronze badges

Comments

Fakhriddin Abdullaev · Accepted Answer · 2017-05-30 08:24:48Z

1

You can use rawQuery

public ArrayList<String> maxFromName(String name){

ArrayList<String> maxNums = new ArrayList<>();

SQLiteDatabase db = this.getWritableDatabase();
String query = "SELECT " + COLUMN_MAX + " FROM "+ TABLE_AZKAR+" WHERE " 
+COLUMN_NAME+ "=" + name ;

Cursor c = db.rawQuery(query, null);
c.moveToFirst();  
do { // if that kind of rows are more then one, this metod get all of them
maxNums.add(c.getInt(c.getColumnIndex("MaxNum")));
}
return maxNums;
}

edited May 30, 2017 at 8:24

answered May 30, 2017 at 8:16

Fakhriddin Abdullaev

4,9902 gold badges44 silver badges42 bronze badges

Collectives™ on Stack Overflow

Getting specific data by column using sqlite database

5 Answers 5

14 Comments

7 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

14 Comments

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related