基于sql动态代理的单表增删改查
搭建mybatis运行环境
注意:mapper扫描是包进行扫描
<!--基于sql动态代理的配置方式 -->
<mappers>
<!--mapper包扫描 -->
<package name="com.bjsxt.mapper"/>
</mappers>
创建mapper包
创建并配置mapper.xml文件
<!--根据产地和价格获取花卉信息 -->
<select id="selF" resultType="flower">
select * from flower where production=#{0} and price>#{1}
</select>
注意:不用书写parameterType了
创建和mapper.xml文件同名的接口
在接口中声明和mapper.xml文件中标签ID相同的方法
public interface FlowerMapper {
List<Flower> selF(String production,double price);
}
注意:
如果接口方法的形参类型全部为基本数据类型,则在mapper对应的标签中使用#{角标}或者#{param参数位置}进行参数占位即可。
如果接口的方法参数类型为实体类类型或者map类型,则在mapper对应的标签中使用#{键名}进行参数占位
如果参数为基本数据类型,但是也想使用#{键名}方式,在接口方法的形参声明中使用@Param(“别名”)方式实现
示例:
int insF(@Param("name")String name,@Param("price")double price,@Param("production")String production);
insert into flower values(default,#{name},#{price},#{production})
创建SqlSession对象并获取接口的实例化对象
//获取SqlSession对象
InputStream is = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
SqlSession ss=factory.openSession();
//基于Sql动态代理方式操作数据库
//获取接口的实例化对象
FlowerMapper fm=ss.getMapper(FlowerMapper.class);
使用对象完成对数据库的操作
List<Flower> lf=fm.selF("商丘",2.1);
mybatis的SQL语句的动态拼接:
标签类型:
if
where
choose
when
otherwise
set
trim
bind
foreach
include
sql
-----------------------------------------------------------------------------------
<?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.bjsxt.mapper.FlowerMapper2">
<!--
根据花名和产地查询花卉信息
如果没有花名和产地参数则查询所有
如果有其中任一参数,则使用参数进行结果筛选。
-->
<!--
if标签:
test属性中书写判断条件,但是需要使用param参数位置进行参数值判断,不能使用#{角标}方式
用来进行判断
-->
<select id="selF" resultType="flower">
select * from flower where 1=1
<if test="param1!=''">
and name=#{param1}
</if>
<if test="param2!=''">
and production=#{1}
</if>
</select>
<!--
where标签:
自动添加where关键字,并去除第一个and关键字
-->
<select id="selWhere" resultType="flower">
select * from flower
<where>
<if test="param1!=''">
and name=#{param1}
</if>
<if test="param2!=''">
and production=#{1}
</if>
</where>
</select>
<!--
choose标签:
when
tes属性:书写判断条件,使用param参数位置进行判断
otherwise
如果when都不成立则执行otherwise
注意:
-->
<select id="selChoose" resultType="flower">
select * from flower
<where>
<choose>
<when test="param1!=''">
and name=#{param1}
</when>
<when test="param2!=''">
and production=#{1}
</when>
<otherwise>
and price>2
</otherwise>
</choose>
</where>
</select>
<!--
set标签:
自动创建set关键字。
自动去除最后一个逗号。
-->
<update id="upF">
update flower
<set>
id=#{param3},
<if test="param1!=''">
name=#{param1},
</if>
<if test="param2!=''">
production=#{param2},
</if>
</set>
where id=#{param3};
</update>
<!--
trim标签:
prefix:在内容之前添加指定的内容
prefixOverrides:去除内容之前的指定内容
suffix:在内容之后添加指定的内容
suffixOverrides:去除内容之后的指定内容
注意:
先去后加
-->
<update id="upTrim">
update flower
<trim prefix="set" suffixOverrides=",">
id=#{param3},
<if test="param1!=''">
name=#{param1},
</if>
<if test="param2!=''">
production=#{param2},
</if>
</trim>
where id=#{param3};
</update>
<!--
bind标签:
对实参数据在真正赋值给SQL语句之前重新进行赋值,主要结合like语句使用
-->
<select id="selBind" resultType="flower">
select * from flower
<where>
<if test="param1!=''">
<bind name="na" value="'%'+param1+'%'"/>
and name like #{na}
</if>
<if test="param2!=''">
<bind name="pd" value="'%'+param2+'%'"/>
and production like #{pd}
</if>
</where>
</select>
<!--
foreach标签:
主要结合in sql关键字使用
collection:要遍历的类型,一般就是list
open:遍历的内容之前添加的内容
close:遍历的内容之后添加的内容
separator:遍历内容间的间隔符号
item:记录每次循环的遍历结果,#{变量名}获取
-->
<select id="selForeach" resultType="flower">
select *from flower where id in
<foreach collection="list" open="(" close=")" separator="," item="i">
#{i}
</foreach>
</select>
<!--
include标签
Sql标签:
-->
<select id="selIn" resultType="flower">
select <include refid="ss"/> from flower f
</select>
<sql id="ss">
f.id,f.name,f.price
</sql>
</mapper>
SQL动态代理练习代码:
需要导入mabatis相关的jar包,和mysql数据库里的表
com.bjsxt.mapper:
FlowerMapper.java:
package com.bjsxt.mapper;
import java.util.List;
import com.bjsxt.pojo.Flower;
public interface FlowerMapper {
//查询所有的花
List<Flower> sel();
//根据产地和价格获取
List<Flower> selF(String production,double price);
//插入一条数据
int ins(Flower f);
//修改一条数据
int up(String name,int id);
//删除一条数据
int del(int id);
}
FlowerMapper.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.bjsxt.mapper.FlowerMapper">
<!--获取全部信息 -->
<select id="sel" resultType="flower">
select * from flower
</select>
<!--根据产地和价格获取 -->
<select id="selF" resultType="flower">
select * from flower where production=#{0} and price>#{1}
</select>
<!--增加一条数据 -->
<insert id="ins">
insert into flower values(default,#{name},#{price},#{production})
</insert>
<!--修改一条数据 -->
<update id="up">
update flower set name=#{0} where id=#{1}
</update>
<!--删除一条数据 -->
<delete id="del">
delete from flower where id=#{id}
</delete>
</mapper>
com.bjsxt.pojo:
Flower.java:
package com.bjsxt.pojo;
public class Flower {
private int id;
private String name;
private double price;
private String production;
public Flower() {
super();
// TODO Auto-generated constructor stub
}
public Flower(int id, String name, double price, String production) {
super();
this.id = id;
this.name = name;
this.price = price;
this.production = production;
}
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 double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getProduction() {
return production;
}
public void setProduction(String production) {
this.production = production;
}
@Override
public String toString() {
return "Flower [id=" + id + ", name=" + name + ", price=" + price + ", production=" + production + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
long temp;
temp = Double.doubleToLongBits(price);
result = prime * result + (int) (temp ^ (temp >>> 32));
result = prime * result + ((production == null) ? 0 : production.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Flower other = (Flower) obj;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
if (Double.doubleToLongBits(price) != Double.doubleToLongBits(other.price))
return false;
if (production == null) {
if (other.production != null)
return false;
} else if (!production.equals(other.production))
return false;
return true;
}
}
com.bjsxt.test:
TestDemo.java:
package com.bjsxt.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.bjsxt.mapper.FlowerMapper;
import com.bjsxt.pojo.Flower;
public class TestDemo {
public static void main(String[] args) throws IOException {
//获取SqlSession对象
InputStream is = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory= new SqlSessionFactoryBuilder().build(is);
SqlSession ss=factory.openSession();
//使用动态代理方式
//获取接口实例化对象
FlowerMapper fm = ss.getMapper(FlowerMapper.class);
List<Flower> lf = fm.sel();
System.out.println(lf);
List<Flower> lf2=fm.selF("巴西", 2.0);
System.out.println(lf2);
//插入一条数据
Flower f=new Flower(0, "玫瑰花", 10.0, "北京");
int i = fm.ins(f);
System.out.println(i);
//修改一条数据
int i2=fm.up("鲜花", 5);
System.out.println(i2);
//删除一条数据
int i3=fm.del(5);
System.out.println(i3);
ss.commit();
}
}
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>
<typeAliases>
<package name="com.bjsxt.pojo"/>
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!-- 扫描mapper包 -->
<mappers>
<package name="com.bjsxt.mapper"/>
</mappers>
</configuration>
小案例
需求分析:
实现:
数据库:
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`phone` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
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>
<typeAliases>
<package name="com.bjsxt.pojo"/>
</typeAliases>
<environments default="mysql">
<environment id="mysql">
<transactionManager type="JDBC">
</transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.bjsxt.mapper"/>
</mappers>
</configuration>
com.bjsxt.mapper:
UserMapper.java:
package com.bjsxt.mapper;
import java.io.IOException;
import java.util.List;
import com.bjsxt.pojo.User;
public interface UserMapper {
//根据条件动态查询结果
List<User> sel(String name,String phone) throws IOException;
}
UserMapper.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.bjsxt.mapper.UserMapper">
<select id="sel" resultType="user">
select * from user
<where>
<if test="param1!=''">
and name=#{param1}
</if>
<if test="param2!=''">
and phone=#{param2}
</if>
</where>
</select>
</mapper>
com.bjsxt.pojo:
User.java:
package com.bjsxt.pojo;
import java.util.List;
public class User {
private int id;
private String name;
private String phone;
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(int id, String name, String phone) {
super();
this.id = id;
this.name = name;
this.phone = phone;
}
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 String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", phone=" + phone + "]";
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
result = prime * result + ((phone == null) ? 0 : phone.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
User other = (User) obj;
if (id != other.id)
return false;
if (name == null) {
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
if (phone == null) {
if (other.phone != null)
return false;
} else if (!phone.equals(other.phone))
return false;
return true;
}
}
com.bjsxt.service:
UserService.java:
package com.bjsxt.service;
import java.io.IOException;
import java.util.List;
import com.bjsxt.pojo.User;
public interface UserService {
//根据条件动态查询结果
List<User> selService(String name,String phone) throws IOException;
}
com.bjsxt.serviceImpl:
UserServiceImpl.java:
package com.bjsxt.serviceImpl;
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.bjsxt.mapper.UserMapper;
import com.bjsxt.pojo.User;
import com.bjsxt.service.UserService;
public class UserServiceImpl implements UserService{
@Override
public List<User> selService(String name, String phone) throws IOException {
//创建SqlSession对象
InputStream is=Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
SqlSession ss=factory.openSession();
//获取接口的实例化对象
UserMapper um=ss.getMapper(UserMapper.class);
List<User> lu=um.sel(name, phone);
return lu;
}
}
com.bjsxt.servlet:
UserServlet.java:
package com.bjsxt.servlet;
import java.io.IOException;
import java.util.List;
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.bjsxt.pojo.User;
import com.bjsxt.service.UserService;
import com.bjsxt.serviceImpl.UserServiceImpl;
import com.google.gson.Gson;
/**
* Servlet implementation class UserServlet
*/
@WebServlet("/user")
public class UserServlet extends HttpServlet {
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//设置请求编码格式
req.setCharacterEncoding("utf-8");
//设置响应编码格式
resp.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
//获取请求信息
String name=req.getParameter("name");
String phone=req.getParameter("phone");
//处理请求信息
UserService us=new UserServiceImpl();
List<User> lu=us.selService(name, phone);
//响应处理结果
resp.getWriter().write(new Gson().toJson(lu));
}
}
webContext:
js文件夹有 j.js(也就是js.1.9版本)
show.jsp:
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
<script type="text/javascript" src="js/j.js"></script>
<script type="text/javascript">
$(function(){
$("#btn").click(function(){
var name=$("#name").val();
var phone=$("#phone").val();
$.get("user",{name:name,phone:phone},function(data){
eval("var obj="+data);
var ta=$("#t1");
ta.empty();
ta.append("<tr height='40px'>"+
"<td width='200px'>编号</td>"+
"<td width='200px'>姓名</td>"+
"<td width='200px'>手机号</td>"+
"</tr>");
for(var i=0;i<=obj.length;i++){
ta.append("<tr height='40px'>"+
"<td width='200px'>"+obj[i].id+"</td>"+
"<td width='200px'>"+obj[i].name+"</td>"+
"<td width='200px'>"+obj[i].phone+"</td>"+
"</tr>");
}
})
})
})
</script>
</head>
<body>
姓名:<input type="text" id="name" name="name" value=""/> 电话 <input type="text" id="phone" name="phone" value=""/>
<input id="btn" type="button" value="查询" />
<table id="t1" cellpadding="0" cellspacing="0" border="solid:1px">
</table>
</body>
</html>
小结:
基于sql动态代理的单表增删改查
mybatis的SQL语句的动态拼接
练习
小案例