mybatis单表增删查改(Users，id,name,age)
1.mybatis的步骤
1.0.准备数据库和表
use big12 ;
create table users(id int primary key auto_increment , name varchar(20) , age int);
desc users; //查看表结构
1.1创建模块，添加Maven
[pom.xml]
<groupId>com.dzc</groupId>
<artifactId>mybatis</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
</dependency>
<!--添加mybatis安装包-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.1</version>
</dependency>
</dependencies>>
1.2创建User类
[User.java]
/**
* 域模型
* **/
public class User {
private Integer id;
private String name;
private int age;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
1.3创建映射文件
[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="users">
<!-- 定义insert语句 ,获得生成的id字段-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
<!--#提取对象，提取参数的对象-->
```MySql insert into users(name,age) values(#{name},#{age})
</insert>
<update id="update">
update users set name = #{name} , age = #{age} where id = #{id}
</update>
<delete id="delete">
delete from users where id = #{id}
</delete>
<select id="selectById" resultMap="rmUser">
select
u.id uid , u.name uname ,u.age uage , o.id oid , o.orderno oorderno,o.price oprice, i.id iid , i.iname iiname
from
users u
left outer join orders o on o.cid = u.id
left outer join orderitems i on i.oid = o.id
where u.id = #{id}
</select>
<select id="selectAll" resultType="_User">
select * from users
</select>
<!-- 用户映射 -->
<resultMap id="rmUser" type="_User">
<id column="uid" property="id" />
<result column="uname" property="name" />
<result column="uage" property="age"/>
<collection property="orders" ofType="_Order" column="uid">
<id column="oid" property="id" />
<result column="oorderno" property="orderNo" />
<result column="oprice" property="price"/>
<collection property="items" ofType="_Item" column="oid">
<id column="iid" property="id"/>
<result column="iiname" property="iname" />
</collection>
</collection>
</resultMap>
</mapper>
1.4创建配置文件
[resources/mybatis-config.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>
<properties>
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/big12"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</properties>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="UserMapper.xml"/>
</mappers>
</configuration>
1.5测试增删改查
package com.dzc.mybatis.test;
import com.dzc.mybatis.domain.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;
import java.io.IOException;
import java.io.InputStream;
/**
* 测试增删改查
*/
public class TestCRUD {
/**添加操作***/
@Test
public void testInsert() throws IOException {
//1.加载配置文件 Resources.getResourceAsStream()（读取内路径的资源流加载）
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建会话工厂(builder模式)（连接池）
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
//3.开启会话，相当于连接
SqlSession sess = sf.openSession();
//4.创建提交对象
User u = new User() ;
u.setName("tom");
u.setAge(12);
//intsert(String:tatement,（语句）Object parameter（对象）)UserMapper.xml
sess.insert("users.insert" , u) ;
//提交(事物操作)
sess.commit();
sess.close();
System.out.println("ok");
}
}
2.update修改
2.1在映射文件中添加新update sql
[resources/UserMapper.xml]
<update id="update">
update users set name = #{name} , age = #{age} where id = #{id}//通过id来更新
</update>
2.2在测试文件中写修改代码
[TestCRUD.java]
/**修改操作***/
public void testUpdate()throws IOException{
//1.加载配置文件 Resources.getResourceAsStream()（读取内路径的资源流加载）
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建会话工厂(builder模式)（连接池）
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
//3.开启会话，相当于连接
SqlSession sess = sf.openSession();
//4.创建提交对象
User u = new User() ;
u.setName("toms");
u.setAge(22);
//通过id修改
u.setId(1);
//update(String:tatement,（语句）Object parameter（对象）)UserMapper.xml
sess.update("users.update" , u) ;
//提交(事物操作)
sess.commit();
sess.close();
System.out.println("ok");//1.加载配置文件 Resources.getResourceAsStream()（读取内路径的资源流加载）
}
3.delete删除
3.1在UserMapper.xml写sql语句
[resources/UserMapper.xml]
<delete>
delete from users where id = #{id}
</delete>
3.2.在Api中调用方法
[TestCRUD.java]
/**
*根据id删除
* */
@Test
public void delectById()throws IOException{
//1.加载配置文件 Resources.getResourceAsStream()（读取内路径的资源流加载）
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建会话工厂(builder模式)（连接池）
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
//3.开启会话，相当于连接
SqlSession sess = sf.openSession();
sess.delete("users.delect",1);
sess.close();
}
4.Select查询按照id查询
4.1给查询操作定义别名
[resources/UserMapper.xml]
[resources/mybatis-config.xml] alias定义别名
<typeAliases>
<typeAlias type="com.dzc.mybatis.domain.User" alias="_User"/>
<typeAliases>
4.2UserMapper.xml中根据id写查询语句
[resources/UserMapper.xml]
<select id="selectById" resultType="_User">
select * from users where id=#{id}
</select>
4.3在Api中调用方法
[TestCRUD.java]
//单个查询
@Test
public void testSelectOne()throws IOException{
//1.加载配置文件 Resources.getResourceAsStream()（读取内路径的资源流加载）
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建会话工厂(builder模式)（连接池）
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
//3.开启会话，相当于连接
SqlSession sess = sf.openSession();
sess.selectOne("users.selectById" , 1) ;
//提交(事物操作)
sess.commit();
sess.close();
}
5.查询多个
5.1给查询操作定义别名
[resources/mybatis-config.xml] alias定义别名
<typeAliases>
<typeAlias type="com.oldboy.mybatis.domain.User" alias="_User"/>
</typeAliases>
5.2在UserMapper.xml添加sql语句
[resources/UserMapper.xml]
<select id="selectById" resultType="_User">
select * from users
</select>
5.3API调用
[TestCRUD.java]
//查询多个
@Test
public void testSelectAll()throws IOException{
//1.加载配置文件 Resources.getResourceAsStream()（读取内路径的资源流加载）
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建会话工厂(builder模式)（连接池）
SqlSessionFactory sf = new SqlSessionFactoryBuilder().build(in);
//3.开启会话，相当于连接
SqlSession sess = sf.openSession();
List<User> list= sess.selectList("users.selectAll" ) ;
//提交(事物操作)
sess.commit();
System.out.println(list.size());
sess.close();
}