MyBatis的CRUD操作

MyBatis的两个主要配置文件

mytatis.xml:放在src目录下，常见的配置如下

<?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>
　　<!--1-->
    <properties resource="db.properties"/>
　　<!--2-->
    <typeAliases>
        <typeAlias type="com.winner.entity.Student" alias="Student"/>
    </typeAliases>
　　<!--3-->
    <environments default="mysql_developer">
        <environment id="mysql_developer">
        <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
        <environment id="oracle_developer">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl"/>
                <property name="username" value="scott"/>
                <property name="password" value="tiger"/>
            </dataSource>
        </environment>
    </environments>
　　<!--4-->
    <mappers>
        <mapper resource="com/winner/entity/StudentMapper.xml"/>
    </mappers>
</configuration>

 SQL语句后面不要有;

StudentMapper.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">

<!--namespace写成类的全限定名有好处，在Dao中方便-->
<mapper namespace="com.winner.entity.Student">

    <!--type是类的全限定名，因为mybatis.xml中有别名的设置，所以用别名，短，方便-->
    <resultMap id="studentMap" type="Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sal" column="sal"/>
    </resultMap>

    <!--方法无参数-->
    <insert id="add1">
      <![CDATA[
        INSERT INTO student(id,name,sal) VALUES (1,"zhangsan",2000)
      ]]>
    </insert>

    <!--type是类的全限定名，因为mybatis.xml中有别名的设置，所以用别名，短，方便-->
    <insert id="add2" parameterType="Student">
        <![CDATA[
        INSERT INTO student(id,name,sal) VALUES (#{id},#{name},#{sal})
      ]]>
    </insert>
</mapper>

1.增加

StudentMapper.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">

<!--namespace写成类的全限定名有好处，在Dao中方便-->
<mapper namespace="com.winner.entity.Student">

    <!--type是类的全限定名，因为mybatis.xml中有别名的设置，所以用别名，短，方便-->
    <resultMap id="studentMap" type="Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sal" column="sal"/>
    </resultMap>

    <!--type是类的全限定名，因为mybatis.xml中有别名的设置，所以用别名，短，方便-->
    <insert id="add" parameterType="Student">
        <![CDATA[
        INSERT INTO student(id,name,sal) VALUES (#{id},#{name},#{sal})
      ]]>
    </insert>
</mapper>

 

public class StudentDao {

    /**
     * 添加学生
     */
    public void add(Student student) throws Exception{
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtil.getSqlSession();
            //事务开始（默认）
            //读取StudentMapper.xml映射文件中的SQL语句
            //insert的第一个参数:
            // namespace写实体类的全限定名就是这个好处
            sqlSession.insert(Student.class.getName() + ".add", student);
            //事务提交
            sqlSession.commit();
        }catch (Exception e){
            e.printStackTrace();
            //事务回滚
            sqlSession.rollback();
            throw e;
        }finally {
            MybatisUtil.closeSqlSession();
        }
    }

    public static void main(String[] args) throws Exception{
        StudentDao dao = new StudentDao();
        dao.add(new Student(1,"zhansan",1000d));
    }
}

2.根据ID查询学生

<!-- 
　　根据ID查询学生
   如果参数不是一个实体的话，只是一个普通变量，例如:int,double,String
   这里的#{中间的变量名可以随便写}，不过提倡就用方法的形参.
   resultType是方法返回值类型
-->
<select id="findById" parameterType="int" resultType="Student">
   SELECT id,name,sal FROM student WHERE id=#{id}
</select>

 

/**
 * 根据ID查询学生
*/
public Student findById(int id) throws Exception{
    SqlSession sqlSession = null;
    try{
        sqlSession = MybatisUtil.getSqlSession();
        Student student = sqlSession.selectOne(Student.class.getName() + ".findById", id);
        sqlSession.commit();
        return student;
    }catch (Exception e){
        e.printStackTrace();
        //事务回滚
        sqlSession.rollback();
        throw e;
    }finally {
        MybatisUtil.closeSqlSession();
    }
}

3.查询所有

<!-- 查询所有学生 
     理论上resultType要写List<Student>
     但这里只需书写List中的类型即可，即只需书写Student的全路径名
-->
<select id="findAll" resultType="student">
    SELECT id,name,sal FROM student
</select>

/**
 * 查询所有学生
 */
public List<Student> findAll() throws Exception{
    SqlSession sqlSession = null;
    try{
        sqlSession = MybatisUtil.getSqlSession();
        return sqlSession.selectList(Student.class.getName()+".findAll");
    }catch(Exception e){
        e.printStackTrace();
        throw e;
    }finally{
        MybatisUtil.closeSqlSession();
    }
}

4.更新学生

<!-- 更新学生 -->
<update id="update" parameterType="Student">
　　UPDATE student SET name=#{name},sal=#{sal} WHERE id=#{id}
</update>

/**
* 更新学生
*/
public void update(Student student) throws Exception{
    SqlSession sqlSession = null;
    try{
        sqlSession = MybatisUtil.getSqlSession();
        sqlSession.update(Student.class.getName()+".update",student);
        sqlSession.commit();
    }catch(Exception e){
        e.printStackTrace();
        sqlSession.rollback();
        throw e;
    }finally{
        MybatisUtil.closeSqlSession();
    }
 }

Student student = studentDao.findById(3);
student.setName("wangwuwu");
studentDao.update(student);

5.删除

<!-- 删除学生 -->
<delete id="delete" parameterType="student">
　　DELETE FROM student WHERE id = #{id}
</delete>

/**
 * 删除学生
*/
public void delete(Student student) throws Exception{
    SqlSession sqlSession = null;
    try{
        sqlSession = MybatisUtil.getSqlSession();
        sqlSession.delete(Student.class.getName()+".delete",student);
        sqlSession.commit();
    }catch(Exception e){
        e.printStackTrace();
        sqlSession.rollback();
        throw e;
    }finally{
        MybatisUtil.closeSqlSession();
    }
}

Student student = dao.findById(3);
dao.delete(student);