【Mybatis框架入门】02 输入映射、输出映射、动态sql(where if foreach sql) 单个对象映射 集合对象映射（resultMap） sprin与mybatis整合

1. 输入映射(就是映射文件中可以传入哪些参数类型)

1)基本类型
2)pojo类型
3)Vo类型

2. 输出映射(返回的结果集可以有哪些类型)

1)基本类型
2)pojo类型
3)List类型

3. 动态sql:动态的拼接sql语句,因为sql中where条件有可能多也有可能少

1)where:可以自动添加where关键字,还可以去掉第一个条件的and关键字
2)if:判断传入的参数是否为空
3)foreach:循环遍历传入的集合参数
4)sql:封装查询条件,以达到重用的目的

QueryVo.java

public class QueryVo {

	private User user;
	
	private List ids;

	
		select * from user where username like '%${user.username}%' and sex=#{user.sex}
	
	
	
	
		select count(*) from user 
	
	
	
		select * from user 
		
		
		
	
	
	
	
		
		
			
				and username like '%${username}%'
			
			
				and sex=#{sex}
			
		
	

	
		select * from user
		
			
				
				
					#{id}
				
			
		
	
	
 
4. 对单个对象的映射关系: 
商品订单数据模型 
 
1)自动关联(偷懒的办法):可以自定义一个大而全的pojo类,然后自动映射其实是根据数据库总的字段名称和
	pojo中的属性名称对应.
 
自动关联 resultType =CustomOrders 

	
		select a.*, b.id uid, username, birthday, sex, address 
		from orders a, user b 
		where a.user_id = b.id
	

Orders.java

public class Orders {
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
    
    //private User user;

CustomOrders.java

public class CustomOrders extends Orders{

	private int uid;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	
	

UserMapper.java

public List findOrdersAndUser1() ;

2)手动关联: 需要指定数据库中表的字段名称和java的pojo类中的属性名称的对应关系.
	使用association标签

手动关联 resultMap=“orderAndUserResultMap”

		select a.*, b.id uid, username, birthday, sex, address 
		from orders a, user b 
		where a.user_id = b.id
	
 

	
	
		
		
		
		
		
		
		
		
		
		
		
			
			
			
			
			
		
	
 
Orders.java 
public class Orders {
    private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;
    
    private User user;
 
UserMapper.java 
public List findOrdersAndUser2();
 
5. 对集合对象的映射关系 
只能使用手动映射:指定表中字段名称和pojo中属性名称的对应关系
	使用collection标签
 

		select a.*, b.id oid ,user_id, number, createtime 
		from user a, orders b where a.id = b.user_id
	

User.java

public class User {
	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	
	private List ordersList;

UserMapper.java

public List findUserAndOrders();

6. spring和mybatis整合

整合后会话工厂都归spring管理
1)原生Dao实现:
	需要在spring配置文件中指定dao实现类
	dao实现类需要继承SqlSessionDaoSupport超类
	在dao实现类中不要手动关闭会话,不要自己提交事务.
2)Mapper接口代理实现:
	在spring配置文件中可以使用包扫描的方式,一次性的将所有mapper加载

Mybatis整合spring 整合思路

1、SqlSessionFactory对象应该放到spring容器中作为单例存在。
2、传统dao的开发方式中，应该从spring容器中获得sqlsession对象。
3、Mapper代理形式中，应该从spring容器中直接获得mapper的代理对象。
4、数据库的连接以及数据库连接池事务管理都交给spring容器来完成。

5.2整合需要的jar包
1、spring的jar包
2、Mybatis的jar包
3、Spring+mybatis的整合包。
4、Mysql的数据库驱动jar包。
5、数据库连接池的jar包。

5.3整合的步骤
第一步：创建一个java工程。
第二步：导入jar包。（上面提到的jar包）
第三步：mybatis的配置文件sqlmapConfig.xml
第四步：编写Spring的配置文件
1、数据库连接及连接池
2、事务管理（暂时可以不配置）
3、sqlsessionFactory对象，配置到spring容器中
4、mapeer代理对象或者是dao实现类配置到spring容器中。
第五步：编写dao或者mapper文件
第六步：测试。

5.3.1SqlMapConfig.xml

5.3.2applicationContext.xml

5.3.3db.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
jdbc.username=root
jdbc.password=123456

5.4Dao的开发

三种dao的实现方式： 1、传统dao的开发方式 2、使用mapper代理形式开发方式 3、使用扫描包配置mapper代理。 5.4.1传统dao的开发方式

接口+实现类来完成。需要dao实现类需要继承SqlsessionDaoSupport类

5.4.1.1Dao实现类

UserDao .java

public interface UserDao {

	public User findUserById(Integer id);
	
	public List findUserByUserName(String userName);
}

UserDaoImpl.java

public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {

	

	@Override
	public User findUserById(Integer id) {
		//sqlSesion是线程不安全的,所以它的最佳使用范围在方法体内
		SqlSession openSession = this.getSqlSession();
		User user = openSession.selectOne("test.findUserById", id);
		//整合后会话归spring管理,所以不需要手动关闭.
		//openSession.close();
		return user;
	}

	@Override
	public List findUserByUserName(String userName) {
		SqlSession openSession = this.getSqlSession();
		List list = openSession.selectList("test.findUserByUserName", userName);
		return list;
	}
	
	
}

5.4.1.2配置dao
把dao实现类配置到spring容器中

5.4.1.3测试方法

UserDaoTest .java

public class UserDaoTest {

	private ApplicationContext applicatonContext;
	
	@Before
	public void setUp() throws Exception{
		String configLocation = "classpath:ApplicationContext.xml";
		applicatonContext = new ClassPathXmlApplicationContext(configLocation);
	}
	
	@Test
	public void testFindUserById() throws Exception{
		//获取UserDao对象, getBean中的字符串是在ApplicationContext.xml中声明的
		UserDao userDao = (UserDao)applicatonContext.getBean("userDao");
		
		User user = userDao.findUserById(1);
		System.out.println(user.toString());
	}
}

5.4.2Mapper代理形式开发dao

5.4.2.1开发mapper接口
开发mapper文件

5.4.2.2配置mapper代理

5.4.2.3测试方法

UserMapperTest.java

public class UserMapperTest {
	private ApplicationContext applicatonContext;
	
	@Before
	public void setUp() throws Exception{
		String configLocation = "classpath:ApplicationContext.xml";
		applicatonContext = new ClassPathXmlApplicationContext(configLocation);
	}
	
//	@Test
//	public void  testFindUserById() throws Exception{
//		UserMapper userMapper = (UserMapper)applicatonContext.getBean("userMapper");
//		
//		User user = userMapper.findUserById(1);
//		System.out.println(user);
//	}
	
	@Test
	public void testFindUserById() throws Exception{
		UserMapper userMapper = (UserMapper)applicatonContext.getBean("userMapper");
		
		User user = userMapper.selectByPrimaryKey(1);
		System.out.println(user);
	}
	
	@Test
	public void testFindUserAndSex() throws Exception{
		UserMapper userMapper = (UserMapper)applicatonContext.getBean("userMapper");
		
		//创建UserExample对象
		UserExample userExample = new UserExample();
		//通过UserExample对象创建查询条件封装对象(Criteria中是封装的查询条件)
		Criteria createCriteria = userExample.createCriteria();
		
		//加入查询条件
		createCriteria.andUsernameLike("%王%");
		createCriteria.andSexEqualTo("1");
		
		
		
		List list = userMapper.selectByExample(userExample);
		System.out.println(list);
	}
}

5.4.3扫描包形式配置mapper

每个mapper代理对象的id就是类名，首字母小写

7. 逆向工程:自动生成Pojo类,还可以自动生成Mapper接口和映射文件

注意:生成的方式是追加而不是覆盖,所以不可以重复生成,重复生成的文件有问题.
	如果想重复生成将原来生成的文件删除

codeMachine FreeMaker ftl RunEntrance.java

public class RunEntrance {
	
