B4J Code Snippet get col type exactly with dbutils(ResultSetMetaData)

icefairy333

Active Member
Licensed User
dbutils
B4X:
'version 1.20
Sub Process_Globals
    Type dbOptret (success As Boolean,msg As String)
    Public conpool As ConnectionPool
    Private curDbType As Int=0
    Public DB_REAL, DB_INTEGER, DB_BLOB, DB_TEXT As String
    DB_REAL = "REAL"
    DB_INTEGER = "INTEGER"
    DB_BLOB = "BLOB"
    DB_TEXT = "TEXT"
    Dim HtmlCSS As String = "table {width: 100%;border: 1px solid #cef;text-align: left; }" _
        & " th { font-weight: bold;    background-color: #acf;    border-bottom: 1px solid #cef; }" _ 
        & "td,th {    padding: 4px 5px; }" _
        & ".odd {background-color: #def; } .odd td {border-bottom: 1px solid #cef; }" _
        & "a { text-decoration:none; color: #000;}"
End Sub
'dbtype:0=mysql connstr:jdbc:mysql://127.0.0.1:3306/webplus?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull
'1=mssql connstr:jdbc:jtds:sqlserver://localhost:1433/dbname;instance=sqlexpress
'2=oracle connstr:jdbc:oracle:thin:@127.0.0.1:1521:orcl
Public Sub initConnectionPool(dbtype As Int,connectionStr As String,dbUser As String,dbPass As String)
    curDbType=dbtype
    Dim conCls As String=""
    Select dbtype
        Case 0
            conCls="com.mysql.jdbc.Driver"
        Case 1
            conCls="net.sourceforge.jtds.jdbc.Driver"
        Case 2
            conCls="oracle.jdbc.driver.OracleDriver"
    End Select
    conpool.Initialize(conCls,connectionStr,dbUser,dbPass)
End Sub
'dbtype:0=mysql 1=mssql 2=oracle
Public Sub setDbType(dbt As Int)
    curDbType=dbt
End Sub
public Sub EscapeField(f As String) As String
    If curDbType=1 Then
        Return "[" & f & "]"
    Else
        Return "`" & f & "`"
    End If
End Sub
Public Sub ExecuteBaseMap(SQL As SQL, Query As String, StringArgs() As String) As Map
    Dim res As Map
    Dim cur As ResultSet
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    Log("ExecuteMap: " & Query)
    res.Initialize
    '    Log("cnt:"&cur.ColumnCount)
    Do While cur.NextRow
        res.Put(cur.GetString2(0).ToLowerCase,cur.GetString2(1))
    Loop
    cur.Close
    Return res
End Sub
'Creates a new table with the given name.
'FieldsAndTypes - A map with the fields names as keys and the types as values.
'You can use the DB_... constants for the types.
'PrimaryKey - The column that will be the primary key. Pass empty string if not needed.
Public Sub CreateTable(SQL As SQL, TableName As String, FieldsAndTypes As Map, PrimaryKey As String)
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("(")
    For i = 0 To FieldsAndTypes.Size - 1
        Dim field, ftype As String
        field = FieldsAndTypes.GetKeyAt(i)
        ftype = FieldsAndTypes.GetValueAt(i)
        If i > 0 Then sb.Append(", ")
        sb.Append(EscapeField(field)).Append(" ").Append(ftype)
        If field = PrimaryKey Then sb.Append(" PRIMARY KEY")
    Next
    sb.Append(")")
    Dim query As String
    query = "CREATE TABLE IF NOT EXISTS " & EscapeField(TableName) & " " & sb.ToString
    g.mLog("CreateTable: " & query)
    SQL.ExecNonQuery(query)
End Sub

'Deletes the given table.
Public Sub DropTable(SQL As SQL, TableName As String)
    Dim query As String
    query = "DROP TABLE IF EXISTS " & EscapeField(TableName)
    g.mLog("DropTable3: " & query)
    SQL.ExecNonQuery(query)
