JDBC 2 query (web basic learning notes 8) and jdbc learning notes

JDBC 2 query (web basic learning notes 8) and jdbc learning notes
1. Create a database

-- Create tablespace tbs_newsDATAFILE 'f: \ ORACLE \ news. dbf 'size 10 mautoextend on; -- CREATE news user create user news identified by newsDEFAULT TABLESPACE tbs_news; -- authorize grant resource, connect to news; -- CREATE Table SELECT * FROM tab; ----- news publishing system ----- User table drop table NEWS_USER; create table NEWS_USER (id NUMBER (10, 0) primary key not null, --- user id username varchar2 (20) not null, --- Username password varchar2 (20) not null, --- password email varchar2 (100) NULL, usertype number () not null ---- User Type 0: Administrator 1: normal user ); 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; ------- a news classification table with a foreign key exists. Therefore, delete the subtable drop table NEWS_COMMENT; drop table NEWS_DETAIL; drop table NEWS_CATEGORY; create table NEWS_CATEGORY (id NUMBER) not null primary key, -- category ID name varchar2 (50) not null, -- category name createdate Date not null --- creation time); insert into NEWS_CATEGORY (id, name, createdate) VALUES (1, 'China', sysdate); insert into NEWS_CATEGORY (id, name, createdate) VALUES (2, 'International ', sysdate); insert into NEWS_CATEGORY (id, name, createdate) VALUES (3, 'enter', sysdate); insert into NEWS_CATEGORY (id, name, createdate) VALUES (4, 'military ', sysdate); insert into NEWS_CATEGORY (id, name, createdate) VALUES (5, 'caijing ', sysdate); insert into NEWS_CATEGORY (id, name, createdate) VALUES (6, 'Weather', sysdate); COMMIT; -- Query SELECT * FROM news_category; ----- create table NEWS_DETAIL (id number (10, 0) not null primary key, -- id categoryId number (10, 0) not null, -- news category id title varchar2 (100) not null, -- News title summary varchar2 (255) NULL, -- news abstract content clob null, -- news content picpath varchar2 (255) NULL, -- news image path author varchar2 (50) NULL, -- table issuer createdate date NULL, -- Creation Time modifydate date NULL, -- modification time Foreign key (categoryId) references NEWS_CATEGORY (id )); -- INSERT data INTO NEWS_DETAIL VALUES (, 'a Nigerian plane crashed ', 'a Nigerian plane crashed with heavy casualties', 'a Nigerian plane crashed with heavy casualties, 10 seriously injured ', '', 'admin', sysdate, sysdate); SELECT * FROM news_detail; ----- create table NEWS_COMMENT (id number (10, 0) primary key, -- id newsId number (2000) not null, -- Comment news id content varchar2 (), -- Comment content author varchar2 (50), -- Comment ip varchar2 (15 ), -- Comment on ip createdate date, -- posting time Foreign key (newsId) references NEWS_DETAIL (id); SELECT * FROM news_detail; delete from news_detail where id = 2; COMMIT;

2. Import oracle driver to web Project 3. Import Driver and establish a connection

// Establish the Connection object Connection conn = null; // establish the Statement object Statement stmt = null; // establish the result set object ResultSet

 

Use Class. forName () to import Driver

// (1) Use Class. forName () to load the driver Class. forName ("jdbc: oracle: thin: @ localhos: 1521: orcl", "news", "news ")

4. Connection to establish a Connection

// (2) use DriverManager. getconnection (url, user name, password) Connection creation return type is Connection type conn = DriverManager. getConnection ("jdbc: oracle: thin :@ localhost: 1521: orcl", "news", "news ");

5. Write and execute SQL statements

// (3) write the query SQL statement 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) create a Statement object, import SQL statements, and use the result set to receive stmt = conn. createStatement (); rs = stmt.exe cuteQuery (SQL );

6. Traverse result set output

// (5) traverse the result set // first declare the variable to store the Field System in the result set. out. println ("============= News list ========================"); 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 );

The complete code above:

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 () {// establish Connection object Connection conn = null; // create a Statement object Statement stmt = null; // create the result set object ResultSet rs = null; try {// (1) Use Class. forName to import drive Class. forName ("oracle. jdbc. driver. oracleDriver "); // (2) use DriverManager. getconnection (url, user name, password) Connection creation return type is Connection type conn = DriverManager. getConnection ("jdbc: oracle: thin :@ localhost: 1521: orcl", "news", "news"); // (3) compile the query SQL statement 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) create a Statement object, import SQL statements, and use the result set to receive stmt = conn. createStatement (); rs = stmt.exe cuteQuery (SQL); // (5) traverse the result set // first declare the variable to store the Field System in the result set. out. println ("============= News list ========================"); 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 ();}}