通过Map接口传递多个参数
在usermapper.xml中加入标签
<select id="seletctUserLike" resultType="tradition_user_package.MyUser" parameterType="map">
select * from user where uname like concat('%',#{uname},'%') and usex=#{usex}
</select>
在userdao中加入函数
public List<MyUser>seletctUserLike(Map<String,Object> mp);
在usercontroller中加入
Map<String,Object>map=new HashMap<String,Object>();
map.put("uname", "陈");
map.put("usex", "男");
List<MyUser>ls=userdao.seletctUserLike(map);
for(MyUser you:ls) {
System.out.println(you);
}
System.out.println("================");
通过JavaBean传递多个参数
类
package tradition_user_package;
public class SelectLikeParam {
private String uname;
private String usex;
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getUsex() {
return usex;
}
public void setUsex(String usex) {
this.usex = usex;
}
}
userDao类修改
public List<MyUser>seletctUserLike(SelectLikeParam sp);
UserController修改
SelectLikeParam sp=new SelectLikeParam();
sp.setUname("陈恒");
sp.setUsex("男");
List<MyUser>ls=userdao.seletctUserLike(sp);
for(MyUser you:ls) {
System.out.println(you);
}
System.out.println("================");
Insert元素
keyProperty
keyColumn
useGeneratedKeys
主键回填(自动递增)
<insert id="addUser" parameterType="tradition_user_package.MyUser"
keyProperty="uid" useGeneratedKeys="true"
>
insert into user(uid,uname,usex) values(#{uid},#{uname},#{usex});
</insert>
自定义主键
实际工程中使用的数据库不支持主键自动递增,或者取消了主键自动递增的规则,可以使用MyBatis的<selectKey>元素来自定义生成主键。
具体配置
<insert id="insertLikeUser" parameterType="tradition_user_package.MyUser">
<!-- 先使用selectKey元素定义主键,然后再定义SQL语句 -->
<selectKey keyProperty="uid" resultType="Integer" order="BEFORE">
<!-- BEFORE 说明先执行selectKey语句再执行Insert语句-->
select if(max(uid) is null,1,max(uid)+1)as newUid from user
</selectKey>
insert into user(uid,uname,usex) values(#{uid},#{uname},#{usex})
</insert>
update 元素与delete元素比较简单属性与select元素 insert元素差不多
<update id="createTable" >
create table if not exists User(uid int,uname varchar(50),usex varchar(50));
</update>
<delete id="deleteUser" parameterType="Integer">
delete from user where uid=#{uid}
</delete>
sql元素的作用在于可以定义SQL语句的一部分,以方便后面的SQL语句引用它。在MyBatis中只需要使用< sql >元素一次便能在其他元素中引用它。
下面是配置代码
<sql id="comColumns">uid,uname,usex</sql>
<select id="selectManyUser" resultType="tradition_user_package.MyUser">
select <include refid="comColumns"/>from user;
</select>
<resultMap>元素
resultMap表示结果映射集,表示映射规则、级联更新以及定义规则转化器。
子元素结构如下:
<resultMap type="" id="">
<constructor><!-- 类在实例化时用来注入结果到构造方法 -->
<idArg/><!-- ID参数,结果为ID -->
<arg/><!-- 注入到构造方法的一个普通结果 -->
</constructor>
<id/><!-- 用于表示那个键是主键 -->
<result/><!-- 注入到字段或JavaBean属性的普通结果 -->
<association property=""/><!-- 用于一对一关联 -->
<collection property=""/><!-- 用于一对多、多对多关联 -->
<discriminator javaType=""><!-- 使用结果值来决定使用那个结果映射 -->
<case value=""/><!-- 基于某些值的结果映射 -->
</discriminator>
</resultMap>
使用Map存储结果集
select语句可以用Map存储结果
<select id="selectAllUserMap" resultType="map">
select * from user
</select>
增加接口方法
public List<Map<String,Object>>selectAllUserMap();
UserController类中调用接口方法
System.out.println("================");
List<Map<String,Object>>lmp=userdao.selectAllUserMap();
for(Map<String,Object>map:lmp) {
System.out.println(map);
}
使用POJO存储结果集
可以使用自动映射,例如resultType属性
复杂映射或级联时,使用select元素的resultMap属性配置映射集合
UserMapper.xml
<resultMap type="tradition_user_package.MapUser" id="myResult">
<!-- property 是 MapUser类中的属性 -->
<!-- column是查询结果的列名,可以来自不同的类 -->
<id property="m_uid" column="uid"/>
<result property="m_uname" column="uname"/>
<result property="m_usex" column="usex"/>
</resultMap>
<select id="selectResultMap" resultMap="myResult">
select * from user
</select>
UserDao接口
public List<MapUser>selectResultMap();
UserController
List<MapUser>lmus=userdao.selectResultMap();
for(MapUser mu:lmus) {
System.out.println(mu);
}
级联查询
一对一级联查询
association元素属性
property:指定映射到实体类的对象属性
column:指定表中的字段(即查询返回的列名)
javaType:指定映射到实体对象属性的类型
select:指定引入嵌套查询的子SQL语句,该属性用于关联语句中的嵌套查询。
两个表
Person表
create table person(id int auto_increment primary key,code varchar(20));
Idcard表
create table idcard(id int primary key,name varchar(20),age int,
idcard_id int ,foreign key(idcard_id) references person(id));
创建持久化类
Person类
package pojo;
public class Person {
private Integer id;
private String name;
private Integer age;
private Integer sex;
private Idcard card;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Idcard getCard() {
return card;
}
public void setCard(Idcard card) {
this.card = card;
}
@Override
public String toString() {
return "Person [id=" + id + ", name=" + name + ", age=" + age + ", sex=" + sex + ", card=" + card + "]";
}
}
Idcard类
package pojo;
public class Idcard {
private Integer id;
private String code;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
@Override
public String toString() {
return "Idcard [id=" + id + ", code=" + code + "]";
}
}
创建映射文件
mybatis-config.xml
<?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>
<settings>
<!-- 开启延时加载开关 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 将积极加载设置为按需加载-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<mappers>
<mapper resource="tradition_user_package/UserMapper.xml"/>
<mapper resource="pojo/IdcardMapper.xml"/>
<mapper resource="pojo/PersonMapper.xml"/>
</mappers>
</configuration>
包下
IdcardMapper.xml
<?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="pojo.CardDao">
<select id="selectCardById" parameterType="Integer" resultType="pojo.Idcard">
select * from Idcard where id=#{id}
</select>
</mapper>
PersonMapper.xml
<?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="pojo.PersonDao">
<resultMap type="pojo.Person" id="cardAndPerson1">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<!-- 一对一级联查询 -->
<association property="card" column="idcard_id" javaType="pojo.Idcard"
select="pojo.CardDao.selectCardById"/>
</resultMap>
<select id="selectPersonById1" parameterType="Integer" resultMap="cardAndPerson1">
select * from person where id=#{id}
</select>
<!-- 一对一根据id查询个人信息,级联查询的第二种方法 -->
<resultMap type="pojo.Person" id="cardAndPerson2">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<association property="card" javaType="pojo.Idcard">
<id property="id" column="id"/>
<result property="code" column="code"/>
</association>
</resultMap>
<select id="selectPersonById2" parameterType="Integer" resultMap="cardAndPerson2">
select p.*,ic.code
from person p,idcard ic
where p.idcard_id=ic.id and p.id=#{id}
</select>
<!-- 使用POJO存储结果 -->
<select id="selectPersonById3" parameterType="Integer" resultType="pojo.SelectPersonById">
select p.*,ic.code
from person p,idcard ic
where p.idcard_id=ic.id and p.id=#{id}
</select>
</mapper>
创建POJO类
package pojo;
public class SelectPersonById {
private Integer id;
private String name;
private Integer age;
private String code;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
@Override
public String toString() {
return "SelectPersonById [id=" + id + ", name=" + name + ", age=" + age + ", code=" + code + "]";
}
public void setAge(Integer age) {
this.age = age;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}
创建数据操作接口
IdcardDao类
package pojo;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Repository("idCardDao")
@Mapper
public interface IdcardDao {
public Idcard selectCardById(Integer i);
}
PerssonDao类
package pojo;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Repository("PersonDao")
@Mapper
public interface PersonDao {
public Person selectPersonById1(Integer id);
public Person selectPersonById2(Integer id);
public SelectPersonById selectPersonById3(Integer id);
}
Spring配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
">
<context:component-scan base-package="pojo"/>
<context:component-scan base-package="tradition_user_package"/>
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/PD?characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
<property name="maxIdle" value="10"/>
<property name="initialSize" value="5"/>
</bean>
<bean id="oto" class="pojo.OOController"></bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocation" value="classpath:mybatis-config.xml"/>
</bean>
<!-- Mapper代理开发,使用Spring自动扫描MyBatis接口并且装配
Mapper标注的接口-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="tradition_user_package"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- Mapper代理开发,使用Spring自动扫描MyBatis接口并且装配
Mapper标注的接口-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="pojo"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- 添加事务支持 -->
<tx:annotation-driven transaction-manager="txManager"/>
</beans>
调用接口方法及测试
测试类 OOController
package pojo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
@Repository("OOController")
public class OOController {
@Autowired
private PersonDao persondao;
public void test()
{
Person p1=persondao.selectPersonById1(1);
System.out.println(p1);
System.out.println("==================");
Person p2=persondao.selectPersonById1(1);
System.out.println(p2);
System.out.println("==================");
SelectPersonById p3=persondao.selectPersonById3(1);
System.out.println(p3);
System.out.println("==================");
}
}
TestOneToOne
package pojo;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class Tc {
public static void main(String[] args) {
// TODO Auto-generated method stub
ApplicationContext app=new ClassPathXmlApplicationContext("Ab.xml");
OOController ooc=(OOController) app.getBean("oto");
ooc.test();
}
}
一对多级联查询
创建数据表
orders表
create table orders(id int auto_increment primary key,
ordersn varchar(10) default null,user_id int default null,
foreign key(user_id) references user(uid));
user表
create table user(uid int primary key,uname varchar(20),usex int);
创建持久类
MyUser类添加
private List<Orders>orderList;
Orders类
package tradition_user_package;
public class Orders {
private Integer id;
private String ordersn;
private Integer user_id;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Orders(Integer id, String ordersn, Integer user_id) {
super();
this.id = id;
this.ordersn = ordersn;
this.user_id = user_id;
}
public String getOrdersn() {
return ordersn;
}
public void setOrdersn(String ordersn) {
this.ordersn = ordersn;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
}
mybatis配置文件
OrdersMapper.xml
<?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="tradition_user_package.OrdersDao">
<select id="selectOrdersById" parameterType="Integer" resultType="tradition_user_package.Orders">
select * from where user_id=#{id}
</select>
</mapper>
UserMapper.xml
<?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="tradition_user_package.UserDao">
<!-- 一对多 根据 uid 查询用户及其关联的订单信息;级联查询的第一种方法(嵌套查询) -->
<resultMap type="tradition_user_package.MyUser" id="UserAndOrders1">
<id property="uid" column="uid"/>
<result property="uname" column="uname"/>
<result property="usex" column="usex"/>
<!-- 一对多级联查询,ofType表示集合中的元素类型,将uid传递给selectOrdersById -->
<collection property="ordersList" ofType="tradition_user_package.Orders" column="uid"
select="tradition_user_package.OrdersDao.selectOrdersById" javaType="java.util.List">
</collection>
</resultMap>
<select id="selectUserOrdersById1" parameterType="Integer" resultMap="UserAndOrders1">
select * from user where uid=#{id}
</select>
<resultMap type="tradition_user_package.MyUser" id="UserAndOrders2">
<id property="uid" column="uid"/>
<result property="uname" column="uname"/>
<result property="usex" column="usex"/>
<!-- 一对多级联查询,ofType表示集合中的元素类型,将uid传递给selectOrdersById -->
<collection property="ordersList" ofType="tradition_user_package.Orders" javaType="java.util.List">
<id property="id" column="id"/>
<result property="ordersn" column="ordersn"/>
</collection>
</resultMap>
<select id="selectUserOrdersById2" parameterType="Integer" resultMap="UserAndOrders2">
select u.*,o.id,o.ordersn from user u,orders o where u.uid=o.user_id and u.uid=#{id}
</select>
<!-- 一对多 根据uid查询用户及其关联的订单的信息,连接查询 (使用POJO存储结果)-->
<select id="selectUserOrdersById3" parameterType="Integer" resultType="tradition_user_package.SelectOrdersById">
select u.*,o.id,o.ordersn from user u,orders o where u.uid=o.user_id and u.uid=#{id}
</select>
</mapper>
POJO类
SelectOrdersById
package tradition_user_package;
public class SelectOrdersById {
private Integer uid;
private String uname;
private String user;
private Integer id;
private String ordersn;
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getUser() {
return user;
}
public void setUser(String user) {
this.user = user;
}
public Integer getId() {
return id;
}
@Override
public String toString() {
return "SelectOrdersById [uid=" + uid + ", uname=" + uname + ", user=" + user + ", id=" + id + ", ordersn="
+ ordersn + "]";
}
public void setId(Integer id) {
this.id = id;
}
public String getOrdersn() {
return ordersn;
}
public void setOrdersn(String ordersn) {
this.ordersn = ordersn;
}
}
创建数据库操作接口
OrdersDao
package tradition_user_package;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Repository("OrdersDao")
@Mapper
public interface OrdersDao {
public List<Orders> selectOrdersById(Integer uid);
}
UserDao
package tradition_user_package;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Repository("userDao")
@Mapper
public interface UserDao {
public MyUser selectUserOrdersById1(Integer uid);
public MyUser selectUserOrdersById2(Integer uid);
public List<SelectOrdersById> selectUserOrdersById3(Integer uid);
public MyUser selectUserById(Integer uid);
public List<MyUser> selectAllUser();
public List<MyUser>seletctUserLike(SelectLikeParam sp);
public List<MyUser>selectLikeUser(MyUser user);
public List<MapUser>selectResultMap();
public List<Map<String,Object>>selectAllUserMap();
public int addUser(MyUser user);
public int updateUser(MyUser user);
public int deleteUser(Integer uid);
public void createTable();
}
调用接口方法及测试
package tradition_user_package;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
@Controller("OneToMany")
public class OneToMany {
@Autowired
private UserDao ud;
public void test()
{
MyUser mu=ud.selectUserOrdersById1(1);
System.out.println(mu);
System.out.println("====================================");
mu=ud.selectUserOrdersById2(1);
System.out.println(mu);
System.out.println("====================================");
List<SelectOrdersById> mus=ud.selectUserOrdersById3(1);
for(SelectOrdersById sob:mus)
System.out.println(sob);
System.out.println("====================================");
}
}
package tradition_user_package;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestOneToMany {
public static void main(String[] args) {
// TODO Auto-generated method stub
ApplicationContext app=new ClassPathXmlApplicationContext("Ab.xml");
OneToMany uc=(OneToMany)app.getBean("otm");
uc.test();
}
}
多对多映射
创建product表和orders_detail表
create table orders_detail(id int not null auto_increment primary key, order_id int default null,product_id default null);
create table product (id int not null auto_increment primary key, name varchar(50) default null,price double default null);
创建持久化类
Product
package mtm;
import java.util.List;
public class Product {
private Integer id;
private String name;
private Double price;
private List<Orders>orders;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public List<Orders> getOrders() {
return orders;
}
public void setOrders(List<Orders> orders) {
this.orders = orders;
}
@Override
public String toString() {
return "Product [id=" + id + ", name=" + name + ", price=" + price + ", orders=" + orders + "]";
}
}
orders类
package mtm;
import java.util.List;
public class Orders {
private Integer id;
private String ordersn;
private List<Product>products;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrdersn() {
return ordersn;
}
public void setOrdersn(String ordersn) {
this.ordersn = ordersn;
}
public List<Product> getProducts() {
return products;
}
@Override
public String toString() {
return "Orders [id=" + id + ", ordersn=" + ordersn + ", products=" + products + "]";
}
public void setProducts(List<Product> products) {
this.products = products;
}
}
创建接口
package mtm;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Repository("od")
@Mapper
public interface OrdersDao {
public List<Orders>selectallOrdersAndProducts();
}
mybatis映射文件配置
ordersMapper.xml
<?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="mtm.OrdersDao">
<resultMap type="mtm.Orders" id="allOrdersAndProducts">
<id property="id" column="id"></id>
<result property="ordersn" column="ordersn"/>
<collection property="products" ofType="mtm.Product">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
</collection>
</resultMap>
<select id="selectallOrdersAndProducts" resultMap="allOrdersAndProducts" >
select o.*,p.id as pid ,p.name ,p.price
from orders o,orders_detail od,product p
where od.order_id=o.id
and od.product_id=p.id
</select>
</mapper>
控制类
package mtm;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
@Controller("mmc")
public class MoreToMoreController {
@Autowired
private OrdersDao od;
public void test() {
List<Orders> os=od.selectallOrdersAndProducts();
for(Orders oo:os) {
System.out.println(oo);
}
}
}
测试类
package mtm;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import pojo.OOController;
public class Main {
public static void main(String[] args) {
// TODO Auto-generated method stub
ApplicationContext app=new ClassPathXmlApplicationContext("Ab.xml");
MoreToMoreController mtm=(MoreToMoreController) app.getBean("mtm");
mtm.test();
}
}