Java tutorial
/* Copyright (C) 2015, University of Kansas Center for Research * * Specify Software Project, specify@ku.edu, Biodiversity Institute, * 1345 Jayhawk Boulevard, Lawrence, Kansas, 66045, USA * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program 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 General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. */ package edu.ku.brc.specify.toycode.mexconabio; import java.io.File; import java.io.FileReader; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.sql.Time; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Vector; import javax.swing.JFrame; import javax.swing.SwingUtilities; import javax.swing.UIManager; import javax.swing.UnsupportedLookAndFeelException; import org.apache.commons.io.FileUtils; import org.apache.commons.lang.StringUtils; import org.xml.sax.Attributes; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.SAXParseException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler; import com.jgoodies.looks.plastic.Plastic3DLookAndFeel; import com.jgoodies.looks.plastic.PlasticLookAndFeel; import com.jgoodies.looks.plastic.theme.DesertBlue; import edu.ku.brc.dbsupport.DBMSUserMgr; import edu.ku.brc.specify.conversion.BasicSQLUtils; import edu.ku.brc.specify.toycode.mexconabio.FieldDef.DataType; /** * @author rods * * @code_status Alpha * * Feb 3, 2010 * */ public class FileMakerToMySQL extends DefaultHandler { protected SimpleDateFormat sdf = new SimpleDateFormat("d/M/yyyy"); protected SimpleDateFormat stf = new SimpleDateFormat("k:mm:ss"); protected XMLReader xmlReader; protected StringBuilder buffer = new StringBuilder(); protected Vector<FieldDef> fields = new Vector<FieldDef>(); protected Vector<String> values = new Vector<String>(); protected Vector<Integer> sizes = new Vector<Integer>(); protected int rowCnt = 0; protected int rowNum = 0; protected int colNum = 0; protected boolean doColSizes = false; protected boolean debug = false; protected int chkRow = 106000; // Database Memebers protected String dbName = "mex"; protected String userName = "root"; protected String password = "root"; protected String tableName = null; protected String keyField = null; protected boolean doAddKey = false; protected Connection conn = null; protected PreparedStatement pStmt = null; /** * */ public FileMakerToMySQL(final String tableName, final String keyField, final boolean doAddKey) { super(); this.tableName = tableName; this.keyField = keyField; this.doAddKey = doAddKey; try { conn = DriverManager.getConnection( "jdbc:mysql://localhost/" + dbName + "?characterEncoding=UTF-8&autoReconnect=true", userName, password); Statement stmt = conn.createStatement(); stmt.execute("DROP TABLE " + tableName); stmt.close(); } catch (SQLException ex) { ex.printStackTrace(); } } /** * */ public void shutdown() { try { if (pStmt != null) { pStmt.close(); } conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } } /* (non-Javadoc) * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes) */ public void startElement(String namespaceURI, String localName, String qName, Attributes attrs) { buffer.setLength(0); if (localName.equals("FIELD")) { if (doColSizes) { FieldDef fldDef = new FieldDef(); fields.add(fldDef); for (int i = 0; i < attrs.getLength(); i++) { String attr = attrs.getLocalName(i); String value = attrs.getValue(i); if (attr.equals("EMPTYOK")) { fldDef.setNullable(value.equals("YES")); } else if (attr.equals("NAME")) { value = StringUtils.capitalize(value.trim()); value = StringUtils.deleteWhitespace(value); //value = StringUtils.replace(value, "", "n"); value = StringUtils.replace(value, ":", ""); value = StringUtils.replace(value, ";", ""); //value = StringUtils.replace(value, "", "a"); value = StringUtils.replace(value, ".", ""); //value = StringUtils.replace(value, "", "a"); //value = StringUtils.replace(value, "", "a"); value = StringUtils.replace(value, "/", ""); //System.out.println(value); if ((value.charAt(0) >= '0' && value.charAt(0) <= '9') || value.equalsIgnoreCase("New") || value.equalsIgnoreCase("Group")) { value = "Fld" + value; } fldDef.setName(value); } else if (attr.equals("TYPE")) { if (value.equals("TEXT")) { fldDef.setType(DataType.eText); } else if (value.equals("NUMBER")) { fldDef.setType(DataType.eNumber); } else if (value.equals("DATE")) { fldDef.setType(DataType.eDate); } else if (value.equals("TIME")) { fldDef.setType(DataType.eTime); } else { System.err.println("Unknown Type[" + value + "]"); } } //System.out.println(attrs.getLocalName(i)+" = "+attrs.getValue(i)); } } } else if (localName.equals("ROW")) { for (int i = 0; i < attrs.getLength(); i++) { String attr = attrs.getLocalName(i); String value = attrs.getValue(i); if (attr.equals("RECORDID")) { rowNum = Integer.parseInt(value); break; } } } } /* (non-Javadoc) * @see org.xml.sax.helpers.DefaultHandler#resolveEntity(java.lang.String, java.lang.String) */ @Override public InputSource resolveEntity(String publicId, String systemId) throws IOException, SAXException { return super.resolveEntity(publicId, systemId); } // /* (non-Javadoc) * @see org.xml.sax.helpers.DefaultHandler#characters(char[], int, int) */ public void characters(char[] ch, int start, int length) { String[] syms = { "", "", "", "", "", "", "", "", "", "", "", "", "x", "", "" }; String[] chars = { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }; if (buffer != null) { String s = new String(ch, start, length); for (int i = 0; i < syms.length; i++) { s = StringUtils.replace(s, syms[i], chars[i]); } /*int inx = s.indexOf(""); if (inx > -1) { for (int i=0;i<s.length();i++) { System.out.println((byte)s.charAt(i)); } System.out.println(s); inx++; }*/ /*if (s != null && !s.isEmpty() && s.indexOf("mez") > -1) { try { /*System.out.println(Arrays.toString("".getBytes("ISO-8859-1"))); s = "M.Gmez"; for (int i=0;i<s.length();i++) { int val = ((int)s.charAt(i)); System.out.println(s.charAt(i)+" ["+val+"]["+Integer.toHexString(val)+"]["+((byte)s.charAt(i))+"]"); } System.out.print(s+" - "+StringUtils.replace(s, "", "") + " - "); ByteArrayInputStream encXML = new ByteArrayInputStream(s.getBytes()); byte[] bytes = new byte[1024]; int n = encXML.read(bytes, 0, length); s = new String(bytes, 0, n, "UTF-8"); System.out.println(">"+s+"<"); encXML.close(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } System.out.println(s); }*/ buffer.append(s); } //System.out.println(buffer.toString()); } /* (non-Javadoc) * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String) */ public void endElement(final String namespaceURI, final String localName, final String qName) { //System.out.println(localName+" "+qName+" ["+buffer.toString()+"]"); if (localName.equals("DATA")) { //if (buffer.length() > 0) System.out.println(buffer.toString()); if (debug) { //if (rowCnt > 106000) { //System.out.println("col["+colNum+"]"); //if (colNum == 0) System.out.println("BarCd["+buffer.toString()+"]"); } } if (!doColSizes) { values.add(buffer.toString()); } else { String val = buffer.toString(); FieldDef fd = fields.get(colNum); fd.setMaxSize(buffer.length()); if (fd.isNumber() && !fd.isDouble() && StringUtils.contains(buffer.toString(), '.')) { fd.setDouble(true); } } colNum++; } else if (localName.equals("ROW")) { if (!doColSizes) { writeRow(); } else if (debug) { //System.out.println("R: "+rowCnt); //if (rowCnt > chkRow) System.out.println("R: "+rowCnt); } if (rowCnt % 1000 == 0) System.out.println(rowCnt); rowCnt++; colNum = 0; } else if (localName.equals("METADATA")) { if (!doColSizes) { createTable(tableName, keyField, true); if (true) { System.exit(0); } } } else if (localName.equals("RESULTSET")) { if (doColSizes) { for (FieldDef fd : fields) { if (fd.getMaxSize() > 0) System.out.println(fd.getName() + " - " + fd.getMaxSize()); } } } buffer.setLength(0); } /** * @param tblName * @param keyField */ private void createTable(final String tblName, final String keyField, final boolean doForceAll) { /*if (doAddKey) { FieldDef fd = new FieldDef("ID", DataType.eNumber, false, false); fields.insertElementAt(fd, 0); }*/ String[] syms = {"", "", "", "", "", "", "", "", "", "", "", "", "x", "", ""}; String[] chars = {"u", "o", "e", "a", "a", "i", "o", "o", "A", "e", "n", "c", "i", "u", "A"}; //String[] chars = {"", "", "", "", "", "", "", "", "", "", "", "", "", "", ""}; StringBuilder selectDB = new StringBuilder(); for (FieldDef fd : fields) { String s = fd.getName(); System.out.println(s); while (s.indexOf('') > -1) { for (int ii=0;ii<syms.length;ii++) { s = StringUtils.replace(s, syms[ii], chars[ii]); } } if (selectDB.length() > 0) selectDB.append(", "); selectDB.append(s); fd.setName(s); } try { FileUtils.writeStringToFile(new File("select.txt"), selectDB.toString()); } catch (IOException e) { e.printStackTrace(); } int primaryIndex = -1; int i = 0; StringBuilder sb = new StringBuilder(); sb.append("CREATE TABLE "+tblName+" ("); for (FieldDef fd : fields) { if ((fd.getName() == null || fd.getMaxSize() == 0) && !doForceAll) continue; String fieldName = fd.getName(); if ((i == 0 && keyField == null) || (keyField != null && keyField.equals(fieldName))) { primaryIndex = i; if (!fieldName.endsWith("ID")) { fieldName += "ID"; } String keyName = StringUtils.deleteWhitespace(fieldName); sb.append(keyName); sb.append(" int(11) NOT NULL AUTO_INCREMENT,\n"); fd.setName(fieldName); } else { sb.append(fd.getName()); sb.append(" "); switch (fd.getType()) { case eText : int sz = ((int)(fd.getMaxSize() / 8.0) + 2) * 8; System.out.println(fd.getName()+" ["+fd.getMaxSize()+"]["+sz+"]"); if (sz > 255) { //fd.setType(DataType.eMemo); sb.append("text"); } else { sb.append("varchar("+sz+")"); } break; case eNumber : sb.append(fd.isDouble() ? "double" : "int(11)"); break; case eDate : sb.append("date"); break; case eTime : sb.append("TIME"); break; } sb.append(fd.isNullable() ? " DEFAULT NULL," : ","); sb.append("\n"); } i++; } sb.append(String.format("PRIMARY KEY (`%s`)\n", fields.get(primaryIndex).getName())); sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;\n"); System.err.println(sb.toString()); System.setProperty(DBMSUserMgr.factoryName, "edu.ku.brc.dbsupport.MySQLDMBSUserMgr"); DBMSUserMgr mgr = DBMSUserMgr.getInstance(); if (mgr.connect("root", "root", "localhost", "mex")) { if (mgr.doesDBHaveTable(tblName)) { mgr.dropTable(tblName); } mgr.close(); } BasicSQLUtils.update(conn, sb.toString()); createPrepare(tblName, true); } /** * @param tblName */ protected void createPrepare(final String tblName, final boolean doAllFields) { if (doAddKey) { fields.remove(0); } StringBuilder sb = new StringBuilder("INSERT INTO "); sb.append(tblName); sb.append(" ("); int i = 0; for (FieldDef fd : fields) { if (doAllFields || fd.getMaxSize() > 0 || doAllFields) { if (i > 0) sb.append(", "); sb.append(fd.getName()); i++; } } sb.append(") VALUES("); i = 0; for (FieldDef fd : fields) { if (fd.getMaxSize() > 0 || doAllFields) { if (i > 0) sb.append(", "); sb.append('?'); i++; } } sb.append(")"); try { pStmt = conn.prepareStatement(sb.toString()); FileUtils.writeStringToFile(new File("prepare_stmt.txt"), sb.toString()); } catch (SQLException ex) { ex.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * */ private void writeRow() { try { int i = 0; int col = 1; for (FieldDef fd : fields) { if (fd.getMaxSize() > 0) { String val = values.get(i); if (StringUtils.isNotEmpty(val)) { switch (fd.getType()) { case eText: pStmt.setString(col, val); break; case eNumber: { if (StringUtils.isNumericSpace(val)) { if (fd.isDouble()) { pStmt.setDouble(col, Double.parseDouble(val)); } else { pStmt.setInt(col, Integer.parseInt(val)); } } else { pStmt.setObject(col, null); } break; } case eDate: { Date date = null; try { date = sdf.parse(val); pStmt.setDate(col, new java.sql.Date(date.getTime())); } catch (Exception ex) { pStmt.setObject(col, null); } break; } case eTime: { Time time = null; try { time = Time.valueOf(val); } catch (Exception ex) { } pStmt.setTime(col, time); break; } } } else { pStmt.setObject(col, null); } col++; } i++; } } catch (SQLException ex) { ex.printStackTrace(); } try { pStmt.execute(); } catch (SQLException ex) { ex.printStackTrace(); } values.clear(); } /** * @param fileName */ protected void cleanFile(final String fileName) { File srcFile = new File(fileName); try { File destFile = new File("xxx.xml");//File.createTempFile("DB", "xml"); FileUtils.copyFile(srcFile, destFile); //System.out.println("Clean FIle: "+dest) FileReader fr = new FileReader(srcFile); PrintWriter pw = new PrintWriter(destFile); char[] chars = new char[4096 * 8]; int numChars = fr.read(chars, 0, chars.length); while (numChars > 0) { for (int i = 0; i < chars.length; i++) { //if (chars[i] < 32 || chars[i] > 126) if (chars[i] == 0xb) { System.out.println("fixed[" + chars[i] + "]"); chars[i] = ' '; } } pw.write(chars, 0, numChars); numChars = fr.read(chars, 0, chars.length); } fr.close(); pw.close(); FileUtils.copyFile(destFile, srcFile); } catch (IOException ex) { ex.printStackTrace(); } } /** * <p>Handles any non-fatal errors generated by incorrect user input.</p> */ public void error(SAXParseException exception) { System.err.println("line " + exception.getLineNumber() + ": col. " + exception.getColumnNumber() + ": " + exception.getMessage()); } /** * <p>Handles any fatal errors generated by incorrect user input.</p> */ public void fatalError(SAXParseException exception) { System.err.println("line " + exception.getLineNumber() + ": col. " + exception.getColumnNumber() + ": " + exception.getMessage()); } /** * */ public void process(final boolean doColSizes) { rowCnt = 0; this.doColSizes = doColSizes; if (doColSizes && doAddKey) { fields.add(new FieldDef("ID", "ID", DataType.eNumber, false, false)); } try { FileReader fr = new FileReader(new File("/Users/rods/Documents/Conabio.xml")); /*System.out.println("Encoding: "+fr.getEncoding()); System.out.println("Encoding: "+System.getProperty("file.encoding")); String property = System.getProperty("file.encoding"); String actual = new OutputStreamWriter(new ByteArrayOutputStream()).getEncoding(); System.out.println("property=" + property + ", actual=" + actual); */ //FileReader fr = new FileReader(new File("/Users/rods/Documents/mex_conabio.xml")); //FileReader fr = new FileReader(new File("/Users/rods/Documents/mex.xml")); //FileReader fr = new FileReader(new File("xxx.xml")); /*File file = new File("/Users/rods/Documents/mex_conabio.xml"); InputStream inputStream = new FileInputStream(file); Reader reader = new InputStreamReader(inputStream,"UTF-8"); InputSource is = new InputSource(reader); is.setEncoding("UTF-8"); SAXParserFactory factory = SAXParserFactory.newInstance(); SAXParser saxParser = factory.newSAXParser(); saxParser.parse(is, this);*/ xmlReader = (XMLReader) new org.apache.xerces.parsers.SAXParser(); xmlReader.setContentHandler(this); xmlReader.setErrorHandler(this); InputSource is = new InputSource(fr); is.setEncoding("UTF-8"); xmlReader.parse(is); } catch (Exception ex) { ex.printStackTrace(); } } /** * @param args */ public static void main(String[] args) { if (!System.getProperty("os.name").equals("Mac OS X")) { try { UIManager.setLookAndFeel(new Plastic3DLookAndFeel()); } catch (UnsupportedLookAndFeelException e) { e.printStackTrace(); } PlasticLookAndFeel.setPlasticTheme(new DesertBlue()); } SwingUtilities.invokeLater(new Runnable() { @Override public void run() { //FileDialog dlg = new FileDialog((Frame)null, "Choose a FileMaker XML File"); //dlg.setVisible(true); String fileName = "xxx.xml";//dlg.getFile(); if (StringUtils.isNotEmpty(fileName)) { //String path = dlg.getDirectory() + File.separator + fileName; //String name = "AgentsBryo_Vasc"; String tableName = "Conabio"; //String name = "TaxonomicBryo_Vasc"; boolean addKey = true; FileMakerToMySQL fm2mysql = new FileMakerToMySQL(tableName, null, addKey); //fm2mysql.cleanFile("/Users/rods/Documents/"+name+".xml"); fm2mysql.process(true); fm2mysql.process(false); fm2mysql.shutdown(); } else { System.exit(0); } } }); } class FileMakerConvertFrame extends JFrame { public void start() { } /** * @param args */ /*public static void main(String[] args) { //String name = "AgentsBryo_Vasc"; //String name = "MexConabio"; String name = "TaxonomicBryo_Vasc"; boolean addKey = true; FileMakerToMySQL fm2mysql = new FileMakerToMySQL(name, null, addKey); //fm2mysql.cleanFile(name+".xml"); fm2mysql.process(true); fm2mysql.process(false); fm2mysql.shutdown(); if (!System.getProperty("os.name").equals("Mac OS X")) { UIManager.setLookAndFeel(new Plastic3DLookAndFeel()); PlasticLookAndFeel.setPlasticTheme(new DesertBlue()); } }*/ } }