mybatis自学历程(二)

传递多个参数

1.在mybatis.xml下<mappers>下使用<package>

<mappers>
        <package name="com.mybatis.mapper"/>
</mappers>

2.在com.mybatis.mapper下新建接口

public interface LogMapper {
    List<Log> sellAll();
    
    List<Log> selByAccInAccOut(Integer accin,Integer accout);
}

3.在com.mybatis.mapper新建一个LogMapper.xml

3.1 namespace 必须和接口全路径(包名+类名)一致

3.2 id值必须和接口中的方法相同

3.3 如果接口中方法为多个参数，可以省略parameterType

3.4.1 #{ } 中使用param+数字，表示第几个参数

<?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.mybatis.mapper.LogMapper">
    <select id="sellAll" resultType="Log">
        select * from log
    </select>
    
    <!-- 当多参数时，不需要写 parameterType-->
    <select id="selByAccInAccOut" resultType="Log" >
        select * from log where accin=#{param1} and accout=#{param2}
    </select>
</mapper>

3.4.2 也可以使用注解

List<Log> selByAccInAccOut(@Param("accin") Integer accin,@Param("accout") Integer accout);

<select id="selByAccInAccOut" resultType="Log" >
        select * from log where accin=#{accin} and accout=#{accout}
</select>

案例

新建项目mybatis05

 

<?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>
    <settings>
        <setting name="logImpl" value="LOG4J" />
    </settings>
    <typeAliases>
        <!-- <typeAlias type="com.mybatis.po.People" alias="peo"/> -->
        <package name="com.mybatis.po" />
    </typeAliases>
    <!-- 配置mybatis运行环境 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <!-- MySQL数据库驱动 -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <!-- 连接数据库的URL -->
                <property name="url"
                    value="jdbc:mysql://localhost:3306/test?serverTimezone=UTC" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <!-- 将mapper文件加入到配置文件中 -->
    <mappers>
        <package name="com.mybatis.mapper"/>
    </mappers>
</configuration>

 

# Global logging configuration
log4j.rootLogger=Info,stdout,R
# MyBatis logging configuration...
log4j.logger.com.mybatis=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

log4j.appender.R=org.apache.log4j.DailyRollingFileAppender
log4j.appender.R.File=D\:\\logs\\log.log   
log4j.appender.R.layout=org.apache.log4j.PatternLayout
1log4j.appender.R.layout.ConversionPattern=%m %n

com.mybatis.po包

package com.mybatis.po;

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 + "]";
    }
    
}

com.mybatis.mapper包

package com.mybatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.mybatis.po.Log;

public interface LogMapper {
    List<Log> sellAll();
    
    List<Log> selByAccInAccOut(@Param("accin") Integer accin,@Param("accout") Integer accout);
}

<?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.mybatis.mapper.LogMapper">
    <select id="sellAll" resultType="Log">
        select * from log
    </select>
    
    <!-- 当多参数时，不需要写 parameterType-->
    <select id="selByAccInAccOut" resultType="Log" >
        select * from log where accin=#{accin} and accout=#{accout}
    </select>
</mapper>

com.mybatis.test包

package com.mybatis.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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 com.mybatis.mapper.LogMapper;
import com.mybatis.po.Log;

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();
        
        LogMapper logMapper=session.getMapper(LogMapper.class);
//        List<Log> list=logMapper.sellAll();
//        for(Log log :list){
//            System.out.println(log);
//        }
        
        List<Log> list=logMapper.selByAccInAccOut(1, 3);
        for(Log log:list){
            System.out.println(log);
        }
        
        session.close();
        
    }
}

数据库数据如下

 

 运行结果如下

 

数据查询和分页

 

 数据库

teachar表(由于输入太快，把teacher输成teachar)

 

 student表

 

 sql语句

create table teachar(
id     int(10) primary key auto_increment,
name varchar(20)
);

create table student(
id int(10) primary key auto_increment,
name varchar(20),
age int(3),
tid int(10),
CONSTRAINT fk_teachar FOREIGN key (tid) REFERENCES teachar(id)
);

insert into teachar VALUES(DEFAULT,'老师1');
insert into teachar VALUES(DEFAULT,'老师2');

