Subscribe via RSS Feed

MySql to MongoDB conversion – A Quick Approach

June 3, 2013 0 Comments


In our  work we had a need to convert dataset from MySql to MongoDB. Below is the quick coding for this conversion.

We should use some framework approach to convert data from RDBMS to NoSql. So there are ways of optimisation for the below code. But as we have used JDBC and Java-MongoDB Api here within same class, we thought to share the code.

Two main objectives to show the code are –

1> How should we approach for conversion to MongoDB from a traditional RDBMS Header-Detail table relationship.

2> How can we save nested documents in collection of MongoDB.

So below is the coding with required minimal Documentation.

 
public static void getmySqlData()
	{
	//Connection, Statement and ResultSet declaration for JDBC requirement of MySql Tables.
        Connection con = null;
        Statement st = null; 
        ResultSet rs = null;

        Statement st1 = null;
        ResultSet rs1 = null;

        String url = "jdbc:mysql://localhost:3306/fao"; //Mysql Database URL
        String user = "root"; // DB User Name
        String password = ""; // DB Password

        try {
        	//MongoDb Code
        	MongoClient mongo = new MongoClient("localhost", 27017); //MongoDb Initialisation
        	DB db = mongo.getDB("faodb"); // Selecting the DB (Either Get DB or Create DB)

        	DBCollection seeds = db.getCollection("seeds"); // Collection Creation in MongoDB

        	//MySql Code
                con = DriverManager.getConnection(url, user, password);
                st = con.createStatement();
                rs = st.executeQuery("SELECT a.categoryid, a.name from assetcategory a, assetcategoryproperty b "+ 
            			    " where a.categoryid = b.categoryid and " +
            		            " b.value = 'SeedParent' "); //Selection of dataset via SQL (Header Table)

            while (rs.next()) {

            	BasicDBObject document = new BasicDBObject(); // MongoDB Document Creation
            	document.put("id", rs.getLong("categoryid"));
            	document.put("seedname", rs.getString("name"));

                System.out.println(rs.getLong("categoryid") + " " + rs.getString("name"));
                st1 = con.createStatement();
                rs1 = st1.executeQuery("SELECT * from fao_price where lf_cat_id = "+ rs.getLong("categoryid") +" 
                      and m_year = 2013 and price <> 0.0 order by m_date ");
                // Record Selection for Detail Table Dataset
                int i = 0;
                ArrayList arrBDB = new ArrayList();
                while (rs1.next()) {
                	i++;
                	System.out.println("Price is " + rs1.getFloat("price"));
                	BasicDBObject document1 = new BasicDBObject();
                	document1.put("id", rs1.getLong("lf_cat_id"));
                	document1.put("mdate", rs1.getDate("m_date"));
                	document1.put("mmonth", rs1.getInt("m_month"));
                	document1.put("price", rs1.getFloat("price"));
                	arrBDB.add(document1); // Put the detail table records in Arraylist of MongoDB Document 

                }
                document.put("seedprice", arrBDB); // Insert Nested Documents in MongoDB
                seeds.insert(document);

            }
        } catch (Exception ex) { // Exception Handling
        	ex.printStackTrace();

        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }

            } catch (Exception ex) {
            	ex.printStackTrace();
            }
        }
	}

MySql To MongoDB is the full class.

Comments are welcome.

Enter your email address:

Delivered by FeedBurner

Leave a Reply

You must be logged in to post a comment.