Oracle CHAR PreparedStatement
来源:互联网 发布:matlab编程实例并解析 编辑:程序博客网 时间:2024/06/11 17:58
使用动态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{
//...
}
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{
//...
}
0 0
- Oracle CHAR PreparedStatement
- PreparedStatement,hibernate查询oracle char类型解决方案
- PreparedStatement查询oracle char类型解决方案
- PreparedStatement,hibernate查询oracle char类型解决方案
- oracle的JDBC使用preparedStatement处理char类型字段的问题
- JDBC oracle PreparedStatement
- PreparedStatement查询注意点(CHAR类型结果找不到)
- java Statement与PreparedStatement在面对char遇到的问题
- preparedstatement
- PreparedStatement
- PreparedStatement
- PreparedStatement
- PreparedStatement
- PreparedStatement
- preparedstatement
- PreparedStatement
- PreparedStatement
- PreparedStatement
- 在Java中究竟什么是null?
- POJ 1679 The Unique MST
- JMS基础
- Mysql学习<->
- apache并发数调整
- Oracle CHAR PreparedStatement
- Subclipse
- Swing —— JButton几个小示例
- maven-eclipse-android
- Eclipse ADT
- 【jbpm5.4中文版】第二章
- Minimum Depth of Binary Tree
- Apache与Nginx优缺点比较
- Hbase API管理功能4-集群状态信息