开发常见场景之 CVS 文件导出

/* -- 依赖 --*/
    <dependency>
        <groupId>net.sourceforge.javacsv</groupId>
        <artifactId>javacsv</artifactId>
        <version>2.0</version>
    </dependency>

/*--entity domain--*/
@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity //1
@NamedQuery(name="Person.withNameAndAddressQuery2",query = "select p from Person p where p.name = ?1 and p.address = ?2")
public class Person implements Serializable {

    private static final long serialVersionUID = 5918727834473026681L;

    @Id //2
    @GeneratedValue //3
    private Long id;

    private String name;

    private Integer age;

    private String address;

}

/* -- Controller --*/
@RestController
@RequestMapping("/person")
public class PersonController {
    
    @GetMapping("/cvs")
    public void expStream() throws IOException {
        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder
            .getRequestAttributes()).getResponse();
            
        response.setContentType("application/csv");
        response.setHeader("Content-Disposition", "attachment; filename=personList.csv");
        personService.getCvs(response);
    } 
}
/*--- Service  Interface and impl---*/
public interface PersonService {

    void getCvs(HttpServletResponse response) throws IOException;
}

@Service
public class PersonServiceImpl implements PersonService {

    @Autowired
    private PersonRepository personRepository;

    @Override
    public void getCvs(HttpServletResponse response) {

        List<Person> list = personRepository.findAll();

        String[] header = new String[]{"姓名", "地址", "年龄"};
        ServletOutputStream out = null;
        CsvWriter csvWriter = null;
        Integer sum = 0;
        try {
            out = response.getOutputStream();
            csvWriter = new CsvWriter(out, ',', Charset.forName("UTF-8"));
            /*-- 写入表头 --*/
            csvWriter.writeRecord(header);
            for (Person p : list) {
                csvWriter.write(withString(p.getName()));
                csvWriter.write(withString(p.getAddress()));
                Integer age = p.getAge();
                csvWriter.write(withString(p.getAge()));
                sum += age;
                /*-- 换行 --*/
                csvWriter.endRecord();
            }
            /*-- 汇总 --*/
            csvWriter.write("");
            csvWriter.write("");
            csvWriter.write(withString(sum));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            csvWriter.close();
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 属性转环为String类型.
     *
     * @param obj
     * @return String
     */
    private String withString(Object obj) {
        if (obj == null) {
            return "";
        }
        if (String.class.isInstance(obj)) {
            return (String) obj;
        }
        if (Date.class.isInstance(obj)) {
            DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            return format.format((Date) obj);
        }
        return obj + "";
    }
}

/*-- dao --*/

@SuppressWarnings("unused")
public interface PersonRepository extends BaseDao<Person, Long> {

}

SQL 脚本

/*
Navicat MySQL Data Transfer

Source Server         : ocpCloud
Source Server Version : 50724
Source Host           : 192.168.13.129:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50724
File Encoding         : 65001

Date: 2019-02-26 14:30:32
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1', '北京', '20', '小天');
INSERT INTO `person` VALUES ('2', '北京', '20', '熊大');
INSERT INTO `person` VALUES ('3', '北京', '20', '熊二');
INSERT INTO `person` VALUES ('4', '长白山', '20', '熊大');
INSERT INTO `person` VALUES ('6', '上海', '19', '江小白');
INSERT INTO `person` VALUES ('7', '合肥', '32', '黄飞鸿');
INSERT INTO `person` VALUES ('8', '北京', '31', '江小鱼');
INSERT INTO `person` VALUES ('9', '上海', '30', '王老五');
INSERT INTO `person` VALUES ('10', '南京', '29', '黄世仁');
INSERT INTO `person` VALUES ('11', '武汉', '28', '马可波罗');
INSERT INTO `person` VALUES ('12', '合肥', '27', '戚继光');
INSERT INTO `person` VALUES ('13', '合肥', '32', '江大大');
INSERT INTO `person` VALUES ('14', '北京', '31', '熊大');
INSERT INTO `person` VALUES ('15', '上海', '30', '熊二');
INSERT INTO `person` VALUES ('16', '南京', '29', '喜洋洋');
INSERT INTO `person` VALUES ('17', '武汉', '28', '德玛西亚');
INSERT INTO `person` VALUES ('18', '合肥', '27', '土豪');
INSERT INTO `person` VALUES ('19', '合肥', '32', '努尔哈赤');
INSERT INTO `person` VALUES ('20', '北京', '31', '李白');
INSERT INTO `person` VALUES ('21', '上海', '30', '宋江');
INSERT INTO `person` VALUES ('22', '南京', '29', '刘德华');
INSERT INTO `person` VALUES ('23', '武汉', '28', '张曼玉');
INSERT INTO `person` VALUES ('24', '合肥', '27', '乔峰');