B4J Code Snippet [Project Template] jRDC2 Server (SQLite, MySQL, MS SQL, Firebird, Postgresql, DBF)

## SQLite configuration:
DriverClass=com.sqlite.JdbcUrl
JdbcUrl=jdbc:sqlite:C:\\Users\\aeric\\AppData\\Roaming\\JRDC\\test.db

'Class module
Sub Class_Globals
    Private pool As ConnectionPool
    Private DebugQueries As Boolean
    Private commands As Map
    Private DBDir As String
    Private DBName As String = "test.db"
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize
    pool.Initialize(Main.config.Get("DriverClass"), _
    Main.config.Get("JdbcUrl"), _
    Main.config.Get("User"), _
    Main.config.Get("Password"))
    #if DEBUG
    DebugQueries = True
    #else
    DebugQueries = False
    #end if
    LoadSQLCommands(Main.config)
    CheckDatabase
End Sub

Public Sub GetCommand (Key As String) As String
    If commands.ContainsKey("SQL." & Key) = False Then
        Log("*** Command not found: " & Key)
    End If
    Return commands.Get("SQL." & Key)
End Sub

Public Sub GetConnection As SQL
    If DebugQueries Then LoadSQLCommands(Main.config)
    If Main.config.Get("DriverClass").As(String).Contains("sqlite") Then
        Dim sql As SQL
        'Dim jdbc() As String = Regex.Split(":", Main.config.Get("JdbcUrl"))
        'If jdbc.Length > 2 Then sql.InitializeSQLite(File.DirApp, jdbc(2), False)
        sql.InitializeSQLite(DBDir, DBName, False)
        Return sql
    Else
        Return pool.GetConnection
    End If
End Sub

Private Sub LoadSQLCommands (config As Map)
    Dim newCommands As Map
    newCommands.Initialize
    For Each k As String In config.Keys
        If k.StartsWith("SQL.") Then
            newCommands.Put(k, config.Get(k))
        End If
    Next
    commands = newCommands
End Sub

Private Sub CheckDatabase
    Try
        Dim con As SQL
        Dim DBType As String
        Dim DBFound As Boolean
        Log($"Checking database..."$)
      
        If Main.config.Get("DriverClass").As(String).Contains("sqlite") Then
            DBType = "sqlite"
            'Dim jdbc() As String = Regex.Split(":", Main.config.Get("JdbcUrl"))
            'If jdbc.Length > 2 Then
            '    DBName = jdbc(2)
            '    If File.Exists(File.DirApp, DBName) Then
            '        DBFound = True
            '    End If
            'End If
            Dim JdbcUrl As String = Main.config.Get("JdbcUrl")
            DBDir = JdbcUrl.SubString(12).Replace("\" & DBName, "")
            If File.Exists(DBDir, DBName) Then
                DBFound = True
            End If
        End If
      
        If Main.config.Get("DriverClass").As(String).Contains("mysql") Then
            DBType = "mysql"
            Dim JdbcUrl As String = Main.config.Get("JdbcUrl").As(String).Replace(DBName, "information_schema")
            pool.Initialize(Main.config.Get("DriverClass"), JdbcUrl, Main.config.Get("User"), Main.config.Get("Password"))
            con = GetConnection
            If con.IsInitialized Then
                Dim strSQL As String = GetCommand("CHECK_DATABASE")
                Dim res As ResultSet = con.ExecQuery2(strSQL, Array As String(DBName))
                Do While res.NextRow
                    DBFound = True
                Loop
                res.Close
            End If
        End If
      
        If DBFound Then
            Log("Database found!")
        Else   ' Create database if not exist
            Log("Database not found!")
            Log("Creating database...")
            Select DBType
                Case "sqlite"
                    'con.InitializeSQLite(File.DirApp, DBName, True)
                    con.InitializeSQLite(DBDir, DBName, True)
                    con.ExecNonQuery("PRAGMA journal_mode = wal")
                Case "mysql"
                    ConAddSQLQuery2(con, "CREATE_DATABASE", "{DBNAME}", DBName)
                    ConAddSQLQuery2(con, "USE_DATABASE", "{DBNAME}", DBName)
            End Select
      
            ConAddSQLQuery(con, "CREATE_TABLE_TBL_CATEGORY")
            ConAddSQLQuery(con, "INSERT_DUMMY_TBL_CATEGORY")
            ConAddSQLQuery(con, "CREATE_TABLE_TBL_PRODUCTS")
            ConAddSQLQuery(con, "INSERT_DUMMY_TBL_PRODUCTS")
            Dim CreateDB As Object = con.ExecNonQueryBatch("SQL")
            Wait For (CreateDB) SQL_NonQueryComplete (Success As Boolean)
            If Success Then
                Log("Database is created successfully!")
            Else
                Log("Database creation failed!")
            End If
        End If
        CloseDB(con)
    Catch
        LogError(LastException)
        CloseDB(con)
        Log("Error creating database!")
        Log("Application is terminated.")
        ExitApplication
    End Try
End Sub

Public Sub CloseDB (con As SQL)
    If con <> Null And con.IsInitialized Then con.Close
End Sub

Private Sub ConAddSQLQuery (Comm As SQL, Key As String)
    Dim strSQL As String = GetCommand(Key)
    If strSQL <> "" Then Comm.AddNonQueryToBatch(strSQL, Null)
End Sub

Private Sub ConAddSQLQuery2 (Comm As SQL, Key As String, Val1 As String, Val2 As String)
    Dim strSQL As String = GetCommand(Key).As(String).Replace(Val1, Val2)
    If strSQL <> "" Then Comm.AddNonQueryToBatch(strSQL, Null)
End Sub