<small id="7ktuj"></small>
      <bdo id="7ktuj"></bdo>
        <mark id="7ktuj"></mark>

        <source id="7ktuj"></source>
        <small id="7ktuj"></small>

        ITPub博客

        首頁 > 應用開發 > Java > java框架之mybatis

        java框架之mybatis

        原創 Java 作者:蝴蝶飛啊飛 時間:2019-11-06 14:26:27 0 刪除 編輯

        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://www.ep4tq.com/69946279/viewspace-2662892/,如需轉載,請注明出處,否則將追究法律責任。

        上一篇: JAVA中線程的創建
        請登錄后發表評論 登錄
        全部評論
        管他誰是誰非,做自己的主宰,我是這條街最亮的崽!

        注冊時間:2019-08-22

        • 博文量
          49
        • 訪問量
          21835
        妹子图每日分享