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

今天试了试原生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());
		}
	}

总结：目前，个人认为是sql中的占位符一定要设置值的，待后面深入学习后再回头看看这个问题