mybatis的增删改查Demo
1. 创建数据库和表
MySQL数据库创建表格,并插入数据
创建数据库mybatis同时创建表格users
create database mybatis;
use mybatis;
CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT);
INSERT INTO users(NAME, age) VALUES('Tom', 12);
INSERT INTO users(NAME, age) VALUES('Jack', 11);
2. 配置文件
2.0 导入需要的包
mybatis-3.1.1.jar
mysql-connector-java-5.1.26-bin.jar
org.junit_4.8.2.v4_8_2_v20110321-1705\junit.jar
2.1 创建一个与表对应的bean
User.java
package com.bart.mybatis.beans;
/**
* User必须要有一个无参构造器,否则会报错
* @author hp
*
*/
public class User {
public User() {
super();
}
public User(int id, String name, int age) {
super();
this.id = id;
this.name = name;
this.age = age;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", age=" + age + "]";
}
public int getId() {
return id;
}
public void setId(int 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;
}
private int id;
private String name;
private int age;
}
配置conf.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>
<!--
development : 开发模式
work : 工作模式
-->
<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://127.0.0.1:3306/mybatis" />
<property name="username" value="root" />
<property name="password" value="123456" />
</dataSource>
</environment>
</environments>
<!-- 映射mapper配置文件 -->
<mappers>
<mapper resource="com/bart/mybatis/mapper/userMapper.xml"/>
<!-- 加载使用注解配置的接口 -->
<mapper class="com.bart.mybatis.interfacemapper.UserMapper"/>
</mappers>
</configuration>
创建mapper配置文件
com.bart.mybatis.mapper目录下创建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="com.bart.mybatis.mapper.userMapper">
<!--
根据id查询得到一个user对象
-->
<select id="getUser" parameterType="int" resultType="com.bart.mybatis.beans.User">
select * from users where id=#{id}
</select>
<update id="updateUser" parameterType="com.bart.mybatis.beans.User">
update users set name=#{name},age=#{age} where id=#{id}
</update>
<insert id="addUser" parameterType="com.bart.mybatis.beans.User">
insert into users(name,age) values(#{name},#{age})
</insert>
<delete id="deleteUser" parameterType="int">
delete from users where id=#{id}
</delete>
<select id="getAllUser" resultType="com.bart.mybatis.beans.User">
select * from users
</select>
</mapper>
创建测试类
com.bart.mybatis.test1包下面创建Test1.java
package com.bart.mybatis.test1;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.bart.mybatis.beans.User;
import com.bart.mybatis.dao.DBUtil;
public class Test1 {
@Test
public void test1() throws IOException{
String resource = "conf.xml";
//加载mybatis的配置文件(同时加载关联的映射文件)
//Reader reader = Resources.getResourceAsReader(resource);
InputStream is = Test.class.getClassLoader().getResourceAsStream(resource);
//获得SqlSession工厂对象
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
//创建sql session
SqlSession session = sessionFactory.openSession();
//映射sql标示符
String statement = "com.bart.mybatis.mapper.userMapper.getUser";
User user = session.selectOne(statement,2);
System.out.println(user.toString());
}
//增加User
@Test
public void testAdd(){
SqlSession session = DBUtil.getSessionFactory().openSession();
String statement = "com.bart.mybatis.mapper.userMapper.addUser";
session.insert(statement, new User(-1,"AAA",22));
session.commit();//提交事务
session.close();//关闭session
}
//更新User
@Test
public void testUpdate(){
SqlSession session = DBUtil.getSessionFactory().openSession();
String statement = "com.bart.mybatis.mapper.userMapper.updateUser";
session.update(statement, new User(3, "AAA3", 23));
session.commit();//提交事务
session.close();//关闭session
}
//删除User
@Test
public void testDelete(){
SqlSession session = DBUtil.getSessionFactory().openSession();
String statement = "com.bart.mybatis.mapper.userMapper.deleteUser";
session.delete(statement, 3);
session.commit();//提交事务
session.close();//关闭session
}
//得到所有的User
@Test
public void testGetAll(){
SqlSession session = DBUtil.getSessionFactory().openSession();
String statement = "com.bart.mybatis.mapper.userMapper.getAllUser";
List<User>list = session.selectList(statement);
session.commit();//提交事务
session.close();//关闭session
System.out.println(list);
}
}
使用注解配置sql
使用xml配置还是比较麻烦的,mybatis支持注解操作,首先创建一个接口,然后穿件抽象方法,在每个方法上面使用对应的注解编写响应的sql语句
注意
创建完注解的接口之后,要在conf.xml
中配置。
package com.bart.mybatis.interfacemapper;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.bart.mybatis.beans.User;
/**
* 使用注解的增删改查
* @author hp
*
*/
public interface UserMapper {
@Select("select * from users where id=#{id}")
public User getUser(int id);
@Insert("insert into users(name,age) values(#{name},#{age})")
public int add(User user);
@Update("update users set name=#{name},age=#{age} where id=#{id}")
public void update(User user);
@Delete("delete from users where id=#{id}")
public void delete(int id);
@Select("select * from users")
public List<User> getAll();
}
测试使用接口的crud
package com.bart.mybatis.interfacemapper;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.bart.mybatis.beans.User;
/**
* 使用注解的增删改查
* @author hp
*
*/
public interface UserMapper {
@Select("select * from users where id=#{id}")
public User getUser(int id);
@Insert("insert into users(name,age) values(#{name},#{age})")
public int add(User user);
@Update("update users set name=#{name},age=#{age} where id=#{id}")
public void update(User user);
@Delete("delete from users where id=#{id}")
public void delete(int id);
@Select("select * from users")
public List<User> getAll();
}
在使用接口的 的时候,crud操作比xml配置要方便多了。