Oracle CHAR PreparedStatement

最新推荐文章于 2022-04-24 16:10:44 发布

编程家阿笨猫

最新推荐文章于 2022-04-24 16:10:44 发布

阅读量712

点赞数

使用动态SQL（PreparedStatement）在对Oracle的CHAR类型变量上动态设置参数时需要注意，如果不对该字段进行trim，结果可能会同预计的不同。

1. 准备数据

drop table users cascade constraints;
create table users (
   userid             NUMBER(4)                       not null,
   username           CHAR(8)                         not null,
   password           VARCHAR2(8)                     not null,
   note               VARCHAR2(20),
   constraint PK_USERS primary key (userid)
);

insert into users(userid,username,password)values(1,'01234567','0124567');
insert into users(userid,username,password)values(2,'abcdabcd','abcdabcd');
insert into users(userid,username,password)values(3,'0123456','0123456');

select userid from users where username='0123456';
select userid from users where username=?;

2. Java程序里分别使用静态SQL和动态SQL

Right: String sql1 = "select userid from users where username='0123456'";
Wrong：String sql2 = "select userid from users where username=?";
Right: String sql2 = "select userid from users where trim(username)=?";

List useridList = dao.queryForList(sql1);

int userid = -1;
try {
    Connection con = dao.getDataSource().getConnection();
    PreparedStatement pstmt = con.prepareStatement(sql2);
    pstmt.setString(1, "0123456");
    ResultSet rs = pstmt.executeQuery();

    while(rs.next()){
        userid = rs.getInt(1);
    }
    rs.close();
    pstmt.close();
    con.close();
} catch(Exception e){
    //...
} finally{
    //...
}