Mybatis CRUD入门

1.准备工作配置

1.1 导入坐标

		<dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.32</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

1.2 sql语句

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `money` double(6, 2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

1.3实体类 Account.java

public class Account implements Serializable {

    private int id;
    private String name;
    private Double money;

    public Account() {
    }

    public Account(int id, String name, Double money) {
        this.id = id;
        this.name = name;
        this.money = money;
    }

    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 Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}

1.4AccountDao.java

public interface AccountDao {

    /**
     * 查找所有账户
     * @return
     */
    List<Account> findAll();

    /**
     * 通过id,查找账户
     * @param id
     * @return
     */
    Account findByID(int id);

    /**
     * 通过名字模糊查询
     * @param name
     * @return
     */
    List<Account> findLikeName(String name);

    /**
     * 账户个数
     * @return
     */
    int findTotal();

    /**
     * 新增账户
     * @param account
     * @return
     */
    int saveAccount(Account account);

    /**
     * 更新账户money
     * @param account
     * @return
     */
    int updateAccount(Account account);

    /**
     * 通过id删除账户
     * @param id
     * @return
     */
    int deleteAccount(int id);

}

1.5 sqlMapConfig.xml

当然在ssm中使用时，这个文件是不存在的
可在spring的配置加上同样完成功能

<!--mybatis-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
        <property name="username" value="root"/>
        <property name="password" value="1234"/>
    </bean>

    <bean id="factory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="typeAliasesPackage" value="priv.xy.domain"/>
    </bean>

    <bean id="configurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="priv.xy.dao"/>
    </bean>

<?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>
    <!-- 配置mybatis环境 -->
    <typeAliases>
        <!-- 对类自定义其别名 -->
        <!--<typeAlias type="priv.xy.domain.Account" alias="account"/>-->
        <!--批量指定:只要指定包名即可，之后程序会为包下的所有类都自动加上别名，
        其定义别名的规范就是对应包装类的类名首字母变为小写-->
        <package name="priv.xy.domain"/>
    </typeAliases>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/ssm"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 配置需要加载的 sql 映射配置文件路径 -->
    <mappers>
        <!-- 通用  xml和注解 -->
         <package name="priv.xy.dao"/>
        <!-- <mapper class="priv.xy.dao.AccountDao"/> -->

        <!-- 以下在注解配置时不在适用 -->
        <!-- <mapper resource="priv/xy/dao/AccountDao.xml"/> -->
        <!-- <mapper url="file:///F:/Codes/Java/Mybatis/src/main/resources/priv/xy/dao/AccountDao.xml"/> -->
    </mappers>
</configuration>

1.6 test类

public class MybatisTest {
    private AccountDao dao;
    private InputStream is;
    private SqlSession session;
    @Before
    public void init() throws IOException {
        is = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        session = factory.openSession();
        dao = session.getMapper(AccountDao.class);
    }
    @After
    public void destory() throws IOException {
        session.commit();
        session.close();
        is.close();
    }
    @Test
    public void testFindAll(){
        List<Account> list = dao.findAll();
        for (Account account : list) {
            System.out.println(account);
        }
    }
    @Test
    public void testFindByID(){
        Account account = dao.findByID(1);
        System.out.println(account);
    }
    @Test
    public void testFindLikeName(){
        List<Account> accountList = dao.findLikeName("%a%");
        for (Account account : accountList) {
            System.out.println(account);
        }
    }
    @Test
    public void testFindTotal(){
        int total = dao.findTotal();
        System.out.println(total);
    }

    @Test
    public void testSaveAccount(){
        Account account = new Account();
        account.setName("test");
        account.setMoney((double)100);
        dao.saveAccount(account);
    }
    @Test
    public void testUpdateAccount(){
        Account daoByID = dao.findByID(2);
        System.out.println(daoByID);
        daoByID.setMoney((double)99);
        dao.updateAccount(daoByID);
    }
    @Test
    public void testDeleteAccount(){
        // 删除id=3的账号
        dao.deleteAccount(3);
    }
}

2.基于xml的Mybatis

2.1 AccountDao.xml

现在的目录结构

值得注意的是：
创建resources下的文件夹的时候 priv/xy/dao 别 priv.xy.dao

<?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="priv.xy.dao.AccountDao">
    <resultMap id="accountMap" type="priv.xy.domain.Account">
    	<!-- 主键必写，其他可以选择性的写-->
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="money" property="money"/>
    </resultMap>
    <select id="findAll" resultMap="accountMap">
        select * from account
    </select>
    <select id="findByID" resultType="account" parameterType="int">
        select * from account where id = #{id}
    </select>
    <select id="findLikeName" resultType="account" parameterType="string">
        select * from account where name like #{name}
    </select>
    <select id="findTotal" resultType="int">
        select count(*) from account
    </select>

    <insert id="saveAccount" parameterType="account">
        insert into account(name,money) values(#{name},#{money})
    </insert>
    <update id="updateAccount" parameterType="account">
        update account set money = #{money} where id = #{id}
    </update>
    <delete id="deleteAccount" parameterType="int">
        delete from account where id = #{id}
    </delete>
</mapper>

3. 基于注解的Mybatis

3.1 在AccountDao.java添加注解

public interface AccountDao {

    /**
     * 查找所有账户
     * @return
     */
    @Select("select * from account")
    @Results(id = "accountMap" ,value = {
            // column:数据库表的列名 property: javaBean的成员属性名
            @Result(id = true,column = "id",property ="id" ),
            @Result(column = "name",property = "name"),
            @Result(column = "money",property = "money")
    })
    List<Account> findAll();

    /**
     * 通过id,查找账户
     * @param id
     * @return
     */
    @Select("select * from account where id = #{id}")
    @ResultMap("accountMap")
    Account findByID(int id);

    /**
     * 通过名字模糊查询
     * @param name
     * @return
     */
    @Select("select * from account where name like #{name}")
    @ResultMap("accountMap")
    List<Account> findLikeName(String name);

    /**
     * 账户个数
     * @return
     */
    @Select("select count(*) from account")
    int findTotal();

    /**
     * 新增账户
     * @param account
     * @return
     */
    @Insert("insert into account(name,money) values(#{name},#{money})")
    int saveAccount(Account account);

    /**
     * 更新账户money
     * @param account
     * @return
     */
    @Update("update account set money = #{money} where id = #{id}")
    int updateAccount(Account account);

    /**
     * 通过id删除账户
     * @param id
     * @return
     */
    @Delete("delete from account where id = #{id}")
    int deleteAccount(int id);

}