多对多+复杂sql查询

最新推荐文章于 2024-08-20 04:56:33 发布

weixin_46981685

最新推荐文章于 2024-08-20 04:56:33 发布

阅读量299

点赞数

文章标签：列表 mysql

本文链接：https://blog.csdn.net/weixin_46981685/article/details/106094898

版权

房地产项目

实体类

package com.bawei.entity;

public class Condition {
	private Integer pageNum;
	private String oname;
	private String address;
	public Integer getPageNum() {
		return pageNum;
	}
	public void setPageNum(Integer pageNum) {
		this.pageNum = pageNum;
	}
	public String getOname() {
		return oname;
	}
	public void setOname(String oname) {
		this.oname = oname;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public Condition(Integer pageNum, String oname, String address) {
		super();
		this.pageNum = pageNum;
		this.oname = oname;
		this.address = address;
	}
	public Condition() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "Condition [pageNum=" + pageNum + ", oname=" + oname + ", address=" + address + "]";
	}
	
	
	
}
---------------------------------------------------------------

package com.bawei.entity;

import java.util.Arrays;
import java.util.List;

import javax.validation.constraints.Size;

import org.springframework.web.multipart.MultipartFile;

public class House {
	private Integer hid;
	private String address;
	private String usee;
	private Double area;
	private String pic;
	private MultipartFile myFile;
	
	
	private List<HouseOwner> owners;
	@Size(min=1,message="房主至少选择一位")
	private Integer[] oids;
	public Integer getHid() {
		return hid;
	}
	public void setHid(Integer hid) {
		this.hid = hid;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getUsee() {
		return usee;
	}
	public void setUsee(String usee) {
		this.usee = usee;
	}
	public Double getArea() {
		return area;
	}
	public void setArea(Double area) {
		this.area = area;
	}
	public String getPic() {
		return pic;
	}
	public void setPic(String pic) {
		this.pic = pic;
	}
	public MultipartFile getMyFile() {
		return myFile;
	}
	public void setMyFile(MultipartFile myFile) {
		this.myFile = myFile;
	}
	public List<HouseOwner> getOwners() {
		return owners;
	}
	public void setOwners(List<HouseOwner> owners) {
		this.owners = owners;
	}
	public Integer[] getOids() {
		return oids;
	}
	public void setOids(Integer[] oids) {
		this.oids = oids;
	}
	public House(Integer hid, String address, String usee, Double area, String pic, MultipartFile myFile,
			List<HouseOwner> owners, Integer[] oids) {
		super();
		this.hid = hid;
		this.address = address;
		this.usee = usee;
		this.area = area;
		this.pic = pic;
		this.myFile = myFile;
		this.owners = owners;
		this.oids = oids;
	}
	public House() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "House [hid=" + hid + ", address=" + address + ", usee=" + usee + ", area=" + area + ", pic=" + pic
				+ ", myFile=" + myFile + ", owners=" + owners + ", oids=" + Arrays.toString(oids) + "]";
	}
	
	

}

----------------------------------------------------------*
package com.bawei.entity;

import java.util.List;

public class HouseOwner {
	private Integer oid;
	private String oname;
	private String idCard;
	private String sex;
	public Integer getOid() {
		return oid;
	}
	public void setOid(Integer oid) {
		this.oid = oid;
	}
	public String getOname() {
		return oname;
	}
	public void setOname(String oname) {
		this.oname = oname;
	}
	public String getIdCard() {
		return idCard;
	}
	public void setIdCard(String idCard) {
		this.idCard = idCard;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public HouseOwner(Integer oid, String oname, String idCard, String sex) {
		super();
		this.oid = oid;
		this.oname = oname;
		this.idCard = idCard;
		this.sex = sex;
	}
	public HouseOwner() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "HouseOwner [oid=" + oid + ", oname=" + oname + ", idCard=" + idCard + ", sex=" + sex + "]";
	}
	
	
}

-------------------------------------------------------------------

package com.bawei.entity;

public class SysUser {
	private int id;
	private String username;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
	public SysUser() {
		super();
		// TODO Auto-generated constructor stub
	}
	public SysUser(int id, String username, String password) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
	}
	@Override
	public String toString() {
		return "SysUser [id=" + id + ", username=" + username + ", password=" + password + "]";
	}

}

