JDBC+Servlet

 

 

package com.dao;
/**
 * 数据库操作工具类
*/
public class BaseDao {
    //1.定义链接数据库的驱动:数据库的类型
    private static final String DRIVER="com.mysql.jdbc.Driver";
    //2.定义链接数据库的url地址:数据库的url
    private static final String URL="jdbc:mysql://localhost:3306/holly";
    //3.定义链接数据库用户名
    private static final String USER="root";
    //4.定义链接数据库密码
    private static final String PASSWORD="ok";
    //5.定义链接数据库的链接通道:路
    public Connection conn=null;
    //6.定义数据库操作的执行者
    public PreparedStatement pstm=null;
    //7.定义接受数据库操作结果集
    public ResultSet rs=null;
    //8.加载驱动
    static{
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("driver is error!!");
            e.printStackTrace();
        }
    }
    /**
     * 9.定义获取数据库链接的方法
     */
    public Connection getConn(){
        try {
            conn=DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            System.out.println("URL, USER, PASSWORD is error!");
            e.printStackTrace();
        }
        return conn;
    }
    /**
     * 10.释放资源
     */
    public void closeAll(ResultSet rs, PreparedStatement pstm,
            Connection conn) {
        try {
            if(rs!=null){ rs.close();}
            if(pstm!=null){ pstm.close();}
            if(conn!=null){ conn.close();}
        } catch (SQLException e) {e.printStackTrace();}
    }
    /**
     * 11.公共增删改的方法
     */
    public int executeUpdate(String sql,Object[] param){
        //获取数据库链接
        conn=this.getConn();               int num=0;
        try {
            //创建执行者
            pstm=conn.prepareStatement(sql);
            if(param!=null){
                for (int i = 0; i < param.length; i++) {pstm.setObject(i+1, param[i]); }
            }
            //执行操作
            num = pstm.executeUpdate();
        } catch (SQLException e) {e.printStackTrace();
        }finally{this.closeAll(rs, pstm, conn); }
        return num; 
    }
    /**
     * 12.公共查询的方法
     */
    public ResultSet executeQuery(String sql,Object[] param){
        //获取数据库链接
        conn=this.getConn();
    
        try {
            //创建执行者
            pstm=conn.prepareStatement(sql);
            if(param!=null){
                for (int i = 0; i < param.length; i++) {pstm.setObject(i+1, param[i]); }
            }
            //执行操作
            rs = pstm.executeQuery();
        } catch (SQLException e) {e.printStackTrace();}
        return rs; 
    }
}

 

package com.entity;
public class Student {
    private int sid;
    private String sname;
    private String pwd;
    private int age;
    private String sex;
    public Student() {}
    public Student(String sname, String pwd, int age, String sex) {
        this.sname = sname;      this.pwd = pwd;
        this.age = age;           this.sex = sex;
    }
    public Student(int sid, String sname, String pwd, int age, String sex) {
        this.sid = sid;        this.sname = sname;
        this.pwd = pwd;       this.age = age;
        this.sex = sex;
    }
    public int getSid() {return sid;}

    public void setSid(int sid) {this.sid = sid;}

    public String getSname() {return sname;}

    public void setSname(String sname) {this.sname = sname; }

    public String getPwd() {return pwd; }

    public void setPwd(String pwd) {this.pwd = pwd; }

    public int getAge() {return age; }

    public void setAge(int age) {this.age = age; }

    public String getSex() {return sex; }

    public void setSex(String sex) {this.sex = sex; }

    public String toString() {
        return "Student [age=" + age + ", pwd=" + pwd + ", sex=" + sex
                + ", sid=" + sid + ", sname=" + sname + "]";
    }
}

 

