Java tutorial
/* This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ /** * */ package edu.ku.brc.specify.toycode; import java.io.BufferedReader; import java.io.File; import java.io.FileNotFoundException; import java.io.FileReader; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.HashMap; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import edu.ku.brc.dbsupport.DBConnection; import edu.ku.brc.dbsupport.DatabaseDriverInfo; import edu.ku.brc.specify.conversion.BasicSQLUtils; /** * @author rods * * @code_status Alpha * * Created Date: Dec 14, 2009 *HashMap<String, String> mv */ public class RegAdder { protected static final Logger log = Logger.getLogger(RegAdder.class); private static final int STR_SIZE = 128; private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); private Timestamp ts = new Timestamp(Calendar.getInstance().getTime().getTime()); private long startDate; private int cnt = 0; private int lineNo = 0; //private Connection connection; private Statement stmt = null; private PreparedStatement trkStmt1; private PreparedStatement trkStmt2; private PreparedStatement trkStmt3; private PreparedStatement trkStmt4; private PreparedStatement regStmt1; private PreparedStatement regStmt2; /** * */ public RegAdder(final Connection connection) { super(); try { stmt = connection.createStatement(); trkStmt1 = connection .prepareStatement("INSERT INTO track (TimestampCreated, Id, CountAmt) VALUES(?, ?, ?)"); trkStmt2 = connection .prepareStatement("INSERT INTO trackitem (Name, CountAmt, Value, TrackID) VALUES(?, ?, ?, ?)"); trkStmt3 = connection.prepareStatement("UPDATE trackitem SET CountAmt=?, Value=? WHERE TrackItemID=?"); trkStmt4 = connection.prepareStatement("UPDATE track SET CountAmt=? WHERE TrackID=?"); regStmt1 = connection.prepareStatement( "INSERT INTO register (TimestampCreated, RegNumber, RegType, IP) VALUES(?, ?, ?, ?)"); regStmt2 = connection.prepareStatement( "INSERT INTO registeritem (Name, CountAmt, Value, RegisterID) VALUES(?, ?, ?, ?)"); startDate = sdf.parse("2009-04-12 00:00:00").getTime(); } catch (Exception ex) { ex.printStackTrace(); } } /** * @param trackId * @param mv * @param pStmt * @throws SQLException */ private void doTrackInserts(final int trackId, final HashMap<String, String> mv, final PreparedStatement pStmt) throws SQLException { for (String key : mv.keySet()) { String value = mv.get(key); pStmt.setString(1, key); if (!StringUtils.contains(value, ".") && StringUtils.isNumeric(value) && value.length() < 10) { pStmt.setInt(2, Integer.parseInt(value)); pStmt.setNull(3, java.sql.Types.VARCHAR); } else if (value.length() < STR_SIZE + 1) { pStmt.setNull(2, java.sql.Types.INTEGER); pStmt.setString(3, value); } else { String v = value.substring(0, STR_SIZE); System.err.println( "Error - On line " + lineNo + " Value[" + value + "] too big trunccating to[" + v + "]"); pStmt.setNull(2, java.sql.Types.INTEGER); pStmt.setString(3, v); } pStmt.setInt(4, trackId); //System.out.println(pStmt2.toString()); int rv = pStmt.executeUpdate(); if (rv != 1) { for (String k : mv.keySet()) { System.out.println("[" + k + "][" + mv.get(k) + "]"); } System.err.println("------------------------ Line No: " + lineNo); throw new RuntimeException("Error insert trackitem for Id: " + trackId); } } } /** * @param trackItemId * @param value * @param pStmt */ private void doTrackUpdate(final int trackItemId, final String value, final PreparedStatement pStmt) throws SQLException { if (!StringUtils.contains(value, ".") && StringUtils.isNumeric(value) && value.length() < 10) { pStmt.setInt(1, Integer.parseInt(value)); pStmt.setNull(2, java.sql.Types.VARCHAR); } else if (value.length() < STR_SIZE + 1) { pStmt.setNull(1, java.sql.Types.INTEGER); pStmt.setString(2, value); } else { String v = value.substring(0, STR_SIZE); System.err.println( "Error - On line " + lineNo + " Value[" + value + "] too big trunccating to[" + v + "]"); pStmt.setNull(1, java.sql.Types.INTEGER); pStmt.setString(2, v); } pStmt.setInt(3, trackItemId); int rv = pStmt.executeUpdate(); if (rv != 1) { throw new RuntimeException("Error insert trackitem for Id: " + trackItemId); } } /** * @param mv */ private void insertTrack(final HashMap<String, String> mv) { if (mv.size() > 0) { /* +------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+----------------+ | TrackID | int(11) | NO | PRI | NULL | auto_increment | | TimestampCreated | datetime | NO | | NULL | | | Id | varchar(64) | YES | | NULL | | | CountAmt | int(11) | YES | | NULL | | +------------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> describe trackitem; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | TrackItemID | int(11) | NO | PRI | NULL | auto_increment | | Name | varchar(64) | NO | | NULL | | | CountAmt | int(11) | YES | | NULL | | | Value | varchar(64) | YES | | NULL | | | TrackID | int(11) | NO | MUL | NULL | | +-------------+-------------+------+-----+---------+----------------+ */ try { String id = mv.get("id"); String ip = mv.get("IP"); if (StringUtils.isNotEmpty(id) && (ip == null || !ip.startsWith("129.237.201"))) { cnt++; if (cnt % 100 == 0) { System.out.println(cnt); } int recCnt = BasicSQLUtils .getCountAsInt(String.format("SELECT COUNT(*) FROM track WHERE Id = '%s'", id)); if (recCnt == 0) // Insert { Timestamp timeStamp = getTimestamp(mv.get("date")); if (timeStamp.getTime() < startDate) return; trkStmt1.setTimestamp(1, timeStamp); trkStmt1.setString(2, id); trkStmt1.setInt(3, 1); //pStmt.toString(); if (trkStmt1.executeUpdate() == 1) { recCnt++; if (recCnt % 100 == 0) { System.out.println(recCnt); } Integer trkId = BasicSQLUtils.getInsertedId(trkStmt1); doTrackInserts(trkId, mv, trkStmt2); } else { throw new RuntimeException("Error insert track for ID: " + id); } } else // Update { recCnt = BasicSQLUtils .getCountAsInt(String.format("SELECT CountAmt FROM track WHERE Id = '%s'", id)) + 1; Integer trackId = BasicSQLUtils .getCount(String.format("SELECT TrackID FROM track WHERE Id = '%s'", id)); if (trackId != null) { trkStmt4.setInt(1, recCnt); trkStmt4.setInt(2, trackId); if (trkStmt4.executeUpdate() == 1) { for (String key : mv.keySet()) { String sql = String.format( "SELECT TrackItemID FROM trackitem WHERE TrackID = %d AND Name ='%s'", trackId, key); Integer trackItemId = BasicSQLUtils.getCount(sql); if (trackItemId == null) // Insert { doTrackInserts(trackId, mv, trkStmt2); } else // Update { doTrackUpdate(trackItemId, mv.get(key), trkStmt3); } } } else { log.error(trkStmt4.toString()); log.error("Error updating " + id); } } } } } catch (SQLException ex) { for (String k : mv.keySet()) { System.out.println("[" + k + "][" + mv.get(k) + "]"); } System.err.println("------------------------ Line No: " + lineNo); ex.printStackTrace(); } } } /** * @param mv */ private void insertReg(final HashMap<String, String> mv) { if (mv.size() > 0) { /* +------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+---------+----------------+ | RegisterID | int(11) | NO | PRI | NULL | auto_increment | | TimestampCreated | datetime | NO | | NULL | | | RegNumber | varchar(32) | YES | UNI | NULL | | | RegType | varchar(32) | YES | | NULL | | +------------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> describe registeritem; +----------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+----------------+ | RegisterItemID | int(11) | NO | PRI | NULL | auto_increment | | Name | varchar(32) | NO | | NULL | | | CountAmt | int(11) | YES | | NULL | | | Value | varchar(64) | YES | | NULL | | | RegisterID | int(11) | NO | MUL | NULL | | +----------------+-------------+------+-----+---------+----------------+ */ try { String type = mv.get("reg_type"); String num = mv.get("reg_number"); String ip = mv.get("ip"); boolean isNotLocalIP = ip == null || (!ip.startsWith("129.237.201") && !ip.startsWith("24.124")); if (StringUtils.isNotEmpty(type) && StringUtils.isNotEmpty(num) && isNotLocalIP) { int numRegNum = BasicSQLUtils.getCountAsInt( String.format("SELECT COUNT(*) FROM register WHERE RegNumber = '%s'", num)); if (numRegNum > 0) { return; } Timestamp timeStamp = getTimestamp(mv.get("date")); if (timeStamp.getTime() < startDate) return; regStmt1.setTimestamp(1, timeStamp); regStmt1.setString(2, num); regStmt1.setString(3, type); regStmt1.setString(4, ip); //pStmt.toString(); if (regStmt1.executeUpdate() == 1) { cnt++; if (cnt % 100 == 0) { System.out.println(cnt); } Integer regId = BasicSQLUtils.getInsertedId(regStmt1); for (String key : mv.keySet()) { String value = mv.get(key); regStmt2.setString(1, key); if (!StringUtils.contains(value, ".") && StringUtils.isNumeric(value) && value.length() < 10) { regStmt2.setInt(2, value.isEmpty() ? 0 : Integer.parseInt(value)); regStmt2.setNull(3, java.sql.Types.VARCHAR); } else if (value.length() < STR_SIZE + 1) { regStmt2.setNull(2, java.sql.Types.INTEGER); regStmt2.setString(3, value); } else { String v = value.substring(0, STR_SIZE); System.err.println("Error - On line " + lineNo + " Value[" + value + "] too big trunccating to[" + v + "]"); regStmt2.setNull(2, java.sql.Types.INTEGER); regStmt2.setString(3, v); } regStmt2.setInt(4, regId); //System.out.println(pStmt2.toString()); int rv = regStmt2.executeUpdate(); if (rv != 1) { for (String k : mv.keySet()) { System.out.println("[" + k + "][" + mv.get(k) + "]"); } System.err.println("------------------------ Line No: " + lineNo); throw new RuntimeException("Error insert registeritem for Reg Id: " + regId); } } } else { throw new RuntimeException("Error insert register for Reg Type: " + type + " Num: " + num); } } else if (isNotLocalIP) { System.err.println("------------------------ Line No: " + lineNo); System.err.println("Error for Reg Type: [" + type + "] or Num: [" + num + "] is null."); } } catch (SQLException ex) { for (String k : mv.keySet()) { System.out.println("[" + k + "][" + mv.get(k) + "]"); } System.err.println("------------------------ Line No: " + lineNo); ex.printStackTrace(); } } } private Timestamp getTimestamp(final String dateStr) { if (dateStr != null && dateStr.length() == 17) { String[] pair = StringUtils.split(dateStr, " "); String[] dStr = StringUtils.split(dateStr, "/"); String str = "20" + dStr[0] + '-' + dStr[1] + '-' + dStr[2].substring(0, 2) + " " + pair[1]; try { java.util.Date date = sdf.parse(str); return new Timestamp(date.getTime()); } catch (Exception e) { } } return ts; } /** * @param dataFileName * @param dbName * @param doClear */ public void process(final String dataFileName, final String dbName, final boolean doClear) { File file = new File(dataFileName); BufferedReader reader = null; boolean isReg = dbName.startsWith("reg"); if (doClear) { if (isReg) { BasicSQLUtils.deleteAllRecordsFromTable("register", BasicSQLUtils.SERVERTYPE.MySQL); BasicSQLUtils.deleteAllRecordsFromTable("registeritem", BasicSQLUtils.SERVERTYPE.MySQL); } else { BasicSQLUtils.deleteAllRecordsFromTable("track", BasicSQLUtils.SERVERTYPE.MySQL); BasicSQLUtils.deleteAllRecordsFromTable("trackitem", BasicSQLUtils.SERVERTYPE.MySQL); } } try { reader = new BufferedReader(new FileReader(file)); String text = null; HashMap<String, String> mappedValues = new HashMap<String, String>(); // repeat until all lines is read while ((text = reader.readLine()) != null) { if (text.startsWith("-----------")) { if (isReg) { insertReg(mappedValues); } else { insertTrack(mappedValues); } mappedValues.clear(); continue; } if (StringUtils.isNotEmpty(text)) { String[] pair = StringUtils.split(text, "="); if (pair.length == 2) { mappedValues.put(pair[0].trim(), pair[1].trim()); } else if (pair.length == 1) { //mappedValues.put(pair[0], ""); } else { System.err.println("Error pairs " + pair.length + " [" + text + "]"); } } lineNo++; } System.out.println("Cnt: " + cnt); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { if (stmt != null) stmt.close(); if (trkStmt1 != null) trkStmt1.close(); if (trkStmt2 != null) trkStmt2.close(); if (trkStmt3 != null) trkStmt3.close(); if (trkStmt4 != null) trkStmt4.close(); if (regStmt1 != null) regStmt1.close(); if (regStmt2 != null) regStmt2.close(); } catch (SQLException e) { e.printStackTrace(); } try { if (reader != null) { reader.close(); } } catch (IOException e) { e.printStackTrace(); } } } /** * @param args */ public static void main(String[] args) { String dbName = "stats"; String itUsername = "root"; String itPassword = "root"; DBConnection colDBConn = null; Connection connection = null; try { DatabaseDriverInfo driverInfo = DatabaseDriverInfo.getDriver("MySQL"); String connStr = driverInfo.getConnectionStr(DatabaseDriverInfo.ConnectionType.Open, "localhost", dbName, itUsername, itPassword, driverInfo.getName()); System.err.println(connStr); colDBConn = DBConnection.createInstance(driverInfo.getDriverClassName(), driverInfo.getDialectClassName(), dbName, connStr, itUsername, itPassword); connection = colDBConn.createConnection(); BasicSQLUtils.setDBConnection(connection); RegAdder ra = new RegAdder(connection); boolean doReg = false; if (doReg) { ra.process("/Users/rods/reg.dat", "register", true); } else { ra.process("/Users/rods/track.dat", "track", true); } } catch (Exception ex) { ex.printStackTrace(); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }