實驗四JDBC技術

1、實驗四JDBC技術實驗四JDBC應用【實驗目的】：1.進一步熟悉MyEclipse下開發(fā)Web應用程序的步驟2.理解JDBC訪問數(shù)據(jù)庫的過 程與步驟3.掌握MySQL數(shù)據(jù)庫的創(chuàng)建與訪問。4. 掌握數(shù)據(jù)庫訪問過程中兒個常用類的使用5. 掌握如何將JDBC技術與Servlet和JSP技術相結(jié)合，用開發(fā)Web應用。【實 驗內(nèi)容】：創(chuàng)建與訪問MySQL數(shù)據(jù)庫；建立Web工程，將JSP技術與JDBC結(jié)合在一起使 用。驗證課本例子。【實驗步驟】：(一) 創(chuàng)建MySQL數(shù)據(jù)庫1.選擇"開始”"所有程序"一 "MySQL" 一 "MySQL Serv

2、er 5. 1" "MySQLCommand Line Clientn ,進入命令行窗口。輸入密碼：mysql»進入數(shù)據(jù)庫管理系統(tǒng)。2. 創(chuàng)建數(shù)據(jù)庫book_shop.使用命令create database book_shop:再選中數(shù)據(jù)庫，使用命 令：use book_shop;3. 新建數(shù)據(jù)表books,并設置字段id為主鍵、口增長。使用命令create table books ( id int(8) auto_increment, name varchar(50), price float (8), author varchar(50), bookConce

3、rn varchar(50), counts int(4), primary key (id);4. 向該數(shù)據(jù)庫中添加若干行記錄，使用insert命令。女口： insert into books(name, price, author, bookConcern, counts)values( *JSPProgramming * ,29.0,4 sunyang technology' ,*Post &Telecom Press * ,106);注意：由于主鍵設成了口增長，因而在插入記錄時不需要插入主鍵字段id。(二) 在MyEclipse的workspace中，新建一個Web工程

4、JDBCDemo。工程的結(jié)構(gòu)如下 圖所示：1. 創(chuàng)建四個包，分另lj是 com. controller, com. dao, com. domain, com. utilo2. 每個包中各新建一個類，分別是 BookController. java BookDao. java、Book, java 和 DBCon. java、。(1)DBCon. java的代碼，用丁獲得數(shù)據(jù)庫連接的工具類。package com util;import javasqlConnection; import javasqlDriverManager; importjava sql SQLException;pub

5、lic class DBCon public static Connection getConnectionO String url = ; String user = ; String psw = ; Connection conn = null; try try return null;conn = DriverManager.getConnection(url, user, psw);connsetAutoCommit(false); return conn;e. printstdckTraceO ;Class forName(); e. printStackTrace 0;:catch

6、 (ClassNotFoundException e) catch (SQLException e) (2) Book, java的代碼，用于代表圖書信息的實體類，該類擁有一些代表圖書信息的 私有屬性以及各個屬性的set方法和get方法；package com .domain;public class Book private Long id; private String name; private double price; private String author; private String bookConcern; private int counts; public BookO

7、 public Long getldO return id; this id = id; public void setId(Long id) public String getName 0return name; public void setName(String name)thisname = name; public double getPrice()return price; public void setPrice(double price) this .price = price; public String getAuthor0 return author: public vo

8、id setAuthor(String author) thisauthor = author; public String getBookConcern() return bookConcern; public void setBookConcern(String bookConcern) thisbookConcern = bookConcern; public int getCountsO return counts; public void setCounts (int counts) this .counts = counts; (3) BookDao. java的代碼，用丁和數(shù)據(jù)庫

9、進行交互，查找所有的記錄、以及添加新的。package com.do;import java sql Connection;import java sql PreparedStatement; import java sql ResuItSet;import javasqlSQLException; import java .util. ArrayList; import java uti1 List;import comdomoin. Book; import com .util. DBCon:public class BookDao private Connection conn;查詢所有

10、圖書信息conn = DBCon getConnectionO ;String listSQL = ; List list = new ArrayList(); tryreturn list;PreparedStatement psmt =conncommit(); return list; printStackTraceO ; if (conn != null) try conn close 0;e. printstdckTraceO; catch (SQLException e) Book book = new BookO ; book setld(rs getLong(l); book

11、setName(rs getString(2);book setPrice(rs getDouble(3); book setAuthor(rs getString(4);book setBookConcern(rs getString(5); book setCounts(rs getlnt(6); list add(book);conn prepareStatement(listSQL);:catch (Exception e) finally public boolean saveUser(Book book) throws Exception conn = DBCongetConnec

12、tionO ; /*System out print In (book getNameO) ; System out println(book getPriceO);System out printin(book getAuthor0);System out printin(book getBookConcern();System out printin(book getCounts 0); */String listSQL = INTO books VALUES(?, ?, ?, ?,?) String listSQL=books(name, price, author, bookConce

13、rn, counts)values(?,?,?,?,?);PreparedStatement pstmt = try return false;pstmt setString(lt book getNameO) ; pstmt setDouble(2, book getPrice (); pstmt setString(3, book. getAuthor0); pstmt setString(4, book getBookConcern(); pstmt setlnt(5, book getCounts();pstmt executeUpdate(); conn. commit(); return true; conn. rollback();e. printStackTrace 0 ; conn closeO ;conn prepareStatement(listSQL);:catch (SQLException e) finally （4） BookController. java的代碼，該類繼承 HttpServlet類，用J接收頁面表單 中的數(shù)據(jù)