JDBC模糊查询报错

最新推荐文章于 2023-07-25 09:51:51 发布

华的曲奇饼

最新推荐文章于 2023-07-25 09:51:51 发布

阅读量516

点赞数

分类专栏： JavaWeb # 问题文章标签： java

本文链接：https://blog.csdn.net/qq_50794782/article/details/123884260

版权

JavaWeb 同时被 2 个专栏收录

12 篇文章 2 订阅

订阅专栏

问题

1 篇文章 0 订阅

订阅专栏

报错信息： Parameter index out of range (3 > number of parameters, which is 2).

报错代码

sBuilder.append(" and bookname like '%?%'");

public List<BookInfo> getBookList(String bookName, String bookType, String startDate, String endDate, int pageIndex,
			int pageSize, String sortField, String sortOrder) {
		Connection conn = JDBCUtils.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		// 图书列表
		List<BookInfo> booklist = new ArrayList<BookInfo>();
		List<Object> params = new ArrayList<>();
		StringBuilder sBuilder = new StringBuilder();
		sBuilder.append(" select * from bookinfo where 1=1");
		if (!StringUtils.isEmpty(bookType)) {
			sBuilder.append(" and booktype = ?");
			params.add(bookType);
		}
		if (!StringUtils.isEmpty(bookName)) {
			sBuilder.append(" and bookname like  '%?%'");
			params.add(bookName);
		}
		if (!StringUtils.isEmpty(startDate)) {
			sBuilder.append(" and remain >= ?");
			params.add(startDate);
		}
		if (!StringUtils.isEmpty(startDate)) {
			sBuilder.append(" and remain <= ?");
			params.add(endDate);
		}
		if (!StringUtils.isEmpty(sortOrder) && !StringUtils.isEmpty(sortField)) {
			sBuilder.append("order by " + sortField + " " + sortOrder);
		}
		if (-1 != pageIndex && -1 != pageSize) {
			sBuilder.append(" limit ?,? ");
			params.add(pageIndex * pageSize);
			params.add(pageSize);
		}

		try {
			pstm = conn.prepareStatement(sBuilder.toString());
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					System.out.println(i);
					System.out.println(params.get(i).toString());
					pstm.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					System.out.println(i);
					System.out.println(params.get(i).toString());
					pstm.setString(i + 1, params.get(i).toString());
				}
			}
			
			rs = pstm.executeQuery();
			while (rs.next()) {
				BookInfo bookInfo = new BookInfo();
				bookInfo.setBookId(rs.getString("bookid"));
				System.out.println(rs.getString("bookname"));
				bookInfo.setBookName(rs.getString("bookname"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setBookType(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));
				booklist.add(bookInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeConnection(conn, pstm, rs);
		}

		return booklist;
	}

原因使用占位符在%外面加上' '导致识别不了里面的 ' %?% ' 的问号，

解决方法

第一种：

sBuilder.append(" and bookname like ？ ");
pstmt = conn.prepareStatement(sql);
// 设定参数
pstmt.setString(1, "%" + customername + "%" );
// 获取查询的结果集
rs = pstmt.executeQuery();

第二种：

百分号直接写在sql语句中把 ' 符号转义

sBuilder.append(" and bookname like \"%\"?\"%\"");
pstmt = conn.prepareStatement(sql);
// 设定参数
pstmt.setString(1, customername);
// 获取查询的结果集
rs = pstmt.executeQuery();

更改后

public List<BookInfo> getBookList(String bookName, String bookType, String startDate, String endDate, int pageIndex,
			int pageSize, String sortField, String sortOrder) {
		Connection conn = JDBCUtils.getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		// 图书列表
		List<BookInfo> booklist = new ArrayList<BookInfo>();
		List<Object> params = new ArrayList<>();
		StringBuilder sBuilder = new StringBuilder();
		sBuilder.append(" select * from bookinfo where 1=1");
		if (!StringUtils.isEmpty(bookType)) {
			sBuilder.append(" and booktype = ?");
			params.add(bookType);
		}
		if (!StringUtils.isEmpty(bookName)) {
			sBuilder.append(" and bookname like  \"%\"?\"%\"");
			params.add(bookName);
		}
		if (!StringUtils.isEmpty(startDate)) {
			sBuilder.append(" and remain >= ?");
			params.add(startDate);
		}
		if (!StringUtils.isEmpty(startDate)) {
			sBuilder.append(" and remain <= ?");
			params.add(endDate);
		}
		if (!StringUtils.isEmpty(sortOrder) && !StringUtils.isEmpty(sortField)) {
			sBuilder.append("order by " + sortField + " " + sortOrder);
		}
		if (-1 != pageIndex && -1 != pageSize) {
			sBuilder.append(" limit ?,? ");
			params.add(pageIndex * pageSize);
			params.add(pageSize);
		}

		try {
			pstm = conn.prepareStatement(sBuilder.toString());
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					System.out.println(i);
					System.out.println(params.get(i).toString());
					pstm.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					System.out.println(i);
					System.out.println(params.get(i).toString());
					pstm.setString(i + 1, params.get(i).toString());
				}
			}
			
			rs = pstm.executeQuery();
			while (rs.next()) {
				BookInfo bookInfo = new BookInfo();
				bookInfo.setBookId(rs.getString("bookid"));
				System.out.println(rs.getString("bookname"));
				bookInfo.setBookName(rs.getString("bookname"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setBookType(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));
				booklist.add(bookInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeConnection(conn, pstm, rs);
		}

		return booklist;
	}