Mybatis第一个例子-CSDN博客

本文链接：https://blog.csdn.net/xinxinqiu/article/details/25157701

导包

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());
	}

}