Category Archives: MongoDB

Transferring from MYSQL to Mongo DB

I recently wanted to transfer from MYSQL to MONGODB so I developed some routines to make the transfer. It’s pretty self explanatory but allows you to dump tables and delete data as required.

package mongo;
import java.math.BigDecimal;
import com.mongodb.BasicDBObject;
import com.mongodb.BasicDBObjectBuilder;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.DBObject;
import com.mongodb.MongoClient;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
 *
 * @author Paul Clevett
 * http://www.dailyforextrader.me
 */
public class Mongo {
/**
 * @param args the command line arguments
 */
 public static void main(String[] args) {
 try {
 // To directly connect to a single MongoDB server (note that this will not auto-discover the primary even
// if it's a member of a replica set:
Connection conn;
 try {
 // create a java mysql database connection
 String myDriver = "com.mysql.jdbc.Driver";
 String myUrl = "jdbc:mysql://***SERVERIPADDRESS****/**INSERTDATABASE***";
 Class.forName(myDriver);
 conn = DriverManager.getConnection(myUrl, "***INSERTUSERNAME***", "**INSERTPASSWORD***");
// your prepstatements goes here...
 } catch (Exception e) {
 System.err.println("Got an exception! ");
 System.err.println(e.getMessage());
 return;
 }
MongoClient mongoClient = new MongoClient("***MONGODBIPADDRESS**", 27017);
PrintDatabaseNames(mongoClient);
 DB db = mongoClient.getDB("**MONGODBDATABASE****");
 char[] password = "r00t21".toCharArray();
 boolean auth = db.authenticate("root", password);
deleteRecords("alarms",db);
 deleteRecords("sites",db);
 deleteRecords("users",db);
 deleteRecords("datakeys",db);
 
 
 
 dumpTable("datakeys",conn,db);
 dumpTable("alarms", conn, db);
 dumpTable("sites", conn, db);
 dumpTable("users", conn, db);
} catch (UnknownHostException ex) {
 System.out.println("Unkoown Host " + ex);
 }
 }
private static void deleteRecords(String sTableName,DB db) {
 BasicDBObject query = new BasicDBObject();
 DBCollection col1=db.getCollection(sTableName);
 col1.remove(query);
 }
private static void dumpTable(String TableName, Connection conn, DB db) {
 CreateCollection(TableName, db);
 DBCollection coll = db.getCollection(TableName);
 Statement s = null;
 ResultSet r = null;
 try {
 // get the iVolt units table
 s = conn.createStatement();
r = s.executeQuery("select * from " + TableName);
} catch (Exception e) {
}
 try {
 while (r.next()) {
 Map fields = new HashMap();
for (int i = 1; i < r.getMetaData().getColumnCount() + 1; ++i) {
 String sColumnName = r.getMetaData().getColumnName(i);
if (r.getObject(sColumnName) != null) {
if (r.getObject(sColumnName).getClass() == BigDecimal.class) {
 fields.put(sColumnName, new BigDecimal(r.getObject(sColumnName) + "").doubleValue());
 } else {
 fields.put(sColumnName, r.getObject(sColumnName));
 }
 }
}
InsertRecord(createRecord(r.getMetaData().getColumnName(1), r.getObject(1) + "", fields), coll);
 }
 } catch (Exception e) {
 System.out.println("Fail " + e);
 }
}
private static BasicDBObject createRecord(String tablename, String DBName, Map keyvalues) {
 BasicDBObject result = new BasicDBObject(tablename, DBName);
 Iterator<Map.Entry<String, String>> i = keyvalues.entrySet().iterator();
 while (i.hasNext()) {
 String key = i.next().getKey();
 result.append(key, keyvalues.get(key));
}
return result;
 }
private static void InsertRecord(BasicDBObject doc, DBCollection coll) {
coll.insert(doc);
 }
private static void PrintDatabaseNames(MongoClient mongoClient) {
List dbnames = mongoClient.getDatabaseNames();
 System.out.println("DatabaseNames");
 for (int i = 0; i < dbnames.size(); ++i) {
 System.out.println(dbnames.get(i) + "");
}
}
private static void PrintCollectionNames(DB db) {
 System.out.println("**** Collection Names ****");
 Set<String> colls = db.getCollectionNames();
for (String s : colls) {
 System.out.println(s);
 }
}
private static void CreateCollection(String CollectionName, DB db) {
DBCollection collection;
 if (db.collectionExists(CollectionName)) {
 collection = db.getCollection(CollectionName);
 } else {
 DBObject options = BasicDBObjectBuilder.start().add("capped", false).get();
 collection = db.createCollection(CollectionName, options);
 }
}
}