package net.eicp.roomally.util.tool;
import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import net.eicp.roomally.util.JdbcManager;
/**
* @author xxf
*/
public class MySqlUtils {
public static final String enter = "\r\n";//windows txt文档的换行
public static void main(String[] args) throws SQLException,
FileNotFoundException, UnsupportedEncodingException, IOException {
MySqlUtils mySqlUtils = new MySqlUtils();
System.out.println(mySqlUtils.exportSqlString(new String[] {
"ally_user", "keywords", "clauses" }));//要导出的表名,可多个
}
public void export(String[] args) throws SQLException,
FileNotFoundException, UnsupportedEncodingException, IOException {
String exportPath = MySqlUtils.class.getResource("/").getPath()//得到classpath路径
+ getDateString("yyyy-MM-dd_hh-mm-ss") + ".sql";
PATH = exportPath;
String sql = exportSqlString(args);
// FileWriter out = new FileWriter(exportPath);
// 如果要写入全角字符,应该选择合适的编码再定义out
FileOutputStream fos = new FileOutputStream(exportPath);
OutputStreamWriter osw = new OutputStreamWriter(fos, "utf-8");
BufferedWriter out = new BufferedWriter(osw);
out.write(sql);//进行文件保存
// out.write(new String(sql.getBytes(),"utf-8"));
out.flush();
out.close();
this.sql = sql;
}
private String sql;
public String getSql() {
return sql;
}
public static String PATH;
public static String getDateString(String format) {
String dateStr = new SimpleDateFormat(format).format(new Date());
return dateStr;
}
public String exportSqlString(String[] tableNames) throws SQLException {
StringBuffer tablesql = new StringBuffer();
for (int i = 0; i < tableNames.length; i++) {
tablesql.append(exportSqlString(tableNames[i]) + enter + enter);
}
return tablesql.toString();
}
public String exportSqlString(String tableName) throws SQLException {
String tablesql = "";
String datasql = "";
Connection con = null;
Statement stat = null;
try {
con = JdbcManager.getConnection();//得到数据库连接
stat = con.createStatement();//创建Statement对象
ResultSet rs = stat.executeQuery("select * from " + tableName);//执行查询语句
tablesql = getCreateTableSql(rs, tableName);//得到创建表的sql语句
datasql = getTableDataSql(rs, tableName);//得到插入数据的sql语句
} catch (SQLException e) {
throw e;
} finally {
JdbcManager.free(stat, con);
}
return tablesql + enter + datasql;
}
public String[] getColumns(ResultSet rs) throws SQLException {//得到字段的名字,存放到一个数组里
ResultSetMetaData rsmd = rs.getMetaData();
int ccount = rsmd.getColumnCount();
String[] args = new String[ccount];
for (int i = 1; i <= ccount; i++) {
String colName = rsmd.getColumnName(i);
args[i - 1] = colName;
}
return args;
}
public String getColumnsString(String[] args) {//拼接所有字段名
StringBuffer buffer = new StringBuffer();
for (int i = 0; i < args.length; i++) {
buffer.append("`" + args[i] + "`,");
}
return buffer.deleteCharAt(buffer.length() - 1).toString();
}
public String getCreateTableSql(ResultSet rs, String tableName)
throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();//主要的通过这个方法
int ccount = rsmd.getColumnCount();
StringBuffer columnBuffer = new StringBuffer("DROP TABLE IF EXISTS `"
+ tableName + "`;" + enter);//为了方便,好多东西是写死的
columnBuffer.append("CREATE TABLE `" + tableName + "` (" + enter);
for (int i = 1; i <= ccount; i++) {
int size = rsmd.getColumnDisplaySize(i);
String colTypeName = rsmd.getColumnTypeName(i);
// String colClassName = rsmd.getColumnClassName(i);
String colName = rsmd.getColumnName(i);
columnBuffer.append("`" + colName + "` ");
columnBuffer.append(colTypeName);//在这儿我只做了一些简单的判断
if (!"double".equalsIgnoreCase(colTypeName)
&& !"date".equalsIgnoreCase(colTypeName)) {
columnBuffer.append("(" + size + ") ");
}
columnBuffer.append(" DEFAULT NULL," + enter);
}
columnBuffer.delete(columnBuffer.length() - 3,
columnBuffer.length() - 2);
columnBuffer.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
return columnBuffer.toString();
}
public String getTableDataSql(ResultSet rs, String tableName)
throws SQLException {
String[] columns = getColumns(rs);
StringBuffer columnBuffer = new StringBuffer();
columnBuffer.append("INSERT INTO `" + tableName + "` ("
+ getColumnsString(columns) + ") VALUES");
while (rs.next()) {
columnBuffer.append("(");
for (int i = 0; i < columns.length; i++) {
Object obj = rs.getObject(columns[i]);
String typeName = "";
if (obj == null) {
obj = "";
}
if (obj.getClass() != null) {
typeName = obj.getClass().getName();
}//在这儿我只做了一些简单的判断
if ("java.lang.String".equals(typeName)
|| "java.sql.Date".equals(typeName)) {
columnBuffer.append("'" + obj + "',");
} else {
columnBuffer.append(obj + ",");
}
}
columnBuffer.deleteCharAt(columnBuffer.length() - 1);
columnBuffer.append("),");
}
if (columnBuffer.toString().endsWith("VALUES"))
return "";
columnBuffer.deleteCharAt(columnBuffer.length() - 1).append(";");
return columnBuffer.toString();
}
}
package net.eicp.roomally.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import net.eicp.roomally.exception.RoomAllyJdbcException;
import net.eicp.roomally.util.tool.CryptUtil;
public class JdbcManager {
private JdbcManager() {
}
private static String url = null;
private static String driver = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream is = JdbcManager.class.getClassLoader()
.getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
url = properties.getProperty("jdbc.url");
driver = properties.getProperty("jdbc.driver");
username = properties.getProperty("jdbc.username");
password = properties.getProperty("jdbc.password");
Class.forName(driver);
} catch (ClassNotFoundException e) {
} catch (IOException e) {
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
}
}
public static void free(Statement st, Connection conn) {
try {
if (st != null)
st.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
}
}
}
在classpath下有jdbc.properties内容如下:
jdbc.url=jdbc:mysql://localhost:3310/roomally?useUnicode=true&characterEncoding=UTF-8
jdbc.driver=com.mysql.jdbc.Driver
jdbc.username=roomally
jdbc.password=039a44b295a8cfb62506b7fe2c23801a