Example: /Java/JavaDatabase/JavaDatabase to JSON

Online Documentation   -   Statistics   -   FAQ   -   Plugin Parts (All, Dependencies)   -   Class hierarchy

New in Version 22.2 22.3 22.4 22.5 23.0 23.1 23.2 23.3 23.4 23.5 24.0 24.1

The list of the   topics,   classes,   interfaces,   controls,   modules,   global methods by category,   global methods by name,   screenshots,   licenses   and   examples.

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:

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


💬 Ask a question or report a problem