import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;


public class JdbcTest {

	/**
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) {

		try {
			Class.forName("org.apache.derby.jdbc.ClientDriver");
		} catch(ClassNotFoundException e) {
			System.out.println("Please ensure that the Derby Client jar is in your classpath");
			System.exit(0);
		}

		String URL = "jdbc:derby://localhost:1527/JavaTunesDB";
		//String URL = "jdbc:derby://192.168.1.149:1527/JavaTunesDB";
		try {
			Connection conn = DriverManager.getConnection(URL, "guest", "password");
			create(conn);
			update(conn);
			retrieve(conn);


		}catch(SQLException e) {
			e.printStackTrace();
		}
	}

	public static void update(Connection conn) throws SQLException {
		String sql = "UPDATE ITEM SET ARTIST=? WHERE ARTIST=?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, "Xeander Lennox");
		stmt.setString(2, "Anthony Lennox");

		int count = stmt.executeUpdate();
		if (count==1){
			System.out.println("Record updated");
		} else {
			System.out.println("No records were updated");
		}
	}


	public static void create(Connection conn) throws SQLException {
		String sql = "INSERT INTO ITEM (TITLE, ARTIST, RELEASEDATE, LISTPRICE, PRICE) VALUES (?, ?, ?, ?, ?)";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, "Diva 2");
		stmt.setString(2, "Anthony Lennox");
		stmt.setDate(3, new Date(System.currentTimeMillis()));
		stmt.setDouble(4, 12.99);
		stmt.setFloat(5, 10.99f);

		int count = stmt.executeUpdate();
		if (count==1){
			System.out.println("Record saved");
		} else {
			System.out.println("Save failed");
		}
	}

	public static void retrieve(Connection conn) throws SQLException {
		String sql = "SELECT * FROM ITEM";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);

		while(rs.next()){
			String ID = rs.getString("ITEM_ID");
			String Artist = rs.getString("ARTIST");
			System.out.println("ID: " + ID + " " + "ARTIST: " + Artist);
		}
	}

}