Java開發之Mybatis框架

mybasits配置文件書寫
1.configer文件配置
<?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>
<!-- 使用Mybaits的日志控制 -->
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<environments default="default"> <!-- 定義所有的數據庫鏈接,并指定使用哪一個數據源 -->
<environment id="default"><!-- 定義數據源名稱 -->
<transactionManager type="JDBC" /><!-- 事務的提交類型 -->
<dataSource type="POOLED"> <!-- 定義一個數據源 ,連接方式為數據庫連接池方式 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test4012?characterEncoding=utf8&serverTimezone=GMT"/>
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 加載數據庫鏈接的時候,加載的sql映射 -->
<mappers>
<mapper resource="com/pojo/usermapper-4012.xml" />
<mappers>
</configuration>
2.mapper文件配置
(1)resultType是返回值類型
（2）pramatetertype是傳入的參數
<?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.service.IUserDao4012"><!--接口的地址-->
<!-- 表示聲明一個數據庫的操作包名 -->
<!-- 聲明一個數據庫的操作方法 -->
<!-- 查詢全部內容的方法 -->
<select id="SelectAll" resultType="com.pojo.User4012">
SELECT * FROM table4012
limit #{offset},#{pagesize}
</select>
<!-- 插入操作-->
<insert id="InsertUser" parameterType="com.pojo.User4012">
insert into table4012(id,username,number,mybasits,android,javaee) values (#{id},#{username},#{number},#{mybasits},#{android},#{javaee})
</insert>
<delete id="DeleteUser" parameterType="com.pojo.User4012">
delete from table4012 where id=#{**}
</delete>
<update id="UpdateUser" parameterType="com.pojo.User4012">
update table4012 set username=#{username},number =#{number},mybasits=#{mybasits},android=#{android},javaee=#{javaee} where id=#{id}
</update>
<select id="findcount" parameterType="com.pojo.PageWays">
SELECT count () from table4012
</select>
<select id="findpage" parameterType="com.pojo.PageWays">
SELECT * from table4012
</select>
</mapper>
3.util類配置
package com.SelfStudy.util;
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 java.io.InputStream;
public class Util {
private static SqlSessionFactory build;//定義一個對象
static {
String s="mybatis.config.xml";//加載config文件
InputStream inputStream=null;//輸入輸出流
try {
inputStream= Resources.getResourceAsStream(s);
build=new SqlSessionFactoryBuilder().build(inputStream);
}
catch (Exception e){
e.printStackTrace();
}
finally {
try {
if ( inputStream !=null){
inputStream.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
}
public static SqlSession getSession(){
return build.openSession();
}
}
4.log4j.properties文件配置
log4j.appender.console =org.apache.log4j.ConsoleAppender
log4j.appender.console.Target =System.out
log4j.appender.console.layout =org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern =[%-5p][%d{yyyy-MM-dd HH:mm:ss}] %c %L %m%n

\u914D\u7F6E\u6839

log4j.rootLogger =debug,console
##log4j.logger.com.mapper.StudentMapper=debug,console
四.動態sql
1.if set標簽的使用
Select from table
if(test="name!=null and name!=''"){
#{id}
}
如果是數據庫添加語句在每一句后面添加，
if(test="name!=null and name!=''"){
#{id}，
}
2.where標簽的使用
Select from table
<where>
if(test="name!=null and name!=''"){
name=#{name}
}
如果是數據庫添加語句在每一句后面添加，
if(test="id!=null and id!=''"){
id=#{id}
}
</where>
3.choose when標簽的使用
<where>
<choose>
<when test="name!=null and name!=''">
and name=#{name}
</when>
<when></when>
</choose>
</where>

4. set標簽的使用
   uddate Table
   set id=#{id}
   <where>
   <if></if>
   </where>
5. trim標簽的使用
   <update id="upd" parameterType="Teacher">
   update Teacher
   <trim prefix="set" suffixOverrides=",">
   name=#{name},age=#{age},
   </trim>
   where id=#{id}
   </update>
   prefix=“在前面進行添加”
   prefixOverrides=“在前面進行去掉”
   suffix=“在后面進行添加”
   suffixOverrides=“在后面進行去掉”
   重點:執行的順序 先去除再添加
6. bind標簽
   <bind nam="nam" valus="'%'+nam+'%'"></bind>
   slct * from tabl wr nam lik #{nam}
7. foreach（集合查詢，添加）
   <foreach collecation="" open="" close="" item="">
   insert into table () valuse
   <if test="">
   #{},
   </if>
   </foreach>
   collection:傳入的參數類型
   open:打開方式
   close：關閉方式
   item:item
8. selectkey 的使用（查詢上一條記錄的一個屬性）
   <insert>
   insert table valuse()
   <selectkey keyproperty="" resulttype="" order="" keycolum=""></selectkey>
   select last_insert_name()
   </insert>
   keypropert是查詢的屬性的名稱
   resulttype是XM代理申請www.fx61.com/brokerlist/xm.html返回值類型,是查詢結果的返回值類型
   order是執行順序
   keycolum:數據庫中對應的屬性
   多種查詢方法的使用
   1.模糊查詢
   select from table where name=#{name}
   [1]在查詢的時候改正
   SelectAll("%ko%");
   [2]在sql語句中改正
   使用concat函數
   select from table where
   id=concat('%',#{id},'%')
9. 多表聯合查詢
   (1) mapper文件的配置類
   <!--首先對查詢的內容進行封裝-->
   <resultMap id="SelectTogether01" type="com.SelfStudy.pojo.PeopleWithDatil">//封裝連接類的屬性，主要是主類屬性
   <id property="detailid" column="id"></id>
   <result property="address" column="address"></result>
   <result property="country" column="country"></result>
   <result property="city" column="city"></result>
   <association property="PeopleDatil" javaType="com.SelfStudy.pojo.People">//用association封裝子類所有的屬性
   <id property="detailid" column="id"></id>
   <result property="name" column="name"></result>
   <result property="phone" column="phone"></result>
   </association>
   </resultMap>
   <select id="SelectTogether" resultMap="SelectTogether01">
   SELECT * FROM people ,peopledatil//聯合查詢，動態sql
   <where>
   people.id=peopledatil.id
   </where>
   </select>
   (2) test測試類@org.junit.Test
   br/>@org.junit.Test
   SqlSession session=Util.getSession();
   PeopleMapperDao peopleMapperDao=session.getMapper(PeopleMapperDao.class);
   peopleMapperDao.SelectTogether();
   List<People> list=null;
   list=peopleMapperDao.SelectTogether();
   System.out.println(list);
   }
   3.一對一聯合查詢
   配置類文件
   package com.pojo;
   public class Student {
   private String name;
   private Integer id;
   private Integer tid;//和老師中的id對因
   private String address;
   private String city;
   //一個學生對應一個老師
   private Teacher teacher;//在學生中查詢老師
   public String getName() {
   return name;
   }
   public void setName(String name) {
   this.name = name;
   }
   public Integer getId() {
   return id;
   }
   public void setId(Integer id) {
   this.id = id;
   }
   public Integer getTid() {
   return tid;
   }
   public void setTid(Integer tid) {
   this.tid = tid;
   }
   public String getAddress() {
   return address;
   }
   public void setAddress(String address) {
   this.address = address;
   }
   public String getCity() {
   return city;
   }
   public void setCity(String city) {
   this.city = city;
   }
   public Teacher getTeacher() {
   return teacher;
   }
   public void setTeacher(Teacher teacher) {
   this.teacher = teacher;
   }
   public Student(String name, Integer id, Integer tid, String address, String city, Teacher teacher) {
   this.name = name;
   this.id = id;
   this.tid = tid;
   this.address = address;
   this.city = city;
   this.teacher = teacher;
   }
   public Student() { }@Override
   br/>@Override
   return "Student{" +
   "name='" + name + '\'' +
   ", id=" + id +
   ", tid=" + tid +
   ", address='" + address + '\'' +
   ", city='" + city + '\'' +
   ", teacher=" + teacher +
   '}';
   }
   }
   接口配置
   package com.service;
   import com.pojo.Student;
   import java.util.List;
   public interface StudentDao {
   public List<Student> Selectall();
   public List<Student> SelectByid(Integer id);
   //查詢所有學生
   public List<Student> SelectAllStudent();
   public List<Student> selct01();
   }
   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.service.StudentDao">
   <resultMap id="map" type="com.pojo.Student">
   <id column="id" property="id"></id>
   <result property="name" column="name"></result>
   <result property="address" column="address"></result>
   <result property="city" column="city"></result>
   <result property="tid" column="tid"></result>
   <association property="teacher" column="tid" select="com.service.TeacherDao.SelectByid"></association>
   </resultMap>
   <!--聯合查詢-->
   <select id="selct01" resultType="com.pojo.Student">
   SELECT s.name, s.id ,s.tid ,s.address , s.city ,t.name , school ,age , sex,t.id
   FROM student s LEFT JOIN teacher t on
   s.tid=t.id
   </select>
   4.多對一聯合查詢
   <resultMap id="map01" type="com.pojo.Teacher">
   <id property="id1" column="id1" ></id>
   <result column="name1" property="name1"></result>
   <result column="school" property="school"></result>
   <result column="age" property="age"></result>
   <result column="sex" property="sex"></result>
   <collection property="students" ofType="com.pojo.Student">
   <id column="id" property="id"></id>
   <result column="name" property="name"></result>
   <result column="tid" property="tid"></result>
   <result column="address" property="address"></result>
   <result column="city" property="city"></result>
   </collection>
   </resultMap>
10. 多對多的聯合查詢
    實體類的配置
    package com.pojo;
    import java.util.Date;
    import java.util.List;
    public class Writer {
    private String name;
    private Integer id;
    private Integer age;
    private String sex;
    private String book;
    private Date birthday;
    //查詢作者里面包含作品
    private List<Works> works;
    public List<Works> getWorks() {
    return works;
    }
    public void setWorks(List<Works> works) {
    this.works = works;
    }
    public Writer(List<Works> works) {
    this.works = works;}
    @Override
    br/>}
    @Override
    return "Writer{" +
    "name='" + name + '\'' +
    ", id=" + id +
    ", age=" + age +
    ", sex='" + sex + '\'' +
    ", book='" + book + '\'' +
    ", birthday=" + birthday +
    ", works=" + works +
    '}';
    }
    public String getName() {
    return name;
    }
    public void setName(String name) {
    this.name = name;
    }
    public Integer getId() {
    return id;
    }
    public void setId(Integer id) {
    this.id = id;
    }
    public Integer getAge() {
    return age;
    }
    public void setAge(Integer age) {
    this.age = age;
    }
    public String getSex() {
    return sex;
    }
    public void setSex(String sex) {
    this.sex = sex;
    }
    public String getBook() {
    return book;
    }
    public void setBook(String book) {
    this.book = book;
    }
    public Date getBirthday() {
    return birthday;
    }
    public void setBirthday(Date birthday) {
    this.birthday = birthday;
    }
    public Writer(String name, Integer id, Integer age, String sex, String book, Date birthday) {
    this.name = name;
    this.id = id;
    this.age = age;
    this.sex = sex;
    this.book = book;
    this.birthday = birthday;
    }
    public Writer() { }
    }
    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.service.WriterDao">
    <!--所有作者-->
    <select id="SelectAllWriter" resultType="com.pojo.Writer">
    select from writer w
    </select>
    <select id="SelectWriterByid" resultType="com.pojo.Writer">
    select from writer w
    <where>
    id=#{id}
    </where>
    </select>
    <resultMap id="map01" type="com.pojo.Writer">
    <id property="id" column="id"></id>
    <result column="name" property="name"></result>
    <result column="age" property="age"></result>
    <result column="sex" property="sex"></result>
    <result column="book" property="book"></result>
    <result column="birthday" property="birthday"></result>
    <collection property="works" ofType="com.pojo.Works">
    <id property="book_id" column="book_id"></id>
    <id property="book_name" column="book_name"></id>
    <id property="book_press" column="book_press"></id>
    <id property="press_date" column="press_date"></id>
    </collection>
    </resultMap>
    <select id="SelectAllWriterAndWorks" resultMap="map01">
    SELECT w.age,w.birthday,w.book,w.id,w.name,w.sex ,o.book_id,o.book_press,o.press_date,o.book_name
    FROM writer w LEFT JOIN information i ON w.id=i.writer_idLEFT JOIN works o ON o.book_id=i.works_id</select>
    </mapper>
    注解方式配置
    @Results(value={
    br/></select>
    </mapper>
    注解方式配置
    @Results(value={
    br/>@Result(column="",property=""),
    br/>@Result(column="",property=""),
    br/>@Result(column="",property=""),
    @Select(".......")
    br/>})
    @Select(".......")
    
11. 導入包
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
    <dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.4</version>
    </dependency>
    //導入依賴
    2.構建方法
    public class ImportExcel {
    //導入excel文件的方法
    public void ReadExcel(File file){
    List<String> list=new ArrayList<>();
    //1.傳入需要導入的Excel文件的路徑
    try{
    //2.讀取文件的內容
    HSSFWorkbook workbook=new HSSFWorkbook(FileUtils.openInputStream(file));
    //3.讀取文件的sheet頁,sheet就是Excel中的每一張表，也就是workboo的sheet頁
    HSSFSheet sheet=workbook.getSheetAt(0);
    //4.開始讀取表的內容
    int startline=0;//起始行的行數，也就是從0 到最后一行
    int endline=sheet.getLastRowNum()+1;//最后一行d的下表
    //遍歷每一行
    Map<Integer,Map<Integer,Object>> map=new HashMap<>();
    //第一行，第一列，值
    //遍歷行
    for (int a=1;a<endline;a++){
    HSSFRow hssfRow=sheet.getRow(a);
    //遍歷列
    //寫一個集合存儲列和值
    Map<Integer,Object> result=new HashMap<>();
    int endcell=hssfRow.getLastCellNum();
    for (int j=0;j<endcell;j++){
    result.put(j, hssfRow.getCell(j));//用行數去獲取列
    }
    map.put(a, result);
    }
    List<People> list1=new ArrayList<>();
    for (Integer xxx:map.keySet()){
    System.out.print(map.get(xxx).get(0));
    System.out.print(map.get(xxx).get(1));
    System.out.print(map.get(xxx).get(2));
    System.out.print(map.get(xxx).get(3));
    People people=new People();
    people.setName(String.valueOf(map.get(xxx).get(0)));
    String java=String.valueOf(map.get(xxx).get(1));
    Integer java1=Integer.parseInt(java.substring(0, java.indexOf(".")));
    people.setJava(java1);
    String mybas=String.valueOf(map.get(xxx).get(2));
    Integer mybas1=Integer.parseInt(mybas.substring(0, mybas.indexOf(".")));
    people.setMybas(mybas1);
    String androi=String.valueOf(map.get(xxx).get(3));
    Integer androi1=Integer.parseInt(androi.substring(0, androi.indexOf(".")));
    people.setAndroi(androi1);
    list1.add(people);
    }
    SqlSession session= Util.getSession();
    PeopleMapperDao peopleMapperDao=session.getMapper(PeopleMapperDao.class);
    peopleMapperDao.ExcelInsert(list1);
    session.commit();
    }
    catch (Exception e){
    e.printStackTrace();
    }
    }
    }
12. 調用方法@org.junit.Test
    br/>@org.junit.Test
    ImportExcel aaa=new ImportExcel();
    File file=new File("C:/Users/lenovo/Desktop/study/ssm筆記/test4012.xls");
    aaa.ReadExcel(file);
    }