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;
}
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>
批量删除和更新我们下期会将