End Sub

'Inserts the data to the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Public Sub InsertMaps(SQL As SQL, TableName As String, ListOfMaps As List) As dbOptret
    Dim sb, columns, values As StringBuilder
    Dim ret As dbOptret
    ret.Initialize
    'Small check for a common error where the same map is used in a loop
    If ListOfMaps.Size > 1 And ListOfMaps.Get(0) = ListOfMaps.Get(1) Then
        '        g.mLog("Same Map found twice in list. Each item in the list should include a different map object.")
        ret.success=False
        ret.msg="Same Map found twice in list. Each item in the list should include a different map object."
        Return ret
    End If
    SQL.BeginTransaction
    Try
        For i1 = 0 To ListOfMaps.Size - 1
            sb.Initialize
            columns.Initialize
            values.Initialize
            Dim listOfValues As List
            listOfValues.Initialize
            sb.Append("INSERT INTO " & EscapeField(TableName) & " (")
            Dim m As Map
            m = ListOfMaps.Get(i1)
            For i2 = 0 To m.Size - 1
                Dim col As String
                Dim value As Object
                col = m.GetKeyAt(i2)
                value = m.GetValueAt(i2)
                If i2 > 0 Then
                    columns.Append(", ")
                    values.Append(", ")
                End If
                columns.Append(EscapeField(col))
               
                values.Append("?")
                listOfValues.Add(value)
            Next
            sb.Append(columns.ToString)
            sb.Append(") VALUES (")
            sb.Append(values.ToString)
            sb.Append(")")
            If i1 = 0 Then Log("InsertMaps (first query out of " & ListOfMaps.Size & "): " & sb.ToString)
            SQL.ExecNonQuery2(sb.ToString, listOfValues)
        Next
        SQL.TransactionSuccessful
        ret.success=True
        ret.msg="成功"
    Catch
        g.mLog(LastException)
        ret.success=False
        ret.msg=LastException.Message
        SQL.Rollback
    End Try
    Return ret
   
End Sub

'Inserts the data to the table.
'ListOfMaps - A list with maps as items. Each map represents a record where the map keys are the columns names
'and the maps values are the values.
'Note that you should create a new map for each record (this can be done by calling Dim to redim the map).
Public Sub ReplaceMap(SQL As SQL, TableName As String, map As Map)
    Dim sb,  values As StringBuilder
    'Small check for a common error where the same map is used in a loop
    sb.Initialize
'            columns.Initialize
    values.Initialize
    Dim listOfValues As List
    listOfValues.Initialize
    sb.Append("REPLACE INTO " & EscapeField(TableName) & " ")
    Dim m As Map=map
   
    For i2 = 0 To m.Size - 1
        Dim col As String
        Dim value As Object
        col = m.GetKeyAt(i2)
        value = m.GetValueAt(i2)
        If i2 > 0 Then
'                    columns.Append(", ")
            values.Append(", ")
        End If
'                columns.Append(EscapeField(col))
       
        values.Append("?")
        listOfValues.Add(value)
    Next
'            sb.Append(columns.ToString)
    sb.Append(" VALUES (")
    sb.Append(values.ToString)
    sb.Append(")")
    Log("ReplaceMap: " & sb.ToString)
    SQL.ExecNonQuery2(sb.ToString, listOfValues)
End Sub
Public Sub InsertMap(SQL As SQL, TableName As String, m As Map) As dbOptret
    Dim lst As List
    lst.Initialize
    lst.Add(m)
    Return InsertMaps(SQL,TableName,lst)
