Intellij Mybatis连接Mysql数据库,并且实现动态sql,增删改查等

本文介绍了使用MyBatis框架进行数据库操作的方法,包括配置、持久化对象操作、动态SQL等,并提供了详细的代码示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

因为昨天看视频学习来着,然后偷了一下懒只更新了一下junit(工欲善其事必先利其器),今天这个估计有点多,文章有点长
先看一下现在的目录结构,因为现在学的是对mysql的操作,抽象一下就是对持久化对象的操作,而且感觉学这个加深对面向对象的更加深入的理解。
这里写图片描述
ok,原来的文件基本没有动地方,动地方的我说一下,数据库配置,从xml配置到了properties中了,简单来说就是高内聚低耦合,这里新建了一个mapper目录,里面写的是接口,接口中的方法对应着xml中的方法,即id,等一会把代码贴出来相信大家就理解了,还有就是建议放在一个包下,因为设置的时候进行映射有包名映射,这样一次配置就可以用了,而且也比较简单,防止自己遗漏。再来说一下model,系统肯定不是编写一次就一成不变了,肯定后期要进行维护,所以这里虽然对model进行了扩展但并不是暴力的在源文件中进行修改,而且新建了一个java文件更利于扩展,剩下的就不多说了,我写的注释挺详细的。因为我也是初学,不会的先看注释还不会的话可以留言,我能解决的话就会回复的。
好了,上代码(用的到的会贴上的,有的是正在学,写了一半的代码就不贴了,希望谅解,完善后我会贴上github地址):
还是老规矩,我按照正常的逻辑顺序上代码(前提是数据库都创建好了的)

user.java

package com.fanyafeng.model;

import java.util.Date;

/**
 * Author: fanyafeng
 * Data: 16/10/11 14:56
 * Email: fanyafeng@live.cn
 */
public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    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 Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

usercustom.java

package com.fanyafeng.model;

/**
 * Author: fanyafeng
 * Data: 16/10/12 16:54
 * Email: fanyafeng@live.cn
 */
public class UserCustom extends User {
}

iusermapper.xml

package com.fanyafeng.mapper;

import com.fanyafeng.model.User;
import com.fanyafeng.model.UserCustom;
import com.fanyafeng.model.UserQueryVo;

import java.util.List;

/**
 * Author: fanyafeng
 * Data: 16/10/11 14:55
 * Email: fanyafeng@live.cn
 */
public interface IUserMapper {

//    只能传一个参数,当需要多个参数时,可以采用pojo

    public List<User> queryUserByName(String name);

    public User selectUserById(int id);

    public void add();

    public void del(int id);

    public void alter(int id);

    public List<UserCustom> findUserByUserQuery(UserQueryVo userQueryVo);

    public int findUserCount(UserQueryVo userQueryVo);

    public User findUserByIdResultMap(int id);

    public List<UserCustom> findUserList(UserQueryVo userQueryVo);

    public List<UserCustom> queryUserList(UserQueryVo userQueryVo);
}

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

<!--resultType 指定单条记录返回的类型-->

<mapper namespace="com.fanyafeng.dao.IUserDao">
    <select id="selectUserById" parameterType="int" resultType="com.fanyafeng.model.User">
        select * from user where id = #{id}
    </select>

    <!--'%${value}' 表示拼接sql串,只能使用value,有可能会引起sql注入的情况-->
    <select id="queryUserByName" parameterType="String" resultType="com.fanyafeng.model.User">
        SELECT * FROM user WHERE username LIKE #{name}
    </select>

    <!--INSERT INTO USER (id,username,sex,address) VALUE (null,"陈潇然","女","大学同学")-->
    <!-- SELECT LAST_INSERT_ID() 得到刚插入数据库的记录的主键值,只适用于自增主键
         keyProperty:将查询到主键值设置到相应的 parameterType 属性中
         order 相对于执行的sql语句的顺序-->
    <insert id="add" parameterType="com.fanyafeng.model.User">
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            SELECT LAST_INSERT_ID()
        </selectKey>

        insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
    </insert>
    <!--非自增的id-->
    <!-- <selectKey keyProperty="id" order="BEFORE" resultType="String">
            SELECT uuid();
        </selectKey>
        insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
    -->

    <!--根据id进行删除-->
    <delete id="del" parameterType="int">
        DELETE FROM user WHERE id = #{id}
    </delete>

    <!--根据id更新数据库-->
    <update id="alter" parameterType="com.fanyafeng.model.User">
        UPDATE USER  SET username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} WHERE id = #{id}
    </update>
