spring jdbc and action class

[manjiri chunekar]

i m doing assignments on struts and spring...
i created db in ms access and DSN id myDSN...
created 3 buttons delete,create, update
created 3 text box: cust_id, cust_fname, cust_lname...
applicationConfig.xml
struts-config.xml
Cust is form bean
CustomerDAo
CustomerAction

what can i do... i m trying.... but not success yet... HELP ME..

[Vijitha Kumara]

Welcome to JavaRanch, Manjiri Chunekar

Please don't duplicate posts (duplicate) in the forum.
Spring and Struts both are web frameworks. You may use some features from Spring and some from Struts in the same application, but that's very unlikely. But if you are a beginner to both of these you may start with the getting started guide provided by both frameworks. I suggest not to go for DAO implementations etc... at the beginning.

[manjiri chunekar]

thanks.....
ok... i wont duplicate post again..
will take care of that....
i done small assignments on struts, spring.... like struts-dispatch,bean etc. AND spring injection,little bit on spring MVC, spring jdbc...
So, i m trying this.....

[Vijitha Kumara]

Ok. Then please post some specific questions/issues you are having probably with some code which would help others to help you.

[manjiri chunekar]

exception

javax.servlet.ServletException: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [INSERT INTO Cust (Cust_Id, Cust_Fname, Cust_Lname) VALUES (?, ?, ?)]; nested exception is java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect
org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:286)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)


root cause

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [INSERT INTO Cust (Cust_Id, Cust_Fname, Cust_Lname) VALUES (?, ?, ?)]; nested exception is java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:111)
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:458)
org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)
org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:497)
com.CustomerDAO.Add(CustomerDAO.java:29)
com.CustomerAction.create(CustomerAction.java:86)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)


root cause

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect
sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source)
org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:443)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:458)
org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:466)
org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:497)
com.CustomerDAO.Add(CustomerDAO.java:29)
com.CustomerAction.create(CustomerAction.java:86)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)



My db is in ms-access
DSN is myDSN

Help me....

[Prasad Krishnegowda]

Ca you show us your code, so we can see what query you are using to insert into database and help you better, also let us know the data types of your columns in the database.

[manjiri chunekar]

CustomerAction

package com;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.Customer;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

public class CustomerAction extends DispatchAction {
	
	private CustomerDAO obj = null;
	private final static String SUCCESS = "success"; 	
	List l = new ArrayList();
	
 public ActionForward common(ActionMapping mapping,
			ActionForm form,HttpServletRequest request,
			HttpServletResponse response)       
	throws Exception {
    	 
WebApplicationContext context = WebApplicationContextUtils.getRequiredWebApplicationContext(request.getSession().getServletContext());

obj=(CustomerDAO)context.getBean("CustomerDAO");

return mapping.findForward(SUCCESS); 
 }

 public ActionForward loop(ActionMapping mapping,
			ActionForm form,HttpServletRequest request,
			HttpServletResponse response)       
	throws Exception {
 
	 Customer customer = (Customer)form;		

for(int i=0;i<l.size();i++)
{
	Map m =(Map)l.get(i);
	
	int custid = ((Number) m.get("Cust_Id")).intValue();
	customer.setCust_id(custid);

	String firstnm = (m.get("Cust_Fname")).toString();
	customer.setFirst_name(firstnm);
	

	String lastnm = (m.get("Cust_Lname")).toString();
	customer.setLast_name(lastnm);
}
 
return mapping.findForward(SUCCESS); 	
}
        
 // METHOD FOR ADD
		public ActionForward view(ActionMapping mapping, ActionForm form,           
		HttpServletRequest request, HttpServletResponse response)     
		throws Exception { 
			
			CustomerAction caction1 = new CustomerAction();
			caction1.common(mapping, form, request, response);
			caction1.obj.Select();
			caction1.loop(mapping, form, request, response);
			Customer customer = (Customer)form;		
			customer.setMessage("NEW CUSTOMER IS CREATED.."); 
		    return mapping.findForward(SUCCESS); 
		} 
   
		//METHOD FOR UPDATE
		public ActionForward create(ActionMapping mapping,
				ActionForm form,HttpServletRequest request,
				HttpServletResponse response)       
		throws Exception { 
			
			CustomerAction caction2 = new CustomerAction();
			caction2.common(mapping, form, request, response);
			caction2.obj.Add();
			caction2.loop(mapping, form, request, response);
		   Customer customer = (Customer)form; 
		   customer.setMessage("CUSTOMER IS UPDATED.."); 
            return mapping.findForward(SUCCESS); 

		} 
	  
		//METHOD FOR DELETE
		public ActionForward delete(ActionMapping mapping,
				ActionForm form, HttpServletRequest request,
				HttpServletResponse response) 
         throws Exception { 
            
			CustomerAction caction3 = new CustomerAction();
			caction3.common(mapping, form, request, response);
			caction3.obj.Delete();
			caction3.loop(mapping, form, request, response);
			Customer customer = (Customer)form;        
			customer.setMessage("CUSTOMER IS DELETED.."); 
        return mapping.findForward(SUCCESS); 

		} 
	}

CustomerDAO

package com;

import java.util.*;
import javax.sql.*;
import org.springframework.jdbc.core.JdbcTemplate;


public class CustomerDAO {

