nutz如何实现三表联查，并将查询的字段存储到第四章表中

wendal

1楼•2753天前

用insert into xxxx select 语法？

萧仁武(qq_e6c6e9d2) 2楼•2753天前

这三表联查的结果是一个集合，多条数据，直接insert into能加到数据库中吗？

wendal

3楼•2753天前

不清楚，试一下吧

萧仁武(qq_e6c6e9d2) 4楼•2753天前

public void addToShopDetail(@Param("..")TbAgentShopDetail tbAgentShopDetail) {
		// 三表联查，获取相应字段
		Sql sql = Sqls.queryRecord("SELECT s.shop_id, s.shop_name, p.pro_id, p.inventory, u.user_type "
				+ "FROM tb_user u "
				+ "INNER JOIN tb_shop s ON u.user_id=s.user_id "
				+ "INNER JOIN tbt_product p ON u.user_type=p.user_type");
		dao.execute(sql);
		List<Record> list = sql.getList(Record.class);
		
		for (Record record : list) {
			Integer shop_id = record.getInt("s.shop_id");
			String shop_name = record.getString("s.shop_name");
			Integer pro_id = record.getInt("p.pro_id");
			Integer inventory = record.getInt("p.inventory");
			
			Cnd cnd = Cnd.where(TbAgentShopDetail.SHOP_ID, "=", shop_id);
			cnd.and(TbAgentShopDetail.SHOP_NAME, "=", shop_name);
			cnd.and(TbAgentShopDetail.PRO_ID, "=", pro_id);
			cnd.and(TbAgentShopDetail.INVENTORY, "=", inventory);
			if (tbAgentShopDetailDao.count(cnd) > 0) {
				return;
			}else {
				tbAgentShopDetail.setShop_id(shop_id);
				tbAgentShopDetail.setShop_name(shop_name);
				tbAgentShopDetail.setPro_id(pro_id);
				tbAgentShopDetail.setInventory(inventory);
				// 获取当前时间
				tbAgentShopDetail.setCreate_time(BurroKit.current());
				tbAgentShopDetailDao.fastInsert(tbAgentShopDetail);
			}
		}
	}

在tbAgentShopDetail.setShop_id(shop_id);处报空指针异常，是怎么回事？是for (Record record : list) {}里面

Integer shop_id = record.getInt("s.shop_id");
String shop_name = record.getString("s.shop_name");
Integer pro_id = record.getInt("p.pro_id");
Integer inventory = record.getInt("p.inventory");

这里有问题吗？应该怎么改？

wendal

5楼•2753天前

shop_id是null，打印一下record的内容你就懂了

萧仁武(qq_e6c6e9d2) 6楼•2753天前

我打印record有值

[{
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :30,
   "inventory" :100,
   "user_type" :"初级代理商"
}, {
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :4,
   "inventory" :56,
   "user_type" :"初级代理商"
}, {
   "shop_id" :1,
   "shop_name" :"11",
   "pro_id" :4,
   "inventory" :44,
   "user_type" :"中级代理商"
}, {
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :16,
   "inventory" :22,
   "user_type" :"初级代理商"
}, {
   "shop_id" :1,
   "shop_name" :"11",
   "pro_id" :30,
   "inventory" :11,
   "user_type" :"中级代理商"
}, {
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :21,
   "inventory" :0,
   "user_type" :"初级代理商"
}, {
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :11,
   "inventory" :0,
   "user_type" :"初级代理商"
}, {
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :1,
   "inventory" :0,
   "user_type" :"初级代理商"
}, {
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :2,
   "inventory" :0,
   "user_type" :"初级代理商"
}, {
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :10,
   "inventory" :0,
   "user_type" :"初级代理商"
}, {
   "shop_id" :1,
   "shop_name" :"11",
   "pro_id" :9,
   "inventory" :0,
   "user_type" :"中级代理商"
}, {
   "shop_id" :1,
   "shop_name" :"11",
   "pro_id" :8,
   "inventory" :0,
   "user_type" :"中级代理商"
}, {
   "shop_id" :1,
   "shop_name" :"11",
   "pro_id" :2,
   "inventory" :0,
   "user_type" :"中级代理商"
}, {
   "shop_id" :1,
   "shop_name" :"11",
   "pro_id" :16,
   "inventory" :0,
   "user_type" :"中级代理商"
}, {
   "shop_id" :1,
   "shop_name" :"11",
   "pro_id" :10,
   "inventory" :0,
   "user_type" :"中级代理商"
}, {
   "shop_id" :1,
   "shop_name" :"11",
   "pro_id" :3,
   "inventory" :0,
   "user_type" :"中级代理商"
}, {
   "shop_id" :17,
   "shop_name" :"111",
   "pro_id" :3,
   "inventory" :0,
   "user_type" :"初级代理商"

报错是在if条件的else语句的第一行

tbAgentShopDetail.setShop_id(shop_id);

是地方

wendal

7楼•2753天前

key写错，这样才对

Integer shop_id = record.getInt("shop_id");

萧仁武(qq_e6c6e9d2) 8楼•2753天前

这里我打印出record时就改过了，还是在if语句的else处报空指针异常

wendal

9楼•2753天前

贴现在的代码

wendal

10楼•2753天前

另外，既然是空指针，把抛错的那一行用到的变量通通打印一下就明了了

萧仁武(qq_e6c6e9d2) 11楼•2753天前

嗯，已经找到了，参数实例对象去掉加@Param，在调用处添加就没问题了