web数据库实验代码.doc

计算机科学与技术系
实 验 报 告

专业名称 14-软件工程
课程名称 计算机组成与结构
姓 名 第一帅陆
同组人员
实验日期 2015-05-21

Web数据库编程
一、实验目的：
编写Java代码，熟悉并掌握JDBC的使用，包括Connection、Statement和ResultSet等对象的使用。

二、实验内容：
在MyEclipse环境下编写Java代码，运用JDBC技术完成相应的功能，调试运行程序。
三、实验要求：
1. 熟悉并掌握JDBC的配置和常用对象；
2. 运用JDBC技术完成规定功能；
3. 写出实验报告。
四、实验学时：2学时
五、实验步骤：
1．进入MyEclipse环境，新建一个Web Project；
2. 设计一个数据库，新建出版社表，出版社表字段包括出版社编码（主键），出版社名称。
3. 设计对出版社信息的显示、增加、编辑和删除功能，要求采用Statement对象发送SQL语句；
4. 调试运行程序。
六、选作实验
1. 设计对出版社信息的条件查询功能

首先得有sqljdbc.jar驱动 用以连接SQL Server数据库
实验代码：
xiancha.jsp
<%@ page language="java" contentType="text/html; charset=GBK"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

My JSP 'bookAddProc.jsp' starting page

ttp-equiv="cache-control" content="no-cache">

t>
function btnDelete(id){
if(confirm("确认删除该条记录吗?"))
location.href="/hehehehhehehehe/b/delete.jsp?id="+id; }

添加
<%
ResultSet rs=null;
Connection conn=null;
Statement stmt= null;

Class.forName"com.microsoft.sqlserver.jdbc.SQLServerDriver");

conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=bookshop","root","1234");

stmt = conn.createStatement();

rs = stmt.executeQuery("SELECT * FROM books");%>
>书号

<%
}
rs.close();
stmt.close();
conn.close();

%>
rs.getString("author") %>getString("bookname") %>
&author=<%=rs.getString("author") %>
&publish=<%=rs.getString("publish") %>
&price=<%=rs.getString("price") %>
&type=<%=rs.getString("type") %>">编辑 
">删除th = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

书籍添加

n="/hehehehhehehehe/b/tianxian.jsp" method="post">
>
e="text" name="author"/>tianxian.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

My JSP 'tianxian.jsp' starting page

iv="expires" content="0">

<%
request.setCharacterEncoding("utf-8");
String id = request.getParameter("bookId");
String num = request.getParameter("bookNumber");
String name= request.getParameter("bookName");
String author = request.getParameter("author");
String pub = request.getParameter("chu_ban_she_shi");
String price = request.getParameter("price");
String type= request.getParameter("type");

Connection conn = null;
PreparedStatement pstm = null;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;databasename=bookshop";
conn = DriverManager.getConnection(url,"root","1234");

String sql = "insert into books(bookid,booknumber,bookname,author,publish,price,type)values(?,?,?,?,?,?,?)";
pstm = conn.prepareStatement(sql);
pstm.setString(1,id);
pstm.setString(2,num);
pstm.setString(3,name);
pstm.setString(4,author);
pstm.setString(5pub);
pstm.setString(6,price);
pstm.setString(7,type);
pstm.executeUpdate();

if(pstm!=null)
pstm.close();
if(conn!=null)
conn.close();

response.sendRedirect("/hehehehhehehehe/b/xiancha.jsp");
%>

编辑页面
edit.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

My JSP 'edit.jsp' starting page

eta http-equiv="description" content="This is my page">

序号sePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

My JSP 'edit1.jsp' starting page

iv="pragma" content="no-cache">

<%
request.setCharacterEncoding("utf-8");
String id = request.getParameter("bookid");
String num = request.getParameter("booknumber");
String name= request.getParameter("bookname");
String pub = request.getParameter("publish");
String author = request.getParameter("author");
String price = request.getParameter("price");
String type= request.getParameter("type");

Connection conn = null;
PreparedStatement pstm = null;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url"jdbc:sqlserver://localhost:1433;databasename=bookshop";
conn = DriverManager.getConnection(url,"root","1234");

String sql = "update books set bookid=?,booknumber=?,bookname=?,author=?,publish=?,price=?,type=? where bookid=?";
pstm = conn.prepareStatement(sql);
pstm.setString(1,id);
pstm.setString(2,num);
pstm.setString(3,name);
pstm.setString(4,author);
pstm.setString(5,pub);
pstm.setString(6,price);
pstm.setString(7,type);
pstm.setString(8,id);
pstm.executeUpdate();
%>

删除并执行功能：
delete.jsp

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

"-//W3C//DTD HTML 4.01 Transitional//EN">

My JSP 'delete.jsp' starting page

"0">

<%
String id = request.getParameter("id");
Connection conn = null;
PreparedStatement pstm = null;
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;databasename=bookshop";
conn = DriverManager.getConnection(url,"root","1234");

String sql = "delete from books where bookid=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,id);
ps.execute();

%>