java索引丢失怎么解决_索引中丢失IN或OUT参数issue解决方案

今天试了试原生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中的占位符一定要设置值的，待后面深入学习后再回头看看这个问题