作业5,6 2019/10/23

上代码:

IgoodsMapper接口:

  @Select("SELECT * FROM provider p , goods g WHERE p.provider_id = #{provider_id} AND p.provider_id = g.provider_id")
    List<Provider> getProviderlistById(Map <String,Integer> map);

测试类:

package com.demo.test;

import com.pojo.Demo;
import com.pojo.Goods;
import com.pojo.Grade;
import com.pojo.Provider;
import com.test.DemoMapper;
import com.test.IgoodsMapper;
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.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Test01 {
    public static void main(String[] args) throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        //2、创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3、创建 SqlSession对象
        SqlSession sqlSession = factory.openSession();
        //4、实现方法
        IgoodsMapper mapper = sqlSession.getMapper(IgoodsMapper.class);

        Map<String,Integer > map = new HashMap<String, Integer>();
        map.put("provider_id",3001);
        List<Provider> list = sqlSession.selectList("getProviderlistById", map);

        for (Provider provider:list){
            System.out.println(provider.getProvider_id()+" "
                    +provider.getProvider_code()+" "
                    +provider.getProvider_name()+" "+provider.getProvider_person()
                    +" "+provider.getProvider_phone()+" "+provider.getGoods_code()
                    +" "+provider.getGoods_name()+" "+provider.getMoney()+" "
                    +provider.getPay_state());
        }

//        if(i>0){
//            sqlSession.commit();
//            System.out.println("删除成功");
//        }else{
//            System.out.println("删除失败");
//        }
        //5、释放资源
        sqlSession.close();
        in.close();

    }

}

效果:

-------------------------------------------------------------------------------------------------------------

因为这个跟之前的多表查询几乎一样

所以我就在原来代码进行简单修改:

IgoodsMapper接口:

  @Select("SELECT * FROM provider p , goods g WHERE g.goods_name LIKE concat('%',#{goods_name},'%') AND p.provider_id = g.provider_id")
    List<Provider> getProviderlistByLikeName(Map <String,String> map);

测试类:

package com.demo.test;

import com.pojo.Demo;
import com.pojo.Goods;
import com.pojo.Grade;
import com.pojo.Provider;
import com.test.DemoMapper;
import com.test.IgoodsMapper;
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.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Test01 {
    public static void main(String[] args) throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        //2、创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3、创建 SqlSession对象
        SqlSession sqlSession = factory.openSession();
        //4、实现方法
        IgoodsMapper mapper = sqlSession.getMapper(IgoodsMapper.class);

        Map<String,String> map = new HashMap<String, String >();
        map.put("goods_name","酒");
        List<Provider> list = sqlSession.selectList("getProviderlistByLikeName", map);

        for (Provider provider:list){
            System.out.println(provider.getProvider_id()+" "
                    +provider.getProvider_code()+" "
                    +provider.getProvider_name()+" "+provider.getProvider_person()
                    +" "+provider.getProvider_phone()+" "+provider.getGoods_code()
                    +" "+provider.getGoods_name()+" "+provider.getMoney()+" "
                    +provider.getPay_state());
        }

//        if(i>0){
//            sqlSession.commit();
//            System.out.println("删除成功");
//        }else{
//            System.out.println("删除失败");
//        }
        //5、释放资源
        sqlSession.close();
        in.close();

    }

}

结果:

 ------------------------------------------------------------------------------------------------------------------