Database to Object

You have a lot of questions in one question.

Linq2SQL is just an ORM kind of, if you are going that route I'd look at the entity framework (microsoft's orm).

Let's talk about layered applications a bit to help you understand how to populate objects. Your typical database app is composed of 3 layers (some say 4 and refer to the database itself as a layer, it really doesn't matter). You have the following:

-UI

-BLL

-DAL

So your communication is the UI talks to the BLL and the BLL talks to the DAL. The DAL returns some data to the BLL which in turn present it back to the UI. I don't know who told you datasets / tables are bad...sure a reader is faster but it doesn't mean using a datatable is bad.

Let me give you an example. Stop thinking of your DAL as one simple class. Start thinking of the DAL layer as an entire folder of different classes. One of those classes is a static DB class. It's static because you are dealing with one database (in most cases), so no need to instantiate the class. So it may look like this:

public static class DB {
private static readonly string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);

public static int Update(string sql)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandText = sql;

                    connection.Open();
                    return command.ExecuteNonQuery();
                }
            }
        }

public static DataTable GetDataTable(string sql)
        {
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;

                using (DbCommand command = factory.CreateCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = sql;

                    using (DbDataAdapter adapter = factory.CreateDataAdapter())
                    {
                        adapter.SelectCommand = command;

                        DataTable dt = new DataTable();
                        adapter.Fill(dt);

                        return dt;
                    }
                }
            }
}

Some of this was taken from the dofactory website. Great resource to learn how to use design patterns. Anyhow that is just one .class file. Now you need another one for say a CustomerDAO (a customer data access object).

Ok so how can you use that DB class you created (well I would use a combination of sprocs but to make this a simple post lets avoid stored procedures for now). If I need to Get Customers I could define this:

public IList<Customer> GetCustomers()
{
    StringBuilder sql = new StringBuilder();
    sql.Append(" SELECT CustomerId, CompanyName, City, Country ");
    sql.Append("   FROM Customer ");

    DataTable dt = Db.GetDataTable(sql.ToString());

    return MakeCustomers(dt);
}

Remember this is in an entirely different .class file. Ok so how does make customers look:

private IList<Customer> MakeCustomers(DataTable dt)
        {
            IList<Customer> list = new List<Customer>();
            foreach (DataRow row in dt.Rows)
                list.Add(MakeCustomer(row));

            return list;
        }

So what I am doing here is I had a datatable full of customers. I need to loop through each row of the datatable and Make the customer:

private Customer MakeCustomer(DataRow row)
        {
            int customerId = int.Parse(row["CustomerId"].ToString());
            string company = row["CompanyName"].ToString();
            string city = row["City"].ToString();
            string country = row["Country"].ToString();

            return new Customer(customerId, company, city, country);
        }

So this customer is new'd and stored in a customer list.

This is just a small example of what your Data Access Layer does. The database class simply stores the connection string and functions to get a data set or get a data table or even in your case get a data reader (which you could do too). The CustomerDAO class is simply a class that deals with customer objects and may implement say an ICustomer interface.

So where is the Customer class itself? It can be in another folder as the business layer, because it simply is a business object. Here you can set validations, and required fields inside of the customer class.

Your UI doesnt have anything related to datareaders, datasets, or SQL at all. Your Business layer has nothing to do with it either (it defines some rules behind your business objects). Your dal can be very flexible (can work with SQL, Oracle, etc) or can be limited to say SQL Server if that is what you plan on doing. Do not overkill your app. If you are a MS guy and are certain to only use SQL Server don't make your job difficult by trying to roll out the ultimate DAL that works with any vendor. it is ok to use SQLCommand, SQLConnection, etc.

Skip to main content
Sign in (or register) English IBM

Technical topics
Evaluation software
Community
Events
Search developerWorks


developerWorks
Web development
Technical library
JavaScript EE, Part 1: Run JavaScript files on the server side

Learn how to use the javax.script API in Ajax and Java EE applications

Andrei Cioroianu, Senior Java Developer and Consultant, Devsphere
Summary:  Combine JavaScript with Java™ code on the server to get the freedom to use the same JavaScript routines on both servers and clients. In addition, the techniques presented throughout this series will allow you to maintain a single code base for both Ajax and non-Ajax clients. Because much of the server-side code would still be written in the Java language, you'll find it necessary to expose the Java Platform, Enterprise Edition (Java EE) features to JavaScript. In this series, learn how to run JavaScript files on the server side, call remote JavaScript functions with Ajax, and use the Java Scripting API with the JavaServer Pages (JSP) technology.

View more content in this series

Date:  16 Dec 2008
Level:  Intermediate
PDF:  A4 and Letter (56KB | 19 pages)Get Adobe® Reader®
Also available in:   Chinese  Russian  Japanese

Activity:  45190 views
Comments:   1 (View | Add comment - Sign in)

 Average rating (22 votes)
Rate this article
Typical Ajax applications use JavaScript on the client side and a different language, such as Java, on the server side. As a result, developers must implement some of their routines twice, using JavaScript for the Web browser and another language for the server. This double-coding issue can be avoided by using JavaScript combined with Java code on the server side, getting full support of scripting languages through the javax.script API. In addition, the Java SE Development Kit (JDK) 6 already includes Mozilla's Rhino JavaScript engine, which means no setup is required.

In this first article of the series, you will use a simple script runner that lets you execute JavaScript files within a Java EE application. Scripts will have access to the so-called "implicit objects" that are used in JSP pages, such as application, session, request, and response. Most of the samples consist of reusable code so that you can easily start using JavaScript on the server in your own applications.

Using the javax.script API

This section provides an overview of the javax.script API. You will learn how to execute scripts that access Java objects, invoke JavaScript functions from your Java code, and implement a caching mechanism for the compiled scripts.

Executing scripts

The javax.script API is very simple. You start by creating a ScriptEngineManager instance, which lets you obtain a ScriptEngine object (see Listing 1), using one of the following methods:

getEngineByName()
getEngineByExtension()
getEngineByMimeType()

Listing 1. Getting a ScriptEngine instance
import javax.script.*;
...
ScriptEngineManager manager = new ScriptEngineManager();
ScriptEngine engine = manager.getEngineByName("JavaScript");
...
engine.eval(...);

You can also obtain the list of available script engines with getEngineFactories(). Currently, only the JavaScript engine is bundled with JDK 6, but ScriptEngineManager implements a discovery mechanism for third-party engines that support JSR-223 Scripting for the Java Platform (see Resources). You just have to place the JAR files of the script engines in your CLASSPATH.

After you get the javax.script.ScriptEngine instance, you can call eval() to execute scripts. You can also export Java objects as script variables, passing a Bindings instance to the eval() method. Listing 2 contains the ScriptDemo.java example, which exports two variables named demoVar and strBuf, executes a script named DemoScript.js, and then gets the variables to output their modified values.


Listing 2. The ScriptDemo.java example
package jsee.demo;

import javax.script.*;
import java.io.*;

public class ScriptDemo {

    public static void main(String args[]) throws Exception {
        // Get the JavaScript engine
        ScriptEngineManager manager = new ScriptEngineManager();
        ScriptEngine engine = manager.getEngineByName("JavaScript");

        // Set JavaScript variables
        Bindings vars = new SimpleBindings();
        vars.put("demoVar", "value set in ScriptDemo.java");
        vars.put("strBuf", new StringBuffer("string buffer"));
       
        // Run DemoScript.js
        Reader scriptReader = new InputStreamReader(
            ScriptDemo.class.getResourceAsStream("DemoScript.js"));
        try {
            engine.eval(scriptReader, vars);
        } finally {
            scriptReader.close();
        }
       
        // Get JavaScript variables
        Object demoVar = vars.get("demoVar");
        System.out.println("[Java] demoVar: " + demoVar);
        System.out.println("    Java object: " + demoVar.getClass().getName());
        System.out.println();
        Object strBuf = vars.get("strBuf");
        System.out.println("[Java] strBuf: " + strBuf);
        System.out.println("    Java object: " + strBuf.getClass().getName());
        System.out.println();
        Object newVar = vars.get("newVar");
        System.out.println("[Java] newVar: " + newVar);
        System.out.println("    Java object: " + newVar.getClass().getName());
        System.out.println();
    }
   
}

