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>

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