spring模拟请求测试报错

Jzxh 发布于 2017/11/24 17:13
阅读 411
收藏 0

实验spring注解方式,模拟请求测试报错

这是错误信息:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 8
### The error may exist in file [D:\eclipse-workspace\ssm_curd\target\classes\mapper\EmployeeMapper.xml]
### The error may involve com.zxh.ssm.dao.EmployeeMapper.selectByExampleWithDept-Inline
### The error occurred while setting parameters
### SQL: select           e.emp_id, e.emp_name, e.gender, e.email, e.d_id, d.dept_id,   d.dept_name      from  tbl_emp e     left join tbl_dept d on e.d_id=d.dept_id; LIMIT 5
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 8
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 8
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:65)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:167)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:134)
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:155)
    at com.zxh.ssm.test.MvcTest.testPage(MvcTest.java:48)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:539)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:761)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:461)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:207)
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 8
### The error may exist in file [D:\eclipse-workspace\ssm_curd\target\classes\mapper\EmployeeMapper.xml]
### The error may involve com.zxh.ssm.dao.EmployeeMapper.selectByExampleWithDept-Inline
### The error occurred while setting parameters
### SQL: select           e.emp_id, e.emp_name, e.gender, e.email, e.d_id, d.dept_id,   d.dept_name      from  tbl_emp e     left join tbl_dept d on e.d_id=d.dept_id; LIMIT 5
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 8
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 8
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
    at com.sun.proxy.$Proxy22.selectList(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
    at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    at com.sun.proxy.$Proxy23.selectByExampleWithDept(Unknown Source)
    at com.zxh.ssm.service.EmployeeService.getAll(EmployeeService.java:19)
    at com.zxh.ssm.service.EmployeeService$$FastClassBySpringCGLIB$$a7379e7c.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:721)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:656)
    at com.zxh.ssm.service.EmployeeService$$EnhancerBySpringCGLIB$$8a7403e6.getAll(<generated>)
    at com.zxh.ssm.controller.EmployeeController.getEmps(EmployeeController.java:33)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    ... 39 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 8
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    at org.apache.ibatis.executor.BatchExecutor.doQuery(BatchExecutor.java:93)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:134)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    at com.sun.proxy.$Proxy33.query(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    ... 72 more

这是mapper.xml文件部分配置:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zxh.ssm.dao.EmployeeMapper">
    <resultMap id="BaseResultMap" type="com.zxh.ssm.bean.Employee">
        <id column="emp_id" jdbcType="INTEGER" property="empId" />
        <result column="emp_name" jdbcType="VARCHAR" property="empName" />
        <result column="gender" jdbcType="CHAR" property="gender" />
        <result column="email" jdbcType="VARCHAR" property="email" />
        <result column="d_id" jdbcType="INTEGER" property="dId" />
    </resultMap>
    <resultMap id="WithDeptResultMap" type="com.zxh.ssm.bean.Employee">
        <id column="emp_id" jdbcType="INTEGER" property="empId" />
        <result column="emp_name" jdbcType="VARCHAR" property="empName" />
        <result column="gender" jdbcType="CHAR" property="gender" />
        <result column="email" jdbcType="VARCHAR" property="email" />
        <result column="d_id" jdbcType="INTEGER" property="dId" />
        <!-- 指定联合查询出的部门信息 -->
        <association javaType="com.zxh.ssm.bean.Department" property="department">
            <id column="dept_id" property="deptId" />
            <result column="dept_name" jdbcType="INTEGER" property="deptName" />
        </association>
    </resultMap>
    <sql id="Example_Where_Clause">
        <where>
            <foreach collection="oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" prefixOverrides="and" suffix=")">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value}
                                    and
                                    #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>
    <sql id="Update_By_Example_Where_Clause">
        <where>
            <foreach collection="example.oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" prefixOverrides="and" suffix=")">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    and ${criterion.condition} #{criterion.value}
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value}
                                    and
                                    #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>
    <sql id="Base_Column_List">
        emp_id, emp_name, gender, email, d_id
    </sql>
    <sql id="WithDept_Column_List">
        e.emp_id, e.emp_name, e.gender, e.email, e.d_id, d.dept_id,
        d.dept_name
    </sql>
    <!-- List<Employee> selectByExampleWithDept(EmployeeExample example); Employee 
        selectByPrimaryKeyWithDept(Integer empId); -->
    <!-- 查询员工带部门 -->
    <select id="selectByExampleWithDept" parameterType="com.zxh.ssm.bean.EmployeeExample" resultMap="WithDeptResultMap">
        select
        <if test="distinct">
            distinct
        </if>
        <include refid="WithDept_Column_List" />
        from     tbl_emp e
    left join tbl_dept d on e.d_id=d.dept_id;
        <if test="_parameter != null">
            <include refid="Example_Where_Clause" />
        </if>
        <if test="orderByClause != null">
            order by ${orderByClause}
        </if>
    </select>

    <select id="selectByPrimaryKeyWithDept"  parameterType="com.zxh.ssm.bean.EmployeeExample"    resultMap="WithDeptResultMap">
        select
        <include refid="WithDept_Column_List" />
        from tbl_emp e
        left join tbl_dept d on e.d_id=d.dept_id
        where emp_id = #{empId,jdbcType=INTEGER}
    </select>

