一.创建表
--user_info表
create table user_info(
id int(2) primary key,
user_name varchar(12) unique,
password varchar(15) not null,
real_name varchar(8) not null,
age int(3)
);
--address表
create table address(
id int(2) primary key,
user_id int(2) not null,
real_name varchar(8),
mobile char(11),
address varchar(150)
);
insert into user_info values(1,'浅唱灬幸福','8912@321','王晓明',12);
insert into address values(1,1,'王小明','15516472282','山西太原');
insert into address values(2,1,'王鑫','18404905139','山西大同');
insert into address values(3,1,'任建','15333021730','山西晋城');
insert into user_info values(2,'ぅ浮生若梦〤','56701wz','王楠',36);
insert into address values(4,2,'王楠','15010303314','北京海淀');
insert into address values(5,2,'赵婕','18435224278','山西长治');
insert into user_info values(3,'街角の风铃','27w4921','李晓飞',9);
insert into address values(6,6,'刘倩','13159775555','吉林长春');
二.一对多查询
1.导入jar包
2.创建UserInfo类
package com.jd.vo;
import java.util.List;
public class UserInfo {
// public UserInfo(Properties properties) {
// System.out.println(properties.getProperty("driver"));
// System.out.println(properties.getProperty("url"));
// System.out.println(properties.getProperty("username"));
// System.out.println(properties.getProperty("password"));
// }
private int id;
private String UserName;
private String password;
private String realName;
private int age;
private List<Address> list;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return UserName;
}
public void setUserName(String userName) {
UserName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public List<Address> getList() {
return list;
}
public void setList(List<Address> list) {
this.list = list;
}
}
3.在mapper文件中配置MySQL语句
<?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.jd.userinfo.dao.IUserInfoDao">
<!-- <select id="getById" resultType="java.lang.String">数据筛选,返回字段 不同方法标签的id属性不同
select name from user_info where id = #{id}
</select>
<select id="selectById" resultType="com.jd.vo.UserInfo">数据筛选,返回对象
select id,name,mobile,address from user_info where id = ${id}
</select>
<select id="select" resultType="com.jd.vo.UserInfo">数据筛选,返回集合
select id,name,mobile,address from user_info where like #{name}
</select>
<select id="delete">删除数据
delete from user_info where id = #{id}
</select> -->
<resultMap type="com.jd.vo.UserInfo" id="ui">
<id column="ui_id" property="id"/>
<result column="user_name" property="userName"/>
<result column="password" property="password"/>
<result column="ui_real_name" property="realName"/>
<result column="age" property="age"/>
<collection property="list" ofType="com.jd.vo.Address">
<id column="address_id" property="id"/>
<result column="addr_real_name" property="realName"/>
<result column="mobile" property="mobile"/>
<result column="address" property="address"/>
</collection>
</resultMap>
<select id="select" resultMap="ui">
select
ui.id ui_id, user_name, password, ui.real_name ui_real_name, age,
addr.id address_id, addr.real_name addr_real_name, mobile, address
from user_info ui
left join address addr on ui.id=addr.user_id
where ui.id = #{id}
</select>
</mapper>
4.注册mapper文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql//127.0.0.1:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--将含有sql语句的mapper文件注册 -->
<mappers>
<mapper resource="user_info.xml"/>
</mappers>
</configuration>
5.创建Address类
package com.jd.vo;
public class Address {
private int id;
private int userId;
private String mobile;
private String realName;
private String address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
6.创建Test类
package com.jd.test;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.jd.userinfo.dao.IUserInfoDao;
import com.jd.vo.UserInfo;
public class Test {
public static void main(String[] args) {
try {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserInfoDao userInfoDao = sqlSession.getMapper(IUserInfoDao.class);
System.out.println(userInfoDao.select(1).getList().size());
sqlSession.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
三.一对一查询
1.改写UserInfo类
public class UserInfo {
private int id;
private String UserName;
private String password;
private String realName;
private int age;
private Address address;
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return UserName;
}
public void setUserName(String userName) {
UserName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getRealName() {
return realName;
}
public void setRealName(String realName) {
this.realName = realName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
2.改写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.jd.userInfo.dao.IUserInfoDao">
<resultMap type="com.jd.vo.UserInfo" id="ui">
<id column="ui_id" property="id"/>
<result column="user_name" property="userName"/>
<result column="password" property="password"/>
<result column="ui_real_name" property="realName"/>
<result column="age" property="age"/>
<association property="address" javaType="com.jd.vo.Address"><!--一对一时使用association标签,javatype属性用于设置对象类型-->
<id column="address_id" property="id"/>
<result column="addr_real_name" property="realName"/>
<result column="mobile" property="mobile"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="get" resultMap="ui">
select
ui.id ui_id,user_name,password,ui.real_name ui_real_name,age,
addr.id address_id,addr.real_name addr_real_name,mobile,address
from user_info ui
LEFT JOIN address addr on addr.user_id=ui.id
where ui.id=#{id}
</select>
</mapper>
3.修改Test类
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.jd.userInfo.dao.IUserInfoDao;
import com.jd.vo.UserInfo;
public class Test {
public static void main(String[] args) {
try {
InputStream inputStream = Resources.getResourceAsStream("myBatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserInfoDao userInfoDao = sqlSession.getMapper(IUserInfoDao.class);
UserInfo ui = new UserInfo();
ui.setId(1);
UserInfo userInfo = userInfoDao.get(ui);
System.out.println(userInfo.getAddress().getAddress());
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}