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://blog.itpub.net/69946279/viewspace-2662892/,如需转载,请注明出处,否则将追究法律责任。

上一篇: JAVA中线程的创建
请登录后发表评论 登录
全部评论
管他谁是谁非,做自己的主宰,我是这条街最亮的崽!

注册时间:2019-08-22

  • 博文量
    49
  • 访问量
    21288