需求:
l 根据Id查询用户:传递Id
l 根据用户名进行模糊查询:传递参数:String Username
l 删除:根据Id进行删除
l 修改:根据Id进行修改
l 保存
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 -->
<configuration>
<!-- 可以配置多个运行环境,但是每个 SqlSessionFactory 实例只能选择一个运行环境
一、development:开发模式
二、work:工作模式-->
<environments default="development">
<!--id属性必须和上面的default一样 -->
<environment id="development">
<!-- mybatis事务管理器 ,由jdbc管理-->
<transactionManager type="JDBC"/>
<!-- mybatis连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///mybatis01"/>
<property name="username" value="root"/>
<property name="password" value="moujinling321"/>
</dataSource>
</environment>
</environments>
<!-- 引入外部映射文件 -->
<mappers>
<mapper resource="sqlMap/User.xml"/>
</mappers>
</configuration>
User:
package domain;
import java.util.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 + "]";
}
}
User.xml:(映射到sqlMapConfig.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">
<!--
MyBatis映射文件的开始标签 ,所有sql语句全部封装在mapper里面
namespace:命名空间
*隔离不同的映射文件,映射文件的唯一标识,可以任意命名
*在接口代理开发中,namespace具有特殊含义,不能任意命名
-->
<mapper namespace="test">
<!-- 查询所有 -->
<select id="findAll" resultType="domain.User">
select * from user
</select>
<!--
根据Id查询用户:传递参数类型:integer id
mybatis加载sqlMapConfig,加载映射文件User.xml
mybatis会把映射文件封装成一个对象MapperStatement
id称为MapperStatement的唯一标识ID
id:sql字段唯一标识,Statement的唯一标识
parameterType:制定传递参数类型
resultType:指定结果集映射文件。无论返回的是集合,还是单个对象,结果集映射都是javaBean
#{}:占位符?
*如果传递的参数是基本类型,{}里可以是任意值
*如果传递的参数是pojo,使用ognl表达式获取。属性.属性
-->
<select id="findUserByID" parameterType="int" resultType="domain.User">
select * from user where id=#{id}
</select>
<!--
根据用户名进行模糊查询
${}:sql语句拼接,原样把输入参数封装到sql语句中。
*如果传递参数是基本类型,${}里面只能是value
*如果传递参数是pojo,使用ognl表达式获取
注意:${}是sql语句拼接,有sql语句注入风险,只在模糊查询时使用
其他情况使用#{}
-->
<select id="findUserByUsername" resultType="domain.User" parameterType="string">
select * from user where username like '%${value}%'
</select>
<!-- 根据id进行删除 -->
<delete id="deleteUserByID" parameterType="int">
delete from user where id=#{id}
</delete>
<!--
根据id进行修改
parameterType:制定传递参数类型,修改,传递pojo类型。
-->
<update id="updateUserByID" parameterType="domain.User">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>
<!-- 保存User用户 (保存后获取id)
方式二:useGeneratedKeys="true"
-->
<insert id="insertUser" parameterType="domain.User" useGeneratedKeys="true" keyProperty="id">
<!--
方式一:
selectKey:选择主键返回
keyProperty:指定返回属性类型
order:由于id是自增的,id是在sql语句执行之后生成的,使用AFTER
resultType:指定返回值类型
-->
<!-- <selectKey keyProperty="id" order="AFTER" resultType="int">
SELECT LAST_INSERT_ID():得到刚 insert 进去记录的主键值,只适用与自增主键
SELECT LAST_INSERT_ID()
</selectKey> -->
insert into user values(#{id},#{username},#{birthday},#{sex},#{address})
</insert>
</mapper>
Main1:
package 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.Before;
import org.junit.Test;
import domain.User;
public class Main1 {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void beforeConf() throws IOException{
// 加载全局配置文件,连接数据库
String resources = "sqlMapConfig.xml";
// 加载配置文件
InputStream inputStream = Resources.getResourceAsStream(resources);
// 获取工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
/**
* 查询所有
* @throws Exception
*/
@Test
public void findAll() throws Exception {
// 加载全局配置文件,连接数据库
String resources = "sqlMapConfig.xml";
// 加载配置文件
InputStream inputStream = Resources.getResourceAsStream(resources);
// 获取工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//使用工厂创建sqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
//查询数据库
//返回结果是List集合:selectList
//selectList如何定位映射文件查询语句。namespace+id
List<User> list = sqlSession.selectList("test.findAll");
System.out.println(list);
}
/**
* 根据ID查询用户
* @throws IOException
*/
@Test
public void findUserByID() throws IOException{
String resources="sqlMapConfig.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession=sqlSessionFactory.openSession();
//返回单个对象,使用selectOne方法
//第一个参数:定位查询语句,namespace+id
//第二个参数:传递参数,id
User user=sqlSession.selectOne("test.findUserByID", 1);
System.out.println(user);
sqlSession.close();
}
/**
* 模糊查询用户
* @throws IOException
*/
@Test
public void findUserByUsername(){
SqlSession sqlSession=sqlSessionFactory.openSession();
List<User> list=sqlSession.selectList("test.findUserByUsername", "三");
System.out.println(list);
}
}
Main2:
package test;
import static org.hamcrest.CoreMatchers.nullValue;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
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;
import domain.User;
public class Main2 {
SqlSessionFactory sqlSessionFactory=null;
@Before
public void beforeConf() throws IOException{
String resources="sqlMapConfig.xml";
InputStream inputStream=Resources.getResourceAsStream(resources);
sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
}
//根据id删除
@Test
public void deleteUserByID(){
SqlSession sqlSession=sqlSessionFactory.openSession();
sqlSession.delete("test.deleteUserByID", 26);
//提交事务
sqlSession.commit();
sqlSession.close();
}
//根据id修改
@Test
public void updateUserByID(){
SqlSession sqlSession=sqlSessionFactory.openSession();
User user=new User();
user.setId(16);
user.setBirthday(new Date());
user.setAddress("旧金山");
user.setUsername("范冰冰");
sqlSession.update("test.updateUserByID", user);
sqlSession.commit();
sqlSession.close();
}
//保存用户
@Test
public void insertUser(){
SqlSession sqlSession=sqlSessionFactory.openSession();
User user=new User();
user.setUsername("花果山寨主");
user.setSex("女");
user.setAddress("花果山");
user.setBirthday(new Date());
sqlSession.insert("test.insertUser",user);
System.out.println(user.getId());
sqlSession.commit();
System.out.println(user.getId());
sqlSession.close();
}
}