mybatis自学历程(二)
2024-08-26 20:14:58
传递多个参数
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>
mybatis.xml
# 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
log4j.properties
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 + "]";
}
}
Log.java
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);
}
LogMapper.java
<?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>
LogMapper.xml
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(); }
}
Test.java
数据库数据如下
运行结果如下
数据查询和分页
数据库
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
log4j.properties
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>
mybatis.xml
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);
}
}
MybatisUtil.java
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 自动生成的方法存根 }
}
OpenSessionInView.java
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;
}
}
Teachar.java
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;
}
}
Student.java
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;
}
}
PageInfo.java
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);
}
StudentMapper.java
package com.mybatis.mapper;
import com.mybatis.po.Teachar;
public interface TeacharMapper {
Teachar selById(int id);
}
TeacharMapper.java
<?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>
StudentMapper.xml
<?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>
TeacharMapper.xml
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);
}
StudentService.java
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;
} }
StudentServiceImpl.java
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);
} }
ShowServlet.java
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>
index.jsp
机场查询
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}.
最新文章
- python address already in use
- CSS系列：CSS文字样式
- AngularJS中bootstrap启动
- html显示时间
- 评价软件_搜狗输入法（pc端）
- iframe 传值问题
- yii2.0 gii
- 【转】Android -- Looper.prepare()和Looper.loop()
- linux指令备份
- [国嵌攻略][103][Linux内核模块基础]
- 使用Eureka作为springcloud的注册机
- Android中的数据储存
- 课程五(Sequence Models)，第二 周（Natural Language Processing & Word Embeddings） —— 2.Programming assignments：Emojify
- BFS广度优先搜索 炸弹人
- 调整SQLServer最大服务器内存参数后实例停止且无法启动
- 关于CSS中的浮动
- TOJ1302: 简单计算器 && TOJ 4873: 表达式求值&&TOJ3231: 表达式求值
- MySql 利用mysql&mysqldum导入导出数据
- 从源码角度一步一步来修改PreferenceActivity界面
- android 安装调试第三方应用失败