ordinal parameter mismatch

© 版权声明：本文为博主原创文章，转载请注明出处

错误描述：Caused by: org.hibernate.HibernateException: ordinal parameter mismatch

错误代码：

/**
	 * 查看该黑名单类型是否已存在
	 * 
	 * @param phone_number
	 *            用户号码
	 * @return
	 */
	public int checkBlack(String phone_number, String call_type, String location_id) {

		Object[] obj = null;// 参数
		String condition = null;// 条件
		condition = "phone_number = ? and call_type = ? and location_id = ?";
		obj = new Object[] { phone_number, Short.valueOf(call_type), location_id };
		List<TphBlack> list = tphBlackDao.find(condition, obj, new OrderBy());
		if (list == null || list.size() == 0) {
			return 0;// 不存在
		} else {
			return 1;// 已存在
		}

	}　

错误截图：

解决方案：将查询条件中带有"call"的字段放在第一位，使其在语句中的位置在"?"和"="之前。

正确代码：

	/**
	 * 查看该黑名单类型是否已存在
	 * 
	 * @param phone_number
	 *            用户号码
	 * @return
	 */
	public int checkBlack(String phone_number, String call_type, String location_id) {

		Object[] obj = null;// 参数
		String condition = null;// 条件
		// 带有call的字段必须放在第一个=或?之前，否则报错ordinal parameter mismatch
		condition = "call_type = ? and phone_number = ? and location_id = ?";
		obj = new Object[] { Short.valueOf(call_type), phone_number, location_id };
		List<TphBlack> list = tphBlackDao.find(condition, obj, new OrderBy());
		if (list == null || list.size() == 0) {
			return 0;// 不存在
		} else {
			return 1;// 已存在
		}

	}

原因分析：查看hibernate-core.jar中的org.hibernate.engine.query包下的ParameterParser.class的源码发现，在parse方法中，将sql语句中的call当做了存储过程中的call关键字。

ParameterParser.class源码

/*
 * Hibernate, Relational Persistence for Idiomatic Java
 *
 * Copyright (c) 2008, Red Hat Middleware LLC or third-party contributors as
 * indicated by the @author tags or express copyright attribution
 * statements applied by the authors.  All third-party contributions are
 * distributed under license by Red Hat Middleware LLC.
 *
 * This copyrighted material is made available to anyone wishing to use, modify,
 * copy, or redistribute it subject to the terms and conditions of the GNU
 * Lesser General Public License, as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
 * for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this distribution; if not, write to:
 * Free Software Foundation, Inc.
 * 51 Franklin Street, Fifth Floor
 * Boston, MA  02110-1301  USA
 *
 */
package org.hibernate.engine.query;

import org.hibernate.QueryException;
import org.hibernate.hql.classic.ParserHelper;
import org.hibernate.util.StringHelper;

/**
 * The single available method {@link #parse} is responsible for parsing a
 * query string and recognizing tokens in relation to parameters (either
 * named, JPA-style, or ordinal) and providing callbacks about such
 * recognitions.
 *
 * @author Steve Ebersole
 */
public class ParameterParser {

	public static interface Recognizer {
		public void outParameter(int position);
		public void ordinalParameter(int position);
		public void namedParameter(String name, int position);
		public void jpaPositionalParameter(String name, int position);
		public void other(char character);
	}

	private ParameterParser() {
		// disallow instantiation
	}

	/**
	 * Performs the actual parsing and tokenizing of the query string making appropriate
	 * callbacks to the given recognizer upon recognition of the various tokens.
	 * <p/>
	 * Note that currently, this only knows how to deal with a single output
	 * parameter (for callable statements).  If we later add support for
	 * multiple output params, this, obviously, needs to change.
	 *
	 * @param sqlString The string to be parsed/tokenized.
	 * @param recognizer The thing which handles recognition events.
	 * @throws QueryException
	 */
	public static void parse(String sqlString, Recognizer recognizer) throws QueryException {
		boolean hasMainOutputParameter = sqlString.indexOf( "call" ) > 0 &&
		                                 sqlString.indexOf( "?" ) < sqlString.indexOf( "call" ) &&
		                                 sqlString.indexOf( "=" ) < sqlString.indexOf( "call" );
		boolean foundMainOutputParam = false;

		int stringLength = sqlString.length();
		boolean inQuote = false;
		for ( int indx = 0; indx < stringLength; indx++ ) {
			char c = sqlString.charAt( indx );
			if ( inQuote ) {
				if ( '\'' == c ) {
					inQuote = false;
				}
				recognizer.other( c );
			}
			else if ( '\'' == c ) {
				inQuote = true;
				recognizer.other( c );
			}
			else {
				if ( c == ':' ) {
					// named parameter
					int right = StringHelper.firstIndexOfChar( sqlString, ParserHelper.HQL_SEPARATORS, indx + 1 );
					int chopLocation = right < 0 ? sqlString.length() : right;
					String param = sqlString.substring( indx + 1, chopLocation );
					if ( StringHelper.isEmpty( param ) ) {
						throw new QueryException("Space is not allowed after parameter prefix ':' '"
								+ sqlString + "'");
					}
					recognizer.namedParameter( param, indx );
					indx = chopLocation - 1;
				}
				else if ( c == '?' ) {
					// could be either an ordinal or JPA-positional parameter
					if ( indx < stringLength - 1 && Character.isDigit( sqlString.charAt( indx + 1 ) ) ) {
						// a peek ahead showed this as an JPA-positional parameter
						int right = StringHelper.firstIndexOfChar( sqlString, ParserHelper.HQL_SEPARATORS, indx + 1 );
						int chopLocation = right < 0 ? sqlString.length() : right;
						String param = sqlString.substring( indx + 1, chopLocation );
						// make sure this "name" is an integral
						try {
							new Integer( param );
						}
						catch( NumberFormatException e ) {
							throw new QueryException( "JPA-style positional param was not an integral ordinal" );
						}
						recognizer.jpaPositionalParameter( param, indx );
						indx = chopLocation - 1;
					}
					else {
						if ( hasMainOutputParameter && !foundMainOutputParam ) {
							foundMainOutputParam = true;
							recognizer.outParameter( indx );
						}
						else {
							recognizer.ordinalParameter( indx );
						}
					}
				}
				else {
					recognizer.other( c );
				}
			}
		}
	}

}