一、单表查询
1、Mapper.xml
2、Dao.java
3、测试
二、多表查询
1、pojo.java
2、Mapper.xml
3、Dao.java
4、测试
一、单表查询
1、Mapper.xml
1.定义一个map模型
<!--
1.property 键名
2.column 列名
3.jdbcType 列类型
-->
<resultMap type="java.util.HashMap" id="TbUserMap">
<result property="userid" column="userid" jdbcType="INTEGER"/>
<result property="username" column="username" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
</resultMap>
2.定义一个查询语句
<!--
1.resultMap 模型名
2.resultType 返回的类型
-->
<select id="queryByIdMap" resultMap="TbUserMap" resultType="java.util.HashMap">
select
userid, username, password
from test.tb_user
where userid = #{userid}
</select>
2、Dao.java
/**
* 通过ID查询单条数据
*
* @param userid 主键
* @return Map对象
*/
public Map<String,Object> queryByIdMap(Integer userid);
3、测试
public class Tests {
public static void main(String[] args) throws Exception {
/**
* 获得SqlSession对象,通过SqlSession操作CRUD
*
*/
SqlSession sqlSession;
//通过Resources类加载核心配置文件,得到文件的输入流
InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml");
//创建会话工厂,编译配置文件流,获得sqlsessionfactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//通过sqlSessionFactory得到sqlsession对象
sqlSession = sqlSessionFactory.openSession();
// 获取TbUserDao类对象
TbUserDao tbUserDaoMapper = sqlSession.getMapper(TbUserDao.class);
// 调用方法
Map<String, Object> maps = tbUserDaoMapper.queryByIdMap(1);
// 输出结果
System.out.println(maps.get("username"));
sqlSession.commit();
sqlSession.close();
}
}
二、多表查询
1、pojo.java
# 从表对象
private UsMeter usMeter;
public UsMeter getUsMeter() {
return usMeter;
}
public void setUsMeter(UsMeter usMeter) {
this.usMeter = usMeter;
}
2、Mapper.xml
1.定义一个map模型
<!--
1.property 键名
2.column 列名
3.jdbcType 列类型
-->
<resultMap type="java.util.HashMap" id="usUserMapper">
<id property="userNo" column="User_No" jdbcType="VARCHAR"/>
<result property="userName" column="User_Name" jdbcType="VARCHAR"/>
<result property="userAbc" column="User_ABC" jdbcType="VARCHAR"/>
<result property="userCreatedate" column="User_CreateDate" jdbcType="TIMESTAMP"/>
<result property="userAddress" column="User_Address" jdbcType="VARCHAR"/>
<result property="userDetailaddress" column="User_detailAddress" jdbcType="VARCHAR"/>
<result property="userPhone" column="User_Phone" jdbcType="VARCHAR"/>
<result property="userSmsphone" column="User_SMSPhone" jdbcType="VARCHAR"/>
<result property="areaId" column="Area_ID" jdbcType="INTEGER"/>
<result property="userType" column="User_Type" jdbcType="VARCHAR"/>
<result property="userPaytype" column="User_PayType" jdbcType="VARCHAR"/>
<result property="userBankname" column="User_BankName" jdbcType="VARCHAR"/>
<result property="userBanknum" column="User_BankNum" jdbcType="VARCHAR"/>
<result property="userFormula" column="User_Formula" jdbcType="VARCHAR"/>
<result property="userMoney" column="User_Money" jdbcType="NUMERIC"/>
<result property="mtId" column="Mt_ID" jdbcType="INTEGER"/>
<result property="userDocnum" column="User_DocNum" jdbcType="VARCHAR"/>
<result property="userPwd" column="User_Pwd" jdbcType="VARCHAR"/>
<result property="userContractnum" column="User_ContractNum" jdbcType="VARCHAR"/>
<result property="userContractdate" column="User_ContractDate" jdbcType="TIMESTAMP"/>
<result property="userVolumeid" column="User_VolumeID" jdbcType="OTHER"/>
<result property="userVolumeorderindex" column="User_VolumeOrderIndex" jdbcType="INTEGER"/>
<result property="userStauts" column="User_stauts" jdbcType="INTEGER"/>
<!--一对一关联映射:association-->
<association property="usMeter" javaType="java.util.HashMap">
<id property="meterNo" column="Meter_No" jdbcType="VARCHAR"/>
<result property="userNo" column="User_No" jdbcType="VARCHAR"/>
<result property="meterName" column="Meter_Name" jdbcType="VARCHAR"/>
<result property="mtId" column="Mt_ID" jdbcType="INTEGER"/>
<result property="meterMaxvalue" column="Meter_MaxValue" jdbcType="INTEGER"/>
<result property="meterStartvalue" column="Meter_StartValue" jdbcType="INTEGER"/>
<result property="meterSetupdate" column="Meter_SetupDate" jdbcType="TIMESTAMP"/>
<result property="meterFactory" column="Meter_Factory" jdbcType="VARCHAR"/>
<result property="meterDisabled" column="Meter_Disabled" jdbcType="INTEGER"/>
</association>
</resultMap>
2.定义一个查询语句
<!--
1.resultMap 模型名
2.resultType 返回的类型
-->
<!--多表查询-->
<select id="queryById" resultMap="usUserMapper">
select
*
from tapwater.us_user u,tapwater.us_meter m
where u.User_No = m.User_No and u.User_No = #{userNo}
</select>
3、Dao.java
/**
* 通过ID查询单条数据
*
* @param userNo 主键
* @return Map对象
*/
public Map<String,Object> queryById(String userNo);
4、测试
public class Tests {
public static void main(String[] args) throws Exception {
/**
* 获得SqlSession对象,通过SqlSession操作CRUD
*
*/
SqlSession sqlSession;
//通过Resources类加载核心配置文件,得到文件的输入流
InputStream inputStream = Resources.getResourceAsStream("mybatisConfig.xml");
//创建会话工厂,编译配置文件流,获得sqlsessionfactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//通过sqlSessionFactory得到sqlsession对象
sqlSession = sqlSessionFactory.openSession();
UsUserDao mapper = sqlSession.getMapper(UsUserDao.class);
Map<String, Object> map = mapper.queryById("01072609X0");
System.out.println(map.get("usMeter"));
sqlSession.commit();
sqlSession.close();
}
}