实验四JDBC技术

1、实验四JDBC技术实验四JDBC应用【实验目的】：1.进一步熟悉MyEclipse下开发Web应用程序的步骤2.理解JDBC访问数据库的过 程与步骤3.掌握MySQL数据库的创建与访问。4. 掌握数据库访问过程中儿个常用类的使用5. 掌握如何将JDBC技术与Servlet和JSP技术相结合，用开发Web应用。【实 验内容】：创建与访问MySQL数据库；建立Web工程，将JSP技术与JDBC结合在一起使 用。验证课本例子。【实验步骤】：(一) 创建MySQL数据库1.选择"开始”"所有程序"一 "MySQL" 一 "MySQL Serv

2、er 5. 1" "MySQLCommand Line Clientn ,进入命令行窗口。输入密码：mysql»进入数据库管理系统。2. 创建数据库book_shop.使用命令create database book_shop:再选中数据库，使用命 令：use book_shop;3. 新建数据表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. 向该数据库中添加若干行记录，使用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。工程的结构如下 图所示：1. 创建四个包，分另lj是 com. controller, com. dao, com. domain, com. utilo2. 每个包中各新建一个类，分别是 BookController. java BookDao. java、Book, java 和 DBCon. java、。(1)DBCon. java的代码，用丁获得数据库连接的工具类。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的代码，用丁和数据库

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接收页面表单 中的数据