6.|6. Inner Join 6.InnerJoin

data[2018-12-23]
MySql Join方式

文章图片
Join.jpg 目地，了解数据库join的结果，为大数据环境下实现做准备

准备数据

userId locationId u1UT u2GA u3CA u4CA u5GA =============================================== transactionId productId userId quantity amount t1p3u11300 t2p1u21100 t3p1u11100 t4p2u2110 t5p4u419 t6p1u11100 t7p4u119 t8p4u5240

public class Users { private String userId; private String locationId; ... get/set public Users(String userId, String locationId) { this.userId = userId; this.locationId = locationId; } }

public class Transactions { private String transactionId; private String productId; private String userId; private String quantity; private String amount; ... get/set public Transactions(String transactionId, String productId, String userId, String quantity, String amount) { this.transactionId = transactionId; this.productId = productId; this.userId = userId; this.quantity = quantity; this.amount = amount; } }

junit准备数据

public static List usersList; public static List transactionsList; @Before public void setUp() { /*users*/ usersList = new ArrayList<>(); usersList.add(new Users("u1", "UT")); usersList.add(new Users("u2", "GA")); usersList.add(new Users("u3", "CA")); usersList.add(new Users("u4", "CA")); usersList.add(new Users("u5", "GA")); /*transactions*/ transactionsList = new ArrayList<>(); transactionsList.add(new Transactions("t1", "p3", "u1", "1", "300")); transactionsList.add(new Transactions("t2", "p1", "u2", "1", "100")); transactionsList.add(new Transactions("t3", "p1", "u1", "1", "100")); transactionsList.add(new Transactions("t4", "p2", "u2", "1", "10")); transactionsList.add(new Transactions("t5", "p4", "u4", "1", "9")); transactionsList.add(new Transactions("t6", "p1", "u1", "1", "100")); transactionsList.add(new Transactions("t7", "p4", "u1", "1", "9")); transactionsList.add(new Transactions("t8", "p4", "u5", "2", "40")); }

将Users 和 Transactions按userId做内连接

java实现，使用list每次遍历或者使用Map辅助 【6.|6. Inner Join】使用list类似于全表扫描，使用Map类似于走主键或者索引

@Test public void testLeftJoin() { /*users left join transactions*/ System.out.println("===========Left Join Result============"); for (Transactions transactions : transactionsList) { for (Users users : usersList) { if (Objects.equals(users.getUserId(), transactions.getUserId())) { System.out.println(transactions.getProductId() + " " + users.getLocationId()); } } } System.out.println("===============EndJoin==============="); }@Test public void testLeftJoinUseMap() { Map usersMap = new HashMap<>(); usersList.forEach(users -> { usersMap.put(users.getUserId(), users); }); /*users left join transactions*/ System.out.println("===========Left Join Result============"); for (Transactions transactions : transactionsList) { Users users = usersMap.get(transactions.getUserId()); if (users != null) { System.out.println(transactions.getProductId() + " " + users.getLocationId()); } } System.out.println("===============EndJoin==============="); }@After public void tearDown() { System.out.println(Users.printHeader()); for (Users users : usersList) { System.out.println(users); }System.out.println("============================="); System.out.println(Transactions.printHeader()); for (Transactions transactions : transactionsList) { System.out.println(transactions); } }