The DemoScript.js file (shown in Listing 3) contains a function named printType(), which is used to output the type of each script variable. The example calls the append() method of the strBuf object, modifies the value of demoVar, and sets a new script variable named newVar.

If the object passed to printType() has a getClass() method, it must be a Java object whose class name is obtained with obj.getClass().name. This JavaScript expression calls the getName() method of the object's java.lang.Class instance. If the object doesn't have a getClass member, printType() calls the toSource() method, which all JavaScript objects must have.


Listing 3. The DemoScript.js example
println("Start script \r\n");

// Output the type of an object
function printType(obj) {
    if (obj.getClass)
        println("    Java object: " + obj.getClass().name);
    else
        println("    JS object: " + obj.toSource());
    println("");
}

// Print variable
println("[JS] demoVar: " + demoVar);
printType(demoVar);

// Call method of Java object
strBuf.append(" used in DemoScript.js");
println("[JS] strBuf: " + strBuf);
printType(strBuf);

// Modify variable
demoVar = "value set in DemoScript.js";
println("[JS] demoVar: " + demoVar);
printType(demoVar);

// Set a new variable
var newVar = { x: 1, y: { u: 2, v: 3 } }
println("[JS] newVar: " + newVar);
printType(newVar);

println("End script \r\n");

Listing 4 contains the output of the ScriptDemo.java example. The first thing you'll notice is that demoVar is exported as a JavaScript String while the type of strBuf remains java.lang.StringBuffer. Primitive variables and Java strings are exported as native JavaScript objects. Any other Java objects (including arrays) are exported unaltered.


Listing 4. The output of ScriptDemo.java
Start script

[JS] demoVar: value set in ScriptDemo.java
    JS object: (new String("value set in ScriptDemo.java"))

[JS] strBuf: string buffer used in DemoScript.js
    Java object: java.lang.StringBuffer

[JS] demoVar: value set in DemoScript.js
    JS object: (new String("value set in DemoScript.js"))

[JS] newVar: [object Object]
    JS object: ({x:1, y:{u:2, v:3}})

End script

[Java] demoVar: value set in DemoScript.js
    Java object: java.lang.String

[Java] strBuf: string buffer used in DemoScript.js
    Java object: java.lang.StringBuffer

[Java] newVar: [object Object]
    Java object: sun.org.mozilla.javascript.internal.NativeObject

After running the script, the engine takes all variables (including new ones) and performs the inverse conversion, turning JavaScript primitives and strings into Java objects. Other JavaScript objects are wrapped into Java objects, which use some engine-specific internal API, such as sun.org.mozilla.javascript.internal.NativeObject.

Because you probably want to use only standard APIs, all data exchange between the Java code and the executed script should be done through primitive variables, strings, and Java objects (for example, beans) whose properties and methods can be accessed very easily in the JavaScript code. Simply said, don't try to access native JavaScript objects in your Java code. Use Java objects in the JavaScript code instead.

Invoking functions

In the previous example, you've seen that it is possible to call Java methods from JavaScript. Now you'll learn how to invoke JavaScript functions from your Java code. First of all, you must execute the script containing the function that you want to call. Then, you'll cast the ScriptEngine instance to javax.script.Invocable, which provides invokeFunction() and invokeMethod(). If your script implements all the methods of a Java interface, you can also use getInterface() to obtain a Java object whose methods are coded in the scripting language.

The InvDemo.java example (shown in Listing 5) executes a script named InvScript.js, which contains the demoFunction() routine. After casting the ScriptEngine instance to Invocable, the Java example passes the function name and the parameters to the engine's invokeFunction() method, which returns the value returned by demoFunction().


Listing 5. The InvDemo.java example
package jsee.demo;

import javax.script.*;
import java.io.*;

public class InvDemo {

    public static void main(String args[]) throws Exception {
        // Get the JavaScript engine
        ScriptEngineManager manager = new ScriptEngineManager();
        ScriptEngine engine = manager.getEngineByName("JavaScript");
       
        // Run InvScript.js
        Reader scriptReader = new InputStreamReader(
            InvDemo.class.getResourceAsStream("InvScript.js"));
        try {
            engine.eval(scriptReader);
        } finally {
            scriptReader.close();
        }
       
        // Invoke a JavaScript function
        if (engine instanceof Invocable) {
            Invocable invEngine = (Invocable) engine;
            Object result = invEngine.invokeFunction("demoFunction", 1, 2.3);
            System.out.println("[Java] result: " + result);
            System.out.println("    Java object: "
                    + result.getClass().getName());
            System.out.println();
        } else
            System.out.println("NOT Invocable");
    }
   
}

The InvScript.js file (see Listing 6) contains the demoFunction() routine and the same printType() function that was used in the previous script example.


Listing 6. The InvScript.js example
println("Start script \r\n");

function printType(obj) {
    if (obj.getClass)
        println("    Java object: " + obj.getClass().name);
    else
        println("    JS object: " + obj.toSource());
    println("");
}

function demoFunction(a, b) {
    println("[JS] a: " + a);
    printType(a);
    println("[JS] b: " + b);
    printType(b);
    var c = a + b;
    println("[JS] c: " + c);
    printType(c);
    return c;
}

println("End script \r\n");

If you look at the output of InvDemo.java (shown in Listing 7), you'll observe that the numeric parameters are converted to JavaScript objects and the value returned by demoFunction() is obtained as a Java object. These conversions are performed only for primitives and strings. Any other objects are passed unchanged between the JVM and the JavaScript engine, and vice-versa.


Listing 7. The output of InvDemo.java
Start script

End script

[JS] a: 1
    JS object: (new Number(1))

[JS] b: 2.3
    JS object: (new Number(2.3))

[JS] c: 3.3
    JS object: (new Number(3.3))

[Java] result: 3.3
    Java object: java.lang.Double

Note that javax.script.Invocable is an optional interface, which some script engines may not implement. The JavaScript engine that comes with JDK 6 does support this interface.

Compiling scripts

Interpreting scripts every time they are executed wastes CPU resources. If you run the same scripts multiple times, you can significantly reduce the execution time by compiling the scripts, using the methods provided by another optional interface named javax.script.Compilable, which is supported by the JavaScript engine of JDK 6.

The CachedScript class (see Listing 8) takes a script file and recompiles it only when the source code is modified. The getCompiledScript() method calls the script engine's compile(), which returns a javax.script.CompiledScript object whose eval() methods execute the script.


Listing 8. The CachedScript class
package jsee.cache;

import javax.script.*;
import java.io.*;
import java.util.*;

public class CachedScript {
    private Compilable scriptEngine;
    private File scriptFile;
    private CompiledScript compiledScript;
    private Date compiledDate;

    public CachedScript(Compilable scriptEngine, File scriptFile) {
        this.scriptEngine = scriptEngine;
        this.scriptFile = scriptFile;
    }
   
    public CompiledScript getCompiledScript()
            throws ScriptException, IOException {
        Date scriptDate = new Date(scriptFile.lastModified());
        if (compiledDate == null || scriptDate.after(compiledDate)) {
            Reader reader = new FileReader(scriptFile);
            try {
                compiledScript = scriptEngine.compile(reader);
                compiledDate = scriptDate;
            } finally {
                reader.close();
            }
        }
        return compiledScript;
    }

}

The ScriptCache class (shown in Listing 9) implements a repository for the compiled scripts, using a java.util.LinkedHashMap object. The map's initial capacity is set to a maximum number of cached scripts and the load factor is 1. These two parameters guarantee that cacheMap will never need a rehash.

By default, the LinkedHashMap class uses the insert-order for its entries. The third parameter of the LinkedHashMap() constructor must be true to use the access-order for the map's entries instead of the default order.

After the maximum capacity of the cache is reached, the removeEldestEntry() method starts returning true so that an entry is automatically removed from cacheMap each time a new compiled script is added to the cache.

By using the auto-removal mechanism of LinkedHashMap in combination with the access-order, ScriptCache ensures that the Least Recently Used (LRU) script will be removed from the full cache when a new script is added.