	public static void main(String[] args) throws SQLException {
		String tableName = "PACKING_LIST_C";
		
		
		CodeMeachine.setGeneratePath("d:\java\codeMachine\");
		CodeMeachine.setPackageName("cn.itcast.jk");
		
		//CodeMeachine.generateFileWithDb("demo.ftl", tableName, "Demo.java");
		
		CodeMeachine.generateFileWithDb("config.ftl", tableName, "Config.txt");
		CodeMeachine.generateFileWithDb("poModel.ftl", tableName, ".java");
		CodeMeachine.generateFileWithDb("mapper.hbm.ftl", tableName, ".hbm.xml");
		CodeMeachine.generateFileWithDb("ServiceInterface.ftl", tableName, "Service.java");
		CodeMeachine.generateFileWithDb("ServiceImpl.ftl", tableName, "ServiceImpl.java");
		CodeMeachine.generateFileWithDb("Action.ftl", tableName, "Action.java");
		
	
		CodeMeachine.generateFileWithDb("jDeptListPage.jsp", tableName, "ListPage.jsp");
		//CodeMeachine.generateFileWithDb("jRole.jsp", tableName, "List.jsp");
		CodeMeachine.generateFileWithDb("jDeptCreate.jsp", tableName, "Create.jsp");
		CodeMeachine.generateFileWithDb("jDeptUpdate.jsp", tableName, "Update.jsp");
		CodeMeachine.generateFileWithDb("jDeptView.jsp", tableName, "View.jsp");
		
		
		
		System.out.println("Generate success.");
	}
	
}

CodeMeachine.java

public class CodeMeachine {
	
	//默认生成文件的路径
	private static String generatePath = "D:\";
	private static String packageName = "cn.itcast.jk.domain";
	
	private static FreeMakerUtil freeMakerUtil = new FreeMakerUtil();
	private static DataService dataService = new DataServiceImpl();
	
	
	public static void generateVoFile(String tableName,String packageName,String classPre,String fileName) throws SQLException{
		generateFileWithDb("voModel.ftl", tableName, packageName, classPre,fileName);
	}
	
	
	public static void generateDaoFile(String tableName,String packageName,String classPre,String fileName){
		generateFileWithOutDb("daoModel.ftl", tableName, packageName, classPre,fileName);
	}
	
	
	public static void generateFileWithOutDb(String templateName,String tableName,String packageName,String classPre,String fileName){
		Map templateData = dataService.getTemplateDataWithOutDb(tableName, packageName, classPre);
		freeMakerUtil.generateFile(templateName, templateData, generatePath+fileName);
	}
	
	
	public static void generateFileWithDb(String templateName,String tableName,String packageName,String classPre,String fileName) throws SQLException{
		Map templateData = dataService.getDbTemplateData(tableName, packageName, classPre);
		freeMakerUtil.generateFile(templateName, templateData, generatePath+fileName);
	}
	
	public static void generateFileWithDb(String templateName,String tableName,String fileNameSuffix) throws SQLException{
		String className = ConvertUtil.getClassName(tableName);
		Map templateData = dataService.getDbTemplateData(tableName, packageName, className);
		
		String fileNamePrefix = ConvertUtil.formatAaa(className);
		if(fileNameSuffix.endsWith(".jsp")){
			fileNamePrefix = "j" + fileNamePrefix;		//如果是jsp文件按 jModelList.jsp命名规则
		}
		freeMakerUtil.generateFile(templateName, templateData, generatePath+fileNamePrefix+fileNameSuffix);
	}
	
	
	public static void setGeneratePath(String generatePath) {
		CodeMeachine.generatePath = generatePath;
	}
	
	//设置包路径
	public static void setPackageName(String packageName) {
		CodeMeachine.packageName = packageName;
	}

}

DbConn.java

public class DbConn {
	
	//定义一个连接对象
	
	
	private Connection conn= null;
	private String driver = "oracle.jdbc.driver.OracleDriver";
	//定义连接数据库的url资源
	private String url = "jdbc:oracle:thin:@192.168.116.132:1521:orcl";
	//定义连接数据库的用户名和密码
	private String userName = "itheima";
	private String passWord = "itheima";
	
