关于jdbc的多表查询

大家好我是曜耀。

今天发一个对多表查询的案例：

package com.dao;


import com.bean.Customer;
import com.bean.User;
import com.util.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CustomerDAo{

    //全查两个表的

    public List selecttwo(){
        Map<Object, Object> map=null;
        List<Map<Object,Object>> list=new ArrayList<>();
        List<Object> list1=new ArrayList<>();

        Map<Customer ,User> map2=new HashMap<>();
        List<Map<Customer ,User>> list2=new ArrayList<>();
        Connection connection= DBHelper.getConnection();
        ResultSet rs=null;
        PreparedStatement ps =null;

                String sql="select a.id,cust_name,cust_company,cust_birth,cust_sex,cust_phone,cust_position,a.create_time,a.modify_time,b.username from t_customer a,t_user b where a.user_id=b.id;";

        try {
            ps=connection.prepareStatement(sql);
            rs=ps.executeQuery();

            while (rs.next()){
                User user=new User();
                Customer customer=new Customer();
                map=new HashMap<Object, Object>();

//                customer.setId(rs.getInt("id"));
//                customer.setCust_name(rs.getString("cust_name"));
//                customer.setCust_birth(rs.getString("cust_birth"));
//                customer.setCust_sex(rs.getInt("cust_sex"));
//                customer.setCust_phone(rs.getString("cust_phone"));
//                customer.setCust_position(rs.getString("cust_position"));
//                customer.setCreate_time(rs.getString("create_time"));
//                customer.setModify_time(rs.getString("modify_time"));
//                user.setUsername(rs.getString("username"));
                map.put("id",rs.getInt("id"));
                map.put("cust_name",rs.getString("cust_name"));
                map.put("cust_birth",rs.getString("cust_birth"));
                map.put("cust_sex",rs.getInt("cust_sex"));
                map.put("cust_phone",rs.getString("cust_phone"));
                map.put("cust_position",rs.getString("cust_position"));
                map.put("create_time",rs.getString("create_time"));
                map.put("modify_time",rs.getString("modify_time"));
                map.put("username",rs.getString("username"));
//                map.put(customer,user);
                list.add(map);
//               list1.add(rs.getInt("id"));
//
//                list1.add(rs.getString("cust_name"));
//                list1.add(rs.getString("cust_birth"));
//                list1.add(rs.getInt("cust_sex"));
//                list1.add(rs.getString("cust_phone"));
//                list1.add(rs.getString("cust_position"));
//                list1.add(rs.getString("create_time"));
//                list1.add(rs.getString("modify_time"));
//
//                map2.put(customer, user);
//                list2.add(map2);




            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


//        return list1;
        return list;
//        return list2;
    }



    //查询条数俩个表的

    public int count(){
        Connection connection= DBHelper.getConnection();
        ResultSet rs=null;
        PreparedStatement ps =null;

        int i=0;
        String sql="select count(*) from t_customer a,t_user b where a.user_id=b.id";
        try {
            ps=connection.prepareStatement(sql);
            rs = ps.executeQuery();

            if (rs.next()){

                i=rs.getInt("count(*)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }



        return i;

    }


    public static void main(String[] args) {

        CustomerDAo customerDAo = new CustomerDAo();
      List<Map<Object, Object>> list = new ArrayList<>();
        List<Object> list1=new ArrayList<>();
        List<Map<Customer ,User>> list2=new ArrayList<>();

        list=customerDAo.selecttwo();
        for (Map<Customer, User> customerUserMap : list2) {
            System.out.println("customerUserMap = " + customerUserMap);
        }


        System.out.println("list = " + list);

        int i=customerDAo.count();
        System.out.println("i = " + i);

    }



}

其中有很多测试的例子，个位小伙伴们可以了解一下基本编程思维。