导出 sqlserver数据表 为文本文件

package temp; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; /* * 根据DBConfig.properties文件中内容 导出指定表到硬盘目录 */ public class ExportTable { private String DBName = ""; private String tableName = ""; private String fileName = ""; private String userName = ""; private String password = ""; public ExportTable(){ DBConfig config = DBConfig.getInstance(); DBName = config.getDBNAME(); tableName = config.getTABLENAME(); fileName = config.getSAVEFILENAME(); userName = config.getDBUSERNAME(); password = config.getDBPASSWORD(); } /* * 拼导出命令 * return 拼接好的导出命令 */ // 导出数据 public String getDCString( ){ //导出指定列到 文本文件 String sql = "exec master..xp_cmdshell /'bcp /"Select number,type from "+ DBName+".."+tableName+"/" queryout /"" + fileName+"/" -c -t # -U "+userName+" -P "+password+"/'"; return sql; } public void tableToTxt(){ String sql = getDCString(); execute(sql); } public void txtToTable(String fileName){ String tempTableName = "a_driver_info_temp1"; String sql = "create table "+tempTableName+"( number varchar(20),type varchar(20))"; execute1(sql); System.out.println("建立临时表：【"+tempTableName+"】"); sql = "BULK INSERT vasp_db.."+tempTableName+" FROM '"+fileName+"'WITH ( FIELDTERMINATOR = '#',ROWTERMINATOR = '/n')"; execute1(sql); System.out.println("将【"+fileName+"】导入【"+tempTableName+"】表中~！"); sql = "delete a_driver_info_temp"; execute1(sql); System.out.println("清空【a_driver_info_temp】表"); sql = "insert into a_driver_info_temp(number,type) select distinct * from "+tempTableName; execute1(sql); System.out.println("将【"+tempTableName+"】导入到 ：【a_driver_info_temp】 中 "); sql = "drop table "+tempTableName; System.out.println("删除临时表【"+tempTableName+"】"); execute1(sql); } public void execute( String sql){ JDBCOperate jdbco = new JDBCOperate(); PreparedStatement ps = null; try{ // System.out.println(sql); ps = jdbco.getConnection().prepareStatement(sql); ResultSet rs = ps.executeQuery(); if(rs!=null){ rs.close(); } }catch(SQLException e){ System.out.println("导出文件失败"); e.printStackTrace(); }finally{ try{ if(ps!=null){ ps.close(); } }catch(SQLException e){ e.printStackTrace(); } } jdbco.closeConnection(); } public void execute1( String sql){ JDBCOperate jdbco = new JDBCOperate(); PreparedStatement ps = null; try{ // System.out.println(sql); ps = jdbco.getConnection().prepareStatement(sql); ps.executeUpdate(); }catch(SQLException e){ System.out.println("导出文件失败"); e.printStackTrace(); }finally{ try{ if(ps!=null){ ps.close(); } }catch(SQLException e){ e.printStackTrace(); } } jdbco.closeConnection(); } }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!