End Sub
' updates a single field in a record
' Field is the column name
Public Sub UpdateRecord(SQL As SQL, TableName As String, Field As String, NewValue As Object, _
    WhereFieldEquals As Map)
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("UPDATE ").Append(EscapeField(TableName)).Append(" SET ").Append(EscapeField(Field)) _
        .Append(" = ? WHERE ")
    If WhereFieldEquals.Size = 0 Then
        g.mLog("WhereFieldEquals map empty!")
        Return
    End If
    Dim args As List
    args.Initialize
    args.Add(NewValue)
    For i = 0 To WhereFieldEquals.Size - 1
        If i > 0 Then sb.Append(" AND ")
        sb.Append(EscapeField(WhereFieldEquals.GetKeyAt(i))).Append(" = ?")
        args.Add(WhereFieldEquals.GetValueAt(i))
    Next
    g.mLog("UpdateRecord: " & sb.ToString)
    SQL.ExecNonQuery2(sb.ToString, args)
End Sub

' updates multiple fields in a record
' in the Fields map the keys are the column names
Public Sub UpdateRecord2(SQL As SQL, TableName As String, Fields As Map, WhereFieldEquals As Map)
    If WhereFieldEquals.Size = 0 Then
        g.mLog("WhereFieldEquals map empty!")
        Return
    End If
    If Fields.Size = 0 Then
        g.mLog("Fields empty")
        Return
    End If
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("UPDATE ").Append(EscapeField(TableName)).Append(" SET ")
    Dim args As List
    args.Initialize
    For i=0 To Fields.Size-1
        If i<>Fields.Size-1 Then
            sb.Append(EscapeField(Fields.GetKeyAt(i))).Append("=?,")
        Else
            sb.Append(EscapeField(Fields.GetKeyAt(i))).Append("=?")
        End If
        args.Add(Fields.GetValueAt(i))
    Next
   
    sb.Append(" WHERE ")
    For i = 0 To WhereFieldEquals.Size - 1
        If i > 0 Then
            sb.Append(" AND ")
        End If
        sb.Append(EscapeField(WhereFieldEquals.GetKeyAt(i))).Append(" = ?")
        args.Add(WhereFieldEquals.GetValueAt(i))
    Next
    g.mLog("UpdateRecord: " & sb.ToString)
    SQL.ExecNonQuery2(sb.ToString, args)
End Sub

'Executes the query and returns the result as a list of arrays.
'Each item in the list is a strings array.
'StringArgs - Values to replace question marks in the query. Pass Null if not needed.
'Limit - Limits the results. Pass 0 for all results.
Public Sub ExecuteMemoryTable(SQL As SQL, Query As String, StringArgs() As Object, Limit As Int) As List
    Dim cur As ResultSet
'    Log(GetType(StringArgs)&" len:"&StringArgs.Length)
'    Dim params As List
'    params.Initialize
'    For Each v As Object In StringArgs
'        params.Add(v)
'    Next
    If StringArgs == Null Or StringArgs.Length<1 Then
        Dim StringArgs(0) As Object
    End If
    cur = SQL.ExecQuery2(Query, StringArgs)
    g.mLog("ExecuteMemoryTable: " & Query)
    Dim table As List
    table.Initialize
    Dim rsmd As clsResultSetMetaData
    rsmd.Initialize(cur)
    Dim lstTyps As List
    Do While cur.NextRow
        Dim m As Map
        m.Initialize
        If lstTyps.IsInitialized=False Then
            lstTyps.Initialize
            For col = 0 To cur.ColumnCount - 1
                Dim key As String=rsmd.getColumnClassName(col).Trim.ToLowerCase
                key=key.SubString(key.LastIndexOf(".")+1)
                lstTyps.Add(key)
            Next
        End If
        For col = 0 To cur.ColumnCount - 1
            Dim val As Object
            Select lstTyps.Get(col)&""
                Case "integer","boolean"
                    val=cur.GetInt2(col)
                Case "double"
                    val=cur.GetDouble2(col)
                Case "long"
                    val=cur.GetLong2(col)
                Case "byte"
                    val=cur.GetBlob2(col)
                Case Else
                    val=cur.GetString2(col)
            End Select
            m.Put(cur.GetColumnName(col).ToLowerCase,val)
        Next
        table.Add(m)
        If Limit > 0 And table.Size >= Limit Then Exit
    Loop
    cur.Close
    Return table
