Saving Data (Spring)

When a save is invoked on the Grid on the client side, a request is made to the URL specified in the savehandler attribute. That URL is responsible for parsing the XML we send in the request and performing the necessary inserts, updates and deletes to the persistent data.

In a Spring environment, the URL used as the savehandler will be a Spring Controller. Using the Nitobi Server Library, we can easily parse the XML request and perform the needed database action.

The Controller

We'll begin with the necessary imports and class definition:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

import com.nitobi.server.handler.SaveHandler;
import com.nitobi.server.tools.Record;

public class SaveHandlerController implements Controller {
public ModelAndView handleRequest(HttpServletRequest request,
HttpServletResponse response) throws Exception
{

Next, we'll set up the connection to the database. This sample uses the sample data found in NitobiSamplesData.sql in the samples directory of your Complete UI installation.

Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/nitobi_testdb_v1?" +
"user=nitobi");
Statement st = conn.createStatement();

As stated above, we need to be able to parse the XML in the request to figure out what we need to persist to the database. We can do this using the com.nitobi.server.handler.SaveHandler class

SaveHandler handler = new SaveHandler(request);

SaveHandler has methods to get the records that need to be inserted (getInsertRecords()), updated (getUpdateRecords()), and deleted(getDeleteRecords()). Let's start with the records to insert:

Record[] insertRecords = handler.getInsertRecords();
  for (int i=0; i< insertRecords.length; i++) {
   String sql="INSERT INTO tblContacts3k (ContactName, ContactEmail, JobTitle, CompanyName, PhoneNumber, Address) VALUES (";
   sql+= "'" + insertRecords[i].getField("ContactName").replaceAll("'","''") + "',";
   sql+= "'" + insertRecords[i].getField("ContactEmail").replaceAll("'","''") + "',";
   sql+= "'" + insertRecords[i].getField("JobTitle").replaceAll("'","''")   + "',";
   sql+= "'" + insertRecords[i].getField("CompanyName").replaceAll("'","''") + "',";
   sql+= "'" + insertRecords[i].getField("PhoneNumber").replaceAll("'","''") + "',";
   sql+= "'" + insertRecords[i].getField("Address").replaceAll("'","''")   + "'";
   sql+= "); ";
   st.executeUpdate(sql);
  }

From SaveHandler#getInsertRecords, we get an array of com.nitobi.server.tools.Record objects. The fields of the Record object correspond with the fields in the Grid on the client side. Using the Record array, we construct the appropriate SQL statement. Updates and deletes are processed similarly, as demonstrated below:

Record[] updateRecords = handler.getUpdateRecords();
  for (int i=0; i< updateRecords.length; i++) {
   String sql = "UPDATE tblContacts3k ";
   sql+= "SET ContactName = '" + updateRecords[i].getField("ContactName").replaceAll("'","''") + "', ";
   sql+= "ContactEmail = '" + updateRecords[i].getField("ContactEmail").replaceAll("'","''") + "', ";
   sql+= "JobTitle   = '" + updateRecords[i].getField("JobTitle").replaceAll("'","''")   + "', ";
   sql+= "CompanyName   = '" + updateRecords[i].getField("CompanyName").replaceAll("'","''") + "', ";
   sql+= "PhoneNumber   = '" + updateRecords[i].getField("PhoneNumber").replaceAll("'","''") + "', ";
   sql+= "Address    = '" + updateRecords[i].getField("Address").replaceAll("'","''")   + "', ";
   sql += "Country = '" + updateRecords[i].getField("Country").replaceAll("'", "''") + "' ";
   sql+=" WHERE ContactID = " + updateRecords[i].getID();
   st.executeUpdate(sql);
  }
  Record[] deleteRecords = handler.getDeleteRecords();
  for (int i=0; i< deleteRecords.length; i++) {
   String sql = "DELETE FROM tblContacts3k WHERE ContactID = " + deleteRecords[i].getID();
   st.executeUpdate(sql);
  }

The last thing we need to do is call SaveHandler#writeToClient:

handler.writeToClient(response);
st.close();
conn.close();
return null;

N.B. We return null because we want to return the response directly back to the client without forwarding to another control.

Full Source

package examples.grid.spring;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

import com.nitobi.server.handler.SaveHandler;
import com.nitobi.server.tools.Record;

public class SaveHandlerController implements Controller {
public ModelAndView handleRequest(HttpServletRequest request,
   HttpServletResponse response) throws Exception
{
  SaveHandler handler = new SaveHandler(request);
  Class.forName("com.mysql.jdbc.Driver").newInstance();
  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/nitobi_testdb_v1?" +
               "user=nitobi");
  Statement st = conn.createStatement();
  Record[] insertRecords = handler.getInsertRecords();
  for (int i=0; i< insertRecords.length; i++) {
   String sql="INSERT INTO tblContacts3k (ContactName, ContactEmail, JobTitle, CompanyName, PhoneNumber, Address) VALUES (";
   sql+= "'" + insertRecords[i].getField("ContactName").replaceAll("'","''") + "',";
   sql+= "'" + insertRecords[i].getField("ContactEmail").replaceAll("'","''") + "',";
   sql+= "'" + insertRecords[i].getField("JobTitle").replaceAll("'","''")   + "',";
   sql+= "'" + insertRecords[i].getField("CompanyName").replaceAll("'","''") + "',";
   sql+= "'" + insertRecords[i].getField("PhoneNumber").replaceAll("'","''") + "',";
   sql+= "'" + insertRecords[i].getField("Address").replaceAll("'","''")   + "'";
   sql+= "); ";
   st.executeUpdate(sql);
  }
  Record[] updateRecords = handler.getUpdateRecords();
  for (int i=0; i< updateRecords.length; i++) {
   String sql = "UPDATE tblContacts3k ";
   sql+= "SET ContactName = '" + updateRecords[i].getField("ContactName").replaceAll("'","''") + "', ";
   sql+= "ContactEmail = '" + updateRecords[i].getField("ContactEmail").replaceAll("'","''") + "', ";
   sql+= "JobTitle   = '" + updateRecords[i].getField("JobTitle").replaceAll("'","''")   + "', ";
   sql+= "CompanyName   = '" + updateRecords[i].getField("CompanyName").replaceAll("'","''") + "', ";
   sql+= "PhoneNumber   = '" + updateRecords[i].getField("PhoneNumber").replaceAll("'","''") + "', ";
   sql+= "Address    = '" + updateRecords[i].getField("Address").replaceAll("'","''")   + "', ";
   sql += "Country = '" + updateRecords[i].getField("Country").replaceAll("'", "''") + "' ";
   sql+=" WHERE ContactID = " + updateRecords[i].getID();
   st.executeUpdate(sql);
  }
  Record[] deleteRecords = handler.getDeleteRecords();
  for (int i=0; i< deleteRecords.length; i++) {
   String sql = "DELETE FROM tblContacts3k WHERE ContactID = " + deleteRecords[i].getID();
   st.executeUpdate(sql);
  }
  st.close();
  conn.close();
  handler.writeToClient(response);
  return null;
}
}

View Printable Version

Nitobi Product Knowledgebase