Maven&&Mybatis

数据库

发布日期: 2023-05-08

文章字数: 2.1k

Maven 常用命令

compile ：编译

clean：清理

test：测试

package：打包

install：安装

Mybatis入门及其开发配置

https://mybatis.org/mybatis-3/zh/sqlmap-xml.html

不采用Mapper代理开发

    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    //2. 获取SqlSession对象，用它来执行sql
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //3. 执行sql
    List<User> users = sqlSession.selectList("test.selectAll");
    System.out.println(users);
    //4. 释放资源
    sqlSession.close();

采用Mapper代理开发

    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    //2. 获取SqlSession对象，用它来执行sql
    SqlSession sqlSession = sqlSessionFactory.openSession();
    /*//3. 执行sql
    List<User> users = sqlSession.selectList("test.selectAll");*/

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    List<User> users = mapper.selectAll();

    System.out.println(users);
    //4. 释放资源
    sqlSession.close();

使用Mapper代理方式，必须满足以下要求：

定义与SQL映射文件同名的Mapper接口，并且将Mapper接口和SQL映射文件放置在同一目录下。

设置SQL映射文件的namespace属性为Mapper接口全限定名

<mapper namespace="com.kang.mapper.UserMapper" >
<select id="selectAll" resultType="com.kang.pojo.User">
    select * from tb_user;
</select>
</mapper>


public interface UserMapper {
List<User> selectAll();

}

在 Mapper 接口中定义方法，方法名就是SQL映射文件中sql语句的id，并保持参数类型和返回值类型一致

使用 mybatis 动态代理的方式可以自动生成dao接口实现类，不用自己实现dao接口类，简化编程

前提:

(1)保证mapper.xml文件的namespace和dao接口的类路径一致
(2)mapper.xml文件中配置的id和dao接口定义的方法的名称一致

Mybatis CRUD

首先获取sqlsession对象

//获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动提交事务，相当于关闭事务
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

mapper去执行相应的方法

   mapper.add(brand);

BrandMapper接口

public interface BrandMapper {
/**
 * 查询所有
 *
 * @return
 */
List<Brand> selectAll();

/**
 * 根据id查询对象
 *
 * @param id
 * @return
 */
Brand selectAllByIdBrand(int id);

/**
 * 动态查询
 *
 * @param map
 * @return
 */
//List<Brand> selectCondition(@Param("status") int status, @Param("companyName")String companyName, @Param("brandName") String brandName);
List<Brand> selectCondition(Map map);

/**
 * 单条件动态查询
 *
 * @param brand
 * @return
 */
List<Brand> selectConditionSingle(Brand brand);

/*
添加
 */
void add(Brand brand);


/*
动态更新
 */
int updateBrand(Brand brand);


/*
根据id删除
 */
int deleteByIdInt(int id);

/**
 * 批量删除
 *
 * @param ids
 * @return
 */
//int deleteByIds(@Param("ids") int[] ids);
int deleteByIds(int[] ids);

BrandMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