</mapper>

userqueryvo.java

package com.fanyafeng.model;

import java.util.List;

/**
 * Author: fanyafeng
 * Data: 16/10/12 16:52
 * Email: fanyafeng@live.cn
 */
public class UserQueryVo {

    private List<Integer> ids;

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

    private UserCustom userCustom;

    public UserCustom getUserCustom() {
        return userCustom;
    }

    public void setUserCustom(UserCustom userCustom) {
        this.userCustom = userCustom;
    }
}

这里说一下,刚开始的文件没有更改目录名称,后面的为了更容易理解,还有就是符合自己的风格,我进行了更改,接口文件和xml放在了同一个目录下了,iusermapper和user用的是同一个xml,这里注意里面的命名空间
iusermapper.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">

<!--resultType 指定单条记录返回的类型-->

<mapper namespace="com.fanyafeng.mapper.IUserMapper">


    <sql id="queryUser">
        <if test="userCustom!=null">
            <if test="userCustom.sex!=null and userCustom.sex!=''">
                AND user.sex=#{userCustom.sex}
            </if>
            <if test="userCustom.username!=null and userCustom.username!=''">
                AND user.username=#{userCustom.username}
            </if>
            -- collection:指定输入对象中集合属性
            -- item:每个遍历生成对象对应的字段名
            -- open:开始遍历拼接的串
            -- close:结束遍历拼接的串
            -- separator:遍历的两个对象中需要拼接的串
            <if test="ids!=null">
                <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                    id=#{id}
                </foreach>
            </if>
        </if>
    </sql>

    <select id="queryUserList" parameterType="com.fanyafeng.model.UserQueryVo"
            resultType="com.fanyafeng.model.UserCustom">
        SELECT * FROM USER
        <where>
            <include refid="queryUser"/>
        </where>
    </select>


    <!--动态sql-->
    <select id="findUserList" parameterType="com.fanyafeng.model.UserQueryVo"
            resultType="com.fanyafeng.model.UserCustom">
        SELECT * FROM USER
        <where>
            <if test="userCustom!=null">
                <if test="userCustom.sex!=null and userCustom.sex!=''">
                    AND user.sex=#{userCustom.sex}
                </if>
                <if test="userCustom.username!=null and userCustom.username!=''">
                    AND user.username=#{userCustom.username}
                </if>
            </if>
        </where>
    </select>

    <!--定义resultMap
        type:resultMap最终映射的java对象
        id:对resultMap的唯一标识
        -->
    <resultMap type="com.fanyafeng.model.User" id="userResultMap">
        <!--id表示查询结果中的唯一标识
            coumn:查询出来的列名
            property:type指定的pojo类型中德属性名
            最终resultmap对colu和property做一个映射关系-->
        <id column="id_" property="id"/>
        <!--
            result:对普通映射的定义-->
        <result column="username_" property="username"/>
    </resultMap>

    <select id="findUserByIdResultMap" parameterType="int" resultMap="userResultMap">
        SELECT id id_,username username_ FROM user WHERE id=#{VALUE}
    </select>

    <!--
        user.username:pojo的映射
    -->
    <select id="findUserByUserQuery" parameterType="com.fanyafeng.model.UserQueryVo"
            resultType="com.fanyafeng.model.UserCustom">
        SELECT * FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}'
    </select>

    <!--获取查询数量-->
    <select id="findUserCount" parameterType="com.fanyafeng.model.UserQueryVo" resultType="int">
        SELECT COUNT(*) FROM USER WHERE user.sex=#{userCustom.sex} AND user.username LIKE '%${userCustom.username}'
    </select>

    <select id="selectUserById" parameterType="int" resultType="com.fanyafeng.model.User">
        select * from user where id = #{id}
    </select>

    <!--'%${value}' 表示拼接sql串,只能使用value,有可能会引起sql注入的情况-->
    <select id="queryUserByName" parameterType="String" resultType="com.fanyafeng.model.User">
        SELECT * FROM user WHERE username LIKE #{name}
    </select>

    <!--INSERT INTO USER (id,username,sex,address) VALUE (null,"陈潇然","女","大学同学")-->
    <!-- SELECT LAST_INSERT_ID() 得到刚插入数据库的记录的主键值,只适用于自增主键
         keyProperty:将查询到主键值设置到相应的 parameterType 属性中
         order 相对于执行的sql语句的顺序-->
    <insert id="add" parameterType="com.fanyafeng.model.User">
        <selectKey keyProperty="id" order="AFTER" resultType="int">
            SELECT LAST_INSERT_ID()
        </selectKey>

        insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
    </insert>
    <!--非自增的id-->
    <!-- <selectKey keyProperty="id" order="BEFORE" resultType="String">
            SELECT uuid();
        </selectKey>
        insert into user (id,username,birthday,sex,address) values (#{id},#{username},#{birthday},#{sex},#{address})
    -->

    <!--根据id进行删除-->
    <delete id="del" parameterType="int">
        DELETE FROM user WHERE id = #{id}
    </delete>

    <!--根据id更新数据库-->
    <update id="alter" parameterType="com.fanyafeng.model.User">
        UPDATE USER  SET username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} WHERE id = #{id}
    </update>
