How to take database backup script in java code? Here is the sample java code to take database backup. According to this program, first get the tables list of a database, then export data of each table into separate txt file and save in the folder
//now backuping database..//
try
{
String saveUrl="D://DataBackupWizard//DATABASE//";
String DB = "";
String url = "";
String uName = "";
String pwd = "";
Connection con=null;
Statement stm=null;
ResultSet rs=null;
File f=new File(saveUrl+dNames);
if(!f.exists())//create folder to store all txt file
{
f.mkdir();
}
String full_url="jdbc:mysql://"+url+"/"+dNames+"?autoReconnect=true";
Class.forName(DB);
con = DriverManager.getConnection(full_url,uName,pwd);
stm = con.createStatement();
if(done<tbList.size())
{
String tblName=(String)tbList.get(done);
File ft=new File(saveUrl+dNames+"//"+tblName+".txt");
if(ft.exists())// delete file if already exists
{
ft.delete();
}
String query="SELECT * INTO OUTFILE 'D://DataBackupWizard//DATABASE//"+dNames+"//"+tblName+".txt' FIELDS TERMINATED BY '\t' FROM "+tblName;
System.out.println(query);
stm.executeQuery(query);
}
stm.close();
con.close();
}
catch(Exception Ex)
{
System.out.println(Ex);
}
thanks sir ......
ReplyDeletereally helpful
ReplyDeletedata type for done and tbList varriable
ReplyDeleteHi Birendra,
ReplyDeleteThe solution is execellent. Here is a suggestion from a project created by me and I think It will be useful to you. (If you find it useful. :) )
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 < colCount; i++) { if (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();
}
}
Good. Appreciable.
Deletewhat is sb in that..can you please give me complete code..
ReplyDeleteI also want to develop this kind of application..