JDBC二查询(web基础学习笔记八)
一、建立数据库
--创建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(); } }
