如题,我是使用触发器和序列自动生成主键。插入一条记录后该如何返回最新插入的主键值呢?
部分代码如下: public void getlist() throws UnsupportedEncodingException {
Connection con = Connect.getCon();
ResultSet rs = null;
Statement stm = null;
members.clear();
String ctymsql="";
try { con.setAutoCommit(false);
stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
int rows = sh.getRows();
System.out.println(rows);
String email="";
for (int i = 1; i < rows; i++) { sql.getsql(i);
email=sh.getCell(sql.getCytmembermap().get("EMAIL"), i).getContents();
ctymsql = sql.getCtymsql();
ctyhList.add(sql.getCtyhsql());
//ctymList.add(sql.getCtymsql());
ctysList.add(sql.getCtyssql());
//System.out.println(m6699sql);
stm.executeUpdate(ctymsql, Statement.RETURN_GENERATED_KEYS);
//System.out.println("-------------");
int autoIncKeyFromFunc = -1;
rs = stm.executeQuery("select member_autoinc.currval as cur from dual");
if (rs.next()) {
autoIncKeyFromFunc = rs.getInt("cur");
members.add(autoIncKeyFromFunc);
if(!email.equals("")){
//插入数据表member_email
stm.execute("insert into tbl_member_email(MEMBER_ID,EMAIL,FLAG) value("+autoIncKeyFromFunc+",'"+email+"',0)");
}
} else {
// throw an exception from here
}
rs.close();
rs = null; }
con.commit();
部分代码如下: public void getlist() throws UnsupportedEncodingException {
Connection con = Connect.getCon();
ResultSet rs = null;
Statement stm = null;
members.clear();
String ctymsql="";
try { con.setAutoCommit(false);
stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
int rows = sh.getRows();
System.out.println(rows);
String email="";
for (int i = 1; i < rows; i++) { sql.getsql(i);
email=sh.getCell(sql.getCytmembermap().get("EMAIL"), i).getContents();
ctymsql = sql.getCtymsql();
ctyhList.add(sql.getCtyhsql());
//ctymList.add(sql.getCtymsql());
ctysList.add(sql.getCtyssql());
//System.out.println(m6699sql);
stm.executeUpdate(ctymsql, Statement.RETURN_GENERATED_KEYS);
//System.out.println("-------------");
int autoIncKeyFromFunc = -1;
rs = stm.executeQuery("select member_autoinc.currval as cur from dual");
if (rs.next()) {
autoIncKeyFromFunc = rs.getInt("cur");
members.add(autoIncKeyFromFunc);
if(!email.equals("")){
//插入数据表member_email
stm.execute("insert into tbl_member_email(MEMBER_ID,EMAIL,FLAG) value("+autoIncKeyFromFunc+",'"+email+"',0)");
}
} else {
// throw an exception from here
}
rs.close();
rs = null; }
con.commit();
package com.zfz.burke.dbutil;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;public class DataBaseConnection {
//定义一个static类型的方法,用户连接数据库
public static Connection getConnection(){
Connection conn=null;
String url="jdbc:oracle:thin:@192.168.0.200:1521:mygod";
String uname="scott";
String upass="tiger";
String classname="oracle.jdbc.driver.OracleDriver";
//加载驱动
try {
Class.forName(classname);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//获取连接
try {
conn=DriverManager.getConnection(url,uname,upass);
System.out.println("--- 数据库连接已经建立成功...");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}}package com.zfz.burke.model.daoimpl;import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;import com.zfz.burke.dbutil.DataBaseConnection;
import com.zfz.burke.model.dao.DeptDAO;public class DeptDAOImpl implements DeptDAO{ @Override
public boolean deleteDeptByDeptno(int deptno) {
// TODO Auto-generated method stub
boolean flag=false;
//建立与数据库的连接
Connection conn=DataBaseConnection.getConnection();
//编写调用存储过程的SQL语句
String callsql="{ call delDeptByDeptno(?,?)}";
//创建调用存储过程的语句集对象
java.sql.CallableStatement cs=null;
try {
cs=conn.prepareCall(callsql);
//cs对象需要设定参数
cs.setInt(1, deptno);
//cs对象注册存储过程种输出模式参数
cs.registerOutParameter(2, Types.VARCHAR);
//cs对象执行调用
cs.execute();
//获取执行结果
String result=cs.getString(2);
//执行业务处理
if(result.equals("部门删除成功")){
flag=true;
}else{
flag=false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return flag;
}
}
package com.zfz.burke.model.daoimpl;import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;import com.zfz.burke.dbutil.DataBaseConnection;
import com.zfz.burke.model.dao.EmpDAO;public class EmpDAOImpl implements EmpDAO{
/*
* 通过调用PLSQL函数实现
*
* 函数的输入参数为:雇员姓名
* 函数的返回值为:该雇员的工资
* 函数的调用形式为:String sql="{ call ?:=function_name(参数)}";
* */ @Override
public double findSalOfEmpByEname(String ename) {
// TODO Auto-generated method stub
double result=-1;
//获取与数据库的连接
Connection conn=DataBaseConnection.getConnection();
//封装SQL语句
String sql="{call ?:=get_sal(?)}";
//获取调用的语句集
java.sql.CallableStatement cs=null;
try {
cs=conn.prepareCall(sql);
cs.setString(2, ename);
cs.registerOutParameter(1, Types.DOUBLE);
cs.execute();
result=cs.getDouble(1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return result;
} /*
* 通过这个方法按照给定的雇员编号,可以查询雇员的编号,姓名,工资信息
* 存储过程输入参数为:雇员编号(Number)
* 存储过程输出参数为:雇员编号(Number)、雇员姓名(varchar2)、雇员工资(number(7,2))
* 存储过程调用形式为:String sql="{call findEmpByEmpnoForJAVA(?,?,?,?)}";
* */
@Override
public void findEmpInfoByEmpno(int empno) {
// TODO Auto-generated method stub
//获取与数据库的连接
Connection conn=DataBaseConnection.getConnection();
//封装SQL语句
String sql="{call findEmpByEmpnoForJAVA(?,?,?,?)}";
//获取调用的语句集
java.sql.CallableStatement cs=null;
try {
cs=conn.prepareCall(sql);
//需要给设定的占位符号设置,并且注册Oracle存储过程输出模式参数值的类型
cs.setInt(1, empno);
//注册返回参数
cs.registerOutParameter(2, Types.INTEGER);
cs.registerOutParameter(3, Types.VARCHAR);
cs.registerOutParameter(4, Types.DOUBLE);
//执行
cs.execute();
//获取执行结果并打印输出
int eno=cs.getInt(2);
String ename=cs.getString(3);
double salary=cs.getDouble(4);
System.out.println ("雇员编号: "+eno+"\t雇员姓名: "+ename+"\t雇员工资: "+salary);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}}
package com.zfz.burke.test;import com.zfz.burke.model.daoimpl.DeptDAOImpl;public class TestingDeptDAOImpl {
/*
* 测试删除执行部门信息的方法
* */
public static void testDeleteDeptByDeptno(int deptno){
DeptDAOImpl deptDAOImpl=new DeptDAOImpl();
boolean flag=deptDAOImpl.deleteDeptByDeptno(deptno);
if(flag){
System.out.println(deptno+" 号部门删除成功");
}else{
System.out.println(deptno+" 号部门删除失败");
}
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
testDeleteDeptByDeptno(90);
}}
package com.zfz.burke.test;import com.zfz.burke.model.daoimpl.EmpDAOImpl;public class TestingEmpDAOImpl { /**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
EmpDAOImpl empDAOImpl=new EmpDAOImpl();
//empDAOImpl.findEmpInfoByEmpno(7782);
//按照给定的雇员姓名,查找该雇员的工资
double res=empDAOImpl.findSalOfEmpByEname("smith");
System.out.println("Smith的工资为: "+res);
}}
package com.zfz.burke.test;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;import com.zfz.burke.dbutil.DataBaseConnection;public class TestProcedureTHREE { /**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub Statement stmt = null;
ResultSet rs = null;
Connection conn = null; try { conn = DataBaseConnection.getConnection(); CallableStatement proc = null;
proc = conn.prepareCall("{ call scott.testc(?) }");
// 由于过程返回列表对象,即一个查询结果集,所以需要注册CURSOR类型的参数
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
//proc对象执行
proc.execute();
//获取执行以后的结果集
rs = (ResultSet) proc.getObject(1); while (rs.next()) {
System.out.println("雇员编号: " + rs.getString(1) +" 雇员姓名: "
+ rs.getString(2) + " ");
}
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception ex2) {
ex2.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
} catch (SQLException ex1) {
}
} }}
package com.zfz.burke.model.dao;public interface DeptDAO { //给定部门编号,删除指定的部门
public boolean deleteDeptByDeptno(int deptno);
}
package com.zfz.burke.model.dao;public interface EmpDAO {
//定义一个调用Oracle存储过程返回单行记录的方法
public void findEmpInfoByEmpno(int empno);
//根据给定的雇员姓名,查找雇员工资的方法
public double findSalOfEmpByEname(String ename);
}
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;public class OracleTest { public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = OracleConnectionFactory.getConnection();
DatabaseMetaData dmd = con.getMetaData();
System.out.println("database product name: " + dmd.getDatabaseProductName());
System.out.println("database product version: " + dmd.getDatabaseProductVersion());
System.out.println("supports generate keys? " + dmd.supportsGetGeneratedKeys());
String sql = "INSERT INTO t_mt_stat (id, mt_time, stat, stat_time) " +
"VALUES (s_mt_stat.nextval, ?, ?, ?)";
ps = con.prepareStatement(sql, new String[]{"ID"}); // 后面一个参数表示需要返回的列
ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
ps.setString(2, "Y");
ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
ps.executeUpdate();
if(dmd.supportsGetGeneratedKeys()) {
rs = ps.getGeneratedKeys();
while(rs.next()) {
// 如果使用 rs.getInt("ID") 会报错,奇怪的 Oracle JDBC 驱动!
System.out.println("ID: " + rs.getInt(1));
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(rs, ps, con);
}
}
}
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;public class OracleTest { public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = OracleConnectionFactory.getConnection();
DatabaseMetaData dmd = con.getMetaData();
System.out.println("database product name: " + dmd.getDatabaseProductName());
System.out.println("database product version: " + dmd.getDatabaseProductVersion());
System.out.println("supports generate keys? " + dmd.supportsGetGeneratedKeys());
String sql = "INSERT INTO t_mt_stat (id, mt_time, stat, stat_time) " +
"VALUES (s_mt_stat.nextval, ?, ?, ?)";
ps = con.prepareStatement(sql, new String[]{"ID"}); // 后面一个参数表示需要返回的列
ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
ps.setString(2, "Y");
ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
ps.executeUpdate();
if(dmd.supportsGetGeneratedKeys()) {
rs = ps.getGeneratedKeys();
while(rs.next()) {
// 如果使用 rs.getInt("ID") 会报错,奇怪的 Oracle JDBC 驱动!
System.out.println("ID: " + rs.getInt(1));
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(rs, ps, con);
}
}
}
select seqname.nextval from dual 查询序列下一值
就算是hibernate自己也是这么实现的,hibernate的save方法返回主键值,如果你打开show_sql,你完全可以看见hibernate先执行select seq_sqlsetence.nextval from dual 语句,然后再执行insert into........这样的语句的。
当然我是通过JDBC实现的,最后顺便说一句 seq_sqlsetence.nextval 你需要取一个别名,不然不好取值,例如语句这么写
select seq_sqlsetence.nextval as id from dual这样你就可以通过res.getString("ID");把值取出来了。
2.根据oracle的标准currValue这个之前必须要先执行nextValue。这两个调用必须在同一次事务(更正确的讲可能是session)中。否则会报错!