Listing 9. The ScriptCache class
package jsee.cache;

import javax.script.*;

import java.io.*;
import java.util.*;

public abstract class ScriptCache {
    public static final String ENGINE_NAME = "JavaScript";
    private Compilable scriptEngine;
    private LinkedHashMap<String, CachedScript> cacheMap;

    public ScriptCache(final int maxCachedScripts) {
        ScriptEngineManager manager = new ScriptEngineManager();
        scriptEngine = (Compilable) manager.getEngineByName(ENGINE_NAME);
        cacheMap = new LinkedHashMap<String, CachedScript>(
                maxCachedScripts, 1, true) {
            protected boolean removeEldestEntry(Map.Entry eldest) {
                return size() > maxCachedScripts;
            }
        };
    }

    public abstract File getScriptFile(String key);

    public synchronized CompiledScript getScript(String key)
            throws ScriptException, IOException {
        CachedScript script = cacheMap.get(key);
        if (script == null) {
            script = new CachedScript(scriptEngine, getScriptFile(key));
            cacheMap.put(key, script);
        }
        return script.getCompiledScript();
    }
   
    public ScriptEngine getEngine() {
        return (ScriptEngine) scriptEngine;
    }
   
}

The next section uses the ScriptCache class, implementing the abstract getScriptFile() method and using getScript() to retrieve compiled scripts from the cache.

Building a script runner

In this section, you'll learn how to create a simple Java servlet that implements a URL-script mapping, which allows you to invoke server-side scripts from the Web browser. In addition, the servlet will expose several Java EE objects as variables that can be used in your JavaScript code. You'll also learn how to use script contexts that let you run multiple concurrent scripts with a single JavaScript engine.

Initializing the servlet

The name of the servlet class is JSServlet. Its init() method (see Listing 10) gets several configuration parameters and creates a ScriptCache object. The servlet's script cache uses getRealPath() to obtain the path of the script file that is mapped to a given URI.


Listing 10. The init() method of JSServlet
package jsee.servlet;

import javax.script.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import jsee.cache.*;

public class JSServlet extends HttpServlet {
    private String cacheControlHeader;
    private String contentTypeHeader;
    private ScriptCache scriptCache;
   
    public void init() throws ServletException {
        ServletConfig config = getServletConfig();
        cacheControlHeader = config.getInitParameter("Cache-Control");
        contentTypeHeader = config.getInitParameter("Content-Type");
        int maxCachedScripts = Integer.parseInt(
                config.getInitParameter("Max-Cached-Scripts"));
        scriptCache = new ScriptCache(maxCachedScripts) {
            public File getScriptFile(String uri) {
                return new File(getServletContext().getRealPath(uri));
            }
        };
    }
    ...
}

Listing 11 contains the servlet's parameters, which are specified in the web.xml file. The Cache-Control header has nothing to do with the script cache. Both headers will be part of the HTTP responses returned by the servlet. The no-cache value will tell the browser not to cache the servlet's response, which should be treated as text/plain.


Listing 11. The web.xml file
<web-app ...>

    <servlet>
        <servlet-name>JSServlet</servlet-name>
        <servlet-class>jsee.servlet.JSServlet</servlet-class>
        <init-param>
            <param-name>Cache-Control</param-name>
            <param-value>no-cache</param-value>
        </init-param>
        <init-param>
            <param-name>Content-Type</param-name>
            <param-value>text/plain</param-value>
        </init-param>
        <init-param>
            <param-name>Max-Cached-Scripts</param-name>
            <param-value>1000</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>

    <servlet-mapping>
        <servlet-name>JSServlet</servlet-name>
        <url-pattern>*.jss</url-pattern>
    </servlet-mapping>

</web-app>

As you can see in Listing 11, the *.jss pattern is mapped to the servlet. This means that JSServlet will handle all requests whose URLs end with the .jss extension. When the user enters such a URL in the Web browser or clicks a .jss link, the browser sends the HTTP request to the Web server (for example, Apache), which should be configured to dispatch it to the servlet container (for example, Tomcat). If the servlet container acts as Web server as well, no extra configuration is required.

When the servlet container gets the request whose URL ends with .jss, it invokes the service() method that JSServlet inherits from javax.servlet.http.HttpServlet. This method calls either doGet() or doPost(), depending on the HTTP method of the request. Both methods are overridden by JSServlet as you'll see later in this section.

Using script contexts

Threading models for script engines

JSR-223 Scripting for the Java Platform (see Resources) defines three types of script engines:

Multithreaded engines that are capable of executing concurrent scripts that may modify variables seen by other threads
Thread-isolated engines, which are multithreaded too, but each thread has its own engine scope for keeping variables
Stateless engines, which are defined as thread-isolated engines that let the engine scope unmodified after the execution of any script
The type of script engine can be obtained with engine.getFactory().getParameter("THREADING"), which may return "MULTITHREADED", "THREAD-ISOLATED", or "STATELESS"

Each script-engine instance has a default context where you can store variables with the put() method and the output of any executed script is directed to System.out by default. In a server environment, you'll want to run concurrent scripts with each of them having its own context. The javax.script API satisfies this need, providing the ScriptContext interface and the SimpleScriptContext implementation.

Mozilla's Rhino JavaScript engine is a multithreaded engine (see the sidebar), allowing you to execute concurrent scripts that share the same context. In our case, however, you want to isolate the engine scopes and the output of the scripts running in different threads, which means you must create a new ScriptContext instance for each HTTP request.

Listing 12 shows the createScriptContext() method of the JSServlet class. This method sets the context's writer property in order to send the script's output to the writer of the response object when the script is executed. This means that everything you pass to print() or println() in your script will be included in the servlet's response.

In addition, createScriptContext() defines the following script variables with the setAttribute() method of the script context:


Table 1. Variables available in scripts that are executed by JSServlet
Script variable Description
config the javax.servlet.ServletConfig instance of the servlet
application the javax.servlet.ServletContext instance of the Web app
session the javax.servlet.http.HttpSession object
request the javax.servlet.http.HttpServletRequest object
response the javax.servlet.http.HttpServletResponse object
out the java.io.PrintWriter object that is used to output the response
factory the javax.script.ScriptEngineFactory of the script engine

The factory variable can be used to obtain information about the JavaScript engine, such as the language version or the engine version. The rest of the variables have the same roles that they have in JSP pages.


Listing 12. The createScriptContext() method of JSServlet
public class JSServlet extends HttpServlet {
    ...
    protected ScriptContext createScriptContext(
            HttpServletRequest request, HttpServletResponse response)
            throws IOException {
        ScriptContext scriptContext = new SimpleScriptContext();
        scriptContext.setWriter(response.getWriter());
        int scope = ScriptContext.ENGINE_SCOPE;
        scriptContext.setAttribute("config", getServletConfig(), scope);
        scriptContext.setAttribute("application", getServletContext(), scope);
        scriptContext.setAttribute("session", request.getSession(), scope);
        scriptContext.setAttribute("request", request, scope);
        scriptContext.setAttribute("response", response, scope);
        scriptContext.setAttribute("out", response.getWriter(), scope);
        scriptContext.setAttribute("factory",
                scriptCache.getEngine().getFactory(), scope);
        return scriptContext;
    }
    ...
}

The runScript() method (see Listing 13) gets a compiled script from the cache and calls the eval() method, passing the given script context as parameter.


Listing 13. The runScript() method of JSServlet
public class JSServlet extends HttpServlet {
    ...
    protected void runScript(String uri, ScriptContext scriptContext)
            throws ScriptException, IOException {
        scriptCache.getScript(uri).eval(scriptContext);
    }
    ...
}

Handling requests

You could simply call the above runScript() method to execute the script that is mapped to the URL of the HTTP request. In a real application, however, you'll probably want to do some initialization before running the script and some finalization after the script's execution.

It is possible to run multiple scripts, sequentially, in the same context. For example, a script could define a set of variables and functions. Then, another script could do some processing, using the variables and functions of the previous script that was executed in the same context.

