导包
init.sql
CREATE DATABASE mybatis
USE mybatis
CREATE TABLE USER(id VARCHAR(36) PRIMARY KEY ,
NAME VARCHAR(64),address VARCHAR(128));
insert into user values ('0001','zhangsan','aa');
insert into user values ('0002','lisi' ,'aa');
create table person(
personid varchar(36) primary key,
personName varchar(64),
personAddress varchar(128),
personTel varchar(11)
)
create table orders(
orderId varchar(36) primary key ,
orderNumber varchar(20),
orderPrice int,
pid varchar(36)
)
新建配置文件
<?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>
<typeAliases>
<typeAlias type="cn.itcast.mybatis.domain.User" alias="User"/>
<typeAlias type="cn.itcast.mybatis.domain.Person" alias="Person"/>
<typeAlias type="cn.itcast.mybatis.domain.Order" alias="Order"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/mybatis" />
<property name="username" value="root" />
<!--如果密码为空 就不要写这行 <property name="password" value=" " /> -->
</dataSource>
</environment>
</environments>
<mappers>
<!-- 映射文件的位置 -->
<mapper resource="cn/itcast/mybatis/domain/User.xml" />
<mapper resource="cn/itcast/mybatis/domain/Person.xml" />
<mapper resource="cn/itcast/mybatis/domain/Order.xml" />
</mappers>
</configuration>
public class User {
private String id;
private String userName;
private String address;
public String getId() {
System.out.println("正在通过getId方法获取id的值:" + id);
return id;
}
public void setId(String id) {
System.out.println("正在通过setId方法注入id的值:" + id);
this.id = id;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
System.out.println("正在通过setAddress方法注入address的值:" + address);
this.address = address;
}
@Override
public String toString() {
return "{id:" + id + ",name:" + userName + ",address:" + address + "}";
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
System.out.println("正在通过setUserName方法注入userName的值:" + userName);
this.userName = userName;
}
}
映射文件 User.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="cn.itcast.mybatis.domain.User">
<!-- namespace唯一即可 -->
<resultMap type="User" id="userMap">
<id column="id" property="id"/>
<result column="name" property="userName"/>
<result column="address" property="address"/>
</resultMap>
<sql id="mysql">
id,name,address
</sql>
<!-- 根据id查询User -->
<!-- id:当前sql语句的唯一标示 parameterType:参数类型(string必须小写 因为是别名 或者这样写 java.lang.String) resultType:结果类型(返回值类型)-->
<select id="selectUserById" parameterType="string" resultMap="userMap">
select <include refid="mysql"/> from user where id = #{userid}<!-- 这个#{userid}可以随便写 -->
</select>
<!-- 查询所有User -->
<select id="selectAllUsers" resultType="User">
select id,name as userName ,address from user<!-- 利用sql的别名解决名词不一致问题 -->
</select>
<!-- 根据id查询User,返回值为:Map -->
<select id="selectUserByIdForMap" parameterType="string" resultType="hashmap">
select id,name,address from user where id = #{userid}
</select>
<!-- 动态sql -->
<select id="selectUserByCondition" parameterType="User" resultType="User">
select id,name as userName ,address from user where 1=1
<if test="id != null">
and id = #{id}
</if>
<!-- userName指的是实体的属性 -->
<if test="userName != null">
and name = #{userName}
</if>
<if test="address != null">
and address = #{address}
</if>
</select>
<!-- 动态sql -->
<select id="selectUserByCondition2" parameterType="User" resultType="User">
select id,name as userName ,address from user
<where>
<if test="id != null">
id = #{id}
</if>
<!-- userName指的是实体的属性 -->
<if test="userName != null">
and name = #{userName}
</if>
<if test="address != null">
and address = #{address}
</if>
</where>
</select>
<!-- 插入User -->
<insert id="insertUser" parameterType="User">
insert into user (id,name,address) values (#{id},#{userName},#{address});<!-- 这个#{id}不可以随便写 要和持久化类保持一致 -->
</insert>
<!-- 插入User 参数为hashmap-->
<insert id="insertUserForMap" parameterType="hashmap">
insert into user (id,name,address) values (#{id},#{name},#{address});<!-- 要和u.put("name", "ddd");一样 -->
</insert>
<!-- 根据id删除User -->
<delete id="deleteUserById" parameterType="string">
delete from user where id = #{id}
</delete>
<!-- 根据id更新User -->
<update id="updateUserById" parameterType="User">
update user set name = #{userName},address = #{address} where id = #{id}
</update>
<!-- 根据id更新User 参数为map-->
<update id="updateUserByIdForMap" parameterType="hashmap">
update user set name = #{userName},address = #{address} where id = #{id}
</update>
</mapper>
public class Person {
private String id;
private String name;
private String address;
private String tel;
List<Order> orders;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
}
Person.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="cn.itcast.mybatis.domain.Person">
<resultMap type="Person" id="personMap">
<id column="personId" property="id"/>
<result column="personName" property="name"/>
<result column="personAddress" property="address"/>
<result column="personTel" property="tel"/>
<!-- 一对多 -->
<!-- property:指的是集合属性的值 ofType:集合中元素的类型-->
<collection property="orders" ofType="Order">
<id column="orderId" property="id"/>
<result column="orderNumber" property="number"/>
<result column="orderPrice" property="price"/>
</collection>
</resultMap>
<select id="selectPersonById" parameterType="string" resultMap="personMap">
select p.*,o.* from person p,orders o where p.personId = o.pid and p.personId = #{id}
</select>
</mapper>
public class Order {
private String id;
private String number;
private int price;
private Person p;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public int getPrice() {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public Person getP() {
return p;
}
public void setP(Person p) {
this.p = p;
}
}
Order.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="cn.itcast.mybatis.domain.Order">
<resultMap type="Order" id="orderMap">
<id column="orderId" property="id"/>
<result column="orderNumber" property="number"/>
<result column="orderPrice" property="price"/>
<!-- 多对一的关系 -->
<association property="p" javaType="Person">
<id column="personId" property="id"/>
<result column="personName" property="name"/>
<result column="personAddress" property="address"/>
<result column="personTel" property="tel"/>
</association>
</resultMap>
<!-- 根据id查询Order,关联将Person查询出来 -->
<select id="selectOrderById" parameterType="string" resultMap="orderMap">
select p.*,o.* from person p,orders o where p.personId = o.pid and o.orderId = #{id}
</select>
</mapper>
public class MybatisTest {
SqlSessionFactory sf;
@Before
public void initSF()throws Exception{
String resource = "sqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sf= new SqlSessionFactoryBuilder().build(inputStream);
}
@Test//根据id查询User
public void test1()throws Exception{
SqlSession session = sf.openSession();
User u=session.selectOne("cn.itcast.mybatis.domain.User.selectUserById", "0001");//namespace+SelectId
System.out.println(u);
}
@Test//查询所有User
public void test2()throws Exception{
SqlSession session = sf.openSession();
List<User> users = session.selectList("cn.itcast.mybatis.domain.User.selectAllUsers");
for(User u: users){
System.out.println(u);
}
}
@Test//添加User
public void test3()throws Exception{
SqlSession session = sf.openSession(true);
User u = new User();
u.setId("0003");
u.setUserName("wangwu");
u.setAddress("beijing");
int i = session.insert("cn.itcast.mybatis.domain.User.insertUser", u);
//
//提交事务
// session.commit();
System.out.println(i);
}
@Test//根据id查询User,返回值为hashmap
public void test4()throws Exception{
SqlSession session = sf.openSession();
Map m = session.selectOne("cn.itcast.mybatis.domain.User.selectUserByIdForMap", "0003");
System.out.println(m);
}
@Test//添加User参数为HashMap
public void test5()throws Exception{
SqlSession session = sf.openSession();
Map u = new HashMap();
u.put("id", "0005");
u.put("name", "ddd");
u.put("address", "tianjin");
int i = session.insert("cn.itcast.mybatis.domain.User.insertUserForMap", u);
//提交事务
session.commit();
System.out.println(i);
}
@Test//根据id删除User
public void test6()throws Exception{
SqlSession session = sf.openSession();
int i = session.delete("cn.itcast.mybatis.domain.User.deleteUserById", "0001");
session.commit();
System.out.println(i);
}
@Test//根据id更新User
public void test7()throws Exception{
SqlSession session = sf.openSession();
User u = new User();
u.setId("0001");
u.setUserName("zhangsan");
u.setAddress("beijing");
int i = session.update("cn.itcast.mybatis.domain.User.updateUserById", u);
session.commit();
System.out.println(i);
}
@Test//根据id更新User,参数为Map
public void test8()throws Exception{
SqlSession session = sf.openSession();
Map u = new HashMap();
u.put("id", "0005");
u.put("userName","aaa");
u.put("address", "ddd");
int i = session.update("cn.itcast.mybatis.domain.User.updateUserByIdForMap", u);
session.commit();
System.out.println(i);
}
@Test//动态sql
public void test9()throws Exception{
SqlSession session = sf.openSession();
User u = new User();
//u.setId("0003");
u.setUserName("aaa");
//u.setAddress("dd");
List<User> users = session.selectList("cn.itcast.mybatis.domain.User.selectUserByCondition2", u);
for(User us : users){
System.out.println(us);
}
}
@Test//一对多关联查询
public void test10()throws Exception{
SqlSession session = sf.openSession();
Person p = session.selectOne("cn.itcast.mybatis.domain.Person.selectPersonById", "001");
System.out.println(p.getOrders().size());
}
@Test//多对一关联查询
public void test11()throws Exception{
SqlSession session = sf.openSession();
Order order = session.selectOne("cn.itcast.mybatis.domain.Order.selectOrderById", "o_00001");
System.out.println(order.getP().getName());
}
}