【SSM-Mybatis】动态SQL语句

1.创建对应数据表的类

public class Category {
    private int id;
    private String name;

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

    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;
    }
}

2.创建写Sql语句的类

public class CategorySqlk {
    //增
    public String insert(){
        return new SQL()
                .INSERT_INTO("category_")
                .VALUES("name","#{name}")
                .toString();
    }
    //删
    public String delete(){
        return new SQL()
                .DELETE_FROM("category_")
                .WHERE("id = #{id}")
                .toString();
    }

    //改
    public String update(){
        return new SQL()
                .UPDATE("category_")
                .SET("name = #{name}")
                .WHERE("id = #{id}")
                .toString();
    }
    //查
    public String list(){
        return new SQL()
                .SELECT("*")
                .FROM("category_")
                .toString();
    }


}

3.创建对应的接口 mapper

public interface CategoryMapper {
    @InsertProvider(type = CategorySqlk.class,method = "insert")
    public void insert(Category category);
    @DeleteProvider(type = CategorySqlk.class,method = "delete")
    public void delete(int id);

    @SelectProvider(type = CategorySqlk.class,method ="list")
    public List<Category> list();

    @UpdateProvider(type = CategorySqlk.class,method = "update")
    public void update(Category category);
}

4.mybatis.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>

  <!--  使用LOG4J-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>

        <!--别名-->
    <typeAliases>
        <package name="M2.pojo"/>
    </typeAliases>


        <!-- environmengs
            transactionMapper
            dataSource
        -->

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/how2java?useSSL=false
&amp;serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="   "/>
            </dataSource>
        </environment>
    </environments>
    
    <mappers>
        <mapper class="M2.mapper.CategoryMapper"/>
    </mappers>
</configuration>

5.Test

public class SqlTest {

    @Test
    public void test1() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession();
        CategoryMapper mapper = session.getMapper(CategoryMapper.class);

        //SqlUpdate(mapper);
        //SqlInsert(mapper);
        SqlDelete(mapper,12);


        session.commit();
        session.close();

    }
    private static void SqlInsert(CategoryMapper mapper){
        Category category =new Category();
        category.setName("insert for sql");
        category.setId(4);
        mapper.insert(category);
        SqlList(mapper);
    }

    private static void SqlDelete(CategoryMapper mapper,int id){
        mapper.delete(id);
        SqlList(mapper);
    }

    private static void SqlUpdate(CategoryMapper mapper){
        Category category =new Category();
        category.setName("hello Sql");
        category.setId(1);
        mapper.update(category);

        SqlList(mapper);
    }

    private static void SqlList(CategoryMapper mapper){
        List<Category> cs = mapper.list();
        for (Category c:cs
        ) {
            System.out.println(c);
        }
    }
}