The servlet's handleRequest() method (shown in Listing 14) sets the HTTP headers, runs the init.jss script, removes the context path from the request's URI, executes the script that has the obtained URI, and then runs another script named finalize.jss.


Listing 14. The handleRequest() method of JSServlet
public class JSServlet extends HttpServlet {
    ...
    protected void handleRequest(
            HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        if (cacheControlHeader != null)
            response.setHeader("Cache-Control", cacheControlHeader);
        if (contentTypeHeader != null)
            response.setContentType(contentTypeHeader);
        ScriptContext scriptContext = createScriptContext(request, response);
        try {
            runScript("/init.jss", scriptContext);
            String uri = request.getRequestURI();
            uri = uri.substring(request.getContextPath().length());
            try {
                runScript(uri, scriptContext);
            } catch (FileNotFoundException x) {
                response.sendError(404, request.getRequestURI());
            }
            runScript("/finalize.jss", scriptContext);
        } catch (ScriptException x) {
            x.printStackTrace(response.getWriter());
            throw new ServletException(x);
        }
    }
    ...
}

The doGet() and doPost() methods (see Listing 15) of JSServlet call handleRequest().


Listing 15. The doGet() and doPost() methods of JSServlet
public class JSServlet extends HttpServlet {
    ...
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        handleRequest(request, response);
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        handleRequest(request, response);
    }

}

Developing server-side scripts

This section contains several examples of server-side scripts, which show how you can get the request parameters, access the properties of your JavaBeans, and generate JSON responses.

Pre-processing and post-processing

In the previous section, you've seen that JSServlet invokes init.jss (shown in Listing 16) before executing the requested script. If you want to measure how long it takes to execute your scripts, you can store the start time into a variable as shown below.


Listing 16. The init.jss script
var debug = true;
var debugStartTime = java.lang.System.nanoTime();

Then, you can calculate the execution time in finalize.jss (see Listing 17). The time is printed as a JavaScript comment so that JSServlet can generate valid JSON responses.


Listing 17. The finalize.jss script
var debugEndTime = java.lang.System.nanoTime();
if (debug)
    println("// Time: " + (debugEndTime - debugStartTime) + " ns");

More code will be added to init.jss and finalize.jss through this series.

Getting request parameters

Scripts invoked with the help of JSServlet can access request parameters with request.getParameter() and request.getParameterValues(), which return Java objects. You might prefer a shorter syntax and you might want to work with JavaScript objects instead of Java strings and arrays. This can be done very easily by adding the following lines to init.jss (see Listing 18).


Listing 18. Getting request parameters in init.jss
var param = new Object();
var paramValues = new Object();

function initParams() {
    var paramNames = request.getParameterNames();
    while (paramNames.hasMoreElements()) {
        var name = paramNames.nextElement();
        param[name] = String(request.getParameter(name));
        paramValues[name] = new Array();
        var values = request.getParameterValues(name);
        for (var i = 0; i < values.length; i++)
            paramValues[name][i] = String(values[i]);
    }
}

initParams();

Let's say you request a script named ParamDemo.jss with the URL shown in Listing 19.


Listing 19. URL example for requesting a script
http://localhost:8080/jsee/ParamDemo.jss?firstName=John&lastName=Smith

In ParamDemo.jss (see Listing 20), you can get the two request parameters with param.firstName and param.lastName.


Listing 20. The ParamDemo.jss example
println("Hello " + param.firstName + " " + param.lastName);

Accessing JavaBeans

The application, session and request scopes of a Web application store bean instances, which you can obtain and replace with the getAttribute() and setAttribute() methods of ServletContext, HttpSession, and HttpServletRequest, respectively. You can also use the getBean() and setBean() functions (shown in Listing 21), which must be placed into the init.jss file so that any script can call them. The scope parameter should be one of the following strings:

"application"
"session"
"request"

Listing 21. The getBean() and setBean() functions of init.jss
function getBean(scope, id) {
    return eval(scope).getAttribute(id);
}

function setBean(scope, id, bean) {
    if (!bean)
        bean = eval(id);
    return eval(scope).setAttribute(id, bean);
}

Now say you want to keep an instance of DemoBean (see Listing 22) in the session scope.


Listing 22. The DemoBean.java example
package jsee.demo;

public class DemoBean {
    private int counter;

    public int getCounter() {
        return counter;
    }

    public void setCounter(int counter) {
        this.counter = counter;
    }

}

The BeanDemo.jss script (shown in Listing 23) imports the package containing the JavaBean with importPackage(Packages.jsee.demo). Then, the script tries to get the bean instance from the session scope with getBean(). If the bean is not found, BeanDemo.jss creates the object and places it into the session scope with setBean(). At the end, the script increments and prints the bean's counter property.


Listing 23. The BeanDemo.jss example
importPackage(Packages.jsee.demo);
var bean = getBean("session", "demo");
if (!bean) {
    bean = new DemoBean();
    setBean("session", "demo", bean);
}
bean.counter++;
println(bean.counter);

If you import a package that starts with java, javax, org, edu, com, or net, you don't have to use the Packages prefix in importPackage(). You may also import individual classes with importClass().

Returning JSON responses

Listing 24 shows an example that generates a JSON response containing some information about the JavaScript engine and the script's URI. The example uses the JavaScript syntax to create the json object whose source code is obtained as a String with the toSource() method.


Listing 24. The JsonDemo.jss example
var json = {
    engine: {
        name: String(factory.engineName),
        version: String(factory.engineVersion),
        threading: String(factory.getParameter("THREADING"))
    },
    language: {
        name: String(factory.languageName),
        version: String(factory.languageVersion)
    },
    script: {
        uri: String(request.requestURI)
    }
};

println(json.toSource());

All Java objects, which are retrieved from the properties of factory and request in this example, must be converted to JavaScript objects so that toSource() can work properly. Listing 25 contains the script's output:


Listing 25. The output of JsonDemo.jss
({language:{name:"ECMAScript", version:"1.6"},
engine:{name:"Mozilla Rhino", threading:"MULTITHREADED",
version:"1.6 release 2"}, script:{uri:"/jsee/JsonDemo.jss"}})

Conclusion

In this article, you've learned how to compile and execute JavaScript files, using the javax.script API. You also learned how to implement an LRU cache based on java.util.LinkedHashMap, how to export Java objects as script variables, how to implement a URL-script mapping, and how to build JavaScript files that are executed on the server-side. Stay tuned for the next part of this series where you'll learn how to call remote JavaScript functions with Ajax.


Back to top

Download

Description Name Size Download method
Sample application for this article jsee_part1_src.zip 25KB HTTP
Information about download methods


Resources

Learn

JSR-223 Scripting for the Java Platform is the specification containing all the details for using scripting languages in Java applications.

The Java Scripting Programmer's Guide presents the Java Scripting API and provides more code examples.

The javax.script package contains all the classes and interfaces of the Java Scripting API.

The developerWorks Web development zone is packed with tools and information for Web 2.0 development.

The developerWorks Ajax resource center contains a growing library of Ajax content as well as useful resources to get you started developing Ajax applications today.

Get products and technologies

Mozilla Rhino is the JavaScript engine bundled with Java SE Development Kit (JDK) 6.

About the author

Andrei Cioroianu is the founder of Devsphere, a provider of Java EE development and Web 2.0/Ajax consulting services. He's been using Java and Web technologies since 1997 and has over 10 years of professional experience in solving complex technical problems and managing the full life cycle of commercial products, custom applications, and open-source frameworks. You can reach Andrei through the contact form at www.devsphere.com.

Close [x]

Report abuse help
Report abuse

Thank you. This entry has been flagged for moderator attention.


Close [x]

Report abuse help
Report abuse

Report abuse submission failed. Please try again later.


Close [x]

developerWorks: Sign in

IBM ID:
Need an IBM ID?
Forgot your IBM ID?

Password:
Forgot your password?
Change your password

 Keep me signed in.

By clicking Submit, you agree to the developerWorks terms of use.

 

The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

