--創(chuàng)建news表空間CREATE TABLESPACE tbs_newsDATAFILE 'F:/Oracle/news.dbf'SIZE 10MAUTOEXTEND ON;--創(chuàng)建news用戶CREATE USER news IDENTIFIED BY newsDEFAULT TABLESPACE tbs_news;--授權(quán)GRANT RESOURCE,CONNECT TO news;--創(chuàng)建表SELECT * FROM tab;-----新聞發(fā)布系統(tǒng)-----用戶表drop table NEWS_USER;create table NEWS_USER(  id                NUMBER(10, 0)    PRIMARY KEY NOT NULL,  ---用戶編號(hào)  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 ---創(chuàng)建時(shí)間);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(1,'國(guó)內(nèi)',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(2,'國(guó)際',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(3,'娛樂(lè)',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(4,'軍事',sysdate);INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(5,'財(cái)經(jīng)',sysdate); INSERT INTO NEWS_CATEGORY(id,name,createdate) VALUES(6,'天氣',sysdate); COMMIT;--查詢SELECT * FROM news_category;-----新聞明細(xì)表create table NEWS_DETAIL(    id               number(10,0)    NOT NULL  PRIMARY KEY,    --id    categoryId       number(10,0)    NOT NULL,                 --新聞?lì)悇eid    title                  varchar2(100)   NOT NULL,--新聞標(biāo)題    summary          varchar2(255)   NULL,                   --新聞?wù)?   content          CLOB   NULL,                           --新聞內(nèi)容    picpath          varchar2(255)   NULL,                  --新聞圖片路徑    author                  varchar2(50)    NULL,--發(fā)表者    createdate       date            NULL,                  --創(chuàng)建時(shí)間    modifydate       date            NULL,                  --修改時(shí)間    Foreign key(categoryId) references NEWS_CATEGORY(id));--插入數(shù)據(jù)INSERT INTO NEWS_DETAIL VALUES(1,1,'尼日利亞一架客機(jī)墜毀','尼日利亞一架客機(jī)墜毀，傷亡慘重','尼日利亞一架客機(jī)墜毀，傷亡慘重,10人重傷','','admin',sysdate,sysdate);SELECT * FROM news_detail;-----新聞評(píng)論表create table NEWS_COMMENT(    id               number(10,0)    PRIMARY KEY,            --id    newsId           number(10,0)    NOT NULL,               --評(píng)論新聞id    content          varchar2(2000),                         --評(píng)論內(nèi)容    author           varchar2(50),                          --評(píng)論者    ip               varchar2(15),                          --評(píng)論ip    createdate       date,                                  --發(fā)表時(shí)間    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復(fù)制到項(xiàng)目中新建lib文件夾保存
3. 右擊項(xiàng)目選擇build path-buil confing--libaries-add jar選擇jar包所在路徑如news/WebRoot/WEB-INF/lib/ojdbc6.jar點(diǎn)擊確定

    // 建立連接對(duì)象        Connection conn = null;        // 建立Statement對(duì)象        Statement stmt = null;        // 建立結(jié)果集對(duì)象ResultSet

使用Class.forName()來(lái)導(dǎo)入Driver

//(1)使用Class.forName()加載驅(qū)動(dòng)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語(yǔ)句            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對(duì)象,將sql語(yǔ)句導(dǎo)入，使用結(jié)果集來(lái)接收            stmt = conn.createStatement();            rs = stmt.executeQuery(sql);

// (5)遍歷結(jié)果集            // 首先聲明變量用來(lái)存放結(jié)果集中的字段            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() {        // 建立連接對(duì)象        Connection conn = null;        // 建立Statement對(duì)象        Statement stmt = null;        // 建立結(jié)果集對(duì)象ResultSet        ResultSet rs = null;        try {            // （1）使用Class.forName來(lái)導(dǎo)入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語(yǔ)句            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對(duì)象,將sql語(yǔ)句導(dǎo)入，使用結(jié)果集來(lái)接收            stmt = conn.createStatement();            rs = stmt.executeQuery(sql);            // (5)遍歷結(jié)果集            // 首先聲明變量用來(lái)存放結(jié)果集中的字段            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();    }}