我在利用Java与Oracle进行连接查询时出现了一点些问题,而且无几天也没找到解决的办法,还望能有高手指教。
我的程序如下:import java.sql.*;/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2003</p>
* <p>Company: </p>
* @author unascribed
* @version 1.0
*/public class searchMain
{
Connection con;
Statement ps;
ResultSet rs;
public searchMain()
{
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.198:1521:ora9","card_manager","sundy"); ps = con.createStatement();
//rs = ps.executeUpdate()
rs = ps.executeQuery("select * from card_manager.t_card_info where rownum < 27 minus select * from card_manager.t_card_info where rownum < 14");
while(rs.next()){
System.out.println("f_id = " + rs.getString("f_id"));
System.out.println("f_club_id = " + rs.getString("f_club_id"));
} System.out.println("查询成功!!!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Exception = " + e.getMessage());
}
}
public static void main(String[] args)
{
searchMain searchMain1 = new searchMain();
}
}
如果将其中的27改为20(即查询14到19条)就会正常查询出结果,如果为27(即查询14到26条)就会出现下面的错误(数据库中有40条记录)java.sql.SQLException: 无法从套接字读取更多的数据 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160) at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963) at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:369) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891) at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1198) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2400) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:572) at search.searchMain.<init>(searchMain.java:28) at search.searchMain.main(searchMain.java:42)不知道该如何解决。
我的程序如下:import java.sql.*;/**
* <p>Title: </p>
* <p>Description: </p>
* <p>Copyright: Copyright (c) 2003</p>
* <p>Company: </p>
* @author unascribed
* @version 1.0
*/public class searchMain
{
Connection con;
Statement ps;
ResultSet rs;
public searchMain()
{
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.198:1521:ora9","card_manager","sundy"); ps = con.createStatement();
//rs = ps.executeUpdate()
rs = ps.executeQuery("select * from card_manager.t_card_info where rownum < 27 minus select * from card_manager.t_card_info where rownum < 14");
while(rs.next()){
System.out.println("f_id = " + rs.getString("f_id"));
System.out.println("f_club_id = " + rs.getString("f_club_id"));
} System.out.println("查询成功!!!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Exception = " + e.getMessage());
}
}
public static void main(String[] args)
{
searchMain searchMain1 = new searchMain();
}
}
如果将其中的27改为20(即查询14到19条)就会正常查询出结果,如果为27(即查询14到26条)就会出现下面的错误(数据库中有40条记录)java.sql.SQLException: 无法从套接字读取更多的数据 at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160) at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963) at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:369) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891) at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1198) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2400) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:572) at search.searchMain.<init>(searchMain.java:28) at search.searchMain.main(searchMain.java:42)不知道该如何解决。
-------^^^^^^^^^^^外面的id是用rownum伪列来生成的,这也是分页的最好方法! card_manager.t_card_info) where id>14 and id < 20");
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573) at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891) at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830) at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:572) at search.searchMain.<init>(searchMain.java:29) at search.searchMain.main(searchMain.java:43)Exception = ORA-00936: 缺少表达式
select * from (select rownum id ,tableA.* form tableA) where (id between 5 and 10 and name = '小明').
结果查询不到数据,tableA中的第5到第10行记录,并没有叫'小明'的。这该怎么办?
不过还是非常的感谢大家对我的帮助,特别要感谢pengdali(大力),是他让我认识到怎样进行改正。谢谢了,我现在太忙,分数过两天一定会分给大家。select * from (select rownum id, tabelA.* from tableA where(name = '%小明%')) where id between 5 and 10;注意不能写成以下形式:
select * from (select rownum, tabelA.* from tableA where(name = '%小明%')) where rownum between 5 and 10;这样写也不行哦:
select * from (select rownum id, * from tableA where(name = '%小明%')) where id between 5 and 10;