Spring JDBC——错误的 SQL 语法

Spring JDBC -- bad SQL grammar

提问by

I'm working my way through a Spring tutorial, and I'm having a problem in the JDBC section. The code was copied directly from the tutorial itself, and several calls to the database ran correctly before the one that failed. Can anyone help me? Here is my error trace.

我正在学习 Spring 教程，但在 JDBC 部分遇到了问题。代码是直接从教程本身复制的，并且在失败之前对数据库的几次调用都正确运行。谁能帮我？这是我的错误跟踪。

Jul 31, 2012 9:40:03 PM org.springframework.jdbc.core.metadata.GenericCallMetaDataProvider processProcedureColumns
WARNING: Error while retrieving metadata for procedure columns: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: FUNCTION getrecord does not exist
Jul 31, 2012 9:40:03 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
Jul 31, 2012 9:40:03 PM org.springframework.jdbc.support.SQLErrorCodesFactory <init>
INFO: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]    Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call getrecord()}]; nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: FUNCTION getrecord does not exist
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:969)
        at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1003)
        at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:391)
        at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:354)
        at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:181)
        at com.tutorialspoint.StudentJDBCTemplate.getStudent(StudentJDBCTemplate.java:32)
        at com.tutorialspoint.MainApp.main(MainApp.java:29)
    Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: FUNCTION getrecord does not exist
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
        at com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
        at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1607)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4034)
        at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:709)
        at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
        at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4583)
        at com.mysql.jdbc.Connection.prepareCall(Connection.java:4657)
        at com.mysql.jdbc.Connection.prepareCall(Connection.java:4631)
        at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:167)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:947)
        ... 6 more

Here is the section of my main application where the problem occurs, and I've marked the line in question.

这是我的主要应用程序中出现问题的部分，我已经标记了有问题的行。

    System.out.println("----Listing Record with ID = 2 -----");
        // Error occurs in next line
    Student student = studentJDBCTemplate.getStudent(2);
    System.out.print("ID : " + student.getId());
    System.out.print(", Name : " + student.getName());
    System.out.println(", Age : " + student.getAge());

StudentJDBCTemplate has these two variables.

StudentJDBCTemplate 有这两个变量。

private DataSource dataSource;
private SimpleJdbcCall jdbcCall;

StudentJDCBTemplate sets the data source like this, and this may be the genesis of the problem, since I cannot figure out what getRecord refers to.

StudentJDCBTemplate 这样设置数据源，这可能是问题的根源，因为我无法弄清楚 getRecord 指的是什么。

public void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
    this.jdbcCall = new SimpleJdbcCall(dataSource)
            .withProcedureName("getRecord");
}

The problem occurs in this method of StudentJDBCTemplate.

这个问题出现在StudentJDBCTemplate的这个方法中。

public Student getStudent(Integer id) {
    SqlParameterSource in = new MapSqlParameterSource().addValue("in_id",
            id);
    Map<String, Object> out = jdbcCall.execute(in);
    Student student = new Student();
    student.setId(id);
    student.setName((String) out.get("out_name"));
    student.setAge((Integer) out.get("out_age"));
    return student;
}

Here is my Beans.xml

这是我的 Beans.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd "> <!-- Initialization for data source -->
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/TEST" />
        <property name="username" value="root" />
        <property name="password" value="password" />
    </bean> <!-- Definition for studentJDBCTemplate bean -->
    <bean id="studentJDBCTemplate" class="com.tutorialspoint.StudentJDBCTemplate">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>

For what it's worth, here is my Student class.

值得一提的是，这是我的学生课程。

package com.tutorialspoint;

public class Student {
    private Integer age;
    private String name;
    private Integer id;

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getAge() {
        return age;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getName() {
        return name;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getId() {
        return id;
    }
}

采纳答案by Serkan Ar?ku?u

Following your tutorial, you missed the creation of the getRecordstored procedure:

按照您的教程，您错过了getRecord存储过程的创建：

DELIMITER $$

DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id INTEGER,
OUT out_name VARCHAR(20),
OUT out_age  INTEGER)
BEGIN
   SELECT name, age
   INTO out_name, out_age
   FROM Student where id = in_id;
END $$

DELIMITER ;

回答by Nenad Bulatovic

Just in case that someone come across this in 2017, here is necessary change to that old tutorial if you are using MySQL Connector version 6.x instead of 5.x

以防万一有人在 2017 年遇到这个问题，如果您使用的是 MySQL Connector 版本 6.x 而不是 5.x，这里有必要对旧教程进行更改

In Beans.xml this line should be:

在 Beans.xml 这一行应该是：

<property name="url" value="jdbc:mysql://localhost:3306/test?nullNamePatternMatchesAll=true" />

instead of: <property name="url" value="jdbc:mysql://localhost:3306/test />

代替： <property name="url" value="jdbc:mysql://localhost:3306/test />

SQL error is quite misleading claiming that "out_name" parameter in not defined.

SQL 错误非常具有误导性，声称未定义“out_name”参数。

This is due to change in MySQL Connector from version 5.x to 6.x as commented hereOriginal SO answer is here:

作为评论这是由于从5.x版本在MySQL Connector来改变6.x的这里原来如此的答案是在这里：

回答by Rahul Agrawal

You are trying to execute Stored Procedure "getrecord". Which does not exist or not compiled properly in database. Try to execute first through SQL Client at database side.

您正在尝试执行存储过程“getrecord”。哪个在数据库中不存在或未正确编译。尝试首先通过数据库端的 SQL 客户端执行。

Procedure should match with signature as 2 out parameter and one input parameter.

程序应与签名匹配为 2 个输出参数和 1 个输入参数。

回答by Mr.Chowdary

Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: FUNCTION getrecord does not exist..So check it once.. Create getrecord function and try once..

Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: FUNCTION getrecord does not exist..所以检查一次.. 创建 getrecord 函数并尝试一次..