java框架之mybatis

蝴蝶飛啊飛發表於2019-11-06

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 是外匯返傭http://www.fx61.com/返回值型別 , 是查詢結果的返回值型別

order 是執行順序

keycolum: 資料庫中對應的屬性

多種查詢方法的使用

1. 模糊查詢

select * from table where name=#{name}

[1] 在查詢的時候改正

SelectAll("%ko%");

[2] sql 語句中改正

使用concat 函式

select * from table where

id=concat('%',#{id},'%')

2. 多表聯合查詢

(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

    public void TestTogether(){

        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

    public String toString() {

        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>

5. 多對多的聯合查詢

實體類的配置

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

    public String toString() {

        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_id`LEFT JOIN works o ON o.`book_id`=i.`works_id`

    </select>

</mapper>

註解方式配置

@Results(value={

    @Result(id=true,column="",property=""),

     @Result(column="",property=""),

     @Result(column="",property=""),

     @Result(column="",property=""),

     @Result(column="",property=""),

     @Result(column="",property=""),

     @Result(column="",property=""),   @Result(column="tid",property="works",many=@Many(Select="com.service.iuserdao.workByid") ),

})

@Select(".......")

java 匯入 Excel 表格

1. 匯入包

<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();

        }

    }

}

3. 呼叫方法

@org.junit.Test

    public void TestExcel(){

        ImportExcel aaa=new ImportExcel();

        File file=new File("C:/Users/lenovo/Desktop/study/ssm 筆記 /test4012.xls");

        aaa.ReadExcel(file);

    }

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69946279/viewspace-2662892/,如需轉載,請註明出處,否則將追究法律責任。

相關文章