End Sub

'query list
Public Sub ExecuteMemoryTable2(SQL As SQL, tablename As String,fields As String,WhereFieldEquals As Map, Limit As Int) As List
    Dim cur As ResultSet
    Dim sb As StringBuilder
    sb.Initialize
    Dim args As List
    args.Initialize
    Dim table As List
    table.Initialize
    sb.Append("select "&fields&" from "&EscapeField(tablename)&" where 1=1")
    For i = 0 To WhereFieldEquals.Size - 1
        sb.Append(" AND ")
        sb.Append(EscapeField(WhereFieldEquals.GetKeyAt(i))).Append(" = ?")
        args.Add(WhereFieldEquals.GetValueAt(i))
    Next
    cur = SQL.ExecQuery2(sb.ToString,args)
    g.mLog("ExecuteMemoryTable: " & sb.ToString)
    Dim rsmd As clsResultSetMetaData
    rsmd.Initialize(cur)
    Dim lstTyps As List
    Do While cur.NextRow
        Dim m As Map
        m.Initialize
        If lstTyps.IsInitialized=False Then
            lstTyps.Initialize
            For col = 0 To cur.ColumnCount - 1
                Dim key As String=rsmd.getColumnClassName(col).Trim.ToLowerCase
                key=key.SubString(key.LastIndexOf(".")+1)
                lstTyps.Add(key)
            Next
        End If
        For col = 0 To cur.ColumnCount - 1
            Dim val As Object
            Select lstTyps.Get(col)&""
                Case "integer","boolean"
                    val=cur.GetInt2(col)
                Case "double"
                    val=cur.GetDouble2(col)
                Case "long"
                    val=cur.GetLong2(col)
                Case "byte"
                    val=cur.GetBlob2(col)
                Case Else
                    val=cur.GetString2(col)
            End Select
            m.Put(cur.GetColumnName(col).ToLowerCase,val)
        Next
        table.Add(m)
        If Limit > 0 And table.Size >= Limit Then Exit
    Loop
    cur.Close
    Return table
End Sub
'Executes the query and returns a Map with the column names as the keys 
'and the first record values As the entries values.
'The keys are lower cased.
'Returns an uninitialized map if there are no results.
Public Sub ExecuteMap(SQL As SQL, Query As String, StringArgs() As Object) As Map
    Dim res As Map
    Dim cur As ResultSet
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    res.Initialize
    g.mLog("ExecuteMap: " & Query)
    If cur.NextRow = False Then
        g.mLog("No records found.")
        Return res
    End If
    Dim rsmd As clsResultSetMetaData
    rsmd.Initialize(cur)
    Dim lstTyps As List
    lstTyps.Initialize
    For col = 0 To cur.ColumnCount - 1
        Dim key As String=rsmd.getColumnClassName(col).Trim.ToLowerCase
        key=key.SubString(key.LastIndexOf(".")+1)
        lstTyps.Add(key)
    Next
    g.mLog("cnt:"&cur.ColumnCount)
    For col = 0 To cur.ColumnCount - 1
       
'        Log("typ:"&lstTyps.Get(col)&"")
        Dim val As Object
        Select lstTyps.Get(col)&""
            Case "integer","boolean"
                val=cur.GetInt2(col)
            Case "double"
                val=cur.GetDouble2(col)
            Case "long"
                val=cur.GetLong2(col)
            Case "byte"
                val=cur.GetBlob2(col)
            Case Else
                val=cur.GetString2(col)
        End Select
        res.Put(cur.GetColumnName(col).ToLowerCase,val)
    Next
    cur.Close
    Return res
End Sub