insert into student values(DEFAULT,'学生1',12,1);
insert into student values(DEFAULT,'学生2',12,1);
insert into student values(DEFAULT,'学生3',12,1);
insert into student values(DEFAULT,'学生4',12,1);
insert into student values(DEFAULT,'学生5',12,1);
insert into student values(DEFAULT,'学生6',12,1);
insert into student values(DEFAULT,'学生7',12,1);
insert into student values(DEFAULT,'学生8',12,2);
insert into student values(DEFAULT,'学生9',12,2);
insert into student values(DEFAULT,'学生10',12,2);

新建项目mybatis07

导入所需jar包和jQuery.js

log4j.properties

# Global logging configuration
log4j.rootLogger=Info,stdout,R
# MyBatis logging configuration...
log4j.logger.com.mybatis=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

log4j.appender.R=org.apache.log4j.DailyRollingFileAppender
log4j.appender.R.File=D\:\\logs\\log.log   
log4j.appender.R.layout=org.apache.log4j.PatternLayout
1log4j.appender.R.layout.ConversionPattern=%m %n

 mybatis.xml

<?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>
    <settings>
        <setting name="logImpl" value="LOG4J" />
    </settings>
    <typeAliases>
        <!-- <typeAlias type="com.mybatis.po.People" alias="peo"/> -->
        <package name="com.mybatis.po" />
    </typeAliases>
    <!-- 配置mybatis运行环境 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <!-- MySQL数据库驱动 -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <!-- 连接数据库的URL -->
                <property name="url"
                    value="jdbc:mysql://localhost:3306/test?serverTimezone=UTC" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <!-- 将mapper文件加入到配置文件中 -->
    <mappers>
        <package name="com.mybatis.mapper"/>
    </mappers>
</configuration>

com.mybatis.util包

package com.mybatis.util;

import java.io.IOException;
import java.io.InputStream;

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

public class MybatisUtil {
    
    private static SqlSessionFactory factory;
    private static ThreadLocal<SqlSession> tl=new ThreadLocal<SqlSession>();
    static{
        try {
            InputStream is=Resources.getResourceAsStream("mybatis.xml");
            factory=new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
    }
    
    public static SqlSession getsSession() {
        SqlSession session=tl.get();
        if (session==null) {
            tl.set(factory.openSession());
        }
        
        return tl.get();
    }
    
    public static void closeSession() {
        SqlSession session=tl.get();
        if(session!=null){
            session.close();
        }
        tl.set(null);
    }
}

com.mybatis.filter包

package com.mybatis.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;

import org.apache.ibatis.session.SqlSession;

import com.mybatis.util.MybatisUtil;

@WebFilter("/*")
public class OpenSessionInView implements Filter{

    @Override
    public void destroy() {
        // TODO 自动生成的方法存根
        
    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {
        SqlSession session=MybatisUtil.getsSession();
        try {
            chain.doFilter(request, response);
            session.commit();
        } catch (Exception e) {
            session.rollback();
            e.printStackTrace();
        }finally {
            MybatisUtil.closeSession();
        }
        
        
    }

    @Override
    public void init(FilterConfig filterConfig) throws ServletException {
        // TODO 自动生成的方法存根
        
    }
}

com.mybatis.po包

package com.mybatis.po;

public class Teachar {
    private int id;
    private String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    
}

package com.mybatis.po;

public class Student {
    private int id;
    private String name;
    private int age;
    private int tid;
    private Teachar teachar;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public int getTid() {
        return tid;
    }
    public void setTid(int tid) {
        this.tid = tid;
    }
    public Teachar getTeachar() {
        return teachar;
    }
    public void setTeachar(Teachar teachar) {
        this.teachar = teachar;
    }
    
    
}

package com.mybatis.po;

import java.util.List;

public class PageInfo {
    //每个分页显示的条数
    private int pageSize;
    //当前是第几页
    private int pageNumber;
    //总页数
    private long total;
    private List<?> list;
    //学生姓名
    private String sname;
    //老师姓名
    private String tname;
    //已查过前几条
    private int pageStart;
    
    
    public int getPageStart() {
        return pageStart;
    }
    public void setPageStart(int pageStart) {
        this.pageStart = pageStart;
    }
    public int getPageSize() {
        return pageSize;
    }
    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }
    public int getPageNumber() {
        return pageNumber;
    }
    public void setPageNumber(int pageNumber) {
        this.pageNumber = pageNumber;
    }
    public long getTotal() {
        return total;
    }
    public void setTotal(long total) {
        this.total = total;
    }
    public List<?> getList() {
        return list;
    }
    public void setList(List<?> list) {
        this.list = list;
    }
    public String getSname() {
        return sname;
    }
    public void setSname(String sname) {
        this.sname = sname;
    }
    public String getTname() {
        return tname;
    }
    public void setTname(String tname) {
        this.tname = tname;
    }
    
    
}

com.mybatis.mapper包

package com.mybatis.mapper;

import java.util.List;

import com.mybatis.po.PageInfo;
import com.mybatis.po.Student;

public interface StudentMapper {
    List<Student> selByPage(PageInfo pi);
    
