现在在做一个进销存系统,需要实现一个订单编号自动生成的功能(每生成一条订单后,下一条订单的编号自动+1),查过资料后,选用Sequence和触发器的方法来实现。具体的表和我完成后的序列,触发器如下:
create table t_sellOutOrder(
sellOrderId varchar2(20) Primary Key,
goodsId varchar2(20),
goodsName varchar2(20) not null,
customerId varchar2(20) not null,Foreign Key(CustomerId) REFERENCES t_customer(CustomerId),
outPrice float,
goodsCount int not null,
employeeId varchar2(20) not null,Foreign Key(employeeId) REFERENCES t_employee(employeeId),
sellDate varchar2(20) not null,
allOutPrice float
);--创建一个序列进行编号自动增加
create sequence sellOutOrder_seq
minvalue 1
maxvalue 99999
start with 1
increment by 1
cache 20
order;--便写一个触发器实现插入后下一个编号自动加一
CREATE OR REPLACE TRIGGER sellOrder_ID_TRIGGER BEFORE
INSERT ON t_sellOutOrder FOR EACH ROW
declare
next_checkup_no number;
begin
select sellOutOrder_seq.nextval
into next_checkup_no
from dual;
:NEW.sellOrderId := next_checkup_no;
end;
/现在的问题是,在sqlplus中,手动insert数据,可以实现自动增加,而使用JDBC连接时,开始使用的Prepared Statement,总是出现一个异常,上网查后有人说这个是oracle的一个bug,无奈,改用Statement,自己写了一个getSql方法将参数传入,现在却在运行时抛出如下异常:java.sql.SQLException: ORA-08002: sequence SELLOUTORDER_SEQ.CURRVAL is not yet defined in this session
调试后发现程序连接时写入的sql语句没有任何问题(将程序输出的sql语句复制到sqlplus就可以插入),这个程序已经调试了两天了,还是没有解决,后天就要项目答辩了,真的很着急,希望高手们能帮帮我,指出我程序中的问题,或者就这个问题如果有别的方法也可以,非常感谢。
程序中连接JDBC部分的代码如下,大家帮我看看有没有错误好吗:
public static boolean addSellOrder(SellOrderInfo aSellOrder){
Statement excuteSql = null;
Connection oracleConn = null;
//定义了sellOrder的各种属性
String productionId = aSellOrder.getProductionId() ;
String productionName = aSellOrder.getProductionName();
String customerId = aSellOrder.getCustomerId();
int productionCount = aSellOrder.getProductionCount();
float outPrice = aSellOrder.getOutPrice();
String employeeId = aSellOrder.getEmployeeId();
String sellDate =aSellOrder.getSellDate();
float allPrice = aSellOrder.getAllPrice();
oracleConn = ConnectionPool.getConnection();
try {
String insertSql = getInsertSql(productionId,productionName,customerId,outPrice,productionCount,employeeId,sellDate,allPrice);
System.out.println(insertSql);
excuteSql = oracleConn.createStatement();
System.out.println(excuteSql.toString());
excuteSql.execute(insertSql);
System.out.println("insert done");
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}finally {
try {
excuteSql.close();
oracleConn.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}这是生成sql语句的方法,应该没有问题吧:
public static String getInsertSql(String productionId,String productionName,String customerId,float outPrice,int productionCount,String employeeId,String sellDate,float allPrice){
String insertSql = "insert into t_sellOutOrder values(sellOutOrder_seq.currval,'" + productionId + "','" + productionName + "','" + customerId + "'," +
outPrice + "," + productionCount + ",'" + employeeId + "','" + sellDate + "'," + allPrice + ")";
return insertSql;
}
create table t_sellOutOrder(
sellOrderId varchar2(20) Primary Key,
goodsId varchar2(20),
goodsName varchar2(20) not null,
customerId varchar2(20) not null,Foreign Key(CustomerId) REFERENCES t_customer(CustomerId),
outPrice float,
goodsCount int not null,
employeeId varchar2(20) not null,Foreign Key(employeeId) REFERENCES t_employee(employeeId),
sellDate varchar2(20) not null,
allOutPrice float
);--创建一个序列进行编号自动增加
create sequence sellOutOrder_seq
minvalue 1
maxvalue 99999
start with 1
increment by 1
cache 20
order;--便写一个触发器实现插入后下一个编号自动加一
CREATE OR REPLACE TRIGGER sellOrder_ID_TRIGGER BEFORE
INSERT ON t_sellOutOrder FOR EACH ROW
declare
next_checkup_no number;
begin
select sellOutOrder_seq.nextval
into next_checkup_no
from dual;
:NEW.sellOrderId := next_checkup_no;
end;
/现在的问题是,在sqlplus中,手动insert数据,可以实现自动增加,而使用JDBC连接时,开始使用的Prepared Statement,总是出现一个异常,上网查后有人说这个是oracle的一个bug,无奈,改用Statement,自己写了一个getSql方法将参数传入,现在却在运行时抛出如下异常:java.sql.SQLException: ORA-08002: sequence SELLOUTORDER_SEQ.CURRVAL is not yet defined in this session
调试后发现程序连接时写入的sql语句没有任何问题(将程序输出的sql语句复制到sqlplus就可以插入),这个程序已经调试了两天了,还是没有解决,后天就要项目答辩了,真的很着急,希望高手们能帮帮我,指出我程序中的问题,或者就这个问题如果有别的方法也可以,非常感谢。
程序中连接JDBC部分的代码如下,大家帮我看看有没有错误好吗:
public static boolean addSellOrder(SellOrderInfo aSellOrder){
Statement excuteSql = null;
Connection oracleConn = null;
//定义了sellOrder的各种属性
String productionId = aSellOrder.getProductionId() ;
String productionName = aSellOrder.getProductionName();
String customerId = aSellOrder.getCustomerId();
int productionCount = aSellOrder.getProductionCount();
float outPrice = aSellOrder.getOutPrice();
String employeeId = aSellOrder.getEmployeeId();
String sellDate =aSellOrder.getSellDate();
float allPrice = aSellOrder.getAllPrice();
oracleConn = ConnectionPool.getConnection();
try {
String insertSql = getInsertSql(productionId,productionName,customerId,outPrice,productionCount,employeeId,sellDate,allPrice);
System.out.println(insertSql);
excuteSql = oracleConn.createStatement();
System.out.println(excuteSql.toString());
excuteSql.execute(insertSql);
System.out.println("insert done");
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
}finally {
try {
excuteSql.close();
oracleConn.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
}这是生成sql语句的方法,应该没有问题吧:
public static String getInsertSql(String productionId,String productionName,String customerId,float outPrice,int productionCount,String employeeId,String sellDate,float allPrice){
String insertSql = "insert into t_sellOutOrder values(sellOutOrder_seq.currval,'" + productionId + "','" + productionName + "','" + customerId + "'," +
outPrice + "," + productionCount + ",'" + employeeId + "','" + sellDate + "'," + allPrice + ")";
return insertSql;
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货