JDBC与JSP初识

主要内容：
-JDBC 三层架构进行组织后台代码
-JSP初识
- 在页面展示表中数据
- 在页面输入数据并处理数据

---

课堂参考代码：
1、后台代码：
（1）cn.sdut.dao. BaseDao.java

package cn.sdut.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BaseDao {
    // 1 定义数据库访问公共变量
    Connection con;
    PreparedStatement pst;
    ResultSet rs;
    // 2 定义数据库的连接方法
    public Connection getConn() {
        try {
            // 1 加载JDBC驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            // 2 得到数据库的连接
            String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8";
            con = DriverManager.getConnection(url, "root", "usbw");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return con;
    }
    // 3 关闭conn,pst,rs
    public void closeAll() {
        try {
            if (rs != null) {
                rs.close();
            }
            if(pst!=null)
            {
                pst.close();
            }
            if(con!=null)
            {
                con.close();
            }

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

（2）cn.sdut.dao.StudentDao.java

package cn.sdut.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cn.sdut.po.Student;

public class StudentDao  extends BaseDao{
        // 增
       public int add(Student stu) {
              int result = 0;
              try {
                      con=getConn();                 
                      String sql="insert into student(name,birthday,score) values(?,?,?)";
                      pst=con.prepareStatement(sql);
                      pst.setString(1,stu.getName());
                      pst.setString(2, stu.getBirthday());
                      pst.setFloat(3, stu.getScore());   
                      result=pst.executeUpdate();
              } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
              }
              finally
              {
                     closeAll();
              }
              return result;
       }

       // 删除
       public int del(int id) {
              int result = 0;
              try {
                     con=getConn();                   
                      String sql="delete from student where id=?";           
                      pst = con.prepareStatement(sql);
                      pst.setInt(1, id);

                     // 5 执行SQL语句
                      result=pst.executeUpdate();
              } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
              }
              finally
              {
                     closeAll();
              }
              return result;
       }

       // 修改
       public int update(Student stu) {
              int result = 0;
              try {
                     con=getConn();                   
                      String sql="update student set name=’"+stu.getName()+"’ where id="+stu.getId();               
                      pst=con.prepareStatement(sql);                
                      result=pst.executeUpdate(sql);
              } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
              }
              finally
              {
                     closeAll();
              }
              return result;
       }

       // 查询记录
       public List queryAll() {
              List list = new ArrayList();          
              try {
                     con=getConn();                   
                     String sql="select * from student";
                     pst=con.prepareStatement(sql);
                      rs=pst.executeQuery(sql);
                     while(rs.next())
                     {
                            Student stu=new Student();
                            stu.setId(rs.getInt(1));
                            stu.setName(rs.getString(2));
                            stu.setBirthday(rs.getString(3));
                            stu.setScore(rs.getFloat(4));
                            list.add(stu);
                     }                                       
              } catch (SQLException e) {
                     // TODO Auto-generated catch block
                     e.printStackTrace();
              }
              finally
              {
                     closeAll();
              }                         
              return list;
       }
}

（3）cn.sdut.po.Student.java

package cn.sdut.po;
public class Student {
    private int id;
    private String name;
    private String birthday;
    private float score;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    public float getScore() {
        return score;
    }

    public void setScore(float score) {
        this.score = score;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", birthday="
                + birthday + ", score=" + score + "]";
    }
}

2、前端页面
（1）WebRoot/index.jsp

<%@page import="cn.sdut.po.Student"%>
<%@page import="cn.sdut.dao.StudentDao"%>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
    String path = request.getContextPath();
    String basePath = request.getScheme() + "://"
            + request.getServerName() + ":" + request.getServerPort()
            + path + "/";
%>
<%
   StudentDao studentDao=new StudentDao();
   List<Student> stuList=studentDao.queryAll();

 %>

<!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>
    <table border="1">
        <tr align="center">
            <td width="50">序号</td>
            <td width="50">姓名</td>
            <td width="100">出生年月</td>
            <td  width="50">成绩</td>
        </tr>
        <%
            for(Student stu:stuList)
            {
               out.print("<tr>");
               out.print("<td>");
               out.print(stu.getId());
               out.print("</td>");
                out.print("<td>");
               out.print(stu.getName());
               out.print("</td>");
                out.print("<td>");
               out.print(stu.getBirthday());
               out.print("</td>");
                out.print("<td>");
               out.print(stu.getScore());
               out.print("</td>");
               out.print("</tr>");
            }
         %>
    </table><br><br>
    <a href="addStudent.jsp" >增加学生</a>
</body>
</html>

（2）WebRoot/addStudent.jsp

<%@ 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 ’addStudent.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>
    <form  action="doAdd.jsp" method="post">
        <table>
            <caption>增加学生</caption>
            <tr>
                <td width="97" align="center">姓&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 名:</td>
              <td width="175"><input type="text" name="name"/></td>
            </tr>
            <tr>
                <td align="center">出生年月:</td>
              <td><input type="text" name="birthday"/></td>
            </tr>
            <tr>
                <td align="center">成&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 绩:</td>
              <td><input type="text" name="score"/></td>
            </tr>
            <tr align="center"><td height="55" colspan="2">
               <input type="submit" value="确定"/>
                     &nbsp; &nbsp; &nbsp; <input type="reset" value="取消"/>
          </td>  </tr>
        </table>

    </form>
</body>
</html>

（3）WebRoot/doAdd.jsp

<%@page import="cn.sdut.dao.StudentDao"%>
<%@page import="cn.sdut.po.Student"%>
<%@ 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 ’doAdd.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>
  <%
     //1 接受客户端传来的数据并封装成Student类的对象

     request.setCharacterEncoding("utf-8");
     String name=request.getParameter("name");
     String birthday=request.getParameter("birthday");
     String score1=request.getParameter("score");
     float score=Float.parseFloat(score1);
     Student student=new Student();
     student.setName(name);
     student.setBirthday(birthday);
     student.setScore(score);

     //2 调用StudentDao的add方法,向数据库表中增 加记录
     StudentDao studentDao=new StudentDao();
     studentDao.add(student);

     //3 转向index.jsp,展示最新的表中的数据
     response.sendRedirect("index.jsp");
   %>
  <body>

  </body>
</html>