Close [x]

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Display name:(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 

All information submitted is secure.

Rate this article

 Average rating (22 votes)

1 star1 star
2 stars2 stars
3 stars3 stars
4 stars4 stars
5 stars5 stars


Comments

Add comment:

Sign in or register to leave a comment.

Note: HTML elements are not supported within comments.



Notify me when a comment is added1000 characters left




Total comments (1)
Excellent article - precise and to the point.
Is it possible to provide an insight into performance with script engine? Similar tasks can be performed by various technologies and in order to adopt a technology, it would help to see how a scripting solution using JS would perform as compared to a native Java solution. For AJAX applications benefits of having JS modules run on server side are tremendous - Security (not sending sensitive data to browser), Overcome hard to solve cross-browser problems, Reduce latencies by eliminating browser-server trips - and at the same time delegating processing to browsers thereby achieving resposive UI, massive scalability. What remains to be seen is how much can we really push to the server without bogging it down. Obviously, answers to lot of these questions depends on - what is your app. However, a some commonly used realistic scenario breakdown w.r.t. performance will certainly help in adopting these technologies.

Posted by J D on 12 December 2009Report abuse
Back to top

Table of contents


Using the javax.script API
Building a script runner
Developing server-side scripts
Conclusion
Download
Resources
About the author
Comments
Next steps from IBM


Create rich Internet applications using WebSphere Portal Express Web 2.0 technologies.

Try: WebSphere Portal Express
Article: Get started using Ajax and WebSphere Portal Express
Tutorial: Quickly build a an Ajax-enabled WebSphere Portal Express Web application
Demo: Architecture, design, and construction using the IBM Rational Software Delivery Platform: AJAX
Buy: WebSphere Portal Express
Dig deeper into Web development on developerWorks

Overview
New to Web development
Open source projects
Technical library (articles, tutorials, training, and more)
Forums
Events
IBM SmartCloud trial. No charge.


Unleash the power of hybrid cloud computing today!

Public and private solutions in one trial.

Special offers







Trial software offers

Print this page
Share this pageFollow developerWorks
About
Help
Contact us
Submit content
Feeds
Report abuse
Terms of use
IBM privacy
IBM accessibility
Faculty
Students
Business Partners

Form Submit as JSON

This is the basic html script


<h2>Form</h2>
<form action="" method="post">
    <label>Hello</label>
First Name:<input type="text" name="Fname" maxlength="12" size="12"/> <br/>
Last Name:<input type="text" name="Lname" maxlength="36" size="12"/> <br/>
Gender:<br/>
Male:<input type="radio" name="gender" value="Male"/><br/>
Female:<input type="radio" name="gender" value="Female"/><br/>
Favorite Food:<br/>
Steak:<input type="checkbox" name="food[]" value="Steak"/><br/>
Pizza:<input type="checkbox" name="food[]" value="Pizza"/><br/>
Chicken:<input type="checkbox" name="food[]" value="Chicken"/><br/>
<textarea wrap="physical" cols="20" name="quote" rows="5">Enter your favorite quote!</textarea><br/>
Select a Level of Education:<br/>
<select name="education">
<option value="Jr.High">Jr.High</option>
<option value="HighSchool">HighSchool</option>
<option value="College">College</option></select><br/>
Select your favorite time of day:<br/>
<select size="3" name="TofD">
<option value="Morning">Morning</option>
<option value="Day">Day</option>
<option value="Night">Night</option></select>
<p><input type="submit" /></p>
</form>
<h2>JSON</h2>
<pre id="result">
</pre>

Here is javascript

$.fn.serializeObject = function()
{
    var o = {};
    var a = this.serializeArray();
    $.each(a, function() {
        if (o[this.name] !== undefined) {
            if (!o[this.name].push) {
                o[this.name] = [o[this.name]];
            }
            o[this.name].push(this.value || '');
        } else {
            o[this.name] = this.value || '';
        }
    });
    return o;
};

$(function() {
    $('form').submit(function() {
        $('#result').text(JSON.stringify($('form').serializeObject()));
        return false;
    });
});


You can see it

http://jsfiddle.net/sxGtM/3/

Consuming JSON Strings in SQL Server

"The best thing about XML is what it shares with JSON, being human readable. That turns out to be important, not because people should be reading it, because we shouldn't, but because it avoids interoperability problems caused by fussy binary encoding issues.

Beyond that, there is not much to like. It is not very good as a data format. And it is not very good as a document format. If it were a good document format, then wikis would use it."

Doug Crockford March 2010

This article describes a TSQL JSON parser and its evil twin, a JSON outputter, and provides the source. It is also designed to illustrate a number of string manipulation techniques in TSQL. With it you can do things like this to extract the data from a JSON document:

Select * from parseJSON('{

  "Person":

  {

     "firstName": "John",

     "lastName": "Smith",

     "age": 25,

     "Address":

     {

        "streetAddress":"21 2nd Street",

        "city":"New York",

        "state":"NY",

        "postalCode":"10021"

     },

     "PhoneNumbers":

     {

        "home":"212 555-1234",

        "fax":"646 555-4567"

     }

  }

}

')

And get:

...or you can do the round trip:

DECLARE @MyHierarchy JSONHierarchy

INSERT INTO @myHierarchy

select * from parseJSON('{"menu": {

  "id": "file",

  "value": "File",

  "popup": {

    "menuitem": [

      {"value": "New", "onclick": "CreateNewDoc()"},

      {"value": "Open", "onclick": "OpenDoc()"},

      {"value": "Close", "onclick": "CloseDoc()"}

    ]

  }

}}')

SELECT dbo.ToJSON(@MyHierarchy)

To get:

{

"menu" :   {

  "id" : "file",

  "value" : "File",

  "popup" :   {

    "menuitem" :   [

        {

        "value" : "New",

        "onclick" : "CreateNewDoc()"

        },

        {

        "value" : "Open",

        "onclick" : "OpenDoc()"

        },

        {

        "value" : "Close",

        "onclick" : "CloseDoc()"

        }

      ]

    }

  }

}

Background

TSQL isn’t really designed for doing complex string parsing, particularly where strings represent nested data structures such as XML, JSON, YAML, or XHTML.

You can do it but it is not a pretty sight; but why would you ever want to do it anyway? Surely, if anything was meant for the 'application layer' in C# or VB.net, then this is it. 'Oh yes', will chime in the application thought police, 'this is far better done in the application or with a CLR.' Not necessarily.

Sometimes, you just need to do something inappropriate in TSQL.

There are a whole lot of reasons why this might happen to you. It could be that your DBA doesn’t allow a CLR, for example, or you lack the necessary skills with procedural code. Sometimes, there isn't any application, or you want to run code unobtrusively across databases or servers.

I needed to interpret or 'shred' JSON data. JSON is one of the most popular lightweight markup languages, and is probably the best choice for transfer of object data from a web page. It is, in fact, executable JavaScript that is very quick to code in the browser in order to dump the contents of a JavaScript object, and is lightning-fast to populate the browser object from the database since you are passing it executable code (you need to parse it first for security reasons - passing executable code around is potentially very risky). AJAX can use JSON rather than XML so you have an opportunity to have a much simpler route for data between database and browser, with less opportunity for error.

The conventional way of dealing with data like this is to let a separate business layer parse a JSON 'document' into some tree structure and then update the database by making a series of calls to it. This is fine, but can get more complicated if you need to ensure that the updates to the database are wrapped into one transaction so that if anything goes wrong, then the whole operation can be rolled back. This is why a CLR or TSQL approach has advantages.

"Sometimes, you just
need to do something
inappropriate in TSQL..."

I wrote the parser as a prototype because it was the quickest way to determine what was involved in the process, so I could then re-write something as a CLR in a .NET language.  It takes a JSON string and produces a result in the form of an adjacency list representation of that hierarchy. In the end, the code did what I wanted with adequate performance (It reads a json file of  540 name\value pairs and creates the SQL  hierarchy table  in 4 seconds) so I didn't bother with the added complexity of maintaining a CLR routine. In order to test more thoroughly what I'd done, I wrote a JSON generator that used the same Adjacency list, so you can now import and export data via JSON!

