查询多个关联表的数据输出到Excel

举个小栗子：

查询student表中的数据，并且通过students中的关联字段cid查询classtable中的信息。

步骤一：可以选择建立一个连接池。

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.LinkedList; public class MyPool { private String url = "jdbc:sqlserver://120.78.80.233;databasename=ZBJF_Product_Test1"; //"useSSL=false"字段可防止不必要的报错 private String user = "zbjf"; private String password = "zbjf1234!@#$"; //数据库的用户名与密码 private static String driverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"; private int initSize = 5;//初始连接数量 private int maxSize = 20;//池中连接数上线 private LinkedList<Connection> conList = new LinkedList<Connection>(); private int currentsize = 0; static { try { Class.forName(driverClass); }catch(ClassNotFoundException e){ e.printStackTrace(); } } //获取连接 private Connection getConnection(){ Connection conn=null; try{ conn=(Connection)DriverManager.getConnection(url,user,password); }catch(SQLException e){ e.printStackTrace(); } return conn; } //初始化池 public MyPool(){ for(int i=0;i<initSize;i++){ Connection connection = this.getConnection(); conList.add(connection); currentsize++; } } //用户只能从池中获取连接 public Connection getConnFromPool(){ if(conList.size()>0){ Connection connection =conList.getFirst(); conList.removeFirst(); return connection; } else if(conList.size()==0&¤tsize<maxSize){ currentsize++; conList.addLast(this.getConnection()); Connection connection=conList.getFirst(); conList.removeFirst(); return connection; } throw new RuntimeException("连接达到上限，请等待"); } //用户释放链接到池 public void releaseConnection(Connection connection){ conList.addLast(connection); } }

步骤二：进行输出操作

import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import com.mysql.jdbc.Statement; public class ImportExcelTest { private static MyPool pool=null; private static Connection conn=null; private static Workbook book=null; private static Statement st1=null; private static Statement st2=null; private static ResultSet rs1=null; private static ResultSet rs2=null; private static String sql1="select name,class,score,cid from students"; //private static String sql2="SELECT classname FROM classtable WHERE id="; //创建一个List来存储多行数据，Map存储一行 private static List<Map<String,Object>> list=new ArrayList<Map<String,Object>>(); public static void importExecel() throws SQLException, FileNotFoundException, IOException { pool=new MyPool(); conn=pool.getConnection(); //创建excel表 book=new HSSFWorkbook(); String table_name="学生成绩统计表"; //在当前excel创建一个子表 ss与sl区别 Sheet sheet=(Sheet) book.createSheet(table_name); st1=(Statement) conn.createStatement(); st2=(Statement) conn.createStatement(); rs1=st1.executeQuery(sql1); //创建行 Row row1=(Row) sheet.createRow(0); //设置表头信息(写入excel左上角从(0,0)开始) String head[]= {"姓名","课程","得分","班级"}; for(int i=0;i<head.length;i++) { //创建单元格 Cell cell=(Cell) row1.createCell(i); //写入数据 cell.setCellValue(head[i]); } //设置表格信息 while(rs1.next()) { //遍历将数据一行一行存入Map Map<String ,Object> m=new HashMap<String,Object>(); for(int i=1;i<=rs1.getMetaData().getColumnCount();i++) { if("name".equals(rs1.getMetaData().getColumnName(i))) { m.put("name", rs1.getString(i)); } if("class".equals(rs1.getMetaData().getColumnName(i))) { m.put("class", rs1.getString(i)); } if("score".equals(rs1.getMetaData().getColumnName(i))) { System.out.println("score+"+rs1.getInt(i)); m.put("score", rs1.getInt(i)); } if("cid".equals(rs1.getMetaData().getColumnName(i))) { System.out.println("cid"+rs1.getInt(i)); String sql2="SELECT classname FROM classtable WHERE id="+rs1.getInt(i); rs2=st2.executeQuery(sql2); while(rs2.next()) { for(int j=1;j<=rs2.getMetaData().getColumnCount();j++) { m.put("classname", rs2.getString(j)); System.out.println("rs2"+rs2.getString(j)); } } } } for(int z=0;z<m.size();z++) { System.out.println("class:"+m.get("class")+"score:"+m.get("score")); } int index=1; list.add(m); String [] header= {"name","class","score","classname"}; for(int i=0;i<list.size();i++) { Row row=sheet.createRow(index++); Map<String,Object> map2=list.get(i); for(int j=0;j<header.length;j++) { Cell cell=row.createCell(j); cell.setCellValue(j); cell.setCellValue(map2.get(header[j])+""); } } } book.write(new FileOutputStream("D://aaa//"+table_name+System.currentTimeMillis()+".xls")); rs1.close(); rs2.close(); st1.close(); st2.close(); con.close(); } public static void main(String[] args) throws FileNotFoundException, SQLException, IOException { importExecel(); //close(rs1,rs2,st1,st2,conn); } }