精华内容
下载资源
问答
  • 导出insertsql语句import java.io.BufferedWriter;import java.io.File;import java.io.FileWriter;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql....

    导出insertsql语句

    import java.io.BufferedWriter;

    import java.io.File;

    import java.io.FileWriter;

    import java.io.IOException;

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.ResultSet;

    import java.sql.ResultSetMetaData;

    import java.sql.SQLException;

    import java.sql.Statement;

    import java.sql.Types;

    import java.util.ArrayList;

    import java.util.List;

    public class Test {

    private static Connection conn=null;

    private static Statement sm = null;

    private static String schema="FJSTL";//模式名

    private static String select="SELECT * FROM";//查询sql

    private static String insert="INSERT INTO";//插入sql

    private static String values="VALUES";//values关键字

    private static String []table={"T_USER"};//table数组

    private static ListinsertList=new ArrayList();//全局存放insertsql文件的数据

    private static String filePath="E://insertSQL.txt";//绝对路径导出数据的文件

    /**

    *导出数据库表

    *@param args

    *@throws SQLException

    */

    public static void main(String[] args) throws SQLException { ListlistSQL=new ArrayList();

    connectSQL("oracle.jdbc.driver.OracleDriver", "xxx.xxx.xxx", "xxx", "xxx");//连接数据库

    listSQL=createSQL();//创建查询语句

    executeSQL(conn,sm,listSQL);//执行sql并拼装

    createFile();//创建文件

    }

    /**

    *创建insertsql.txt并导出数据

    */

    private static void createFile() {

    File file=new File(filePath);

    if(!file.exists()){

    try {

    file.createNewFile();

    } catch (IOException e) {

    System.out.println("创建文件名失败!!");

    e.printStackTrace();

    }

    }

    FileWriter fw=null;

    展开全文
  • import java.io.BufferedWriter; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import ...
    import java.io.BufferedWriter;
    import java.io.File;
    import java.io.FileWriter;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.sql.Types;
    import java.util.ArrayList;
    import java.util.List;
    
    public class testExportSql {
        private static Connection conn=null;
        private static Statement sm = null;
        private static String schema="***";//数据库名
        private static String select="SELECT * FROM";//查询sql
        private static String insert="INSERT INTO";//插入sql
        private static String values="VALUES";//values关键字
        private static String []table={"***"};//table数组
        private static List<String> insertList=new ArrayList<String>();//全局存放insertsql文件的数据
        private static String filePath="E://insertSQL.txt";//绝对路径 导出数据的文件
    
        private static String driver="com.mysql.jdbc.Driver";
        private static String url="jdbc:mysql://123.16.10.456:3306";
        private static String userName="root";
        private static String password="root";
    
        /**
         * 导出数据库表
         * @param args
         * @throws SQLException
         */
        public static void main(String[] args) throws SQLException {
            List<String> listSQL=new ArrayList<String>();
            connectSQL(driver, url, userName, password);//连接数据库
            listSQL=createSQL();//创建查询语句
            executeSQL(conn,sm,listSQL);//执行sql并拼装
            createFile();//创建文件
        }
        /**
         * 创建insertsql.txt并导出数据
         */
        private static void createFile() {
            File file=new File(filePath);
            if(!file.exists()){
                try {
                    file.createNewFile();
                } catch (IOException e) {
                    System.out.println("创建文件名失败!!");
                    e.printStackTrace();
                }
            }
            FileWriter fw=null;
            BufferedWriter bw=null;
            try {
                fw = new FileWriter(file);
                bw = new BufferedWriter(fw);
                if(insertList.size()>0){
                    for(int i=0;i<insertList.size();i++){
                        bw.append(insertList.get(i));
                        bw.append("\n");
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }finally{
                try {
                    bw.close();
                    fw.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        /**
         * 拼装查询语句
         * @return 返回select集合
         */
        private static List<String> createSQL() {
            List<String> listSQL=new ArrayList<String>();
            for(int i=0;i<table.length;i++){
                StringBuffer sb=new StringBuffer();
                sb.append(select).append(" ").append(schema).append(".").append(table[i]);
                listSQL.add(sb.toString());
            }
            return listSQL;
        }
        /**
         * 连接数据库 创建statement对象
         * @param driver
         * @param url
         * @param UserName
         * @param Password
         */
        public static void connectSQL(String driver,String url,String UserName,String Password){
            try{
                Class.forName(driver).newInstance();
                conn = DriverManager.getConnection(url, UserName, Password);
                sm=conn.createStatement();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        /**
         * 执行sql并返回插入sql
         * @param conn
         * @param sm
         * @param listSQL
         * @throws SQLException
         */
        public static void executeSQL(Connection conn,Statement sm,List listSQL) throws SQLException{
            List<String> insertSQL=new ArrayList<String>();
            ResultSet rs=null;
            try {
                rs = getColumnNameAndColumeValue(sm, listSQL, rs);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                if(rs!=null){
                    rs.close();
                }
                sm.close();
                conn.close();
            }
        }
        /**
         * 获取列名和列值
         * @param sm
         * @param listSQL
         * @param rs
         * @return
         * @throws SQLException
         */
        private static ResultSet getColumnNameAndColumeValue(Statement sm,
                                                             List listSQL, ResultSet rs) throws SQLException {
            if(listSQL.size()>0){
                for(int j=0;j<listSQL.size();j++){
                    String sql=String.valueOf(listSQL.get(j));
                    rs=sm.executeQuery(sql);
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    while(rs.next()){
                        StringBuffer ColumnName=new StringBuffer();
                        StringBuffer ColumnValue=new StringBuffer();
                        for(int i=1;i<=columnCount;i++){
                            if(rs.getString(i)!=null){
                                String value=rs.getString(i).trim();
                                    if(i==1){
                                        ColumnName.append(rsmd.getColumnName(i));
                                    }else{
                                        ColumnName.append(","+rsmd.getColumnName(i));
                                    }
                                    if(Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i)
                                            || Types.LONGVARCHAR == rsmd.getColumnType(i)){
                                        if(i==1){
                                            ColumnValue.append("'").append(value).append("',");
                                        }else if(i==columnCount){
                                            ColumnValue.append("'").append(value).append("'");
                                        }else{
                                            ColumnValue.append("'").append(value).append("'").append(",");
                                        }
                                    }else if(Types.SMALLINT == rsmd.getColumnType(i) || Types.INTEGER == rsmd.getColumnType(i)
                                            || Types.BIGINT == rsmd.getColumnType(i) || Types.FLOAT == rsmd.getColumnType(i)
                                            || Types.DOUBLE == rsmd.getColumnType(i) || Types.NUMERIC == rsmd.getColumnType(i)
                                            || Types.DECIMAL == rsmd.getColumnType(i)){
                                        if(i==1){
                                            ColumnValue.append(value).append(",");
                                        }else if(i==columnCount){
                                            ColumnValue.append(value);
                                        }else{
                                            ColumnValue.append(value).append(",");
                                        }
                                    }else if(Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i)
                                            || Types.TIMESTAMP == rsmd.getColumnType(i)){
                                        if(i==1){
                                            ColumnValue.append(value).append("',");
                                        }else if(i==columnCount){
                                            ColumnValue.append(value).append("'");
                                        }else{
                                            ColumnValue.append("'").append(value).append("'").append(",");
                                        }
                                    }else{
                                       if(i!=columnCount){
                                            ColumnValue.append(value).append(",");
                                       }
                                    }
                            }
    
    
                        }
                        System.out.println(ColumnName.toString());
                        System.out.println(ColumnValue.toString());
                        insertSQL(ColumnName, ColumnValue);
                    }
                }
            }
            return rs;
        }
        /**
         * 拼装insertsql 放到全局list里面
         * @param ColumnName
         * @param ColumnValue
         */
        private static void insertSQL(StringBuffer ColumnName,
                                      StringBuffer ColumnValue) {
            for(int i=0;i<table.length;i++){
                StringBuffer insertSQL=new StringBuffer();
                insertSQL.append(insert).append(" ")
                        .append(table[i]).append("(").append(ColumnName.toString())
                        .append(")").append(values).append("(").append(ColumnValue.toString()).append(");");
                insertList.add(insertSQL.toString());
                System.out.println(insertSQL.toString());
            }
        }
    }

     

    参考:https://www.iteye.com/blog/jlwangjinshuang-163-com-2157199

    参考博文有点小问题,代码报错,导出的sql也不能插入数据库,我这做了修正,但是核心方法是getColumnNameAndColumeValue 还很不错的值得借鉴

    展开全文
  • 由于公司数据库没有进行分库分表,导出上亿数据量时,内存溢出已经解决但是存在速度很慢的问题,在不允许动表情况下还未找到解决办法。 import java.io.BufferedReader; import java.io.BufferedWriter; import...

    由于公司数据库没有进行分库分表,导出上亿数据量时,内存溢出已经解决但是存在速度很慢的问题,在不允许动表情况下还未找到解决办法。


    import java.io.BufferedReader;
    import java.io.BufferedWriter;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.FileReader;
    import java.io.IOException;
    import java.io.InputStreamReader;
    import java.io.OutputStreamWriter;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.omg.CORBA.PUBLIC_MEMBER;
    
    import com.f1j.ss.t;
    import com.f1j.util.br;
    import com.lowagie.tools.concat_pdf;
    import com.sinosoft.lis.pubfun.PubFun;
    import com.sinosoft.utility.DBConnPool;
    import com.sinosoft.utility.ExeSQL;
    import com.sinosoft.utility.SSRS;
    
    public class LisTransferBL {
    	private static String path;
    	private final static String[] ss = { "T_POLICY_MAIN", "", "", "", "", "",
    			"", "" };
    	private static List<String> bugschema = new ArrayList<String>();
    	private static Map<String, String> bigschema = new HashMap<String, String>();
    	private static Map<String, String> yearschema = new HashMap<String, String>();
    	private static Map<String, String> caoschema = new HashMap<String, String>();;
    
    	static {
    		// 不能查询的表格
    		String[] s1 = { "RATE121501", "SUPPRISKSCORE240301", "MISETLDATE_2",
    				"SMT", "TEMPYJ", "LQGRPCONTDAYDATA", "LL", "LDCODE_2",
    				"RATE646_B", "RATE646_C", "TEMPGET", "STMT_SQL",
    				"TABLESPACE_TABL", "CONTROL_TABL", "CASHVALUE285",
    				"CASHVALUE237", "GETFEE330801", "CASHVALUE284", "RATE290",
    				"CASHVALUE290", "CASHVALUE238", "CTRATE331501", "RATE331501",
    				"GETFEE331501", "LXUPDATELOG", "STATSQLCONFIG_2009",
    				"RATE6601_1", "RATE6601_0", "CONTTEST", "TEMP330501:286707",
    				"Rate550806", "PAYTIMES662", "RATE6623",
    				"LPEDORESPECIALDATA0520", "RATE662", "LDMENU_BAK20090701",
    				"RISKDEATHRATE331601", "RATE6622", "LABANKMISION_S",
    				"RISKRATE331901", "FINPREM_YM_TB_01", "RATE6621", "POLNO",
    				"INTFDATATRACE_SH", "RATE243", "RATE277", "CASHVALUE239",
    				"CASHVALUE240", "RATE245", "RATE244", "TEMPYYL", "POLNO_2",
    				"LCGRPEDOR", "RATE2301", "RATE2601", "RATE5201", "RATE550706",
    				"RATE550806", "RE230801", "RE230901", "LOBGRPPOSITION",
    				"RATE6693", "RATE6692", "RATE6691", "LPCERTIFYINSURED",
    				"CASHVALUE208", "RATE208", "CASHVALUE217", "RATE217",
    				"LPGRPPOSITION", "CASHVALUE301", "CASHVALUE302",
    				"LBASCRIPTIONRULEFACTORY", "LPASCRIPTIONRULEFACTORY",
    				"LOBASCRIPTIONRULEFACTORY", "LBASCRIPTIONRULEPARAMS",
    				"LPASCRIPTIONRULEPARAMS", "LOBASCRIPTIONRULEPARAMS",
    				"LCGRPFEETOACC", "LBRISKZTFEE", "LPRISKZTFEE", "LOBRISKZTFEE",
    				"LDDINGHOSPITALB", "LQGRPPREMDATA", "LPRREPORTITEM",
    				"GETFEE332601", "GETFEE332501", "CTRATE332501",
    				"RISKRATE332501", "RATE304", "CASHVALUE309", "RATE309",
    				"CASHVALUE314", "RATE314", "RATE308", "TESTCONFIG",
    				"FORMU330501_YEAR", "CZL_TEST", "GETFEE332701", "CTRATE332701",
    				"LICRMCONTTRACK", "SUPPRISKSCORE231501B",
    				"SUPPRISKSCORE231501A", "RATE231501A", "RATE231501B",
    				"SUPPRISKSCORE231502A", "SUPPRISKSCORE231502B",
    				"PREMYZ333102A", "PREMYZ333102B", "PREMYZ231502A",
    				"PREMYZ231502B", "RATE231502A", "RATE231502B", "RATE333101A",
    				"RATE333101B", "RATE333102A", "RATE333102B", "CASHVALUE333601",
    				"RATE231601", "RATE333601", "SUPPRISKSCORE231601", "RATE5608",
    				"PLANQDWZBRIEF", "RATE561101", "LLCASEAFEHZ1t",
    				"LLCASEAFEHZ1s", "RATE551401", "RATE122101_2", "RATE122101_3",
    				"插入网点SQL", "LDCODE_DATA", "LLCLAIMUWMAIN_DATA",
    				"LLCLAIMUWMDETAIL_DATA", "ADMIN_MOVE_TABLE_YM",
    				"LLCASE20161012", "EXPLAIN_INSTANCE", "EXPLAIN_STATEMENT",
    				"EXPLAIN_ARGUMENT", "EXPLAIN_OBJECT", "EXPLAIN_OPERATOR",
    				"EXPLAIN_PREDICATE", "EXPLAIN_STREAM", "ADVISE_INSTANCE",
    				"ADVISE_INDEX", "ADVISE_WORKLOAD", "ADVISE_MQT",
    				"ADVISE_PARTITION", "ADVISE_TABLE", "EXPLAIN_ACTUALS",
    				"LRCESSLISTBAK" };
    
    		bugschema = Arrays.asList(s1);
    		// 没有makedate的大表
    		bigschema.put("LCGCUWERROR", "modifydate");
    		bigschema.put("CUST_INFO", "etltime");
    		bigschema.put("LCCUWERROR", "modifydate");
    		bigschema.put("LCGUWERROR", "modifydate");
    		bigschema.put("LCUWERROR", "modifydate");
    		bigschema.put("LDTASKRUNLOG", "executedate");
    		bigschema.put("HISTORY", "outputdate");
    		bigschema.put("LOBCUWERROR", "modifydate");
    		bigschema.put("LOBGCUWERROR", "modifydate");
    		bigschema.put("LOBUWERROR", "modifydate");
    		bigschema.put("LYRETURNFROMBANK", "modifydate");
    		bigschema.put("LCPENOTICEITEM", "modifydate");
    		bigschema.put("LICRMCONTINFO", "modifydate");
    		bigschema.put("TEMPRP", "paydate");
    		bigschema.put("POL_MAIN_BAK", "input_date");
    		bigschema.put("ENDO_FEE_BAK", "app_date");
    		bigschema.put("WFCONTLIST", "activedate");
    		bigschema.put("INTFDATATRACE", "policydate");
    		bigschema.put("WFTRANSLOG", "senddate");
    		bigschema.put("LRDUTY", "modifydate");
    		bigschema.put("ENDO_FEE", "app_date");
    		bigschema.put("TEMP_GOLDELEPHANT", "signdate");
    		bigschema.put("TEMP_GOLDELEPHANT2", "signdate");
    		bigschema.put("CLAIM_MAIN", "app_date");
    		bigschema.put("POL_MAIN", "input_date");
    		bigschema.put("LIDATATRANSRESULT", "accountdate");
    		bigschema.put("FIVOUCHERDATADETAIL", "accountdate");
    		bigschema.put("LIABORIGINALDATA", "paydate");
    		bigschema.put("LLCASEOPTIME", "startdate");
    		bigschema.put("LYRETURNFROMBANKB", "modifydate");
    		bigschema.put("LKELECTRONCONT", "contsenddate");
    
    		// 只保存年
    
    		yearschema.put("LFTZXML", "statyear");
    		yearschema.put("LFRLXML", "statyear");
    		yearschema.put("LFEXCELCOLL", "statyear");
    		yearschema.put("LFXMLCOLL", "statyear");
    
    		// 特殊
    
    		caoschema.put("T_POLICY_MAIN", "datasign"); // 20100824暂不会处理
    
    	}
    
    	// 执行全量导入
    	public boolean submitFull(String starttime, String endtime, String path) {
    		this.path = path;
    		fullupdate(starttime, endtime);
    
    		return true;
    	}
    
    	// 生成全量导入
    	public static void fullupdate(String startdate, String enddate) {
    
    		System.out.println("START FULL!");
    		String full = "";
    		// 获取所有表名
    		// String mSQL =
    		// "select name from sysibm.systables where type='T' and creator='DB2INST1' and name='LCCONT'";//单表测试sql
    		String mSQL = "select name from sysibm.systables where type='T' and creator='DB2INST1'";
    		SSRS mSSRS = new ExeSQL().execSQL(mSQL);
    		for (int i = 1; i <= mSSRS.getMaxRow(); i++) {
    			String table = mSSRS.GetText(i, 1);
    			try {
    
    				System.out.println("正在导出:" + table);
    
    				String sql = "";
    
    				if (bugschema.contains(table)) {
    					System.out.println("查询不了的表格,跳过");
    					continue;
    				} else if (yearschema.containsKey(table)) {
    					String where = yearschema.get(table);
    					String startyear = startdate.split("-")[0];
    					String endyear = enddate.split("-")[0];
    					sql = "select * from DB2INST1." + table
    							+ " where statyear>='" + startyear
    							+ "' and statyear<'" + endyear + "'";
    
    				} else if (bigschema.containsKey(table)) {
    					String where = bigschema.get(table);
    					sql = "select * from DB2INST1." + table + " where " + where
    							+ ">='" + startdate + "' and " + where + "<'"
    							+ enddate + "'";
    
    				} else {
    					String pdsql = "select 1 from sysibm.syscolumns where tbname='"
    							+ table.toUpperCase() + "' and name='MAKEDATE'";
    					SSRS pdSSRS = new ExeSQL().execSQL(pdsql);
    					if (pdSSRS.getMaxRow() != 0) {
    						sql = "select * from DB2INST1." + table
    								+ " where makedate>='" + startdate
    								+ "'  and makedate<'" + enddate + "'";
    
    					} else {
    						sql = "select * from DB2INST1." + table;
    					}
    
    				}
    				String countsql = "select count(1) from  db2inst1." + table;
    				SSRS countSSRS = new ExeSQL().execSQL(countsql);
    				int max = Integer.parseInt(countSSRS.GetText(1, 1));
    
    				// 区分大小表
    				/*
    				 * 对于超5w的大表 通过分页每次导出5000,避免导出过多内存溢出 但该功能随着数据量的大小所消耗时间会增加。
    				 * 通过使用fetch优化缩短一半时间,但是2亿数据还是很慢,不懂如何继续改进
    				 */
    
    				if (max >= 50000) {
    					int min = 0;
    
    					System.out.println("开始分批导出:");
    					while (min != max) {
    						System.out.println("开始分批导出:从第" + min + "行开始导出5000行");
    						String maxsql = "SELECT * FROM "
    								+ "( SELECT B.*, ROWNUMBER() OVER() AS RN FROM ("
    								+ sql + ") AS B" + ")AS A WHERE A.RN >=" + min
    								+ " fetch first 5000 rows only";
    						// 生成cvs
    						SSRS tSSRS = new ExeSQL().execSQL(maxsql);
    						String url = path + "/data/lis/" + table.toLowerCase();
    						full = url + "/full//" + changedate(startdate)
    								+ "_21_30_01" + "-" + changedate(enddate)
    								+ "_21_30_01//"; // 20161001_21_30_01
    						createDir(path + "/data");
    						createDir(path + "/data/lis/");
    						createDir(url);
    						createDir(url + "/full//");
    						createDir(full);
    
    						writrCVS(tSSRS, full, "data_full.csv");
    
    						if (min + 5000 >= max) {
    
    							min = max;
    
    						} else {
    							min += 5000;
    						}
    
    					}
    					;
    					System.out.println("分批导出完毕,共计" + max);
    					try {
    						createSql(table, full, "schema.sql");
    
    					} catch (Exception e) {
    
    						e.printStackTrace();
    					}
    					try {
    						createFile(full + "upload_completed");
    					} catch (IOException e) {
    						// TODO Auto-generated catch block
    						e.printStackTrace();
    					}
    
    				} else {
    					// 生成cvs
    					SSRS tSSRS = new ExeSQL().execSQL(sql);
    					String url = path + "/data/lis/" + table.toLowerCase();
    					full = url + "/full//" + changedate(startdate)
    							+ "_21_30_01" + "-" + changedate(enddate)
    							+ "_21_30_01//"; // 20161001_21_30_01
    					createDir(path + "/data");
    					createDir(path + "/data/lis/");
    					createDir(url);
    					createDir(url + "/full//");
    					createDir(full);
    
    					writrCVS(tSSRS, full, "data_full.csv");
    
    					createSql(table, full, "schema.sql");
    					createFile(full + "upload_completed");
    
    				}
    			} catch (Exception e) {
    				writeTxt2("导出" + table + "出错", path + "/data/bug.txt");
    				continue;
    			}
    
    		}
    		System.out.println("END");
    
    	}
    
    	// 增量导入
    	public void increaseupdate(String date) {
    		System.out.println("START INCREASE!");
    		String mSQL = "select name from sysibm.systables where type='T' and creator='DB2INST1'";
    		SSRS mSSRS = new ExeSQL().execSQL(mSQL);
    		for (int i = 1; i <= mSSRS.getMaxRow(); i++) {
    			String table = mSSRS.GetText(i, 1);
    			String sql = "";
    			if (bugschema.contains(table)) {
    				System.out.println("查询不了的表格,跳过");
    				continue;
    			} else if (yearschema.containsKey(table)) {
    				String where = yearschema.get(table);
    				String startyear = date.split("-")[0];
    
    				sql = "select * from " + table + " where statyear=" + startyear
    						+ "";
    
    			} else if (bigschema.containsKey(table)) {
    				String where = bigschema.get(table);
    				sql = "select * from " + table + " where " + where + "='"
    						+ date + "'";
    
    			} else {
    				String pdsql = "select 1 from sysibm.syscolumns where tbname='"
    						+ table.toUpperCase() + "' and name='MAKEDATE'";
    				SSRS pdSSRS = new ExeSQL().execSQL(pdsql);
    				if (pdSSRS.getMaxRow() != 0) {
    					sql = "select * from " + table + " where makedate='" + date
    							+ "'";
    
    				} else {
    					System.out.println("固定表" + table + "不进行增量导入");
    					continue;
    				}
    
    			}
    			String countsql = "select count(1) from  db2inst1." + table;
    			SSRS countSSRS = new ExeSQL().execSQL(countsql);
    			int max = Integer.parseInt(countSSRS.GetText(1, 1));
    
    			// 区分大小表
    
    			if (max >= 50000) {
    				int min = 0;
    
    				String url = "";
    				String full = "";
    
    				while (min != max) {
    					System.out.println("开始分批导出:从第" + min + "行开始导出5000行");
    					String maxsql = "SELECT * FROM "
    							+ "( SELECT B.*, ROWNUMBER() OVER() AS RN FROM ("
    							+ sql + ") AS B" + ")AS A WHERE A.RN >=" + min
    							+ " fetch first 5000 rows only";
    					// 生成cvs
    					SSRS tSSRS = new ExeSQL().execSQL(maxsql);
    					url = path + "/data/lis/" + table.toLowerCase();
    					full = url + "/incremental//" + changedate(date)
    							+ getCurrentTime() + "//";
    					createDir(path + "/data");
    					createDir(path + "/data/lis/");
    					createDir(url);
    					createDir(url + "/incremental//");
    					createDir(full);
    
    					writrCVS(tSSRS, full, "data_insert_updated.csv");
    					if (min + 5000 >= max) {
    
    						min = max;
    
    					} else {
    						min += 5000;
    					}
    
    				}
    				;
    				System.out.println("分批导出完毕,共计" + max);
    				try {
    					// 创建创表语句,为提升效率改为从全量数据文件夹粘贴
    					// createSql(table, full, "schema.sql");
    					createFile(full + "schema.sql");
    					coby(url
    							+ "/full//20050101_21_30_01-20100101_21_30_01//schema.sql",
    							full + "schema.sql");
    					createFile(full + "upload_completed");
    				} catch (Exception e) {
    
    					e.printStackTrace();
    				}
    
    			} else {
    				SSRS tSSRS = new ExeSQL().execSQL(sql);
    				String url = path + "/data/lis/" + table.toLowerCase();
    				String full = url + "/incremental//" + changedate(date)
    						+ getCurrentTime() + "//";
    				createDir(path + "/data");
    				createDir(path + "/data/lis/");
    				createDir(url);
    				createDir(url + "/incremental//");
    				createDir(full);
    
    				writrCVS(tSSRS, full, "data_insert_updated.csv");
    				try {
    					// 创建创表语句,为提升效率改为从全量数据文件夹粘贴
    					// createSql(table, full, "schema.sql");
    					createFile(full + "schema.sql");
    					coby(url
    							+ "/full//20050101_21_30_01-20100101_21_30_01//schema.sql",
    							full + "schema.sql");
    					createFile(full + "upload_completed");
    				} catch (Exception e) {
    
    					e.printStackTrace();
    				}
    
    			}
    
    		}
    		System.out.println("END");
    	}
    
    	/*
    	 * 创建目录 如果存在则不创建
    	 */
    	public static boolean createDir(String destDirName) {
    		File dir = new File(destDirName);
    		if (dir.exists()) {// 判断目录是否存在
    
    			return true;
    		}
    
    		if (dir.mkdirs()) {// 创建目标目录
    			System.out.println("创建目录成功!" + destDirName);
    			return true;
    		} else {
    			System.out.println("创建目录失败!");
    			return false;
    		}
    	}
    
    	/*
    	 * 创建文档,存在则不创建
    	 */
    
    	public static boolean createFile(String FileName) throws IOException {
    		File dir = new File(FileName);
    		if (dir.exists()) {// 判断目录是否存在
    
    			return true;
    		}
    
    		if (dir.createNewFile()) {// 创建目标目录
    			System.out.println("创建文件成功!" + FileName);
    			return true;
    		} else {
    			System.out.println("创建文件失败!");
    			return false;
    		}
    
    	}
    
    	/*
    	 * 写入cvs 编码:UTF8 以“|”隔开
    	 */
    	public static void writrCVS(SSRS tSSRS, String url, String filename) {
    		System.out.println("START WRITECVS!");
    		FileOutputStream tFileOutputStream;
    		try {
    			createFile(url + filename);
    			tFileOutputStream = new FileOutputStream(url + filename, true);
    			// tFileOutputStream = new FileOutputStream(url + filename);
    			OutputStreamWriter tOutputStreamWriter = new OutputStreamWriter(
    					tFileOutputStream, "utf-8");
    
    			BufferedWriter mBufferedWriter = new BufferedWriter(
    					tOutputStreamWriter);
    			for (int i = 1; i <= tSSRS.getMaxRow(); i++) {
    				StringBuffer sb = new StringBuffer();
    				for (int j = 1; j <= tSSRS.getMaxCol(); j++) {
    					String info = tSSRS.GetText(i, j);
    					sb.append(info);
    					sb.append("|");
    
    				}
    				sb.deleteCharAt(sb.lastIndexOf("|"));
    				String string = sb.toString();
    				// System.out.println(string);
    				mBufferedWriter.write(string);
    				mBufferedWriter.newLine();
    				mBufferedWriter.flush();
    			}
    			mBufferedWriter.close();
    			tOutputStreamWriter.close();
    			tFileOutputStream.close();
    		} catch (Exception e) {
    			System.out.println("创建文件失败");
    			writeTxt2("导出" + filename + "出错", path + "/data/bug.txt");
    		}
    		System.out.println("END");
    	}
    
    	/*
    	 * 自动生成建表sql 目前如果为空表不确定能否生成TAT 如果生成过,会重写覆盖
    	 */
    	public static void createSql(String table, String url, String name)
    			throws SQLException {
    		Connection conn = null;
    		try {
    
    			System.out.println("START CREATESQL!");
    			// 用于拼凑字符串
    			StringBuffer sb = new StringBuffer();
    			// 查找表格列是否可以为空,表名和是否可以为空存入map
    			String tSQL = "select name,nulls from sysibm.syscolumns where tbname='"
    					+ table + "'";
    			SSRS tSSRS = new ExeSQL().execSQL(tSQL);
    			Map<String, String> map = new HashMap<String, String>();
    			for (int i = 1; i <= tSSRS.getMaxRow(); i++) {
    				String value = "";
    				if ("Y".equals(tSSRS.GetText(i, 2))) {
    					value = " ";
    				} else {
    					value = "NOT NULL";
    				}
    				map.put(tSSRS.GetText(i, 1), value);
    
    			}
    			// 查找表格主键 主键列名存入list
    			String cSQL = "select colname from SYSCAT.KEYCOLUSE where tabname='"
    					+ table + "'";
    			SSRS cSSRS = new ExeSQL().execSQL(cSQL);
    			List<String> list = new ArrayList<String>();
    			if (cSSRS.getMaxRow() == 0) {
    
    			} else {
    				for (int i = 1; i <= cSSRS.getMaxRow(); i++) {
    
    					list.add(cSSRS.GetText(i, 1));
    
    				}
    			}
    			// 为了查找顺序列名的sql,好像必须有数据,未测试空表
    			String sql = "select * from db2inst1." + table
    					+ " fetch first 1 rows only with ur";
    
    			String columnName = "";
    			String columnTypeName = "";
    			int columnDisplaySize = 0;
    			int max = 0;
    			conn = DBConnPool.getConnection();
    			Statement stmt = conn.createStatement();
    			ResultSet rs = stmt.executeQuery(sql);
    			ResultSetMetaData data = rs.getMetaData();
    
    			sb.append("CREATE TABLE " + table + " (");
    			rs.next();
    			for (int i = 1; i <= data.getColumnCount(); i++) {
    				// 获得指定列的列名
    				columnName = data.getColumnName(i);
    				// 获得指定列的数据类型名
    				columnTypeName = data.getColumnTypeName(i);
    				// 在数据库中类型的最大字符个数
    				columnDisplaySize = data.getColumnDisplaySize(i);
    				// 保留位数
    				max = data.getScale(i);
    				// System.out.println(columnName + "," + columnTypeName + ","+
    				// columnDisplaySize);
    				String nulls = map.get(columnName);
    				sb.append(columnName + " ");
    				// 有小数点约定的
    				if ("DECIMAL".equals(columnTypeName)) {
    					sb.append(columnTypeName + "(" + columnDisplaySize + ","
    							+ max + ") " + nulls);
    				} else {
    					sb.append(columnTypeName + "(" + columnDisplaySize + ") "
    							+ nulls);
    				}
    
    				if (list.contains(columnName)) {
    					sb.append(" " + " primary key,\n");
    				} else {
    					sb.append(" " + ",\n");
    				}
    			}
    			// 去掉末尾多余,
    			sb.deleteCharAt(sb.lastIndexOf(","));
    			sb.append(");");
    			String sqlString = sb.toString();
    			FileOutputStream tFileOutputStream;
    			try {
    				String filename = url + "//" + name;
    				createFile(filename);
    				// tFileOutputStream = new FileOutputStream(filename, true);
    				// 不会覆盖,会续写
    				tFileOutputStream = new FileOutputStream(filename);
    				OutputStreamWriter tOutputStreamWriter = new OutputStreamWriter(
    						tFileOutputStream, "utf-8");
    
    				BufferedWriter mBufferedWriter = new BufferedWriter(
    						tOutputStreamWriter);
    				mBufferedWriter.write(sqlString);
    
    				mBufferedWriter.close();
    				tOutputStreamWriter.close();
    				tFileOutputStream.close();
    			} catch (Exception e) {
    				System.out.println("创建文件失败");
    				e.printStackTrace();
    			}
    
    			rs.close();
    
    			System.out.println("END");
    		} catch (Exception e) {
    			writeTxt2("创建" + table + "的sql语句出错", path + "/data/bug.txt");
    		} finally {
    			conn.close();
    		}
    
    	}
    
    	public static void coby(String inputpath, String outputpath) {
    		byte[] dates = new byte[1024];
    		FileInputStream fis = null;
    		FileOutputStream fos = null;
    		try {
    			fis = new FileInputStream(inputpath);
    			fos = new FileOutputStream(outputpath);
    			long time1 = System.currentTimeMillis();
    			int i = fis.read(dates);
    			while (i != -1) {
    				fos.write(dates, 0, i);
    				i = fis.read(dates);
    			}
    			long time2 = System.currentTimeMillis();
    			long time3 = time2 - time1;
    			System.out.println(time3);
    		} catch (Exception e) {
    			// TODO: handle exception
    		} finally {
    			try {
    				fos.close();
    				fis.close();
    			} catch (IOException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    
    		}
    
    	}
    
    	/*
    	 * 日期格式转换 yyyy-mm-dd to yyyymmdd
    	 */
    	public static String changedate(String date) {
    		String[] s = date.split("-");
    		StringBuffer sb = new StringBuffer();
    		for (String s1 : s) {
    			sb.append(s1);
    		}
    
    		return sb.toString();
    
    	}
    
    	// 输出txt文件,会覆盖之前写的文件
    	public static void writeTxt(String bl, String filepath) {
    
    		// 确定写出文件的位置
    		try {
    			createFile(filepath);
    		} catch (IOException e1) {
    			e1.printStackTrace();
    		}
    		File file = new File(filepath);
    		// 建立输出字节流
    		PrintWriter pw = null;
    		try {
    			pw = new PrintWriter(file);
    			pw.println(bl);
    			System.out.println("写入成功");
    		} catch (FileNotFoundException e) {
    			e.printStackTrace();
    		} finally {
    			pw.close();
    		}
    	}
    
    	// 输出TXT 不会覆盖之前文件,会续写
    	public static void writeTxt2(String bl, String filepath) {
    		FileOutputStream tFileOutputStream = null;
    		OutputStreamWriter tOutputStreamWriter = null;
    		BufferedWriter mBufferedWriter = null;
    		try {
    			createFile(filepath);
    			tFileOutputStream = new FileOutputStream(filepath, true);
    			// tFileOutputStream = new FileOutputStream(url + filename);
    			tOutputStreamWriter = new OutputStreamWriter(tFileOutputStream,
    					"utf-8");
    
    			mBufferedWriter = new BufferedWriter(tOutputStreamWriter);
    			mBufferedWriter.write(bl);
    			mBufferedWriter.newLine();
    			mBufferedWriter.flush();
    
    		} catch (IOException e) {
    
    			e.printStackTrace();
    		} finally {
    			try {
    				mBufferedWriter.close();
    				tOutputStreamWriter.close();
    				tFileOutputStream.close();
    			} catch (IOException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    
    		}
    
    	}
    
    	// 检查是否有表格未导出完全
    	public static void checkTxt() {
    		createDir(path + "/data/");
    		createDir(path + "/data/lis/");
    		createDir(path + "/data/lis/bug/");
    		File f1 = new File(path + "/data/lis/bug");
    		File[] f2 = f1.listFiles();
    		BufferedReader br = null;
    		for (File txt : f2) {
    			String filename = txt.getName();
    			if (filename.endsWith(".txt")) {
    				try {
    					br = new BufferedReader(new FileReader(txt));
    					String info = br.readLine();
    					String[] s = info.split("|");
    					String tablename = s[0];
    					String time = s[1];
    					String name = s[2];
    					String bl = s[3];
    					if (bl.indexOf("false") != -1) {
    						String sql = "select * from " + tablename
    								+ " where makedate='" + time + "'";
    						SSRS tSSRS = new ExeSQL().execSQL(sql);
    						String url = path + "/data/lis/"
    								+ tablename.toLowerCase();
    						String full = url + "/incremental//" + name + "//";
    						createDir(path + "/data");
    						createDir(path + "/data/lis/");
    						createDir(url);
    						createDir(url + "/incremental//");
    						createDir(full);
    						writrCVS(tSSRS, full, "data_insert_updated.csv");
    
    					}
    
    				} catch (Exception e) {
    
    					e.printStackTrace();
    				} finally {
    					try {
    						br.close();
    					} catch (IOException e) {
    
    						e.printStackTrace();
    					}
    				}
    			}
    		}
    	}
    
    	public static String getCurrentTime() {
    		String time = PubFun.getCurrentTime();
    		String currenttime = time.replaceAll(":", "_");
    
    		return currenttime;
    
    	}
    
    	// 该方法于导出数据库信息无关
    	// 用于生成导入EXCEL时需要的配置文件
    	public static void createxml(String[] tables, String[] confignames) {
    		StringBuffer sb = new StringBuffer();
    		sb.append("<CONFIG>\n");
    		for (String table : tables) {
    			int a = 0;
    			sb.append("<" + confignames[a] + ">\n");
    			sb.append("<COL0></COL0>\n");
    			System.out.println("START CREATEXML!");
    			Connection conn = null;
    			try {
    				// 为了查找顺序列名的sql,好像必须有数据,未测试空表
    				String sql = "select * from db2inst1." + table
    						+ " fetch first 1 rows only with ur";
    				String columnName = "";
    				String columnTypeName = "";
    				int columnDisplaySize = 0;
    				int max = 0;
    				conn = DBConnPool.getConnection();
    				Statement stmt = conn.createStatement();
    				ResultSet rs = stmt.executeQuery(sql);
    				ResultSetMetaData data = rs.getMetaData();
    				rs.next();
    
    				int j = 1;
    				// 由于我从第二个列开始需要导入所以i=2,如果表格全部导入为i=1
    				for (int i = 2; i <= data.getColumnCount(); i++) {
    					// 获得指定列的列名
    					columnName = data.getColumnName(i);
    					sb.append("<COL" + j + ">" + columnName + "</COL" + j
    							+ ">\n");
    					j += 1;
    				}
    				sb.append("</" + confignames[a] + ">\n");
    				rs.close();
    				a += 1;
    			} catch (Exception e) {
    
    			} finally {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    
    		}
    		sb.append("</CONFIG>\n");
    		String sqlString = sb.toString();
    
    		writeTxt2(sqlString, "F:/XFYJ.xml");
    		System.out.println("END");
    	}
    
    	// 该方法于导出数据库信息无关
    	// 用于生成导入EXCEL时需要的配置文件,只输出到所需列数,最后一列不会取到
    	public static void createxml(String[] tables, String[] confignames,
    			int startcol, int endcol) {
    		StringBuffer sb = new StringBuffer();
    		sb.append("<CONFIG>\n");
    		for (String table : tables) {
    			int a = 0;
    			sb.append("<" + confignames[a] + ">\n");
    			sb.append("<COL0></COL0>\n");
    			System.out.println("START CREATEXML!");
    			Connection conn = null;
    			try {
    				// 为了查找顺序列名的sql,好像必须有数据,未测试空表
    				String sql = "select * from db2inst1." + table
    						+ " fetch first 1 rows only with ur";
    				String columnName = "";
    				String columnTypeName = "";
    				int columnDisplaySize = 0;
    				int max = 0;
    				conn = DBConnPool.getConnection();
    				Statement stmt = conn.createStatement();
    				ResultSet rs = stmt.executeQuery(sql);
    				ResultSetMetaData data = rs.getMetaData();
    				rs.next();
    				if (endcol > data.getColumnCount()) {
    					endcol = data.getColumnCount();
    				}
    
    				int j = 1;
    				// 由于我从第二个列开始需要导入所以i=2,如果表格全部导入为i=1
    				for (int i = startcol; i <= endcol; i++) {
    					// 获得指定列的列名
    					columnName = data.getColumnName(i);
    					sb.append("<COL" + j + ">" + columnName + "</COL" + j
    							+ ">\n");
    					j += 1;
    				}
    				sb.append("</" + confignames[a] + ">\n");
    				rs.close();
    				a += 1;
    			} catch (Exception e) {
    
    			} finally {
    				try {
    					conn.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					e.printStackTrace();
    				}
    			}
    
    		}
    		sb.append("</CONFIG>\n");
    		String sqlString = sb.toString();
    
    		writeTxt2(sqlString, "F:/XFYJ.xml");
    		System.out.println("END");
    	}
    	// 导出特定表的信息
    		public static void createtable(String [] tables) {
    			
    			for (int i = 0; i <tables.length; i++) {
    				String table = tables[i];
    				String sql = "select * from "+table+" where prtno='1130002645147'with ur";
    				
    					SSRS tSSRS = new ExeSQL().execSQL(sql);
    					String url =  "F:/data/lis/" + table.toLowerCase();
    					String full = url + "/incremental//" ;
    					createDir( "F:/data");
    					createDir("F:/data/lis/");
    					createDir(url);
    					
    					createDir(full);
    
    					writrCVS(tSSRS, full, "data.csv");
    //					try {
    //						// 创建创表语句,为提升效率改为从全量数据文件夹粘贴
    //						// createSql(table, full, "schema.sql");
    //						createFile(full + "schema.sql");
    //						coby(url
    //								+ "/full//20050101_21_30_01-20100101_21_30_01//schema.sql",
    //								full + "schema.sql");
    //						createFile(full + "upload_completed");
    //					} catch (Exception e) {
    //
    //						e.printStackTrace();
    //					}
    
    				
    
    			}
    			System.out.println("END");
    		}
    
    
    	public static void main(String[] args) throws Exception {
    		// 查询所有表
    		// String tSQL =
    		// "SELECT TABNAME FROM SYSCAT.TABLES where type='T' and creator='DB2INST1' ";
    		// select name from sysibm.systables where type='T' and
    		// creator='DB2INST1' ;
    		// LisTransferBL l1=new LisTransferBL();
    		// l1.submitFull("2005-01-01", "2010-01-01", "F://");
    		// System.out.println(PubFun.getCurrentTime());
    		// String mSQL =
    		// "select * from LCCONT where makedate>='2005-01-01' and makedate<='2006-01-01'";
    		// String maxsql="SELECT * FROM "
    		// +"( SELECT B.*, ROWNUMBER() OVER() AS RN FROM"
    		// +mSQL+") AS B"
    		// +")AS A WHERE A.RN BETWEEN 1 AND 10000";
    		// SSRS mSSRS = new ExeSQL().execSQL(mSQL);
    		// String url = "F://"+"/data/lis/" + "lccont";
    		// String full = url + "/incremental//20170410" + "//";
    		// createDir("F://"+"/data");
    		// createDir("F://"+"/data/lis/");
    		// createDir(url);
    		// createDir(url + "/incremental//");
    		// createDir(full);
    		//
    		// writrCVS(mSSRS, full, "data_insert_updated.csv");
    		// try {
    		// //创建创表语句,为提升效率改为从全量数据文件夹粘贴
    		// createSql("LCCONT", full, "schema.sql");
    		//
    		//
    		// createFile(full+"upload_completed");
    		// } catch (Exception e) {
    		//
    		// e.printStackTrace();
    		// }
    		// System.out.println(PubFun.getCurrentTime());
    //		String[] tables = { "XFYJAPPNTINFO","XFYJINSUREDINFO"};
    //		String[] conf = { "AppntInfo","InsuredInfo" };
    //		createxml(tables, conf);
    		String [] tables={"LCCONT","LCINSURED","LCAPPNT","LCPOL"};
    		createtable(tables);
    	}
    }
    

    展开全文
  • java类实现导出各种数据库insert语句导出insertsql语句import java.io.BufferedWriter;import java.io.File;import java.io.FileWriter;import java.io.IOException;import java.sql.Connection;import java.sql....

    java类实现导出各种数据库insert语句

    导出insertsql语句

    import java.io.BufferedWriter;

    import java.io.File;

    import java.io.FileWriter;

    import java.io.IOException;

    import java.sql.Connection;

    import java.sql.DriverManager;

    import java.sql.ResultSet;

    import java.sql.ResultSetMetaData;

    import java.sql.SQLException;

    import java.sql.Statement;

    import java.sql.Types;

    import java.util.ArrayList;

    import java.util.List;

    public class Test {

    private static Connection conn=null;

    private static Statement sm = null;

    private static String schema="FJSTL";//模式名

    private static String select="SELECT * FROM";//查询sql

    private static String insert="INSERT INTO";//插入sql

    private static String values="VALUES";//values关键字

    private static String []table={"T_USER"};//table数组

    private static List insertList=new ArrayList();//全局存放insertsql文件的数据

    private static String filePath="E://insertSQL.txt";//绝对路径 导出数据的文件

    /**

    * 导出数据库表

    * @param args

    * @throws SQLException

    */

    public static void main(String[] args) throws SQLException {

    List listSQL=new ArrayList();

    connectSQL("oracle.jdbc.driver.OracleDriver", "xxx.xxx.xxx", "xxx", "xxx");//连接数据库

    listSQL=createSQL();//创建查询语句

    executeSQL(conn,sm,listSQL);//执行sql并拼装

    createFile();//创建文件

    }

    /**

    * 创建insertsql.txt并导出数据

    */

    private static void createFile() {

    File file=new File(filePath);

    if(!file.exists()){

    try {

    file.createNewFile();

    } catch (IOException e) {

    System.out.println("创建文件名失败!!");

    e.printStackTrace();

    }

    }

    FileWriter fw=null;

    BufferedWriter bw=null;

    try {

    fw = new FileWriter(file);

    bw = new BufferedWriter(fw);

    if(insertList.size()>0){

    for(int i=0;i

    bw.append(insertList.get(i));

    bw.append("\n");

    }

    }

    } catch (IOException e) {

    e.printStackTrace();

    }finally{

    try {

    bw.close();

    fw.close();

    } catch (IOException e) {

    e.printStackTrace();

    }

    }

    }

    /**

    * 拼装查询语句

    * @return 返回select集合

    */

    private static List cre

    展开全文
  • java导出数据库sql脚本

    2021-06-15 10:55:47
    private static Connection conn = null; private static Statement sm = null; private static String schema = "lp_library";//模式名 private static String select = "SELECT ...//插入sql private static String .
  • insertSQL = new ArrayList();ResultSet rs = null;try {rs = getColumnNameAndColumeValue(sm, listSQL, rs);} catch (SQLException e) {e.printStackTrace();} finally {rs.close();sm.close();...
  • 选中某条记录,导出该记录的插入(insert)语句
  • java 实现数据库导出 sql文件

    千次阅读 2020-01-07 14:48:34
    导出mysql数据库之后,发出一条FLUSH PRIVILEGES 语句。为了正确恢复,该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。 mysqldump -uroot -p --all-databases --flush-privileges 33...
  • 前言工作中遇到批量往数据库插入全量数据的情况,在插入之前,需要删除原有数据,在删除之前,需要备份老数据。mysql自带导出数据的工具mysqldump,它的命令如下:命令mysqldump -u用戶名 -p密码 -d 数据库名 表名 &...
  • 最近的项目需要导入大量的数据,插入的过程中还需要边查询边插入插入的数据量在100w左右。一开始觉得100w的数据量不大,于是就插啊插,吃了个饭,回来一看,在插入了50多w条数据后,每秒就只能插10条了。。觉得很...
  • 【随笔】postgresql导出sql插入语句

    千次阅读 2017-04-04 17:07:11
    但,有时会需要使用其进行导出类似insert的语句,来完成对其他数据库的导入。 现提供方法如下: pg_dump -U postgres --inserts > 1234.sql xxx 在Postgresql的服务器,进行如上命令的执行进行sql备份,从而能够...
  • SQL Server导出建表语句和插入语句,包括当前数据库中已有的数据信息 1、找到要进行操作的表所在的数据库,右键-->任务-->生成脚本 2、首页是简介,可直接点击下一步 3、第二页是选择要生成脚本的...
  • Springboot Controller中使用service调SQL语句的方法,动态获取数据库信息添加到WORD表格中,检查到的数据绘制为柱形图(直方图)添加到文档中,并生成word(含表格,柱状图)存储到本地,在浏览器下载本地的word文档
  • 前言:项目需要导出数据库数据到本地文件,要求格式为excel或者文本文件,excel可以采用java的 Apache POI进行导出数据;当然也可以用Apache POI导出为txt文件。但感觉用ooxml导出为txt文件感觉比较麻烦 而且效率感觉...
  • 序列的主要作用是生成表的主键值,可以在插入语句中使用,也可以通过检查当前值,使得序列曾只下一个值。创建序列创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下:CREATE SEQUENCE序列名[INCREMENT BY n]...
  • 这里博主为了偷懒,就没有使用mybatis以及前端页面了,纯...Cs.java - - - 主方法类 package com.export;   import java.util.List; import java.util.Map;   import javax.swing.JFileChooser; impor...
  • mysql导出insert语句

    千次阅读 2021-02-04 16:56:58
    --disable-keys 告诉 mysqldump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!...导出一个数据库结构 mysqldump -u wcnc -p -d –add-drop-table smgp_apps_w...
  • Java代码实体类生成SQL语句Java实体类转数据库

    万次阅读 多人点赞 2021-07-19 17:43:04
    有的时候把数据库删了,如果照着实体类重新创建数据库的话比较麻烦,可以使用这个工具,把代码复制到项目里面设置一下即可把Java代码中的实体类转换为SQL语句输出为一个文件,打开执行命令即可。 代码如下: ...
  • Java中Date插入数据库的一些问题总结(二) 关于java.sql.Date中插入/提取数据库中 Date类型的date值–带有时分秒的方法
  • JAVA数据库插入操作

    千次阅读 2009-04-15 13:23:00
    因为数据库的更新,新的数据库是基于原有的数据库,只是新的数据库对部分... 先把原有的数据库数据导出到一个文件中,再用java代码来读取文件中的insert语句,并执行。由于某些表的数据量有点多,就会出现多次 insert
  • import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.io.OutputStreamWriter;i...
  • 本人的环境为Myeclipse10、MySQL5.7.15本文包括:简介JDBC编程步骤打通数据库程序详解—DriverManager程序详解—Connection程序详解—Statement程序详解—ResultSet进阶应用—ResultSet滚动结果集程序详解—释放资源...
  • 把存储在在Excel中的多条User类数据导入到目标数据库 准备阶段: 配置了Mysql数据库 编译器导入了 jxl.jar 包,Excel表格处理包 编译器导入了 sql.jar 包,Mysql包 全部代码如下: JavaBean类(User类): package...
  • 一、背景当前B/S模式已成为应用开发的主流,而在企业办公系统中,...最近在java上做了一个EXCEL的导出功能,写了一个通用类,在这里分享分享,该类支持多sheet,且无需手动进行复杂的类型转换,只需提供三个参数即...
  • 因项目中有需要需根据MySQL表数据生成INSERT INTO语句,在网上找了...修改后能较好的导出INSERT INTO语句。代码如下:package dwz.interaction;import java.io.*;import java.sql.*;import java.util.ArrayList;imp...
  • Java连接数据库oracle中文乱码解决方案今天写了一个java项目连接数据库,之后写了一个执行入库操作的模块。此时暴露出一个问题就是项目的中文插入数据库时会是乱码:项目输出的中文:执行插入操作后数据库中内容:...
  • 导出数据库中的表头

    千次阅读 2018-05-31 22:08:40
    Mysql数据库中,查出表之后,点导出到excle,勾选上表头即可!这个很实用,特别是字段多的表,想插入数据,用此方法,导入excle,可以批量的处理字段,变成你想要的格式!正常写插入sql的时候用的比较多!很实用的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 19,455
精华内容 7,782
关键字:

java导出数据库插入语句

java 订阅