这真是个好网站我要努力工作
分类: Java
2014-08-22 14:33:28
用jdbc给数据库插入数据,主从表插入的过程中,需要先取出主表id。
conn = DriverManager.getConnection(PL.DATABASE_URL ,PL.DATABASE_UID,PL.DATABASE_PWD );
pstmt = conn.prepareStatement(PL.InsertPerson,Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, this.person.getAddress());
pstmt.executeUpdate();
ResultSet rs=pstmt.getGeneratedKeys();
long m=(long) 0;
if(rs.next()){
/*mysql code*/ m=rs.getLong(1);
}
pstmt.close();
pstmt = null;
return m;
以上代码在mysql数据库环境下工作正常,转移到oracle环境出现以下异常:
void insertPersons(ServiceFormBean bean): SQLException 无效的列类型: getLong not implemented for class oracle.jdbc.driver.T4CRowidAccessor
后在网上找到以下解决方法,感谢网友智慧:
1,用JDBC的getGeneratedKeys,返回的是Oracle 的rowid.第一次返回rowid, 第二次再通过rowid再查一次数据库。
String sql = "insert into xx_cust(cust_type,cust_name,login_name)"
+ " values('ipn','aaa','bb')";
Connection conn = DBConnectionManager.getConnection();
PreparedStatement sta = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
sta.execute();
System.out.println(sta.getGeneratedKeys());
ResultSet rest = sta.getGeneratedKeys();
rest.next();
String rowid=rest.getString(1);
System.out.println("rowid="+rowid);
PreparedStatement stat=conn.prepareStatement("select cust_id from xx_cust where rowid=?");
stat.setString(1, rowid);
ResultSet rest2 = stat.executeQuery();
rest2.next();
String custId=rest2.getString(1);
System.out.println("custid="+custId);
2,通过用Oracle 的returning 语句
因为要用到oracle自己的API,所以如何用到像c3po等通用数据库连接池的话可以不能用。但是在数据库只连接一次。Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();?
String url="jdbc:oracle:thin:@192.168.3.95:1521:tdm"; //orcl为数据库的SID?
String user="pesup";?
String password="pesup";?
Connection conn= DriverManager.getConnection(url,user,password);
String sql = "insert into xx_cust(cust_type,cust_name,login_name)"
+ " values('ipn','aaa','bb') returning cust_id into ?";
OraclePreparedStatement sta = (OraclePreparedStatement) conn
.prepareStatement(sql);
sta.registerReturnParameter(1, OracleTypes.INTEGER);
int count = sta.executeUpdate();
if (count > 0) {
ResultSet rset = sta.getReturnResultSet();
while (rset.next()) {
String name = rset.getString(1);
System.out.println("name=" + name);}