1.Read the txt data by java program,and insert into database.It can avoid the character encoding problem at least in mysql
2.Use awk command to seperate the data in txt file and generate the sql batch,then use import funtion to put the data to database
2.Use awk command to seperate the data in txt file and generate the sql batch,then use import funtion to put the data to database
import java.util.*;
import java.io.*;
import paytv.DBConnection;
import java.sql.*;
import mfutil.LoggableStatement;
public class SatelliteSQL
{
private boolean isVerbose;
public SatelliteSQL(boolean isVerbose)
{
this.isVerbose = isVerbose;
} public SatelliteSQL()
{}
/**
* Get the sql statment from the files
*
* @param path the sql file
*
* @return a set of sql statment
*/
private String[] getSQL(String path)
{
java.io.RandomAccessFile raf;
try
{
File f = new File(path);
if( (!f.isFile()) && (!f.exists()))
{
System.out.println("Invalid file:" + path);
return new String[0];
}
raf = new java.io.RandomAccessFile(path,"r");
String s = "";
ArrayList al = new ArrayList();
while((s = raf.readLine()) != null)
{
this.printLog(paytv.paytvutil.isoToBig5(s));
al.add(s);
}
String sqls[] = new String[al.size()];
al.toArray(sqls);
raf.close();
return sqls;
}catch(java.io.FileNotFoundException e)
{
return new String[0];
}
catch(java.io.IOException e)
{
return new String[0];
}
} /**
* Set up the satellite schedule
*
* @param ss SatelliteSchedule
* @param str the data string
*
* @return if setup successfully,return true,otherwise return false.
*/
private boolean setSatelliteSchedule(SatelliteSchedule ss,String str)
{ int no1SpacePos = str.indexOf(" ");
if(no1SpacePos < 0)
{
System.out.println("No first separator");
return false;
}
int no2SpacePos = str.indexOf(" ",no1SpacePos+1);
if(no2SpacePos < 0)
{
System.out.println("No second separator");
return false;
}
String time = str.substring(0,no2SpacePos); int no1ColonPos = str.indexOf(":");
if((no1ColonPos <0) || (no1ColonPos > no2SpacePos))
{
System.out.println("No first colon");
return false;
}
int no2ColonPos = str.indexOf(":",no1ColonPos+1); if((no2ColonPos < 0) || (no2ColonPos > no2SpacePos))
{
time += ":00";
}
if(!mfutil.ValidateUtil.isDate(time))
{
System.out.println("Invalid time:" + time);
return false;
} String name = str.substring(no2SpacePos+1);
ss.setChiProgName(name);
ss.setProgName(name);
ss.setStartTime(time);
return true;
}
private SatelliteSchedule[] getSatelliteSchedule(String path)
{
java.io.RandomAccessFile raf;
try
{
File f = new File(path);
if( (!f.isFile()) && (!f.exists()))
{
System.out.println("Invalid file:" + path);
return new SatelliteSchedule[0];
}
raf = new java.io.RandomAccessFile(path,"r");
String s = "";
ArrayList al = new ArrayList();
while((s = raf.readLine()) != null)
{
this.printLog(paytv.paytvutil.isoToBig5(s));
SatelliteSchedule ss = new SatelliteSchedule();
if(this.setSatelliteSchedule(ss,s))
al.add(ss);
else
System.out.println("Invalid source format:" + paytv.paytvutil.isoToBig5(s));
}
SatelliteSchedule sss[] = new SatelliteSchedule[al.size()];
al.toArray(sss);
raf.close();
System.out.println("Total schedule:" + sss.length);
return sss;
}catch(java.io.FileNotFoundException e)
{
return new SatelliteSchedule[0];
}
catch(java.io.IOException e)
{
return new SatelliteSchedule[0];
}
} /**
* Insert the data to database,by specified table name
*
* @param sss a set of satelliet schedule
* @param table the table in database
*/
private void execData(SatelliteSchedule sss[],String table)
{
DBConnection db = new DBConnection(); if(!db.CmdConnectDB())
{
System.out.println("Can not connect to database");
}
try
{
Connection con = db.Cmdgetcon();
String sql = "insert into " + table + " (start_time,name,description) values(?,?,?)";
java.sql.PreparedStatement pstmt = new mfutil.LoggableStatement(con,sql);
for(int i=0;i<sss.length;i++)
{
pstmt.setString(1,sss[i].getStartTime());
pstmt.setString(2,sss[i].getProgName());
pstmt.setString(3," ");
pstmt.addBatch();
}
int result[] = pstmt.executeBatch();
}
catch(SQLException e)
{
System.out.println(e.getMessage());
}
finally
{
db.CmdCloseDB();
} }
/**
* Execute the sql batch
*
* @param sqls a set of sql statement
*/
private void execSQL(String sqls[])
{
DBConnection db = new DBConnection(); if(!db.CmdConnectDB())
{
System.out.println("Can not connect to database");
}
try{
Connection con = db.Cmdgetcon();
for(int i=0;i<sqls.length;i++)
{
try
{
java.sql.PreparedStatement pstmt = new mfutil.LoggableStatement(con,sqls[i]);
pstmt.executeUpdate();
}
catch(Exception e)
{
System.out.println("Invalid sql:" + sqls[i]);
}
}
System.out.println("Finished total sql:" + sqls.length);
}
finally
{
db.CmdCloseDB();
} }
/**
* Print out the log message
* @param str the message
*/
private void printLog(String str)
{
if(this.isVerbose)
System.out.println(str);
} public static void addSatelliteSchedule(String path)
{
SatelliteSQL ssql = new SatelliteSQL();
ssql.execSQL(ssql.getSQL(path));
} public static void addTVBSchedule(String path,boolean isVerbose)
{
SatelliteSQL ssql = new SatelliteSQL(isVerbose);
ssql.execData(ssql.getSatelliteSchedule(path),"p_tvb");
} public static void addATVSchedule(String path,boolean isVerbose)
{
SatelliteSQL ssql = new SatelliteSQL(isVerbose);
ssql.execData(ssql.getSatelliteSchedule(path),"p_atv");
} public static void help()
{
System.out.println("Invalid argument");
System.out.println("yourcommand sqlfile");
}
public static void main(String argv[])
{
if(argv.length == 0)
{
help();
return;
}
else if( (argv.length == 3) && (argv[2].equalsIgnoreCase("-gentvb"))&& (argv[0].equalsIgnoreCase("-v")))
{
SatelliteSQL.addTVBSchedule(argv[1],true);
}
else if( (argv.length ==2 ) && (argv[1].equalsIgnoreCase("-gentvb")))
{
SatelliteSQL.addTVBSchedule(argv[0],false);
}
else if( (argv.length == 3) && (argv[2].equalsIgnoreCase("-genatv"))&& (argv[0].equalsIgnoreCase("-v")))
{
SatelliteSQL.addATVSchedule(argv[1],true);
}
else if( (argv.length ==2 ) && (argv[1].equalsIgnoreCase("-genatv")))
{
SatelliteSQL.addATVSchedule(argv[0],false);
}
else
help(); }
}
最好有通用的工具包！！