B4J Tutorial Easier SQL Inserts

Discussion in 'B4J Tutorials' started by keirS, Jul 24, 2018.

  1. keirS

    keirS Well-Known Member Licensed User

    A standard SQL insert in B4J may look something like this:

    Code:
    Dim InsertCustomerSQL As StringBuilder
       
    Dim InsertValues As List
       
    Dim InsertID As Long
       
    Dim RS As ResultSet
     
       InsertValues.Initialize
       InsertCustomerSQL.Initialize
       InsertCustomerSQL.Append(
    "Insert  into `customers`(")  _
       .Append(
    "`customerName`,") _
       .Append(
    "`contactLastName`,")  _
       .Append(
    "`contactFirstName`,")  _
       .Append(
    "`phone`,")  _
       .Append(
    "`addressLine1`,")  _
       .Append(
    "`addressLine2`,")  _
       .Append(
    "`city`,")  _
       .Append(
    "`state`,")  _
       .Append(
    "`postalCode`,")  _
       .Append(
    "`country`,")  _
       .Append(
    "`salesRepEmployeeNumber`,")  _
       .Append(
    "`creditLimit`)")  _
       .Append(
    "values (?,?,?,?,?,?,?,?,?,?,?,?)")
     
        InsertValues.Add(
    "Atelier graphique")
        InsertValues.Add(
    "Schmitt")
        InsertValues.Add(
    "Carine ")
        InsertValues.Add(
    "40.32.2555")
        InsertValues.Add(
    "54, rue Royale")
        InsertValues.Add(
    Null)
        InsertValues.Add(
    "Nantes")
        InsertValues.Add(
    Null)
        InsertValues.Add(
    "44000")
        InsertValues.Add(
    "France")
        InsertValues.Add(
    1370)
        InsertValues.Add(
    21000.00)
     
        Connection.ExecNonQuery2(InsertCustomerSQL.ToString,InsertValues)
     
       
    'Get customerNumber for row just inserted
       If GetDatabaseType(Connection).ToUpperCase = "SQLITE" Then
            RS = Connection.ExecQuery(
    "SELECT last_insert_rowid()")
        
    Else If GetDatabaseType(Connection).ToUpperCase = "H2" Then
          RS = Connection.ExecQuery(
    "SELECT SCOPE_IDENTITY()")
     
     
    End If
       RS.NextRow
       InsertID = RS.GetLong2(
    0)
       RS.Close
    There a couple of issues I have with this. The first is matching the question marks to the column names. With relatively few columns in a table it's not a major issue. If you have 20+ columns it becomes more difficult.

    The second issue I have is that many tables have primary key columns where the column value is generated by the database by defining the column as AUTO_INCREMENT (MYSQL,H2), IDENTITY (MS SQL) , SERIAL (PostgreSQL) or AUTOINCREMENT (SQLite). Retrieving this ID is vital for performing parent child inserts.

    There is no generic way to do this in SQL and like the column definitions above each RDBMS has a different way of retrieving the generated key. JDBC does have a generic way of retrieving the generated key however and it is supported for all of the common RDBMSes.

    So here is a little example class that solves both issues:

    Code:
    Sub Class_Globals
        
    Private B4JInsertStatement As String
        
    Private SQLInsertStatement As String
        
    Private SQLParameterMap As Map
        
    Private SQLParameterIdentifiers As List
        
    Private SQLParameters As List
    End Sub
    'Initialze with B4JInsertStatement
    Public Sub Initialize(InsertStatement As String)
        SQLParameterIdentifiers.Initialize
        SQLParameters.Initialize
        SQLParameterMap.Initialize
        B4JInsertStatement = InsertStatement
        
    'Create standard JDBC insert statement and replace ':columnparameter' with '?'
        SQLInsertStatement = Parse(B4JInsertStatement)
    End Sub
    'set the parameter map; map containging columnparameter, column value pairs
    Public Sub setParameterMap(InsertParameters As MapAs Boolean
        SQLParameterMap = InsertParameters
        
    Return GenerateParameterList
    End Sub
    Public Sub getParameterMap() As Map
        
    Return SQLParameterMap
    End Sub
    Public Sub getInsertParameters() As List
        
    Return SQLParameters
    End Sub
    'Turn parameter map into a list of column values for the insert
    Private Sub GenerateParameterList() As Boolean
        
    Dim ParametersMatched As Boolean = True
        SQLParameters.Clear
        
    For Each Parameter As String In SQLParameterIdentifiers
            
    If SQLParameterMap.ContainsKey(Parameter) Then
                SQLParameters.Add(SQLParameterMap.Get(Parameter))
            
    Else
                ParametersMatched = 
    False
            
    End If
        
        
    Next
        
    Return ParametersMatched
    End Sub
    Private Sub Parse(SQLInsert As String)  As String
        
    Dim JOCharArray As JavaObject =  SQLInsert
        
    Dim ParamQuery As StringBuilder
        ParamQuery.Initialize
        
    Dim Query() As Char = JOCharArray.RunMethod("toCharArray",Null)
        
    Dim  length As Int = Query.Length
        
    Dim  InSingleQuote As Boolean =False
        
    Dim  InDoubleQuote  As Boolean =False
        
    Dim  Index As Int = 1
        
    Dim  JOChar As JavaObject
        
    Dim AChar As Char = "C"
        SQLParameterIdentifiers.Clear
     
     
        JOChar.InitializeNewInstance(
    "java.lang.Character",Array(AChar))
        
    For i = 0 To  length - 1
            
    Dim C As Char = Query(i)
     
            
    If InSingleQuote Then
                
    If C = "'" Then
                    InSingleQuote=
    False
              
                
    End If
                ParamQuery.Append(Query(i))
            
    Else If InDoubleQuote Then
                
    If C = """" Then
                    InDoubleQuote = 
    False
                
    End If
                ParamQuery.Append(Query(i))
            
            
    Else
                
    If C = "'" Then
                    InSingleQuote=
    True
                    ParamQuery.Append(Query(i))
                
    Else If InDoubleQuote Then
                    
    If C = """" Then
                        InDoubleQuote = 
    True
                        ParamQuery.Append(Query(i))
                    
    End If
                
    Else If C=":" Then And i+1 < length And JOChar.RunMethod("isJavaIdentifierStart",Array(Query(i+1))) Then
                    
    Dim J As Int = i+2
                    
    Dim SQLParameter As StringBuilder
                    SQLParameter.Initialize
                    SQLParameter.Append(Query(J -
    1))
                    
    Do While j<length And JOChar.RunMethod("isJavaIdentifierPart",Array(Query(J)))
                        SQLParameter.Append(Query(J))
                        J = J + 
    1
                    
    Loop
                    ParamQuery.Append(
    "?")
                    SQLParameterIdentifiers.Add(SQLParameter.ToString)
                    i = J -
    1
                
    Else
                    ParamQuery.Append(Query(i))
                
    End If
                
            
    End If
     
        
        
    Next
        
    Return ParamQuery.ToString
    End Sub
    'Execute insert statement and return generated key
    Sub ExecInsertWithGeneratedKey(Connection As SQLAs Long
        
    Dim JOConnection As JavaObject = Connection
        
    Dim JOStatement As JavaObject
        
    Dim GeneratedKeys  As ResultSet
        
    Dim JOKeys As JavaObject
        
    Dim GeneratedKeyValue As Long
        JOConnection = JOConnection.GetFieldJO(
    "connection")
        JOStatement = JOConnection.RunMethodJO(
    "createStatement",Null)
        JOStatement =  JOConnection.RunMethodJO(
    "prepareStatement",Array(SQLInsertStatement, JOStatement.GetField("RETURN_GENERATED_KEYS")))
        
    For I = 0 To SQLParameters.Size -1
            JOStatement.RunMethod(
    "setObject",Array (I+1,SQLParameters.Get(I)))
        
    Next
        JOStatement.RunMethod(
    "executeUpdate",Null)
        JOKeys = JOStatement.RunMethod(
    "getGeneratedKeys",Null)
        
    Log(JOKeys.RunMethod("next",Null))
        GeneratedKeyValue =  JOKeys.RunMethod(
    "getLong",Array(1))
        JOKeys.RunMethod(
    "close",Null)
        
    Return GeneratedKeyValue
     
    End Sub
    Sub ExecInsert(Connection As SQL)
        Connection.ExecNonQuery2(SQLInsertStatement,SQLParameters)
     
    End Sub
    Using the class:

    Code:
    Dim InsertCustomerSQL As StringBuilder
        
    Dim InsertValues As Map
        
    Dim CustomerInsert As EZSQLInsert
        
    Dim InsertKeyID As Long
        InsertValues.Initialize
        InsertCustomerSQL.Initialize
        InsertCustomerSQL.Append(
    "Insert  into `customers`(")  _
        .Append(
    "`customerName`,") _
       .Append(
    "`contactLastName`,")  _
       .Append(
    "`contactFirstName`,")  _
       .Append(
    "`phone`,")  _
       .Append(
    "`addressLine1`,")  _
       .Append(
    "`addressLine2`,")  _
       .Append(
    "`city`,")  _
       .Append(
    "`state`,")  _
       .Append(
    "`postalCode`,")  _
       .Append(
    "`country`,")  _
       .Append(
    "`salesRepEmployeeNumber`,")  _
       .Append(
    "`creditLimit`)")  _
       .Append(
    "values (:customerName,")  _
       .Append(
    ":contactLastName,")  _
       .Append(
    ":contactFirstName,")  _
       .Append(
    ":phone,")  _
       .Append(
    ":addressLine1,")  _
       .Append(
    ":addressLine2,")  _
       .Append(
    ":city,")  _
       .Append(
    ":state,")  _
       .Append(
    ":postalCode,")  _
       .Append(
    ":country,")  _
       .Append(
    ":salesRepEmployeeNumber,")  _
       .Append(
    ":creditLimit)")
     
     
     
        InsertValues.Put(
    "customerName","Signal Gift Stores")
        InsertValues.Put(
    "contactLastName","King")
        InsertValues.Put(
    "contactFirstName","Jean")
        InsertValues.Put(
    "phone","7025551838")
        InsertValues.Put(
    "addressLine1","8489 Strong St.")
        InsertValues.Put(
    "addressLine2",Null)
        InsertValues.Put(
    "city","Las Vegas")
        InsertValues.Put(
    "state""NV")
        InsertValues.Put(
    "postalCode"," 83030")
        InsertValues.Put(
    "country","USA")
        InsertValues.Put(
    "salesRepEmployeeNumber",1166)
        InsertValues.Put(
    "creditLimit",71800.00)
     
        CustomerInsert.Initialize(InsertCustomerSQL.ToString)
        CustomerInsert.ParameterMap = InsertValues
        InsertKeyID = CustomerInsert.ExecInsertWithGeneratedKey(Connection)
    Instead of using '?' for the parameters in the INSERT I am using
    Code:
    ':parametername'
    .

    I create a Map of the values to insert with the parameter name as the Map key and the column value to be inserted as the Map value.

    The class EZSQLInsert parse method turns
    Code:
    ':parametername'
    back into a '?' when the SQL statement (InsertCustomerSQL) is passed in the Initialize method. While parsing it builds a List of the identifiers (SQLParameterIdentifiers).

    ExecWithGenratedKey inserts the row into the table with the values set in ParameterMap and returns the ID of the auto generated key.


    The sample program uses the H2 database as well as SQLite. H2 is a fully featured DBMS written completely in Java and can run in embedded mode (like SQLite), server mode or a hybrid mode.

    The JAR is too big to include in the project but it can be downloaded from Maven:

    http://central.maven.org/maven2/com/h2database/h2/1.4.196/h2-1.4.196.jar

    It's used to show that retrieving generated keys is generic.[/code]
     

    Attached Files:

    embedded, DonManfred and Erel like this.
  2. Robert Valentino

    Robert Valentino Well-Known Member Licensed User

    Not sure why you would do so many appends.

    when I define my SQLTables I defined them with default values
    Code:
    Private Const DEFINE_DatabaseCreatePlayerTable                        As String = "CREATE TABLE " &DEFINE_DB_PlayerTableName                                                          _
                                                                                                    &
    " (" &DEFINE_DB_TagPlayerRecordID           &" integer primary key autoincrement, "      _
                                                                                                          &DEFINE_DB_TagPlayerFrameWorkID        &
    " integer       DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerScoringID          &
    " integer       DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerIsPlayer           &
    " int           DEFAULT 0,  "                 _                                                                                                     
                                                                                                          &DEFINE_DB_TagPlayerName               &
    " text       DEFAULT '', "                 _
                                                                                                          &DEFINE_DB_TagPlayerInActive           &
    " int           DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerAvg                &
    " int           DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerTeam               &
    " int           DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerPosition           &
    " int           DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerLane               &
    " int           DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerSex                &
    " int           DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerTotalCost          &
    " integer       DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerTotalEntries       &
    " integer       DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerTotalWinnings      &
    " integer       DEFAULT 0,  "                 _
                                                                                                          &DEFINE_DB_TagPlayerTotalCashes        &
    " integer       DEFAULT 0,  "                 _                                                                                                     
                                                                                                          &DEFINE_DB_TagPlayerWinningPlaces      &
    " text       DEFAULT '', "                 _
                                                                                                          &DEFINE_DB_TagPlayerWinningPlacesMoney &
    " text       DEFAULT '', "                 _
                                                                                                          &DEFINE_DB_TagPlayerScores             &
    " text       DEFAULT '', "                 _
                                                                                                          &DEFINE_DB_TagPlayerBonusPins          &
    " text       DEFAULT '', "                 _
                                                                                                         &DEFINE_DB_TagPlayerTeamPlayerID1        &
    " integer       DEFAULT 0,  "                 _
                                                                                                         &DEFINE_DB_TagPlayerTeamPlayerID2        &
    " integer       DEFAULT 0)  "
    Then when I want to do an Insert I just need to do an Insert into and then read the last record added

    Code:
    '------------------------------------------------------------------------------------------------------
                       '  NO reuseable players - Create a New Player
                       '------------------------------------------------------------------------------------------------------
                           cDatabase.SQLDatabase.BeginTransaction

                           mSQLStatement = 
    "INSERT into " &DEFINE_DB_PlayerTableName &" DEFAULT VALUES "

                           
    '------------------------------------------------------------------------------------------------------
                           '  Insert the New record
                           '------------------------------------------------------------------------------------------------------
                           Try
                             cDatabase.SQLDatabase.ExecNonQuery(mSQLStatement)
                             cDatabase.SQLDatabase.TransactionSuccessful
                             cDatabase.SQLDatabase.EndTransaction

                             
    '---------------------------------------------------------------------------------------------------
                             '  Retrieve the RecordID for the last record just Added
                             '---------------------------------------------------------------------------------------------------
                             mRecordID = cDatabase.SQLDatabase.ExecQuerySingleResult("SELECT max(RecordID) FROM " &DEFINE_DB_PlayerTableName)
                           
    Catch
                             
    #If Debug             
                             Log(LastException.Message)
                             
    #End If
                             cDatabase.SQLDatabase.EndTransaction

                             
    Return False
                           
    End Try
    By defining default values for my table entries I can just insert a blank record, then retrieve the blank record and update the fields based on their names

    Code:
    cDatabase.SQLDatabase.BeginTransaction


               
    '---------------------------------------------------------------------------------------------------
               '  TotalWinnings and TotalCashes are calculated before ever update
               '---------------------------------------------------------------------------------------------------
               mTotalWinnings = cGenFuncs.SumList(mWinningPlacesMoney) - mTotalCost
               mTotalCashes   = cGenFuncs.SumList(mWinningPlaces)


                mSQLStatement = 
    "UPDATE " &DEFINE_DB_PlayerTableName &" SET " &DEFINE_DB_TagPlayerFrameWorkID          &"="    &mFrameWorkID                                       &", "       _
                                                                              &DEFINE_DB_TagPlayerScoringID            &
    "="    &mScoringID                                         &", "       _
                                                                              &DEFINE_DB_TagPlayerIsPlayer             &
    "="    &mIsPlayer                                           &", "       _                                                                         
                                                                              &DEFINE_DB_TagPlayerName                 &
    "="""  &mName                                               &""", "     _
                                                                              &DEFINE_DB_TagPlayerInActive             &
    "="    &mInActive                                           &", "       _
                                                                              &DEFINE_DB_TagPlayerAvg                  &
    "="    &mAverage                                           &", "       _
                                                                              &DEFINE_DB_TagPlayerTeam                 &
    "="    &mTeam                                               &", "       _
                                                                              &DEFINE_DB_TagPlayerPosition             &
    "="    &mPosition                                           &", "       _
                                                                              &DEFINE_DB_TagPlayerLane                 &
    "="    &mLane                                               &", "       _
                                                                              &DEFINE_DB_TagPlayerSex                  &
    "="    &mSex                                               &", "       _
                                                                              &DEFINE_DB_TagPlayerTotalEntries         &
    "="    &mTotalEntries                                       &", "       _
                                                                              &DEFINE_DB_TagPlayerTotalWinnings        &
    "="    &mTotalWinnings                                       &", "       _
                                                                              &DEFINE_DB_TagPlayerTotalCashes          &
    "="    &mTotalCashes                                       &", "       _                                                                                                                                                   
                                                                              &DEFINE_DB_TagPlayerTotalCost            &
    "="    &mTotalCost                                         &", "       _
                                                                              &DEFINE_DB_TagPlayerWinningPlaces        &
    "="""  &cGenFuncs.ListToString(mWinningPlaces, True)        &""", "     _
                                                                              &DEFINE_DB_TagPlayerWinningPlacesMoney   &
    "="""  &cGenFuncs.ListToString(mWinningPlacesMoney, True)   &""", "     _
                                                                              &DEFINE_DB_TagPlayerScores               &
    "="""  &cGenFuncs.ListToString(mScores, True)               &""", "     _
                                                                              &DEFINE_DB_TagPlayerBonusPins            &
    "="""  &BonusPinsListToString(mBonusPins)                     &""", "     _
                                                                              &DEFINE_DB_TagPlayerTeamPlayerID1        &
    "="    &mTeamPlayerID1                                       &", "       _                                                                         
                                                                              &DEFINE_DB_TagPlayerTeamPlayerID2        &
    "="    &mTeamPlayerID2                                       &"  "       _                                                                                                                                                   
                                         &
    "  WHERE " &DEFINE_DB_TagPlayerRecordID &"=" &mRecordID


                
    Try
                    cDatabase.SQLDatabase.ExecNonQuery(mSQLStatement)
                    cDatabase.SQLDatabase.TransactionSuccessful
                    cDatabase.SQLDatabase.EndTransaction
                    
    Return True
                
    Catch
                   
    #If Debug             
                    Log(LastException.Message)
                   
    #End If
                                   cDatabase.SQLDatabase.EndTransaction
                    
    Return False
                
    End Try
    I guess looking at this it is a real case of 6 of one 1/2 dozen of another
     
  3. keirS

    keirS Well-Known Member Licensed User

    Default values are useful when there is a useful default. The example table uses default values.
    Code:
    CreateTable.Append("Create TABLE `customers` (") _
     .Append(
    "`customerNumber` INTEGER primary key AUTOINCREMENT,") _
     .Append(
    " `customerName` varchar(50) Not Null,") _
      .Append(
    " `contactLastName` varchar(50) Not Null,") _
      .Append(
    " `contactFirstName` varchar(50) Not Null,") _
      .Append(
    "`phone` varchar(50) Not Null,") _
      .Append(
    "`addressLine1` varchar(50) Not Null,") _
      .Append(
    " `addressLine2` varchar(50) DEFAULT Null,") _
      .Append(
    "`city` varchar(50) Not Null,") _
      .Append(
    "`state` varchar(50) DEFAULT Null,") _
      .Append(
    "`postalCode` varchar(15) DEFAULT Null,") _
      .Append(
    "`country` varchar(50) Not Null,") _
      .Append(
    "`salesRepEmployeeNumber` int(11) DEFAULT Null,") _
      .Append(
    "`creditLimit` decimal(10,2) DEFAULT Null)")
    This code will only work for SQLite (and even then I think I could come up with a way of breaking it). It will not work for any multi connection DBMS that supports inserts and updates for connections simultaneously. That includes H2 the other DBMS used in the example. All I need to do is change the connection string from:
    Code:
    H2DBFILE= "jdbc:h2:file:" &  File.DirData(APP_NAME) & "/H2/customer"
    to:
    Code:
    H2DBFILE= "jdbc:h2:file:" &  File.DirData(APP_NAME) & "/H2/customer;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=9090"
    and H2 becomes a fully fledged server supporting multiple connections with simultaneous writes. This works in a UI app by the way and not just a server app. Using SELECT max(RecordID) will just not work as I can't guarantee another connection inserted a record after I have done the insert and before I have done the SELECT max(RecordID). That's why SQLite has the last_insert_rowid() function because it returns the last Row ID from the connection.
    Doing it this way is creating a performance bottleneck. On a single connection simple SQLite table it may perform OK. On a MySQL or SQL Server table with many columns and indexes with lots of inserts and updates it will cause issues.

    This is because the DBMS will have to go and find each index key and delete it and add a new index key. So for example if I have a table with 6 indexes (including the primary key). What my code would do is:


    Insert record
    Add index key 1 (primary key)
    Add index key 2
    Add index key 3
    Add index key 4
    Add index key 5
    Add index key 6
    Retrieve Index key 1


    What you are potentiality doing if all indexed columns are updated is.


    Insert record
    Add index key 1 (primary key)
    Add index key 2
    Add index key 3
    Add index key 4
    Add index key 5
    Add index key 6
    Retrieve Index key 1
    Update Record

    Find old index key 2
    Delete old index key 2
    Add new index key 2
    Find old index key 3
    Delete old index key 3
    Add new index key 3
    Find old index key 4
    Delete old index key 4
    Add new index key 4
    Find old index key 5
    Delete old index key 5
    Add new index key 5
    Find old index key 6
    Delete old index key 6
    Add new index key 6


    This is a very simplified example of what actually happens but it does illustrate the performance implications of your method.

    As a side note part of performance tuning a DBMS involves identifying updates where it is actually quicker to perform a delete and insert rather than an update.
     
  4. Robert Valentino

    Robert Valentino Well-Known Member Licensed User

Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice