mybatis开发Dao

参考文献:https://blog.csdn.net/u013036274/article/details/55668317

原始dao方式

需要写dao接口和dao实现类。

创建dao接口：UserDao.java

package cn.itcast.mybatis.dao;
import cn.itcast.mybatis.po.Employee;
public interface UserDao {
	public Employee findUserById(int id) throws Exception;
	public void insertUser(Employee employee) throws Exception;
	public void deleteUser(int id) throws Exception;
}

创建dao实现类：UserDaoImpl.java

package cn.itcast.mybatis.dao;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import cn.itcast.mybatis.po.Employee;
public class UserDaoImpl implements UserDao{
	private SqlSessionFactory sqlSessionFactory;
	public  UserDaoImpl(SqlSessionFactory sqlSessionFactory) {
		this.sqlSessionFactory = sqlSessionFactory;
	}
	@Override
	public Employee findUserById(int id) throws Exception {
		SqlSession sqlSession =sqlSessionFactory.openSession();
        Employee employee = sqlSession.selectOne("test.findUserById",id);
	    sqlSession.close();
	    return employee;
	}
	@Override
	public void insertUser(Employee employee) throws Exception {
		SqlSession sqlSession =sqlSessionFactory.openSession();
        sqlSession.insert("test.insertUser",employee);
        sqlSession.commit();
	    sqlSession.close();	
	}
	@Override
	public void deleteUser(int id) throws Exception {
		SqlSession sqlSession =sqlSessionFactory.openSession();
        sqlSession.delete("test.deleteUser",id);
        sqlSession.commit();
	    sqlSession.close();	
	}
}

测试方法：

package cn.itcast.mybatis.dao;
import static org.junit.Assert.*;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import cn.itcast.mybatis.po.Employee;
public class UserDaoImplTest {
	private SqlSessionFactory sqlSessionFactory;
	@Before
	public void setUp() throws Exception{
		String resource="SqlMapConfig.xml";
	    InputStream inputStream= Resources.getResourceAsStream(resource);
		sqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);
	}
	@Test
	public void test() throws Exception {
		UserDao userDao = new UserDaoImpl(sqlSessionFactory);
		Employee employee = userDao.findUserById(3);
		System.out.println(employee);
	}
}

缺点：

接口实现类方法中存在大量模板方法，设想能否将这些代码提取出来。
另外调用SqlSession方法时，将Statement的id硬编码了。
调用SqlSession方法时，传入的变量，由于SqlSession方法使用泛型，即使变量类型传入错误，在编译阶段也不报错，不利于开发。

mapper代理方法

xml映射文件 (UserMapper.xml)
①在mapper.xml中namespace等于mapper接口地址
②mapper.java接口中的方法名和mapper.xml中的Statement的id一致
③mapper.java接口中的方法输入参数类型和mapper.xml中Statement的parameterType指定类型一致
④mapper.java接口中的方法返回值类型和mapper.xml中Statement的resultType指定的类型一致

<mapper namespace="cn.itcast.mybatis.mapper.UserMapper">
     <select id="findUserById" parameterType="int" resultType="cn.itcast.mybatis.po.Employee">
         select id,username,sex,address from users where id=#{id};
     </select>

mapper接口，相当于dao接口(UserMapper.java)

package cn.itcast.mybatis.mapper;
import cn.itcast.mybatis.po.Employee;
public interface UserMapper {
	public Employee findUserById(int id) throws Exception;
        public java.util.List<Employee> findUserByName(String name) throws Exception;
	public void insertUser(Employee employee ) throws Exception;
	public void deleteUser(int id) throws Exception;

}

还需在SqlMapConfig.xml加载映射文件

1	<mapper resource="mapper/UserMapper.xml"/>

测试文件

package cn.itcast.mybatis.mapper;
import static org.junit.Assert.*;
import java.io.InputStream;
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;
import cn.itcast.mybatis.po.Employee;
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
	@Before
	public void setUp() throws Exception{
		String resource="SqlMapConfig.xml";
	    InputStream inputStream= Resources.getResourceAsStream(resource);
		sqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);
	}
	@Test
	public void test() throws Exception {
		SqlSession sqlSession =sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		Employee employee =userMapper.findUserById(2);
		System.out.println(employee);
	}
        @Test
	public void test1() throws Exception {
		SqlSession sqlSession =sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		java.util.List<Employee> list =userMapper.findUserByName("zhao");
		sqlSession.close();
		System.out.println(list);
	}
}

