how to join three tables into one table using java and where should the set method values be written

Question

My CustomerServicesDAO class has customer address, customer database and customer discount table. My question is how to join it and how to write the set method.

package com.glomindz.mercuri.dao;

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

import com.glomindz.mercuri.pojo.Customer;
import com.glomindz.mercuri.util.MySingleTon;

public class CustomerServicesDAO {

    private Connection connection;

    public CustomerServicesDAO() {
    // connection = new MySingleTon().getConnection();
    connection = MySingleTon.getInstance().getConnection();

}

public List<Customer> get_all_data() {
    List<Customer> customersList = new ArrayList<Customer>();
    String query = "SELECT * FROM spl_customer_address";
    try {
        PreparedStatement stmt = connection.prepareStatement(query);
        boolean execute = stmt.execute();
        System.out.println(execute);
        ResultSet resultSet = stmt.getResultSet();
        System.out.println(resultSet.getMetaData());
        while (resultSet.next()) {
            Customer customer = new Customer();
            customer.setId(resultSet.getInt("id"));
            customer.setCompany_id(resultSet.getInt("company_id"));
            customer.setAddress_type(resultSet.getString("address_type"));
            customer.setAddress(resultSet.getString("address"));
            customer.setLocality(resultSet.getString("locality"));
            customer.setCity(resultSet.getString("city"));
            customer.setState(resultSet.getString("state"));
            customer.setCountry(resultSet.getString("country"));
            customer.setPin(resultSet.getString("pin"));
            customer.setTel(resultSet.getString("tel"));
            customer.setFax(resultSet.getString("fax"));
            customer.setLast_udpate(resultSet.getString("last_update"));
            customersList.add(customer);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return customersList;
}

public boolean set_all_data() {
    Customer customer = new Customer();
    boolean result = false;

    try {
        PreparedStatement stmt = connection.prepareStatement("INSERT INTO spl_customer_Address(company_id,address_type,address,locality,city,state,country,pin,tel,fax,last_update)VALUES(?,?,?,?,?,?,?,?,?,?,?)");
        stmt.setInt(1, customer.getCompany_id());
        stmt.setString(2, customer.getAddress_type());
        stmt.setString(3, customer.getAddress());
        stmt.setString(4, customer.getLocality());
        stmt.setString(5, customer.getCity());
        stmt.setString(6, customer.getState());
        stmt.setString(7, customer.getCountry());
        stmt.setString(8, customer.getPin());
        stmt.setString(9, customer.getTel());
        stmt.setString(10, customer.getFax());
        stmt.setString(11, customer.getLast_update());

        boolean execute = stmt.execute();
        System.out.println(execute);
        stmt.getResultSet();

    }

    catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return result;
}

public List<Customer> get_all_data1() {
    List<Customer> customersList = new ArrayList<Customer>();
    String query = "SELECT * FROM spl_customer_database";
    try {
        PreparedStatement stmt = connection.prepareStatement(query);
        boolean execute = stmt.execute();
        System.out.println(execute);
        ResultSet resultSet = stmt.getResultSet();
        System.out.println(resultSet.getMetaData());
        while (resultSet.next()) {
            Customer customer = new Customer();
            customer.setId(resultSet.getInt("id"));
            customer.setName(resultSet.getString("name"));
            customer.setCompany(resultSet.getString("company"));
            customer.setCustomer_type(resultSet.getString("customer_type"));
            customer.setEmail(resultSet.getString("email"));
            customer.setMobile(resultSet.getString("mobile"));
            customer.setTel(resultSet.getString("tel"));
            customer.setFax(resultSet.getString("fax"));
            customer.setPan(resultSet.getString("pan"));
            customer.setVat(resultSet.getString("vat"));
            customer.setCst(resultSet.getString("cst"));
            customer.setCst_valid(resultSet.getString("cst_valid"));
            customer.setBank_name(resultSet.getString("bank_name"));
            customer.setBank_branch(resultSet.getString("bank_branch"));
            customer.setBank_city(resultSet.getString("bank_city"));
            customer.setBank_swift_code(resultSet.getString("bank_swift_code"));
            customer.setBank_ac_no(resultSet.getString("bank_ac_no"));
            customer.setRating(resultSet.getString("rating"));
            customer.setUser_id(resultSet.getInt("user_id"));
            customer.setLast_udpate(resultSet.getString("last_update"));
            customersList.add(customer);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return customersList;
}

public boolean set_all_data1() {
    Customer customer = new Customer();
    boolean result = false;
    try {
        PreparedStatement stmt = connection.prepareStatement("INSERT INTO spl_customer_database(name,company,customer_type,email,mobile,tel,fax,pan,vat,cst,cst_valid,bank_name,bank_branch,bank_city,bank_swift_code,bank_ac_no,rating,user_id,last_update)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        stmt.setString(1, customer.getName());
        stmt.setString(2, customer.getCompany());
        stmt.setString(3, customer.getCustomer_type());
        stmt.setString(4, customer.getEmail());
        stmt.setString(5, customer.getMobile());
        stmt.setString(6, customer.getTel());
        stmt.setString(7, customer.getFax());
        stmt.setString(8, customer.getPan());
        stmt.setString(9, customer.getVat());
        stmt.setString(10,customer.getCst());
        stmt.setString(11,customer.getCst_valid());
        stmt.setString(12,customer.getBank_name());
        stmt.setString(13, customer.getBank_branch());
        stmt.setString(14, customer.getBank_city());
        stmt.setString(15, customer.getBank_swift_code());
        stmt.setString(16, customer.getBank_ac_no());
        stmt.setString(17, customer.getRating());
        stmt.setInt(18, customer.getUser_id());
        stmt.setString(19, customer.getLast_update());

        result = stmt.execute();

    }

    catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return result;
}

public List<Customer> get_all_data2() {
    List<Customer> customersList = new ArrayList<Customer>();
    String query = "SELECT * FROM spl_customer_discount_master";
    try {
        PreparedStatement stmt = connection.prepareStatement(query);
        boolean execute = stmt.execute();
        System.out.println(execute);
        ResultSet resultSet = stmt.getResultSet();
        System.out.println(resultSet.getMetaData());
        while (resultSet.next()) {
            Customer customer = new Customer();
            customer.setId(resultSet.getInt("id"));
            customer.setName(resultSet.getString("name"));
            customer.setRemarks(resultSet.getString("remarks"));
            customer.setCustomer_type(resultSet.getString("customer_type"));
            customer.setPercentage(resultSet.getFloat("percentage"));
            customer.setStatus(resultSet.getInt("status"));
            customer.setValid_from(resultSet.getString("valid_from"));
            customer.setValid_to(resultSet.getString("valid_to"));
            customer.setLast_udpate(resultSet.getString("last_update"));
            customersList.add(customer);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return customersList;
}

public boolean set_all_data2() {
    Customer customer = new Customer();
    boolean result = false;

    try {
        PreparedStatement stmt = connection.prepareStatement("INSERT INTO spl_customer_discount_master(name,remarks,customer_type,percentage,status,valid_from,valid_to,last_update)VALUES(?,?,?,?,?,?,?,?)");
        stmt.setString(1, customer.getName());
        stmt.setString(2, customer.getRemarks());
        stmt.setString(3, customer.getCustomer_type());
        stmt.setFloat(4, customer.getPercentage());
        stmt.setInt(5, customer.getStatus());
        stmt.setString(6, customer.getValid_from());
        stmt.setString(7, customer.getValid_to());
        stmt.setString(8, customer.getLast_update());
        result = stmt.execute();

    }

    catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    return result;
}

public static void main(String[] args) {
    CustomerServicesDAO cus = new CustomerServicesDAO();
    List<Customer> data = cus.get_all_data();
    boolean data1 = cus.set_all_data();
    List<Customer> data2 = cus.get_all_data1();
    boolean data3 = cus.set_all_data1();
    List<Customer> data4 = cus.get_all_data2();
    boolean data5 = cus.set_all_data2();
    System.out.println(data);
    System.out.println(data1);
    System.out.println(data2);
    System.out.println(data3);
    System.out.println(data4);
    System.out.println(data5);
    System.exit(0);
}
}

Answer 1

SELECT
    cusAdd.company_id, cusAdd.address_type, cusAdd.address,
    cusAdd.locality, cusAdd.city, cusAdd.state, cusAdd.country,
    cusAdd.pin, cusAdd.tel, cusAdd.fax, cusAdd.last_update,

    cusData.name, cusData.company, cusData.customer_type,
    cusData.email, cusData.mobile, cusData.tel, cusData.fax,
    cusData.pan, cusData.vat, cusData.cst, cusData.cst_valid,
    cusData.bank_name, cusData.bank_branch, cusData.bank_city,
    cusData.bank_swift_code, cusData.bank_ac_no, cusData.rating,
    cusData.user_id, cusData.last_update,

    cusDis.name, cusDis.remarks, cusDis.customer_type,
    cusDis.percentage, cusDis.status, cusDis.valid_from,
    cusDis.valid_to, cusDis.last_update

FROM
    spl_customer_address cusAdd,
    spl_customer_database cusData,
    spl_customer_discount_master cusDis

WHERE
    cusAdd.last_update = cusData.last_update
    AND cusAdd.last_update = cusDis.last_update