get_movies

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package bigdatamangementcs1;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author cyl1
 */
public class get_movies {
    public get_movies() {
        try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection conn = DriverManager.getConnection("jdbc:mysql://mysql-server-1.macs.hw.ac.uk/movielens?user=cyl1&password=abccyl1354");
			Statement st = conn.createStatement();
			String query = "SELECT movies.id, movies.title, movies.release_date, "
                                + "movies.video, movies.IMDBURL, ratings.user, ratings.rating, "
                                + "ratings.timestamp  FROM movies INNER JOIN ratings ON movies.id = ratings.movie";
                        ResultSet rs = st.executeQuery(query);

                        String json = "";

                        File file = new File("moviess.json");
                        if (!file.exists()){
                            file.createNewFile();
                        }
                        FileWriter fw = new FileWriter(file.getAbsoluteFile());
                        BufferedWriter bw = new BufferedWriter(fw);

                        while(rs.next()){
                            int id = rs.getInt("id");
                            String title = "\""+rs.getString("title")+"\"";
                            String release_date ="\""+ rs.getString("release_date")+"\"";
                            String video = "\""+rs.getString("video")+"\"";
                            String IMDBURL = "\""+rs.getString("IMDBURL")+"\"";
                            int user = rs.getInt("user");
                            int rating = rs.getInt("rating");
                            String timestamp = "\""+rs.getString("timestamp")+"\"";
                            //System.out.format("%s %s\n", id,genre);
                            json = "{\"id\" :"+id+", "
                                    + "\"title\" :"+title+", "
                                    + "\"release_date\" :"+release_date+", "
                                    + "\"video\" :"+video+", "
                                    + "\"IMDBURL\" :"+IMDBURL+", "
                                    + "\"user\" :"+user+", "
                                    + "\"rating\" :"+rating+", "
                                    + "\"timestamp\" :"+timestamp+"}\n";
                            bw.write(json);

                        }
                        bw.close();
			st.close();
			conn.close();
                        System.out.println("moviess.json saved");
		} catch (Exception e) {
			System.out.println("ERROR!! " +e);
                }

    }
}