A standard SQL insert in B4J may look something like this:
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:
Using the class:
Instead of using '?' for the parameters in the INSERT I am using
.
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
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]
B4X:
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.CloseThere 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:
B4X:
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 SubUsing the class:
B4X:
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
B4X:
':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
B4X:
':parametername'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]
