Ario Query Builder - Meraj/ArioSql GitHub Wiki

Ario Query Builder is similar to laravel query builder
In the first step, we need to define ArioQueryBuilder Class

val arioQB = ArioQueryBuilder(context,"my new database name")

and then we need to define our table that we are going to run queries for it

arioQB.table("persons")

List of Options

now let`s explain how it work

1️⃣ get single row

for get a single row use :

val cursor = arioQB.first()  // return cursor
cursor.getString(0) // return column one value
// OR use : 
val row = ArioQueryBuilder.convertCursorToArray(cursor) // automatically generate a array from cursor
row[0][0] // return column one value

first() method return cursor

convert Cursor to Array method

▶️ get multiple rows

for getting multiple rows use :

val cursor = arioQB.get() // return cursor
while(cursor.moveToNext()){
cursor.getString(0) // return column one value
}
// OR use :
val row = ArioQueryBuilder.convertCursorToArray(cursor) // automatically generate a array from cursor
row[0][0] // return first row column one value
row[1][0] // return second row column one value

get() method return cursor

convert Cursor to Array method

🔵 Select

select

for select specific column ,just use select() function

arioQB.select("person_name").first() // for get a single column
arioQB.select(arrayOf("person_name","phone_number")).first() // for get a multiple column
arioQB.select("person_name").addSelect("phone_number").first() // this work too

addSelect is for the time that you write your query and want to add another column/columns \

select with custom raw

Sometimes you may need to insert an arbitrary string into a query. To create a raw string expression ,use :

arioQB.selectRaw("count(*) as persons_count, person_name").first()
arioQB.selectRaw("id * ? as new_id",arrayOf("55")).first() // binding params for prevent sql injection
// Or 
arioQB.select("person_name").addSelectRaw("count(*) as persons_count").first()

🟣 where

where and orWhere

for where statements you can use where function

arioQB.where("person_name","jafar").first()
arioQB.where("person_name","jafar").where("id","5").get() // where person_name = jafar AND id = 5

OrWhere :

arioQB.where("person_name","jafar").orWhere("id","5").first() // where person_name = jafar OR id = 5

where with operation

using operation in where statements ,for example LIKE and etc

arioQB.where("id","<","5").get()
arioQB.where("person_name","LIKE","%jafar%").get()

also orWhere with operation :

arioQB.where("id","<","5").orWhere("id",">","50").get()
arioQB.where("person_name","LIKE","%ario%").orWhere("person_name","LIKE","%a name%").get()

whereNull/whereNotNull And orWhereNull/orWhereNotNull

maybe you want to search in database and found null columns or maybe you want search for NOT Null columns

arioQB.whereNull("phone_number").get() // search in phone_number column and get the rows that phone_number is null
arioQB.whereNotNull("phone_number").get() // search in phone_number column and get the rows that phone_number is NOT Null

and also :

arioQB.where("id",">","5").orWhereNull("phone_number").get()
arioQB.where("person_name","LIKE","%ario%").orWhereNotNull("phone_number").get()

whereBetween/whereNotBetween and orWhereBetween/orWhereNotBetween

maybe you want to get columns that they values are between 2 number you use :

arioQB.whereBetween("id","3","6").get() // rows that id column is between 3 and 6
arioQB.whereNotBetween ("id","3","6").get() // rows that id column is NOT between 3 and 6

and with OR :

arioQB.where("id",">","5").orWhereNull("phone_number").orWhereBetween("id","3","6").get()
arioQB.where("person_name","LIKE","%ario%").orWhereNotNull("phone_number").orWhereNotBetween("id","3","6").get()

whereRaw and orWhereRaw

maybe you want to execute your custom sql query in where

arioQB.whereRaw("id > 50").get()
arioQB.whereRaw("id > ?",arrayOf("50")).get() // binding params for prevent sql injection

using or :

arioQB.where("person_name","ario").orWhereRaw("id > 50").get()
arioQB.where("person_name","ario").orWhereRaw("id > ?",arrayOf("50")).get() // binding params for prevent sql injection

⚫️ limit

limit rows :

arioQB.limit(5).get() // select 5 rows
arioQB.limit(5,50).get() // select 5 rows and Offset (get 5 rows after 50 rows passed)

limit(limit :Int, offset :Int)

⚪️ orderBy

for sorting rows :

arioQB.orderBy("id").get() // sorting DESCENDING by the "id" column
arioQB.orderBy("id","DESC").get() // sorting DESCENDING by the "id" column
arioQB.orderBy("id","ASC").get() // sorting ascending by the "id" column

custom order by query

arioQB.orderByRaw("phone_number - id DESC").get()

📁 group by

for group :

arioQB.groupBy("column_one").get();
arioQB.groupBy(arrayOf("column_one","column_two")).get();

🟤 count rows

for counting rows use :

arioQB.count()

count rows and return Int

🔹 row exists/doesntExist

check if a row exists or Does NOT exist

arioQB.where("id","5").exists() // return true if exist
arioQB.where("id","5").doesntExist() // return true if DOES NOT exist

exist() => if exist method return True
doesntExist() => if DOES NOT exist return True

🆕 insert

for insert data into table use :

arioQB.insert("phone_number","09120000000") 
arioQB.insert(arrayOf("person_name","phone_number"),arrayOf("ario","09120000000"))

return row unique ID ID successfull, - 1 otherwise

🟦 update row/rows

for update row/rows use :

arioQB.update("phone_number","0")
arioQB.where("id","6").update("person_name","nikita")

arioQB.update(arrayOf("person_name","phone_number"),arrayOf("new name","0013333333"))
arioQB.where("id","6").update(arrayOf("person_name","phone_number"),arrayOf("ario","09120000000"))

return true if successful

🔴 delete row/rows

delete row/rows :

arioQB.delete() // delete all rows
arioQB.where("id","6").delete()
arioQB.whereNull("person_name").delete()

return true if successful

🟨 paginate

pagination system implement paginate in your app and it return ArioPaginate Class wich is a model and have 3 variable

  • totalPages -> Int
  • Rows -> Cursor
  • currentPage -> Int paginate() function in ArioQueryBuilder get 2 parameter
  1. results per page -> Int
  2. current Page -> Int - Nullable (default page = 1)
val pagination = arioQB.paginate(5) // 5 results per page - current page = 1
pagination.totalPages // return total pages as Int
pagination.Rows // return current page rows as Cursor
pagination.currentPage // return currentPage as Int

another example :

val pagination = arioQB.paginate(5,2) // 5 results per page - current page = 2
pagination.totalPages // return total pages as Int
pagination.Rows // return current page rows as Cursor
pagination.currentPage // return currentPage as Int

◻️ run custom query - rawQuery

for running custom query :

arioQB.rawQuery("SELECT * FROM table_name WHERE id = 5")
arioQB.rawQuery("SELECT * FROM table_name WHERE id = ?",arrayOf("5")) // binding params to prevent sql injection

return cursor

🔳 convert Cursor to Array

if you don`t want to make a model and manage cursor manual you convertCursorToArray function
wich convert Cursor to a String Array \

val rows = ArioQueryBuilder.convertCursorToArray(Cursor) // return Array<Array<String>>
rows[0] // first row
rows[0][0] // first row and first column
rows[0][1] // first row and second column
... 

rows[1] // second row
rows[1][0] // second row and first column
rows[1][1] // second row and second column
....

....

return Array

〰️ build and get query as String

if you want to build queries and get theme as string to use your self ,you can do :

arioQB.buildQuery(ArioQueryBuilder.INSERT) // return string
arioQB.buildQuery(ArioQueryBuilder.GET) // return string
arioQB.buildQuery(ArioQueryBuilder.UPDATE) // return string
arioQB.buildQuery(ArioQueryBuilder.DELETE)  // return string

return String

〰️ get last executed Query

arioQB.lastExecutedQuery() // return string

return String

〰️ return db connection

arioQB.connection() // Return current database connection as SQLiteDatabase

return SQLiteDatabase

〰️ close connection to the database

for close current connection to the database

arioQB.close()

〰️ open connection to the database

if you close the connection to the database manually and want to execute SQL queries again you have to reOpen connection to the database
but as default connection to the database is open since to define ArioQueryBuilder Class
and no need to run this function

arioQB.open()
⚠️ **GitHub.com Fallback** ⚠️