'Creates a html text that displays the data in a table.
'The style of the table can be changed by modifying HtmlCSS variable.
Public Sub ExecuteHtml(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As String
    Dim cur As ResultSet
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    g.mLog("ExecuteHtml: " & Query)
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("<html><body>").Append(CRLF)
    sb.Append("<style type='text/css'>").Append(HtmlCSS).Append("</style>").Append(CRLF)
    sb.Append("<table><thead><tr>").Append(CRLF)
    For i = 0 To cur.ColumnCount - 1
        sb.Append("<th>").Append(cur.GetColumnName(i)).Append("</th>")
    Next
    sb.Append("</thead>")
   
    '    For i = 0 To cur.ColumnCount - 1
    '        If i = 1 Then
    '            sb.Append("<th style='width:200px;'>").Append(cur.GetColumnName(i)).Append("</th>")
    '        Else
    '            sb.Append("<th>").Append(cur.GetColumnName(i)).Append("</th>")
    '        End If
    '    Next
       
    sb.Append("</tr>").Append(CRLF)
    Dim row As Int
    Do While cur.NextRow
        If row Mod 2 = 0 Then
            sb.Append("<tr>")
        Else
            sb.Append("<tr class='odd'>")
        End If
        For i = 0 To cur.ColumnCount - 1
            sb.Append("<td>")
            sb.Append(cur.GetString2(i))
            sb.Append("</td>")
        Next
        sb.Append("</tr>").Append(CRLF)
        row = row + 1
    Loop
    cur.Close
    sb.Append("</table></body></html>")
    Return sb.ToString
End Sub

Public Sub DeleteRecord(SQL As SQL, TableName As String, WhereFieldEquals As Map)
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("DELETE FROM ").Append(EscapeField(TableName)).Append(" WHERE ")
    If WhereFieldEquals.Size = 0 Then
        g.mLog("WhereFieldEquals map empty!")
        Return
    End If
    Dim args As List
    args.Initialize
    For i = 0 To WhereFieldEquals.Size - 1
        If i > 0 Then sb.Append(" AND ")
        sb.Append(EscapeField(WhereFieldEquals.GetKeyAt(i))).Append(" = ?")
        args.Add(WhereFieldEquals.GetValueAt(i))
    Next
    g.mLog("DeleteRecord: " & sb.ToString)
    SQL.ExecNonQuery2(sb.ToString, args)
End Sub
#Region Pagination
'support mysql only
'fromQuery:from wp_user
'whereQuery: username like '1%'
'orderby:username asc,nickname desc
'args:array(1,"123")
'pageno start from 0
Public Sub Pagination_TotalSize(SQL As SQL,fromQuery As String,whereQuery As String,args() As Object) As Int
    Dim qsb As StringBuilder
    qsb.Initialize
    qsb.Append("select count(1) cnt ")
    qsb.Append(fromQuery&" where 1=1 ")
    If whereQuery<>Null And whereQuery.Length>0 Then
        qsb.Append("and "&whereQuery&" ")
    End If
    Dim query As String=qsb.ToString
    G.mLog("PaginationTotalSize:"&query)
    Dim cur As ResultSet
    If args<>Null Then
        cur=SQL.ExecQuery2(query,args)
    Else
        cur=SQL.ExecQuery(query)
    End If
    Try
        cur.NextRow
        Return cur.GetInt("cnt")
    Catch
        Log(LastException)
        Return 0
    End Try
End Sub

'support mysql only
'selectQuery:*
'fromQuery:from wp_user
'whereQuery: username like '1%'
'orderby:username asc,nickname desc
'args:array(1,"123")
'pageno start from 0
Public Sub Pagination(SQL As SQL,selectQuery As String,fromQuery As String,whereQuery As String,orderby As String,args() As Object,pageno As Int,pagesize As Int) As List
    Dim start As Int=pageno*pagesize
    Dim qsb As StringBuilder
    qsb.Initialize
    If selectQuery<>Null And selectQuery.Length>0 Then
        qsb.Append("select "&selectQuery&" ")
    Else
        qsb.Append("select * ")
    End If
    qsb.Append(fromQuery&" where 1=1 ")
    If whereQuery<>Null And whereQuery.Length>0 Then
        qsb.Append("and "&whereQuery&" ")
    End If
    If orderby<>Null And orderby.Length>0 Then
        qsb.Append("order by "&orderby&" ")
    End If
    qsb.Append("limit "&start&","&pagesize)
    Dim query As String=qsb.ToString
    G.mLog("Pagination:"&query)
    Dim cur As ResultSet
    If args<>Null Then
        cur=SQL.ExecQuery2(query,args)
    Else
        cur=SQL.ExecQuery(query)
    End If
    Dim table As List
    table.Initialize
    Dim rsmd As clsResultSetMetaData
    rsmd.Initialize(cur)
    Dim lstTyps As List
    Do While cur.NextRow
        Dim m As Map
        m.Initialize
        If lstTyps.IsInitialized=False Then
            lstTyps.Initialize
            For col = 0 To cur.ColumnCount - 1
                Dim key As String=rsmd.getColumnClassName(col).Trim.ToLowerCase
                key=key.SubString(key.LastIndexOf(".")+1)
                lstTyps.Add(key)
            Next
        End If
        For col = 0 To cur.ColumnCount - 1
            Dim val As Object
'            Log("typ:"&lstTyps.Get(col)&"")
            Select lstTyps.Get(col)&""
                Case "integer","boolean"
                    val=cur.GetInt2(col)
                Case "double"
                    val=cur.GetDouble2(col)
                Case "long"
                    val=cur.GetLong2(col)
                Case "byte"
                    val=cur.GetBlob2(col)
                Case Else
                    val=cur.GetString2(col)
            End Select
            m.Put(cur.GetColumnName(col).ToLowerCase,val)
        Next
        table.Add(m)
    Loop
    cur.Close
    Return table
End Sub
'support mysql only
Public Sub truncateTable(sql As SQL, table As String)
    sql.ExecNonQuery("truncate "&EscapeField(table))
End Sub
#End Region
#Region alias
Public Sub QueryOne(SQL As SQL, Query As String, StringArgs() As Object) As Map
    Return ExecuteMap(SQL,Query,StringArgs)
End Sub
Public Sub QueryList(SQL As SQL, Query As String, StringArgs() As Object, Limit As Int) As List
    Return ExecuteMemoryTable(SQL, Query, StringArgs, Limit)
End Sub
Public Sub QueryList2(SQL As SQL, tablename As String,fields As String,WhereFieldEquals As Map, Limit As Int) As List
    Return ExecuteMemoryTable2(SQL,tablename,fields,WhereFieldEquals,Limit)
End Sub
Public Sub InsertOne(SQL As SQL, TableName As String, m As Map) As dbOptret
    Return InsertMap(SQL,TableName,m)
End Sub
Public Sub InsertList(SQL As SQL, TableName As String, ListOfMaps As List) As dbOptret
    Return InsertMaps(SQL,TableName,ListOfMaps)
End Sub
#End Region
file:clsResultSetMetaData.bas
B4X:
'Class module
Sub Class_Globals
    Private jo As JavaObject
End Sub

'Initializes the object. You can add parameters to this method if needed.
Public Sub Initialize(rs As ResultSet)
    jo=rs
    jo=jo.RunMethodJO("getMetaData",Null)
End Sub
'idx start from 1
Public Sub getColumnClassName(idx As Int) As String
    If idx>(getColumnCount-1) Then Return ""
    Return jo.RunMethod("getColumnClassName",Array(idx+1))
End Sub
'idx start from 1
Public Sub     getColumnLabel(idx As Int) As String
    If idx>(getColumnCount-1) Then Return ""
    Return jo.RunMethod("getColumnLabel",Array(idx+1))
End Sub
'idx start from 1
Public Sub getColumnTypeName(idx As Int) As String
    If idx>(getColumnCount-1) Then Return ""
    Return jo.RunMethod("getColumnTypeName",Array(idx+1))
End Sub
Public Sub     getColumnCount As Int
    Return jo.RunMethod("getColumnCount",Null)
End Sub
 
Last edited:
Top