    long selCountByPageInfo(PageInfo pi);
}

package com.mybatis.mapper;

import com.mybatis.po.Teachar;

public interface TeacharMapper {
    Teachar selById(int id);
}

<?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.mybatis.mapper.StudentMapper">
    <select id="selByPage" parameterType="PageInfo" resultType="Student">
        select * from student
        <where>
            <if test="sname!=null and sname!=''">
                <bind name="sname" value="'%'+sname+'%'"/>
                and name like #{sname}
            </if>
            <if test="tname!=null and tname!=''">
                <bind name="tname" value="'%'+tname+'%'"/>
                and tid in (select id from teachar where name like #{tname})
            </if>
        </where>
        limit #{pageStart},#{pageSize}
    </select>
    
    <select id="selCountByPageInfo" resultType="long" parameterType="PageInfo">
        select count(*) from student
        <where>
            <if test="sname!=null and sname!=''">
                <bind name="sname" value="'%'+sname+'%'"/>
                and name like #{sname}
            </if>
            <if test="tname!=null and tname!=''">
                <bind name="tname" value="'%'+tname+'%'"/>
                and tid in (select id from teachar where name like #{tname})
            </if>
        </where>
    </select>
</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.mybatis.mapper.TeacharMapper">
    <select id="selById" parameterType="int" resultType="Teachar">
        select * from teachar where id = #{param1}
    </select>
</mapper>

com.mybatis.service包

package com.mybatis.service;

import com.mybatis.po.PageInfo;

public interface StudentService {
    PageInfo showPge(String sname,String tname,String pageSize,String pageNumber);
}

com.mybatis.service.Impl包

package com.mybatis.service.Impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.mybatis.mapper.StudentMapper;
import com.mybatis.mapper.TeacharMapper;
import com.mybatis.po.PageInfo;
import com.mybatis.po.Student;
import com.mybatis.service.StudentService;
import com.mybatis.util.MybatisUtil;

public class StudentServiceImpl implements StudentService {

    @Override
    public PageInfo showPge(String sname, String tname, String pageSizeStr, String pageNumberStr) {
        int pageSize=2;
        if(pageSizeStr!=null&&!pageSizeStr.equals("")){
            pageSize=Integer.parseInt(pageSizeStr);
        }
        
        
        int pageNumber=1;
        if(pageNumberStr!=null&&!pageNumberStr.equals("")){
            pageNumber=Integer.parseInt(pageNumberStr);
        }
        
        SqlSession session=MybatisUtil.getsSession();
        StudentMapper studentMapper=session.getMapper(StudentMapper.class);
        
        PageInfo pi=new PageInfo();
        pi.setPageNumber(pageNumber);
        pi.setPageSize(pageSize);
        pi.setPageStart((pageNumber-1)*pageSize);
        pi.setSname(sname);
        pi.setTname(tname);
        
        List<Student> list=studentMapper.selByPage(pi);
        
        TeacharMapper teacharMapper=session.getMapper(TeacharMapper.class);
        for(Student student:list){
            student.setTeachar(teacharMapper.selById(student.getTid()));
        }
        long count=studentMapper.selCountByPageInfo(pi);
        
        pi.setList(list);
        
        pi.setTotal(count%pageSize==0?count/pageSize:count/pageSize+1);
        return pi;
    }

}

com.mybatis.servlet包

package com.mybatis.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mybatis.po.PageInfo;
import com.mybatis.service.StudentService;
import com.mybatis.service.Impl.StudentServiceImpl;

@WebServlet("/show")
public class ShowServlet extends HttpServlet {
    private StudentService stuSerivce = new StudentServiceImpl();

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String sname = req.getParameter("sname");
        //sname=new String(sname.getBytes("iso-8859-1"),"utf-8");
        String tname = req.getParameter("tname");
        //tname=new String(tname.getBytes("iso-8859-1"),"utf-8");
        String pageSize = req.getParameter("pageSize");
        String pageNumber = req.getParameter("pageNumber");
        PageInfo pi = stuSerivce.showPge(sname, tname, pageSize, pageNumber);
        req.setAttribute("pageInfo", pi);
        req.getRequestDispatcher("index.jsp").forward(req, resp);
    }

}

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'index.jsp' starting page</title>
<script type="text/javascript" src="/mybatis07/js/jquery-3.4.1.min.js"></script>
<script type="text/javascript">
    $(function() {
        var pageSize = "${pageInfo.pageSize}";
        var pageNumber = "${pageInfo.pageNumber}";
        var tname = "${pageInfo.tname}";
        var sname = "${pageInfo.sname}";
        var total = "${pageInfo.total}";


        $.each($(":radio"), function(i, n) {
            if ($(n).val() == pageSize) {
                $(n).attr("checked", "checked");
            }
        });

        $(":text[name='sname']").val(sname);
        $(":text[name='tname']").val(tname);

        $("button").click(function() {
            location.href = "show?pageSize=" + pageSize + "&pageNumber=1&tname=" + $(":text[name='tname']").val() + "&sname=" + $(":text[name='sname']").val();
        });

        $(":radio").click(function() {
            pageSize = $(this).val();
            location.href = "show?pageSize=" + pageSize + "&pageNumber=1&tname=" + $(":text[name='tname']").val() + "&sname=" + $(":text[name='sname']").val();
        });

        $(".page_a:eq(0)").click(function() {
            pageNumber = parseInt(pageNumber) - 1;
            if (pageNumber >= 1) {
                location.href = "show?pageSize=" + pageSize + "&pageNumber=" + pageNumber + "&tname=" + $(":text[name='tname']").val() + "&sname=" + $(":text[name='sname']").val();
            } else {
                pageNumber = 1;
            }

            return false;
        });

        $(".page_a:eq(1)").click(function() {
            pageNumber = parseInt(pageNumber) + 1;
            if (pageNumber <= total) {
                location.href = "show?pageSize=" + pageSize + "&pageNumber=" + pageNumber + "&tname=" + $(":text[name='tname']").val() + "&sname=" + $(":text[name='sname']").val();
            } else {
                pageNumber = total;
            }

            return false;
        });

    });
</script>
</head>

<body>
    <input type="radio" value="2" name="pageSize" />2
    <input type="radio" value="3" name="pageSize" />3
    <input type="radio" value="4" name="pageSize" />4
    <br /> 学生姓名：
    <input type="text" name="sname" /> 老师姓名：
    <input type="text" name="tname" />
    <button>查询</button>
    <br />
    <table border="1">
        <tr>
            <td>学生编号</td>
            <td>学生姓名</td>
            <td>年龄</td>
            <td>任课老师</td>
        </tr>
        <c:forEach items="${pageInfo.list }" var="stu">
            <tr>
                <td>${stu.id }</td>
                <td>${stu.name }</td>
                <td>${stu.age }</td>
                <td>${stu.teachar.name }</td>
            </tr>
        </c:forEach>
    </table>
    <a href="" class="page_a">上一页</a>
    <a href="" class="page_a">下一页</a>
</body>
</html>

 

机场查询

IDE：idea

建表

create table airport(
id int(10) primary key auto_increment,
portname varchar(20),
cityname varchar(20)
);

insert into airport values(default,'首都机场','北京');
insert into airport values(default,'南菀机场','北京');
insert into airport values(default,'虹桥机场','上海');

create table airplane(
id int(10) primary key auto_increment,
airno varchar(20),
time int(5) COMMENT '单位分钟',
price double,
takeid int(10) comment '起飞机场',
landid int(10) comment '降落机场'
);

insert into airplane values(default,'波音747',123,100,1,3);
insert into airplane values(default,'波音858',56,300,3,2);

 

新建项目

导入jar包

 

 

 src目录

 

 

mybatis.xml

<?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>
    <settings>
        <setting name="logImpl" value="LOG4J" />
    </settings>
    <typeAliases>
        <!-- <typeAlias type="com.mybatis.po.People" alias="peo"/> -->
        <package name="com.mybatis.po" />
    </typeAliases>
    <!-- 配置mybatis运行环境 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <!-- MySQL数据库驱动 -->
                <property name="driver" value="com.mysql.cj.jdbc.Driver" />
                <!-- 连接数据库的URL -->
                <property name="url"
                    value="jdbc:mysql://localhost:3306/test?serverTimezone=UTC" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <!-- 将mapper文件加入到配置文件中 -->
    <mappers>
        <!-- <mapper resource="com/mybatis/mapper/LogMapper.xml"/> -->
        <package name="com.mybatis.mapper"/>
    </mappers>
</configuration>

log4j.properties

# Global logging configuration
log4j.rootLogger=Info,stdout,R
# MyBatis logging configuration...
log4j.logger.com.mybatis=DEBUG
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

log4j.appender.R=org.apache.log4j.DailyRollingFileAppender
log4j.appender.R.File=D\:\\logs\\log.log   
log4j.appender.R.layout=org.apache.log4j.PatternLayout
1log4j.appender.R.layout.ConversionPattern=%m %n

 

 

 util包

package com.mybatis.util;

import java.io.IOException;
import java.io.InputStream;

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

public class MybatisUtil {
    
    private static SqlSessionFactory factory;
    private static ThreadLocal<SqlSession> tl=new ThreadLocal<SqlSession>();
    static{
        try {
            InputStream is=Resources.getResourceAsStream("mybatis.xml");
            factory=new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
    }
    
    public static SqlSession getsSession() {
        SqlSession session=tl.get();
        if (session==null) {
            tl.set(factory.openSession());
        }
        
        return tl.get();
    }
    
    public static void closeSession() {
        SqlSession session=tl.get();
        if(session!=null){
            session.close();
        }
        tl.set(null);
    }
}

filter包

package com.mybatis.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;

import org.apache.ibatis.session.SqlSession;

import com.mybatis.util.MybatisUtil;

@WebFilter("/*")
public class OpenSessionInView implements Filter{

    @Override
    public void destroy() {
        // TODO 自动生成的方法存根
        
    }

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {
        SqlSession session=MybatisUtil.getsSession();
        try {
            chain.doFilter(request, response);
            session.commit();
        } catch (Exception e) {
            session.rollback();
            e.printStackTrace();
        }finally {
            MybatisUtil.closeSession();
        }
        
        
    }

    @Override
    public void init(FilterConfig filterConfig) throws ServletException {
        // TODO 自动生成的方法存根
        
    }
}

po包

package com.mybatis.po;

public class Airport {
    private int id;
    private String portName;
    private String cityName;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getPortName() {
        return portName;
    }

    public void setPortName(String portName) {
        this.portName = portName;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    @Override
    public String toString() {
        return "Airprt [id=" + id + ", portName=" + portName + ", cityName=" + cityName + "]";
    }


}

package com.mybatis.po;

public class Airplane {
    private int id;
    private String airNo;
    private int time;
    private double price;
    private Airport takePort;
    private Airport landPort;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getAirNo() {
        return airNo;
    }

    public void setAirNo(String airNo) {
        this.airNo = airNo;
    }

    public int getTime() {
        return time;
    }

    public void setTime(int time) {
        this.time = time;
    }

    public double getPrice() {
        return price;
    }

    public void setPrice(double price) {
        this.price = price;
    }

    public Airport getTakePort() {
        return takePort;
    }

    public void setTakePort(Airport takePort) {
        this.takePort = takePort;
    }

    public Airport getLandPort() {
        return landPort;
    }

    public void setLandPort(Airport landPort) {
        this.landPort = landPort;
    }

    @Override
    public String toString() {
        return "Airplane [id=" + id + ", airNo=" + airNo + ", time=" + time + ", price=" + price + ", takePort="
                + takePort + ", landPort=" + landPort + "]";
    }

}

mapper包

package com.mybatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import com.mybatis.po.Airport;

public interface AirportMapper {
    /**
     * 查询起飞机场
     * @return
     */
    @Select("select * from airport where id in (select distinct takeid from airplane)")
    List<Airport> selTakePort();
    
    /**
     * 查询降落机场
     * @return
     */
    @Select("select * from airport where id in (select distinct landid from airplane)")
    List<Airport> selLandPort();
}

package com.mybatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.mybatis.po.Airplane;

public interface AirplaneMapper {
    List<Airplane> selByTakeidLandid(@Param("takeid") Integer takeid, @Param("landid") Integer landid);
}

<?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.mybatis.mapper.AirplaneMapper">
    <resultMap type="Airplane" id="mymap">
        <id column="id" property="id"/>
        <result column="time" property="time"/>
        <result column="price" property="price"/>
        <result column="airno" property="airNo"/>
        <association property="takePort" javaType="airport">
            <id column="takeid" property="id"/>
            <result column="takecityname" property="cityName"/>
            <result column="takeportname" property="portName"/>
        </association>
        <association property="landPort" javaType="airport">
            <id column="landid" property="id"/>
            <result column="landcityname" property="cityName"/>
            <result column="landportname" property="portName"/>
        </association>
    </resultMap>
    <select id="selByTakeidLandid" resultMap="mymap">
        select a.*,p.id takeid,p.portname takeportname,p.cityname takecityname, t.id landid,t.portname landportname
        ,t.cityname landcityname
        from airplane a LEFT JOIN airport p on a.takeid=p.id LEFT JOIN airport t on t.id=a.landid
        <where>
            <if test="takeid>0">
                and takeid=#{takeid}
            </if>
            <if test="landid>0">
                and landid=#{landid}
            </if>
        </where>
    </select>
</mapper>

service包

package com.mybatis.service;

import java.util.List;

import com.mybatis.po.Airplane;

public interface AirplaneService {
    List<Airplane> show(int takeid, int landid);
}

package com.mybatis.service;

import java.util.List;

import com.mybatis.po.Airport;

public interface AirportService {
    /**
     * 显示所有起飞机场
     * @return
     */
    List<Airport> showTakePort();
    
    /**
     * 显示所有降落机场
     * @return
     */
    List<Airport> showLandPort();
}

impl包

package com.mybatis.service.impl;

import java.util.List;

import com.mybatis.mapper.AirplaneMapper;
import com.mybatis.po.Airplane;
import com.mybatis.service.AirplaneService;
import com.mybatis.util.MybatisUtil;

public class AirplaneServiceImpl implements AirplaneService {

    @Override
    public List<Airplane> show(int takeid, int landid) {
        return MybatisUtil.getsSession().getMapper(AirplaneMapper.class).selByTakeidLandid(takeid, landid);
    }
    
}

package com.mybatis.service.impl;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.mybatis.mapper.AirportMapper;
import com.mybatis.po.Airport;
import com.mybatis.service.AirportService;
import com.mybatis.util.MybatisUtil;

public class AirportServiceImpl implements AirportService {

    @Override
    public List<Airport> showTakePort() {
        SqlSession session=MybatisUtil.getsSession();
        AirportMapper airportMapper=session.getMapper(AirportMapper.class);
        return airportMapper.selTakePort();
    }

    @Override
    public List<Airport> showLandPort() {
        SqlSession session=MybatisUtil.getsSession();
        AirportMapper airportMapper=session.getMapper(AirportMapper.class);
        return airportMapper.selLandPort();
    }

    
}

servlet包

package com.mybatis.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mybatis.service.AirplaneService;
import com.mybatis.service.impl.AirplaneServiceImpl;

@WebServlet("/showairplane")
public class ShowAirplaneServlet extends HttpServlet {
    private AirplaneService airplaneService= new AirplaneServiceImpl();

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        int takeid=0;
        String takeIdStr=req.getParameter("takeid");
        if(takeIdStr!=null&&!takeIdStr.equals("")){
            takeid=Integer.parseInt(takeIdStr);
        }
        
        int landid=0;
        String landIdStr=req.getParameter("landid");
        if(landIdStr!=null&&!landIdStr.equals("")){
            landid=Integer.parseInt(landIdStr);
        }
        
        req.setAttribute("list", airplaneService.show(takeid, landid));
        req.getRequestDispatcher("index.jsp").forward(req, resp);
    }
    
}

package com.mybatis.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mybatis.service.AirportService;
import com.mybatis.service.impl.AirportServiceImpl;

@WebServlet("/showland")
public class ShowLandServlet extends HttpServlet {
    private AirportService airportService=new AirportServiceImpl();

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setAttribute("landport", airportService.showLandPort());
        req.getRequestDispatcher("showairplane").forward(req, resp);
//        req.getRequestDispatcher("index.jsp").forward(req, resp);
    }
    
}

package com.mybatis.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.mybatis.service.AirportService;
import com.mybatis.service.impl.AirportServiceImpl;

@WebServlet("/showtake")
public class ShowTakeServlet extends HttpServlet {
    private AirportService airportService=new AirportServiceImpl();

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setAttribute("takeport", airportService.showTakePort());
        req.getRequestDispatcher("showland").forward(req, resp);
    }
    
}

index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title>机场查询</title>
</head>

<body>
<form action="showtake" method="post">
  起飞机场：
  <select name="takeid">
    <option value="0">请选择</option>
    <c:forEach items="${takeport }" var="take">
      <option value="${take.id }">${take.portName }</option>
    </c:forEach>
  </select>