</mapper>

还有就是新建了dao目录,这里是ibatis的遗留问题,不难,但是我感觉有点麻烦了,可是有可能有的企业还在用,然后学了一下
iuserdaoimpl.java实现了iuserdao的接口,我记得hibernate一般都这么搞

package com.fanyafeng.dao;

import com.fanyafeng.model.User;

import java.util.List;

/**
 * Author: fanyafeng
 * Data: 16/10/11 14:55
 * Email: fanyafeng@live.cn
 */
public interface IUserDao {
    public List<User> queryUserByName(String name);

    public User selectUserById(int id);

    public void add();

    public void del(int id);

    public void alter(int id);

}

iuserdao接口实现

package com.fanyafeng.dao;

import com.fanyafeng.model.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.List;

/**
 * Author: fanyafeng
 * Data: 16/10/12 11:25
 * Email: fanyafeng@live.cn
 */
public class IUserDaoImpl implements IUserDao {

    //    需要向dao中注入sqlsessionfactory
//    通过构造方法注入session工厂
    private SqlSessionFactory sqlSessionFactory;

    public IUserDaoImpl(SqlSessionFactory sqlSessionFactory) {
        this.sqlSessionFactory = sqlSessionFactory;
    }

    public List<User> queryUserByName(String name) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        List<User> userList = sqlSession.selectList("com.fanyafeng.dao.IUserDao.queryUserByName", name);
        sqlSession.close();
        return userList;
    }

    public User selectUserById(int id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = sqlSession.selectOne("com.fanyafeng.dao.IUserDao.selectUserById", id);
        sqlSession.close();
        return user;
    }

    public void add() {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.insert("com.fanyafeng.dao.IUserDao.selectUserById");
        sqlSession.close();
    }

    public void del(int id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.delete("com.fanyafeng.dao.IUserDao.del", id);
        sqlSession.commit();
        sqlSession.close();
    }

    public void alter(int id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        sqlSession.update("com.fanyafeng.dao.IUserDao.alter", id);
        sqlSession.commit();
        sqlSession.close();
    }
}

