JDBC常用API之外的总结

编写自己的JDBC框架所涉及到的几个知识点先了解一下。元数据:拿到元数据,就相当于拿到了数据库的定义信息。

做JAVA的人玩JDBC肯定已经很熟练了,像DriverManager、Connection、ResultSet、Statement这些基本类大家肯定很常用啦,我不赘述那些诸如注册JDBC驱动、创建连接、获取数据集的API了,在这我介绍一些写框架时常用的API,大家共同学习吧。

1.如何通过元数据拿到数据库的信息? 
2.如何用Java生成Excel表? 
3.将数据库中的表导出生成Excel案例

图片 1元数据

 

如何通过元数据拿到数据库的信息

元数据:描述数据的数据

Java中使用元数据的两个方法

  • DatabaseMetaData
    通过连接可以拿到的信息:数据库软件,所有数据库名,所有数据库里面的表名,描述数据库的元数据
  • ResultSetMetaData 拿到的表结构信息:获得表的列数目 类型和属性
    ,描述数据库表的元数据

DatabaseMetaData 的使用学习

@Test//
DatabaseMetaData
通过连接可以拿到的信息:数据库软件,所有数据库名,所有数据库里面的表名
public void
DatabaseMetaData_Demo() throws Exception{ //
自己写的工具包来获得数据库连接 Connection con = ConnUtils4.getConn();
//DatabaseMetaData 通过连接获得
DatabaseMetaData dbmt = con.getMetaData(); //
数据库软件名 System.out.println(” 数据库软件名:”+dbmt.getDatabaseProductName());
// 拿到所有数据库名字 ResultSet rs
=dbmt.getCatalogs(); List<String> tablenames = new ArrayList<String>(); while(rs.next()){ String tabname=rs.getString(“TABLE_CAT”); tablenames.add(tabname); System.out.println(“数据库名字:”+tabname); } System.out.println(“————–“);; //拿到某个数据库李曼所有的表名—可以指定表的类型
rs = dbmt.getTables(“ake”, “ake”, null, new String[]{“TABLE”,”VIEW”}); while(rs.next()){ System.out.println(“数据库ake里的表名:”+rs.getString(“TABL““` _NAME”)); }
}

 

 

图片 2

ResultSetMetaData的使用学习

@Test// ResultSetMetaData 拿到的表结构信息:获得表的列数目 类型和属性
public void ResultSetMetaData_Demo2() throws Exception{ //
自己写的工具包来获得数据库连接 Connection con = ConnUtils4.getConn(); String
sql = “select * from ake.book”; Statement st =
con.createStatement(); ResultSet rs = st.executeQuery(sql);
// ResultSetMetaData 通过 查询的返回集获取 ResultSetMetaData rsmt =
rs.getMetaData(); //获得表的列数 int n =rsmt.getColumnCount();
//类型—某一列 // getColumnTypeName:INT //某医疗的名字 //
getColumnName:id //某一列的长度 // getColumnDisplaySize:11 for(int i=1;i<n;i++){ System.out.println(rsmt.getTableName(i)+”表的第”+i+”列描述信息”);
System.out.println(“getColumnDisplaySize:”+rsmt.getColumnDisplaySize(i)); System.out.println(“getColumnLabel:”+rsmt.getColumnLabel(i)); System.out.println(“getColumnName:”+rsmt.getColumnName(i));
System.out.println(“getColumnType:”+rsmt.getColumnType(i));
System.out.println(“getColumnTypeName:”+rsmt.getColumnTypeName(i)); System.out.println(“getPrecision:”+rsmt.getPrecision(i));
System.out.println(“getScale:”+rsmt.getScale(i));
System.out.println(“getSchemaName:”+rsmt.getSchemaName(i));
System.out.println(“————“); }
con.close();
}

 

图片 3

拿出ake表里面所有的内容~~~~

// 拿出ake表里面所有的内容~~~~ public static void main(String[] args)
throws Exception{ Connection con = ConnUtils4.getConn(); System.out.println(con); DatabaseMetaData dbmt =
con.getMetaData(); //拿到所有的ake所有表名
ResultSet rs =dbmt.getTables(“ake”, “ake”, null, new String[]{“TABLE”,”VIEW”});
List<String> tablenames = new
ArrayList<String>(); while(rs.next()){
String tablename = rs.getString(“TABLE_NAME”); tablenames.add(tablename); } for(String tablename:tablenames){ System.out.println(tablename+”表:”); if(tablename.equals(“img”)){ continue; }
String sql = “select * from ake.”+tablename;
Statement st = con.createStatement(); ResultSet RS =
st.executeQuery(sql); ResultSetMetaData rsmt = RS.getMetaData(); // 拿到列数 int colnums
= rsmt.getColumnCount(); for(int i=1;i<=colnums;i++){ //拿到表头信息 String colName =
rsmt.getColumnName(i); System.out.print(colName+”t”);
} System.out.println(); while(RS.next()){ for(int i=1;i<=colnums;i++){ //拿到表信息 System.out.print( RS.getString(i)+”t”); } System.out.println(); } } con.close();
}

 

我把那到的表格信息输出 
图片 4 
图片 5

  • DataBaseMetaData

 

如何用Java生成Excel表?

需要一个插件工具包 
图片 6

@Test public
void Workbook_demo() throws
Exception{ //
建立一个工作表–相当于一个数据库 Workbook book = new HSSFWorkbook(); //
数据库中的一个表 Sheet sheet1 =book.createSheet(“表1”); // 行 Row row
=sheet1.createRow(4); // 单元格 Cell cell = row.createCell(3); // 写入数据
cell.setCellValue(“通过java写的Excel”); // 保存到银盘 book.write( new FileOutputStream(“d:a/a.xls”));
}

图片 7

这个是拿到库的元数据。

  • ResultSetMetaData获取ResultSet对象的元数据信息

将数据库中的表导出生成Excel案例

public static void main(String[] args) throws Exception { //把数据库里所有的信息导入到Excel表中~ Connection
con = ConnUtils4.getConn(); DatabaseMetaData dbmt = con.getMetaData();
//要通过 DatabaseMetaData
拿到所有数据库的名字 List<String>
Database_Names = new ArrayList<String>(); ResultSet rs =dbmt.getCatalogs();
while(rs.next()){ Database_Names.add(
rs.getString(“TABLE_CAT”)); } //DatabaseMetaData 拿到所有数据表名 int m = 0; for(String Database_Name:Database_Names){ if(!Database_Name.equals(“ake”)){ continue; }
// if(m++>=3){ // break; // } // 一个数据库对于一个 Excel文档~ Workbook book =
new HSSFWorkbook(); rs =
dbmt.getTables(Database_Name, Database_Name, null, new String[]{“TABLE”,”VIEW”}); //封装所有表名
List<String> Table_Names = new ArrayList<String>(); while(rs.next()){ Table_Names.add(
rs.getString(“TABLE_NAME”)); } for(String
Table_Name:Table_Names){ if(“img”.equals(Table_Name) ||”note”.equals(Table_Name) ){ // img为二进制文件导入会出错 continue; } //创建一个表
Sheet sheet = book.createSheet(Table_Name); Statement st =
con.createStatement(); String sql = “select * from “+Database_Name+”.”+Table_Name; //
System.out.println(“sql:”+sql); rs = st.executeQuery(sql); //设置表头信息 Row row1 = sheet.createRow(0); ResultSetMetaData rsmd = rs.getMetaData(); int
colnum = rsmd.getColumnCount(); for(int
i=1;i<=colnum;i++){ String name = rsmd.getColumnName(i); Cell cell =
row1.createCell(i-1); cell.setCellValue(name);
// System.out.print(name+”t”); } // System.out.println(); //设置表格信息 int idx = 1; while(rs.next()){ Row
row = sheet.createRow(idx++); for(int i=1;i<=colnum;i++){ String str = rs.getString(i); // System.out.print(str+”t”); Cell cell =
row.createCell(i-1); cell.setCellValue(str); }
// System.out.println(); } } book.write(
new FileOutputStream( “d:a/”+Database_Name+”.xls”)); }
}

 

执行结果就成功啦! 
图片 8 
图片 9 
图片 10

/**
     * excel
     * @param request
     * @param resp
     * @throws UnsupportedEncodingException
     */
    @RequestMapping(value = "/exportReceiptReport", method = RequestMethod.GET)
    @ResponseBody
    public void exportReceiptReport(HttpServletRequest request, HttpServletResponse response,JyHTMLpurchaseinfoApiForm form) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/x-download");
        Date d = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String now = sdf.format(d);
        String fileName = "采购"+now+".xls";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        HSSFSheet sheet = wb.createSheet("sheet1");
        sheet.setDefaultRowHeight((short) (2 * 256));
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        HSSFRow row = sheet.createRow((int) 0);
        sheet.createRow((int) 1);
        sheet.createRow((int) 2);
        sheet.createRow((int) 3);
        sheet.createRow((int) 4);
        sheet.createRow((int) 5);
        sheet.createRow((int) 6);
        sheet.createRow((int) 7);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCell cell = row.createCell(0);
        cell.setCellValue("收货");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("来源");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("入库");
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue("名称");
        cell.setCellStyle(style);
        cell = row.createCell(4);
        cell.setCellValue("商品名称");
        cell.setCellStyle(style);
        cell = row.createCell(5);
        cell.setCellValue("单位");
        cell.setCellStyle(style);
        cell = row.createCell(6);
        cell.setCellValue("规格");
        cell.setCellStyle(style);
        cell = row.createCell(7);
        cell.setCellValue("数量");
        cell.setCellStyle(style);
        cell = row.createCell(8);
        cell.setCellValue("单价");
        cell.setCellStyle(style);
        cell = row.createCell(9);
        cell.setCellValue("金额");
        cell.setCellStyle(style);
        cell = row.createCell(10);
        cell.setCellValue("用户名称");
        cell.setCellStyle(style);

        List list = SF.JYPURCHASEINFO_SERVICE.exportReceiptReport(form);
        for (int i = 0; i < list.size(); i++) {
            HSSFRow row1 = sheet.createRow(i+1);
            Map<String, Object> map = (Map<String,Object>)list.get(i);
            row1.createCell(0).setCellValue((map.get("receipt_no") != null ? map.get("receipt_no") : "").toString());
            row1.createCell(1).setCellValue((map.get("purchase_no") != null ? map.get("purchase_no") : "").toString());
            row1.createCell(2).setCellValue((map.get("value") != null ? map.get("value") : "").toString());
            row1.createCell(3).setCellValue((map.get("supplier_name") != null ? map.get("supplier_name") : "").toString());
            row1.createCell(4).setCellValue((map.get("packing_name") != null ? map.get("packing_name") : "").toString());
            row1.createCell(5).setCellValue((map.get("purchase_unit") != null ? map.get("purchase_unit") : "").toString());
            row1.createCell(6).setCellValue((map.get("packing_spec") != null ? map.get("packing_spec") : "").toString());
            row1.createCell(7).setCellValue((map.get("boxcount") != null ? map.get("boxcount") : "").toString()+
                    (map.get("packing_unit") != null ? map.get("packing_unit") : "").toString()+
                    (map.get("pingCount") != null ? map.get("pingCount") : "").toString()+
                    (map.get("item_unit") != null ? map.get("item_unit") : "").toString());
            row1.createCell(9).setCellValue((map.get("purchase_price") != null ? map.get("purchase_price") : "").toString());
            row1.createCell(10).setCellValue((map.get("name") != null ? map.get("name") : "").toString());
        }
        try {
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (ServiceException e) {
            logger.info 

("ServiceException=====导出excel异常====" + e);
            e.printStackTrace();
        } catch (Exception e1) {
            logger.info 

("Exception=====导出excel异常====" + e1);
            e1.printStackTrace();
        }
    }

 多个excel:

/**
     * 费用结算清单导出:
     */
    @RequestMapping(value = "/exportDealerCosts", method = RequestMethod.GET)
    @ResponseBody
    public void exportDealerCosts(HttpServletRequest request, HttpServletResponse response,JyDealerHomeform form) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/x-download");
        Date d = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String now = sdf.format(d);
        String fileName = "费用结算清单"+now+".xls";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        //合计
        HSSFSheet sheet = wb.createSheet("费用结算清单");
        sheet.setDefaultRowHeight((short) (2 * 256));
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 16);
        HSSFRow row = sheet.createRow((int) 0);
        sheet.createRow((int) 1);
        sheet.createRow((int) 2);
        sheet.createRow((int) 3);
        sheet.createRow((int) 4);
        sheet.createRow((int) 5);
        sheet.createRow((int) 6);
        sheet.createRow((int) 7);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("公司名称");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("仓储费");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("配送费");
        cell.setCellStyle(style);
        cell = row.createCell(3);
        cell.setCellValue("回空配送费");
        cell.setCellStyle(style);
        cell = row.createCell(4);
        cell.setCellValue("退货配偶费");
        cell.setCellStyle(style);
        cell = row.createCell(5);
        cell.setCellValue("入出库费");
        cell.setCellStyle(style);
        List dealerCosts = SF.JYDEALER_SERVICE.dealerCosts(form);
        for (int i = 0; i < dealerCosts.size(); i++) {
            HSSFRow row1 = sheet.createRow(i+1);
            Map<String, Object> map = (Map<String,Object>)dealerCosts.get(i);
            row1.createCell(0).setCellValue((map.get("name") != null ? map.get("name") : "").toString());
            row1.createCell(1).setCellValue((map.get("cc_fee") != null ? map.get("cc_fee") : "").toString());
            row1.createCell(2).setCellValue((map.get("ps_fee") != null ? map.get("ps_fee") : "").toString());
            row1.createCell(3).setCellValue((map.get("hkps_fee") != null ? map.get("hkps_fee") : "").toString());
            row1.createCell(4).setCellValue((map.get("thps_fee") != null ? map.get("thps_fee") : "").toString());
            row1.createCell(5).setCellValue((map.get("rc_fee") != null ? map.get("rc_fee") : "").toString());
        }

        //仓储费
        HSSFSheet sheet2 = wb.createSheet("仓储费明细");
        sheet2.setDefaultRowHeight((short) (2 * 256));
        HSSFRow row2 = sheet2.createRow((int) 0);
        sheet2.createRow((int) 1);
        sheet2.createRow((int) 2);
        sheet2.createRow((int) 3);
        sheet2.createRow((int) 4);
        sheet2.createRow((int) 5);
        sheet2.createRow((int) 6);
        sheet2.createRow((int) 7);
        HSSFCell cell2 = row2.createCell(0);
        cell2.setCellValue("日期");
        cell2.setCellStyle(style);
        cell2 = row2.createCell(1);
        cell2.setCellValue("公司名称");
        cell2.setCellStyle(style);
        cell2 = row2.createCell(2);
        cell2.setCellValue("商品类型");
        cell2.setCellStyle(style);
        cell2 = row2.createCell(3);
        cell2.setCellValue("商品名称");
        cell2.setCellStyle(style);
        cell2 = row2.createCell(4);
        cell2.setCellValue("库存件数");
        cell2.setCellStyle(style);
        cell2 = row2.createCell(5);
        cell2.setCellValue("总重量");
        cell2.setCellStyle(style);
        cell2 = row2.createCell(6);
        cell2.setCellValue("仓储费");
        cell2.setCellStyle(style);
        List dealerStorageFee = SF.JYDEALER_SERVICE.dealerStorageFee(form);
        for (int i = 0; i < dealerStorageFee.size(); i++) {
            HSSFRow row1 = sheet2.createRow(i+1);
            Map<String, Object> map = (Map<String,Object>)dealerStorageFee.get(i);
            row1.createCell(0).setCellValue((map.get("calendarDate") != null ? map.get("calendarDate") : "").toString());
            row1.createCell(1).setCellValue((map.get("dealerName") != null ? map.get("dealerName") : "").toString());
            row1.createCell(2).setCellValue((map.get("itemType") != null ? map.get("itemType") : "").toString());
            row1.createCell(3).setCellValue((map.get("packingName") != null ? map.get("packingName") : "").toString());
            row1.createCell(4).setCellValue((map.get("packingQty") != null ? map.get("packingQty") : "").toString());
            row1.createCell(5).setCellValue((map.get("totalWeight") != null ? map.get("totalWeight") : "").toString());
            row1.createCell(6).setCellValue((map.get("totalAmount") != null ? map.get("totalAmount") : "").toString());
        }

        //配送费
        HSSFSheet sheet3 = wb.createSheet("配送费明细");
        sheet3.setDefaultRowHeight((short) (2 * 256));
        HSSFRow row3 = sheet3.createRow((int) 0);
        sheet3.createRow((int) 1);
        sheet3.createRow((int) 2);
        sheet3.createRow((int) 3);
        sheet3.createRow((int) 4);
        sheet3.createRow((int) 5);
        sheet3.createRow((int) 6);
        sheet3.createRow((int) 7);
        HSSFCell cell3 = row3.createCell(0);
        cell3.setCellValue("日期");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(1);
        cell3.setCellValue("公司名称");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(2);
        cell3.setCellValue("费用区分");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(3);
        cell3.setCellValue("费项");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(4);
        cell3.setCellValue("金额");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(5);
        cell3.setCellValue("客户");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(6);
        cell3.setCellValue("结算对象");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(7);
        cell3.setCellValue("订单编号");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(8);
        cell3.setCellValue("送货件数");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(9);
        cell3.setCellValue("总重量");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(10);
        cell3.setCellValue("送货单号");
        cell3.setCellStyle(style);
        cell3 = row3.createCell(11);
        cell3.setCellValue("配送方式");
        cell3.setCellStyle(style);
        List dealerDeliverFee = SF.JYDEALER_SERVICE.dealerDeliverFee(form);
        for (int i = 0; i < dealerDeliverFee.size(); i++) {
            HSSFRow row1 = sheet3.createRow(i+1);
            Map<String, Object> map = (Map<String,Object>)dealerDeliverFee.get(i);
            row1.createCell(0).setCellValue((map.get("confirm_date") != null ? map.get("confirm_date") : "").toString());
            row1.createCell(1).setCellValue((map.get("name") != null ? map.get("name") : "").toString());
            row1.createCell(2).setCellValue((map.get("fee_type_name") != null ? map.get("fee_type_name") : "").toString());
            row1.createCell(3).setCellValue((map.get("calculate_subject") != null ? map.get("calculate_subject") : "").toString());
            row1.createCell(4).setCellValue((map.get("out_amount") != null ? map.get("out_amount") : "").toString());
            row1.createCell(5).setCellValue((map.get("customer_name") != null ? map.get("customer_name") : "").toString());
            row1.createCell(6).setCellValue((map.get("source_type_name") != null ? map.get("source_type_name") : "").toString());
            row1.createCell(7).setCellValue((map.get("source_order_no") != null ? map.get("source_order_no") : "").toString());
            row1.createCell(8).setCellValue((map.get("box_count") != null ? map.get("box_count") : "").toString());
            row1.createCell(9).setCellValue((map.get("weight") != null ? map.get("weight") : "").toString());
            row1.createCell(10).setCellValue((map.get("deliver_no") != null ? map.get("deliver_no") : "").toString());
            row1.createCell(11).setCellValue((map.get("value") != null ? map.get("value") : "").toString());
        }

        //回空配送费
        HSSFSheet sheet4 = wb.createSheet("回空配送费明细");
        sheet4.setDefaultRowHeight((short) (2 * 256));
        HSSFRow row4 = sheet4.createRow((int) 0);
        sheet4.createRow((int) 1);
        sheet4.createRow((int) 2);
        sheet4.createRow((int) 3);
        sheet4.createRow((int) 4);
        sheet4.createRow((int) 5);
        sheet4.createRow((int) 6);
        sheet4.createRow((int) 7);
        HSSFCell cell4 = row4.createCell(0);
        cell4.setCellValue("日期");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(1);
        cell4.setCellValue("公司名称");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(2);
        cell4.setCellValue("回空品名称");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(3);
        cell4.setCellValue("费用区分");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(4);
        cell4.setCellValue("费项");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(5);
        cell4.setCellValue("金额");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(6);
        cell4.setCellValue("收货单号");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(7);
        cell4.setCellValue("客户");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(8);
        cell4.setCellValue("结算对象");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(9);
        cell4.setCellValue("销售单号");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(10);
        cell4.setCellValue("回空件数");
        cell4.setCellStyle(style);
        cell4 = row4.createCell(11);
        cell4.setCellValue("送货单号");
        cell4.setCellStyle(style);

        List dealerBackDeliverFee = SF.JYDEALER_SERVICE.dealerBackDeliverFee(form);
        for (int i = 0; i < dealerBackDeliverFee.size(); i++) {
            HSSFRow row1 = sheet4.createRow(i+1);
            Map<String, Object> map = (Map<String,Object>)dealerBackDeliverFee.get(i);
            row1.createCell(0).setCellValue((map.get("confirm_date") != null ? map.get("confirm_date") : "").toString());
            row1.createCell(1).setCellValue((map.get("name") != null ? map.get("name") : "").toString());
            row1.createCell(2).setCellValue((map.get("item_name") != null ? map.get("item_name") : "").toString());
            row1.createCell(3).setCellValue((map.get("fee_type_name") != null ? map.get("fee_type_name") : "").toString());
            row1.createCell(4).setCellValue((map.get("calculate_subject") != null ? map.get("calculate_subject") : "").toString());
            row1.createCell(5).setCellValue((map.get("detail_amount") != null ? map.get("detail_amount") : "").toString());
            row1.createCell(6).setCellValue((map.get("source_order_no") != null ? map.get("source_order_no") : "").toString());
            row1.createCell(7).setCellValue((map.get("customer_name") != null ? map.get("customer_name") : "").toString());
            row1.createCell(8).setCellValue((map.get("source_type_name") != null ? map.get("source_type_name") : "").toString());
            row1.createCell(9).setCellValue((map.get("sales_no") != null ? map.get("sales_no") : "").toString());
            row1.createCell(10).setCellValue((map.get("receipt_box") != null ? map.get("receipt_box") : "").toString());
            row1.createCell(11).setCellValue((map.get("deliver_no") != null ? map.get("deliver_no") : "").toString());
        }

        //退货配送费
        HSSFSheet sheet5 = wb.createSheet("退货配送费明细");
        sheet5.setDefaultRowHeight((short) (2 * 256));
        HSSFRow row5 = sheet5.createRow((int) 0);
        sheet5.createRow((int) 1);
        sheet5.createRow((int) 2);
        HSSFCell cell5 = row5.createCell(0);
        cell5.setCellValue("日期");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(1);
        cell5.setCellValue("公司名称");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(2);
        cell5.setCellValue("商品名称");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(3);
        cell5.setCellValue("费用区分");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(4);
        cell5.setCellValue("费项");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(5);
        cell5.setCellValue("金额");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(6);
        cell5.setCellValue("收货单号");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(7);
        cell5.setCellValue("客户");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(8);
        cell5.setCellValue("结算对象");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(9);
        cell5.setCellValue("销售单号");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(10);
        cell5.setCellValue("退货件数");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(11);
        cell5.setCellValue("总重量");
        cell5.setCellStyle(style);
        cell5 = row5.createCell(12);
        cell5.setCellValue("送货单号");
        cell5.setCellStyle(style);

        List dealerReturnDeliverFee = SF.JYDEALER_SERVICE.dealerReturnDeliverFee(form);
        for (int i = 0; i < dealerReturnDeliverFee.size(); i++) {
            HSSFRow row1 = sheet5.createRow(i+1);
            Map<String, Object> map = (Map<String,Object>)dealerReturnDeliverFee.get(i);
            row1.createCell(0).setCellValue((map.get("confirm_date") != null ? map.get("confirm_date") : "").toString());
            row1.createCell(1).setCellValue((map.get("name") != null ? map.get("name") : "").toString());
            row1.createCell(2).setCellValue((map.get("packing_name") != null ? map.get("packing_name") : "").toString());
            row1.createCell(3).setCellValue((map.get("fee_type_name") != null ? map.get("fee_type_name") : "").toString());
            row1.createCell(4).setCellValue((map.get("calculate_subject") != null ? map.get("calculate_subject") : "").toString());
            row1.createCell(5).setCellValue((map.get("detail_amount") != null ? map.get("detail_amount") : "").toString());
            row1.createCell(6).setCellValue((map.get("source_order_no") != null ? map.get("source_order_no") : "").toString());
            row1.createCell(7).setCellValue((map.get("customer_name") != null ? map.get("customer_name") : "").toString());
            row1.createCell(8).setCellValue((map.get("source_type_name") != null ? map.get("source_type_name") : "").toString());
            row1.createCell(9).setCellValue((map.get("sales_no") != null ? map.get("sales_no") : "").toString());
            row1.createCell(10).setCellValue((map.get("receipt_box") != null ? map.get("receipt_box") : "").toString());
            row1.createCell(11).setCellValue((map.get("detail_weight") != null ? map.get("detail_weight") : "").toString());
            row1.createCell(12).setCellValue((map.get("deliver_no") != null ? map.get("deliver_no") : "").toString());
        }

        //入出库费
        HSSFSheet sheet6 = wb.createSheet("入出库费明细");
        sheet6.setDefaultRowHeight((short) (2 * 256));
        HSSFRow row6 = sheet6.createRow((int) 0);
        sheet6.createRow((int) 1);
        sheet6.createRow((int) 2);
        HSSFCell cell6 = row6.createCell(0);
        cell6.setCellValue("日期");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(1);
        cell6.setCellValue("公司名称");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(2);
        cell6.setCellValue("费用区分");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(3);
        cell6.setCellValue("费项");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(4);
        cell6.setCellValue("结算对象");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(5);
        cell6.setCellValue("收货单号");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(6);
        cell6.setCellValue("对应商品");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(7);
        cell6.setCellValue("件数");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(8);
        cell6.setCellValue("计算重量");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(9);
        cell6.setCellValue("费用");
        cell6.setCellStyle(style);
        cell6 = row6.createCell(10);
        cell6.setCellValue("入库类型");
        cell6.setCellStyle(style);

        List dealerInoutFee = SF.JYDEALER_SERVICE.dealerInoutFee(form);
        for (int i = 0; i < dealerInoutFee.size(); i++) {
            HSSFRow row1 = sheet6.createRow(i+1);
            Map<String, Object> map = (Map<String,Object>)dealerInoutFee.get(i);
            row1.createCell(0).setCellValue((map.get("confirm_date") != null ? map.get("confirm_date") : "").toString());
            row1.createCell(1).setCellValue((map.get("name") != null ? map.get("name") : "").toString());
            row1.createCell(2).setCellValue((map.get("fee_type_name") != null ? map.get("fee_type_name") : "").toString());
            row1.createCell(3).setCellValue((map.get("calculate_subject") != null ? map.get("calculate_subject") : "").toString());
            row1.createCell(4).setCellValue((map.get("source_type_name") != null ? map.get("source_type_name") : "").toString());
            row1.createCell(5).setCellValue((map.get("source_order_no") != null ? map.get("source_order_no") : "").toString());
            row1.createCell(6).setCellValue((map.get("packing_name") != null ? map.get("packing_name") : "").toString());
            row1.createCell(7).setCellValue((map.get("receipt_box") != null ? map.get("receipt_box") : "").toString());
            row1.createCell(8).setCellValue((map.get("detail_weight") != null ? map.get("detail_weight") : "").toString());
            row1.createCell(9).setCellValue((map.get("detail_amount") != null ? map.get("detail_amount") : "").toString());
            row1.createCell(10).setCellValue((map.get("receipt_type") != null ? map.get("receipt_type") : "").toString());
        }
        try {
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (ServiceException e) {
            logger.info("ServiceException=====导出excel异常====" + e);
        } catch (Exception e1) {
            logger.info("Exception=====导出excel异常====" + e1);
        }
    }

 就是excel出来一行合并行 xxx年xx月xx日下面是内容

HSSFRow rowA = sheet.createRow((int) 0);
sheet.createRow((int) 1);
sheet.createRow((int) 2);
sheet.createRow((int) 3);
sheet.createRow((int) 4);
sheet.createRow((int) 5);
sheet.createRow((int) 6);
sheet.createRow((int) 7);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFCell cell = rowA.createCell(0);
cell.setCellValue("库存:"+form.getBeginDate()+"起至"+form.getEndDate()+"");
cell.setCellStyle(style);
cell = rowA.createCell(1);
cell.setCellValue("");
cell.setCellStyle(style);
cell = rowA.createCell(2);
cell.setCellValue("");
cell.setCellStyle(style);
cell = rowA.createCell(3);
cell.setCellValue("");
cell.setCellStyle(style);
cell = rowA.createCell(4);
cell.setCellValue("");
cell.setCellStyle(style);
cell = rowA.createCell(5);
cell.setCellValue("");
cell.setCellStyle(style);
cell = rowA.createCell(6);
cell.setCellValue("");
cell.setCellStyle(style);
cell = rowA.createCell(7);
cell.setCellValue("");
cell.setCellStyle(style);

// 合并单元格
CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 10); // 起始行, 终止行, 起始列, 终止列
sheet.addMergedRegion(cra);

 

 

Connection conn = null;conn = JdbcUtils_c3p0.getConnection();DatabaseMetaData meta = conn.getMetaData();System.out.println(meta.getDatabaseProductVersion;System.out.println(meta.getDatabaseMajorVersion;System.out.println(meta.getDatabaseMinorVersion;System.out.println(meta.getDatabaseProductName;

 

图片 11元数据
-ParameterMetaData

以Hibernate的Session.get(class,id)为例,该api是根据主键id获取class映射表的一条数据,这里只要class参数传入不同,则获取的表也不同,然而数据库那么多表,不同表的字段个数又不同,如何准确把表字段映射到class就是关键了,这里ResultSetMetaData就可以办到。

这个是拿到参数的元数据。

下面是如何获取ResultSetMetaData并且使用该对象内的一些常用方法:

 Connection conn = JdbcUtils_c3p0.getConnection(); String sql = "insert into user values"; PreparedStatement ps = conn.prepareStatement; //得到sql语句的参数元数据 ParameterMetaData meta = ps.getParameterMetaData(); //得到要往里面插入几个参数,也就是有几个问号 System.out.println(meta.getParameterCount; //mysql这里得不到参数的类型,会报错 System.out.println(meta.getParameterType;

 

图片 12元数据

Java代码 

  • ResultSetMetaData

    Connection conn = JdbcUtils_c3p0.getConnection(); String sql = “select * from account”; PreparedStatement ps = conn.prepareStatement; ResultSet rs = ps.executeQuery(); ResultSetMetaData meta = rs.getMetaData(); //得到结果集的列数 System.out.println(meta.getColumnCount; //得到每列的属性 System.out.println(meta.getColumnName; System.out.println(meta.getColumnName; System.out.println(meta.getColumnName;

 图片 13

下篇:编写自己的JDBC框架

  1. ResultSet rs = …… //获取数据集合  
  2. ResultSetMetaData meta= rs .getMetaData();//获取ResultSetMetaData对象  
  3. int columnCount = meta.getColumnCount();//获取ResultSet总共返回了多少个列  
  4. for(int columnIndex=1;columnIndex<=columnCount;columnIndex++){  
  5. resultData.getColumnName(columnIndex);//列名  
  6. resultData.getColumnType(columnIndex);//列数据类型(INT之类的)  
  7. resultData.getColumnTypeName(columnIndex);//列数据类型名  
  8. resultData.getColumnClassName(columnIndex);//列java类型名  
  9. resultData.getColumnDisplaySize(columnIndex);列大小  
  10. }  

    从上面的代码可以看出,ResultSetMetaData能获取查询返回的数据集的内置信息,使用ResultSetMetaData能做很多事,

比如可以获取指定表的所有列信息并且可以通过IO创建对应的JAVABean;可以用反射给JAVABean自动赋数据表中的值等等。

 

 

  • DatabaseMetaData获取数据库元数据信息

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website