jsp+servlet手机小项目（增删改查 一）

jsp+servlet手机管理（查询一）

一 设计数据库

手机（id ,name 手机名称，bid 手机品牌编号）

品牌（id 手机品牌编号 dname 品牌名称）

我们的t_tel 和 t_brand 是两表关联的

这是我的项目列表

二：连接数数据（准备)

1.导入jar包

2.创建 jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/lsk
username=root     //数据库
password=root     //密码

3.创建 JDBCUtil

public class JDBCUtil {
   //定义属性信息
   private static String propertiesURL;
   private static String driver;
   private static String url;
   private static String username;
   private static String password;
   
   //加载资源
   static {
      //属性资源路径
      propertiesURL = "jdbc.properties";
      Properties prop = new Properties();
      try {
         prop.load(JDBCUtil.class.getClassLoader().getResourceAsStream(propertiesURL));
         driver = prop.getProperty("driver");
         url = prop.getProperty("url");
         username = prop.getProperty("username");
         password = prop.getProperty("password");
      } catch (IOException e) {
         e.printStackTrace();
      }
      
      //加载驱动只要加载一次即可
      try {
         Class.forName(driver);
      } catch (ClassNotFoundException e) {
         e.printStackTrace();
      }
      
   }
   

   public static Connection getConnection(){
      Connection conn = null;
      
      try {
         conn = DriverManager.getConnection(url, username, password);
      } catch (SQLException e) {
         e.printStackTrace();
      }
      
      return conn;
   }
   
   /**
    * 关闭资源
    * @param rs
    * @param ps
    * @param conn
    */
   public static void close(ResultSet rs, PreparedStatement ps, Connection conn){
      if(rs != null){
         try {
            rs.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
      if(ps != null){
         try {
            ps.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
      if(conn != null){
         try {
            conn.close();
         } catch (SQLException e) {
            e.printStackTrace();
         }
      }
   }

}

4.创建pojo包 来我们的javaBean

Tel 手机类

public class Tel {
    private Integer id;
    private String name;
    private Integer bid;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

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

    public Integer getBid() {
        return bid;
    }

    public void setBid(Integer bid) {
        this.bid = bid;
    }

    @Override
    public String toString() {
        return "Tel{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", bid=" + bid +
                '}';
    }
}

Tbrand 品牌类

public class Tbrand {
    private Integer id;
    private String dname;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    @Override
    public String toString() {
        return "Tbrand{" +
                "id=" + id +
                ", dname='" + dname + '\'' +
                '}';
    }
}

三: 实现我们的列表功能

把数据库里的数据显示出来 因为我们是两表查询 而手机表里面没有品牌的品牌名称 我们创建一个TelVo类来继承Tel

public class TelVo extends Tel {
    private String dname;

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }
}

​ 创建我们的dao包 创建接口TelDao:

 List<TelVo> findAll( );

​ dao包的Impl包下创建TelDao的接口实现类TelDaoImpl

首先 你要implements接口TelDao   
public List<TelVo> findAll() {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        String sql="select t.id,t.name,t.bid,d.dname from t_tel t,t_brand d where t.bid=d.id";
        List<TelVo> telVos=null;
        TelVo telVo =null;
        try {
             telVos=new ArrayList<>();
             connection = JDBCUtil.getConnection();
             preparedStatement = connection.prepareStatement(sql);
             resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
               telVo=new TelVo();
                //resultSet.getXXX 通过列名来获得查询结果集中的某一列的值
              telVo.setId(resultSet.getInt("id"));
              telVo.setName(resultSet.getString("name"));
              telVo.setBid(resultSet.getInt("bid"));
              telVo.setDname(resultSet.getString("dname"));
                //存入到 List集合
              telVos.add(telVo);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            // 释放资源
            JDBCUtil.close(resultSet,preparedStatement,connection);
        }
        return telVos;
    }

创建service包

TelService接口

List<TelVo> findAll( );

接口实现类 TelServiceImpl

service调用dao层 然后实现TelService接口

private TelDao telDao=new TelDaoImpl();
public List<TelVo> findAll() {
        return telDao.findAll();
    }

最后来实现我们的servlet

<servlet>
    <servlet-name>Telservlet</servlet-name>
    <servlet-class>servlet.Telservlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>Telservlet</servlet-name>
    <url-pattern>/tel</url-pattern>
</servlet-mapping>

public class Telservlet extends HttpServlet {
    private TelService telService=new TelServiceImpl();
    private TbrandService tbrandService=new TbrandServiceImpl();
    private LoginService loginService=new LoginServiceImpl();
     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String method = request.getParameter("method");
        if ("findAll".equals(method)){
            findAll(request,response);
        }
    }
     private void findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //调用telService
        List<TelVo> telVos = telService.findAll();
        //存入作用域
        request.setAttribute("telVos",telVos);
         //跳转
        request.getRequestDispatcher("/t_list.jsp").forward(request,response);
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doPost(request,response);
    }
}
    

t_list.jsp 页面

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>首页</title>
</head>
<body>
<table style="width: 90%" border="1">
    <tr>
        <td>序列</td>
        <td>id</td>
        <td>手机名称</td>
        <td>品牌</td>
    </tr>
    <c:forEach items="${requestScope.telVos}" var="vo" varStatus="vs">
    <tr>
        <td>${vs.count}</td>
        <td>${vo.id}</td>
        <td>${vo.name}</td>
        <td>${vo.dname}</td>
    </tr>
    </c:forEach>
</table>
</body>
</html>

批量删除和更新我们下期会将