数据库导入代码:
数据库导出代码:
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WriteException;
public class Export3 {
String dbDriver="org.gjt.mm.mysql.Driver";
String url = "";
String user = "";
String passwordOfDB = ""; //������ݿⲢ��֤���
private static int ExpTable(Connection oConn,String FileName,String []TableName,int maxRowCount)
{
jxl.write.WritableWorkbook wwb=null;
jxl.write.WritableSheet wa=null;
OutputStream outf=null;
try {
File myFilePath=new File(FileName);
if(!myFilePath.exists())
myFilePath.createNewFile();
FileWriter resultFile=new FileWriter(myFilePath);
PrintWriter myFile=new PrintWriter(resultFile);
resultFile.close();
outf = new FileOutputStream(FileName);
wwb = Workbook.createWorkbook(outf);
wa = wwb.createSheet("main", 0);
wa.addCell(new Label(0,0,"tablename"));
wa.addCell(new Label(1,0,"datasheet"));
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
int n=1;
int a=0;
for (int m = 0; m < TableName.length; m++)
{
String sql = "select * from "+TableName[m];
PreparedStatement stmt=null;
ResultSet rs = null;
try
{
stmt=oConn.prepareStatement(sql);
rs=stmt.executeQuery(sql);
int pagecount=0;
jxl.write.WritableSheet ws = null;
int nColmnCount=rs.getMetaData().getColumnCount();
int index = 0;
int headercount=0;
boolean bHasnext=false;
while((bHasnext=rs.next())||headercount==0){
if(ws==null||index==0)
{
pagecount++;
ws=wwb.createSheet(TableName[m]+pagecount, pagecount);
wa.addCell(new Label(0,a+1,TableName[m]));
wa.addCell(new Label(1,a+1,TableName[m]+pagecount));
a++;
for (int k = 0; k < rs.getMetaData().getColumnCount(); k++) {
String ColName=rs.getMetaData().getColumnName(k+1);
String ColType=rs.getMetaData().getColumnTypeName(k+1);
ws.addCell(new Label(k,0,ColName+"/"+ColType));
}
index=0;
headercount++;
}
index++;
for (int k = 0; bHasnext&&k < nColmnCount; k++) {
ws.addCell(new Label(k,index,rs.getString(k+1)));
}
if(index>=maxRowCount)
{
index=0;
}
}
}catch(Exception e){e.printStackTrace();}
finally{
if(rs!=null)
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(stmt!=null)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
try {
wwb.write();
wwb.close();
outf.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return n;
}
public Export3(String dbDriver,String url,String user,String passwordOfDB)
{
this.dbDriver=dbDriver;
this.url=url;
this.user=user;
this.passwordOfDB=passwordOfDB;
try {
Class.forName(dbDriver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public int ExpTable(String FileName,String []TableName,int maxRowCount)
{
Connection oConn=null;
try{
oConn = DriverManager.getConnection(url, user, passwordOfDB);
} catch (Exception ex)
{ ex.printStackTrace();
return -1;
}
int cnt=ExpTable(oConn,FileName,TableName,maxRowCount);
try {
oConn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return cnt;
}
// public static void main(String [] argv)
// {
//
// Export3 aa=new Export3
// ("org.gjt.mm.mysql.Driver",
// "jdbc:mysql://10.4.116.12:3306/radius",
// "root","123456");
// String []TableName={"sys_login_rec_t","department_t"};
// aa.ExpTable("d:\\mmm.xls",TableName,20);
// }
}
数据库导出代码:
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class Import {
String dbDriver="org.gjt.mm.mysql.Driver";
String url = "";
String user = "";
String passwordOfDB = "";
private static int ImpTable(Connection oConn,String FileName,String[] TableName) throws SQLException{
HashMap<String,List<String>> oTabSheets=new HashMap<String,List<String>>();
Statement stmt=null;
PreparedStatement pstmt=null;
try {
InputStream is = new FileInputStream(FileName);
Workbook book=Workbook.getWorkbook(is);
//Workbook book = Workbook.getWorkbook( new File("D:\\kl.xls "));
//获得第一个工作表对象
Sheet sheet = book.getSheet("main");
for(int i=1;i<sheet.getRows();i++)
{
Cell cell=sheet.getCell(0, i);
String sTablename=cell.getContents();
cell=sheet.getCell(1, i);
String sSheet=cell.getContents();
List<String> curSheet=oTabSheets.get(sTablename);
if(curSheet==null)
{
curSheet=new ArrayList<String>();
oTabSheets.put(sTablename, curSheet);
}
curSheet.add(sSheet);
}
for(int i=0;i<TableName.length;i++)
{
List<String> curSheet=oTabSheets.get(TableName[i]);
stmt=oConn.createStatement();
stmt.executeUpdate("truncate table "+TableName[i] );
stmt.close();
stmt=null;
for(int n=0;n<curSheet.size();n++)
{
String sheetname=curSheet.get(n);
Sheet sheett = book.getSheet(sheetname);
//拿到列,行
int column=sheett.getColumns();
int row=sheett.getRows();
//拿到每一行,每列的值
String inssql="insert into "+TableName[i] +" (";
String sqlvalues=" values (";
for(int j=0;j<column;j++){
Cell colName=sheett.getCell(j,0 );
//Cell colType=sheett.getCell(j,1);
//System.out.println();
if(j>0)
inssql+=",";
String sColName=colName.getContents();
int pos=sColName.indexOf('/');
if(pos>=0)
{
sColName=sColName.substring(0,pos);
}
inssql+=sColName;
if(j>0)
sqlvalues+=",";
sqlvalues+="?";
}
inssql=inssql+") "+sqlvalues+")";
pstmt=oConn.prepareStatement(inssql);
System.out.println(inssql);
for(int k=1;k<row;k++){
for(int j=0;j<column;j++){
Cell col=sheett.getCell( j,k);
System.out.println(col.getContents());
String val=col.getContents();
if(val!=null&&val.length()>0)
pstmt.setString(j+1, val);
else
pstmt.setString(j+1,null);
}
pstmt.execute();
}
pstmt.close();
pstmt=null;
}
}
book.close();
} catch (Exception e) {
e.printStackTrace();
}
finally {
if(stmt!=null)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(pstmt!=null)
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return 0;
}
public Import (String dbDriver,String url,String user,String passwordOfDB)
{
this.dbDriver=dbDriver;
this.url=url;
this.user=user;
this.passwordOfDB=passwordOfDB;
try {
Class.forName(dbDriver);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public int ImpTable(String FileName,String[] TableName)
{
Connection oConn=null;
try{
oConn = DriverManager.getConnection(url, user, passwordOfDB);
Statement stmt=oConn.createStatement();
stmt.execute("set names 'utf8'");
stmt.close();
int cnt=ImpTable(oConn,FileName,TableName);
} catch (Exception ex)
{ ex.printStackTrace();
return -1;
}
try {
oConn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return 0;
}
//public static void main(String [] argv)
//{
// Import bb=new Import
// ("org.gjt.mm.mysql.Driver",
// "jdbc:mysql://10.4.116.12:3306/radius",
// "root","123456");
// String []TableName={"sys_login_rec3_t","department3_t"};
// bb.ImpTable("d:\\kl.xls",TableName);
//
//}
}