package com.xuezhi.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 org.junit.Test;
import com.xuezhi.pojo.User;
public class mybatistest {
//查询单个用户
@Test
public void queryUserbyId() throws IOException{
//以流的方式获取资源
InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
//通过SqlSessionFactoryBuilder(sqlsession工厂建筑者的构造方法)构建一个SqlSessionFactory
SqlSessionFactory sqlsessionfactory=new SqlSessionFactoryBuilder().build(inputStream);
//通过SqlSessionFactory(sqlsession工厂)生产sqlsession
SqlSession sqlsession=sqlsessionfactory.openSession();
User user=sqlsession.selectOne("test.queryUserbyId",1);
System.out.print("根据id查询user:"+user);
sqlsession.close();
}
//查询所有用户
@Test
public void findUsers() throws IOException{
//以流的方式获取资源
InputStream inputStream=Resources.getResourceAsStream("sqlMapConfig.xml");
//通过SqlSessionFactoryBuilder(sqlsession工厂建筑者的构造方法)构建一个SqlSessionFactory
SqlSessionFactory sqlsessionfactory=new SqlSessionFactoryBuilder().build(inputStream);
//通过SqlSessionFactory(sqlsession工厂)生产sqlsession
SqlSession sqlsession=sqlsessionfactory.openSession();
List<User> list = sqlsession.selectList("test.findUsers");
System.out.println("查询出来的users:"+list);
sqlsession.close();
}
//删除
@Test
public void delUserbyId() throws IOException{
//以流的方式获取资源
InputStream inputStream=Resources.getResourceAsStream("sqlMapConfig.xml");
//通过SqlSessionFactoryBuilder(sqlsession工厂建筑者的构造方法)构建一个SqlSessionFactory
SqlSessionFactory sqlsessionfactory=new SqlSessionFactoryBuilder().build(inputStream);
//通过SqlSessionFactory(sqlsession工厂)生产sqlsession
SqlSession sqlsession=sqlsessionfactory.openSession();
int row=sqlsession.delete("test.delUserbyId",1);
System.out.println("row:"+row);
sqlsession.commit();
sqlsession.close();
}
//更新
@Test
public void updateUser() throws IOException{
//以流的方式获取资源
InputStream inputStream=Resources.getResourceAsStream("sqlMapConfig.xml");
//通过SqlSessionFactoryBuilder(sqlsession工厂建筑者的构造方法)构建一个SqlSessionFactory
SqlSessionFactory sqlsessionfactory=new SqlSessionFactoryBuilder().build(inputStream);
//通过SqlSessionFactory(sqlsession工厂)生产sqlsession
SqlSession sqlsession=sqlsessionfactory.openSession();
//创建user对象
User user=new User();
user.setId(1);
user.setUsername("阿坤");
user.setPassword("123456");
//通过selectOne查询一条结果集的语句,指定mapper的地址和SQL的ID来找到SQL语句的地址
int row =sqlsession.update("test.updateUser",user);
System.out.println("row:"+row);
sqlsession.commit();
sqlsession.close();
}
//新增用户
@Test
public void addUser() throws IOException{
//以流的方式获取资源
InputStream inputStream=Resources.getResourceAsStream("sqlMapConfig.xml");
//通过SqlSessionFactoryBuilder(sqlsession工厂建筑者的构造方法)构建一个SqlSessionFactory
SqlSessionFactory sqlsessionfactory=new SqlSessionFactoryBuilder().build(inputStream);
//通过SqlSessionFactory(sqlsession工厂)生产sqlsession
SqlSession sqlsession=sqlsessionfactory.openSession();
//创建user对象
User user=new User();
user.setUsername("张起灵");
user.setPassword("123321");
//通过selectOne查询一条结果集的语句,指定mapper的地址和SQL的ID来找到SQL语句的地址
int row =sqlsession.insert("test.addUser",user);
System.out.println("row:"+row);
sqlsession.commit();
sqlsession.close();
}
@Test
public void addUserAndfindkey() throws IOException{
//以流的方式获取资源
InputStream inputStream=Resources.getResourceAsStream("sqlMapConfig.xml");
//通过SqlSessionFactoryBuilder(sqlsession工厂建筑者的构造方法)构建一个SqlSessionFactory
SqlSessionFactory sqlsessionfactory=new SqlSessionFactoryBuilder().build(inputStream);
//通过SqlSessionFactory(sqlsession工厂)生产sqlsession
SqlSession sqlsession=sqlsessionfactory.openSession();
//创建user对象
User user=new User();
user.setUsername("坎肩");
user.setPassword("123456");
int row =sqlsession.insert("test.addUserAndfindkey",user);
System.out.println("row:"+row);
System.out.println("获得的主键:"+user.getId());
sqlsession.commit();
sqlsession.close();
}
}
package com.xuezhi.pojo;
public class User {
private int id;
private String username;
private String password;
@Override
public String toString() {
return “User [id=” + id + “, username=” + username + “, password=” + password + “]”;
}
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
<!-- 根据传入的参数模糊查询用户 -->
<select id="findUserlikeName" parameterType="String" resultType="com.xuezhi.pojo.User">
select * from user where username like '%${value}'
</select>
<!-- 根据传入的参数模糊查询用户,避免使用${} -->
<select id="findUserlikeName2" parameterType="String" resultType="com.xuezhi.pojo.User">
select * from user where username like concat('%',#{v},'%'
</select>
<!-- 查询所有用户 -->
<select id="findUsers" resultType="com.xuezhi.pojo.User">
select * from user
</select>
<!-- 根据ID删除用户 -->
<delete id="delUserbyId" parameterType="int">
delete from user where id=#{id}
</delete>
<!-- 更新用户 -->
<!-- 由于输入的参数不为基本数据类型,传入的参数为一个对象,所以#{}里需要指定对象中相应的属性 -->
<update id="updateUser" parameterType="com.xuezhi.pojo.User">
update user set username=#{username},password=#{password} where id=#{id}
</update>
<!-- 新增用户 -->
<insert id="addUser" parameterType="com.xuezhi.pojo.User">
insert into user(id,username,password) values(null,#{username},#{password})
</insert>
<!-- 新增用户并且获取自增主键 -->
<insert id="addUserAndfindkey" parameterType="com.xuezhi.pojo.User">
<!--
select LAST_INSERT_ID():获取自增主键的值,必须和insert语句一起使用
keyProperty:查询出来的主键绑定到user对象的那个属性上
order:规定查询主键SQL的函数,是在执行insert之前运行还是之后运行,AFTER:之后执行,BEFORE:之前执行
resultType:查询出来的主键返回的是什么类型
-->
<selectKey keyProperty="id" resultType="int" order="AFTER">
select LAST_INSERT_ID()
</selectKey>
insert into user values(null,#{username},#{password})