Demo entry 6529141

jdbc

   

Submitted by anonymous on Jun 01, 2017 at 01:15
Language: Java. Code size: 17.1 kB.

package team10;

import java.io.*;
import java.sql.*;
import java.util.Scanner;

/**
 * <h1>Film Data Management</h1> The Team10 program implements an application
 * that insert, update, delete, retrieve data from database Team10.
 * 
 * <p>
 * <b>Note:</b> The database Team10 includes 10 tables: DBCOURSE_PERSON,
 * DBCOURSE_ACTOR, DBCOURSE_DIRECTOR, DBCOURSE_FILM, DBCOURSE_CASTING,
 * DBCOURSE_CRITIC, DBCOURSE_EVALUATION, DBCOURSE_ARTIST, DBCOURSE_OST
 * 
 * @author Juhee Kang, Rheehyun Kim, Hyangmin Lee
 * @version 1.0
 * @since 2017-06-01
 *
 */
public class Team10 {

	static Connection conn = null;
	static Statement stmt = null;
	static String table = null;
	static ResultSet srs = null;
	Scanner sc = new Scanner(System.in);

	/**
	 * This is the main method which makes use of printTable, insert, delete
	 * method.
	 * 
	 * @param args
	 *            Unused.
	 * @return void No return value is used in this method.
	 * @exception UnsupportedEncodingException
	 *                on connecting sql.
	 * @see UnsupportedEncodingException
	 */
	// main method: written by ������, ������
	public static void main(String[] args) throws UnsupportedEncodingException {
		// TODO Auto-generated method stub

		String databaseURL = "jdbc:mysql://localhost:3306/Team10";
		String user = "team10";
		String password = "team10";

		while (true) {
			try {
				Class.forName("com.mysql.jdbc.Driver");
				conn = DriverManager.getConnection(databaseURL, user, password);
				if (conn != null) {
					System.out.println("Connected to the database");

					try {
						conn.setAutoCommit(false);
						System.out.println("--------------------");
						Scanner sc = new Scanner(System.in);
						System.out.println("1. insert");
						System.out.println("2. update");
						System.out.println("3. delete");
						System.out.println("4. retrieve");
						System.out.println("5. exit");
						System.out.println("--------------------");
						System.out.print("enter number>> ");
						int num = sc.nextInt();

						switch (num) {
						case 1:
							System.out.println("enter table name to insert: ");
							System.out.println(
									"(actor, artist, casting, country, critic, director, evaluation, film, ost, person)");
							System.out.print(">> ");
							table = sc.next();
							insert(table);
							break;

						case 2:
							sc.nextLine();
							stmt = conn.createStatement();
							System.out.println("enter table name to update: ");
							System.out.println(
									"(actor, artist, casting, country, critic, director, evaluation, film, ost, person)");
							System.out.print(">> ");
							String update = sc.nextLine();
							System.out.print("set >> ");
							String set = sc.nextLine();
							System.out.print("where >> ");
							String where = sc.nextLine();
							stmt.executeUpdate("update DBCOURSE_" + update + " set " + set + " where " + where + ";");
							break;

						case 3:
							System.out.println("enter table name to delete: ");
							System.out.println(
									"(actor, artist, casting, country, critic, director, evaluation, film, ost, person)");
							System.out.print(">> ");
							table = sc.next();
							delete(table);
							break;

						case 4:
							sc.nextLine();
							System.out.println("enter table name to retrieve: ");
							System.out.println(
									"(actor, artist, casting, country, critic, director, evaluation, film, ost, person)");
							String from = sc.nextLine();
							System.out.print(from + "table has these attributes: ");
							switch (from) {
							case "actor":
								System.out.print("name, born_year, gender, debut_year");
								break;
							case "artist":
							case "director":
								System.out.print("name, gender, birth, debut");
								break;
							case "casting":
								System.out.print("film_actor, film_name");
								break;
							case "country":
								System.out.print("name, language, capital");
								break;
							case "critic":
								System.out.print("name, birth, gender");
								break;
							case "evaluation":
								System.out.print("film, critic, score");
								break;
							case "film":
								System.out.print("title, release_year, genre");
								break;
							case "ost":
								System.out.print("film, artist, song");
								break;
							case "person":
								System.out.print("name, born_year, gender, job");
								break;
							}
							System.out.println(
									"\n You can refer to these attributes above or use other nested/join queries");
							System.out.print("enter where clause >> ");
							String where1 = sc.nextLine();
							stmt = conn.createStatement();
							ResultSet rs = stmt
									.executeQuery("select * from DBCOURSE_" + from + " where " + where1 + ";");
							printTable(rs, from);

							break;

						case 5:
							System.out.println("End of the program");
							return;
						}

						conn.commit();

					} catch (SQLException ex) {
						ex.printStackTrace();
						try {
							conn.rollback();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					} finally {
						try {
							stmt.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}

				}
			} catch (ClassNotFoundException ex) {
				System.out.println("Could not find database driver class");
				ex.printStackTrace();
			} catch (SQLException ex) {
				System.out.println("An error occurred. Maybe user/password is invalid");
				ex.printStackTrace();
			}
		}
	}

	/**
	 * This method is used to print table of the database Team10.
	 * 
	 * @param rs
	 *            This is the first parameter to printTable method
	 * @param from
	 *            This is the second parameter to printTable method
	 * @return void No return value is used in this method.
	 * @exception SQLException,
	 *                UnsupportedEncodingException.
	 * @see SQLException, UnsupportedEncodingException
	 */
	// printTable: written by ������(case actor, artist, casting, critic,
	// country)
	// ������(case director, evaluation, film, ost, person)
	private static void printTable(ResultSet rs, String from) throws SQLException, UnsupportedEncodingException {
		switch (from) {
		case "actor":
			System.out.printf("%-20s|%-10s|%-10s|%-10s\n", "name", "gender", "birth", "debut");
			while (rs.next()) {
				System.out.printf("%-20s|", new String(rs.getString("name")));
				System.out.printf("%-10s|", new String(rs.getString("gender")));
				System.out.printf("%-10s|", rs.getString("born_year"));
				System.out.printf("%-10s\n", rs.getString("debut_year"));
			}
			break;
		case "artist":
			System.out.printf("%-25s|%-10s|%-10s|%-10s\n", "name", "gender", "birth", "debut");
			while (rs.next()) {
				System.out.printf("%-25s|", new String(rs.getString("name")));
				System.out.printf("%-10s|", new String(rs.getString("gender")));
				System.out.printf("%-10s|", rs.getString("birth"));
				System.out.printf("%-10s\n", rs.getString("debut"));
			}
			break;
		case "casting":
			System.out.printf("%-25s|%-45s\n", "actor", "film");
			while (rs.next()) {
				System.out.printf("%-25s|", new String(rs.getString("film_actor")));
				System.out.printf("%-45s\n", new String(rs.getString("film_name")));
			}
			break;
		case "country":
			System.out.printf("%-20s|%-20s|%-20s\n", "name", "capital", "language");
			while (rs.next()) {
				System.out.printf("%-20s|", new String(rs.getString("name")));
				System.out.printf("%-20s|", new String(rs.getString("capital")));
				System.out.printf("%-20s\n", new String(rs.getString("language")));
			}
			break;
		case "critic":
			System.out.printf("%-20s|%-10s|%-10s\n", "name", "birth", "gender");
			while (rs.next()) {
				System.out.printf("%-20s|", new String(rs.getString("name")));
				System.out.printf("%-10s|", rs.getString("birth"));
				System.out.printf("%-10s\n", new String(rs.getString("gender")));
			}
			break;
		case "director":
			System.out.printf("%-30s|%-10s|%-10s|%-10s\n", "name", "gender", "birth", "debut");
			while (rs.next()) {
				System.out.printf("%-30s|", new String(rs.getString("name")));
				System.out.printf("%-10s|", new String(rs.getString("gender")));
				System.out.printf("%-10s|", rs.getString("birth"));
				System.out.printf("%-10s\n", rs.getString("debut"));
			}
			break;
		case "evaluation":
			System.out.printf("%-50s|%-10s|%-20s\n", "film", "critic", "score");
			while (rs.next()) {
				System.out.printf("%-50s|", new String(rs.getString("film")));
				System.out.printf("%-20s|", new String(rs.getString("critic")));
				System.out.printf("%-20s\n", new String(rs.getString("score")));
			}
			break;
		case "film":
			System.out.printf("%-50s|%-10s|%-20s\n", "title", "release_year", "genre");
			while (rs.next()) {
				System.out.printf("%-50s|", new String(rs.getString("title")));
				System.out.printf("%-20s|", new String(rs.getString("release_year")));
				System.out.printf("%-20s\n", new String(rs.getString("genre")));
			}

			break;
		case "ost":
			System.out.printf("%-50s|%-10s|%-20s\n", "film", "artist", "song");
			while (rs.next()) {
				System.out.printf("%-50s|", new String(rs.getString("film")));
				System.out.printf("%-20s|", new String(rs.getString("artist")));
				System.out.printf("%-20s\n", new String(rs.getString("song")));
			}

			break;
		case "person":
			System.out.printf("%-40s|%-10s|%-10s|%-10s\n", "name", "born_year", "gender", "job");
			while (rs.next()) {
				System.out.printf("%-40s|", new String(rs.getString("name")));
				System.out.printf("%-10s|", new String(rs.getString("born_year")));
				System.out.printf("%-10s|", rs.getString("gender"));
				System.out.printf("%-10s\n", rs.getString("job"));
			}
			break;
		}

	}

	/**
	 * This method is used to insert data in the database Team10.
	 * 
	 * @param table
	 *            This is the first parameter to insert method
	 * @return void No return value is used in this method.
	 * @exception SQLException
	 *                on using sql.
	 * @see SQLException
	 */
	// insert: written by ������, ������
	private static void insert(String table) throws SQLException {
		stmt = conn.createStatement();
		Scanner sc1 = new Scanner(System.in);
		System.out.println("enter data information below");
		switch (table) {
		case "person":
			System.out.print("name: ");
			String name = sc1.nextLine();
			System.out.print("birth: ");
			int birth = sc1.nextInt();
			System.out.print("gender: ");
			String gender = sc1.next();
			System.out.print("job: ");
			String job = sc1.next();
			stmt.executeUpdate(
					"insert into DBCOURSE_PERSON values('" + name + "'," + birth + ",'" + gender + "','" + job + "');");
			break;
		case "actor":
			System.out.print("name: ");
			String aname = sc1.nextLine();
			System.out.print("born_year: ");
			int abirth = sc1.nextInt();
			System.out.print("gender: ");
			String agender = sc1.next();
			sc1.nextLine();
			System.out.print("debut_year: ");
			int adebut = sc1.nextInt();
			stmt.executeUpdate("insert into DBCOURSE_ACTOR values('" + aname + "'," + abirth + ",'" + agender + "',"
					+ adebut + ");");
			break;

		case "artist":
			System.out.print("name : ");
			String artname = sc1.nextLine();
			System.out.print("gender : ");
			String artgender = sc1.next();
			System.out.print("birth : ");
			int artbirth = sc1.nextInt();
			System.out.print("debut : ");
			int debut = sc1.nextInt();
			stmt.executeUpdate("insert into DBCOURSE_ARTIST values('" + artname + "','" + artgender + "'," + artbirth
					+ "," + debut + ");");
			break;
		case "critic":
			System.out.print("name: ");
			String cname = sc1.nextLine();
			System.out.print("birth: ");
			int cbirth = sc1.nextInt();
			System.out.print("gender: ");
			String cgender = sc1.next();
			stmt.executeUpdate("insert into DBCOURSE_CRITIC values('" + cname + "'," + cbirth + ",'" + cgender + "');");
			break;
		case "director":
			System.out.print("name: ");
			String dirname = sc1.nextLine();
			System.out.print("birth : ");
			int dirbirth = sc1.nextInt();
			System.out.print("gender : ");
			String dirgender = sc1.next();
			System.out.print("debut : ");
			int dirdebut = sc1.nextInt();
			stmt.executeUpdate("insert into DBCOURSE_DIRECTOR values('" + dirname + "'," + dirbirth + ",'" + dirgender
					+ "'," + dirdebut + ");");
			break;
		case "casting":
			System.out.print("film_actor : ");
			String film_actor = sc1.nextLine();
			System.out.print("film_name : ");
			String film_name = sc1.nextLine();
			stmt.executeUpdate("insert into DBCOURSE_CASTING values('" + film_actor + "','" + film_name + "');");
			break;

		case "film":
			System.out.print("title: ");
			String title = sc1.nextLine();
			System.out.print("release_year: ");
			int release_year = sc1.nextInt();
			System.out.print("genre: ");
			String genre = sc1.next();
			stmt.executeUpdate(
					"insert into DBCOURSE_FILM values('" + title + "'," + release_year + ",'" + genre + "');");
			break;

		case "country":
			System.out.print("name: ");
			String ctrname = sc1.nextLine();
			System.out.print("capital: ");
			String capital = sc1.nextLine();
			System.out.print("language: ");
			String language = sc1.next();
			stmt.executeUpdate(
					"insert into DBCOURSE_COUNTRY values('" + ctrname + "','" + capital + "','" + language + "');");
			break;

		case "ost":
			System.out.print("film: ");
			String film = sc1.nextLine();
			System.out.print("artist: ");
			String artist = sc1.nextLine();
			System.out.print("song: ");
			String song = sc1.nextLine();
			stmt.executeUpdate("insert into DBCOURSE_OST values('" + film + "','" + artist + "','" + song + "');");
			break;

		case "evaluation":
			System.out.print("film: ");
			String film_title = sc1.nextLine();
			System.out.print("critic: ");
			String critic = sc1.nextLine();
			System.out.print("score: ");
			int score = sc1.nextInt();
			stmt.executeUpdate(
					"insert into DBCOURSE_EVALUATION values('" + film_title + "','" + critic + "'," + score + ");");

		}
	}

	/**
	 * This method is used to delete data from the database Team10.
	 * 
	 * @param table
	 *            This is the first parameter to delete method
	 * @return void No return value is used in this method.
	 * @exception SQLException
	 *                on using sql.
	 * @see SQLException
	 */
	// delete: written by ������, ������
	private static void delete(String table) throws SQLException {
		stmt = conn.createStatement();
		Scanner sc1 = new Scanner(System.in);
		switch (table) {
		case "actor":
			System.out.print("name :");
			String name = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_ACTOR where name='" + name + "';");
			break;

		case "casting":
			System.out.print("film_actor : ");
			String film_actor = sc1.nextLine();
			System.out.print("film_name : ");
			String film_name = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_CASTING where film_actor='" + film_actor + "' and film_name='"
					+ film_name + "';");
			break;

		case "film":
			System.out.print("title : ");
			String title = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_FILM where title='" + title + "';");
			break;

		case "director":
			System.out.print("name: ");
			String dirname = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_DIRECTOR where name='" + dirname + "';");
			break;

		case "country":
			System.out.print("name : ");
			String cname = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_COUNTRY where name='" + cname + "';");
			break;

		case "evaluation":
			System.out.print("film : ");
			String film = sc1.nextLine();
			System.out.print("critic : ");
			String critic = sc1.nextLine();
			stmt.executeUpdate(
					"delete from DBCOURSE_EVALUATION where film='" + film + "' and critic='" + critic + "';");
			break;

		case "artist":
			System.out.print("artist name: ");
			String artist_name = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_ARTIST where name='" + artist_name + "';");
			break;

		case "critic":
			System.out.print("critic name: ");
			String critic_name = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_CRITIC where name='" + critic_name + "';");
			break;

		case "ost":
			System.out.print("film name: ");
			String filmName = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_OST where film='" + filmName + "';");
			break;

		case "person":
			System.out.print("name: ");
			String person_name = sc1.nextLine();
			stmt.executeUpdate("delete from DBCOURSE_PERSON where name='" + person_name + "';");
			break;

		}
	}
}

This snippet took 0.03 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).