一、建立数据库
--创建news表空间
CREATE TABLESPACE tbs_news
DATAFILE 'F:\ORACLE\news.dbf'
SIZE 10M
AUTOEXTEND ON;
--创建news用户
CREATE USER news IDENTIFIED BY news
DEFAULT 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;
二、将oracle的driver导入到web项目中
- oracle11g为例:D:\app\Administrator\product\11.2.0\dbhome_1\jdbc\lib\ojdbc6.jar
- 将此ojdbc6.jar复制到项目中新建lib文件夹保存
- 右击项目选择build path-buil confing–libaries-add jar选择jar包所在路径如news/WebRoot/WEB-INF/lib/ojdbc6.jar点击确定
三、将Driver导入并建立连接
// 建立连接对象
Connection conn = null;
// 建立Statement对象
Statement stmt = null;
// 建立结果集对象ResultSet
使用Class.forName()来导入Driver
//(1)使用Class.forName()加载驱动
Class.forName("jdbc:oracle:thin:@localhos:1521:orcl","news","news")
四、Connection来建立连接
// (2)使用DriverManager.getconnection(url,用户名，密码)建立连接返回类型是Connection类型
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "news", "news");
五、写SQL并执行
// (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(); }}
