Generating a database dump SQL Script from Java

The aim of this project is to generate an SQL script from an existing database such that the tables and data can be recreated exactly as it was. There are lots of reasons for having such a tool, for example:

  • We want to transfer the development database from our machine to the client’s server
  • We’re developing on different sites so we need to be able to duplicate database schemas and data
  • We’re developing using one database type (SQLServer, Oracle, MySQL…) and we need to test and/or deploy on another
  • We want to be able to keep snapshots of the database schema and data in a version control system such as CVS in a form where we can easily compare versions

Most database vendors provide a way of executing SQL scripts but often, exporting both the schema and data from a database as SQL can be difficult (SQLServer for example allows you to export the schema, but notโ€”as far as we knowโ€”the data). The other problem though is that each database vendor understands SQL slightly differently which makes making a vendor-independent SQL file beyond this project. Instead, the code presented here will generate an SQL file which you may then have to tweak to get it to run on your database.

Background

Fortunately, Java’s JDBC interface provides most of the functionality that we need to be able to interrogate a database and extract the tables and columns and types. It’s then simply a matter of calling SELECT * FROM <tablename> to extract the data. We can then generate the appropriate CREATE TABLE ... and INSERT INTO ... statements to get our script.

The first thing we need to do is to connect to the database. We’ve used Class.forName(<driverClassName>) and DriverManager.getConnection(<driverURL>, <properties>) to load up the appropriate JDBC driver and get a connection. We can then call connection.getMetaData() to get the database metadata. From here, we can get the tables and then for each table get the columns, the primary keys and then the data. So far we haven’t bothered to get any other constraints, so these would have to be added by hand.

In order to make the code as easy to use as possible, we’ve used a properties file to contain all the configuration information, so the username, password, driverURL, driverClass, schema pattern, tableName pattern, catalog and table name quote character are all read from the properties file. To use the code, you will need to create a properties file where you fill in the values as described below:

# Properties file for controlling db2sql.java

# Driver information (make sure the appropriate classes/jars are on the classpath)
# ==================
#
# These are mandatory, you must provide appropriate values
driver.url=jdbc:mysql://localhost/testdb
driver.class=com.mysql.jdbc.Driver

# Information passed to DriverManger.getConnection
# ================================================
#
# Put any information here that you want to pass to the DriverManager, for example:
user=<username>
password=<password>

# Configuration information
# =========================
#
# Optional information that you can set to control which tables are output etc.
# See 
target="_BLANK">DatabaseMetaData.getTables(...) for information on how to use these.
# Leave these blank to get all tables (note that only normal tables are returned, not
# views, system tables, temporary tables, synonyms or aliases)
# catalog=
# schemaPattern=
# tableName=
#
# You can also specify a quote character that is used to surround column names. This is
# useful if your tables contain any SQL-unfriendly characters such as hyphens. This is
# configurable so that you can set it up for your target database rather than the source
# database. Note however that if you do specify a quote character then the case of the
# table will probably become significant and this may not be what you want. Note also
# that this is not the same as the quote character for data values. For that a single
# quote is used always. Leave this blank for no quote character.
# columnName.quoteChar="

Notes

  1. Once you’ve got the script, you’ll need to be able to execute it against your database. In the end, we wrote a JSP which would upload the script and then execute because we were using JSPs anyway and they had an easy way to get a connection to the current database. However, in order to use statement.execute() we first had to tokenise the SQL file using the semi-colon to spot the individual statements and then we executed each statement
    one after the other. The only problem with this is that any backslashes in the file are interpreted rather than just left as they are, so if you intend to do something like this, you need to first go through and quote the backslashes.
  2. We had a database that had values which included the ampersand sign (&) [there were snippets of HTML in the data]. We were trying to import these into Oracle using SQLPlus. Unfortunately, SQLPlus interprets the ampersand as a variable and assumes that the next line is the value which screws everything up. We found out (from this rather excellent site: Oracle SQL*Plus FAQ) that adding
    SET DEFINE OFF stops SQLPlus from prompting for variable names.
  3. The script works OK for all the basic types, but isn’t going to work for large types like BLOBS, CLOBS, MEMO (in access) LONG VARCHAR and all sorts of unusual column types. Watch out for this and alter the script accordingly.
  4. If you are trying to suck database information out of Microsoft Access, then you’ll get errors saying that it can’t get the primary keys for the table. This is a limitation it seems either with the ODBC manager or with the JDBC-ODBC link. So again, you’ll have to add these by hand afterwards.

