I try to make server - client connection, using Java Restful, JqGRID and SQL Server. I have a problem with my jqGrid. In my grid I have 9 columns, but when I execute, the grid return:
{"firstname": "Jojo ","surname":"Bobo","chefdepartment":"No","position":" Position 3","joinyear":"09/17/2015","oper":"add","id":"jqg1"}
whithout the values for username, email and experience, and in my database (SQL server) will return:
values('"Jojo "' , '"Bobo"' , 'null' , 'null' , '"No"' , '" Position 3"' , '"09/17/2015"' , 'null')
, so return "null" for username, email and experience.
I use getCell, for take the values like this:
firstname = grid.jqGrid('getCell', Id, "firstname");
surname = grid.jqGrid('getCell', Id, "surname");
username = grid.jqGrid('getCell', Id, "username");
username = firstname + "." + surname;
email = username + "@test.com";
jqGRID code:
grid.jqGrid({
data: mydata,
mtype:'GET',
datatype:'json',
url:'http://localhost:8080/JDBCapplication/app/test/get',
ajaxGridOptions: { contentType: 'application/json; charset=utf-8'},
serializeGridData: function (postdata) {
return JSON.stringify(postdata);
},
jsonReader : {
root:"rows",
page: "page",
total: "total",
records: "records",
cell: "",
repeatitems: false,
},
autowidth: true,
height: 200,
editurl:'http://localhost:8080/JDBCapplication/app/test/post',
gridview: true,
colModel: [
{name: 'id', label: 'Id', width: 150, align: 'center', search:false},
{name: 'firstname', label: 'Firstname', align:'center', editable: true, searchoptions:{sopt:['eq','ne','bw','ew','cn','nc']}, editoptions: {defaultValue: ' '},formatter: 'text'},
{name: 'surname', label: 'Surname', align:'center', editable: true, searchoptions:{sopt:['eq','ne','bw','ew','cn','nc']}, editoptions: {defaultValue: ' '},formatter: 'text'},
{name: 'username', label: 'Username', align:'center', searchoptions:{sopt:['eq','ne','bw','ew','cn','nc']}},
{name: 'email', label: 'Email', align:'center', searchoptions:{sopt:['eq','ne','bw','ew','cn','nc']}},
{name: 'chefdepartment', label: 'Chef Department', editable: true, align: 'center',
edittype: "checkbox", formatter: chefdepFunc, editoptions:{ dataEvents: checkboxEdit, value: "Yes:No", defaultValue: 'No'},
stype:'select', searchoptions:{value: "Yes:Yes; No:No",sopt:['eq','ne']}
},
{name: 'position', label: 'Position', editable: true, align: 'center',
edittype: "select", formatter: 'select', editoptions:{value: ' : ; Position 1:Position 1; Position 2:Position 2; Position 3:Position 3; Position 4:Position 4; Position 5:Position 5'},
stype: 'select', searchoptions:{value: ' : ; Position 1:Position 1; Position 2:Position 2; Position 3:Position 3; Position 4:Position 4; Position 5:Position 5', sopt:['eq','ne']}
},
{name: 'joinyear', label : 'Join Year', align: 'center', editable: true, editoptions: { size: 20, maxlengh: 10,
dataInit: function(e)
{ $(e).datepicker({ dateFormat: 'mm/dd/yy' });
} },
searchtype:'datepicker', searchoptions:{size: 20, maxlengh: 10,
dataInit: function(e)
{ $(e).datepicker({ dateFormat: 'mm/dd/yy' });
}, sopt:['eq','ne']}
},
{name: 'experience', label: 'Experience', searchoptions:{sopt:['eq','ne']}, align: 'center'},
{name: 'actions', label: 'Actions', search: false,
formatter: 'actions', formatoptions:{
afterSave: myFunction,
}}
],
rowNum: 10,
rowList:[5,10,15],
pager: '#pager',
viewrecords: true,
loadonce:true,
sortname: 'id',
caption: "Personal Informations",
inlineEditing: {
ajaxSaveOptions: { contentType: "application/json" },
serializeSaveData: function (postData) {
return JSON.stringify(postData);
}
}
I tried using postData like this (but without any luck):
postData:{
username: function(){return grid.jqGrid('getCell', Id, "username")} ,
email: function(){return grid.jqGrid('getCell', Id, "email")} ,
experience: function(){return grid.jqGrid('getCell', Id, "experience")} ,
},
Java code for POST method:
@POST
@Path("/post")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public void addElement(String data) throws SQLException{
System.out.println(data);
JsonParser parser = new JsonParser();
JsonObject addElem =(JsonObject) parser.parse(data);
String sql = "insert into InternshipBC.dbo.EMPLOYEES values('" + addElem.get("firstname") + "' , '" + addElem.get("surname") + "' , '" + addElem.get("username") + "' , '"
+ addElem.get("email") + "' , '" + addElem.get("chefdepartment") + "' , '" + addElem.get("position") + "' , '" + addElem.get("joinyear") + "' , '" + addElem.get("experience") + "')";
executeQuery(sql);
}
public ResultSet executeQuery(String sql){
ResultSet result = null;
Statement st = null;
try {
conn = DriverManager.getConnection(DB_URL,USER,PASS);
st = conn.createStatement();
result = st.executeQuery(sql);
conn.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
return result;
}
Any suggestion would be appreciated. Thanks.
Now I use free jqGrid 4.11.0.
jqGrid code:
grid.jqGrid({
mtype:'GET',
datatype:'json',
url:'http://localhost:8080/JDBCapplication/app/test/get',
ajaxGridOptions: { contentType: 'application/json; charset=utf-8'},
serializeGridData: function (postdata) {
return JSON.stringify(postdata);
},
jsonReader : {
root:"rows",
page: "page",
total: "total",
records: "records",
cell: "",
repeatitems: false,
},
autowidth: true,
height: 200,
editurl:'http://localhost:8080/JDBCapplication/app/test/post',
gridview: true,
colModel: [
{name: 'id', label: 'Id', width: 150, align: 'center',hidden:true, search:false},
{name: 'firstname', label: 'Firstname', align:'center', editable: true, searchoptions:{sopt:['eq','ne','bw','ew','cn','nc']}, editoptions: {defaultValue: ' '},formatter: 'text'},
{name: 'surname', label: 'Surname', align:'center', editable: true, searchoptions:{sopt:['eq','ne','bw','ew','cn','nc']}, editoptions: {defaultValue: ' '},formatter: 'text'},
{name: 'username', label: 'Username', align:'center', editable: "hidden", searchoptions:{sopt:['eq','ne','bw','ew','cn','nc']}},
{name: 'email', label: 'Email', align:'center', editable: "hidden", searchoptions:{sopt:['eq','ne','bw','ew','cn','nc']}},
{name: 'chefdepartment', label: 'Chef Department', editable: true, align: 'center',
edittype: "checkbox", formatter: chefdepFunc, editoptions:{ dataEvents: checkboxEdit, value: "Yes:No", defaultValue: 'No'},
stype:'select', searchoptions:{value: "Yes:Yes; No:No",sopt:['eq','ne']}
},
{name: 'position', label: 'Position', editable: true, align: 'center',
edittype: "select", formatter: 'select', editoptions:{value: ' : ; Position 1:Position 1; Position 2:Position 2; Position 3:Position 3; Position 4:Position 4; Position 5:Position 5'},
stype: 'select', searchoptions:{value: ' : ; Position 1:Position 1; Position 2:Position 2; Position 3:Position 3; Position 4:Position 4; Position 5:Position 5', sopt:['eq','ne']}
},
{name: 'joinyear', label : 'Join Year', align: 'center', editable: true, editoptions: { size: 20, maxlengh: 10,
dataInit: function(e)
{ $(e).datepicker({ dateFormat: 'mm/dd/yy' });
} },
searchtype:'datepicker', searchoptions:{size: 20, maxlengh: 10,
dataInit: function(e)
{ $(e).datepicker({ dateFormat: 'mm/dd/yy' });
}, sopt:['eq','ne']}
},
{name: 'experience', label: 'Experience', editable: "hidden", editoptions: {disabled: true} , searchoptions:{sopt:['eq','ne']}, align: 'center'},
{name: 'actions', label: 'Actions', search: false,
formatter: 'actions', formatoptions:{
afterSave: myFunction,
}}
],
rowNum: 10,
rowList:[5,10,15],
pager: '#pager',
viewrecords: true,
loadonce:true,
sortname: 'id',
caption: "Personal Informations",
inlineEditing: {
ajaxSaveOptions: { contentType: "application/json" },
serializeSaveData: function (postData) {
postData.username= grid.jqGrid("getCell", postData.id,"username"),
postData.email= grid.jqGrid("getCell", postData.id,"email") ,
postData.experience= grid.jqGrid("getCell", postData.id, "experience");
return JSON.stringify(postData);
}
},
});
grid.jqGrid('navGrid', '#pager',{add:false, search: true, searchtext: "Search", refresh: true, refreshtext: "Refresh", del: true, deltext: "Remove", edit: false, save: false, cancel: false},
{},{},{
url:'http://localhost:8080/JDBCapplication/app/test/del',
mtype: 'DELETE',
reloadAfterSubmit: true,
ajaxDeleteOptions:{contentType: "application/json"},
serializeDeleteData: function(postData){
return JSON.stringify(postData);
}
},{},{},{},{});
grid.jqGrid('inlineNav', '#pager',{
add: true,
addicon: "ui-icon-plus",
addtext: "Add",
addParams:{
addRowParams:{
aftersavefunc: myFunction,
keys: true,
},
position:"last",
},
edit: true,
editicon: "ui-icon-pencil",
edittext: "Edit",
editParams:{
url: 'http://localhost:8080/JDBCapplication/app/test/edit',
mtype: 'POST',
keys: true,
aftersavefunc: myFunction,
},
save: true,
saveicon: "ui-icon-disk",
savetext: "Save",
cancel: true,
cancelicon: "ui-icon-cancel",
canceltext: "Cancel",
search: false,
refresh: false,
del:false
});
});
$(function(){
var grid = $("#grid");
var myFunction = function(Id){
var firstname, surname, username, email;
firstname = grid.jqGrid('getCell', Id, "firstname");
surname = grid.jqGrid('getCell', Id, "surname");
username = grid.jqGrid('getCell', Id, "username");
if((firstname=="")&&(surname==""))
{
username="";
email="";
}
else
{
username = firstname + "." + surname;
email = username + "@test.com";
}
grid.jqGrid('setCell', Id, "username", username);
grid.jqGrid('setCell', Id, "email", email);
checkboxEdit = [
{type : 'click', data: {'Yes': 'Yes'}, fn: function (e, id) {
var checkbox = grid.getCol('chefdepartament'),
ids = grid.jqGrid('getDataIDs'),
totalOfCheckedBoxes = [],
k;
for (k = 0; k < ids.length; k++) {
if(checkbox[k] == 'Yes'){
totalOfCheckedBoxes.push(checkbox[k]);
if (totalOfCheckedBoxes.length == 1){
alert('Chef Deparment already exist!');
$(this).prop("checked",false);
totalOfCheckedBoxes = 0;
}
}
}
}}];
var chefdepFunc = function(cellvalue, options ,rowObject){
if(cellvalue == 'Yes'){
return 'Yes';
}
return 'No';
};
JAVA code:
@GET
@Path("/get")
@Produces(MediaType.APPLICATION_JSON)
public ArrayList<Employee>getEmployee() throws SQLException{
ArrayList<Employee>collection = new ArrayList<Employee>();
String sql = "select * from InternshipBC.dbo.EMPLOYEES";
ResultSet result = executeSelect(sql);
try{
while(result.next()){
Employee employee = new Employee();
employee.setId(result.getInt("id"));
employee.setFirstname(result.getString("firstname"));
employee.setSurname(result.getString("surname"));
employee.setUsername(result.getString("username"));
employee.setEmail(result.getString("email"));
employee.setChefdepartment(result.getString("chefdepartment"));
employee.setPosition(result.getString("position"));
employee.setExperience(result.getString("experience"));
collection.add(employee);
}
}catch(Exception e){
System.out.println(e.getMessage());
}
return collection;
}
@POST
@Path("/post")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public void addElement(String data) throws SQLException{
System.out.println(data);
JsonParser parser = new JsonParser();
JsonObject addElem =(JsonObject) parser.parse(data);
String sql = "insert into InternshipBC.dbo.EMPLOYEES values('" + addElem.get("firstname") + "' , '" + addElem.get("surname") + "' , '" + addElem.get("username") + "' , '"
+ addElem.get("email") + "' , '" + addElem.get("chefdepartment") + "' , '" + addElem.get("position") + "' , '" + addElem.get("joinyear") + "' , '" + addElem.get("experience")+ "')";
sql = sql.toString().replaceAll("\"", "");
executeQuery(sql);
}
public void uniqueID() throws SQLException{
String sql = "INSERT INTO InternshipBC.dbo.EMPLOYEES (firstname, surname, username, email, chefdepartment, position, joinyear, experience) values (?,?,?,?,?,?,?,?)";
PreparedStatement pst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pst.setString(1, "firstname");
pst.setString(2, "surname");
pst.setString(3, "username");
pst.setString(4, "email");
pst.setString(5, "chefdepartment");
pst.setString(6, "position");
pst.setString(7, "joinyear");
pst.setString(8, "experience");
int affectedRows = pst.executeUpdate();
if(affectedRows == 0){
throw new SQLException("Creating user failed, no rows afected!");
}
ResultSet result = pst.getGeneratedKeys();
if (result != null && result.next()) {
int id = result.getInt("id");
System.out.println("Generated id :" + result.getInt("id"));
}
}
@POST
@Path("/edit")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public void editElement(String data) throws SQLException{
System.out.println(data);
JsonParser parser = new JsonParser();
JsonObject editElem =(JsonObject) parser.parse(data);
String sql = "Update InternshipBC.dbo.EMPLOYEES set firstname = '" + editElem.get("firstname")+ "', surname = '" + editElem.get("surname") + "', username = '" + editElem.get("username") + "', email = '" + editElem.get("email") + "', chefdepartment = '"+ editElem.get("chefdepartment") + "', position = '" + editElem.get("position") + "', joinyear = '" + editElem.get("joinyear") + "', experience = '" + editElem.get("experience")+ "', where id = '"+ editElem.get("id") + "'";
executeQuery(sql);
}
@POST
@Path("/del")
@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
public void deleteElement(String data){
System.out.println(data);
JsonParser parser = new JsonParser();
JsonObject delElem =(JsonObject) parser.parse(data);
String sql = "delete from InternshipBC.dbo.EMPLOYEES where id = '" + delElem.get("id")+ "'";
executeQuery(sql);
}
public ResultSet executeQuery(String sql){
ResultSet result = null;
Statement st = null;
try {
conn = DriverManager.getConnection(DB_URL,USER,PASS);
st = conn.createStatement();
result = st.executeQuery(sql);
conn.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
return result;
}
public ResultSet executeSelect(String sql){
ResultSet result = null;
Statement st = null;
try {
conn = DriverManager.getConnection(DB_URL,USER,PASS);
st = conn.createStatement();
result = st.executeQuery(sql);
conn.close();
}catch(Exception e){
System.out.println(e.getMessage());
}
return result;
}
}
When I run the application, in my database (Sql sever), the HTTP POST Request :204 No Content will return:
{"firstname": "Jojo ","surname":"Bobo","username": "","email": "","chefdepartment":"No","position":" Position 3","joinyear":"01/22/2016","experience": ""}
In my console for jqGrid if I execute :
console printscreen for jqGrid
What I want is that the http POST Request to return in database the all values.
