Java使用JDBC连接Oracle_MSSQL实例代码
一、statement
import java.sql.*;
public class testjdbc {
public static void main(string[] args) {
connection oracle_conn = null;
statement oracle_stmt = null;
resultset oracle_rs = null;
connection mssql_conn = null;
statement mssql_stmt = null;
resultset mssql_rs = null;
try {
class.forname("oracle.jdbc.driver.oracledriver");
oracle_conn = drivermanager.getconnection("jdbc:oracle:thin:@192.168.0.215:1521:orcl", "username", "password");
oracle_stmt = oracle_conn.createstatement();
class.forname("com.microsoft.sqlserver.jdbc.sqlserverdriver");
mssql_conn = drivermanager.getconnection("jdbc:sqlserver://192.168.0.213:1433;databasename=vis", "username", "password");
mssql_stmt = mssql_conn.createstatement();
mssql_rs = mssql_stmt.executequery("select * from videobaseinfo");
while(mssql_rs.next()) {
system.out.println("正在插入videoid：" + mssql_rs.getint("videoid") + "的记录...");
oracle_stmt.executeupdate("insert into video_baseinfo values("
+ mssql_rs.getint("videoid") + ",'"
+ mssql_rs.getstring("videoname") + "','"
+ mssql_rs.getstring("videoversion") + "',"
+ mssql_rs.getint("videomp4items") + ","
+ mssql_rs.getint("videormvbitems") + ",'"
+ mssql_rs.getstring("videoaliasname") + "','"
+ mssql_rs.getstring("videoage") + "'"
+ ")");
}
} catch (classnotfoundexception e) {
e.printstacktrace();
} catch (sqlexception e) {
e.printstacktrace();
} finally {
try {
if(oracle_rs != null) {
oracle_rs.close();
oracle_rs = null;
}
if(oracle_stmt != null) {
oracle_stmt.close();
oracle_stmt = null;
}
if(oracle_conn != null) {
oracle_conn.close();
oracle_conn = null;
}
if(mssql_rs != null) {
mssql_rs.close();
mssql_rs = null;
}
if(mssql_stmt != null) {
mssql_stmt.close();
mssql_stmt = null;
}
if(mssql_conn != null) {
mssql_conn.close();
mssql_conn = null;
}
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
}
二、preparedstatement
import java.sql.*;
public class testpreparedstatement {
public static void main(string[] args) {
connection oracle_conn = null;
preparedstatement oracle_stmt = null;
resultset oracle_rs = null;
connection mssql_conn = null;
statement mssql_stmt = null;
resultset mssql_rs = null;
try {
class.forname("oracle.jdbc.driver.oracledriver");
oracle_conn = drivermanager.getconnection("jdbc:oracle:thin:@192.168.0.215:1521:orcl", "username", "password");
oracle_stmt = oracle_conn.preparestatement("insert into video_iteminfo values(?, ?, ?, ?, ?, ?, ?, ?, ?)");
class.forname("com.microsoft.sqlserver.jdbc.sqlserverdriver");
mssql_conn = drivermanager.getconnection("jdbc:sqlserver://192.168.0.213:1433;databasename=vis", "username", "password");
mssql_stmt = mssql_conn.createstatement();
mssql_rs = mssql_stmt.executequery("select * from videoiteminfo");
while(mssql_rs.next()) {
system.out.println("正在插入itemindex：" + mssql_rs.getint("itemindex") + "的记录...");
oracle_stmt.setint(1, mssql_rs.getint("itemindex"));
oracle_stmt.setint(2, mssql_rs.getint("videoid"));
oracle_stmt.setstring(3, mssql_rs.getstring("videoitemname"));
oracle_stmt.setstring(4, mssql_rs.getstring("videoextname"));
oracle_stmt.setdouble(5, mssql_rs.getdouble("videosize"));
oracle_stmt.setstring(6, mssql_rs.getstring("videopath"));
oracle_stmt.setstring(7, mssql_rs.getstring("videotype"));
oracle_stmt.setdate(8, mssql_rs.getdate("videodate"));
oracle_stmt.setstring(9, mssql_rs.getstring("applicationway"));
oracle_stmt.executeupdate();
}
system.out.println("插入数据到video_iteminfo表中操作已完成！");
} catch (classnotfoundexception e) {
e.printstacktrace();
} catch (sqlexception e) {
e.printstacktrace();
} finally {
try {
if(oracle_rs != null) {
oracle_rs.close();
oracle_rs = null;
}
if(oracle_stmt != null) {
oracle_stmt.close();
oracle_stmt = null;
}
if(oracle_conn != null) {
oracle_conn.close();
oracle_conn = null;
}
if(mssql_rs != null) {
mssql_rs.close();
mssql_rs = null;
}
if(mssql_stmt != null) {
mssql_stmt.close();
mssql_stmt = null;
}
if(mssql_conn != null) {
mssql_conn.close();
mssql_conn = null;
}
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
}
三、callablestatement
import java.sql.*;
public class testproc {
/**
* @param args
*/
public static void main(string[] args) throws exception {
class.forname("oracle.jdbc.driver.oracledriver");
connection conn = drivermanager.getconnection("jdbc:oracle:thin:@192.168.0.1:1521:sxt", "scott", "tiger");
callablestatement cstmt = conn.preparecall("{call p(?, ?, ?, ?)}");
cstmt.registeroutparameter(3, types.integer);
cstmt.registeroutparameter(4, types.integer);
cstmt.setint(1, 3);
cstmt.setint(2, 4);
cstmt.setint(4, 5);
cstmt.execute();
system.out.println(cstmt.getint(3));
system.out.println(cstmt.getint(4));
cstmt.close();
conn.close();
}
}
四、batch
import java.sql.*;
public class testbatch {
public static void main(string[] args) throws exception {
class.forname("oracle.jdbc.driver.oracledriver");
connection conn = drivermanager.getconnection("jdbc:oracle:thin:@192.168.0.1:1521:sxt", "scott", "tiger");
/*
statement stmt = conn.createstatement();
stmt.addbatch("insert into dept2 values (51, '500', 'haha')");
stmt.addbatch("insert into dept2 values (52, '500', 'haha')");
stmt.addbatch("insert into dept2 values (53, '500', 'haha')");
stmt.executebatch();
stmt.close();
*/
preparedstatement ps = conn.preparestatement("insert into dept2 values (?, ?, ?)");
ps.setint(1, 61);
ps.setstring(2, "haha");
ps.setstring(3, "bj");
ps.addbatch();
ps.setint(1, 62);
ps.setstring(2, "haha");
ps.setstring(3, "bj");
ps.addbatch();
ps.setint(1, 63);
ps.setstring(2, "haha");
ps.setstring(3, "bj");
ps.addbatch();
ps.executebatch();
ps.close();
conn.close();
}
}
五、transaction
import java.sql.*;
public class testtransaction {
public static void main(string[] args) {
connection conn = null;
statement stmt = null;
try {
class.forname("oracle.jdbc.driver.oracledriver");
conn = drivermanager.getconnection("jdbc:oracle:thin:@127.0.0.1:1521:sxt", "scott", "tiger");
conn.setautocommit(false);
stmt = conn.createstatement();
stmt.addbatch("insert into dept2 values (51, '500', 'haha')");
stmt.addbatch("insert into dept2 values (52, '500', 'haha')");
stmt.addbatch("insert into dept2 values (53, '500', 'haha')");
stmt.executebatch();
conn.commit();
conn.setautocommit(true);
} catch (classnotfoundexception e) {
e.printstacktrace();
} catch(sqlexception e) {
e.printstacktrace();
try {
if(conn != null)
{
conn.rollback();
conn.setautocommit(true);
}
} catch (sqlexception e1) {
e1.printstacktrace();
}
}finally {
try {
if(stmt != null)
stmt.close();
if(conn != null)
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
}
六、scrollresultset
import java.sql.*;
public class testscroll {
public static void main(string args[]) {
try {
new oracle.jdbc.driver.oracledriver();
string url = "jdbc:oracle:thin:@192.168.0.1:1521:sxt";
connection conn = drivermanager
.getconnection(url, "scott", "tiger");
statement stmt = conn.createstatement(
resultset.type_scroll_insensitive,
resultset.concur_read_only);
resultset rs = stmt
.executequery("select * from emp order by sal");
rs.next();
system.out.println(rs.getint(1));
rs.last();
system.out.println(rs.getstring(1));
system.out.println(rs.islast());
system.out.println(rs.isafterlast());
system.out.println(rs.getrow());
rs.previous();
system.out.println(rs.getstring(1));
rs.absolute(6);
system.out.println(rs.getstring(1));
rs.close();
stmt.close();
conn.close();
} catch (sqlexception e) {
e.printstacktrace();
}
}
}
七、updateresultset
import java.sql.*;
public class testupdatars {
public static void main(string args[]){
try{
new oracle.jdbc.driver.oracledriver();
string url="jdbc:oracle:thin:@192.168.0.1:1521:sxt";
connection conn=drivermanager.getconnection(url,"scott","tiger");
statement stmt=conn.createstatement(resultset.type_scroll_insensitive,resultset.concur_updatable);
resultset rs=stmt.executequery("select * from emp2");
rs.next();
//更新一行数据
rs.updatestring("ename","aaaa");
rs.updaterow();
//插入新行
rs.movetoinsertrow();
rs.updateint(1, 9999);
rs.updatestring("ename","aaaa");
rs.updateint("mgr", 7839);
rs.updatedouble("sal", 99.99);
rs.insertrow();
//将光标移动到新建的行
rs.movetocurrentrow();
//删除行
rs.absolute(5);
rs.deleterow();
//取消更新
//rs.cancelrowupdates();
}catch(sqlexception e){
e.printstacktrace();
}
}
}