mybatis-day01

使用JDBC编程

首先我们需要创建数据库mybatis，然后创建表user并新增数据，sql语句在项目中。
创建web项目，导入mysql-connector-java-5.1.7-bin.jar

步骤

	/**
	 * 1 加载驱动
	 * 2 获取连接
	 * 3 编写sql，预编译sql，赋值
	 * 4 实行sql
	 * 5 遍历结果集
	 * 6 释放资源
	 */

代码

@Test
public void test1() {
	Connection conn=null;
	PreparedStatement ps=null;
	ResultSet resultSet=null;
	try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "root");
		String sql="select * from user where username=?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, "王五");
		resultSet = ps.executeQuery();
		while (resultSet.next()) {
			int id=resultSet.getInt("id");
			String username=resultSet.getString("username");
			System.out.println(id+"---"+username);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}	
}

mybatis入门

准备

加入jar

asm-3.3.1.jar
cglib-2.2.2.jar
commons-logging-1.1.1.jar
javassist-3.17.1-GA.jar
junit-4.9.jar
log4j-1.2.17.jar
log4j-api-2.0-rc1.jar
log4j-core-2.0-rc1.jar
mybatis-3.2.7.jar
mysql-connector-java-5.1.7-bin.jar
slf4j-api-1.7.5.jar
slf4j-log4j12-1.7.5.jar

创建pojo、pojo.xml、sqlMapConfig.xml
pojo.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="test">
	<select id="findUserById" parameterType="int" resultType="com.cqc.mybatis01.pojo.User">
		select * from user where id=#{id}
	</select>
</mapper>

sqlMapConfig.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">
<!-- 
加载数据库properties
设置别名
设置环境（整合spring后去掉）
加载user.xml文件
 -->
<configuration>
	<properties resource="db.properties"/>
	<typeAliases>
		<typeAlias type="com.cqc.mybatis01.pojo.User" alias="user"/>
	</typeAliases>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="${jdbc.driver}" />
			<property name="url" value="${jdbc.url}" />
			<property name="username" value="${jdbc.username}" />
			<property name="password" value="${jdbc.password}" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="User.xml"/>
	</mappers>
</configuration>

代码

selectOne()

@Test
public void testSelectOne() throws IOException {
	//加载sqlMapConfig.xml
	String resource="sqlMapConfig.xml";
	InputStream is = Resources.getResourceAsStream(resource);
	//获取sqlSessionFactory
	SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
	//获取sqlSession
	SqlSession session = factory.openSession();
	//执行selectOne()或selectList()
	User user = session.selectOne("test.findUserById",1);
	System.out.println(user);
	session.close();
}

selectList()

注意：#{}不可以加引号

<select id="findUserByName" parameterType="java.lang.String" resultType="com.cqc.mybatis01.pojo.User">
	select * from user where username like "%"#{username}"%"
</select>

或者

select * from user where username like '%${value}%'

List<User> list = session.selectList("test.findUserByName", "王");

insert()

User user = new User("lucy", new Date(), "女", "上海市");

<insert id="insertUser" parameterType="com.cqc.mybatis01.pojo.User" >
	insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})
</insert>

插入并返回 int类型的id,把id赋值给user

<insert id="insertUser2" parameterType="com.cqc.mybatis01.pojo.User" >
	<selectKey keyColumn="id" keyProperty="id" order="AFTER" resultType="int">
		select last_insert_id()
	</selectKey>
	insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address})
</insert>

如果使用uuid作为主键，那么order=“before”

update()

int update = session.update("test.updateUserById", user);

<update id="updateUserById" parameterType="com.cqc.mybatis01.pojo.User">
	update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>

如果用${}也是可以的，但是date类型的值不行

<!-- 修改： 使用$赋值 birthday=${birthday}, 无效。  加单引号也不行。 ${id}加不加单引号都行 -->
<update id="updateUserById2" parameterType="com.cqc.mybatis01.pojo.User">
	update user set username='${username}',birthday=${birthday},sex='${sex}',address='${address}' where id=${id}
</update>

delete

int delete = session.delete("test.deleteUserById",user);

<delete id="deleteUserById" parameterType="user">
	delete from user where id=#{id}
</delete>

DAO层开发的2种方式

第一种：原始方式-接口Dao和实现类DaoImpl

接口

public interface UserDao {

	User findUserById(int id);
}

实现类

public class UserDaoImpl implements UserDao {

	//传递sessionFactory
	private SqlSession session;
	public UserDaoImpl(SqlSession session) {
		super();
		this.session = session;
	}

	@Override
	public User findUserById(int id) {
		User user = session.selectOne("test.findUserById",1);
		return user;
	}

}

test

public class UseDaoTest {

	private SqlSessionFactory factory;

	@Before
	public void init() {
		//加载sqlMapConfig.xml
		String resource="sqlMapConfig.xml";
		InputStream is;
		try {
			is = Resources.getResourceAsStream(resource);
			factory = new SqlSessionFactoryBuilder().build(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	@Test
	public void test1() {
		//获取sqlSession
		SqlSession session = factory.openSession();
		//执行selectOne()或selectList()
		User user = session.selectOne("test.findUserById",1);
		System.out.println(user);
		session.close();
	}
}

第二种：Mapper动态代理

只写Mapper.java Mapper.xml不写实现类

public interface UserMapper {
	User findUserById(int id);
}

<?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.cqc.mybatis01.mapper.UserMapper">
	<select id="findUserById" parameterType="int" resultType="User">
		select * from user where id=#{id}
	</select>
</mapper>

SqlSession session = factory.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = mapper.findUserById(1);

mybatis中mapper.xml where if foreach 标签

where

<select id="findByNameAndSex" parameterType="User" resultType="User" >
	select * from user 
	<where>
		<if test="username!=null and username!=''">
			and username like "%"#{username}"%"
		</if>
		<if test="sex!=null and sex!=''">
			and sex=#{sex}
		</if>
	</where>
</select>

if

<select id="findByNameAndSex2" parameterType="User" resultType="User" >
	select * from user 
	<where>
		<if test="username!=null and username!=''">
			and username like "%"#{username}"%"
		</if>
		<if test="sex!=null and sex!=''">
			and sex=#{sex}
		</if>
	</where>
</select>

foreach

<select id="findUserByIds" parameterType="com.cqc.mybatis01.vo.UserVo" resultType="User">
	select * from user 
	<where>
		<foreach collection="ids" item="id" open="id in(" close=")" separator=",">
			#{id}
		</foreach>
	</where>
</select>

模糊查询中$#的写法

List<User> list = mapper.findUserByName("王");
select * from user where username like '%${value}%'

List<User> list = mapper.findUserByName("王");
select * from user where username like "%"#{username}"%"

List<User> list = mapper.findUserByName("%王%");
select * from user where username like #{username}

#与$的区别？#

mybatis和hibernate的区别