Java tutorial
/* * 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. */ import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.json.JSONArray; import org.json.JSONObject; /** * * @author Sweta */ @WebServlet(urlPatterns = { "/SyncMusicServlet" }) public class SyncMusicServlet extends HttpServlet { private String JDBC_DRIVER = "com.mysql.jdbc.Driver"; private String DB_URL = "jdbc:mysql://localhost:3306/AndroidFamilyMusicBrowserDB"; // Database credentials private String USER = "root"; private String PASS = ""; /** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); PrintWriter out = response.getWriter(); try { String android_id = request.getParameter("androidId"); JSONObject json = new JSONObject(request.getParameter("json_data")); // Process Albums JSONArray albumJSON = json.getJSONArray("albums"); for (int i = 0; i < albumJSON.length(); i++) { JSONObject album = albumJSON.getJSONObject(i); String album_name = album.getString("album_name"); String album_artist = album.getString("album_artist"); String album_composer = album.getString("album_composer"); String album_year = album.getString("album_year"); processAlbumData(album_name, album_artist, album_composer, album_year); } // Process Songs JSONArray songsJSON = json.getJSONArray("songs"); for (int i = 0; i < songsJSON.length(); i++) { JSONObject song = songsJSON.getJSONObject(i); String title = song.getString("title"); String album = song.getString("album"); String artist = song.getString("artist"); String genre = song.getString("genre"); processSongData(android_id, title, album, artist, genre); } } catch (Exception e) { e.printStackTrace(); } } private void processAlbumData(String name, String artist, String composer, String year) { Connection conn = null; Statement stmt = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); String sql = "select * from album where album_title='" + name + "'"; rs = stmt.executeQuery(sql); rs.next(); if (rs.getRow() == 0) { pstmt = conn.prepareStatement( "insert into album (album_title, album_artist, album_composer, release_year) values (?, ?, ?, ?)"); pstmt.setString(1, name); pstmt.setString(2, artist); pstmt.setString(3, composer); pstmt.setString(4, year); pstmt.executeUpdate(); } //out.print(resultJSON); } catch (Exception se) { //out.println("Exception preparing or processing query: " + se); se.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { } } } private void processSongData(String androidId, String title, String album, String artist, String genre) { Connection conn = null; Statement stmt = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName(JDBC_DRIVER); conn = DriverManager.getConnection(DB_URL, USER, PASS); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); // Get the albumId String sql = "select album_id from album where album_title='" + album + "'"; rs = stmt.executeQuery(sql); rs.next(); int albumId = rs.getInt("album_id"); sql = "select * from song where song_title = '" + title.replaceAll("'", "\u0027") + "'"; rs = stmt.executeQuery(sql); rs.next(); if (rs.getRow() == 0) { // Insert Song pstmt = conn.prepareStatement( "insert into song (song_title, genre, artist_name, album_id) values (?, ?, ?, ?)"); pstmt.setString(1, title.replaceAll("'", "\u0027")); pstmt.setString(2, genre); pstmt.setString(3, artist); pstmt.setInt(4, albumId); pstmt.executeUpdate(); } // Get Song Id sql = "select song_id from song where song_title='" + title.replaceAll("'", "\u0027") + "'"; rs = stmt.executeQuery(sql); rs.next(); int songId = rs.getInt("song_id"); // Check if user_music_data entry exist sql = "select * from user_music_data where song_id=" + songId + " AND android_id='" + androidId + "'"; rs = stmt.executeQuery(sql); rs.next(); if (rs.getRow() == 0) { // Insert into user_music_data sql = "insert into user_music_data (android_id, song_id) values ('" + androidId + "', " + songId + ")"; stmt.executeUpdate(sql); } //out.print(resultJSON); } catch (Exception se) { //out.println("Exception preparing or processing query: " + se); se.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { } } } // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code."> /** * Handles the HTTP <code>GET</code> method. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } /** * Handles the HTTP <code>POST</code> method. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } /** * Returns a short description of the servlet. * * @return a String containing servlet description */ @Override public String getServletInfo() { return "Short description"; }// </editor-fold> }