import oracle.sql.*;
import oracle.jdbc.pool.OracleDataSource;
public class JdbcOracle {
public static void main(String[] args) {
// URL格式:drivername:@driver_information
// 1,drivername主要有以下兩種
// jdbc:oracle:thin (thin驅動程式)
// jdbc:oracle:oci (oci驅動程式)
// 2,driver_information
// host_nameort:database_sid
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@localhost:1521:ORADB";
String username = "scott";
String password = "tiger";
try {
//一、註冊驅動程式
// 方法一Class.forName("oracle.jdbc.OracleDriver";
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
//二、開啟資料庫連線
// 方法一,使用oracle資料來源物件?
// oracle.jdbc.pool.OracleDataSource ds=new oracle.jdbc.pool.OracleDataSource();
// ds.setServerName("localhost";
// ds.setDatabaseName("ORADB"; //資料庫存名
// ds.setDriverType("oci"; //要使用的JDBC驅動程式(OracleDatasore的擴充套件)
// ds.setURL("jdbc:oracle:thin:@localhost:1521:ORADB"; //指定資料庫的URL(OracleDataSource的擴充套件)
// ds.setDataSourceName(""; //底層資料來源的名稱
// ds.setNetworkProtocol("tcp";//用於資料庫通訊的協議
// ds.setPortNumber(1521);//埠號
// ds.setUser("scott";
// ds.setPassword("tiger";
// Connection conn=ds.getConnection();
//方法二、使用Drivermanger
conn = DriverManager.getConnection(url, username, password);
//設定事務提交模式
//conn.setAutoCommit(true);
//若禁止了自動提交模式,那麼在關閉Connection物件時會執行一次自動隱式提交,以保證還沒有提交的所有DML語句被自動提交
conn.setAutoCommit(false);
//三、建立JDBC Statement物件
stmt = conn.createStatement();
//PreparedStatement pstmt=conn.prepareStatement("帶有引數的SQL語句");
//CallableStatement cstmt=conn.prepareCall("呼叫儲存過程的語句");
//四、從資料庫獲取行
//select 語句用executeQuery()
//insert,update,delete語句用 executeUpdate()
//若預先不知道要執行的SQL語句型別,那麼用execute()
rs = stmt.executeQuery("select id,name,age,sex,birth from employee");
//五、從資料庫獲取行
while (rs.next()) {
int id = rs.getInt("id";
String name = rs.getString("name";
int age = rs.getInt("age";
String sex = rs.getString("sex";
Date birth = rs.getDate("birth";
}
//rs.close();
//六、向資料庫中新增行(注:月份的編碼是從0開始的,因此月份1代表2月)
java.sql.Date date = new java.sql.Date(82, 10, 05);
int i = stmt.executeUpdate("insert into employee values" +"(1,'qds',22,'1',TO_DATE(date,'YYYY,MM,DD'))";
//七、修改資料中的行
int j = stmt.executeUpdate("update employee set age=21 where id=1";
//八、從資料庫中刪除行
int k = stmt.executeUpdate("delete from employee set id=1";
//九、處理資料庫的NULL值方法一:使用結果集物件的wasNull方法判斷
conn.commit();
rs = stmt.executeQuery("select id,type_id,prod_name from product where id=1");
//此次假設type_id列為Null值
System.out.println("id=" + rs.getInt("id");
System.out.println("type_id=" + rs.getInt("type_id");
if (rs.wasNull()) {
System.out.println("type_id was null!";
}
System.out.println("prod_name=" + rs.getString("prod_name");
//九、處理資料庫的NULL值方法二:使用JAVA包裝器類.因為JAVA包裝器類可以賦於NULL值
//java.lang.Integer typeId=(java.lang.Integer)rs.getObject("type_id";
//System.out.println(typeId);此時typeId的值為Null
//在向資料庫插入或更新某一行為NULL值時,也可以使用JAVA包裝器物件
//java.lang.Double price=null;
//int ii=stmt.executeUpdate("update products set price="+price+" where id=12";
rs.close();
//十、執行資料定義語言語句(DDL:CREATE,ALTER,DROP)----採用execute()方法執行DDL語句
//執行DDL語句會導致一次隱式提交,因此,如果你在發出DDL語句之前執行了一些未提交的DML語句,那麼這些DML語句將被提交
boolean result = stmt.execute("create table customers(" +
"id integer constraint customers_pK primary key," +
"first_name varchar2(10) not null," +
"last_name varchar2(10) not null," +
"dob date," +
"phone varchar2(15)" +
""
;
if (result == true) {
System.out.println("The table has Created!";
}
else {
System.out.println("The table hasn't Create";
}
}
catch (Exception e) {
System.out.println("error: " + e);
try {
conn.rollback();
}
catch (SQLException sqle) {}
}
finally {
try {
if (rs != null)
rs.close();
}
catch (SQLException sqle) {
System.out.println("SQLState: " + sqle.getSQLState());
System.out.println("SQLErrorCode: 錯誤程式碼" + sqle.getErrorCode());
System.out.println("SQLErrorMessage:錯誤情況的字串 " + sqle.toString());
}
try {
if (stmt != null)
stmt.close();
}
catch (SQLException sqle1) {
System.out.println("SQLState: " + sqle1.getSQLState());
System.out.println("SQLErrorCode: 錯誤程式碼" + sqle1.getErrorCode());
System.out.println("SQLErrorMessage:錯誤情況的字串 " + sqle1.toString());
}
try {
if (conn != null)
conn.close();
}
catch (SQLException sqle2) {
System.out.println(sqle2.toString());
System.out.println(sqle2.getSQLState());
System.out.println(sqle2.getErrorCode());
}
}
} //方法結束
} //類結束