通过调用单独的文件连接数据库

SqlMapConfig.xml

<properties resource="db.properties"></properties>
<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>

db.properties（和SqlMapConfig.xml在同一目录下）

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8
jdbc.username=root
jdbc.password=

pojo包装类型实现

UserMapper.xml（定义用户信息综合查询，查询条件复杂）

1
2
3

<select id="findUserList" parameterType="cn.itcast.mybatis.po.UserQueryVo" resultType="cn.itcast.mybatis.po.EmployeeCustom">
        select id,username,sex,address from users where sex= #{employeeCustom.sex} and username like '%${employeeCustom.username}%';
    </select>

UserQueryVo.java（包装对象）

package cn.itcast.mybatis.po;
public class UserQueryVo {
	private EmployeeCustom employeeCustom;
	public EmployeeCustom getEmployeeCustom() {
		return employeeCustom;
	}
	public void setEmployeeCustom(EmployeeCustom employeeCustom) {
		this.employeeCustom = employeeCustom;
	}
}

EmployeeCustom.java

1
2
3

package cn.itcast.mybatis.po;
public class EmployeeCustom extends Employee{	
}

测试文件

@Test
	public void test1() throws Exception {
		SqlSession sqlSession =sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
                //创建包装对象，设置查询条件
		UserQueryVo userQueryVo= new UserQueryVo();
		EmployeeCustom employeeCustom=new EmployeeCustom();
		employeeCustom.setSex("nv");
		employeeCustom.setUsername("zhou");
		userQueryVo.setEmployeeCustom(employeeCustom);
               //调用userMapper的方法
		java.util.List<EmployeeCustom> list =userMapper.findUserList(userQueryVo);
		sqlSession.close(); 
		System.out.println(list);
	}

动态sql判断

UserMapper.xml(判断传入的sex和username是否为空，若是空则不进行拼接)

<select id="findUserList" parameterType="cn.itcast.mybatis.po.UserQueryVo" resultType="cn.itcast.mybatis.po.EmployeeCustom">
        select id,username,sex,address from users 
        <where>
             <if test="employeeCustom!=null">
                <if test="employeeCustom.sex!=null and employeeCustom.sex!=''">
                     and sex= #{employeeCustom.sex}
                </if>
                <if test="employeeCustom.username!=null and employeeCustom.username!=''">
                      and username like '%${employeeCustom.username}%';
                </if>
              </if>
        </where>  
         
    </select>

测试文件

@Test
	public void test1() throws Exception {
		SqlSession sqlSession =sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		UserQueryVo userQueryVo= new UserQueryVo();
		EmployeeCustom employeeCustom=new EmployeeCustom();
		//employeeCustom.setSex("nv");//注释此句后，查询语句中没有sex这个条件
		employeeCustom.setUsername("zhou");
		userQueryVo.setEmployeeCustom(employeeCustom);
		java.util.List<EmployeeCustom> list =userMapper.findUserList(userQueryVo);
		sqlSession.close(); 
		System.out.println(list);
	}

进行select * from users where id=1 or id=10 or id=11 ;

在上面的UserMapper.xml中添加

<if test="ids!=null">
                 <!-- collection:指定输入对象中集合属性 item:每个遍历生成对象中 open:开始遍历时拼接串 
                 close:结束遍历时拼接的串 separator:遍历两个对象须拼接的串
                                实现拼接 AND (id=1 or id=10 or id=13) -->
                 <foreach collection="ids" item="user_id" open="AND (" close=")" separator="or">
                      id=#{user_id}
                 </foreach>

测试文件

@Test
	public void test1() throws Exception {
		SqlSession sqlSession =sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		UserQueryVo userQueryVo= new UserQueryVo();
		EmployeeCustom employeeCustom=new EmployeeCustom();
		//employeeCustom.setSex("nv");
		employeeCustom.setUsername("zhou");
		java.util.List<Integer> ids =new ArrayList<Integer>();
		ids.add(1);
		ids.add(10);
		ids.add(14);
		userQueryVo.setIds(ids);
		userQueryVo.setEmployeeCustom(employeeCustom);
		java.util.List<EmployeeCustom> list =userMapper.findUserList(userQueryVo);
		sqlSession.close(); 
		System.out.println(list);
	}