MyBatis学习笔记四之动态SQL

一.pojo

Log.java

package com.bjsxt.pojo;

public class Log {
	private int id;
	private String accIn;
	private String accOut;
	private double money;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getAccIn() {
		return accIn;
	}
	public void setAccIn(String accIn) {
		this.accIn = accIn;
	}
	public String getAccOut() {
		return accOut;
	}
	public void setAccOut(String accOut) {
		this.accOut = accOut;
	}
	public double getMoney() {
		return money;
	}
	public void setMoney(double money) {
		this.money = money;
	}
	@Override
	public String toString() {
		return "Log [id=" + id + ", accIn=" + accIn + ", accOut=" + accOut + ", money=" + money + "]";
	}
}

二.mapper

LogMapper.java

package com.bjsxt.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.bjsxt.pojo.Log;

public interface LogMapper {
	List<Log> selByAccinAccout(@Param("accout") String accout, @Param("accin") String accin);
	
	
	int upd(Log log);
	
	
	List<Log> selByLog(Log log);
	
	
	List<Log> selIn(List<Integer> list);
	
	
	int ins(List<Integer> list);
}

LogMapper.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.bjsxt.mapper.LogMapper">
	<!-- <select id="selByAccinAccout" resultType="log">
		select * from log where 1=1
		OGNL表达式,直接写key或对象的属性.不需要添加任何特字符号
		<if test="accin!=null and accin!=''">
			and accin=#{accin}
		</if>
		<if test="accout!=null and accout!=''">
			and accout=#{accout}
		</if>
	</select> -->
	
	<!-- <select id="selByAccinAccout" resultType="log">
		select * from log 
		<where>
			<if test="accin!=null and accin!=''">
				and accin=#{accin}
			</if>
			<if test="accout!=null and accout!=''">
				and accout=#{accout}
			</if>
		</where>
	</select> -->
	
	<select id="selByAccinAccout" resultType="log">
		select * from log 
		<where>
			<choose>
				<when test="accin!=null and accin!=''">
					and accin=#{accin}
				</when>
				<when test="accout!=null and accout!=''">
					and accout=#{accout}
				</when>
			</choose>
		</where>
	</select>
	
<!-- 	<update id="upd" parameterType="log" >
		update log 
		<set>
			id=#{id},
			<if test="accIn!=null and accIn!=''">
				accin=#{accIn},
			</if>
			<if test="accOut!=null and accOut!=''">
				accout=#{accOut},
			</if>
		</set>
		where id=#{id}
	</update> -->
	
	<update id="upd" parameterType="log">
		update log
		<trim prefix="set" suffixOverrides=",">
		a=a,
		</trim>
		where id=100
	</update>
	
	<!-- <select id="selByLog" parameterType="log" resultType="log">
		select * from log
		<trim prefix="abc" prefixOverrides="abc">abc</trim>
	</select> -->
	
	
	<select id="selByLog" parameterType="log" resultType="log">
		<bind name="accin" value="'%'+accin+'%'"/>
		#{money}
	</select>
	
	<select id="selIn" parameterType="list" resultType="log">
		select * from log where id in
		<foreach collection="list" item="abc" open="(" close=")" separator=",">
			#{abc}
		</foreach>
	</select>
    

    <!-- 批量新增-->	
	<insert id="ins" parameterType="list">
		insert into log values
		<trim suffixOverrides=",">
			<foreach collection="list" item="log">
				(default,#{log},2,2),
			</foreach>
		</trim>
	</insert>
	
	
	<select id="">
		 select <include refid="mysql"></include>
		 from log
	</select>
	
	<sql id="mysql">
		id,accin,accout,money
	</sql>
		
</mapper>

三.test

Test.java

package com.bjsxt.test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.bjsxt.mapper.LogMapper;
import com.mysql.jdbc.PreparedStatement;


public class Test {
	public static void main(String[] args) throws IOException {
		
		
		InputStream is = Resources.getResourceAsStream("mybatis.xml");
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
		SqlSession session = factory.openSession(ExecutorType.BATCH);
		Scanner input = new Scanner(System.in);
		System.out.println("请输入转账帐号:");
		String accout = input.nextLine();
		System.out.println("请输入收款帐号");
		String accin = input.nextLine();
		
		LogMapper mapper = session.getMapper(LogMapper.class);
//		List<Log> list = mapper.selByAccinAccout(accout, accin);
//		System.out.println(list);
//		Log log = new Log();
//		log.setId(1);
//		log.setAccOut(accout);
//		log.setAccIn(accin);
//		int index = mapper.upd(log);
//		System.out.println(index);
//		Log log = new Log();
//		mapper.selByLog(new Log());
//		mapper.upd(new Log());
		
		List<Integer> list = new ArrayList<>();
		for (int i = 0; i < 10000; i++) {
			list.add(i);
		}
		
		mapper.ins(list);
		
//		mapper.selIn(list);
		
		
		session.commit();//增删改需要进行提交
		session.close();
		System.out.println("程序执行结束");
	}
}

对应笔记复习