java - 如何使用java将三个表连接成一个表以及set方法值应该写在哪里

Question

我的CustomerServicesDAO类有客户地址、客户数据库和客户折扣表。我的问题是如何加入它以及如何编写set方法。

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