Oracleä¸æ们ç»å¸¸ä½¿ç¨Dateå段类åè®°å½æ¥æåæ¶é´ï¼æçæ¶åè¿å¨è¿ä¸ªå段ä¸å»ºç«ç´¢å¼ã
ç¶åéè¿Javaç¨åºè®¿é®æ°æ®åºçæ¶åï¼æ们å¾èªç¶ç类似è¿æ ·ä½¿ç¨ï¼select * from table where endDate>? and endDate<?ï¼ç¶åéè¿PreparedStatementé¢ç¼è¯ï¼åéè¿setTimestampä¼ å
¥ç±java.util.Date转æjava.sql.Timestampçåæ°ï¼å 为java.sql.Dateåªææ¥æï¼java.sql.Timeåªææ¶é´ï¼æ以æ们åªè½ç¨java.sql.Timestampç±»åï¼ãæ们ä¼è®¤ä¸ºè¿æ ·åºè¯¥èµ°ç´¢å¼åºé´æ«æï¼æçåºè¯¥æ¯é常é«çã
èäºå®ä¸ï¼Oracleä¼æsql解éæå¦ä¸è¿æ ·æ¥æ§è¡ï¼select * from table where TO_TIMESTAMP(endDate)>? and TO_TIMESTAMP(endDate)<?; 为ä»ä¹ï¼å ä¸ºä¼ å
¥çåæ°æ¯timestampç±»åï¼Oracleä»9.2çæ¬ä»¥åæ¯æè¿ç§ç±»åï¼æ以Oracleåäºè¿æ ·ç转æ¢ï¼ç»æå°±æ¯è¿ä¸ªSQLæ§è¡åæäºå
¨è¡¨æ«æãæ们åçè¯éªï¼å äºä¸ä¸ªindex hintï¼å¼ºå¶èµ°æ¶é´ç´¢å¼å段ï¼ç»ææçä¹ä¸é«ï¼sqlæ§è¡åæäºå
¨ç´¢å¼æ«æï¼åå
¨è¡¨æ«æ没å¤å¤§åºå«ãç»ææçè¿æ¯ä½ã
ä¸å
ç´æ¥ä½¿ç¨JDBCä¼æ¯è¿æ ·ï¼Springï¼iBatiså¨å¤çä¼ å
¥åæ°æ¯java.util.Dateç±»åçæ¶åï¼é½ä¼ä½¿ç¨setTimestamp设å®åæ°ï¼æ以é½éè¦æ³¨æã
解å³åæ³ï¼åç§è§£å³åæ³ï¼æ¨èæ¹æ¡ä¸ï¼ï¼
1.sqlä¿®æ¹æè¿æ ·ï¼select * from table where endDate>to_date(?,âyyyymmddhh24missâ) and endDate<to_date(?,âyyyymmddhh24missâ);ç¶åå°ä¼ å
¥åæ°æ ¼å¼åæ对åºæ ¼å¼ç
å符串å¨ä¼ å
¥ï¼è¿æ ·ç±Oracleå°å符串转æDateç±»åï¼å°±å¾é¡ºå©çèµ°ç´¢å¼åºé´æ«æï¼æçæé«ã
2.å¨å»ºç«æ°æ®åºè¿æ¥çæ¶åå¢å ä¸ä¸ªå±æ§oracle.jdbc.V8Compatible=trueï¼ä»£ç å¦ä¸ï¼
Properties prop=new Properties();
prop.setProperty(âuserâ,â****â);
prop.setProperty(âpasswordâ,â****â);
prop.setProperty(âoracle.jdbc.V8Compatibleâ,âtrueâ);
Connection connection = DriverManager.getConnection(âjdbc:oracle:thin:@127.0.0.1:1521:testâ, prop);
è¿æ¥æ± ä¹æ ¹æ®åèªçé
ç½®æ¹å¼å¢å è¿ä¸ªå±æ§å³å¯ãç®åçæ¥è¿ä¸ªå±æ§åæ°æ¯å¤çæ¶é´
æ å°å
³ç³»çï¼ä½æ¯è¿ä¸ç¡®å®å®æ¯å¦ä¼å¸¦æ¥å
¶ä»çé®é¢ï¼æ以è¦æ
é使ç¨ã
3.ä¿®æ¹æ°æ®åºåç±»å为timestampç±»åã
4.ä¾æ®ç½ä¸èµæï¼Oracle 11gä¿®æ¹äºé©±å¨ apiï¼ä½¿ç¨æ¹å¼å¯è½ä¹ä¼ææ¹åãå 为æ æ³è¯éªï¼æ以ä¹ä¸ç¡®å®å
·ä½ç»èã