[JAVA] Talking about stumbling with SQLite query

Introduction

This stumbled upon my first Android application development and then spelled out a reminder of how it was resolved.

I wanted to see if the thing I wanted to add to the database already existed

While creating an application using SQLite with java, if the item entered in EditText was already registered in the database, I tried to display an alert and not add it, but I could not do it well.

What I went to first

example.java

String item = editText.getText().toString();
Cursor c = db.query("table", new String[]{"item", "yen"}, "item = " + item, null, null, null, null);

This was the method described on some of the various sites I looked up. When I actually tried it ...

android.database.sqlite.SQLiteException: no such column:Apple

Error will be displayed.

By the way, the column is ...

The "column" that corresponds to each item. https://26gram.com/database-terms

Challenge with try-catch statement

So I didn't know the column and didn't even try to check it, so I used the try-catch statement to check if it already existed with try. Should I add it to the database with catch? I thought, so I actually tried it with the code.

example.java

String item = editText.getText().toString();
try{
    Cursor c = db.query("table", new String[]{"item", "num"}, "item = " + item, null, null, null, null);
    /*Processing when existing*/
}catch(Exception e){
    /*Process to add to database*/
}

When I try to run this result ...

The same has been added! !! That's funny ... As a result of various investigations, it seems that the way to write " item = "+ item is wrong. (← I noticed the meaning of the column here and blushed safely)

Original search method

example.java

Cursor c = db.query("table", new String[]{"item", "yen"}, "item = ?", new String[]{item}, null, null, null);

It seems that the correct answer is to write. The code I wrote first is more intuitive ... I know how to write it, so let's move on!

... but I stumbled again here. The code is here.

example.java

Cursor c = db.query("table", new String[]{"item", "yen"}, "item = ?", new String[]{item}, null, null, null);
if(c.moveToFirst()){
    /*Processing when existing*/
}else{
    /*Process to add to database*/
}

You can move the cursor to the first position ⇒ You came up with the idea that it already exists. Whether or not it was actually added, the existing process was executed every time because it became "positive" regardless of that.

How to determine if it exists

So how did the program that determines whether it already exist finally solved it?

example.java

String item = editText.getText().toString();
Cursor c = db.query("table", new String[]{"item", "yen",}, "item = ?", new String[]{item}, null, null, null);
if(c.getCount() != 0) {
    /*Processing when existing*/
}else{
    /*Process to add to database*/
}

Since there was a method with a function to count records (rows), I used it and used it. -Existing processing if the number of lines is not 0 -Process to add to the database if there are 0 rows By doing so, I was able to implement the program as desired.

Digression

With " item like? ", New String [] {"% "+ item +"% "}, you can use the SQL lile clause to find the field (element) that contains the word. ..

Finally

That's all for this article. Since this was my first attempt at an Android application, I often stumbled, but I was able to create it safely and install it on my mobile phone. If you have any questions, check them out. Check and utilize the method carefully. I would like to add these two to my motto and continue to challenge Android application development. If you have any advice or questions, please leave them in the comments section. Thank you for staying with us until the end.