一、实验目的
1
、了解
JDBC
的概念及工作原理;
2
、熟悉
JDBC API
的主要接口或类;
3
、掌握使用
JDBC
进行数据库访问(增删改查)的步骤和方法;
二、实验内容和要求
采用
JSP+Servlet+JavaBean
架构,设计并实现如下内容:
1
、添加用户页面
(AddUser.jsp)
;
2
、删除用户页面
(Delete.jsp)
;
3
、查找和修改用户页面
(SearchAndModify.jsp)
。
![](https://i-blog.csdnimg.cn/blog_migrate/563d014bac75430f29d868ebeb9c20b6.png)
Step2:分别执行下列命令://支持中文
① 创建数据库:
CREATE DATABASE `db`
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
USE DB;
② 建表
:
CREATE TABLE `user` (
`username` varchar(20) NOT NULL PRIMARY KEY,
`password` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
开发过程:
UserServlet.java
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import toolbean.DB;
import valuebean.User;
@javax.servlet.annotation.WebServlet(name="userServlet",urlPatterns="/UserServlet")
public class UserServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//response.setContentType( "text/html;charset=utf-8");
// PrintWriter out = response.getWriter();
// out = response.getWriter();
//out.println("<script>alert('用户不存在,删除失败!');history.back();</script>");
String action=request.getParameter("action");
String name=request.getParameter("username");
String pass=request.getParameter("password");
if(name==null)
name="";
if(pass==null)
pass="";
DB db=new DB();
if("add".equals(action)){
try {
db.Add(new User(name,pass));
response.sendRedirect("UserServlet?action=search");//显示所有记录
} catch (SQLException e) {
e.printStackTrace();
}
}else if("delete".equals(action)){
try {
db.Delete(name);
response.sendRedirect("UserServlet?action=search");
} catch (SQLException e) {
e.printStackTrace();
}
}else if("edit".equals(action)){
User user=new User(name,pass);
try {
db.Update(user);
response.sendRedirect("UserServlet?action=search");
} catch (SQLException e) {
e.printStackTrace();
}
}else if("search".equals(action)){
String sql="select * from user where username like '%"+name+"%' and password like '%"+pass+"%'";
System.out.println(sql);
try {
List<User> users=db.search(sql);
request.getSession().setAttribute("userlist", users);
response.sendRedirect("crud.jsp");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
DB.java
package toolbean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import valuebean.User;
public class DB {
private static Connection conn=null;
private static Statement stmt=null;
//构造函数中完成对数据库进行初始化
public DB() {
//创建连接
conn=this.getConnection();
try {
//创建Statement
stmt=conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
//将对象user添加到表中
public void Add(User user) throws SQLException
{
String sql="INSERT INTO user(username,password) VALUES('"
+user.getUsername()+"','"+user.getPassword()+"')";
stmt.executeUpdate(sql);
}
//根据用户名(关键字)从数据库删除相应的记录
public void Delete(String username) throws SQLException
{
String sql="Delete From user Where username='"+username+"'";
stmt.executeUpdate(sql);
}
//修改数据库中的user
public void Update(User user) throws SQLException
{
String sql="UPDATE user set password='"
+user.getPassword()+"' where username='"+user.getUsername()+"'";
stmt.executeUpdate(sql);
}
//根据用户名称(关键字)从数据库中查找记录,并将找到的记录写入对象user中返回
public User searchByUsername(String username) throws SQLException
{
User user=new User();
String sql="select * from user where username='"+username+"'";
ResultSet rs=stmt.executeQuery(sql);
while(rs.next())
{
String name=rs.getString("username");
if(name.equals(username))
{
//将从数据库查找得到的记录存入对象user中
user.setUsername(name);
user.setPassword(rs.getString("password"));
if(rs!=null)
rs.close();
return user;
}
}
return null;//查找不到返回null
}
public List<User> search(String sql) throws SQLException
{
List<User> users=new ArrayList<User>();
ResultSet rs=stmt.executeQuery(sql);
while(rs.next())
{
String name=rs.getString("username");
String pass=rs.getString("password");
User user=new User(name,pass);
users.add(user);
}
return users;
}
//根据用户判断该记录是否存在
public boolean exists(String username)
{
boolean rtn=false;
try {
if(searchByUsername(username)!=null)
rtn=true;
} catch (SQLException e) {
e.printStackTrace();
}
return rtn;
}
///建立与数据的连接
private Connection getConnection()
{
String driverClass="com.mysql.jdbc.Driver";
String url=//与创建数据库的代码结合,保证访问数据库时不出现乱码
"jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=UTF-8";
String username = "root";
String password = "12345";
try {
Class.forName(driverClass);// 加载数据库驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(url, username, password);//建立连接
} catch (SQLException e) {
e.printStackTrace();
System.out.print("数据库连接失败!");
}
return conn;
}
//关闭与访问数据库有关连接
protected void finalize()
{
try {
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
User.java
package valuebean;
public class User {
public User( String username,String password) {
this.password = password;
this.username = username;
}
public User( ) { }
private String username;
private String password;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}