下面虽然是进行的测试,但却是重点,很重要,这里直接加载了xml的编译文件,然后进行相应的事务操作,这里用的是junit,上文中已经说了junit的用法了,不会的话可以去谷歌,推荐类的文章都比我说的详细
先来看一下dao的实现,一会再去看直接映射的实现
dao的实现测试

package test.com.fanyafeng.dao;

import com.fanyafeng.dao.IUserDao;
import com.fanyafeng.dao.IUserDaoImpl;
import com.fanyafeng.model.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import org.junit.Before;
import org.junit.After;

import java.io.IOException;

/**
 * IUserDaoImpl Tester.
 *
 * @author <Authors name>
 * @version 1.0
 * @since <pre>十月 12, 2016</pre>
 */
public class IUserDaoImplTest {
    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void before() throws Exception {
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("Configuration.xml"));
    }

    @After
    public void after() throws Exception {
    }

    /**
     * Method: queryUserByName(String name)
     */
    @Test
    public void testQueryUserByName() throws Exception {
//TODO: Test goes here... 
    }

    /**
     * Method: selectUserById(int id)
     */
    @Test
    public void testSelectUserById() throws Exception {
//TODO: Test goes here...
        IUserDao iUserDao = new IUserDaoImpl(sqlSessionFactory);
        User user = iUserDao.selectUserById(1);
        System.out.println(user.toString());
    }

    /**
     * Method: add()
     */
    @Test
    public void testAdd() throws Exception {
//TODO: Test goes here... 
    }

    /**
     * Method: del(int id)
     */
    @Test
    public void testDel() throws Exception {
//TODO: Test goes here... 
    }

    /**
     * Method: alter(int id)
     */
    @Test
    public void testAlter() throws Exception {
//TODO: Test goes here... 
    }


} 

iusermappertest.java

package test.com.fanyafeng.mapper;

import com.fanyafeng.mapper.IUserMapper;
import com.fanyafeng.model.User;
import com.fanyafeng.model.UserCustom;
import com.fanyafeng.model.UserQueryVo;
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 java.util.ArrayList;
import java.util.List;

import static org.junit.Assert.*;

/**
 * Author: fanyafeng
 * Data: 16/10/12 14:57
 * Email: fanyafeng@live.cn
 */
public class IUserMapperTest {
    private SqlSessionFactory sqlSessionFactory;

    @Before
    public void before() throws Exception {
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("Configuration.xml"));
    }

    @Test
    public void testSelectUserById() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
//        通过mapper映射
        IUserMapper iUserMapper = sqlSession.getMapper(IUserMapper.class);
        iUserMapper.selectUserById(1);
        System.out.println(iUserMapper.selectUserById(1).toString());

        List<User> userList = iUserMapper.queryUserByName("%亚风%");
        for (int i = 0; i < userList.size(); i++) {
            System.out.println(userList.get(i).toString());
        }
    }

    @Test
    public void testFindUserByUserQuery() throws Exception {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IUserMapper iUserMapper = sqlSession.getMapper(IUserMapper.class);
        UserQueryVo userQueryVo = new UserQueryVo();
        UserCustom userCustom = new UserCustom();
        userCustom.setSex("男");
        userCustom.setUsername("李宁");
        userQueryVo.setUserCustom(userCustom);
        List<Integer> ids=new ArrayList<Integer>();
        ids.add(9);
        ids.add(10);
        ids.add(23);
        userQueryVo.setIds(ids);
        List<UserCustom> userCustomList = iUserMapper.queryUserList(userQueryVo);
        for (int i = 0; i < userCustomList.size(); i++) {
            System.out.println(userCustomList.get(i).toString());
        }

        int count = iUserMapper.findUserCount(userQueryVo);
        System.out.println("查询结果:" + count);

        User user = iUserMapper.findUserByIdResultMap(1);
        System.out.println(user.toString());
    }
}

数据库服务打开就可以测试了,这里的xml编写sql语句比在mysqlworkbench方便,本来想找几篇连贯的文章学习的,无奈没有只能硬着头皮看eclipse的学习视频然后自己在intellij上自己摸索,然后自己写文章了

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值