这是测试类:

package com.zxh.ssm.test;

import java.util.Iterator;
import java.util.List;

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.web.MockHttpServletRequest;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.MockMvcBuilder;
import org.springframework.test.web.servlet.MvcResult;
import org.springframework.test.web.servlet.request.MockMvcRequestBuilders;
import org.springframework.test.web.servlet.setup.MockMvcBuilders;
import org.springframework.web.context.WebApplicationContext;
import com.zxh.ssm.bean.Employee;
import com.github.pagehelper.PageInfo;
import com.mysql.jdbc.PingTarget;

/**
 * 使用spring测试模块提供的模拟请求功能
 * Spring4需要Servlet3.0支持
 */
@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(locations = { "classpath:applicationContext.xml",
        "file:src/webapp/WEB-INF/dispatcherServlet-servlet.xml" })
public class MvcTest {
    // 传入springmvc
    @Autowired
    WebApplicationContext context;
    // 虚拟mvc请求,获取处理结果
    MockMvc mockMvc;

    @Before
    public void initMockMvc() {
        mockMvc=MockMvcBuilders.webAppContextSetup(context).build();
    }
    @Test
    public void testPage() throws Exception {
            // 模拟请求,拿到返回值
            MvcResult result = mockMvc.perform(MockMvcRequestBuilders.get("/emps").param("pn", "1")).andReturn();
            // 请求成功后,请求域中会有pageInfo,取出pageInfoj进行验证。
            MockHttpServletRequest request = result.getRequest();
            PageInfo pi = (PageInfo) request.getAttribute("pageInfo");
            System.out.println("当前页码" + pi.getPageNum());
            System.out.println("总页码" + pi.getPages());
            System.out.println("总记录数" + pi.getTotal());
            System.out.println("在页面需要连续显示的页码");
            int[] nums = pi.getNavigatepageNums();
            for (int i : nums) {
                System.out.print(" " + i);
            }
            // 获取员工数据
            List<Employee> list = pi.getList();
            for (Employee employee : list) {
                System.out.println("ID:" + employee.getEmpId() + "Name" + employee.getEmpName());
            }

        
    }
}
 

 

加载中
0
J
Jzxh

大佬们,帮帮忙,谢谢了

0
budexing
budexing

check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 5' at line 8

J
Jzxh
回复 @亚林瓜子 : 好的 谢谢
亚林瓜子
亚林瓜子
回复 @京墨 : 尝试输出到控制台,主要为了了解运行时sql语句
J
Jzxh
回复 @亚林瓜子 : 请问怎么使用日志,没有用过。>.<
亚林瓜子
亚林瓜子
回复 @京墨 :运行时的sql可以用日志打印出来,或者debug看sql,这样可以确保运行时的sql和自己在数据库客户端的sql一致
J
Jzxh
回复 @亚林瓜子 : 使用这种方法了,sql语句单独在数据库中执行是可以的,放到配置文件就不行了
下一页
0
sprouting
sprouting

去掉sql语句中的“ ; ” 号

J
Jzxh
sql写在配置文件中,并没有使用;号
0
sprouting
sprouting

引用来自“sprouting”的评论

去掉sql语句中的“ ; ” 号

自己看 id为  selectByExampleWithDept 的sql

J
Jzxh
回复 @sprouting : 去掉;号,重新创建了一个项目,可以运行
sprouting
sprouting
回复 @京墨 : 解决?
J
Jzxh
哦哦,发现了,应该从数据库复制过来没有删,谢谢了
返回顶部
顶部