	private JdbcTemplate jdbctemplate;
		
		public void setDataSource(DataSource ds){
			try {
				 this.jdbctemplate = new  JdbcTemplate(ds); 
				}
				catch (Exception e) { 
				 System.out.println(""+e.getMessage()); 
				}
		}
	
		public List Select(){
			String sql1 = "select * from Cust";
			List cust = jdbctemplate.queryForList(sql1);
			return cust;
		}
		
		public List Add(){
		   String sql2 = "INSERT INTO Cust (Cust_Id, Cust_Fname, Cust_Lname) VALUES (?, ?, ?)";
		   List cust = jdbctemplate.queryForList(sql2);
		   return cust;
		}
		
		public List Delete(){
			String sql3 = "select * from Cust where cust_id = ?";
			List cust = jdbctemplate.queryForList(sql3);
			return cust;
		} 
		}

index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    
    <%@taglib uri="/WEB-INF/lib/struts-html.tld" prefix="html" %>
    <%@taglib uri="/WEB-INF/lib/struts-bean.tld" prefix="bean" %>
       
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<script type="text/javascript"> 
04.function submitForm() 
05.{ 
06.document.forms[0].action = "CustomerAction.do?method=add" 
07.document.forms[0].submit(); 
08.} 
09.</script>
</head>
<body>
<html:form action="CustomerAction.do"> 
<table> 
<tr>   
<td> 
<bean:write name="CustomerForm" property="message"/>  
</td> 
</tr> 
<tr> 
<td>         
Customer ID:<html:text property="cust_id"></html:text> 
</td> 
</tr> 
<tr>  
<td>         
Customer First Name:<html:text property="first_name"></html:text>
</td> 
</tr> 
<tr>  
<td>         
Customer Last Name:<html:text property="last_name"></html:text>
</td> 
</tr> 
<tr> 
<td>         
<html:submit property="method" value="view"></html:submit> 
 
<!--<html:link page="/CustomerAction.do?parameter=view">view</html:link>-->
</td> 
</tr> 
<tr>  
<td>      
<html:submit property="method" value="create"></html:submit> 

<!--<html:link page="/CustomerAction.do?parameter=create">create</html:link>-->
</td> 
</tr>
<tr> 
<td>      
<html:submit  property="method" value="delete"></html:submit>

<!--<html:link page="/CustomerAction.do?parameter=delete">delete</html:link>-->
</td> 
</tr> 
</table> 
</html:form>
</body>
</html>

Customer

package com;

import org.apache.struts.action.ActionForm;

public class Customer extends ActionForm {
	
	private static final long serialVersionUID = 1L;
  
	private int cust_id;
	private String first_name;
	private String last_name;
    private String message;


	public int getCust_id() {
		return cust_id;
	}
	public void setCust_id(int cust_id) {
		this.cust_id = cust_id;
	}
	
	public String getFirst_name() {
		return first_name;
	}
	public void setFirst_name(String first_name) {
		this.first_name = first_name;
	}
	public String getLast_name() {
		return last_name;
	}
	public void setLast_name(String last_name) {
		this.last_name = last_name;
	}
	public String getMessage() {
		return message;
	}
	public void setMessage(String message) {
		this.message = message;
	}
}

[Prasad Krishnegowda]

Where are you setting the values for the '?' in the query..
Please edit your post and use code tags, codes which are not formatted are very difficult to read..

[Josh X Long]

Hi,

First, I'd ensure that your query is correct. I note that in the INSERT statement you ran a queryForList which won't have the result you're expecting. Try using JdbcTemplate#execute* and so on instead for operations that aren't expected to return a result (except, perhaps, the keys that were updated or generated as a side effect). As the previos commenter noted, you should also provide query parameters: e.g., "UPDATE FOO SET a =?, b = ? " would only work if you ran a jdbcTemplate call that specified values for the positional parameters (the parts with "?"), like this:

  JdbcTemplate template = new JdbcTemplate( this.dataSource) ; 
  template.update( "UPDATE FOO  SET a =?, b = ?" ,
            "my value for a", "my value for b") ;  // note that you can specify as many arguments as you have parameters after the query, in this case. They will map to the parameters in the query.

Also, to help narrow this problem down, ensure that you've got a java.sql.DataSource configured and then a JdbcTemplate and try runninng the operations seperately, in a public static void main(){ } block or unit test. This will help you identify problems with your logic, and *then* you can integrate into your struts code as you have here.

[Josh X Long]

BTW, my book 'Spring Recipes, 2nd Edition' has lots of good information on this kind of stuff. If you don't want a book, SpringSource has published a lot of content specifically geared to getting started with Spring. See http://blog.springsource.com/category/green-beans/ for a list of blogs that are dedicated to taking the first steps with things including data access. To learn more, see the blog post http://blog.springsource.com/2011/01/07/green-beans-getting-started-with-spring-in-your-service-tier/. There's also corresponding sample code that you're free to download and examine.

[manjiri chunekar]

thaks for giving me links..
yet I am not succeed..
please help me
its giving still same error

whats wrong in my syntax???

javax.servlet.ServletException: StatementCallback; bad SQL grammar [INSERT INTO Cust (Cust_Id, Cust_Fname, Cust_Lname) VALUES (?,?,?)]; nested exception is java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect

db in ms access