省三级联动信息查询

数据持久层

创建Dynamic Web Project，项目名称为exam，创建父包（一级），并在父包下统一创建entity、dao、sql、util、test等子包（二级）。运用持久层框架Mybatis实现【会员地址】的【查询功能】并完成Junit测试，输出会员地址的信息包括：地址编号、会员编号、详细的收货地址（如XX省+XX市+XX区+XXXX）。
这便是一个要求下面的话我们直接切入主题
首先我们先列举一下自己的编码思路
(1)新建项目搭建环境
(2)编写实体类
(3)定义SQL语句(单表） + （关联表）
(4)构建SQLSession
(5)编写DAO接口
(6)获取DAO对象并测试
这是一个非常大的作业所以呢就开始搭建环境吧
在这里的话我们的包是这样的，
此包的话只是借鉴，所以存在问题的时候请各位指出。在这里呢我们使用了mybatis的技术。
然后我们用到的包是 dao entity sql test util
主要的包就是这五个
我们先看看sql的相关文件 SqlMapConfig.xml文件。

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" 
"http://ibatis.apache.org/dtd/ibatis-3-config.dtd">

<configuration>
   <!--  alt+/ 快捷提示符 -->
	<!-- 解析db.properties文件 -->
	<properties resource="db.properties"></properties>
	
	 <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
              <!-- 对应db.properties文件的key -->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${pwd}"/>
            </dataSource>
        </environment>
    </environments>
    
    <!-- 映射文件配置 -->
     <mappers>
    <mapper resource="tzzy2002320220/sql/MemberAddressSqlMap.xml"/>
    </mappers>
    
</configuration>

我们使用的是mysql的5.1.8的版本在这里的话配置了映射文件和解析db.properties文件

这是db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mobile_shop?useUnicode=true&characterEncoding=UTF-8
user=root
pwd=123456

在这个文件里面的话有我们mysql相关配置信息
下面的话就从实体类开始写吧
先看看我们的会员地址的结构图

entity MemberAddress

import java.sql.Timestamp;

public class MemberAddress {
	private Integer address_id;
	private Integer member_id;
	private String province;
	private String city;
	private String region;
	private String addr;
	private String mobile;
	private String receiver;
	private Timestamp creatime;
	private Timestamp modifytime;
	private String flag;
	public Integer getAddress_id() {
		return address_id;
	}
	public void setAddress_id(Integer address_id) {
		this.address_id = address_id;
	}
	public Integer getMember_id() {
		return member_id;
	}
	public void setMember_id(Integer member_id) {
		this.member_id = member_id;
	}
	public String getProvince() {
		return province;
	}
	public void setProvince(String province) {
		this.province = province;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getRegion() {
		return region;
	}
	public void setRegion(String region) {
		this.region = region;
	}
	public String getAddr() {
		return addr;
	}
	public void setAddr(String addr) {
		this.addr = addr;
	}
	public String getMobile() {
		return mobile;
	}
	public void setMobile(String mobile) {
		this.mobile = mobile;
	}
	public String getReceiver() {
		return receiver;
	}
	public void setReceiver(String receiver) {
		this.receiver = receiver;
	}
	public Timestamp getCreatime() {
		return creatime;
	}
	public void setCreatime(Timestamp creatime) {
		this.creatime = creatime;
	}
	public Timestamp getModifytime() {
		return modifytime;
	}
	public void setModifytime(Timestamp modifytime) {
		this.modifytime = modifytime;
	}
	public String getFlag() {
		return flag;
	}
	public void setFlag(String flag) {
		this.flag = flag;
	}
	@Override
	public String toString() {
		return "MemberAddress [address_id=" + address_id + ", member_id=" + member_id + ", province=" + province
				+ ", city=" + city + ", region=" + region + ", addr=" + addr + ", mobile=" + mobile + ", receiver="
				+ receiver + ", creatime=" + creatime + ", modifytime=" + modifytime + ", flag=" + flag + "]";
	}
}

这是我们实体类的一个写法声明各种类型使用get set方法 toString实现实体类的一个编写
dao接口的编写

dao MemberAddressDao

import java.util.List;

import tzzy2002320220.entity.MemberAddress;



public interface  MemberAddressDao {
	public List<MemberAddress>findAll() ;
	
}

SQL编写 MemberAddressSqlMap

sql要求如下
查询【会员地址表】数据如下图所示（注：需要对数据按照指定的字段进行升/降排序）。

通过【会员地址表】和【行政区域表】关联查询，得到如下数据

这里的话我们写了一个单表查询和一个多表查询语句升序相关代码只写了一小部分

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="tzzy2002320220.dao.MemberAddressDao">
	<select id="findAll"
		resultType="tzzy2002320220.entity.MemberAddress">
		select * from ms_member_address ORDER BY member_id DESC, province DESC,
		region DESC

	</select>
	<select id="findMemberAddress"
		resultType="tzzy2002320220.entity.MemberAddress">
		SELECT
		mmb.area_name AS province,
		mmb2.area_name city,

		mmb3.area_name region,

		mma.address_id,
		mma.member_id,
		mma.addr,
		mma.receiver,
		mma.mobile
		FROM
		ms_member_address mma
		JOIN ms_sys_area mmb ON mma.province =mmb.id
		JOIN ms_sys_area mmb2 ON mma.city=mmb2.id
		JOIN ms_sys_area mmb3 on mma.region= mmb3.id
		ORDER BY mma.province DESC

	</select>
</mapper>

sql语句写完之后呢就要开始写我们的测试类代码

testMemberAddress

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import tzzy2002320220.dao.MemberAddressDao;
import tzzy2002320220.entity.MemberAddress;
import tzzy2002320220.util.MybatisUtil;

public class TestMemberAddress {
	// 获取SqlSession
	SqlSession session = MybatisUtil.getSqlsession();
	// 获取memberaddressDao实例
	MemberAddressDao memberaddressDao = session.getMapper(MemberAddressDao.class);

	@Test
	public void testFindAll() {

		List<MemberAddress> memberaddress1 = memberaddressDao.findAll();
		for (MemberAddress memberaddress : memberaddress1) {
			System.out.println(memberaddress);
		}

	}

	@Test
	public void testFindMemberAddress() {

		List<MemberAddress> memberaddress2 = memberaddressDao.findMemberAddress();
		for (MemberAddress memberaddress : memberaddress2) {
			System.out.println(memberaddress);
		}
	}
}

util MybatisUtil

sqlSession方法

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;


public class MybatisUtil {
	/**
	 * 获取SqlSession方法
	 * 
	 * @return
	 */
	public static SqlSession getSqlsession() {
		// 获取SqlSessionFactoryBuilder
		SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
		// 加载SqlMapConfig.xml
		ClassLoader loader = MybatisUtil.class.getClassLoader();
		InputStream inStream = loader.getResourceAsStream("SqlMapConfig.xml");

		// 通过SqlSessionFactory获取SqlSession
		SqlSessionFactory factory = builder.build(inStream);
		SqlSession session = factory.openSession();
		return session;
	}

	public static void main(String[] args) {
		SqlSession session = MybatisUtil.getSqlsession();
		System.out.println(session);
		
		session.close();
	}

}

在这里呢说一下 包名没有按照专业的规范进行。