  降落机场：
  <select name="landid">
    <option value="0">请选择</option>
    <c:forEach items="${landport }" var="take">
      <option value="${take.id }">${take.portName }</option>
    </c:forEach>
  </select>
  <input type="submit" value="查询"/>
</form>
<table border="1">
  <tr>
    <td>飞机编号</td>
    <td>起飞机场</td>
    <td>起飞城市</td>
    <td>降落机场</td>
    <td>降落城市</td>
    <td>航行时间</td>
    <td>票价(元)</td>
  </tr>
  <c:forEach items="${list }" var="plane">
    <tr>
      <td>${plane.airNo }</td>
      <td>${plane.takePort.portName}</td>
      <td>${plane.takePort.cityName }</td>
      <td>${plane.landPort.portName }</td>
      <td>${plane.landPort.cityName }</td>
      <%--<td>
        <c:if test="${Math.floor(plane.time/60)>0 }">
          ${Math.floor(plane.time/60) }小时
        </c:if>
        <c:if test="${plane.time%60>0 }">
          ${plane.time%60 }分钟
        </c:if>
      </td>--%>
        <td>
            <c:if test="${plane.time/60>1 }">
<%--              用formatNumber是为去小数点--%>
                  <fmt:formatNumber value="${Math.floor(plane.time/60) }" pattern="0"></fmt:formatNumber>小时
            </c:if>
          <c:if test="${plane.time%60>0}">
            ${plane.time%60 }分钟
          </c:if>
        </td>
      <td>${plane.price }</td>
    </tr>
  </c:forEach>
</table>
</body>
</html>

 运行结果如下

 

 航行时间，后因测试有修改，故实际效果不一致

 

补充:

mybatis使用#{}和${}的区别

在mybatis中#{}表示一个占位符：

        1、#将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号

        2、#在很大程度上可以防止sql注入

        3、例如#{id}：#{}中的id表示输入的参数名称，如果输入参数是简单类型，那么#{}中的参数可以任意。

        4、能用#{}就别用${}
在mybatis中${}表示一个拼接符：

       1、${}将传入的数据直接显示生成在sql中。

       2、如果使用${},而你传入的是字符串，比如中文、英文。就必须这样：'${}',不然会报（Unknown column 'TT' in 'where clause'）的错误，当然传入数字没问题。

       3、${value}: ${}中value表示输入的参数名称，如果输入的参数是简单类型，那么${}中的值只能是value

       4、${}存在sql注入的风险，慎用！但是在特殊场景下必须使用${},比如order by 语句后面要跟动态列，就得使用${colname}.

 

也就有这样一个问题 为什么${} 不安全 还要用 ${} ？

有一些场景是必须用 ${} 的

就是 一些string 类型的 不会给加上 ‘ ’ 比如order by 语句后面要跟动态列，就得使用${colname}.