索引中丢失IN或OUT参数issue解决方案

最新推荐文章于 2022-09-26 08:52:19 发布

梦想家杰西

最新推荐文章于 2022-09-26 08:52:19 发布

阅读量303

点赞数

java 同时被 3 个专栏收录

8 篇文章 0 订阅

订阅专栏

oracle

1 篇文章 0 订阅

订阅专栏

jdbc

1 篇文章 0 订阅

订阅专栏

本文记录了一次使用原生JDBC进行Oracle数据库插入操作时遇到的问题及解决方案。主要问题是在某些字段未赋值的情况下尝试插入数据时，出现了参数缺失的错误。通过为所有字段包括未赋值的字段明确指定NULL值，成功解决了该问题。

摘要由CSDN通过智能技术生成

今天试了试原生jdbc insert操作，oracle数据库。出现错误如下


 INFO [main] (ConnectionPool.java:98) -Connection created successfully!
 INFO [main] (ConnectionPool.java:54) -create pool [Sun Oct 13 22:30:30 CST 2013]
ERROR [main] (UserDaoImpl.java:57) -insert user User [userName=Jesse, passWord=Jesse, gender=null, email=null, address=null, birthday=null, registerDate=null, status=null] failed!
[color=red]ERROR [main] (UserDaoImplTest.java:40) -索引中丢失  IN 或 OUT 参数:: 6[/color]
 INFO [main] (ConnectionPool.java:189) -return connection [oracle.jdbc.driver.T4CConnection@4d66f8] to pool
 INFO [main] (ConnectionPool.java:236) -close connection [oracle.jdbc.driver.T4CConnection@4d66f8]

其中，insert操作关键代码如下


private static final String ADD_USER_SQL = "insert into oss_user_tbl(username, password, gender, email, address, birthday, registerdate, status) values (?,?,?,?,?,?,?,?)";


		try {
			PreparedStatement pst = conn.prepareStatement(ADD_USER_SQL);
			pst.setString(1, user.getUserName());
			pst.setString(2, user.getPassWord());
			pst.setString(3, user.getGender());
			pst.setString(4, user.getEmail());
			pst.setString(5, user.getAddress());
			Date birthday = user.getBirthday();
			Date registerDate = user.getRegisterDate();
			if(birthday != null) {
				pst.setDate(6, SimpleUtils.revertDateToSqlDate(birthday));
			}
			if(registerDate != null) {
				pst.setDate(7, SimpleUtils.revertDateToSqlDate(registerDate));
			}
			pst.setString(8, user.getStatus());
			pst.execute();
		} catch (SQLException e) {
			logger.error("insert user " + user + " failed!");
			throw new DaoException(e.getMessage());
		}

测试代码如下


	@Test
	public void testAdd() {
		Connection conn = cm.getConnection();
		User user = new User();
		user.setUserName("Jesse");
		user.setPassWord("Jesse");
		try {
			userDao.add(conn, user);
			logger.info(user + " added successfully!");
		} catch (DaoException e) {
			logger.error(e.getMessage());
		}
		cm.free(conn);
		cm.close();
	}

但是，当将user所有字段都设置成不为null的值时，不会出现上述错误


	@Test
	public void testAdd() {
		Connection conn = cm.getConnection();
		User user = new User();
		user.setUserName("Jesse");
		user.setPassWord("Jesse");
		user.setGender("M");
		user.setEmail("jesse");
		user.setAddress("jesse");
		user.setBirthday(new Date());
		user.setRegisterDate(new Date());
		user.setStatus("jesse");
		try {
			userDao.add(conn, user);
			logger.info(user + " added successfully!");
		} catch (DaoException e) {
			logger.error(e.getMessage());
		}
		cm.free(conn);
		cm.close();
	}

网上查了下，说出现这种问题的情况非常多，有的是和数据库类型不一致问题，所以，想到了是insert操作中下面部分代码问题


			if(birthday != null) {
				pst.setDate(6, SimpleUtils.revertDateToSqlDate(birthday));
			}
			if(registerDate != null) {
				pst.setDate(7, SimpleUtils.revertDateToSqlDate(registerDate));
			}

将其改如下，问题解决


	@Override
	public void add(Connection conn, User user) throws DaoException {
		try {
			PreparedStatement pst = conn.prepareStatement(ADD_USER_SQL);
			pst.setString(1, user.getUserName());
			pst.setString(2, user.getPassWord());
			pst.setString(3, user.getGender());
			pst.setString(4, user.getEmail());
			pst.setString(5, user.getAddress());
			Date birthday = user.getBirthday();
			Date registerDate = user.getRegisterDate();
			if(birthday != null) {
				pst.setDate(6, SimpleUtils.revertDateToSqlDate(birthday));
			} else {
				pst.setDate(6, null);
			}
			if(registerDate != null) {
				pst.setDate(7, SimpleUtils.revertDateToSqlDate(registerDate));
			} else {
				pst.setDate(7, null);
			}
			pst.setString(8, user.getStatus());
			pst.execute();
		} catch (SQLException e) {
			logger.error("insert user " + user + " failed!");
			throw new DaoException(e.getMessage());
		}
	}