In the environment of the virtual host, because there is no administrator privilege, only the database use permission, the timing backup SQL data is
A more troublesome problem. Solution The idea is as follows: (only for SQL2000) 1. Use the T-SQL backup database to the specified directory, the file name is specified according to the date. 2. Use the RESIN timing function to perform a backup. 3. User timing download backup and delete an expired backup.
Export all data to Excel is also more practical. The method provided below can export all tables in the library, or specify
Several tables export. Note: 1. Because it is exporting all data, it is used with more data. 2. Theoretically apply a variety of JDBC databases, only testing SQL2000 3. For B / S download, it is recommended not to generate files on the server to avoid garbage files. OutputStream is obtained directly from HTTP's Response. 4. Excel operation API please download: http://www.andykhan.com/jexcelapi/
Package steeven; import java.util. *; import java.sql.statement; import java.sql.resultset; import java.sql.types; import java.sql.connection; import java.sql. PreparedStatement; import java.sql.sqlexception; import java.text.dateFormat; import java.io. *;
/ ** *
Title: Database Backup *
Description: Backup Database in Java * @Author steeven * @version 1.0 * / public class dBACKUP {Context CTX; Public DBACKUP Context CTX) {this.ctx = CTX;} / ** * Back up the SQL2000 database to file, the entire database backup * file name will add backup annual month. For example: c: / dbbackup / mydb031109 * @Param file exported file name full path. For example: "c: // dbckup // mydb" * / public void backup (String Dbname, string file) throws sqlexception {string SQL = "backup Database [" DBNAME "] to disk = n '"; sql = file New SimpleDateFormat ("YYMMDD"). format (new date ()); SQL = "'with noinit, nounload, name = n'lvdong', Noskip,
Stats = 10, Noformat "; statement stmt = ctx.getConnection (). CreateStatement (); stmt.executeUpdate (SQL);} / ** * Export the entire database to Excel * @Param conn database connection * @Param Out Output stream * / Public static void db2excel (Conn, OutputStream out) throwsexception {db2excel (conn, out, gettables (conn);} / ** * Export Name to Excel * @Param Conn Database Connection * @Param Out Output stream * @Param Tables table name * @throws exception * / public static void db2excel (Connection CONN, OUTPUTSTREAM OUT, LIST
Tables) Throws Exception {WritableWorkbook WB = JXL.Workbook.createworkBook (out); for (int i = 0; i WritableCell Cell; Switch (Meta.getColumnType (i 1)) {copy type type.bigint: Case Types.Boolean: Case Types.decimal: Case Types.integer: Case Types.Numeric: Case Types.Real: Case Types.Smallint: Double Types.tinyint: Double Val = rs.getdouble (i 1); if (rs.wasnull ()) Cell = New JXL.WRITE.BLANK (i, row); Cell = New JXL.WRITE.NUMBER (I, Row, Val); Break; Case Types.date: Case Types.time: Case Types.TimeStamp: Date Date = r.getdate (i 1); if (Rs.wasnull )) Cell = New jxl.write.blank (i, row); else cell = new jxl.write.datetime (i, row, date); Break; default: Cell = New Label (i, row, rs.getstring (i 1));} // end of switch sheet.addcell (Cell);} // end of for Each Column Row ;} // end Of while (rs.next ())} / ** * Name of all data tables from Connection * / public static list getTables (Connection Conn) throws Exception {ResultSet RS = conn.getMetadata (). GetTables (NULL, NULL, NULL, New String [] {"Table"}); list list = new arraylist (); while (rs.next ()) list.add (rs.getstring (3)); rs.close (); Return List;}
