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>

批次刪除和更新我們下期會將