/* -- 依赖 --*/<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 //3private 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;}@Servicepublic class PersonServiceImpl implements PersonService {@Autowiredprivate PersonRepository personRepository;@Overridepublic 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 TransferSource Server : ocpCloudSource Server Version : 50724Source Host : 192.168.13.129:3306Source Database : testTarget Server Type : MYSQLTarget Server Version : 50724File Encoding : 65001Date: -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', '乔峰');
