oracle表中有一字段operatetime (date)类型的,现在要用sql语句查找。String time1="2010-08-09 00:00:00";
String time2="2010-08-09 23:29:29";
String sql = select from yuqingdongtai where operatetime<=to_date('time1','yyyy-mm-dd hh24:mi:ss') and operatetime>=to_date('time2','yyyy-mm-dd hh24:mi:ss') 这条sql语句是可以的!
但我想的是能不能直接传个date类型的到oracle中,而不需要to_char来转换!
我想用的主法是下面这样的:import java.sql.Date;String timea="2010-08-09 00:00:00";
String timeb="2010-08-09 23:29:29";
SimpleDateFormat formatter = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
java.util.Date time11 = null;
java.util.Date time22 = null;
try {
time11 = formatter.parse(timea);
time22 = formatter.parse(timeb);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Date time1=new java.sql.Date(time11.getTime());
Date time2=new java.sql.Date(time22.getTime());
String sql="SELECT * FROM YUQINGDONGTAI WHERE OPERATETIME>="+time1+" AND OPERATETIME <= "+time2+"";但这个方法好像不行啊,报的错误是:
ORA-00932: 不一致的数据类型: 要求 DATE 得到的却是 NUMBER
请问怎么解决啊?
PS:还想问一下,oracle中执行sql时,带有to_char或to_date速度会不会变慢啊?
String time2="2010-08-09 23:29:29";
String sql = select from yuqingdongtai where operatetime<=to_date('time1','yyyy-mm-dd hh24:mi:ss') and operatetime>=to_date('time2','yyyy-mm-dd hh24:mi:ss') 这条sql语句是可以的!
但我想的是能不能直接传个date类型的到oracle中,而不需要to_char来转换!
我想用的主法是下面这样的:import java.sql.Date;String timea="2010-08-09 00:00:00";
String timeb="2010-08-09 23:29:29";
SimpleDateFormat formatter = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss");
java.util.Date time11 = null;
java.util.Date time22 = null;
try {
time11 = formatter.parse(timea);
time22 = formatter.parse(timeb);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Date time1=new java.sql.Date(time11.getTime());
Date time2=new java.sql.Date(time22.getTime());
String sql="SELECT * FROM YUQINGDONGTAI WHERE OPERATETIME>="+time1+" AND OPERATETIME <= "+time2+"";但这个方法好像不行啊,报的错误是:
ORA-00932: 不一致的数据类型: 要求 DATE 得到的却是 NUMBER
请问怎么解决啊?
PS:还想问一下,oracle中执行sql时,带有to_char或to_date速度会不会变慢啊?
String time1="2010-08-09 00:00:00";
String time2="2010-08-09 23:29:29";
String sql = select from yuqingdongtai where operatetime<=date'time1' and operatetime>=date'time2'
这样就可以了!
String url = "jdbc:oracle:thin:@localhost:1521:oamis";
String username = "xx";
String passwd = "xx";
Properties props = new Properties();
props.put("user", username);
props.put("password", passwd);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, props);
String sql = "insert into date_tab values (?,?)";
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setDate(1, new Date(new java.util.Date().getTime()));// 外边的date是java.sql包中的,里边的date是java.util中的
pstm.setString(2, "id1");
pstm.execute();
System.out.println("ok..................");
conn.commit();
pstm.close();
conn.close(); }
表结构:
CREATE TABLE "DATE_TAB"
( "DT" DATE,
"ID" VARCHAR2(10 BYTE)
)TABLESPACE "TEST_BASIC_TBS" ;
查询结果:
SQL> create table date_tab(dt date,id varchar2(10));--建表表已创建。SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';--设置会话显示的日期格式会话已更改。SQL> select * from date_tab;DT ID
------------------- ----------
2010-08-09 00:00:00 id1
public static void main(String[] args) throws Exception {
String url = "jdbc:oracle:thin:@localhost:1521:oamis";
String username = "xiaoqiang";
String passwd = "xiaoqiang";
Properties props = new Properties();
props.put("user", username);
props.put("password", passwd);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, props);
String sql = "insert into date_tab values (?,?)";
PreparedStatement pstm = conn.prepareStatement(sql);
pstm.setDate(1, new Date(new java.util.Date().getTime()));// 外边的date是java.sql包中的,里边的date是java.util中的
pstm.setString(2, "id1");
pstm.execute();
System.out.println("ok..................");
conn.commit();
String qsql = "select * from date_tab where dt >= ?";
pstm = conn.prepareStatement(qsql);
pstm.setDate(1, new Date(new java.util.Date().getTime()));
ResultSet rst = pstm.executeQuery();
while (rst.next()) {
System.out.println(rst.getString(1));
System.out.println(rst.getString(2));
}
pstm.close();
conn.close(); }
String time2="2010-08-09 23:29:29";
String sql = select * from yuqingdongtai where operatetime<=to_date('"+time1+"','yyyy-mm-dd hh24:mi:ss') and operatetime>=to_date('"+time2+"','yyyy-mm-dd hh24:mi:ss') ;
这种就可以;后面那种就复杂了;无论你怎么写都是String类型的,到数据中都需要重新解析。to_char,to_date,substr等用在索引上会使索引失效,如:to_char(operatetime,'yyyymmdd hh24:mi:ss')>='2010-08-09 00:00:00',但如果是operatetime>=to_date('2010-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')我认为不会,具体你可以把Sql语句分析一下,看看索引有没有失效。