B4J Library Build parametized SQL queries for Insert,Update and Delete

This is a small class to easily build SQL queries

declare an instance of the class clBuildSQL and initialize it

Private fBuildSQL As clBuildSQL
....

fBuildSQL.initialize

Insert record
Just pass as first parameter the table name and as second parameter a map with pairs keys/values FieldName/value

    Dim s As TSQLQuery=fBuildSQL.insertSQL("t_persons",CreateMap("name":"Alan","age":35))
    fDB.ExecNonQuery2(s.fSQL,s.fValues)

the sql will be "insert into t_persons(name,age) values(?,?)" with parameters array as object("Alan",35)


update record
Just pass as first parameter the table name, as second parameter a map with pairs keys/values FieldName/value and as third parameter a map with pairs keys/values FieldName|Operator/value for the where clause.

    Dim s As TSQLQuery=fBuildSQL.UpdateSQL("t_persons",CreateMap("age":30),CreateMap("name=":"Mary"))
    fDB.ExecNonQuery2(s.fSQL,s.fValues)

the sql will be "update t_persons set name=?,,age=? where name=?" with parameters array as object(30,"Mary")

Delete record
Just pass as first parameter the table name, and as second parameter a map with pairs keys/values FieldName|Operator/value for the where clause
In the where clause you can combine more criteria

    Dim s As TSQLQuery=fBuildSQL.deleteSQL("t_persons",CreateMap("name=":"Alan","or age>":32))
    fDB.ExecNonQuery2(s.fSQL,s.fValues)

the sql will be "delete from t_persons where name=? or age>?" with parameters array as object("Alan",32)


Don't use space in your table name or column name!


A B4X sample (B4A and B4J) shows the result

 

[Mashiane]

The issue of the space in table name and field names can be sorted by enclosing them inside [] or ``.

You are perhaps making an assumption that deletes, updates work only with = sign in your class. Perhaps add an additional variable that could be a list that receives the operators like >, >= so that a person can be able to 'delete from abc where xxx > 1' and not only 'delete from abc where xxx = 1'

 

[spsp]

For the spaces, it's only a personal rule: no space in table name and column name, i use underscore (_)
for the update and delete, you are now able to use other operators, see post #1

The issue of the space in table name and field names can be sorted by enclosing them inside [] or ``.

You are perhaps making an assumption that deletes, updates work only with = sign in your class. Perhaps add an additional variable that could be a list that receives the operators like >, >= so that a person can be able to 'delete from abc where xxx > 1' and not only 'delete from abc where xxx = 1'