时间戳

首页 > 解决方案 > java.sql.SQLSyntaxErrorException：查看与您的 MySQL 服务器版本对应的手册

mysql - java.sql.SQLSyntaxErrorException：查看与您的 MySQL 服务器版本对应的手册

问题描述

我正在尝试在 MySQL 数据库中插入数据；除此插入操作外，所有操作均有效。当我点击此服务时，我遇到了以下异常：

java.sql.SQLSyntaxErrorException：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行使用接近 'rank) 值 (300.0, 'rahul', 20)' 的正确语法

控制器

@RequestMapping(value="/insertData", method = RequestMethod.POST,produces={"application/json"})
@ResponseBody
public StatusObject insertStudentData(@RequestBody InsertStudentData data){
    return userService.insertStudentData(data);
}

服务

@Transactional
    public StatusObject insertStudentData(InsertStudentData insertStudentData){

        Users users = new Users();
        users.setName(insertStudentData.getName());
        users.setMarks(insertStudentData.getMarks());
        users.setRank(insertStudentData.getRank());
        int status =  usersdao.insertObjectIntoDatabase(users);
        StatusObject statusObject = new StatusObject();
        if(status==0){
            statusObject.setStatus(true);
            statusObject.setMessage("Failed to insert data");
            return statusObject;
        }else{
            statusObject.setStatus(false);
            statusObject.setMessage("Success to insert data");
            return statusObject;
        }
    }

道

public int insertObjectIntoDatabase(Users userOj){

        Object obj = hibernateUtil.create(userOj);
        if (obj != null) {
            Session session = sessionFactory.getCurrentSession();
            int userID = (Integer) session.createCriteria(Users.class).setProjection(Projections.max("user_id")).uniqueResult();
            session.flush();
            session.clear();
            return userID;
        } else {
            return 0;
        }
    }

休眠实用程序：

@Repository
public class HibernateUtil {

    @Autowired
    private SessionFactory sessionFactory;

    public <T> Serializable create(final T entity) {
        return sessionFactory.getCurrentSession().save(entity);        
    }

    public <T> T update(final T entity) {
        sessionFactory.getCurrentSession().update(entity);   
        return entity;
    }

    public <T> void delete(final T entity) {
        sessionFactory.getCurrentSession().delete(entity);
    }

    public <T> void delete(Serializable id, Class<T> entityClass) {
        T entity = fetchById(id, entityClass);
        delete(entity);
    }

    @SuppressWarnings("unchecked")  
    public <T> List<T> fetchAll(Class<T> entityClass) {        
        return sessionFactory.getCurrentSession().createQuery(" FROM "+entityClass.getName()).list();        
    }

    @SuppressWarnings("rawtypes")
    public <T> List fetchAll(String query) {        
        return sessionFactory.getCurrentSession().createSQLQuery(query).list();        
    }

    @SuppressWarnings("unchecked")
    public <T> T fetchById(Serializable id, Class<T> entityClass) {
        return (T)sessionFactory.getCurrentSession().get(entityClass, id);
    }
}

