B4J Tutorial Easier SQL Inserts

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

 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:

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 Map) As 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 SQL) As 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:

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

':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

':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]

 

[Robert Valentino]

Not sure why you would do so many appends.

when I define my SQLTables I defined them with default values

  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

                     '------------------------------------------------------------------------------------------------------
                   '  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

            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

 

[keirS]

Not sure why you would do so many appends.

when I define my SQLTables I defined them with default values.

Default values are useful when there is a useful default. The example table uses default values.

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)")

Then when I want to do an Insert I just need to do an Insert into and then read the last record added

                         '---------------------------------------------------------------------------------------------------
                         '  Retrieve the RecordID for the last record just Added
                         '---------------------------------------------------------------------------------------------------
                         mRecordID = cDatabase.SQLDatabase.ExecQuerySingleResult("SELECT max(RecordID) FROM " &DEFINE_DB_PlayerTableName)

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:

H2DBFILE= "jdbc:h2:file:" &  File.DirData(APP_NAME) & "/H2/customer"

to:

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.

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

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.

 

[Robert Valentino]

Agreee