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