使用mybatis实现CRUD

使用mybatis教程

1、导入mybaties的jar，如mybatis-3.1.1.jar（再加上mysql的jar）

2.在src文件下新建一个xml文件，如Configuration.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>
    <typeAliases> 
        <typeAlias alias="User" type="com.zzu.cy.User"/>
        <typeAlias alias="Orders" type="com.zzu.cy.Orders"/>
    </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://127.0.0.1:3306/mybaties" />
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="com/zzu/cy/User.xml"/>
        <mapper resource="com/zzu/cy/orders.xml"/>
    </mappers>
</configuration>

3、新建一个查询User类的xml文件：com/zzu/cy/User.xml，com/zzu/cy/orders.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="com.zzu.cy.User">
	
	<resultMap type="com.zzu.cy.User" id="userResultMap">
		<id column="id" property="id"/>
		<result column="userName" property="userName"/>
		<result column="userAge" property="userAge"/>
		<result column="userAddress" property="userAddress"/>
		<collection property="orderList" ofType="com.zzu.cy.Orders" column="oid">
			<!-- 表的主键不能同名，如user表主键为id，则关联的orders表的主键不能为id，否则查询的结果为一条 -->
			<id column="ooid" property="id"/>
			<result column="price" property="price"/>
		</collection>
	</resultMap>

	<select id="selectUserByID" parameterType="int" resultType="User">
		select * from user where id = #{id}
	</select>
	<select id="selectAll" resultType="User" >
		select * from user
	</select>
	<insert id="inserUser" useGeneratedKeys="true" keyProperty="id" parameterType="User">
		insert into user (userName,userAge,userAddress) values
		(#{userName},#{userAge},#{userAddress})
	</insert>
	<update id="updateUserByID" parameterType="User">
		update user set userName = #{userName} where id = #{id}
	</update>
	
	<delete id="deleteUserByID" parameterType="int">
		delete from user where id = #{id}
	</delete>
	
	<select id="getOrdersByUserID" parameterType="int" resultMap="userResultMap">
		select u.*, o.* from user u,orders o where u.id = o.uid and o.uid=#{id}
	</select>
	
</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.zzu.cy.Orders">
	<resultMap type="com.zzu.cy.Orders" id="ordersResultMap">
		<id column="ooid" property="id"/>
		<result column="price" property="price"/>
		<association property="user" javaType="com.zzu.cy.User">
			<id column="id" property="id"/>
			<result column="userName" property="userName"/>
			<result column="userAge" property="userAge"/>
			<result column="userAddress" property="userAddress"/>
		</association>
	</resultMap>
	
	<select id="getUserByOrdersID" parameterType="int" resultMap="ordersResultMap">
		select u.* , o.* from user u , orders o where u.id = o.uid and o.ooid = #{id}
	</select>
	
	<!-- 级联插入#{user.id} -->
	<insert id="insertOrders" useGeneratedKeys="true" keyProperty="id" parameterType="Orders">
		insert into orders(price,uid) values(#{price},#{user.id})
	</insert>
</mapper>

测试代码：

package com.zzu.cy;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

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 org.junit.Before;
import org.junit.Test;

public class UserTest {

	private Reader reader;
	private SqlSessionFactory sqlSessionFactory;
	
	@Before
	public void getSession() {
		try {
			reader = Resources.getResourceAsReader("Configuration.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@Test
	public void testSelectById() {
		SqlSession session = sqlSessionFactory.openSession();
		User user = session.selectOne("com.zzu.cy.User.selectUserByID", 1);
		System.out.println(user.toString());

	}
	
	@Test
	public void testSelectAll() {
		SqlSession session = sqlSessionFactory.openSession();
		List<User> users = session.selectList("com.zzu.cy.User.selectAll");
		for (User user : users) {
			System.out.println(user);
		}
	}
	
	@Test
	public void testInsert() {
		SqlSession session = sqlSessionFactory.openSession();
		User user = new User();
		user.setUserName("展开剂");
		user.setUserAge(40);
		user.setUserAddress("北京朝阳");
		int row = session.insert("com.zzu.cy.User.inserUser", user);
		session.commit();
		System.out.println(row);
	}
	
	@Test
	public void testUpdate() {
		SqlSession session = sqlSessionFactory.openSession();
		User user = session.selectOne("com.zzu.cy.User.selectUserByID", 3);
		user.setUserName("数据库");
		session.update("com.zzu.cy.User.updateUserByID", user);
		session.commit();
		session.close();
	}
	
	@Test
	public void testDelete() {
		SqlSession session = sqlSessionFactory.openSession();
		session.delete("com.zzu.cy.User.deleteUserByID", 8);
		session.commit();
	}
	
	@Test
	public void testCollection() {
		SqlSession session = sqlSessionFactory.openSession();
		User user = session.selectOne("com.zzu.cy.User.getOrdersByUserID", 1);
		System.out.println(user.toString());
		List<Orders> orders = user.getOrderList();
		for (Orders orders2 : orders) {
			System.out.println(orders2);
		}
		session.close();
	}
	
	@Test
	public void testManyToOne() {
		SqlSession session = sqlSessionFactory.openSession();
		Orders order = session.selectOne("com.zzu.cy.Orders.getUserByOrdersID",1);
		System.out.println(order.toString());
		User user = order.getUser();
		System.out.println(user.toString());
	} 
	
	@Test
	public void testInsetOrders() {
		SqlSession session = sqlSessionFactory.openSession();
		Orders orders = new Orders();
		orders.setPrice(new Double(23.2));
		User user = new User();
		user.setId(1);
		orders.setUser(user);
		session.insert("com.zzu.cy.Orders.insertOrders", orders);
		session.commit();
		session.close();
	}
	


}