	//加载数据库连接驱动
	public Connection getConnection(){
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, userName, passWord);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	//关闭数据库
	public void closeALL(Connection conn, Statement st, ResultSet rs, PreparedStatement pst) {
	    try {
	      if (rs != null) {
	        rs.close();
	      }
	      if (st != null) {
	        st.close();
	      }
	      if (conn != null) {
	        conn.close();
	      }
	      if (pst != null)
	        pst.close();
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	    }
	  }
	
	
}

Action.ftl

package ${ package }.action.xxx;

import java.util.List;

import com.itheima.jk.action.baseAction;
import com.itheima.jk.domain.${ className };
import com.itheima.jk.utils.Page;
import com.itheima.jk.service.${ className }Service;

import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ModelDriven;

<#import "CopyRight.ftl" as my>
<@my.CopyRight/>

public class ${ className }Action extends baseAction implements ModelDriven<${ className }> {
	//注入service
	private ${ className }Service ${ className?uncap_first }Service;
	public void set${ className }Service(${ className }Service ${ className?uncap_first }Service) {
		this.${ className?uncap_first }Service = ${ className?uncap_first }Service;
	}
	
	//model驱动
	private ${ className } model = new ${ className }();
	public ${ className } getModel() {
		return this.model;
	}
	
	//作为属性驱动，接收并封装页面参数
	private Page page = new Page();			//封装页面的参数，主要当前页参数
	public void setPage(Page page) {
		this.page = page;
	}


	//列表展示
	public String list(){
		String hql = "from ${ className } o";			//查询所有内容
		//给页面提供分页数据
		page.setUrl("${ className?uncap_first }Action_list");		//配置分页按钮的转向链接
		page = ${ className?uncap_first }Service.findPage(hql, page, ${ className }.class, null);
		super.put("page", page);
		
		return "plist";						//page list
	}
	
	//转向新增页面
	public String tocreate(){
		//准备数据
		List<${ className }> ${ className?uncap_first }List = ${ className?uncap_first }Service.${ className?uncap_first }List();
		super.put("${ className?uncap_first }List", ${ className?uncap_first }List);		//页面就可以访问${ className?uncap_first }List
		
		return "pcreate";
	}
	
	//新增保存
	public String insert(){
		${ className?uncap_first }Service.saveOrUpdate(model);
		
		return "alist";			//返回列表，重定向action_list
	}

	//转向修改页面
	public String toupdate(){
		//准备数据
		List<${ className }> ${ className?uncap_first }List = ${ className?uncap_first }Service.${ className?uncap_first }List();
		super.put("${ className?uncap_first }List", ${ className?uncap_first }List);		//页面就可以访问${ className?uncap_first }List
				
		//准备修改的数据
		${ className } obj = ${ className?uncap_first }Service.get(${ className }.class, model.getId());
		super.getValueStack().push(obj);
		
		return "pupdate";
	}
	
	//修改保存
	public String update(){
		${ className } ${ className?uncap_first } = ${ className?uncap_first }Service.get(${ className }.class, model.getId());
		
		//设置修改的属性，根据业务去掉自动生成多余的属性
		<#list properties as pro>  
		${ className?uncap_first }.set${ pro.proName?cap_first }(model.get${ pro.proName?cap_first }());
		
		
		${ className?uncap_first }Service.saveOrUpdate(${ className?uncap_first });
		
		return "alist";
	}
	
	//删除一条
	public String deleteById(){
		${ className?uncap_first }Service.deleteById(${ className }.class, model.getId());
		
		return "alist";
	}
	
	
	//删除多条
	public String delete(){
		${ className?uncap_first }Service.delete(${ className }.class, model.getId().split(", "));
		
		return "alist";
	}
	
	//查看
	public String toview(){
		${ className } obj = ${ className?uncap_first }Service.get(${ className }.class, model.getId());
		super.push(obj);
		
		return "pview";			//转向查看页面
	}
}