1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
|
package librairie;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.lang.StringEscapeUtils;
/**
* Exporte les données de la table au format sql<br />
*
* @param stmt 1er Statement
* @param stmt 2ème Statement
* @param tbl nom de la table
* @param critere clause where avec le mot where
* @author
*
*/
public class FormatageSQL {
public static String main( final Statement stmt, final Statement stmt1, final String tbl, final String critere) {
//Export sous format sql
String sql = "";
String val_chp = "";
StringBuffer text = new StringBuffer();
StringBuffer text1 = new StringBuffer();
StringBuffer val = new StringBuffer();
ResultSet rs = null;
ResultSet rs1 = null;
sql = "SELECT "+tbl+".* FROM "+tbl+" "+critere;
try{rs1 = stmt1.executeQuery("DESCRIBE "+tbl);
int y = 0;
rs = stmt.executeQuery(sql);
text1 = new StringBuffer();
text1.append("INSERT INTO "+tbl+" (");
while(rs.next())
{text = new StringBuffer();
while(rs1.next())
{//on saute les chp auto_incrément
if(rs1.getObject(6).equals(""))
{if(y==0){text1.append(rs1.getString(1)+", ");}
try{val_chp = ""+rs.getObject(rs1.getString(1));}catch(final SQLException e){val_chp ="null";}
//échappement
val_chp = StringEscapeUtils.escapeSql(val_chp);
//traitement de la valeur nulle
//val par défaut = null
if(rs1.getObject(5)==null && val_chp.equals("null"))
{val_chp = "NULL ";}
else
//val par défaut <> null
{if(rs1.getObject(5)!=null && val_chp.equals("null"))
{val_chp = ""+rs1.getObject(5);}
else
{val_chp = "'"+val_chp+"' ";}}
//ajout valeur à la rqte
text.append(val_chp+", ");
}
}
rs1.beforeFirst();
y++;
val.append(text1.substring(0,text1.length()-2)+" ) VALUES ( "+text.substring(0,text.length()-3)+" );\n");
}
rs.close();
rs1.close();
}catch(final SQLException e){val.append("--Erreur d'export\n");}
finally{try{if(rs!=null)rs.close();
if(rs1!=null)rs1.close();
}catch(final SQLException e){}}
if(val.equals("")){val.append("--Pas de données\n");}
return(val.toString());
}
} |
Partager