These markup languages such as JSON and XML all represent object data as hierarchies. Although it looks very different to the entity-relational model, it isn't. It is rather more a different perspective on the same model. The first trick is to represent it as a Adjacency list hierarchy in a table, and then use the contents of this table to update the database. This Adjacency list is really the Database equivalent of any of the nested data structures that are used for the interchange of serialized information with the application, and can be used to create XML, OSX Property lists, Python nested structures or YAML as easily as JSON.

Adjacency list tables have the same structure whatever the data in them. This means that you can define a single Table-Valued  Type and pass data structures around between stored procedures. However, they are best held at arms-length from the data, since they are not relational tables, but something more like the dreaded EAV (Entity-Attribute-Value) tables. Converting the data from its Hierarchical table form will be different for each application, but is easy with a CTE. You can, alternatively, convert the hierarchical table into XML and interrogate that with XQuery.

JSON format.

JSON is designed to be as lightweight as possible and so it has only two structures. The first, delimited by curly brackets, is a collection of name/value pairs, separated by commas. The name is followed by a colon. This structure is generally implemented in the application-level as an object, record, struct, dictionary, hash table, keyed list, or associative array. The other structure is an ordered list of values, separated by commas. This is usually manifested as an array, vector, list, or sequence.

"Using recursion in TSQL is
like Sumo Wrestlers doing Ballet.
It is possible but not pretty."

The first snag for TSQL is that the curly or square brackets are not 'escaped' within a string, so that there is no way of shredding a JSON 'document' simply. It is difficult to  differentiate a bracket used as the delimiter of an array or structure, and one that is within a string. Also, interpreting a string into a SQL String isn't entirely straightforward since hex codes can be embedded anywhere to represent complex Unicode characters, and all the old C-style escaped characters are used. The second complication is that, unlike YAML, the datatypes of values can't be explicitly declared. You have to sniff them out from applying the rules from the JSON Specification.

Obviously, structures can be embedded in structures, so recursion is a natural way of making life easy. Using recursion in TSQL is like Sumo Wrestlers doing Ballet. It is possible but not pretty.

The implementation

Although the code for the JSON Parser/Shredder will run in SQL Server 2005, and even in SQL Server 2000 (with some modifications required), I couldn't resist using a TVP (Table Valued Parameter) to pass a hierarchical table to the function, ToJSON, that produces a JSON 'document'. Writing a SQL Server 2005 version should not be too hard.

First the function replaces all strings with tokens of the form @Stringxx, where xx is the foreign key of the table variable where the strings are held. This takes them, and their potentially difficult embedded brackets, out of the way. Names are  always strings in JSON as well as  string values.

Then, the routine iteratively finds the next structure that has no structure contained within it, (and is, by definition the leaf structure), and parses it, replacing it with an object token of the form '@Objectxxx', or '@arrayxxx', where xxxis the object id assigned to it. The values, or name/value pairs are retrieved from the string table and stored in the hierarchy table. Gradually, the JSON document is eaten until there is just a single root object left.

The JSON outputter is a great deal simpler, since one can be surer of the input, but essentially it does the reverse process, working from the root to the leaves. The only complication is working out the indent of the formatted output string.

In the implementation, you'll see a fairly heavy use of PATINDEX. This uses a poor man's RegEx, a starving man's RegEx. However, it is all we have, and can be pressed into service by chopping the string it is searching (if only it had an optional third parameter like CHARINDEX that specified the index of the start position of the search!). The STUFF function is also a godsend for this sort of string-manipulation work.

IF OBJECT_ID (N'dbo.parseJSON') IS NOT NULL

  DROP FUNCTION dbo.parseJSON

GO

CREATE FUNCTION dbo.parseJSON( @JSON NVARCHAR(MAX))

RETURNS @hierarchy table

(

  element_id int IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */

  parent_id int, /* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */

  object_id int, /* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */

  name nvarchar(2000), /* the name of the object */

  stringvalue nvarchar(4000) NOT NULL, /*the string representation of the value of the element. */

  valuetype nvarchar(100) NOT null /* the declared type of the value represented as a string in stringvalue*/

)

AS

