索引中丢失IN或OUT参数issue解决方案_JAVA_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > JAVA > 索引中丢失IN或OUT参数issue解决方案

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

 2013/10/14 0:30:52  eric2500  程序员俱乐部  我要评论(0)
  • 摘要:今天试了试原生jdbcinsert操作,oracle数据库。出现错误如下INFO[main](ConnectionPool.java:98)-Connectioncreatedsuccessfully!INFO[main](ConnectionPool.java:54)-createpool[SunOct1322:30:30CST2013]ERROR[main](UserDaoImpl.java:57)-insertuserUser[userName=Jesse,passWord=Jesse
  • 标签:解决方案 解决 索引
今天试了试原生jdbc insert操作,oracle数据库。出现错误如下
class="java" name="code">
 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中的占位符一定要设置值的,待后面深入学习后再回头看看这个问题
上一篇: ActionBarCompat 教程-1. 预备知识及准备工作 下一篇: 没有下一篇了!
发表评论
用户名: 匿名