Download Source

Download the source.

19 thoughts on “Generating a database dump SQL Script from Java”

  1. When exceptions are thrown, it doesn’t close connections which is bad… Otherwise, it is pretty neat.

  2. hi sir,
    I am getting following exception on console – Unable to dump table customer_information because: java.sql.SQLException: Value ‘0000-00-00’ can not be represented as java.sql.Date. Please help.
    With Regards,
    Abhinav

  3. Is this property file is a java class?

    Does the following format is correct??

    public class Property {

    driver.url=jdbc:mysql://localhost/testdb
    driver.class=com.mysql.jdbc.Driver
    user=
    password=

    }

  4. Hi dinesh,

    Thanks for the prompt reply… And thank you many more times for this amazing, mind blowing program… To me you are a life saver.. I owe you one here.. ๐Ÿ™‚

    And dinesh, i was wondering how to restore the back up the output of this program.. Is there a java way to do that?? If there is please direct me to where to look and on what areas should i focus on buddy, because i really need it…

    Thanx for this amazing program again and again… Keep up the good work chap… ๐Ÿ™‚

  5. Oh sorry.. My bad.. its has to be DenisH.. Not dinesh… Sorry guys.. DenisH, thank you for the program… ๐Ÿ™‚

    PS:
    Sorry the name mix up… ๐Ÿ™

    1. Hi Xolani,

      You do need the properties file (unless you want to hard-code the values in to the Java). You can call the properties file (a simple text file) whatever you like and then pass it as a parameter to the program when you run it.

      1. once i try to execute your code i get this error:
        usage: db2sql
        Exception in thread “main” java.lang.ArrayIndexOutOfBoundsException: 0
        at dbtosql.db2sql.main(db2sql.java:202)

        Please help on what i must do

  6. i made much more superious version of this algorithm.

    Works only for postgres

    generates tables,sequences, functions,indexes, fk, views, schemas, db dump

    code:

    /**
     * Copyright Isocra Ltd 2004
     * You can use, modify and freely distribute this file as long as you credit Isocra Ltd.
     * There is no explicit or implied guarantee of functionality associated with this file, use it at your own risk.
     */
    
    
    import java.io.BufferedWriter;
    import java.io.FileInputStream;
    import java.io.FileWriter;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DatabaseMetaData;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.util.HashSet;
    import java.util.Properties;
    import java.util.Set;
    
    
    /**
     * This class connects to a database and dumps all the tables and contents out to stdout in the form of
     * a set of SQL executable statements
     */
    public class db2sql {
    
        /** Dump the whole database to an SQL string */
        public static Strings dumpDB(Properties props) {
            String driverClassName = props.getProperty("driver.class");
            String driverURL = props.getProperty("driver.url");
            // Default to not having a quote character
            String columnNameQuote = props.getProperty("columnName.quoteChar", "");
            DatabaseMetaData dbMetaData = null;
            Connection dbConn = null;
            try {
                Class.forName(driverClassName);
                dbConn = DriverManager.getConnection(driverURL, props);
                dbMetaData = dbConn.getMetaData();
            }
            catch( Exception e ) {
                System.err.println("Unable to connect to database: "+e);
                return null;
            }
            try {
            	Strings strings = new Strings();
                Set schemas = new HashSet();
                String catalog = props.getProperty("catalog");
                String schema = props.getProperty("schemaPattern");
                String tables = props.getProperty("tableName");
                String[] types = {"VIEW","TABLE"};
                ResultSet rs = dbMetaData.getTables(catalog, schema, tables, types);
                if (! rs.next()) {
                    System.err.println("Unable to find any tables matching: catalog="+catalog+" schema="+schema+" tables="+tables);
                    rs.close();
                } else {
                    // Right, we have some tables, so we can go to work.
                    // the details we have are
                    // TABLE_CAT String => table catalog (may be null)
                    // TABLE_SCHEM String => table schema (may be null)
                    // TABLE_NAME String => table name
                    // TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
                    // REMARKS String => explanatory comment on the table
                    // TYPE_CAT String => the types catalog (may be null)
                    // TYPE_SCHEM String => the types schema (may be null)
                    // TYPE_NAME String => type name (may be null)
                    // SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
                    // REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)
                    // We will ignore the schema and stuff, because people might want to import it somewhere else
                    // We will also ignore any tables that aren't of type TABLE for now.
                    // We use a do-while because we've already caled rs.next to see if there are any rows
                    do {
                        String tableName = rs.getString("TABLE_NAME");
                        String tableType = rs.getString("TABLE_TYPE");
                        String tableSchema = rs.getString("TABLE_SCHEM");
                        String fullTableName = tableSchema+"."+tableName;
                        schemas.add(tableSchema);
                        if ("TABLE".equalsIgnoreCase(tableType)) {
                        	strings.initResult.append("\n\n-- "+fullTableName);
                        	strings.initResult.append("\nCREATE TABLE "+fullTableName+" (\n");
                            ResultSet tableMetaData = dbMetaData.getColumns(null, null, tableName, "%");
                            boolean firstLine = true;
                            while (tableMetaData.next()) {
                                if (firstLine) {
                                    firstLine = false;
                                } else {
                                    // If we're not the first line, then finish the previous line with a comma
                                	strings.initResult.append(",\n");
                                }
                                String columnName = tableMetaData.getString("COLUMN_NAME");
                                String columnType = tableMetaData.getString("TYPE_NAME");
                                // WARNING: this may give daft answers for some types on some databases (eg JDBC-ODBC link)
                                String decimalDigits = tableMetaData.getString("DECIMAL_DIGITS");
                                String columnSize = tableMetaData.getString("COLUMN_SIZE");
                                String columnPrecision;
                                
                                if(columnType.equals("varchar") ){
                                	columnPrecision = " ("+columnSize+")";
                                }else if(columnType.equals("numeric") || columnType.equals("decimal")){
                                	if(Integer.parseInt(columnSize) >1000)
                                		columnSize="1000";
                                	columnPrecision=" ("+columnSize+", "+decimalDigits+" )";
                                	
                                }else
                                	columnPrecision = "";
                                
                                
                                String nullable = tableMetaData.getString("IS_NULLABLE");
                                String defaultValue = tableMetaData.getString("COLUMN_DEF");
                                if(defaultValue == null)
                                	defaultValue ="";
                                else
                                	defaultValue =" DEFAULT "+defaultValue;
                                	
                                if(columnType.equalsIgnoreCase("SERIAL")){
                                	defaultValue="";
                                	columnSize="";
                                }
                                String nullString = "NULL";
                                if ("NO".equalsIgnoreCase(nullable)) {
                                    nullString = "NOT NULL";
                                }
                                strings.initResult.append("    "+columnNameQuote+columnName+columnNameQuote+" "+columnType+columnPrecision+""+" "+nullString+ defaultValue);
                            }
                            tableMetaData.close();
    
                            // Now we need to put the primary key constraint
                            Set pkNames = new HashSet();
                            try {
                                ResultSet primaryKeys = dbMetaData.getPrimaryKeys(catalog, schema, tableName);
                                // What we might get:
                                // TABLE_CAT String => table catalog (may be null)
                                // TABLE_SCHEM String => table schema (may be null)
                                // TABLE_NAME String => table name
                                // COLUMN_NAME String => column name
                                // KEY_SEQ short => sequence number within primary key
                                // PK_NAME String => primary key name (may be null)
                                String primaryKeyName = null;
                                StringBuffer primaryKeyColumns = new StringBuffer();
                                while (primaryKeys.next()) {
                                    String thisKeyName = primaryKeys.getString("PK_NAME");
                                    pkNames.add(thisKeyName);
                                    if ((thisKeyName != null && primaryKeyName == null)
                                            || (thisKeyName == null && primaryKeyName != null)
                                            || (thisKeyName != null && ! thisKeyName.equals(primaryKeyName))
                                            || (primaryKeyName != null && ! primaryKeyName.equals(thisKeyName))) {
                                        // the keynames aren't the same, so output all that we have so far (if anything)
                                        // and start a new primary key entry
                                        if (primaryKeyColumns.length() > 0) {
                                            // There's something to output
                                        	strings.initResult.append(",\n CONSTRAINT "+primaryKeyName+" PRIMARY KEY ");
                                        	
                                        	strings.initResult.append("("+primaryKeyColumns.toString()+")");
                                        }
                                        // Start again with the new name
                                        primaryKeyColumns = new StringBuffer();
                                        primaryKeyName = thisKeyName;
                                    }
                                    // Now append the column
                                    if (primaryKeyColumns.length() > 0) {
                                        primaryKeyColumns.append(", ");
                                    }
                                    primaryKeyColumns.append(primaryKeys.getString("COLUMN_NAME"));
                                }
                                if (primaryKeyColumns.length() > 0) {
                                    // There's something to output
                                	strings.initResult.append(",\n    CONSTRAINT "+primaryKeyName+" PRIMARY KEY ");
                                	strings.initResult.append(" ("+primaryKeyColumns.toString()+")");
                                }
                            } catch (SQLException e) {
                                // NB you will get this exception with the JDBC-ODBC link because it says
                                // [Microsoft][ODBC Driver Manager] Driver does not support this function
                                System.err.println("Unable to get primary keys for table "+tableName+" because "+e);
                            }
                            strings.initResult.append("\n);\n");
                            //and FK
                            try{
                            	ResultSet exportedKeys = dbMetaData.getExportedKeys(catalog, tableSchema, tableName);
                           
    //                        	PKTABLE_CAT String => primary key table catalog (may be null) 
    //                        	PKTABLE_SCHEM String => primary key table schema (may be null) 
    //                        	PKTABLE_NAME String => primary key table name 
    //                        	PKCOLUMN_NAME String => primary key column name 
    //                        	FKTABLE_CAT String => foreign key table catalog (may be null) being exported (may be null) 
    //                        	FKTABLE_SCHEM String => foreign key table schema (may be null) being exported (may be null) 
    //                        	FKTABLE_NAME String => foreign key table name being exported 
    //                        	FKCOLUMN_NAME String => foreign key column name being exported 
    //                        	KEY_SEQ short => sequence number within foreign key( a value of 1 represents the first column of the foreign key, a value of 2 would represent the second column within the foreign key). 
    //                        	UPDATE_RULE short => What happens to foreign key when primary is updated: 
    //                        	importedNoAction - do not allow update of primary key if it has been imported 
    //                        	importedKeyCascade - change imported key to agree with primary key update 
    //                        	importedKeySetNull - change imported key to NULL if its primary key has been updated 
    //                        	importedKeySetDefault - change imported key to default values if its primary key has been updated 
    //                        	importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility) 
    //                        	DELETE_RULE short => What happens to the foreign key when primary is deleted. 
    //                        	importedKeyNoAction - do not allow delete of primary key if it has been imported 
    //                        	importedKeyCascade - delete rows that import a deleted key 
    //                        	importedKeySetNull - change imported key to NULL if its primary key has been deleted 
    //                        	importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility) 
    //                        	importedKeySetDefault - change imported key to default if its primary key has been deleted 
    //                        	FK_NAME String => foreign key name (may be null) 
    //                        	PK_NAME String => primary key name (may be null) 
    //                        	DEFERRABILITY short => can the evaluation of foreign key constraints be deferred until commit 
    //                        	importedKeyInitiallyDeferred - see SQL92 for definition 
    //                        	importedKeyInitiallyImmediate - see SQL92 for definition 
    //                        	importedKeyNotDeferrable - see SQL92 for definition 
    
                            while(exportedKeys.next()){
                            	String fkName = exportedKeys.getString("FK_NAME");
                            	String fKColumnName = exportedKeys.getString("FKCOLUMN_NAME");
                            	String pKColumnName = exportedKeys.getString("PKCOLUMN_NAME");
                            	String fKTableFullName = exportedKeys.getString("FKTABLE_SCHEM")+"."+exportedKeys.getString("FKTABLE_NAME");
                            	
                            	strings.fkREsult.append("\n--fk for "+fKTableFullName+"\n");
                            	strings.fkREsult.append("\nALTER TABLE "+fKTableFullName+"\n");
                            	strings.fkREsult.append(" ADD CONSTRAINT "+fkName + " FOREIGN KEY ("+ fKColumnName+  ")\n");
                            	strings.fkREsult.append(" REFERENCES "+fullTableName +"("+ pKColumnName+  ") \n");
                            	strings.fkREsult.append("     ON UPDATE NO ACTION\n");
                            	strings.fkREsult.append("     ON DELETE RESTRICT;\n");
                            }
                            exportedKeys.close();
                            }catch(Exception e){
                            	System.out.println(e);
                            }
                          //and INDEX
                            try{
    	                        ResultSet indexInfo = dbMetaData.getIndexInfo(catalog, tableSchema, tableName, false, false);
    //	                        TABLE_CAT String => table catalog (may be null) 
    //	                        TABLE_SCHEM String => table schema (may be null) 
    //	                        TABLE_NAME String => table name 
    //	                        NON_UNIQUE boolean => Can index values be non-unique. false when TYPE is tableIndexStatistic 
    //	                        INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic 
    //	                        INDEX_NAME String => index name; null when TYPE is tableIndexStatistic 
    //	                        TYPE short => index type: 
    //	                        tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions 
    //	                        tableIndexClustered - this is a clustered index 
    //	                        tableIndexHashed - this is a hashed index 
    //	                        tableIndexOther - this is some other style of index 
    //	                        ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic 
    //	                        COLUMN_NAME String => column name; null when TYPE is tableIndexStatistic 
    //	                        ASC_OR_DESC String => column sort sequence, "A" => ascending, "D" => descending, may be null if sort sequence is not supported; null when TYPE is tableIndexStatistic 
    //	                        CARDINALITY int => When TYPE is tableIndexStatistic, then this is the number of rows in the table; otherwise, it is the number of unique values in the index. 
    //	                        PAGES int => When TYPE is tableIndexStatisic then this is the number of pages used for the table, otherwise it is the number of pages used for the current index. 
    //	                        FILTER_CONDITION String => Filter condition, if any. (may be null) 
    	                        
    	                        //TODO if index unique and if something else than btree
    	                        String indexComment = "\n--index for  "+fullTableName+"\n";
    	                        boolean hasIndex = false;
    	                        String indexPreviousName = null;
    	                        String columnNames="";
    	                        StringBuilder tempIndexResult = new StringBuilder();
    	                        
    	                        while(indexInfo.next()){
    	                        	String indexName = indexInfo.getString("INDEX_NAME");
    	                        	if(!pkNames.contains(indexName)){
    	                        		if(hasIndex == false){
    	                        			tempIndexResult.append(indexComment);
    	                        			hasIndex = true;
    	                        		}
    	                        		String columnName = indexInfo.getString("COLUMN_NAME");
    	                        		
    	                        		if(indexName.equals(indexPreviousName)){
    	                        			tempIndexResult = new StringBuilder();
    	                        			columnNames = columnNames+", "+ columnName;
    	                        			tempIndexResult.append(" CREATE INDEX "+indexName);
    	                        			tempIndexResult.append("  ON "+fullTableName);
    	                        			tempIndexResult.append("  USING btree ("+columnNames+") ; \n");
    	                        			
    	                        		}else{
    	                        			strings.indexResult.append(""+tempIndexResult);
    	                        			
    	                        			tempIndexResult = new StringBuilder();
    			                        	columnNames = columnName;
    			                        	tempIndexResult.append(" CREATE INDEX "+indexName);
    			                        	tempIndexResult.append("  ON "+fullTableName);
    			                        	tempIndexResult.append("  USING btree ("+columnName+") ; \n");
    	                        		}
    	                        		indexPreviousName = indexName;
    	                        	}
    	                        	
    	                        }
    	                        strings.indexResult.append(""+tempIndexResult);
    	                        
    	                        indexInfo.close();
                            }catch(Exception e){
                            	System.out.println(e);
                            }
                            
                            try{
                            	//TODO doesnt work well because of unknown grants
    	                        ResultSet grants = dbMetaData.getTablePrivileges(catalog, null, tableName);
    
    //	                        TABLE_CAT String => table catalog (may be null) 
    //	                        TABLE_SCHEM String => table schema (may be null) 
    //	                        TABLE_NAME String => table name 
    //	                        GRANTOR String => grantor of access (may be null) 
    //	                        GRANTEE String => grantee of access 
    //	                        PRIVILEGE String => name of access (SELECT, INSERT, UPDATE, REFRENCES, ...) 
    //	                        IS_GRANTABLE String => "YES" if grantee is permitted to grant to others; "NO" if not; null if unknown 
    
    	                        strings.grantResult.append("\n-- grants on "+fullTableName+"\n");
    	                        while(grants.next()){
    	                        	
    	                        	String privileges = grants.getString("PRIVILEGE");
    
    	                        	String grantee = grants.getString("GRANTEE");
    	                        	
    	                        	strings.grantResult.append(" GRANT "+privileges+ " ON "+fullTableName+ " TO "+grantee+ ";\n");
    	                        	
    	                        }
    	                        grants.close();
                            }catch(Exception e){
                            	System.out.println(e);
                            }
                            
                            // Right, we have a table, so we can go and dump it
                            dumpTable(dbConn, strings.insertResult, fullTableName);
                            
                        }
                        else if("VIEW".equalsIgnoreCase(tableType)){
    
                        	strings.viewResult.append("\nCREATE VIEW "+fullTableName+" \n(\n");
                        	strings.viewResult.append("");
                        	boolean firstLine = true;
                        	ResultSet tableMetaData = dbMetaData.getColumns(null, null, tableName, "%");
                        	while(tableMetaData.next()){
                                if (firstLine) {
                                    firstLine = false;
                                } else {
                                    // If we're not the first line, then finish the previous line with a comma
                                	strings.viewResult.append(",\n");
                                }
                                String columnName = tableMetaData.getString("COLUMN_NAME");
                                strings.viewResult.append("   "+columnName);
                        	}
                        	
                        	strings.viewResult.append("\n)\nAS\n ");
                        	
                        	ResultSet executeQuery = dbConn.prepareCall(
                        			" SELECT * FROM pg_views " +
                        			" WHERE viewname = '"+tableName+"'")
                        			.executeQuery();
                        	executeQuery.next();
                        	strings.viewResult.append(executeQuery.getString("definition"));
                        	
                        }
                        
                    } while (rs.next());
                    rs.close();
                }
                //functions
                try{
                	//TODO if more than 3 arguments on function ... it fails.
                String functionDataString =
                " SELECT r.* ,"+
    			" (CASE WHEN pronargs = 0 THEN '' "+
    			"       WHEN pronargs = 1 THEN (SELECT typname FROM pg_type WHERE oid = proargtypes [0]) "+
    			"       WHEN pronargs = 2 THEN (SELECT typname FROM pg_type WHERE oid = proargtypes [0]) || ', ' ||(SELECT typname FROM pg_type WHERE oid = proargtypes [1]) "+
    			"       WHEN pronargs = 3 THEN (SELECT typname FROM pg_type WHERE oid = proargtypes [0]) || ', ' ||(SELECT typname FROM pg_type WHERE oid = proargtypes [1]) || ', ' ||(SELECT typname FROM pg_type WHERE oid = proargtypes [2])"+ 
    			"       ELSE 'exception' "+
    			" 			  END) as args"+
    			" FROM"+
    			" (SELECT r.routine_schema sname, "+
    			"    r.routine_name AS fname,"+
    			"    r.security_type,"+
    			"    r.routine_definition as data,"+
    			"    r.external_language AS language,"+
    			"    r.data_type AS return_type"+
    			" FROM information_schema.routines r"+
    			" WHERE r.specific_schema  'information_schema' AND r.specific_schema 'pg_catalog'"+
    			" ) as r, pg_proc p"+
    			" WHERE  p.proname = r.fname";
                ResultSet functionColumns = dbConn.prepareCall(functionDataString)
            			.executeQuery();
                
                
                while(functionColumns.next()){
                	String funSchema = functionColumns.getString("sname");
                	String funName = functionColumns.getString("fname");
                	String args = functionColumns.getString("args");
                	String returnType = functionColumns.getString("return_type");
                	String security = functionColumns.getString("security_type");
                	String pllanguage = functionColumns.getString("language");
                	String fData = functionColumns.getString("data").replaceAll("'", "''");
                	
                	strings.functionResult.append("CREATE OR REPLACE FUNCTION "+funSchema+"."+funName+"("+args+") RETURNS "+returnType+"\n SECURITY "+security+" " +
                			" LANGUAGE "+pllanguage+ " AS '"+fData+" ' ;\n");
                	
                }
                }catch(Exception e){
                	System.err.println(e);
                }
                
                //sequences
                try{
                	
    
                ResultSet sequences = dbConn.prepareCall("select sequence_name,sequence_schema from information_schema.sequences s ")
            			.executeQuery();
                
                
                while(sequences.next()){
                	String sSchema = sequences.getString("sequence_schema");
                	String sName = sequences.getString("sequence_name");
                	
                	ResultSet query = dbConn.prepareCall("select start_value, increment_by from "+sSchema+"."+sName).executeQuery();
                	query.next();
                	String startValue = query.getString("start_value");
                	String incrementBy = query.getString("increment_by");
                	
                	
                	strings.sequenceResult.append("\nCREATE SEQUENCE "+sSchema+"."+sName+" \n");
                	strings.sequenceResult.append("START WITH "+startValue+" INCREMENT by "+incrementBy+"; \n");
                	
                }
                }catch(Exception e){
                	System.err.println(e);
                }
                
                dbConn.close();
                
                for(String s :schemas){
                	strings.schemaResult.append("CREATE SCHEMA "+s+";\n");
                }
    
                return strings;
            } catch (SQLException e) {
                e.printStackTrace();  //To change body of catch statement use Options | File Templates.
            }
            return null;
        }
    
    	/** dump this particular table to the string buffer */
        private static void dumpTable(Connection dbConn, StringBuilder result, String tableName) {
            try {
                // First we output the create table stuff
                PreparedStatement stmt = dbConn.prepareStatement("SELECT * FROM "+tableName);
                ResultSet rs = stmt.executeQuery();
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
    
                // Now we can output the actual data
                result.append("\n\n-- Data for "+tableName+"\n");
                while (rs.next()) {
                    result.append("INSERT INTO "+tableName+" VALUES (");
                    for (int i=0; i 0) {
                            result.append(", ");
                        }
                        Object value = rs.getObject(i+1);
                        if (value == null) {
                            result.append("NULL");
                        } else {
                            String outputValue = value.toString();
                            outputValue = outputValue.replaceAll("'","\\'");
                            result.append("'"+outputValue+"'");
                        }
                    }
                    result.append(");\n");
                }
                rs.close();
                stmt.close();
            } catch (SQLException e) {
                System.err.println("Unable to dump table "+tableName+" because: "+e);
            }
        }
    
        /** Main method takes arguments for connection to JDBC etc. */
        public static void main(String[] args) {
    
            if (args.length != 1) {
               System.err.println("usage: db2sql ");
            }
            // Right so there's one argument, we assume it's a property file
            // so lets open it
            Properties props = new Properties();
            try {
                props.load(new FileInputStream(args[0]));
    
                Strings strings = dumpDB(props);
                
                try{
    			
    			System.out.println(strings.schemaResult.toString());
    			System.out.println(strings.initResult.toString());
    			System.out.println(strings.functionResult.toString());
    			System.out.println(strings.viewResult.toString());
    			System.out.println(strings.insertResult.toString());
    			System.out.println(strings.indexResult.toString());
    			System.out.println(strings.fkREsult.toString());
    			System.out.println(strings.sequenceResult.toString());
    			/*
                	  
                	  FileWriter fstream = new FileWriter("dump_database.txt");
                	  BufferedWriter out = new BufferedWriter(fstream);
                	  out.write(strings.schemaResult.toString());
                	  out.write(strings.initResult.toString());
                	  out.write(strings.functionResult.toString());
                	  out.write(strings.viewResult.toString());
                	  out.write(strings.insertResult.toString());
                	  out.write(strings.indexResult.toString());
                	  out.write(strings.fkREsult.toString());
                	  out.write(strings.sequenceResult.toString());
                	  //Close the output stream
                	  out.close();
                	  }catch (Exception e){//Catch exception if any
                	  System.err.println("Error: " + e.getMessage());
                	  }
                */
                
            } catch (IOException e) {
                System.err.println("Unable to open property file: "+args[0]+" exception: "+e);
            }
    
        }
    }
    class Strings{
    	StringBuilder schemaResult = new StringBuilder();
    	StringBuilder initResult = new StringBuilder();
    	StringBuilder functionResult = new StringBuilder();
    	StringBuilder viewResult = new StringBuilder();
    	StringBuilder fkREsult = new StringBuilder();
    	StringBuilder indexResult = new StringBuilder();
    	StringBuilder insertResult = new StringBuilder();
    	StringBuilder grantResult = new StringBuilder();
    	StringBuilder sequenceResult = new StringBuilder();
    	
    }
    
  7. Hi DenisH:
    I just want to say thank you very much for this code, I really appreciate this release, I implemented a few other things for approval between different DBMS … many many thank’s.

  8. I created a code in scratch to dump a sql file like the ones you see when done with phpmyadmin , Navicat and such similar software. here it is.

    I have used SHOW CREATE, SHOW TABLES and such kind of simple SQL statements to do the work.

    Link to code

    This is the code. I like to improve myself and any comment is appreciated.


    /*
    * To change this template, choose Tools | Templates
    * and open the template in the editor.
    */
    package isuru.jdbcaup;

    import java.io.BufferedWriter;
    import java.io.FileWriter;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;

    /**
    *
    * @author Isuru Ranawaka - isuru@diyatha.com
    */
    public class JDBCup {

    private static Connection con;

    public static Connection getConnection() throws Exception {
    if (con == null || con.isClosed()) {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "root");
    }
    return con;
    }

    public static synchronized ResultSet query(String sql) throws Exception {
    return getConnection().createStatement().executeQuery(sql);
    }

    public static void dumpDatabase(String Filename) {
    try {
    FileWriter fw = new FileWriter(Filename);
    BufferedWriter buff = new BufferedWriter(fw);

    StringBuilder sb = new StringBuilder();
    sb.append("SET FOREIGN_KEY_CHECKS=0;");
    sb.append("\n");

    ResultSet rs = query("SHOW FULL TABLES WHERE Table_type != 'VIEW'");
    while (rs.next()) {
    String tbl = rs.getString(1);

    sb.append("\n");
    sb.append("-- ----------------------------\n")
    .append("-- Table structure for `").append(tbl)
    .append("`\n-- ----------------------------\n");
    sb.append("DROP TABLE IF EXISTS `").append(tbl).append("`;\n");
    ResultSet rs2 = query("SHOW CREATE TABLE `" + tbl + "`");
    rs2.next();
    String crt = rs2.getString(2) + ";";
    sb.append(crt).append("\n");
    sb.append("\n");
    sb.append("-- ----------------------------\n").append("-- Records for `").append(tbl).append("`\n-- ----------------------------\n");

    ResultSet rss = query("SELECT * FROM " + tbl);
    while (rss.next()) {
    int colCount = rss.getMetaData().getColumnCount();
    if (colCount > 0) {
    sb.append("INSERT INTO ").append(tbl).append(" VALUES(");

    for (int i = 0; i 0) {
    sb.append(",");
    }
    String s = "";
    try {
    s += "'";
    s += rss.getObject(i + 1).toString();
    s += "'";
    } catch (Exception e) {
    s = "NULL";
    }
    sb.append(s);
    }
    sb.append(");\n");
    buff.append(sb.toString());
    sb = new StringBuilder();
    }
    }
    }

    ResultSet rs2 = query("SHOW FULL TABLES WHERE Table_type = 'VIEW'");
    while (rs2.next()) {
    String tbl = rs2.getString(1);

    sb.append("\n");
    sb.append("-- ----------------------------\n")
    .append("-- View structure for `").append(tbl)
    .append("`\n-- ----------------------------\n");
    sb.append("DROP VIEW IF EXISTS `").append(tbl).append("`;\n");
    ResultSet rs3 = query("SHOW CREATE VIEW `" + tbl + "`");
    rs3.next();
    String crt = rs3.getString(2) + ";";
    sb.append(crt).append("\n");
    }

    buff.flush();
    buff.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }

    public static void main(String[] args) {
    dumpDatabase("dump_file.sql");
    }
    }

Leave a Reply

Your email address will not be published. Required fields are marked *