BEGIN

   DECLARE

     @firstobject int, --the index of the first open bracket found in the JSON string

     @opendelimiter int,--the index of the next open bracket found in the JSON string

     @nextopendelimiter int,--the index of subsequent open bracket found in the JSON string

     @nextclosedelimiter int,--the index of subsequent close bracket found in the JSON string

     @type nvarchar(10),--whether it denotes an object or an array

     @nextclosedelimiterChar CHAR(1),--either a '}' or a ']'

     @contents nvarchar(MAX), --the unparsed contents of the bracketed expression

     @start int, --index of the start of the token that you are parsing

     @end int,--index of the end of the token that you are parsing

     @param int,--the parameter at the end of the next Object/Array token

     @endofname int,--the index of the start of the parameter at end of Object/Array token

     @token nvarchar(4000),--either a string or object

     @value nvarchar(MAX), -- the value as a string

     @name nvarchar(200), --the name as a string

     @parent_id int,--the next parent ID to allocate

     @lenjson int,--the current length of the JSON String

     @characters NCHAR(62),--used to convert hex to decimal

     @result BIGINT,--the value of the hex symbol being parsed

     @index SMALLINT,--used for parsing the hex value

     @escape int --the index of the next escape character

   /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped'

    * in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in

    * the JSON string by tokens representing the string

    */

   DECLARE @strings table

   (

     string_id int IDENTITY(1, 1),

     stringvalue nvarchar(MAX)

   )

   /* initialise the characters to convert hex to ascii */

   SELECT

     @characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',

     @parent_id = 0;

   /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */

   WHILE 1 = 1 /* forever until there is nothing more to do */

   BEGIN

     SELECT @start = PATINDEX('%[^a-zA-Z]["]%', @json collateSQL_Latin1_General_CP850_Bin); /* next delimited string */

     IF @start = 0 BREAK /*no more so drop through the WHILE loop */

     IF SUBSTRING(@json, @start+1, 1) = '"'

     BEGIN  /* Delimited name */

      SET @start = @start+1;

      SET @end = PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collateSQL_Latin1_General_CP850_Bin);

     END

     IF @end = 0 /*no end delimiter to last string*/

      BREAK /* no more */

     SELECT @token = SUBSTRING(@json, @start+1, @end-1)

     /* now put in the escaped control characters */

     SELECT @token = REPLACE(@token, from_string, to_string)

     FROM

     (

      SELECT '\"' AS from_string, '"' AS to_string

      UNION ALL

      SELECT '\\', '\'

      UNION ALL

      SELECT '\/', '/'

      UNION ALL

      SELECT '\b', CHAR(08)

      UNION ALL

      SELECT '\f', CHAR(12)

      UNION ALL

      SELECT '\n', CHAR(10)

      UNION ALL

      SELECT '\r', CHAR(13)

      UNION ALL

      SELECT '\t', CHAR(09)

     ) substitutions

     SELECT @result = 0, @escape = 1

     /*Begin to take out any hex escape codes*/

     WHILE @escape > 0

     BEGIN

      /* find the next hex escape sequence */

      SELECT

        @index = 0, 

        @escape = PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collateSQL_Latin1_General_CP850_Bin)

      IF @escape > 0 /* if there is one */

      BEGIN

        WHILE @index < 4 /* there are always four digits to a \x sequence  */

        BEGIN

          /* determine its value */

          SELECT

           @result =

           @result + POWER(16, @index) * (CHARINDEX(SUBSTRING(@token, @escape + 2 + 3 -@index, 1), @characters) - 1), @index = @index+1 ;

          END

          /* and replace the hex sequence by its unicode value */

          SELECT @token = STUFF(@token, @escape, 6, NCHAR(@result))

        END

      END

      /* now store the string away */

      INSERT INTO @strings

      (stringvalue)

      SELECT @token

      /* and replace the string with a token */

      SELECT @json = STUFF(@json, @start, @end + 1, '@string' + CONVERT(nvarchar(5),@@identity))

     END

     /* all strings are now removed. Now we find the first leaf. */

     WHILE 1 = 1  /* forever until there is nothing more to do */

     BEGIN

      SELECT @parent_id = @parent_id + 1

     

      /* find the first object or list by looking for the open bracket */

      SELECT @firstobject = PATINDEX('%[{[[]%', @json collateSQL_Latin1_General_CP850_Bin)  /*object or array*/

      IF @firstobject = 0

        BREAK

      IF (SUBSTRING(@json, @firstobject, 1) = '{')

        SELECT @nextclosedelimiterChar = '}', @type = 'object'

      ELSE

        SELECT @nextclosedelimiterChar = ']', @type = 'array'

     

      SELECT @opendelimiter = @firstobject

      WHILE 1 = 1 --find the innermost object or list...

      BEGIN

        SELECT @lenjson = LEN(@json+'|')-1

        /* find the matching close-delimiter proceeding after the open-delimiter */

        SELECT @nextclosedelimiter = CHARINDEX(@nextclosedelimiterChar, @json,@opendelimiter + 1)

        /* is there an intervening open-delimiter of either type */

        SELECT @nextopendelimiter = PATINDEX('%[{[[]%',RIGHT(@json, @lenjson-@opendelimiter) collate SQL_Latin1_General_CP850_Bin) /*object*/

        IF @nextopendelimiter = 0

          BREAK

       

        SELECT @nextopendelimiter = @nextopendelimiter + @opendelimiter

       

        IF @nextclosedelimiter < @nextopendelimiter

          BREAK

       

        IF SUBSTRING(@json, @nextopendelimiter, 1) = '{'

          SELECT @nextclosedelimiterChar = '}', @type = 'object'

        ELSE

          SELECT @nextclosedelimiterChar = ']', @type = 'array'

       

        SELECT @opendelimiter = @nextopendelimiter

      END

     /* and parse out the list or name/value pairs */

     SELECT @contents = SUBSTRING(@json, @opendelimiter+1, @nextclosedelimiter-@opendelimiter - 1)

     SELECT @json = STUFF(@json, @opendelimiter, @nextclosedelimiter - @opendelimiter +1, '@' + @type + CONVERT(nvarchar(5), @parent_id))

     WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collateSQL_Latin1_General_CP850_Bin)) <  > 0

     BEGIN /* WHILE PATINDEX */

      IF @type = 'object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/

      BEGIN

        SELECT @end = CHARINDEX(':', ' '+@contents) /*if there is anything, it will be a string-based name.*/

        SELECT @start = PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collateSQL_Latin1_General_CP850_Bin) /*AAAAAAAA*/

        SELECT

          @token = SUBSTRING(' '+@contents, @start + 1, @end - @start - 1),

          @endofname = PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin),

          @param = RIGHT(@token, LEN(@token)-@endofname+1)

        SELECT

          @token = LEFT(@token, @endofname - 1),

          @contents = RIGHT(' ' + @contents, LEN(' ' + @contents + '|') - @end - 1)

        SELECT @name = stringvalue

        FROM @strings

        WHERE string_id = @param /*fetch the name*/

      END

      ELSE

      BEGIN

        SELECT @name = null

      END

      SELECT @end = CHARINDEX(',', @contents)  /*a string-token, object-token, list-token, number,boolean, or null*/

      IF @end = 0

        SELECT @end = PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @contents+' 'collate SQL_Latin1_General_CP850_Bin) + 1

      SELECT @start = PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' ' + @contentscollate SQL_Latin1_General_CP850_Bin)

      /*select @start,@end, LEN(@contents+'|'), @contents */

      SELECT

        @value = RTRIM(SUBSTRING(@contents, @start, @end-@start)),

        @contents = RIGHT(@contents + ' ', LEN(@contents+'|') - @end)

    

      IF SUBSTRING(@value, 1, 7) = '@object'

        INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)

        SELECT @name, @parent_id, SUBSTRING(@value, 8, 5),

        SUBSTRING(@value, 8, 5), 'object'

      ELSE

        IF SUBSTRING(@value, 1, 6) = '@array'

          INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)

          SELECT @name, @parent_id, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5),'array'

        ELSE

          IF SUBSTRING(@value, 1, 7) = '@string'

          INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype)

         

          SELECT @name, @parent_id, stringvalue, 'string'

          FROM @strings

          WHERE string_id = SUBSTRING(@value, 8, 5)

         

          ELSE

           IF @value IN ('true', 'false')

             INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype)

             

              SELECT @name, @parent_id, @value, 'boolean'

           ELSE

              IF @value = 'null'

              INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype)

              

              SELECT @name, @parent_id, @value, 'null'

       

              ELSE

               IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin) > 0

                 INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype)

                 SELECT @name, @parent_id, @value, 'real'

               ELSE

                 INSERT INTO @hierarchy (name, parent_id, stringvalue, valuetype)

                 SELECT @name, @parent_id, @value, 'int'       

     END /* WHILE PATINDEX */

   END /* WHILE 1=1 forever until there is nothing more to do */

   INSERT INTO @hierarchy (name, parent_id, stringvalue, object_id, valuetype)

   SELECT '-', NULL, '', @parent_id - 1, @type

   RETURN

END

GO

So once we have a hierarchy, we can pass it to a stored procedure. As the output is an adjacency list, it should be easy to access the data. You might find it handy to create a table type if you are using SQL Server 2008. Here is what I use. (Note that if you drop a Table Valued Parameter type, you will have to drop any dependent functions or procedures first, and re-create them afterwards).

-- Create the data type

IF EXISTS (SELECT * FROM sys.types WHERE name LIKE 'JSONHierarchy')

  DROP TYPE dbo.JSONHierarchy

go

CREATE TYPE dbo.JSONHierarchy AS TABLE

(

   element_id INT NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */

   parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */

   Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */

   NAME NVARCHAR(2000),/* the name of the object, null if it hasn't got one */

   StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */

   ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/

    PRIMARY KEY (element_id)

)

ToJSON. A function that creates JSON Documents

Firstly, we need a simple utility function:

IF OBJECT_ID (N'dbo.parseJSON') IS NOT NULL

   DROP FUNCTION dbo.JSONEscaped

GO

CREATE FUNCTION JSONEscaped ( /* this is a simple utility function that takes a SQL String with all its clobber and outputs it as a sting with all the JSON escape sequences in it.*/

  @Unescaped NVARCHAR(MAX) --a string with maybe characters that will break json

  )

RETURNS NVARCHAR(MAX)

AS

BEGIN

  SELECT  @Unescaped = REPLACE(@Unescaped, FROMString, TOString)

  FROM    (SELECT

            '\"' AS FromString, '"' AS ToString

           UNION ALL SELECT '\', '\\'

           UNION ALL SELECT '/', '\/'

           UNION ALL SELECT  CHAR(08),'\b'

           UNION ALL SELECT  CHAR(12),'\f'

           UNION ALL SELECT  CHAR(10),'\n'

           UNION ALL SELECT  CHAR(13),'\r'

           UNION ALL SELECT  CHAR(09),'\t'

          ) substitutions

RETURN @Unescaped

END

And now, the function that takes a JSON Hierarchy table and converts it to a JSON string.

IF OBJECT_ID (N'dbo.ToJSON') IS NOT NULL

   DROP FUNCTION dbo.ToJSON

GO

CREATE FUNCTION ToJSON

(

      @Hierarchy JSONHierarchy READONLY

)

RETURNS NVARCHAR(MAX)--JSON documents are always unicode.

AS

BEGIN

  DECLARE

    @JSON NVARCHAR(MAX),

    @NewJSON NVARCHAR(MAX),

    @Where INT,

    @ANumber INT,

    @notNumber INT,

    @indent INT,

    @CrLf CHAR(2)--just a simple utility to save typing!

     

  --firstly get the root token into place

  SELECT @CrLf=CHAR(13)+CHAR(10),--just CHAR(10) in UNIX

         @JSON = CASE ValueType WHEN 'array' THEN '[' ELSE '{' END

            +@CrLf+ '@Object'+CONVERT(VARCHAR(5),OBJECT_ID)

            +@CrLf+CASE ValueType WHEN 'array' THEN ']' ELSE '}' END

  FROM @Hierarchy

    WHERE parent_id IS NULL AND valueType IN ('object','array') --get the root element