pom.xml

  <project xmlns="http://maven.apache.org/POM/4.0.0"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <groupId>com.ensis.bloodgroup</groupId>
        <artifactId>bloodgroup</artifactId>
        <packaging>war</packaging>
        <version>0.0.1-SNAPSHOT</version>
        <name>bloodgroup Maven Webapp</name>
        <url>http://maven.apache.org</url>

        <properties>

            <spring.version>4.3.2.RELEASE</spring.version>
            <servlet.api.version>3.1.0</servlet.api.version>
            <log4j.version>1.2.17</log4j.version>
            <fasterxml.jackson.core.version>2.5.3</fasterxml.jackson.core.version>
            <codehaus.jackson.version>1.9.13</codehaus.jackson.version>
            <hibernate.version>4.3.5.Final</hibernate.version>
            <mysql.version>8.0.15</mysql.version>
            <quartz.version>2.2.1</quartz.version>
            <spring.security.version>3.2.4.RELEASE</spring.security.version>
            <spring.security.outh2.version>2.0.12.RELEASE</spring.security.outh2.version>

        </properties>



        <dependencies>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>3.8.1</version>
                <scope>test</scope>
            </dependency>

            <dependency>
                <groupId>javax</groupId>
                <artifactId>javaee-web-api</artifactId>
                <version>6.0</version>
                <scope>provided</scope>
            </dependency>


            <!-- Spring Farmework -->
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-core</artifactId>
                <version>${spring.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-web</artifactId>
                <version>${spring.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-webmvc</artifactId>
                <version>${spring.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-aop</artifactId>
                <version>${spring.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context-support</artifactId>
                <version>${spring.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-orm</artifactId>
                <version>${spring.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>${spring.version}</version>
            </dependency>

            <!-- Quartz framework -->
            <dependency>
                <groupId>org.quartz-scheduler</groupId>
                <artifactId>quartz</artifactId>
                <version>${quartz.version}</version>
            </dependency>

            <!-- Servlet -->

            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>jstl</artifactId>
                <version>1.2</version>
                <scope>compile</scope>
            </dependency>

            <dependency>
                <groupId>javax.servlet</groupId>
                <artifactId>javax.servlet-api</artifactId>
                <version>${servlet.api.version}</version>
                <scope>compile</scope>
            </dependency>

            <dependency>
                <groupId>commons-fileupload</groupId>
                <artifactId>commons-fileupload</artifactId>
                <version>1.3.1</version> <!-- makesure correct version here -->
            </dependency>

            <dependency>
                <groupId>javax.mail</groupId>
                <artifactId>mail</artifactId>
                <version>1.4.7</version>
            </dependency>

            <!-- Jackson -->
            <dependency>
                <groupId>org.codehaus.jackson</groupId>
                <artifactId>jackson-mapper-asl</artifactId>
                <version>${codehaus.jackson.version}</version>
            </dependency>

            <dependency>
                <groupId>org.codehaus.jackson</groupId>
                <artifactId>jackson-core-asl</artifactId>
                <version>${codehaus.jackson.version}</version>
            </dependency>

            <dependency>
                <groupId>javax.xml.bind</groupId>
                <artifactId>jaxb-api</artifactId>
                <version>2.2.12</version>
            </dependency>

            <dependency>
                <groupId>javax.xml</groupId>
                <artifactId>jaxb-impl</artifactId>
                <version>2.1</version>
            </dependency>


            <!-- Jackson -->
            <dependency>
                <groupId>com.fasterxml.jackson.core</groupId>
                <artifactId>jackson-core</artifactId>
                <version>${fasterxml.jackson.core.version}</version>
            </dependency>

            <dependency>
                <groupId>com.fasterxml.jackson.core</groupId>
                <artifactId>jackson-databind</artifactId>
                <version>${fasterxml.jackson.core.version}</version>
            </dependency>

            <!-- Loggers -->
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>${log4j.version}</version>
            </dependency>


            <!-- @Inject -->
            <dependency>
                <groupId>javax.inject</groupId>
                <artifactId>javax.inject</artifactId>
                <version>1</version>
            </dependency>

            <!-- Hibernate -->
            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-core</artifactId>
                <version>${hibernate.version}</version>
            </dependency>
            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-entitymanager</artifactId>
                <version>${hibernate.version}</version>
            </dependency>

            <!-- Apache Commons DBCP -->
            <dependency>
                <groupId>commons-dbcp</groupId>
                <artifactId>commons-dbcp</artifactId>
                <version>1.4</version>
            </dependency>

            <!--MYSQL Connector -->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>${mysql.version}</version>
            </dependency> 

            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-io</artifactId>
                <version>1.3.2</version>
            </dependency>


            <dependency>
                <groupId>com.sun.jersey</groupId>
                <artifactId>jersey-bundle</artifactId>
                <version>1.13-b01</version>
            </dependency>

            <!-- Spring Security -->
            <dependency>
                <groupId>org.springframework.security</groupId>
                <artifactId>spring-security-web</artifactId>
                <version>${spring.security.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.security</groupId>
                <artifactId>spring-security-config</artifactId>
                <version>${spring.security.version}</version>
            </dependency>

            <!-- https://mvnrepository.com/artifact/org.springframework.security.oauth/spring-security-oauth2 -->
            <dependency>
                <groupId>org.springframework.security.oauth</groupId>
                <artifactId>spring-security-oauth2</artifactId>
                <version>${spring.security.outh2.version}</version>
            </dependency>

            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-c3p0</artifactId>
                <version>3.6.3.Final</version>
            </dependency>

            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-beans</artifactId>
                <version>4.3.7.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>4.3.7.RELEASE</version>
            </dependency>

        </dependencies>
        <build>
            <finalName>bloodgroup</finalName>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-war-plugin</artifactId>
                    <version>2.1.1</version>
                    <configuration>
                        <packagingExcludes>WEB-INF/web.xml</packagingExcludes>
                    </configuration>
                </plugin>
            </plugins>

        </build>
    </project>

标签： mysqlspring-mvc

解决方案

rank(RANK (R); added in 8.0.2 (reserved)) 是 mysql 中的关键字，如这里和这里所示。

推荐阅读