数据库访问及JSTL

一、实验目的

1 、了解 JDBC 的概念及工作原理；

2 、熟悉 JDBC API 的主要接口或类；

3 、掌握使用 JDBC 进行数据库访问（增删改查）的步骤和方法；

二、实验内容和要求

采用 JSP+Servlet+JavaBean 架构，设计并实现如下内容：

1 、添加用户页面 (AddUser.jsp) ；

2 、删除用户页面 (Delete.jsp) ；

3 、查找和修改用户页面 (SearchAndModify.jsp) 。

 

 

 

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;
    }
    

}