mybatis连接mysql的使用方法

mybatis连接mysql的使用方法

一、安装：

maven仓库导入mybatis、mysql依赖即可

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>

二、构建SqlSessionFactory

MyBatis应用程序都是以一个SQLSessionFactory的实例为核心的

SqlSessionFactory实例通过SqlSessionFactoryBuilder 从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>
    <typeAliases>
        <typeAlias type="cn.xym.kgc.entity.Local" alias="Local"></typeAlias>
    </typeAliases>
    <environments default="xym">
        <environment id="xym">
            <transactionManager type="jdbc"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.221.221:3306/myshops"/>
                <property name="username" value="root"/>
                <property name="password" value="javakb10"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/local.xml"></mapper>
    </mappers>
</configuration>

其中标签关联具体的sql映射语句的文件

三、构建实体类

根据MySQL里具体的表构建实体类，每个字段对应其中的一个属性，除非建立映射关系，否则需要完全相同一一对应

public class Local {
    private Integer localid;
    private String localename;
    private Integer sublocal;
    
   	构造器、getter、setter、toString等此处省略
}

四、创建DAO接口

基本的增删改查方法

public interface LocalDAO {
    public void insertLocal(List<Local> locals);
    public void deleteLocal(int localid);
    public void updateLocal(Local local);
    public List<Local> findAll();
    public Local findById(int localid);
    public List<Local> findBy(Local local);
}

五、SQL语句映射接口方法

<?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="cn.xym.kgc.dao.LocalDAO">
    <insert id="insertLocal" parameterType="Local">
        insert into local values
        <foreach collection="list" item="lo" separator="," >
            (#{lo.localid},#{lo.localename},#{lo.sublocal})
        </foreach>
    </insert>
    <delete id="deleteLocal" parameterType="int">
        delete from local where localid=#{localid}
    </delete>
    <update id="updateLocal" parameterType="Local">
        update local set localename=#{localename} ,sublocal=#{sublocal} where localid=#{localid}
    </update>
    <select id="findAll" resultType="Local">
        select * from local
    </select>
    <select id="findById" parameterType="int" resultType="Local">
        select * from local where localid=#{localid}
    </select>
    <select id="findBy" parameterType="Local" resultType="Local">
        select * from local
        <where>
            <if test="null != localid">
                and localid=#{localid}
            </if>
            <if test="null != localid">
                and localname=#{localname}
            </if>
            <if test="null != localid">
                and sublocal=#{sublocal}
            </if>
        </where>
    </select>
</mapper>

六、测试类

1、读取配置文件获取数据库操作接口实例

//获取一个Resources，读取配置文件
Reader reader = Resources.getResourceAsReader("mybatis.cfg.xml");
//根据配置获得数据库操作会话工厂
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(reader);
//根据操作工厂生成操作对话
SqlSession session = factory.openSession();
//根据会话生成对应的数据库操作接口实例
LocalDAO ldao = session.getMapper(LocalDAO.class);

2、测试插入案例

Local loc = new Local();
loc.setLocalid(25);
loc.setLocalename("扬州");
loc.setSublocal(2);
ldao.insertLocal(loc);
session.commit();

3、在执行插入、删除、修改方法后，需要commit提交