俩表联查(1.0)
第二个是等值连接 脏数据也能查出 但不常用,企业用第一个
这句话保持怀疑,版本正确如下:
如果a 中有脏数据,那么left join 能把脏数据也查出,
而where不可以,join也不可以。
实战(导包全省略)
(1)创建Web工程,数据库
导入jar包(javax.servlet\jstl\standard\servlet-api\mysql),js包
t_role
t_user
(2)cn.kgc.util/BaseDao、UserDao、UserDaoImpl
BaseDao 省略
UserDao
public interface UserDao {List<User> findAll();//查询所有}
UserDaoImpl
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public List<User> findAll() {
List<User> list = new ArrayList<>();
rs = super.executeQuery("select a.uid as uid,a.upwd as upwd,b.rname as rName from t_user a left join t_role b on a.rid = b.rid ", null);
try {
while (rs.next()){
User u = new User();
u.setuId(rs.getInt("uid"));
u.setuPwd(rs.getString("upwd"));
u.setrName(rs.getString("rName"));
list.add(u);
}
} catch (SQLException e) {e.printStackTrace();}
finally {super.closeAll();}
return list;
}
}
(3)cn.kgc.entity/Role、User
Role
public class Role {
private Integer rId;
private String rName;
public Role() { }
public Role(Integer rId, String rName) {
this.rId = rId;
this.rName = rName;
}
//省略getter和setter方法
}
User
public class User {
private Integer uId;
private String uName;
private String uPwd;
private Integer rId;
private String rName;
public User() { }
public User(Integer uId, String uName, String uPwd, Integer rId) {
this.uId = uId;
this.uName = uName;
this.uPwd = uPwd;
this.rId = rId;
}
//省略getter和setter方法
}
(4)cn.kgc.service/UserService、UserServiceImpl
省略
(5)cn.kgc.servlet/IndexServlet
public class IndexServlet extends HttpServlet {
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text.html;charset=utf-8");
String action = request.getParameter("action");
UserServiceImpl userService = new UserServiceImpl();
if (action.equals("index")){
List<User> list = userService.finaAll();
request.setAttribute("list",list);
request.getRequestDispatcher("list.jsp").forward(request,response);
}
}
}
(6)配置xml和Tomcat
(7)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>
<script type="text/javascript" src="js/jquery-1.8.3.min.js"></script>
<script type="text/javascript">
$(function () { $("tr:even").css("background","red");} );
</script>
</head>
<body>
<form>
<table border="1px black" cellspacing="0px" cellpadding="0px" style="margin: 0 auto">
<tr>
<td>用户编码</td>
<td>用户密码</td>
<td>用户角色名</td>
</tr>
<c:forEach var="user" items="${list}">
<tr>
<td>${user.uId}</td>
<td>${user.uPwd}</td>
<td>${user.rName}</td>
</tr>
</c:forEach>
</table>
</form>
</body>
</html>
结果:
http://localhost:8080/IndexServlet?action=index
三表联查(2.0)省略
用户表写r_id 不纯净了 用连接表 即码表
这个比第一个更好
五表联查(3.0)
实战(导包全省略)
(1)创建Web工程,数据库
导入jar包(javax.servlet\jstl\standard\servlet-api\mysql),js包
t_role
t_user
t_permission
t_r_p
t_u_r
(2)cn.kgc.util/BaseDao、UserDao、UserDaoImpl
BaseDao 省略
UserDao
public interface UserDao {List<User> findAll();//查询所有}
UserDaoImpl
public class UserDaoImpl extends BaseDao implements UserDao {
@Override
public List<User> finaAll() {
List<User> list = new ArrayList<>();
rs = super.executeQuery
("SELECT a.id as id,a.`name` as name,c.`name` as roleName,e.`name` as permissionName FROM t_user a " +
" LEFT JOIN t_u_r b ON a.id=b.u_id " +
" LEFT JOIN t_role c ON b.r_id=c.id " +
" LEFT JOIN t_r_p d ON c.id = d.r_id " +
" LEFT JOIN t_permission e ON d.p_id = e.id",
null);
try {
while (rs.next()){
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setRoleName(rs.getString("roleName"));
user.setPermissionName(rs.getString("permissionName"));
list.add(user);
}
} catch (SQLException e) {e.printStackTrace();}
finally {super.closeAll();}
return list;
}
}
(3)cn.kgc.entity/Permission、Role、RP、UR、User
Permission
public class Permission {
private Integer id;
private String name;
//省略getter和setter方法
}
Role
public class Role {
private Integer id;
private String name;
public Role() {}
public Role(Integer id, String name) {
this.id = id;
this.name = name;
}
//省略getter和setter方法
}
RP
public class RP {
private Integer id;
private Integer rId;
private Integer pId;
//省略getter和setter方法
}
UR
public class UR {
private Integer id;
private Integer uId;
private Integer rId;
//省略getter和setter方法
}
User
public class User {
private Integer id;
private String name;
private String pwd;
private String roleName;
private String permissionName;
public User() {}
public User(Integer id, String name, String roleName) {
this.id = id;
this.name = name;
this.roleName = roleName;
}
//省略getter和setter方法
}
(4)cn.kgc.service/UserService、UserServiceImpl
省略
(5)cn.kgc.servlet/IndexServlet
public class IndexServlet extends HttpServlet {
protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String action = request.getParameter("action");
if (action.equals("index")){
List<User> list =new UserServiceImpl().finaAll();
request.setAttribute("list",list);
request.getRequestDispatcher("index.jsp").forward(request,response);
}
}
}
(6)配置xml和Tomcat
(7)index.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$</title>
<script src="js/jquery-1.8.3.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {$("tr:even").css("background","yellow")})
</script>
</head>
<body>
<form>
<table border="1px solid " style="border-color:palevioletred;border-collapse: collapse">
<tr>
<td>用户编码</td>
<td>用户名称</td>
<td>用户角色</td>
<td>权限名称</td>
</tr>
<c:forEach var="user" items="${list}">
<tr>
<td>${user.id}</td>
<td>${user.name}</td>
<td>${user.roleName}</td>
<td>${user.permissionName}</td>
</tr>
</c:forEach>
</table>
</form>
</body>
</html>
结果:
http://localhost:8080/IndexServlet?action=index
----2021.11.06&11.07