service接口

package com.bawei.service;

import java.util.List;

import com.bawei.entity.Condition;
import com.bawei.entity.House;
import com.bawei.entity.HouseOwner;
import com.bawei.entity.SysUser;

public interface HouseService {

	boolean only(String username);

	void regist(SysUser u);

	boolean login(SysUser u);

	List<House> list(Condition con);

	List<HouseOwner> toAdd();

	void add(House house);

	House toUpdate(Integer hid);

	void update(House house);

}

Dao接口

package com.bawei.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.bawei.entity.Condition;
import com.bawei.entity.House;
import com.bawei.entity.HouseOwner;
import com.bawei.entity.SysUser;

public interface HouseDao {

	SysUser only(String usernamename);

	void regist(SysUser u);

	SysUser login(SysUser u);

	List<House> list(Condition con);
	List<HouseOwner> findHid(Integer hid);

	List<HouseOwner> toAdd();

	void addHouse(House house);

	void addHouseOwner(@Param("hid")Integer hid, @Param("oids")Integer[] oids);

	House toUpdate(Integer hid);

	void update(House house);

	void delete(Integer hid);
	
	
}

Mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bawei.dao.HouseDao">
	
	<select id="only" resultType="SysUser">
		select * from sys_user where username=#{username}
	</select>
	
	<insert id="regist">
		insert into sys_user values(null,#{username},#{password})
	</insert>
	
	<select id="login" resultType="SysUser">
		select * from sys_user where username=#{username} and password=#{password}
	</select>
	<select id="list" resultMap="HouseMap">
		
			SELECT house.* from house join h_o on house.hid=h_o.hid
 			<where>
 				<if test="oname!=null and oname!=''">
						and oid=(SELECT oid from house_owner where oname like concat('%',#{oname},'%'))
 				</if>
 				
 				<if test="address!=null and address!=''">
						and address like concat('%',#{address},'%')
 				</if>
 				
 			</where>
 			group by house.hid
	</select>
	<resultMap type="House" id="HouseMap">
		<id property="hid" column="hid"/>
		<result property="address" column="address"/>
		<result property="usee" column="usee"/>
		<result property="area" column="area"/>
		<result property="pic" column="pic"/>
		
		<collection property="owners" ofType="HouseOwner"
			select="findByHid"
			column="hid"
		></collection>
	</resultMap>	
	<select id="findByHid" resultType="HouseOwner">
		select * from house_owner join h_o on house_owner.oid = h_o.oid where h_o.hid = #{hid}
	</select>
	<select id="toAdd" resultType="HouseOwner">
		select * from house_owner
	</select>
	
	<insert id="addHouse" useGeneratedKeys="true" keyProperty="hid">
		insert into house values(null,#{address},#{usee},#{area},#{pic})
	</insert>
	
	<insert id="addHouseOwner">
		insert into h_o values
			<foreach collection="oids" item="oid" separator=",">
				(#{hid},#{oid})
			</foreach>
	</insert>
	<select id="toUpdate" resultMap="HouseMap">
		
			SELECT house.* from house join h_o on house.hid=h_o.hid where  house.hid = #{hid}
 			
 			group by house.hid
	</select>
	
	<update id="update">
		update house set address=#{address},usee=#{usee},area=#{area},pic=#{pic} where hid = #{hid}
	</update>
	<delete id="delete">
		delete from h_o where hid=#{hid}
	</delete>
	
</mapper>

service实现类

package com.bawei.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bawei.dao.HouseDao;
import com.bawei.entity.Condition;
import com.bawei.entity.House;
import com.bawei.entity.HouseOwner;
import com.bawei.entity.SysUser;
import com.bawei.service.HouseService;
@Transactional
@Service
public class HouseServiceImpl implements HouseService {

	@Autowired
	private HouseDao dao;

	@Override
	public boolean only(String usernamename) {
			
		SysUser u =dao.only(usernamename);
		
		
		
		return u==null;
	}

	@Override
	public void regist(SysUser u) {
		dao.regist(u);
	}

	@Override
	public boolean login(SysUser u) {
		SysUser user= dao.login(u);
		return user!=null;
	}

	@Override
	public List<House> list(Condition con) {

		
		
		return dao.list(con);
	}

	@Override
	public List<HouseOwner> toAdd() {
		// TODO Auto-generated method stub
		return dao.toAdd();
	}

	@Override
	public void add(House house) {
		 dao.addHouse(house);
		dao.addHouseOwner(house.getHid(),house.getOids());
		
	}

	@Override
	public House toUpdate(Integer hid) {
		
		return dao.toUpdate(hid); 
	}

	@Override
	public void update(House house) {
		
		dao.update(house);
		
		dao.delete(house.getHid());
		
		dao.addHouseOwner(house.getHid(),house.getOids());
		
	}
	
	
}

前台列表页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
   <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt"  prefix="fmt"%>
   <%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<LINK href="resources/css/css.css" type="text/css" rel="stylesheet">
<script src="resources/js/jquery-1.8.2.min.js"></script>
  <script src="My97DatePicker/WdatePicker.js"></script>
  <script type="text/javascript">
  	function fenye(pageNum) {
		$("[name=pageNum]").val(pageNum);
		$("form").submit();
  	}
  	function toAdd() {
		location="toAdd.do";
	}
  	function upd(hid) {
  		location="update.jsp?hid="+hid;
	}
  </script>

</head>
<body>
	<form action="list.do" method="post">
		<input type="hidden" name="pageNum">
		房主:<input type="text" name="oname" value=${con.oname }>
		地址:<input type="text" name="address" value=${con.address }>
		<button>查看</button>
		<input type="button" onclick="toAdd()" value="添加">
	</form>                                               
	<table>
		<tr>
			<td>编2号</td>
			<td>房3主</td>
			<td>地址</td>
			<td>用途</td>
			<td>面积</td>
			<td>房产证</td>
			<td>操作</td>
		</tr>
		
		<c:forEach items="${page.list }" var="h" varStatus="count">
			<tr>
				<td>${count.count+page.startRow-1 }</td>
				<td>
					<c:forEach items="${h.owners}" var="o" varStatus="num">
						${o.oname }
						<c:if test="${h.owners.size()>num.count}">、</c:if>
					</c:forEach>
				</td>
				<td>${h.address }</td>
				<td>${h.usee }</td>
				<td>${h.area }</td>
				<td>
					<img style="height: 50px; width: 50px;" alt="失败" src="load/${h.pic }">
					
				
				</td>
				<td><button onclick="upd(${h.hid})">修改</button></td>
			</tr>
		
		</c:forEach>
		<tr>
			<td colspan="100">
				<button onclick="fenye(1)">首页</button>
				<button onclick="fenye(${page.prePage==0?1:page.prePage})">上一页</button>
				<button onclick="fenye(${page.nextPage==0?page.pages:page.nextPage})">下一页</button>
				<button onclick="fenye(${page.pages})">尾页</button>
				第${page.pageNum }/${page.pages }页,共${page.total }条
			</td>
		
		</tr>
		
	</table>

</body>
</html>

前台添加页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
   <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt"  prefix="fmt"%>
   <%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<LINK href="resources/css/css.css" type="text/css" rel="stylesheet">
<script src="resources/js/jquery-1.8.2.min.js"></script>
  <script src="My97DatePicker/WdatePicker.js"></script>

</head>
<body>
	<form:form action="add.do" method="post" modelAttribute="house" enctype="multipart/form-data">
	
		<table>
			<tr>
				<td>地址:</td>
				<td>
					<form:input path="address"/>
				</td>
			</tr>
			
			<tr>
				<td>用途:</td>
				<td>
					<form:input path="usee"/>
				</td>
			</tr>	
			<tr>
				<td>面积:</td>
				<td>
					<form:input path="area"/>
				</td>
			</tr>	
			<tr>
				<td>房主:</td>
				<td>
					<form:checkboxes items="${list }" path="oids" itemValue="oid" itemLabel="oname"/>
					<form:errors path="oids" cssStyle="color:red"></form:errors>
				</td>
			</tr>	
			
			<tr>
				<td>房产证:</td>
				<td>
					<input type="file" name="myFile">
				</td>
			</tr>	
			
			<tr>
				<td></td>
				<td>
					<form:button>提交</form:button>
					
				</td>
			</tr>
		</table>
	
</form:form>
</body>
</html>

前台登录页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
   <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt"  prefix="fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<LINK href="resources/css/css.css" type="text/css" rel="stylesheet">
<script src="resources/js/jquery-1.8.2.min.js"></script>
  <script src="My97DatePicker/WdatePicker.js"></script>
<script type="text/javascript">
	function regist() {
		location="regist.jsp";
	}

</script>
</head>
<body>
${msg }
	<form action="login.do" method="post">
		<table>
			<tr>
				<td>用户名:</td>
				<td>
					<input type="text" name="username">
				</td>
			</tr>
						<tr>
				<td>密码:</td>
				<td>
					<input type="text" name="password">
				</td>
			</tr>			
						<tr>
				<td></td>
				<td>
					<input type="submit" value="登录">
					<input type="button" value="注册" onclick="regist()">
					
				</td>
			</tr>
		</table>
	
	</form>

</body>
</html>

前台注册页面

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
   <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt"  prefix="fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<LINK href="resources/css/css.css" type="text/css" rel="stylesheet">
<script src="resources/js/jquery-1.8.2.min.js"></script>
<script src="resources/js/jquery.validate.js"></script>
  <script src="My97DatePicker/WdatePicker.js"></script>
  <style type="text/css">
  	.error{
  		color:red
  	}
  </style>
<script type="text/javascript">
	$(function() {
		$("form").validate({
			rules:{
				username:{
					remote:{
						url:"only.do",
						dataType:"json",
						type:"post",
						data:{username:function(){
							
								return $("[name='username']").val();
							}
						
						}
					}
				}				
			},messages:{
				username:{
					
					remote:"用户名已经存在!"
				}
				
			}
			
		})
		
	})

</script>
</head>
<body>
	<form action="regist.do" method="post">
		<table>
			<tr>
				<td>用户名:</td>
				<td>
					<input type="text" name="username">
				</td>
			</tr>
						<tr>
				<td>密码:</td>
				<td>
					<input type="text" name="password">
				</td>
			</tr>			
						<tr>
				<td></td>
				<td>
					<input type="submit" value="注册">
					<input type="reset"  value="重置" />
				</td>
			</tr>
		</table>
	
	</form>

</body>
</html>
``
### 前台修改+回显页面

```java
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
   <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt"  prefix="fmt"%>
   <%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<LINK href="resources/css/css.css" type="text/css" rel="stylesheet">
<script src="resources/js/jquery-1.8.2.min.js"></script>
  <script src="My97DatePicker/WdatePicker.js"></script>
	<script type="text/javascript">
	$(function (){
		var hid="${param.hid}";
		$.post("toUpdate.do",{hid:hid},function(obj){
			
			$("[name=address]").val(obj.address);
			$("[name=usee]").val(obj.usee);
			$("[name=area]").val(obj.area);
			
			 $.post("owners.do",function(arr){
				for ( var i in arr) {
					var flag=false;
					for ( var j in obj.owners) {
						if(obj.owners[j].oid==arr[i].oid){
							flag=true;
						}
					}
					if(flag){
						$("#td").append("<input  checked type='checkbox' name='oids' value='"+arr[i].oid+"'/>"+arr[i].oname)
					}else{
						$("#td").append("<input type='checkbox' name='oids' value='"+arr[i].oid+"'/>"+arr[i].oname)
					}
					
					
				}
				
			},"json") 
			
		},"json")
	})	
	
	</script>
</head>
<body>
	<form action="update.do" method="post" enctype="multipart/form-data">
	
		<table>
			<tr>
				<td>地址:</td>
				<td>
					<input type="hidden" name="hid" value="${param.hid }">
					<input type="text" name="address">
				</td>
			</tr>
			
			<tr>
				<td>用途:</td>
				<td>
					<input type="text" name="usee">
				</td>
			</tr>	
			<tr>
				<td>面积:</td>
				<td>
					<input type="text" name="area">
				</td>
			</tr>	
			<tr>
				<td>房主:</td>
				<td id="td">
					
				</td>
			</tr>	
			
			<tr>
				<td>房产证:</td>
				<td>
					<input type="file" name="myFile">
				</td>
			</tr>	
			
			<tr>
				<td></td>
				<td>
					<input type="submit" value="提交">
					
				</td>
			</tr>
		</table>
	
</form>
</body>
</html>
``