Java Android: 2015

Dojo Database Query Table

Lets show the result of a database in a dojo table.

The front end will be developed in jsp page by using html+jsp+dojo framework+ajax

The back end will be developed in java servlet + jdbc + mysql + json

Take the query from a input box then refresh the grid changing with the json data accordingly.

Create your jsp page

1 ) Define your dojo amd

data-dojo-config="async:true, parseOnLoad:true">

2 ) Link to claro style

3 ) give body class

4 ) Import these

require(["dijit/form/Button""dijit/form/TextBox","dijit/registry"]);

</script>

5 ) Place your table div

<input type="text" name="user_type_query"

data-dojo-type="dijit/form/TextBox"

data-dojo-props="trim:true, uppercase:true" id="user_type_query">

Submit

</button>

6 ) Define the datagrid to be placed on our div.

var grid, dataStore, store;

require([

"dojox/grid/DataGrid",

"dojo/store/Memory",

"dojo/data/ObjectStore",

"dojo/request",

"dojo/domReady!"

], function(DataGrid, Memory, ObjectStore,request){

request.get("x.json", {

handleAs: "json"

}).then(function(data){

store = new Memory({ data: data.items });

dataStore = new ObjectStore({ objectStore: store });

grid = new DataGrid({

store: dataStore,

query: { id: "*" },

structure: []

}, "grid");

// you can enhance here even you can place a glittering div to enjoy //your users

grid.startup();

grid.on("RowClick", function(evt){

alert(grid.getItem(evt.rowIndex).first+"/"+evt.rowIndex);

}, true);

});

7 ) Now grid is okay lets code our function

//Even we can do all inside the dojo function but to make it more readable i chosed to code outside.

//To code them all inside the dojo, you can use the syntax of calling the dojo function out of dojo from javascript as below

runQuery() = function()

{

//Do whatever you want

}

function runQuery()

{

var user_type_query = document.getElementById("user_type_query").value;

refresh(user_type_query);

}

function refresh(query)

{

grid.store.close();//If you do not call this function you have to change the new grid id, in order to achieve that you have to remove the div from that dom and you have to re-define that div by appending that dom, it is time consuming

require([

"dojox/grid/DataGrid",

"dojo/store/Memory",

"dojo/data/ObjectStore",

"dojo/request",

"dojo/domReady!"

], function(DataGrid, Memory, ObjectStore,request){

request.get("http://localhost:8080/Mying/MyQuery?query="+query, {

handleAs: "json"

}).then(function(data){

//here the json result will be received

//to import the values i had to split them into meaningfull pieces

//example json

{"u_name":"Can1234","u_id":"5","u_address":"Can","u_updated_by":"1","u_update_reason":"Create","u_update_time":"2015-11-28 15:27:59.0","u_password":"can","u_type":"1","u_telephone":"12313"}

var datas = data;

var gridLayout = [];

var gridLayout1 = [];

var counter = 0;

var key, i;

//here we split up the keys and values in json

for (var event in data) {

var dataCopy = data[event];

for (data in dataCopy) {

var mainData = dataCopy[data];

for (key in mainData)

{

counter = counter + 1;

if(gridLayout1.indexOf(key) == -1)

{

gridLayout1[counter] = key;

}

//Here we are recreating the array of our store

for(i = 1; i < gridLayout1.length; i++){

key = i + "";

gridLayout.push({

field: gridLayout1[i],

name: gridLayout1[i],

editable: false});

}

//we import our store to our structure

// Call startup, in order to render the grid:

grid.setStructure(gridLayout);

store = new Memory({ data: datas.items });

dataStore = new ObjectStore({ objectStore: store });

grid.store = dataStore;

grid.render();

grid.startup();

});

}

8 ) The query will be sent and the code will wait fort he json response from server in above code Now place your Servlet

If you create your servlet in your eclipse no new definition will be required in your web.xml file

Otherwise you have to define fort he path

<display-name>MyQuery</display-name>

<servlet-name>MyQuery</servlet-name>

<servlet-class>MyQuery</servlet-class>

</servlet>

Below

import java.io.IOException;

import java.sql.Connection;

import java.sql.SQLException;

import java.util.ArrayList;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.richfaces.json.JSONArray;

import org.richfaces.json.JSONException;

import org.richfaces.json.JSONObject;

import com.mying.dao.operations.DbOp;

import com.ic.database.DbConnection;

/**

* Servlet implementation class MyQuery

public class MyQuery extends HttpServlet {

private static final long serialVersionUID = 1L;

/**

* @see HttpServlet#HttpServlet()

public MyQuery() {

super();

}

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

String query = request.getParameter("query");

String ch = "",nm="";

DbOp db = new DbOp();

ArrayList<String> sts = new ArrayList<String>();

try {

DbConnection instance = DbConnection.getInstance();

Connection conn = instance.fetchConnection();

ArrayList<String> doQuery = db.doQuery(query, conn);

for(int i = 0 ; i < doQuery.size() ; i++)

{

nm = "";

String[] numChar = doQuery.get(i).split("/");

for(int k = 1 ; k < numChar.length ; k++)

{

String[] num_char = numChar[k].split("%");

if(i == 0)

{

ch = ch + num_char[0]+"!";

}

nm = nm + num_char[1]+"!";

}

// nm=nm+"*";

sts.add(nm);

}

} catch (SQLException e) {

}

String[] split = ch.split("!");
//Lets define our json object to be sent

response.setContentType("application/json");

JSONObject json = new JSONObject();

JSONArray queries = new JSONArray();

JSONObject query = new JSONObject();

for (int i=0 ; i<sts.size() ; i++)

{

String[] splitted = sts.get(i).split("!");

json = new JSONObject();

for(int k = 0 ; k <splitted.length-1 ; k++)

{

try {

json.put(split[k], splitted[k]);

} catch (JSONException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

queries.put(json);

}

try {

query.put("items", queries);

} catch (JSONException e) {

e.printStackTrace();

}

response.setContentType("application/json");

response.getWriter().write(query.toString());

}

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

}

In the data access layer part this function will be executed

public ArrayList<String> doQuery(String query,Connection conn)

{

ArrayList<String> strs = new ArrayList<String>();

try

{

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery(query);

while (rs.next())

{

String str = "";

ResultSetMetaData rsmd = rs.getMetaData();

int columnCount = rsmd.getColumnCount();

for(int i = 1 ; i < columnCount ; i++)

{

String name = rsmd.getColumnName(i);

int columnType = rsmd.getColumnType(i);

//int in = rs.getInt(i);

String string = rs.getString(i);

str = str +"/"+ name+"%"+string;

}

strs.add(str);

}

st.close();

}

catch (Exception e)

{

System.err.println("doQuery Error : "+e.toString());

}

finally

{

}

return strs;

}

Just look at the columns they all change in regards to your query

Here you can see different columns in accordance with your different query

Wednesday, December 16, 2015