package com.dao.impl;
public class StudentDaoImpl extends BaseDao implements IStudentDao {
    /**
     * 1.根据用户名和密码查询
     */
    public Student findByNamePwd(String sname, String pwd) {
        Student stu=null;
        String sql="select * from student where sname=? and pwd=?";
        Object[] param={sname,pwd};
        this.rs=this.executeQuery(sql, param);
        try {
            if(rs.next()){
stu=new Student(rs.getInt("sid"), rs.getString("sname"), rs.getString("pwd"), rs.getInt("age"), rs.getString("sex"));
            }
        } catch (SQLException e) {e.printStackTrace();
}finally{this.closeAll(rs, pstm, conn); }
        return stu;
    }
    /**
     * 2.添加
     */
    public int addStudent(Student stu) {
        String sql="insert into student(sname,pwd,age,sex) values(?,?,?,?)";
        Object[] param={stu.getSname(),stu.getPwd(),stu.getAge(),stu.getSex()};
        return this.executeUpdate(sql, param);
    }
    /**
     * 3.查询所有
     */
    public List<Student> findAll() {
        List<Student> list=new ArrayList<Student>();
        String sql="select * from student";
        this.rs=this.executeQuery(sql, null);
        try {
while(rs.next()){Student stu=new Student(rs.getInt("sid"), rs.getString("sname"), rs.getString("pwd"), rs.getInt("age"), rs.getString("sex"));
              list.add(stu);
            }
        } catch (SQLException e) {e.printStackTrace();}finally{
            this.closeAll(rs, pstm, conn); }
        return list;
    }
}

 

package com.dao;

import java.util.List;

import com.entity.Student;

public interface IStudentDao {
    /**
     * 1.根据用户名和密码查询
     * @param sname
     * @param pwd
     * @return
     */
     Student findByNamePwd(String sname,String pwd);
     /**
      * 2.添加
      * @param stu
      * @return
      */
      int addStudent(Student stu);
      /**
       * 3.查询所有
       * @return
       */
      List<Student> findAll();
}

 

package com.service;

import java.util.List;

import com.entity.Student;

/**
 * 业务层
 * @author pc
 *
 */
public interface StudentService {

    /**
     * 1.登录
     * @param sname
     * @param pwd
     * @return
     */
    Student login(String sname,String pwd);

    /**
     * 2.注册
     * @param stu
     * @return
     */
    int register(Student stu);

    /**
     * 3.查询所有
     * @return
     */
    List<Student> query();

}

 

package com.service.impl;

import java.util.List;

import com.dao.IStudentDao;
import com.dao.impl.StudentDaoImpl;
import com.entity.Student;
import com.service.StudentService;

public class StudentServiceImpl implements StudentService {
    IStudentDao dao=new StudentDaoImpl();
    /**
     * 1.登录
     */
    public Student login(String sname, String pwd) {
        return dao.findByNamePwd(sname, pwd);
    }

   /**
    * 2.注册
    */
    public int register(Student stu) {
        return dao.addStudent(stu);
    }

    /**
     * 3.查询所有
     */
    public List<Student> query() {
        return dao.findAll();
    }

}

 

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="com.service.StudentService"%>
<%@page import="com.service.impl.StudentServiceImpl"%>
<%@page import="com.entity.Student"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%
  //1.乱码处理
  //请求通道
  request.setCharacterEncoding("UTF-8");
  //响应通道
  response.setCharacterEncoding("UTF-8");
  //响应页面
  response.setContentType("text/html;charset=UTF-8");
  
  //2.接受请求参数
 String username= request.getParameter("username");
 String password= request.getParameter("password");
 
  //3.业务处理
  StudentService service=new StudentServiceImpl();
  if(username!=null && password!=null){
      Student stu=service.login(username,password);
      if(stu!=null){
        System.out.print("登录成功!");
        List<Student> list=service.query();
        request.setAttribute("list",list);
        request.getRequestDispatcher("index.jsp").forward(request,response);
      }else{
        System.out.print("登录失败!");
        
      }
      
  
  }else{
     System.out.print("参数为空!");
  }
     //4.页面跳转
     //request对象跳转携带数据
    
%>

 

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%
  //1.乱码处理
  //请求通道
  request.setCharacterEncoding("UTF-8");
  //响应动态
  response.setCharacterEncoding("UTF-8");
  //响应页面
  response.setContentType("text/html;charset=UTF-8");
  
  //2.接受请求参数
 String username= request.getParameter("username");
 String password= request.getParameter("password");
 String[] ah=request.getParameterValues("ah"); 
 
  //3.业务处理
  if(username!=null && password !=null&& username.length()>0&& password.length()>0&& ah!=null){
    out.print("你的注册信息如下:<br/>");
    out.print("用户名:"+username+"<br/>");
    out.print("密码:"+password+"<br/>");
    out.print("爱好:<br/>");
    for(int i=0; i<ah.length; i++ ){
      out.print(ah[i]+",");
    }
  }else{
     out.print("注册失败!");
     
  }
  //4.页面跳转
    
%>

 

package com.test;

import java.util.List;

