Xojo Conferences
XDCMay2019MiamiUSA

Platforms to show: All Mac Windows Linux Cross-Platform

/Java/JavaDatabase/JavaDatabase to JSON
Function:
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.rbp"
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:

Feedback, Comments & Corrections

The items on this page are in the following plugins: MBS Java Plugin.




Links
MBS Xojo Chart Plugins