mybatis自学历程(二)
传递多个参数
public interface LogMapper {
List
List<Log> selByAccInAccOut(Integer accin,Integer accout); }
3.1 namespace 必须和接口全路径(包名+类名)一致
3.2 id值必须和接口中的方法相同
3.3 如果接口中方法为多个参数，可以省略parameterType
3.4.1 #{ } 中使用param+数字，表示第几个参数
<!-- 当多参数时，不需要写 parameterType-->
<select id="selByAccInAccOut" resultType="Log" >
select \* from log where accin=#{param1} and accout=#{param2}
</select> 3.4.2 也可以使用注解
List
新建项目mybatis05
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
List<Log> selByAccInAccOut(@Param("accin") Integer accin,@Param("accout") Integer accout); }
LogMapper.java
<!-- 当多参数时，不需要写 parameterType-->
<select id="selByAccInAccOut" resultType="Log" >
select \* from log where accin=#{accin} and accout=#{accout}
</select> 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
// 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表
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);
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
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
long selCountByPageInfo(PageInfo pi); }
StudentMapper.java
package com.mybatis.mapper;
import com.mybatis.po.Teachar;
public interface TeacharMapper {
Teachar selById(int id);
}
TeacharMapper.java
<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> StudentMapper.xml
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"%>学生姓名： 老师姓名：
| 学生编号 | 学生姓名 | 年龄 | 任课老师 |
| ${stu.id } | ${stu.name } | ${stu.age } | ${stu.teachar.name } |
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
| 飞机编号 | 起飞机场 | 起飞城市 | 降落机场 | 降落城市 | 航行时间 | 票价(元) | |
| ${plane.airNo } | ${plane.takePort.portName} | ${plane.takePort.cityName } | ${plane.landPort.portName } | ${plane.landPort.cityName } | <%--
| --%>
|
${plane.price } |
运行结果如下
航行时间，后因测试有修改，故实际效果不一致
补充:
在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}.
手机扫一扫
移动阅读更方便
你可能感兴趣的文章