import com.entity.Student;
import com.service.StudentService;
import com.service.impl.StudentServiceImpl;

public class Test {

    /**
     * @param args
     */
    public static void main(String[] args) {
        StudentService service=new StudentServiceImpl();
        /*Student stu=service.login("小胖", "123");
        if(stu!=null){
            System.out.println("login success!!");
        }else{
            System.out.println("login fail!!");
        }*/


        /*int num=service.register(new Student("holly", "123", 18, "女"));
        if(num>0){
            System.out.println("register success~!");
        }else{
            System.out.println("register fail~!");
            
        }*/


        List<Student> list=service.query();
        for (Student student : list) {
            System.out.println(student);
        }
        
        
    }

}

 

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@page import="com.entity.Student"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    <title>My JSP 'index.jsp' starting page</title>
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <!--<link rel="stylesheet" type="text/css" href="styles.css">-->
  </head>
  <body> 欢迎<%=request.getParameter("username") %>登录首页!!
    <table>
      <tr><td>序号</td>
        <td>姓名</td>
        <td>密码</td>
        <td>年龄</td>
        <td>性别</td>
        <td>操作</td>
      </tr>
      <% List<Student> list=(List<Student>)request.getAttribute("list"); for(Student stu:list){ %>
      <tr><td><%=stu.getSid() %></td>
        <td><%=stu.getSname() %></td>
        <td><%=stu.getPwd() %></td>
        <td><%=stu.getAge() %></td>
        <td><%=stu.getSex() %></td>
        <td>
          <a href="deleteservice.jsp">删除</a>&nbsp;&nbsp;
            <a href="findbyidservice.jsp">修改</a>
          </td>
      </tr>
      <% }%>
    </table>
  </body>
</html>

 

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://"
            + request.getServerName() + ":" + request.getServerPort()
            + path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>
        <base href="<%=basePath%>">
        <title>My JSP 'login.jsp' starting page</title>
        <meta http-equiv="pragma" content="no-cache">
        <meta http-equiv="cache-control" content="no-cache">
        <meta http-equiv="expires" content="0">
        <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
        <meta http-equiv="description" content="This is my page">
        <!--<link rel="stylesheet" type="text/css" href="styles.css">-->
    </head>
    <body>
        <fieldset style="width: 400px;">
            <legend>登录</legend>
            <form action="loginservice.jsp" method="post">
                <table>
                    <tr>
                        <td>用户名: </td>
                        <td><input type="text" name="username" /></td>
                    </tr>
                    <tr>
                        <td>密码: </td>
                        <td><input type="password" name="password" /></td>
                    </tr>
                    <tr>
                        <td><input type="submit" value="登录" /></td>
                        <td><input type="reset" value="重置" /></td>
                    </tr>
                </table>
            </form>
        </fieldset>
    </body>
</html>

 

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://"
            + request.getServerName() + ":" + request.getServerPort()
            + path + "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
    <head>
        <base href="<%=basePath%>">
        <title>My JSP 'login.jsp' starting page</title>
        <meta http-equiv="pragma" content="no-cache">
        <meta http-equiv="cache-control" content="no-cache">
        <meta http-equiv="expires" content="0">
        <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
        <meta http-equiv="description" content="This is my page">
        <!--<link rel="stylesheet" type="text/css" href="styles.css">-->
    </head>
    <body>
        <fieldset style="width: 400px;">
            <legend>注册</legend>
            <form action="registerservice.jsp" method="post">
                <table>
                    <tr><td>用户名: </td>
                        <td><input type="text" name="username" /></td>
                    </tr>
                    <tr><td>密码: </td>
                        <td><input type="password" name="password" /></td>
                    </tr>
                    <tr><td>爱好: </td>
                        <td><input type="checkbox" name="ah" value="吃饭"/>吃饭
                            <input type="checkbox" name="ah" value="睡觉"/>睡觉
                            <input type="checkbox" name="ah" value="打豆豆"/>打豆豆
                        </td>
                    </tr>
                    <tr><td><input type="submit" value="注册" /></td>
                        <td><input type="reset" value="重置" /></td>
                    </tr>
                </table>
            </form>
        </fieldset>
    </body>
</html>

 

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'error.jsp' starting page</title>
    
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">    
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="This is my page">
    <!--
    <link rel="stylesheet" type="text/css" href="styles.css">
    -->

  </head>
  
  <body>
     欢迎<%=request.getParameter("username") %>登录首页!!
  </body>
</html>