B4A Library SqlObject - a small composer library for sql (mysql)

Many thanks to all of you. I learned a lot from your samples. This is my very first contribution to this forum.

I wrote this composer library to support object oriented programming with sql statements.

This first version has following features:
- support of DQL and DML statements (select, insert, update, delete)
- it handles joins and complex nested conditions

This library does not execute and handle data access. It only composes
sql statements. The advantage of this library is, that you can write complex statements in an object oriented way. Sql commands may be arranged in any order. This is different to common sql string handling.
The library comes along with a fluent design. SqlObject library has a dialect class which generates sql statements. In future releases further dialects could be supported.

I have seen some DB Wrappers. You can use this library with these wrappers easily.


e.g.
in JAVA

SqlSelect s = new SqlSelect();
      s.from("users").column("username");
      s.where(
         SqlObject.Or()
         .add(
            SqlObject.And()
               .add(SqlObject.EQ("is_deleted", 1))
                .add(SqlObject.EQ("is_super", 0))
          )
         .add(SqlObject.EQ("id",1))
      );

in b4a:

Dim stmt As SqlObject
Dim s As SqlSelect

stmt.Select() _
  .column("username") _
  .from("users") _
      .where( stmt.Or() _
      .add(stmt.And() _
         .add(stmt.EQ("is_super", 1))  _
         .add(stmt.Like("is_deleted", 0)) _
      ) _   
      .add(stmt.EQ("id", 1)) _
   )

output:

SELECT username FROM users WHERE (is_super=1 AND is_deleted=0) or (id=1)

 

[scaphare]

Version2

I enhanced the SqlObject by following features:
- adding create table and create index
- adding introspection class
- adding adapter classes:
- console
- adding connection classes: mysql

dependencies:
sqlitejdbc class to connect to sqlite db

      Config.getInstance().register("connection.default", "jdbc:sqlite:data/migli.db");
      Config.getInstance().register("connection.driver", "org.sqlite.JDBC");
      
      SqlSelect s = new SqlSelect();
      s.from("users");
      s.where(SqlObject.EQ("id",1));
      
      try {
         System.out.println(s.toString());
         System.out.println(s.Execute().save(new ConsoleTable()));
         Inspector.inspect(s);
         
         //AdapterInterface a = s.Execute().save(new JasperTable("C:\\pathtoyourjasperreport\\report4.jrxml"));
         //AdapterInterface a = s.Execute().save(new ITextTable("pathtopdfout/.pdf"));
          //System.out.println(a.toString());
         
      } catch (Exception e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
      }

 

[scaphare]

My Eclipse project archive

For all who want to examine the code in eclipse, here is my eclipse project file.

 

[scaphare]

Sample Console Output in Eclipse with insert statement

package anywheresoftware.b4a.tsqlobject;

import java.security.SecureRandom;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import anywheresoftware.b4a.inspector.inspect.*;

import anywheresoftware.b4a.tadapter.*;
import anywheresoftware.b4a.BA.ShortName;

@ShortName("Main")
public class Main {

   public static void main(String[] args) throws Exception {

      Config.getInstance().register("connection.default", "jdbc:sqlite:data/migli.db");
      Config.getInstance().register("connection.driver", "org.sqlite.JDBC");
      
      String str = new BigInteger(130, new SecureRandom()).toString(4);
      String str2 = new BigInteger(130, new SecureRandom()).toString(8);
      
      DateFormat df = new SimpleDateFormat("yyyy-MM-dd H:m:s");
      Date today = Calendar.getInstance().getTime();
      String CreatedAt = df.format(today);
      
      SqlInsert ins = new SqlInsert();
      ins.into("users");
      ins.column("user_name").value("tsc" + str);
      ins.column("user_pass").value("pw"+str2);
      ins.column("is_active").value(1);
      ins.column("is_deleted").value(0);
      ins.column("created_at").value(CreatedAt);
      
      try {      
         System.out.println(ins.Execute());
      } catch(Exception e) {
         e.printStackTrace();         
      }
      
      
      SqlSelect s = new SqlSelect();
      s.from("users");
      
      try {
         System.out.println(s.toString());
         System.out.println(s.Execute().save(new ConsoleTable()));
         
      } catch (Exception e) {
         e.printStackTrace();
      }
       
   }

}

 

[moster67]

Thanks for this.

I am not using SQL or databases in my apps (yet) but it might become handy in future.

More importantly, it is nice to see contributions like yours continuously being developed for the B4A-community and which will make B4A in the future even more complete than today.

Cudos to you also for supplying your sources. Those are very valuable as learning-material for others who would like to write libraries for B4A.