--創建news表空間CREATE TABLESPACE tbs_newsDATAFILE 'F:/Oracle/news.dbf'SIZE 10MAUTOEXTEND ON;--創建news用戶CREATE USER news IDENTIFIED BY newsDEFAULT TABLESPACE tbs_news;--授權GRANT RESOURCE,CONNECT TO news;--創建表SELECT * FROM tab;-----新聞發布系統-----用戶表drop table NEWS_USER;create table NEWS_USER(  id                NUMBER(10, 0)    PRIMARY KEY NOT NULL,  ---用戶編號  username          varchar2(20)     NOT NULL,              ---用戶名  passWord          varchar2(20)    NOT NULL,              ---密碼  email             varchar2(100)   NULL,  usertype          number(5,0)     NOT NULL               ----用戶類型  0：管理員 1：普通用戶);INSERT INTO NEWS_USER VALUES(1,'admin','admin','admin@bdqn.cn',0);INSERT INTO NEWS_USER VALUES(2,'user','user','user@bdqn.cn',1);INSERT INTO NEWS_USER VALUES(3,'test','test','test@bdqn.cn',1);COMMIT;SELECT * FROM news_user;-------新聞分類表， 有外鍵存在，因此先刪除子表drop table NEWS_COMMENT;drop table NEWS_DETAIL;drop table NEWS_CATEGORY;create table NEWS_CATEGORY(    id              NUMBER(10,0)     NOT NULL PRIMARY KEY,   --類別ID    name            varchar2(50)     NOT NULL,                 --類別名稱    createdate      Date             NOT　NULL ---創建時間);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(1,'國內',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(2,'國際',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(3,'娛樂',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(4,'軍事',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(5,'財經',sysdate); INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(6,'天氣',sysdate); COMMIT;--查詢SELECT * FROM news_category;-----新聞明細表create table NEWS_DETAIL(    id               number(10,0)    NOT NULL  PRIMARY KEY,    --id    categoryId       number(10,0)    NOT NULL,                 --新聞類別id    title                  varchar2(100)   NOT NULL,--新聞標題    summary          varchar2(255)   NULL,                   --新聞摘要    content          CLOB   NULL,                           --新聞內容    picpath          varchar2(255)   NULL,                  --新聞圖片路徑    author                  varchar2(50)    NULL,--發表者    createdate       date            NULL,                  --創建時間    modifydate       date            NULL,                  --修改時間    Foreign key(categoryId) references NEWS_CATEGORY(id));--插入數據INSERT INTO NEWS_DETAIL VALUES(1,1,'尼日利亞一架客機墜毀','尼日利亞一架客機墜毀，傷亡慘重','尼日利亞一架客機墜毀，傷亡慘重,10人重傷','','admin',sysdate,sysdate);SELECT * FROM news_detail;-----新聞評論表create table NEWS_COMMENT(    id               number(10,0)    PRIMARY KEY,            --id    newsId           number(10,0)    NOT NULL,               --評論新聞id    content          varchar2(2000),                         --評論內容    author           varchar2(50),                          --評論者    ip               varchar2(15),                          --評論ip    createdate       date,                                  --發表時間    Foreign key(newsId) references NEWS_DETAIL(id));SELECT * FROM news_detail;DELETE FROM news_detail WHERE ID=2;COMMIT;

1. oracle11g為例:D:/app/Administrator/product/11.2.0/dbhome_1/jdbc/lib/ojdbc6.jar
2. 將此ojdbc6.jar復制到項目中新建lib文件夾保存
3. 右擊項目選擇build path-buil confing--libaries-add jar選擇jar包所在路徑如news/WebRoot/WEB-INF/lib/ojdbc6.jar點擊確定

    // 建立連接對象        Connection conn = null;        // 建立Statement對象        Statement stmt = null;        // 建立結果集對象ResultSet

使用Class.forName()來導入Driver

//(1)使用Class.forName()加載驅動Class.forName("jdbc:oracle:thin:@localhos:1521:orcl","news","news")

// (2)使用DriverManager.getconnection(url,用戶名，密碼)建立連接返回類型是Connection類型            conn = DriverManager.getConnection(                    "jdbc:oracle:thin:@localhost:1521:orcl", "news", "news");

// (3)編寫查詢sql語句            String sql = "SELECT c.name,d.title,d.summary,d.content,d.author,d.createdate,d.modifydate FROM news_detail d,news_category c WHERE d.categoryid=c.id ";            // (4)建立Statement對象,將sql語句導入，使用結果集來接收            stmt = conn.createStatement();            rs = stmt.executeQuery(sql);

// (5)遍歷結果集            // 首先聲明變量用來存放結果集中的字段            System.out.println("===========新聞列表================");            while(rs.next()){            String catename = rs.getString("name");            String title = rs.getString("title");            String summary = rs.getString("summary");            String content = rs.getString("content");            String author = rs.getString("author");            Date createdate = rs.getDate("createdate");            Timestamp modifydate = rs.getTimestamp("modifydate");                                    System.out.println(catename + "/t" + title + "/t" + summary + "/t"                    + content + "/t" + author + "/t" + createdate + "/t"                    + modifydate);

以上完整代碼:

package pb.news;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Timestamp;import java.util.Date;public class newstest {    public void select() {        // 建立連接對象        Connection conn = null;        // 建立Statement對象        Statement stmt = null;        // 建立結果集對象ResultSet        ResultSet rs = null;        try {            // （1）使用Class.forName來導入drive            Class.forName("oracle.jdbc.driver.OracleDriver");            // (2)使用DriverManager.getconnection(url,用戶名，密碼)建立連接返回類型是Connection類型            conn = DriverManager.getConnection(                    "jdbc:oracle:thin:@localhost:1521:orcl", "news", "news");            // (3)編寫查詢sql語句            String sql = "SELECT c.name,d.title,d.summary,d.content,d.author,d.createdate,d.modifydate FROM news_detail d,news_category c WHERE d.categoryid=c.id ";            // (4)建立Statement對象,將sql語句導入，使用結果集來接收            stmt = conn.createStatement();            rs = stmt.executeQuery(sql);            // (5)遍歷結果集            // 首先聲明變量用來存放結果集中的字段            System.out.println("===========新聞列表================");            while(rs.next()){            String catename = rs.getString("name");            String title = rs.getString("title");            String summary = rs.getString("summary");            String content = rs.getString("content");            String author = rs.getString("author");            Date createdate = rs.getDate("createdate");            Timestamp modifydate = rs.getTimestamp("modifydate");                                    System.out.println(catename + "/t" + title + "/t" + summary + "/t"                    + content + "/t" + author + "/t" + createdate + "/t"                    + modifydate);            }        } catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } finally {            try {                rs.close();                stmt.close();                conn.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }    public static void main(String[] args) {        newstest nt = new newstest();        nt.select();    }}