mybatis的简单使用和demo
demo 这个是简单demo的 码云地址 https://git.oschina.net/yuhaifei/Mybatis_demo.git
1.导入jar包
2.配置mybatis一下必要的文件
<?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>
<properties resource="db.properties"></properties>
<typeAliases>
<!-- 定义单个pojo类别名
type:类的全路劲名称
alias:别名
-->
<!-- <typeAlias type="cn.itheima.pojo.User" alias="user"/> -->
<!-- 使用包扫描的方式批量定义别名
定以后别名等于类名,不区分大小写,但是建议按照java命名规则来,首字母小写,以后每个单词的首字母大写
-->
<package name="cn.itheima.pojo"/>
</typeAliases>
<!-- 和spring整合后 environments配置将废除-->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理-->
<transactionManager type="JDBC" />
<!-- 数据库连接池-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 配置,文件,关键 -->
<mapper resource="User.xml"/>
<!--
使用class属性引入接口的全路径名称:
使用规则:
1. 接口的名称和映射文件名称除扩展名外要完全相同
2. 接口和映射文件要放在同一个目录下
-->
<!-- <mapper class="cn.itheima.mapper.UserMapper"/> -->
<!-- 使用包扫描的方式批量引入Mapper接口
使用规则:
1. 接口的名称和映射文件名称除扩展名外要完全相同
2. 接口和映射文件要放在同一个目录下
-->
</mappers>
</configuration>
3.java 文件 做mode文件
package mode;
import java.sql.Date;
public class User {
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address + "]";
}
}
3.1 还要配置,User.xml文件,这个很关键,这个是把spl语句配置在这个文件里
<?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">
<!-- namespace:命名空间,做sql隔离 -->
<mapper namespace="test">
<!--
id:sql语句唯一标识
parameterType:指定传入参数类型
resultType:返回结果集类型
#{}占位符:起到占位作用,如果传入的是基本类型(string,long,double,int,boolean,float等),那么#{}中的变量名称可以随意写.
-->
<select id="findUserById" parameterType="java.lang.Integer" resultType="mode.User">
select * from user where id=#{id}
</select>
</mapper>
4.测试代码 这个很关键
package mybatis_demo_01;
import java.io.InputStream;
import mode.User;
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 org.junit.Test;
public class UserTest {
//有时候,@Test这个注解没法出现,要全写
@Test
public void testData() throws Exception{
String resource= "SqlMapConfig.xml";
//通过流将核心配置文件读取进来
InputStream config= Resources.getResourceAsStream(resource);
通过核心配置文件输入流来创建会话工厂
SqlSessionFactory factory= new SqlSessionFactoryBuilder().build(config);
//通过工厂创建回话
SqlSession openSession = factory.openSession();
//只查询一条数据,(第一个参数:所调用的sql语句= namespace+.+sql的ID,参数)
User user = openSession.selectOne("test.findUserById", 1);
System.out.println(user);
}
}
5。数据库的样子
在查询数据回来是list的时候。xml文件配,以及使用
1.user.xml配置
<!--
如果返回结果为集合,可以调用selectList方法,这个方法返回的结果就是一个集合,所以映射文件中应该配置成集合泛型的类型
${}拼接符:字符串原样拼接,如果传入的参数是基本类型(string,long,double,int,boolean,float等),那么${}中的变量名称必须是value
防止sql注入,只能在like的时候使用
${} 拼接符 value
-->
<select id="findUserByUsername" parameterType="String" resultType="mode.User">
SELECT * from user a WHERE a.username LIKE '%${value}%'
</select>
2.java文件测试
/**
* 根据username查询数据
* @throws Exception
*/
@Test
public void testSelectUserName() throws Exception{
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = factory.openSession();
List<User> selectList = openSession.selectList("test.findUserByUsername", "王五");
System.out.println(selectList);
}
5.添加数据
5.1 在user.xml文件中,添加
<!--
添加数据
(#{username},#{birthday},#{sex},#{address}) 必须是user里面的参数
-->
<insert id="insertUser" parameterType="mode.User" >
<!-- 执行 select LAST_INSERT_ID()数据库函数,返回自增的主键
keyProperty:将返回的主键放入传入参数的Id中保存.
order:当前函数相对于insert语句的执行顺序,在insert前执行是before,在insert后执行是AFTER
resultType:id的类型,也就是keyproperties中属性的类型
-->
<selectKey keyProperty="id" order="AFTER" resultType="int" >
select LAST_INSERT_ID()
</selectKey>
INSERT INTO user (username,birthday,sex,address) VALUES (#{username},#{birthday},#{sex},#{address})
</insert>
5.2 java中测试
public class UserAddTest {
@Test
public void instarUserData() throws Exception{
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = factory.openSession();
//List<User> selectList = openSession.selectList("test.insertUser", "王五");
User user = new User();
user.setUsername("XXX");
user.setSex("1");
user.setAddress("常州");
user.setBirthday(new Date());
System.out.println("///"+user.getId());
openSession.insert("test.insertUser", user);
System.out.println("///"+user.getId());
}
}
5.3 添加uuid,id主键不是int型
需要增加通过select uuid()得到uuid值
<insert id="insertUser" parameterType="cn.itcast.mybatis.po.User">
<selectKey resultType="java.lang.String" order="BEFORE"
keyProperty="id">
select uuid()
</selectKey>
insert into user(id,username,birthday,sex,address)
values(#{id},#{username},#{birthday},#{sex},#{address})
</insert>
注意这里使用的order是“BEFORE”
6 删除数据 和 修改数据 在user.xml文件和Test文件中
<delete id="delectUser" parameterType="int" >
DELETE FROM user WHERE id = #{id}
</delete>
<update id= "updateUser" parameterType="mode.User" >
update user set username=#{username} where id=#{id}
</update>
java测试代码
/**
* 删除
* @throws Exception
*/
@Test
public void delectUserData() throws Exception{
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = factory.openSession();
openSession.delete("test.delectUser", 24);
openSession.commit();
}
@Test
/**
* 修改
* @throws Exception
*/
public void updateUserData() throws Exception{
/*
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession openSession = factory.openSession();
User user = new User();
user.setId(10);
user.setUsername("yyyyyyyyyyyyy");
openSession.update("test.updateUser", user);
openSession.commit();
*/
UserDaoImp userDaoImp = new UserDaoImp();
userDaoImp.updateUser();
}
7 dao编程使用
dao.java
package dao;
public interface UserDao {
public void updateUser();
}
daoImpl.java
package dao;
import java.io.IOException;
import java.io.InputStream;
import mode.User;
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 UserDaoImp implements UserDao{
private static SqlSessionFactory factory;
public static SqlSessionFactory getFactory() {
if (null == factory) {
String resource = "SqlMapConfig.xml";
InputStream inputStream;
try {
inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory newfactory = new SqlSessionFactoryBuilder().build(inputStream);
return newfactory;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return factory;
}
public static void setFactory(SqlSessionFactory factory) {
UserDaoImp.factory = factory;
}
public UserDaoImp() {
}
@Override
public void updateUser() {
// TODO Auto-generated method stub
SqlSession openSession = getFactory().openSession();
User user = new User();
user.setId(10);
user.setUsername("aaaaaaaaaaaa");
openSession.update("test.updateUser", user);
openSession.commit();
}
}
1.mybatis中动态代理,最大优势不写过多的java代码
切记,xml文件和java接口文件必须在一个目录里面
2.配置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="mapper.UserMapper">
<select id="selectUser" parameterType="int" resultType="mode.User">
select * from user where id=#{id}
</select>
<select id="selectLikeUser" parameterType="String" resultType="mode.User">
SELECT * from user a where a.username like '%${value}%'
</select>
<insert id="insertUser" parameterType="mode.User" >
<selectKey order="AFTER" resultType="int" keyProperty="id">
select LAST_INSERT_ID()
</selectKey>
INSERT INTO user (username,birthday,sex,address) VALUES (#{username},#{birthday},#{sex},#{address})
</insert>
</mapper>
3.java 接口,方法名必须要和xml里面的id一样
package mapper;
import java.util.List;
import mode.User;
public interface UserMapper {
public User selectUser(int id);
public List<User> selectLikeUser(String name);
}
4.测试java方法,与前面的方法一样
package mybatis_demo_01;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
import javax.persistence.Basic;
import mapper.UserMapper;
import mode.User;
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 org.junit.Before;
import org.junit.Test;
public class UserTestMapper {
private SqlSessionFactory factory;
//这个是测试的时候先启动
@Before
public void setUp() throws Exception{
String resource = "SqlMapConfig.xml";
//通过流将核心配置文件读取进来
InputStream inputStream = Resources.getResourceAsStream(resource);
//通过核心配置文件输入流来创建会话工厂
factory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void selectUser(){
/*
* 第一查询
SqlSession openSession = factory.openSession();
UserMapper mapper = openSession.getMapper(UserMapper.class);
User selectUser = mapper.selectUser(22);
System.out.println(selectUser.getUsername());
//模糊查询
SqlSession openSession = factory.openSession();
UserMapper mapper = openSession.getMapper(UserMapper.class);
List<User> selectLikeUser = mapper.selectLikeUser("王");
for (User user : selectLikeUser) {
System.out.println(user.getUsername());
}
*/
SqlSession openSession = factory.openSession();
UserMapper mapper = openSession.getMapper(UserMapper.class);
User user = new User();
user.setSex("男");
user.setUsername("XXX");
user.setBirthday(new Date());
user.setAddress("常州");
int insertUser = mapper.insertUser(user);
System.out.println(insertUser);
//必须要这个
openSession.commit();
}
}
在myBatis中一些关键字的使用
<?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="mapper.UserMapper">
<!-- 封装sql条件,封装后可以重用.
id:是这个sql条件的唯一标识 -->
<sql id="user_where">
<!-- where标签作用:
会自动向sql语句中添加where关键字
会去掉第一个条件的and关键字
-->
<where>
<if test="username != null and username != '' ">
and username like '%${username}%'
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</where>
</sql>
<select id="selectUser" parameterType="int" resultType="mode.User">
select * from user where id=#{id}
</select>
<select id="selectLikeUser" parameterType="String" resultType="mode.User">
SELECT * from user a where a.username like '%${value}%'
</select>
<insert id="insertUser" parameterType="mode.User" >
<selectKey order="AFTER" resultType="int" keyProperty="id">
select LAST_INSERT_ID()
</selectKey>
INSERT INTO user (username,birthday,sex,address) VALUES (#{username},#{birthday},#{sex},#{address})
</insert>
<!-- where 和 if foreach 关键字的使用 -->
<select id="selectLikeAndWhereUser" parameterType="vo.ViewBean" resultType="mode.User">
select * from user
<include refid="user_where"></include>
</select>
<select id="selectInUser" parameterType="vo.ViewBean" resultType="mode.User">
select * from user
<where>
<if test="ids != null">
<!--
foreach:循环传入的集合参数
collection:传入的集合的变量名称
item:每次循环将循环出的数据放入这个变量中
open:循环开始拼接的字符串
close:循环结束拼接的字符串
separator:循环中拼接的分隔符
-->
<foreach collection="ids" item="id" open="id in(" separator="," close=")">
#{id}
</foreach>
</if>
</where>
</select>
</mapper>
2.在SqlMapConfig.xml中配置log4j输出文件
<?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>
<properties resource="db.properties"></properties>
<!-- 配置log4j 会出现一定的错误 http://www.cnblogs.com/zipon/p/7081978.html 解决问题地址 -->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<!-- 定义单个pojo类别名
type:类的全路劲名称
alias:别名
-->
<!-- <typeAlias type="cn.itheima.pojo.User" alias="user"/> -->
<!-- 使用包扫描的方式批量定义别名
定以后别名等于类名,不区分大小写,但是建议按照java命名规则来,首字母小写,以后每个单词的首字母大写
-->
<package name="modo"/>
</typeAliases>
<!-- 和spring整合后 environments配置将废除-->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理-->
<transactionManager type="JDBC" />
<!-- 数据库连接池-->
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 配置,文件,关键 -->
<mapper resource="User.xml"/>
<mapper class="mapper.UserMapper"/>
<!--
使用class属性引入接口的全路径名称:
使用规则:
1. 接口的名称和映射文件名称除扩展名外要完全相同
2. 接口和映射文件要放在同一个目录下
-->
<!-- <mapper class="cn.itheima.mapper.UserMapper"/> -->
<!-- 使用包扫描的方式批量引入Mapper接口
使用规则:
1. 接口的名称和映射文件名称除扩展名外要完全相同
2. 接口和映射文件要放在同一个目录下
-->
</mappers>
</configuration>
3.java 代码中测试(必须在接口java文件中配置,与id相同的方法)
SqlSession openSession = factory.openSession();
UserMapper mapper = openSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("小");
List<User> selectLikeAndWhereUser = mapper.selectLikeAndWhereUser(user);
for (User user2 : selectLikeAndWhereUser) {
System.out.println(user2.getUsername() + "-----------------------");
}
SqlSession openSession = factory.openSession();
UserMapper mapper = openSession.getMapper(UserMapper.class);
List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(10);
ViewBean viewBean = new ViewBean();
viewBean.setIds(list);
List<User> selectInUser = mapper.selectInUser(viewBean);
for (User usr : selectInUser) {
System.out.println(usr.getUsername());
}