/* now we simply iterate from the root token growing each branch and leaf in each iteration. This won't be enormously quick, but it is simple to do. All values, or name/value pairs withing a structure can be created in one SQL Statement*/

  WHILE 1=1

    begin

    SELECT @where= PATINDEX('%[^[a-zA-Z0-9]@Object%',@json)--find NEXT token

    if @where=0 BREAK

    /* this is slightly painful. we get the indent of the object we've found by looking backwards up the string */

    SET@indent=CHARINDEX(char(10)+char(13),Reverse(LEFT(@json,@where))+char(10)+char(13))-1

    SET @NotNumber= PATINDEX('%[^0-9]%', RIGHT(@json,LEN(@JSON+'|')-@Where-8)+' ')--find NEXT token

    SET @NewJSON=NULL --this contains the structure in its JSON form

    SELECT @NewJSON=COALESCE(@NewJSON+','+@CrLf+SPACE(@indent),'')

      +COALESCE('"'+NAME+'" : ','')

      +CASE valuetype

        WHEN 'array' THEN '  ['+@CrLf+SPACE(@indent+2)

           +'@Object'+CONVERT(VARCHAR(5),OBJECT_ID)+@CrLf+SPACE(@indent+2)+']'

        WHEN 'object' then '  {'+@CrLf+SPACE(@indent+2)

           +'@Object'+CONVERT(VARCHAR(5),OBJECT_ID)+@CrLf+SPACE(@indent+2)+'}'

        WHEN 'string' THEN '"'+dbo.JSONEscaped(StringValue)+'"'

        ELSE StringValue

       END

     FROM @Hierarchy WHERE parent_id= SUBSTRING(@JSON,@where+8, @Notnumber-1)

     /* basically, we just lookup the structure based on the ID that is appended to the @Object token. Simple eh? */

    --now we replace the token with the structure, maybe with more tokens in it.

    Select @JSON=STUFF (@JSON, @where+1, 8+@NotNumber-1, @NewJSON)

    end

  return @JSON

end

ToXML. A function that creates XML

The function that converts a hierarchy  table to XML gives us a JSON to XML converter. It is surprisingly similar to the previous function

IF OBJECT_ID (N'dbo.ToXML') IS NOT NULL

   DROP FUNCTION dbo.ToXML

GO

CREATE FUNCTION ToXML

(

/*this function converts a JSONhierarchy table into an XML document. This uses the same technique as the toJSON function, and uses the 'entities' form of XML syntax to give a compact rendering of the structure */

      @Hierarchy JSONHierarchy READONLY

)

RETURNS NVARCHAR(MAX)--use unicode.

AS

BEGIN

  DECLARE

    @XMLAsString NVARCHAR(MAX),

    @NewXML NVARCHAR(MAX),

    @Entities NVARCHAR(MAX),

    @Objects NVARCHAR(MAX),

    @Name NVARCHAR(200),

    @Where INT,

    @ANumber INT,

    @notNumber INT,

    @indent INT,

    @CrLf CHAR(2)--just a simple utility to save typing!

     

  --firstly get the root token into place

  --firstly get the root token into place

  SELECT @CrLf=CHAR(13)+CHAR(10),--just CHAR(10) in UNIX

         @XMLasString ='<?xml version="1.0" ?>

@Object'+CONVERT(VARCHAR(5),OBJECT_ID)+'

'

    FROM @hierarchy

    WHERE parent_id IS NULL AND valueType IN ('object','array') --get the root element

/* now we simply iterate from the root token growing each branch and leaf in each iteration. This won't be enormously quick, but it is simple to do. All values, or name/value pairs within a structure can be created in one SQL Statement*/

  WHILE 1=1

    begin

    SELECT @where= PATINDEX('%[^a-zA-Z0-9]@Object%',@XMLAsString)--find NEXT token

    if @where=0 BREAK

    /* this is slightly painful. we get the indent of the object we've found by looking backwards up the string */

    SET@indent=CHARINDEX(char(10)+char(13),Reverse(LEFT(@XMLasString,@where))+char(10)+char(13))-1

    SET @NotNumber= PATINDEX('%[^0-9]%', RIGHT(@XMLasString,LEN(@XMLAsString+'|')-@Where-8)+' ')--find NEXT token

    SET @Entities=NULL --this contains the structure in its XML form

    SELECT @Entities=COALESCE(@Entities+' ',' ')+NAME+'="'

     +REPLACE(REPLACE(REPLACE(StringValue, '<', '&lt;'), '&', '&amp;'),'>', '&gt;')

     + '"' 

       FROM @hierarchy

       WHERE parent_id= SUBSTRING(@XMLasString,@where+8, @Notnumber-1)

          AND ValueType NOT IN ('array', 'object')

    SELECT @Entities=COALESCE(@entities,''),@Objects='',@name=CASE WHEN Name='-' THEN'root' ELSE NAME end

      FROM @hierarchy

      WHERE [Object_id]= SUBSTRING(@XMLasString,@where+8, @Notnumber-1)

   

    SELECT  @Objects=@Objects+@CrLf+SPACE(@indent+2)

           +'@Object'+CONVERT(VARCHAR(5),OBJECT_ID)

           --+@CrLf+SPACE(@indent+2)+''

      FROM @hierarchy

      WHERE parent_id= SUBSTRING(@XMLasString,@where+8, @Notnumber-1)

      AND ValueType IN ('array', 'object')

    IF @Objects='' --if it is a lef, we can do a more compact rendering

         SELECT @NewXML='<'+COALESCE(@name,'item')+@entities+' />'

    ELSE

        SELECT @NewXML='<'+COALESCE(@name,'item')+@entities+'>'

            +@Objects+@CrLf++SPACE(@indent)+'</'+COALESCE(@name,'item')+'>'

     /* basically, we just lookup the structure based on the ID that is appended to the @Object token. Simple eh? */

    --now we replace the token with the structure, maybe with more tokens in it.

    Select @XMLasString=STUFF (@XMLasString, @where+1, 8+@NotNumber-1, @NewXML)

    end

  return @XMLasString

  end

This provides you the means of converting a JSON string into XML

DECLARE @MyHierarchy JSONHierarchy,@xml XML

INSERT INTO @myHierarchy

select * from parseJSON('{"menu": {

  "id": "file",

  "value": "File",

  "popup": {

    "menuitem": [

      {"value": "New", "onclick": "CreateNewDoc()"},

      {"value": "Open", "onclick": "OpenDoc()"},

      {"value": "Close", "onclick": "CloseDoc()"}

    ]

  }

}}')

SELECT dbo.ToXML(@MyHierarchy)

SELECT @XML=dbo.ToXML(@MyHierarchy)

SELECT @XML

This gives the result...

<?xml version="1.0" ?>

<root>

  <menu id="file" value="File">

    <popup>

      <menuitem>

        <item value="New" onclick="CreateNewDoc()" />

        <item value="Open" onclick="OpenDoc()" />

        <item value="Close" onclick="CloseDoc()" />

      </menuitem>

    </popup>

  </menu>

</root>

(1 row(s) affected)

<root><menu id="file" value="File"><popup><menuitem><item value="New" onclick="CreateNewDoc()" /><item value="Open" onclick="OpenDoc()" /><item value="Close" onclick="CloseDoc()" /></menuitem></popup></menu></root>

(1 row(s) affected)

Wrap-up

The so-called 'impedence-mismatch' between applications and databases is, I reckon, an illusion. The object-oriented nested data-structures that we receive from applications are, if the developer has understood the data correctly,  merely a perspective from a particular entity of the relationships it is involved with. Whereas it is easy to shred XML documents to get the data from it to update the database, it has been trickier with other formats such as JSON. By using techniques like this, it should be possible to liberate the application, or website, programmer from having to do the mapping from the object model to the relational, and spraying the database with ad-hoc TSQL  that uses the base tables or updateable views.  If the database can be provided with the JSON, or the Table-Valued parameter, then there is a better chance of  maintaining full transactional integrity for the more complex updates.

The database developer already has the tools to do the work with XML, but why not the simpler, and more practical JSON? I hope these two routines get you started with experimenting with this.