cKHzVvVa

1//  SQLLite : Server Query Language
2    //  We are going to Create a database and table inside that and save some data in table and pull data out of the table.
3    @Override
4    protected void onCreate(Bundle savedInstanceState) {
5        super.onCreate(savedInstanceState);
6        setContentView(R.layout.activity_main);
7
8        try {
9            //  Stage 1: Creating our database
10            SQLiteDatabase myDatabase = this.openOrCreateDatabase("Users", MODE_PRIVATE, null);
11            //  This method opens the database in case it exist or create it if it does not exist.
12            //  null is a special error catching method in case we need it.
13
14            //  Stage 2: Putting a table inside the database
15            //  Creating a table if it doesn't exist.
16            myDatabase.execSQL("CREATE TABLE IF NOT EXISTS users (name VARCHAR, age INT(3))");
17
18            //  Stage 3: Inserting some data inside that table
19            myDatabase.execSQL("INSERT INTO users (name, age) VALUES ('Lord', 27)");
20
21            //  Stage 4: Getting data out of the database
22            //  Cursor allows us to loop through all the result of a query ando something with them
23            Cursor c = myDatabase.rawQuery("SELECT * FROM users", null);
24
25            //  We need to get column indexes
26            int nameIndex = c.getColumnIndex("name");
27            int ageIndex = c.getColumnIndex("age");
28
29            //  Looping through the result from the query we created in the Cursor
30            c.moveToFirst();
31            while (c != null) {
32                Log.i("Lord-Name", c.getString(nameIndex));
33                Log.i("Lord-Age", String.valueOf(c.getInt(ageIndex)));
34                c.moveToNext();
35            }
36            
37            //  Advanced Queries
38            Cursor c1 = myDatabase.rawQuery("SELECT * FROM users WHERE age<18", null);
39            Cursor c2 = myDatabase.rawQuery("SELECT * FROM users WHERE name= 'mohammad'", null);
40            Cursor c3 = myDatabase.rawQuery("SELECT * FROM users WHERE age<18 AND name= 'mohammad'", null);
41            Cursor c4 = myDatabase.rawQuery("SELECT * FROM users WHERE name LIKE 'm%'", null);   //search for anyone that name starts with m and anything after that.
42            Cursor c5 = myDatabase.rawQuery("SELECT * FROM users WHERE name LIKE '%m%'", null);   //search for anyone that name has m and anything after and before that.
43            Cursor c6 = myDatabase.rawQuery("SELECT * FROM users WHERE name LIKE '%m%' LIMIT 1", null);   //search for anyone that name has m and anything after and before that. with Limitation.
44
45            // Delete Item
46            myDatabase.execSQL("DELETE FROM users WHERE name='mohammad' LIMIT 1");
47
48            //  Update Item
49            myDatabase.execSQL("UPDATE users SET age =30 WHERE name = 'mohmmmad'");
50
51            //  CREATING A TABLE WITH PRIMARY KEY , primary key starts from 1 and increment by one automatically.
52            myDatabase.execSQL("CREATE TABLE IF NOT EXISTS newUsers (name VARCHAR , age INTEGER(3) , id INTEGER PRIMARY KEY)");
53
54        } catch (Exception e) {
55            Log.i("Lord-Error",e.toString());
56
57        }
58    }