Sql2excel for lazy tool design
Today, I am working with Shandong customers for debugging and remote logon. I have no tools in Linux. I used SSH to log on to the server and directly used MySQL to query the database. All the Chinese characters in the record are garbled characters. Ah, the hateful company, won't let me use windows, or I can use putty or EMS, I ft!
I decided to write a tool myself to extract all the data in the customer database and save it to excel, so that I can see it clearly. Hey hey, okay, let me write a tool.
The first part is who will perform JDBC operations, connect to the database, and extract data sets. Connection con;
Statement state;
/*** // *** Initialize the connection
* @ Param serverip
* @ Param Database
* @ Param Username
* @ Param Password
* @ Throws classnotfoundexception
* @ Throws sqlexception
*/
Public void Init (string serverip, string database, string username, string password) throws classnotfoundexception, sqlexception ...{
Class. forname ("com. MySQL. JDBC. Driver ");
// Configure the data source
String url = "JDBC: mysql: //" + serverip + "/" + database + "? Useunicode = true & characterencoding = gb2312 ";
Con = drivermanager. getconnection (URL, username, password );
}
/*** // ** Get the query result set.
* @ Param SQL
* @ Return
* @ Throws sqlexception
*/
Public resultset getresultset (string SQL) throws sqlexception ...{
State = con. createstatement ();
Resultset res = state.exe cutequery (SQL );
Return res;
}
/***** // Close the connection
* @ Throws sqlexception
*/
Public void close () throws sqlexception ...{
If (con! = NULL)
Con. Close ();
If (State! = NULL)
State. Close ();
}
The second part is to write records in the resultset into an Excel file.
I use jxl for Excel operations. For unfamiliar users, refer to: using Java to Operate Excel files.
/***** // Write the query result to an Excel file
* @ Param rs
* @ Param File
* @ Throws sqlexception
*/
Public void writeexcel (resultset RS, file) throws sqlexception ...{
Writableworkbook WWB = NULL;
Try ...{
// First, use the workbook class factory method to create a writeable workbook object.
WWB = Workbook. createworkbook (File );
} Catch (ioexception e )...{
E. printstacktrace ();
}
If (WWB! = NULL )...{
Writablesheet Ws = WWB. createsheet ("sheet1", 0 );
Int I = 0;
While (Rs. Next ())...{
Label label1 = new label (0, I, Rs. getstring ("ID "));
Label label2 = new label (1, I, Rs. getstring ("category "));
Try ...{
WS. addcell (label1 );
WS. addcell (label2 );
} Catch (rowsexceededexception e )...{
E. printstacktrace ();
} Catch (writeexception e )...{
E. printstacktrace ();
}
I ++;
}
Try ...{
// Write files from memory
WWB. Write ();
// Close the resource and release the memory
WWB. Close ();
} Catch (ioexception e )...{
E. printstacktrace ();
} Catch (writeexception e )...{
E. printstacktrace ();
}
}
}
Test procedure:
Sql2excel Se = new sql2excel ();
Try ...{
Se. INIT ("maid", "mydabase", "root", "1234 ");
Resultset rs = Se. getresultset ("select ID, category from xx ");
Se. writeexcel (RS, new file ("/root/sql2excel.xls "));
Se. Close ();
} Catch (classnotfoundexception e )...{
E. printstacktrace ();
} Catch (sqlexception e )...{
E. printstacktrace ();
}
Haha, it's relatively simple, but it is still very useful. In fact, those database query tools such as EMS and MySQL query browser are similar to this one. After understanding the principle, we can also develop our own query tools, backup Tool.
