6使用JDBC操作数据库
-----新闻分类表, 有外键存在,因此先删除子表
drop table NEWS_COMMENT;
drop table NEWS_DETAIL;
drop table NEWS_CATEGORY;
create table NEWS_CATEGORY(
id NUMBER(10,0) NOT NULL PRIMARY KEY,
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);
-----新闻明细表
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);
-----新闻评论表
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
1.解决问题
2.使用JDBC查询新闻信息
package com.pb.news.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
public class NewsDao {
// 查询新闻信息
public void getNewsList(){
Connection connection=null;
Statement stmt=null;
ResultSet rs=null;
try {
//(1)Class.forName()加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//(2)DriverManager.getConnection(URL,用户名,密码)获得数据库连接 (Connection)
connection=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:news","scott","tiger");
//(3)获得Statement对象,执行SQL语句
String sql="select * from news_detail";
stmt=connection.createStatement();
rs=stmt.executeQuery(sql);
//(4)处理执行结果(ResultSet),
while(rs.next()){
int id=rs.getInt("id");
String title=rs.getString("title");
String summary=rs.getString("summary");
String content=rs.getString("content");
String author=rs.getString("author");
Timestamp time=rs.getTimestamp("createdate");
System.out.println(id + "\t" + title + "\t" + summary + "\t"+