编写简单脚本导数据

意义：通过编写脚本对数据处理更加灵活，可以通过逻辑关系增强数据的安全性

一、公共方法

1. 数据库连接

    private ResultSet rs = null;
    private PreparedStatement ps = null;
    private Connection connection = null;
    /**
     * 获取数据库连接
     */
    public static Connection ConnectOracle() {
            Connection con = null;
            try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    System.out.println("开始尝试连接数据库！");
                    String url = "jdbc:oracle:" + "thin:@//XXX:1521/XXX";
                    String user = "XXX";
                    String password = "XXX";
                    con = DriverManager.getConnection(url, user, password);
                    System.out.println("连接成功！");
            } catch (Exception e) {
                    e.printStackTrace();
            }
            return con;
    }

2. 资源释放

public void closeAll() {
       try { // 释放资源
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (connection != null)
                connection.close();
       } catch (SQLException e) {  
            e.printStackTrace();
       }
}

二、遍历Excel取数据

import jxl.Sheet;

/**
	 * 获取 sheet 工具类
	 *
	 * @param file
	 * @return
	 */
	private static Sheet[] getSheet(String file) {
		FileInputStream fileInputStream = null;
		try {
			URL url = Thread.currentThread().getContextClassLoader().getResource(file);
			fileInputStream = new FileInputStream(url.getFile());
			Workbook workbook = Workbook.getWorkbook(fileInputStream);
			return workbook.getSheets();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				fileInputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return null;
}



    /**
     * 取excel数据
     *
     * @param file
     * @return
     */
    private static Map<String, Integer> getIdAndNameMap(String file) {
            Sheet[] sheets = getSheet(file);
            Map<String, Integer> map = new HashMap<String, Integer>();
            if (sheets == null) {
                    return map;
            }
            for (Sheet sheet : sheets) {
                    int rows = sheet.getRows();
                    for (int i = 1; i < rows; i++) {
                            Cell[] rowsCells = sheet.getRow(i);
                            System.out.print(rowsCells[0].getContents()+"   row:   ");
                            System.out.println(rowsCells[0].getRow());
                            /*if (rowsCells[5].getContents().trim().equals("1")) {
                                map.put(rowsCells[1].getContents().trim() + "&" + rowsCells[4].getContents().trim(),
                                                    Integer.parseInt(rowsCells[0].getContents().trim()));
                            }*/

                    }
            }
            return map;
    }

   /**
     * 测试
     * @param file
     * @return
     */  
public static void main(String[] args) {
        getIdAndNameMap("com/cits/category/tools/flushrc/product_class.xls");
    }

三、查询结果csv输出

	/**
	 * SQL结果输出（csv输出）
	 * 
	 * @param result
	 * @throws Exception
	 */
	public static void outputfilesqlresult(ResultSet result) throws Exception {
		FileWriter fileWriter = new FileWriter("C:/Users/wenrui/Desktop/output.csv");
		try {
			ResultSetMetaData meta = result.getMetaData();
			int columns = meta.getColumnCount();
			// 表头
			for (int i = 1; i <= columns; i++) {
				if (i == columns) {
					fileWriter.append(meta.getColumnName(i) + "\r\n");
				} else {
					fileWriter.append(meta.getColumnName(i) + ",");
				}
			}
			// 内容
			while (result.next()) {
				for (int i = 1; i <= columns; i++) {
					if (i == columns) {
						fileWriter.append(result.getString(i) + "\r\n");
					} else {
						fileWriter.append(result.getString(i) + ",");
					}
				}
			}
		} catch (Exception e) {
			System.out.println(e);
		} finally {
			result.close();
			fileWriter.close();
		}

	}

    /**
     * 测试入口 主方法
     */
    public static void main(String[] args) throws Exception {
        ConnectOracle();
        System.out.println(connection);
        ps = connection.prepareStatement("select * from CODE1 where ROWNUM <10");
        System.out.println(ps);
        rs = ps.executeQuery();
        outputfilesqlresult(rs);  
    }