 <mapper namespace="com.kang.mapper.BrandMapper">

<resultMap id="brandResultMap" type="com.kang.pojo.Brand">
    <result column="brand_name" property="brandName"/>
    <result column="company_name" property="companyName"/>
    <!--        id主键名。result普通-->
</resultMap>


<!--    resultMap-->
<!--    定义resultMap标签-->
<!--    在select语句中用resultMap代替resultType-->

<!--    查询所有-->
<select id="selectAll" resultMap="brandResultMap">
    select * from tb_brand;
</select>

<!--    根据id查询品牌信息-->
<!--    parameterType指定传入参数类型-->
<!--<select id="selectAllByIdBrand" parameterType="int" resultMap="brandResultMap">
    select * from tb_brand where id = #{id}

</select>-->

<!--    特殊字符比如 < 写转义字符&lt;或者CDATA区-->
<select id="selectAllByIdBrand" parameterType="int" resultMap="brandResultMap">
    select * from tb_brand where id
    <![CDATA[
    <
    ]]>
    #{id}

</select>


<!--    条件查询-->
<!--    <select id="selectCondition" resultMap="brandResultMap">-->
<!--        select * from tb_brand-->
<!--        where status = #{status}-->
<!--        and company_name like #{companyName}-->
<!--        and brand_name like #{brandName};-->
<!--    </select>-->

<!--    动态条件查询-->
<select id="selectCondition" resultMap="brandResultMap">
    select * from tb_brand
    <where>
        <if test="status !=null">
            status = #{status}
        </if>
        <if test="companyName !=null and companyName != ''">
            and company_name like #{companyName}
        </if>
        <if test="brandName !=null and brandName != ''">
            and brand_name like #{brandName};
        </if>
    </where>
</select>
<select id="selectConditionSingle" resultType="com.kang.pojo.Brand">
    select * from tb_brand
    <where>
        <choose>
            <when test="status !=null">
                status = #{status}
            </when>
            <when test="companyName !=null and companyName != ''">
                and company_name like #{companyName}
            </when>
            <when test="brandName !=null and brandName != ''">
                and company_name like #{companyName}
            </when>
            <!--<otherwise>
                1=1
            </otherwise>-->
        </choose>
    </where>
</select>

<!--返回添加数据的主键 useGeneratedKeys   keyProperty-->
<insert id="add" useGeneratedKeys="true" keyProperty="id">
    insert into tb_brand(id,brand_name,company_name,ordered,description,status) value
    (#{id},#{brandName},#{companyName},#{ordered},#{description},#{status});
</insert>

<update id="updateBrand">
    update tb_brand
    <set>
        <if test="status !=null">
            status = #{status},
        </if>
        <if test="companyName !=null and companyName != ''">
            company_name = #{companyName},
        </if>
        <if test="brandName !=null and brandName != ''">
            brand_name = #{brandName},
        </if>
        <if test="ordered!=null">
            ordered =#{ordered},
        </if>
        <if test="description!=null and description != ''">
            description =#{description}
        </if>
    </set>
    where id = #{id}
</update>


<delete id="deleteByIdInt">
    delete from tb_brand where id = #{id};
</delete>
<!--    collection应为array名称,写ids需要前面加param-->
<delete id="deleteByIds">
    delete from tb_brand where id in
    <foreach collection="array" item="id" open=" (" close=")" separator=",">
        #{id}
    </foreach>
    ;
 </delete>

</mapper>

MybatisTest

public class MybatisTest {
@Test
public void selectAll() throws IOException {
    //获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

    List<Brand> brands = mapper.selectAll();
    System.out.println(brands);
    sqlSession.close();
}

@Test
public void selectAllById() throws IOException {
    int id = 1;
    //获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

    Brand brands = mapper.selectAllByIdBrand(id);
    System.out.println(brands);
    sqlSession.close();
}

@Test
public void selectCondition() throws IOException {
    int status = 1;
    String companyName = "华为";
    String brandName = "华为";

   //处理参数
    companyName = "%" + companyName + "%";
    brandName = "%" + brandName + "%";

    Map map = new HashMap<>();
    map.put("status", status);
    map.put("companyName", companyName);
    map.put("brandName", brandName);

    //获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

    //List<Brand> brands = mapper.selectCondition(status,companyName,brandName);
    List<Brand> brands = mapper.selectCondition(map);

    System.out.println(brands);
    sqlSession.close();
}


@Test
public void selectConditionSingle() throws IOException {
    int status = 1;
    String companyName = "华为";
    String brandName = "华为";

   //处理参数
    companyName = "%" + companyName + "%";
    brandName = "%" + brandName + "%";

    /*Map map = new HashMap<>();
    map.put("status",status);
    map.put("companyName",companyName);
    map.put("brandName",brandName);*/
    Brand brand = new Brand();
    //brand.setStatus(status);
    //  brand.setCompanyName(companyName);
    //brand.setBrandName(brandName);


    //获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

    //List<Brand> brands = mapper.selectCondition(status,companyName,brandName);
    List<Brand> brands = mapper.selectConditionSingle(brand);

    System.out.println(brands);
    sqlSession.close();
}


@Test
public void add() throws IOException {
    String companyName = "不哦里哦";
    String brandName = "菠萝";
    int ordered = 500;
    String description = "米家无敌";
    int status = 1;

    Brand brand = new Brand();
    brand.setStatus(status);
    brand.setCompanyName(companyName);
    //brand.setId(id);
    brand.setBrandName(brandName);
    brand.setOrdered(ordered);
    brand.setDescription(description);


    //获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);//自动提交事务，相当于关闭事务
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

    mapper.add(brand);
    //提交事务
    // sqlSession.commit();

    sqlSession.close();
}


@Test
public void updateBrand() throws IOException {
    String companyName = "不哦里哦";
    String brandName = "菠萝";
    int ordered = 500;
    String description = "菠萝手机";
    int status = 1;
    int id = 6;

    Brand brand = new Brand();
    brand.setStatus(status);
    brand.setCompanyName(companyName);
    brand.setBrandName(brandName);
    brand.setOrdered(ordered);
    brand.setDescription(description);
    brand.setId(id);


    //获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession();//自动提交事务，相当于关闭事务
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

    int count = mapper.updateBrand(brand);
    //提交事务
    // sqlSession.commit();
    System.out.println(count);
    sqlSession.commit();
    sqlSession.close();
}

@Test
public void deleteByIdInt() throws IOException {
    int id = 6;
    //获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

    int count = mapper.deleteByIdInt(id);
    System.out.println(count);
    sqlSession.commit();
    sqlSession.close();
}

@Test
public void deleteByIds() throws IOException {
    int[] id = {2, 3};
    //获取sqlsession对象
    //1. 加载mybatis的核心配置文件，获取 SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //获取 mapper接口的代理对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

    int count = mapper.deleteByIds(id);
    System.out.println(count);
    sqlSession.commit();
    sqlSession.close();
}

@Test
public void test() throws FileNotFoundException {
    PrintStream printStream = new PrintStream("d:\\d1.txt");

    System.setOut(printStream);
    printStream.println("你好");
    System.setOut(new PrintStream(new FileOutputStream(FileDescriptor.out)));//强制重定向到控制台
    System.out.println("你好");

 }


}

是小康呀~

http://jiuwei2003.github.io/2023/05/08/maven-mybatis/