Platforms to show: All Mac Windows Linux Cross-Platform
/Java/JavaDatabase/JavaDatabase to JSON
Required plugins for this example: MBS Java Plugin, MBS Util Plugin
You find this example project in your Plugins Download as a Xojo project file within the examples folder: /Java/JavaDatabase/JavaDatabase to JSON
This example is the version from Sat, 15th Jul 2016.
Project "JavaDatabase to JSON.xojo_binary_project"
Class App Inherits Application
Const kEditClear = "&Delete"
Const kFileQuit = "&Quit"
Const kFileQuitShortcut = ""
EventHandler Sub Open()
dim j as JavaConnectionMBS
dim d as JavaDatabaseMBS
dim r as JavaResultSetMBS
dim f as FolderItem=SpecialFolder.desktop.Child("sqlite-jdbc-3.8.11.2.jar")
if not f.Exists then
MsgBox "Missing sqlite connector classes!"
Return
end if
if TargetLinux then
// change path for your linux PC!
'JavaVMMBS.SetLibraryPath("/home/cs/jre1.6.0_05/lib/i386/client/libjvm.so")
end if
dim v as new JavaVMMBS(f)
d=new JavaDatabaseMBS(v,"org.sqlite.JDBC")
j=d.getConnection("jdbc:sqlite:/Users/cs/Desktop/test.sqlite")
if j<>Nil then
r=j.MySelectSQLwithPreparedStatement("SELECT * from Documentation")
// convert to JSON and measure
dim t1 as Double = Microseconds
dim jj as JSONMBS = ToJSON(r, j)
dim t2 as Double = Microseconds
// write to file
dim dd as new date
dim ff as FolderItem = GetFolderItem("json "+dd.SQLDateTime.ReplaceAll(":", "-")+".txt")
dim t as TextOutputStream = TextOutputStream.Create(ff)
t.WriteLine jj.toString
t.WriteLine str((t2-t1)/1000000.0, "0.0")+ " seconds"
t.Close
MsgBox "done."
r=nil
else
MsgBox "not connected"
end if
Exception e as JavaExceptionMBS
MsgBox e.message+" errorcode: "+str(e.ErrorNumber)
End EventHandler
Function ToJSON(jrs as JavaResultSetMBS, con as JavaConnectionMBS) As JSONMBS
#pragma DisableBackgroundTasks true
//This method receive a JavaResultSetMBS as input parameter and convert it
//into a JSONMBS object in which there's an array node called "Recordset"
//Each node of the "Recordset" rappresents a Record with its field name and value
dim result as JSONMBS = JSONMBS.NewObjectNode
dim recordset as JSONMBS = JSONMBS.NewArrayNode
dim recordCount as Integer
dim columnCount as Integer
if jrs<>Nil then
dim db as JavaConnectionMBS = con
dim typeBIT as integer = db.typeBIT
dim typeDOUBLE as integer = db.typeDOUBLE
dim typeFLOAT as integer = db.typeFLOAT
dim typeINTEGER as integer = db.typeINTEGER
dim typeNULL as integer = db.typeNULL
dim typeREAL as integer = db.typeREAL
dim typeSMALLINT as integer = db.typeSMALLINT
dim typeTINYINT as integer = db.typeTINYINT
//With getMetaData get the properties of recordset
dim jrsMetaData as JavaResultSetMetaDataMBS = jrs.getMetaData
columnCount = jrsMetaData.getColumnCount
// get column types and names
dim columnTypes() as integer
dim columnNames() as string
redim columnTypes(columnCount)
redim columnNames(columnCount)
For c as Integer = 1 to columnCount
columnTypes(c) = jrsMetaData.getColumnType(c)
columnNames(c) = jrsMetaData.getColumnName(c)
Next
//For each record into the recordset
while jrs.NextRecord
//We increment the local variable "recordCount" to use it at the end
//of the records browsing cycle for the "RecordCount" node
recordCount = recordCount + 1
//We create a node for the record
dim record as JSONMBS = JSONMBS.NewObjectNode
For c as Integer = 1 to columnCount
dim columnType as integer = columnTypes(c)
dim columnName as string = columnNames(c)
select case columnType
case typeNULL
// null
dim sj as JSONMBS = JSONMBS.NewNullNode
record.AddItemToObject columnName, sj
case typeDOUBLE, typeFLOAT, typeREAL, typeINTEGER, typeTINYINT, typeSMALLINT
// double
dim v as double = jrs.getDouble(c)
dim sj as JSONMBS = JSONMBS.NewNumberNode(v)
record.AddItemToObject columnName, sj
case typeBIT
// boolean
dim v as Boolean = jrs.getBoolean(c)
dim sj as JSONMBS = JSONMBS.NewBoolNode(v)
record.AddItemToObject columnName, sj
else
// all others as string
dim v as string = jrs.getString(c)
dim sj as JSONMBS = JSONMBS.NewStringNode(v)
record.AddItemToObject columnName, sj
end Select
Next
//We add the "record" node to the recordset array node
recordset.AddItemToArray(record)
wend
//We add the "recordset" node to the JSONMBS result
result.AddItemToObject "Recordset", recordset
end if
//We add the two nodes with column count and records count
result.AddItemToObject "ColumnCount", JSONMBS.NewNumberNode(columnCount)
result.AddItemToObject "RecordCount", JSONMBS.NewNumberNode(recordCount)
Return result
//Java Exception
Exception e as JavaExceptionMBS
End Function
End Class
MenuBar MenuBar1
MenuItem FileMenu = "&File"
MenuItem FileQuit = "#App.kFileQuit"
MenuItem EditMenu = "&Edit"
MenuItem EditUndo = "&Undo"
MenuItem UntitledMenu1 = "-"
MenuItem EditCut = "Cu&t"
MenuItem EditCopy = "&Copy"
MenuItem EditPaste = "&Paste"
MenuItem EditClear = "#App.kEditClear"
MenuItem UntitledMenu0 = "-"
MenuItem EditSelectAll = "Select &All"
End MenuBar
Module JavaUtil
Sub ExecuteSQL(extends c as JavaConnectionMBS, sql as string)
try
dim s as JavaStatementMBS
s=c.createStatement
if s<>nil then
call s.executeUpdate sql
end if
catch d as JavaExceptionMBS
MsgBox d.message+" ErrorCode: "+str(d.errornumber)
end try
End Sub
Sub MyExecuteSQL(extends j as javaconnectionMBS, sql as string)
try
j.ExecuteSQL sql
catch d as JavaExceptionMBS
MsgBox d.message+" ErrorCode: "+str(d.errornumber)
end try
End Sub
Sub MyExecuteSQLwithPreparedStatement(extends j as javaconnectionMBS, sql as string)
try
dim p as JavaPreparedStatementMBS
p=j.prepareStatement(sql)
if p<>Nil then
call p.execute
end if
catch d as JavaExceptionMBS
MsgBox d.message+" ErrorCode: "+str(d.errornumber)
end try
End Sub
Function MySelectSQL(extends j as javaconnectionMBS, sql as string, editable as boolean=false) As JavaResultSetMBS
try
return j.SelectSQL(sql,editable)
catch d as JavaExceptionMBS
MsgBox d.message
end try
End Function
Function MySelectSQLwithPreparedStatement(extends j as javaconnectionMBS, sql as string, editable as boolean=false) As JavaResultSetMBS
try
dim p as JavaPreparedStatementMBS
p=j.prepareStatement(sql)
if p<>Nil then
dim r as JavaResultSetMBS
r=p.executeQuery
r.Tag=p // keep a reference to the statement
Return r
end if
catch d as JavaExceptionMBS
MsgBox d.message
end try
End Function
Function SelectSQL(extends c as JavaConnectionMBS, sql as string, editable as boolean=false) As JavaResultSetMBS
try
dim mode as integer = c.CONCUR_READ_ONLY
dim s as JavaStatementMBS
s=c.createStatement(c.TYPE_FORWARD_ONLY, mode)
if s<>nil then
dim r as JavaResultSetMbs
r=s.executeQuery(sql)
if r<>Nil then
// you need to keep the statement with the
r.Tag=s
Return r
end if
end if
catch d as JavaExceptionMBS
MsgBox d.message+" ErrorCode: "+str(d.errornumber)
end try
End Function
End Module
End Project
See also:
- /Java/JavaDatabase/JavaDatabase MDB
- /Java/JavaDatabase/JavaDatabase MySQL Blob and Clob
- /Java/JavaDatabase/JavaDatabase MySQL test
- /Java/JavaDatabase/JavaDatabase MySQL test with Threading
- /Java/JavaDatabase/JavaDatabase MySQL with JavaInputStreamMBS
- /Java/JavaDatabase/JavaDatabase SQLite
- /Java/JavaDatabase/JavaDatabase SQLite blob
The items on this page are in the following plugins: MBS Java Plugin.