1、Mybatis简介
* MyBatis本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation迁移到了google code,并且改名为MyBatis。
* MyBatis是一个优秀的持久层框架,使用简单的XML或注解用于配置和原始映射,将接口和Java的POJOs(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录。
注:MyBatis和Ibatis是同一个框架。
正在上传…重新上传取消
正在上传…重新上传取消
正在上传…重新上传取消
正在上传…重新上传取消
* MyBatis是一个优秀的持久层框架,使用简单的XML或注解用于配置和原始映射,将接口和Java的POJOs(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录。
注:MyBatis和Ibatis是同一个框架。
MyBatis把sql语句交给xml进行管理。
2、一个简单的mybatis案例
①导入mybatis相关jar包
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
②写一个sqlMapConfig.xml
提供mybatis的核心配置文件sqlMapConfig.xml,并放在项目的src目录下。此文件中配置了数据库的基本连接参数和sql映射文件等相关信息。我们可以从mybatis安装包中提供的官方文档(mybatis-3.1.1.pdf)中获得此文件的模板。
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
sqlMapConfig.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>
<environments default ="development" >
<environment id ="development" >
<transactionManager type ="JDBC" />
<dataSource type ="POOLED" >
<property name ="driver" value="com.mysql.jdbc.Driver" />
<property name ="url" value="jdbc:mysql:///mybatis" />
<property name ="username" value="root" />
<property name ="password" value="sorry" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource ="cn/itcast/mybatis/domain/User.xml" />
</mappers>
</configuration>
③创建数据库Mybatis和数据表user
创建与数据表user对应的java实体类User.java
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
User.java
public class User {
private String id ;
private String name ;
private Integer age ;
private String address ;
public String getId() {
return id ;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name ;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age ;
}
public void setAge(Integer age) {
this.age = age;
}
public String getAddress() {
return address ;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [address=" + address + ",name=" + name + ",age=" + age + ",address" + address + "]" ;
}
}
④创建映射文件
提供User实体的sql映射文件User.xml,并和User实体放在同一个包中,此文件的模板同样可以从mybatis的官方文档(mybatis-3.1.1.pdf)中获得。
正在上传…重新上传取消
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
user.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 ="cn.itcast.mybatis.domain.User" >
<!--id:sql的唯一标识,只要在本xml中不重名即可,parameterType:参数类型 resultType:结果类型-->
<select id ="selectUserById" parameterType="string" resultType="cn.itcast.mybatis.domain.User" >
select * from user where id = #{id}
</select>
</mapper>
⑤执行代码
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
MybatisTest.java
public class MybatisTest {
//根据id查询user
@Test
public void test1() throws IOException{
String resource = "sqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
//参数1:命名空间+sqlId类唯一锁定要执行的sql
//参数2:sql语句所需要的参数
User user = (User)session.selectOne( "cn.itcast.mybatis.domain.User.selectUserById" ,"1" );
System. out.println(user);
session.close();
}
}
⑥执行结果:
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
(注:mybatis在生成实体对象的时候实际上调用了set方法。)
3、显示执行的sql语句
为了更加直观的看到mybatis执行的sql语句,可以在项目的src目录下加入log4j.properties文件,将程序执行的sql输出到控制台。
02 模拟框架将查询到的数据通过反射包装成指定对象
1、代码
public class MybatisTest {
@Test
public void test2() throws Exception {
String className = "cn.itcast.mybatis.domain.User" ;
Object object = Class. forName(className).newInstance();
Class. forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mybatis", "root","sorry" );
String sql = "select * from user where id = ?" ;
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "1");
ResultSet rs = ps.executeQuery();
while(rs.next()){
ResultSetMetaData data = rs.getMetaData();
int count = data.getColumnCount();
for(int i = 1; i <= count; i++){
String columnName = data.getColumnName(i);
String setMethodName = buildSetMethod(columnName);
String typeName = data.getColumnTypeName(i);
if(typeName.equals("VARCHAR" )){
String value = rs.getString(columnName);
Method method = object.getClass().getMethod(setMethodName, String.class);
method.invoke(object, value);
} else if (typeName.equals("INT")){
int value = rs.getInt(columnName);
Method method = object.getClass().getMethod(setMethodName, Integer.class);
method.invoke(object, value);
}
}
User user = (User)object;
System. out.println(user);
}
}
public String buildSetMethod(String cName){
String c1 = "set";
String c2 = cName.substring(0,1).toUpperCase();
String c3 = cName.substring(1);
return c1 + c2 + c3;
}
}
2、执行结果
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
03 Mybatis基础代码
sqlMapConfig.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>
<typeAlias type ="cn.itcast.mybatis.domain.User" alias="User" />
</typeAliases>
<environments default ="development" >
<environment id ="development" >
<transactionManager type ="JDBC" />
<dataSource type ="POOLED" >
<property name ="driver" value="com.mysql.jdbc.Driver" />
<property name ="url" value="jdbc:mysql:///mybatis" />
<property name ="username" value="root" />
<property name ="password" value="sorry" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource ="cn/itcast/mybatis/domain/User.xml" />
</mappers>
</configuration>
User.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 ="cn.itcast.mybatis.domain.User" >
<!--通过resultMap可以解决字段名和属性名冲突的情况-->
<resultMap type ="User" id= "userMap">
<id property ="id" column="id" />
<result property ="name" column="name" />
<result property ="age" column="age" />
<result property ="address" column="address" />
</resultMap>
<!-- 当字段很多的时候,可以通过下面这种方式抽取字段 -->
<sql id ="allColumns" >
id,name,age,address
</sql>
<!-- 如果在sqlMapConfig.xml中配置了别名,那么这里的resultType就可以直接写User -->
<select id ="selectUserById" parameterType="string" resultMap="userMap" >
select
<include refid ="allColumns" />
from user where id = #{id}
</select>
<!-- bean的属性名和数据库中的字段名不一致,那么将会出现取出来的属性值为null情况,这时候可以通过取别名的方式解决 -->
<select id ="selectUserById2" parameterType="string" resultType="User" >
select id,name as userName,age,address from user where id = #{id}
</select>
<!-- 查询所有的用户 -->
<select id ="selectAllUser" resultType="cn.itcast.mybatis.domain.User" >
select <include refid ="allColumns" /> from user
</select>
<!-- 根据用户的id删除用户 -->
<delete id ="deleteUserById" parameterType="string" >
delete from user where id = #{id}
</delete>
<!-- 插入一条用户数据 -->
<insert id ="insertUser" parameterType="cn.itcast.mybatis.domain.User" >
insert into user(id,name,age,address) values(#{id},#{name},#{age},#{address})
</insert>
<!-- 根据id修改数据 -->
<insert id ="updateUserById" parameterType="cn.itcast.mybatis.domain.User" >
update user set name=#{name},age=#{age},address=#{address} where id=#{id}
</insert>
<!-- 利用动态sql语句更新user数据 -->
<insert id ="updateUserByCondition" parameterType="cn.itcast.mybatis.domain.User" >
update user
<set>
<if test ="name!=null" >
name = #{name}
</if>
<if test ="age!=null" >
age = #{age}
</if>
<if test ="address!=null" >
address = #{address}
</if>
</set>
where id=#{id}
</insert>
<!-- 动态seql语句查询用户 -->
<select id ="selectUserByCondition" parameterType="cn.itcast.mybatis.domain.User" resultType="cn.itcast.mybatis.domain.User" >
select * from user where 1 = 1
<if test ="id!=null" >
and id = #{id}
</if>
<if test ="name!=null" >
and name = #{name}
</if>
<if test ="age!=null" >
and age = #{age}
</if>
<if test ="address!=null" >
and address = #{address}
</if>
</select>
<!-- 动态seql语句查询用户方式2 -->
<!-- 这种方式会自动根据是否有id,如果有没有id,那么第一个条件将不加and -->
<select id ="selectUserByCondition2" parameterType="cn.itcast.mybatis.domain.User" resultType="cn.itcast.mybatis.domain.User" >
select * from user
<where>
<if test ="id!=null" >
id = #{id}
</if>
<if test ="name!=null" >
and name = #{name}
</if>
<if test ="age!=null" >
and age = #{age}
</if>
<if test ="address!=null" >
and address = #{address}
</if>
</where>
</select>
<!-- 利用hashmap传递参数数据查询用户 -->
<select id ="selectUserById4Map" parameterType="string" resultType="hashmap" >
select
<include refid ="allColumns" />
from user where id = #{id}
</select>
<!-- 利用hashmap传递参数数据插入用户信息 -->
<select id ="insertUser4Map" parameterType="hashmap" >
insert into user(id,name,age,address) values(#{id},#{name},#{age},#{address})
</select>
<!-- 模糊查询 -->
<select id ="selectUserByCondition3" parameterType="cn.itcast.mybatis.domain.User" resultType="cn.itcast.mybatis.domain.User" >
select * from user where 1 = 1
<if test ="id!=null" >
and id = #{id}
</if>
<if test ="name!=null" >
and name like '%${name}%'
</if>
<if test ="age!=null" >
and age = #{age}
</if>
<if test ="address!=null" >
and address = #{address}
</if>
</select>
</mapper>
MybatisTest.java
package cn.itcast.mybatis.test;
import cn.itcast.mybatis.domain.User;
public class MybatisTest {
private SqlSessionFactory sqlSessionFactory = null;
@Before
public void init() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
//根据id查询user
@Test
public void test1() throws IOException{
SqlSession session = sqlSessionFactory.openSession();
//参数1:命名空间+sqlId类唯一锁定要执行的sql
//参数2:sql语句所需要的参数
User user = (User)session.selectOne("cn.itcast.mybatis.domain.User.selectUserById" ,"1" );
System. out.println(user);
session.close();
}
//根据所有的user
@Test
public void test3() throws IOException{
SqlSession session = sqlSessionFactory.openSession();
List<User> users = session.selectList("cn.itcast.mybatis.domain.User.selectAllUser" );
for(User user : users){
System. out.println(user);
}
session.close();
}
//根据用户id删除某个用户
@Test
public void test4() throws IOException{
SqlSession session = sqlSessionFactory.openSession();
int count = session.delete("cn.itcast.mybatis.domain.User.deleteUserById" ,"3" );
System. out.println(count);
//这里一定要注意提交事务,在执行delete操作的时候已经将setAutoCommit设置为false了
session.commit();
session.close();
}
//插入一条用户信息
@Test
public void test5() throws IOException{
User user = new User();
user.setId( "3");
user.setName( "wangwu");
user.setAge(22);
user.setAddress( "tj");
SqlSession session = sqlSessionFactory.openSession();
session.delete( "cn.itcast.mybatis.domain.User.insertUser" ,user);
session.commit();
session.close();
}
//根据id修改用户信息
@Test
public void test6() throws IOException{
User user = new User();
user.setId( "3");
user.setName( "wangwu2");
//如果下面这两条代码不写,那么数据库中的用户的age和address属性将会被设置为null,在mybatis中采用动态sql的方式实现动态拼凑sql语句
//user.setAge(24);
//user.setAddress("tj2");
SqlSession session = sqlSessionFactory.openSession();
session.delete( "cn.itcast.mybatis.domain.User.updateUserById" ,user);
session.commit();
session.close();
}
//动态sql语句更新用户的信息
//这时候一定要注意sql语句可能出现任何信息都不更新的情况,这个时候就应该在页面就拦截住这种查询
@Test
public void test7() throws IOException{
User user = new User();
user.setId( "3");
user.setName( "wangwu3");
SqlSession session = sqlSessionFactory.openSession();
session.delete( "cn.itcast.mybatis.domain.User.updateUserByCondition" ,user);
session.commit();
session.close();
}
//动态sql语句查询所有的用户
@Test
public void test8() throws IOException{
User user = new User();
// user.setId("1");
//不传任何条件,就相当于查询出所有的用户数据
SqlSession session = sqlSessionFactory.openSession();
List<User> users = session.selectList("cn.itcast.mybatis.domain.User.selectUserByCondition2" ,user);
for(User user2 : users){
System. out.println(user2);
}
session.commit();
session.close();
}
//利用hashmap传递参数查询用户信息
@Test
public void test9() throws IOException{
SqlSession session = sqlSessionFactory.openSession();
Map<String,Object> map = (Map)session.selectOne("cn.itcast.mybatis.domain.User.selectUserById4Map" ,"1" );
System. out.println(map);
session.close();
}
//利用hashmap传递参数插入用户信息
@Test
public void test10() throws IOException{
SqlSession session = sqlSessionFactory.openSession();
Map map = new HashMap();
map.put( "id", "4" );
map.put( "name", "zl" );
map.put( "age", 28);
map.put( "address", "bz" );
session.insert( "cn.itcast.mybatis.domain.User.insertUser4Map" ,map);
session.commit();
session.close();
}
//模糊查询
@Test
public void test11() throws IOException{
SqlSession session = sqlSessionFactory.openSession();
User user = new User();
user.setName( "wang");
User user2 = session.selectOne("cn.itcast.mybatis.domain.User.selectUserByCondition3" ,user);
System. out.println(user2);
session.close();
}
}
04 Mybatis中的关联映射
1、建表、插入数据
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
2、代码
Customer.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 ="cn.itcast.mybatis.domain.Customer" >
<resultMap type ="Customer" id= "customerMap">
<id property ="id" column="id" />
<result property ="name" column="name" />
<result property ="age" column="age" />
<result property ="address" column="address" />
<collection property ="orders" ofType="Order" >
<id property ="orderId" column="oid" />
<result property ="orderPrice" column="price" />
<result property ="orderNumber" column="orderNumber" />
</collection>
</resultMap>
<select id ="selectCustomerById" parameterType="string" resultMap="customerMap" >
select c.id,c.name,c.age,c.address,o.id oid,o.price,o.orderNumber,o.cid from customer c,orders o where c.id=o.cid and c.id=#{id}
</select>
<!-- 批量插入数据 -->
<insert id ="insertBatch" parameterType="list" >
insert into customer(id,name,age,address) values
<foreach collection ="list" item="obj" separator="," >
(#{obj.id},#{obj.name},#{obj.age},#{obj.address})
</foreach>
</insert>
</mapper>
Order.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 ="cn.itcast.mybatis.domain.Order" >
<!-- 可以写多个resultMap,根据查询的需求而定 -->
<resultMap type ="Order" id= "orderMap2">
<id property ="orderId" column="oid" />
<result property ="orderPrice" column="price" />
<result property ="orderNumber" column="orderNumber" />
</resultMap>
<resultMap type ="Order" id= "orderMap">
<id property ="orderId" column="oid" />
<result property ="orderPrice" column="price" />
<result property ="orderNumber" column="orderNumber" />
<association property ="cus" javaType="Customer" >
<id property ="id" column="id" />
<result property ="name" column="name" />
<result property ="age" column="age" />
<result property ="address" column="address" />
</association>
</resultMap>
<select id ="selectOrderById" parameterType="string" resultMap="orderMap" >
select c.id,c.name,c.age,c.address,o.id oid,o.price,o.orderNumber,o.cid from customer c,orders o where c.id=o.cid and o.id=#{oid}
</select>
</mapper>
sqlMapConfig.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>
<typeAlias type ="cn.itcast.mybatis.domain.User" alias="User" />
<typeAlias type ="cn.itcast.mybatis.domain.Customer" alias="Customer" />
<typeAlias type ="cn.itcast.mybatis.domain.Order" alias="Order" />
</typeAliases>
<environments default ="development" >
<environment id ="development" >
<transactionManager type ="JDBC" />
<dataSource type ="POOLED" >
<property name ="driver" value="com.mysql.jdbc.Driver" />
<property name ="url" value="jdbc:mysql:///mybatis" />
<property name ="username" value="root" />
<property name ="password" value="sorry" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource ="cn/itcast/mybatis/domain/User.xml" />
<mapper resource ="cn/itcast/mybatis/domain/Customer.xml" />
<mapper resource ="cn/itcast/mybatis/domain/Order.xml" />
</mappers>
</configuration>
Customer.xml
public class Customer {
private String id ;
private String name ;
private Integer age ;
private String address ;
Set<Order> orders = new HashSet<Order>();
public String getId() {
return id ;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name ;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age ;
}
public void setAge(Integer age) {
this.age = age;
}
public String getAddress() {
return address ;
}
public void setAddress(String address) {
this.address = address;
}
public Set<Order> getOrderss() {
return orders ;
}
public void setOrderss(Set<Order> orders) {
this.orders = orders;
}
public String toString(){
return "Customer[address=" + address + ",age=" + age + ",id=" + id + ",name=" + name + ",orders=" + orders + "]";
}
}
Order.xml
public class Order {
private String orderId ;
private String orderNumber ;
private Integer orderPrice ;
private Customer cus = new Customer();
public String getOrderId() {
return orderId ;
}
public void setOrderId(String orderId) {
this.orderId = orderId;
}
public String getOrderNumber() {
return orderNumber ;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
public Integer getOrderPrice() {
return orderPrice ;
}
public void setOrderPrice(Integer orderPrice) {
this.orderPrice = orderPrice;
}
public Customer getCus() {
return cus ;
}
public void setCus(Customer cus) {
this.cus = cus;
}
}
MybatisTest2.java
public class MybatisTest2 {
private SqlSessionFactory sqlSessionFactory = null;
@Before
public void init() throws IOException {
String resource = "sqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void test1() throws IOException{
SqlSession session = sqlSessionFactory .openSession();
Customer cus = (Customer)session.selectOne("cn.itcast.mybatis.domain.Customer.selectCustomerById" ,"1" );
System. out.println(cus);
session.close();
}
@Test
public void test2() throws IOException{
SqlSession session = sqlSessionFactory .openSession();
Order order = session.selectOne("cn.itcast.mybatis.domain.Order.selectOrderById" ,"001" );
System. out.println(order);
session.close();
}
@Test
public void test3() throws IOException{
SqlSession session = sqlSessionFactory .openSession();
List<Customer> customers = new ArrayList<Customer>();
for(int i = 0; i < 10; i++){
Customer customer = new Customer();
customer.setId(UUID. randomUUID().toString());
customer.setName( "customer" + i);
customer.setAge(i);
customer.setAddress( "address" + i);
customers.add(customer);
}
session.insert( "cn.itcast.mybatis.domain.Customer.insertBatch" ,customers);
session.commit();
session.close();
}
}
05 Spring与Mybatis的整合
1、导入jar包
正在上传…重新上传取消
正在上传…重新上传取消
正在上传…重新上传取消
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
2、代码
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
User.java
public class User {
private String id ;
private String name ;
private Integer age ;
private String address ;
public String getId() {
return id ;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name ;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age ;
}
public void setAge(Integer age) {
this.age = age;
}
public String getAddress() {
return address ;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [address=" + address + ",name=" + name + ",age=" + age + ",address" + address + "]" ;
}
}
User.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 ="cn.itcast.mybatis.domain.User" >
<resultMap type ="User" id= "userMap">
<id property ="id" column="id" />
<result property ="name" column="name" />
<result property ="age" column="age" />
<result property ="address" column="address" />
</resultMap>
<!-- 当字段很多的时候,可以通过下面这种方式抽取字段 -->
<sql id ="allColumns" >
id,name,age,address
</sql>
<!-- 如果在sqlMapConfig.xml中配置了别名,那么这里的resultType就可以直接写User -->
<select id ="selectUserById" parameterType="string" resultMap="userMap" >
select
<include refid ="allColumns" />
from user where id = #{id}
</select>
<!-- bean的属性名和数据库中的字段名不一致,那么将会出现取出来的属性值为null情况,这时候可以通过取别名的方式解决 -->
<select id ="selectUserById2" parameterType="string" resultType="cn.itcast.mybatis.domain.User" >
select id,name as userName,age,address from user where id = #{id}
</select>
<!-- 查询所有的用户 -->
<select id ="selectAllUser" resultType="cn.itcast.mybatis.domain.User" >
select <include refid ="allColumns" /> from user
</select>
<!-- 根据用户的id删除用户 -->
<delete id ="deleteUserById" parameterType="string" >
delete from user where id = #{id}
</delete>
<!-- 插入一条用户数据 -->
<insert id ="insertUser" parameterType="cn.itcast.mybatis.domain.User" >
insert into user(id,name,age,address) values(#{id},#{name},#{age},#{address})
</insert>
<!-- 根据id修改数据 -->
<insert id ="updateUserById" parameterType="cn.itcast.mybatis.domain.User" >
update user set name=#{name},age=#{age},address=#{address} where id=#{id}
</insert>
<!-- 利用动态sql语句更新user数据 -->
<insert id ="updateUserByCondition" parameterType="cn.itcast.mybatis.domain.User" >
update user
<set>
<if test ="name!=null" >
name = #{name}
</if>
<if test ="age!=null" >
age = #{age}
</if>
<if test ="address!=null" >
address = #{address}
</if>
</set>
where id=#{id}
</insert>
<!-- 动态seql语句查询用户 -->
<select id ="selectUserByCondition" parameterType="cn.itcast.mybatis.domain.User" resultType="cn.itcast.mybatis.domain.User" >
select * from user where 1 = 1
<if test ="id!=null" >
and id = #{id}
</if>
<if test ="name!=null" >
and name = #{name}
</if>
<if test ="age!=null" >
and age = #{age}
</if>
<if test ="address!=null" >
and address = #{address}
</if>
</select>
<!-- 动态seql语句查询用户方式2 -->
<!-- 这种方式会自动根据是否有id,如果有没有id,那么第一个条件将不加and -->
<select id ="selectUserByCondition2" parameterType="cn.itcast.mybatis.domain.User" resultType="cn.itcast.mybatis.domain.User" >
select * from user
<where>
<if test ="id!=null" >
id = #{id}
</if>
<if test ="name!=null" >
and name = #{name}
</if>
<if test ="age!=null" >
and age = #{age}
</if>
<if test ="address!=null" >
and address = #{address}
</if>
</where>
</select>
<!-- 利用hashmap传递参数数据查询用户 -->
<select id ="selectUserById4Map" parameterType="string" resultType="hashmap" >
select
<include refid ="allColumns" />
from user where id = #{id}
</select>
<!-- 利用hashmap传递参数数据插入用户信息 -->
<select id ="insertUser4Map" parameterType="hashmap" >
insert into user(id,name,age,address) values(#{id},#{name},#{age},#{address})
</select>
<!-- 模糊查询 -->
<select id ="selectUserByCondition3" parameterType="cn.itcast.mybatis.domain.User" resultType="cn.itcast.mybatis.domain.User" >
select * from user where 1 = 1
<if test ="id!=null" >
and id = #{id}
</if>
<if test ="name!=null" >
and name like '%${name}%'
</if>
<if test ="age!=null" >
and age = #{age}
</if>
<if test ="address!=null" >
and address = #{address}
</if>
</select>
</mapper>
IUserDao.xml
public interface IUserDao {
public User findUserById(String id);
public int insertUser(User user);
}
UserDaoImpl.java
public class UserDaoImpl extends SqlSessionDaoSupport implements IUserDao {
// private SqlSessionFactory sf;
//
// public SqlSessionFactory getSf() {
// return sf;
// }
//
// public void setSf(SqlSessionFactory sf) {
// this.sf = sf;
// }
public User findUserById(String id) {
// SqlSession session = sf.openSession();
SqlSession session = this.getSqlSession();
User user = (User)session.selectOne("cn.itcast.mybatis.domain.User.selectUserById" ,id);
// session.close();
return user;
}
public int insertUser(User user) {
// SqlSession session = sf.openSession();
SqlSession session = this.getSqlSession();
int i = session.insert("cn.itcast.mybatis.domain.User.insertUser" ,user);
// int c = 1/0;
// session.close();
return i;
}
}
IUserService.java
public interface IUserService {
public User findUserById(String id);
public int insertUser(User user);
}
UserServiceImpl.java
public class UserServiceImpl implements IUserService {
private IUserDao userDao ;
public IUserDao getUserDao() {
return userDao ;
}
public void setUserDao(IUserDao userDao) {
this.userDao = userDao;
}
public User findUserById(String id) {
return userDao .findUserById(id);
}
public int insertUser(User user) {
return userDao .insertUser(user);
}
}
sqlMapConfig.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>
<typeAlias type ="cn.itcast.mybatis.domain.User" alias="User" />
</typeAliases>
<!-- 由于spring中已经配置了数据源,所以这里不再需要重复配置
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql:///mybatis" />
<property name="username" value="root" />
<property name="password" value="sorry" />
</dataSource>
</environment>
</environments>
-->
<mappers>
<mapper resource ="cn/itcast/mybatis/domain/User.xml" />
</mappers>
</configuration>
beans.xml
<?xml version ="1.0" encoding="UTF-8" ?>
<beans xmlns ="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<!-- 配置数据源 -->
<bean id ="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
<property name ="driverClassName" value="com.mysql.jdbc.Driver" ></property>
<property name ="url" value="jdbc:mysql://localhost/mybatis" ></property>
<property name ="username" value="root" ></property>
<property name ="password" value="sorry" ></property>
</bean>
<bean id ="sf" class="org.mybatis.spring.SqlSessionFactoryBean" >
<property name ="dataSource" ref="dataSource" ></property>
<property name ="configLocation" value="sqlMapConfig.xml" ></property>
</bean>
<!--
<bean id="userDao" class="cn.itcast.mybatis.dao.impl.UserDaoImpl">
<property name="sf" ref="sf"></property>
</bean>
-->
<bean id ="userDao" class="cn.itcast.mybatis.dao.impl.UserDaoImpl" >
<property name ="sqlSessionFactory" ref="sf" ></property>
</bean>
<bean id ="userService" class="cn.itcast.mybatis.service.impl.UserServiceImpl" >
<property name ="userDao" ref="userDao" ></property>
</bean>
<!-- 事务管理器 -->
<bean id ="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" >
<property name ="dataSource" ref="dataSource" ></property>
</bean>
<tx:advice id ="mybatis_advice" transaction-manager="txManager" >
<tx:attributes>
<tx:method name ="insert*" propagation="REQUIRED" />
<tx:method name ="update*" propagation="REQUIRED" />
<tx:method name ="delete*" propagation="REQUIRED" />
<tx:method name ="find*" read-only="true" />
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut expression ="execution(* cn.itcast.mybatis.service.*.*(..))" id ="mybatis_pc" />
<aop:advisor advice-ref ="mybatis_advice" pointcut-ref="mybatis_pc" />
</aop:config>
</beans>
MybatisSpringTest.java
public class MybatisSpringTest {
@Test
public void test1(){
ApplicationContext ctx = new ClassPathXmlApplicationContext("beans.xml" );
IUserService s = (IUserService)ctx.getBean( "userService");
User user = s.findUserById( "1");
System. out.println(user);
}
@Test
public void test2(){
//利用下面这种方式,自动进行了事务提交,即使出现异常,也会插入成功,所以需要使用事务管理器进行管理
ApplicationContext ctx = new ClassPathXmlApplicationContext("beans.xml" );
IUserService s = (IUserService)ctx.getBean( "userService");
User user = new User();
user.setId( "121");
user.setName( "ddd");
user.setAge(30);
user.setAddress( "beijing");
int i = s.insertUser(user);
System. out.println(i);
}
}
06 SSI Struts+Spring+Ibatis整合
![uploading.4e448015.gif](https://i-blog.csdnimg.cn/blog_migrate/8d6f93dd9c49cf646dd2deaf19080974.gif)
User.java
public class User {
private String id ;
private String name ;
public String getName() {
return name ;
}
public void setName(String userName) {
this.name = userName;
}
private Integer age ;
private String address ;
public String getId() {
return id ;
}
public void setId(String id) {
this.id = id;
}
public Integer getAge() {
return age ;
}
public void setAge(Integer age) {
this.age = age;
}
public String getAddress() {
return address ;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [address=" + address + ", age=" + age + ", id=" + id
+ ", name=" + name + "]";
}
}
User.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 ="cn.itcast.mybatis.domain.User" >
<resultMap type ="User" id= "userMap">
<id property ="id" column="id" />
<result property ="name" column="name" />
<result property ="age" column="age" />
<result property ="address" column="address" />
</resultMap>
<sql id ="allColumns" >
id,name,age ,address
</sql>
<!-- id:sql的唯一标示 parameterType:参数类型 resultType:结果类型-->
<select id ="selectUserById" parameterType="string" resultMap="userMap" >
select
<include refid ="allColumns" />
from user where id = #{id}
</select>
<!-- id:sql的唯一标示 parameterType:参数类型 resultType:结果类型-->
<select id ="selectUserById4Map" parameterType="string" resultType="hashmap" >
select
<include refid ="allColumns" />
from user where id = #{id}
</select>
<!-- 查询所有User -->
<select id ="selectAllUser" resultType="User" >
select <include refid ="allColumns" /> from user
</select>
<!-- 根据id删除User -->
<delete id ="deleteUserById" parameterType="string" >
delete from user where id = #{id}
</delete>
<!-- 插入一条数据 -->
<insert id ="insertUser" parameterType="User" >
insert into user (id,name,age,address) values(#{id},#{name},#{age},#{address})
</insert>
<!-- 插入一条数据 -->
<insert id ="insertUser4Map" parameterType="hashmap" >
insert into user (id,name,age,address) values(#{userid},#{userName},#{age},#{address})
</insert>
<!-- 根据id修改数据 -->
<update id ="updateUserById" parameterType="User" >
update user set name=#{name},age = #{age} ,address = #{address} where id = #{id}
</update>
<!-- 动态sql:动态更新 -->
<update id ="updateUserByCondition" parameterType="User" >
update user
<set>
<if test ="name != null" >
name = #{name},
</if>
<if test ="age != null" >
age = #{age},
</if>
<if test ="address != null" >
address = #{address}
</if>
</set>
where id = #{id}
</update>
<!-- 动态sql:查询操作 -->
<select id ="selectUserByCondition" parameterType="User" resultType="User" >
select * from user where 1=1
<if test ="id != null" >
and id = #{id}
</if>
<if test ="name != null" >
and name like '${name}%'
</if>
<if test ="age != null" >
and age = #{age}
</if>
<if test ="address != null" >
and address = #{address}
</if>
</select>
<!-- 动态sql:查询操作 -->
<select id ="selectUserByCondition2" parameterType="User" resultType="User" >
SELECT * from user
<where>
<if test ="id != null" >
id = #{id}
</if>
<if test ="name != null" >
and name = #{name}
</if>
<if test ="age != null" >
and age = #{age}
</if>
<if test ="address != null" >
and address = #{address}
</if>
</where>
</select>
</mapper>
IUserDao.java
public interface IUserDao {
public User findUserById(String id);
public int insertUser(User u);
public List<User> findAllUsers();
public int deleteUserById(String id);
public int updateUserById(User u);
}
UserDaoImpl.java
public class UserDaoImpl extends SqlSessionDaoSupport implements IUserDao {
public User findUserById(String id) {
SqlSession session = this.getSqlSession();
User user = (User)session.selectOne("cn.itcast.mybatis.domain.User.selectUserById" , id);
//session.close();
return user;
}
public int insertUser(User u) {
SqlSession session = this.getSqlSession();
int i = session.insert("cn.itcast.mybatis.domain.User.insertUser" , u);
//session.close();
return i;
}
public int deleteUserById(String id) {
int i = this.getSqlSession().delete("cn.itcast.mybatis.domain.User.deleteUserById" , id);
return i;
}
public List<User> findAllUsers() {
return this.getSqlSession().selectList("cn.itcast.mybatis.domain.User.selectAllUser" );
}
public int updateUserById(User u) {
int i = this.getSqlSession().update("cn.itcast.mybatis.domain.User.updateUserByCondition" , u);
return i;
}
}
IUserService.java
public interface IUserService {
public User findUserById(String id);
public int insertUser(User u);
public List<User> findAllUsers();
public int deleteUserById(String id);
public int updateUserById(User u);
}
UserServiceImpl.java
public class UserServiceImpl implements IUserService {
private IUserDao userDao ;
public IUserDao getUserDao() {
return userDao ;
}
public void setUserDao(IUserDao userDao) {
this.userDao = userDao;
}
public User findUserById(String id) {
return userDao .findUserById(id);
}
public int insertUser(User u) {
int i = userDao .insertUser(u);
//int c = 1/0;
return i;
}
public int deleteUserById(String id) {
// TODO Auto-generated method stub
return userDao .deleteUserById(id);
}
public List<User> findAllUsers() {
// TODO Auto-generated method stub
return userDao .findAllUsers();
}
public int updateUserById(User u) {
// TODO Auto-generated method stub
return userDao .updateUserById(u);
}
}
UserAction.java
public class UserAction extends ActionSupport {
private IUserService userService ;
private String id ;
private User user ;
public void setUserService(IUserService userService) {
this.userService = userService;
}
public User getUser() {
return user ;
}
public void setUser(User user) {
this.user = user;
}
public String getId() {
return id ;
}
public void setId(String id) {
this.id = id;
}
public String findUserByid(){
User user = userService.findUserById(id );
System. out.println(user);
return "user" ;
}
public String insertUser(){
int i = userService .insertUser(user);
System. out.println(i);
return "user" ;
}
public String deleteUser(){
int i = userService .deleteUserById(id);
System. out.println(i);
return "showUsers" ;
}
public String findAllUser(){
List<User> users = userService.findAllUsers();
ActionContext. getContext().put("users", users);
return "userList" ;
}
public String updateUserUI(){
User user = userService.findUserById(id );
ActionContext. getContext().put("user", user);
return "updateUser" ;
}
public String updateUser(){
int i = userService .updateUserById(user);
return "showUsers" ;
}
}
struts.xml
<?xml version ="1.0" ?>
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.1.7//EN"
"http://struts.apache.org/dtds/struts-2.1.7.dtd" >
<struts>
<package name ="mybaits" namespace="/" extends="struts-default" >
<action name ="UserAction_*" class="userAction" method="{1}" >
<result name ="user" >/user.jsp</ result>
<result name ="userList" >/userList.jsp</ result>
<result name ="showUsers" type="redirectAction" >
<param name ="namespace" >/</ param>
<param name="actionName" >UserAction_findAllUser</ param>
</result>
<result name ="updateUser" >/updateUser.jsp</ result>
</action>
</package>
</struts>
sqlMapConfig.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>
<typeAlias type ="cn.itcast.mybatis.domain.User" alias="User" />
</typeAliases>
<!-- sql映射文件 -->
<mappers>
<mapper resource ="cn/itcast/mybatis/domain/User.xml" />
</mappers>
</configuration>
beans.xml
<?xml version ="1.0" encoding="UTF-8" ?>
<beans xmlns ="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<!-- 配置数据源 -->
<bean id ="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource" >
<property name ="driverClassName" value="com.mysql.jdbc.Driver" ></property>
<property name ="url" value="jdbc:mysql://localhost/mybatis" ></property>
<property name ="username" value="root" ></property>
<property name ="password" value="sorry" ></property>
</bean>
<bean id ="sf" class="org.mybatis.spring.SqlSessionFactoryBean" >
<property name ="dataSource" ref="dataSource" ></property>
<property name ="configLocation" value="classpath:sqlMapConfig.xml" ></property>
</bean>
<bean id ="userDao" class="cn.itcast.mybatis.dao.UserDaoImpl" >
<property name ="sqlSessionFactory" ref="sf" ></property>
</bean>
<bean id ="userService" class="cn.itcast.mybatis.service.UserServiceImpl" >
<property name ="userDao" ref="userDao" ></property>
</bean>
<bean id ="userAction" class="cn.itcast.mybatis.action.UserAction" scope="prototype" >
<property name ="userService" ref="userService" ></property>
</bean>
<!-- 事务管理器 -->
<bean id ="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager" >
<property name ="dataSource" ref="dataSource" ></property>
</bean>
<tx:advice id ="mybatis_advice" transaction-manager="txManager" >
<tx:attributes>
<tx:method name ="insert*" propagation="REQUIRED" />
<tx:method name ="update*" propagation="REQUIRED" />
<tx:method name ="delete*" propagation="REQUIRED" />
<tx:method name ="find*" read-only="true" />
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut expression ="execution(* cn.itcast.mybatis.service.*.*(..))" id ="mybatis_pc" />
<aop:advisor advice-ref ="mybatis_advice" pointcut-ref="mybatis_pc" />
</aop:config>
</beans>
web.xml
<?xml version ="1.0" encoding="UTF-8" ?>
<web-app version ="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" >
<context-param>
<param-name> contextConfigLocation</param-name >
<param-value> classpath:beans.xml</param-value >
</context-param>
<listener>
<listener-class> org.springframework.web.context.ContextLoaderListener </listener-class>
</listener>
<filter>
<filter-name> action</filter-name >
<filter-class> org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter </filter-class>
</filter>
<filter-mapping>
<filter-name> action</filter-name >
<url-pattern> *.action</url-pattern >
</filter-mapping>
<welcome-file-list >
<welcome-file >index.jsp</ welcome-file>
</welcome-file-list >
</web-app>
updateUser.jsp
<%@ page language ="java" import="java.util.*" pageEncoding="UTF-8" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://" +request.getServerName()+":"+request.getServerPort()+path+ "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head >
<title >修改用户表单页面</title >
</head >
<body >
<form action=" <%=path %> /UserAction_updateUser.action" method="post" >
<input type="hidden" value=" ${user.id }" name="user.id">
name: <input type ="text" name="user.name" value=" ${user.name }"><br >
age: <input type ="text" name="user.age" value=" ${user.age }">< br/>
address: <input type ="text" name="user.address" value=" ${user.address }"/>< br/>
<input type="submit" value="update" >
</form >
</body >
</html>
user.jsp
<%@ page language ="java" import="java.util.*" pageEncoding="UTF-8" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://" +request.getServerName()+":"+request.getServerPort()+path+ "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head >
<title >My JSP 'user.jsp' starting page</ title>
</head >
<body >
This is user.jsp
</body >
</html>
userList.jsp
<%@ page language ="java" import="java.util.*" pageEncoding="UTF-8" %>
<%@ taglib prefix ="s" uri="/struts-tags" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://" +request.getServerName()+":"+request.getServerPort()+path+ "/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head >
<title >用户列表页面</title >
</head >
<body >
<table border="1" width="80%" >
<tr >
<td> id</ td>
<td> name</td >
<td> age</td >
<td> address</td >
<td> delete</td >
<td> update</td >
</tr >
<s:iterator value="#users" >
<tr>
<td>< s:property value="id"/></ td>
<td>< s:property value="name"/></ td>
<td>< s:property value="age"/></ td>
<td>< s:property value="address"/></ td>
<td>
<a href =" <%=path %>/UserAction_deleteUser.action?id=< s:property value ="id" />"> delete</a >
</td>
<td>
<a href =" <%=path %>/UserAction_updateUserUI.action?id=< s:property value="id" />"> update</a >
</td>
</tr>
</s:iterator >
</table >
</body >
</html>