Java와 MariaDB 연동(INSERT, UPDATE, READ, DELETE) 예제

자바로 MariaDB 연동하여 INSERT, UPDATE, READ, DELETE 하기 예제

 

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
	//접속정보를 가지고 있는 클래스
	public static Connection getConnection() {
		Connection conn = null;		
		try {
			Class.forName("org.mariadb.jdbc.Driver");
			String url = "jdbc:mariadb://220.70.33.29/unknown";
			conn = DriverManager.getConnection(url, "unknown", "01234567");
		} catch (Exception e) {
			e.printStackTrace();
		}			
		return conn; 
	}
}

 

Ex 1)

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class DB05 {
	static Connection conn = null;
	static Statement stmt = null;
	static ResultSet rs = null;

	public static void list() {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM member LIMIT 10;");
			System.out.println("번호\t이름\t나이\t사는곳");
			System.out.println("---------------------------");
			while (rs.next()) {
				System.out.print(rs.getString("member_no") + "\t");// 컬럼명
				System.out.print(rs.getString("member_name") + "\t");
				System.out.print(rs.getString("member_age") + "\t");
				System.out.println(rs.getString("member_addr"));
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}finally {
			System.out.println("조회 완료");
		}
	}

	public static void delete(Scanner sc) {
		System.out.println("------삭제-------");
		System.out.print("삭제할 번호를 입력하세요");
		int delnum = sc.nextInt();
		try {
			stmt = conn.createStatement();
			stmt.execute("DELETE FROM member WHERE member_no=" + delnum + ";");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			System.out.println("삭제 완료");
		}
	}

	public static void insert(Scanner sc) {
		System.out.println("------입력-------");
		System.out.println("이름을 입력하세요.");
		String name = sc.next();
		System.out.println("나이를 입력하세요.");
		int age = sc.nextInt();
		System.out.println("사는 곳을 입력하세요.");
		String addr = sc.next();
		try {
			stmt = conn.createStatement();
			stmt.execute("INSERT INTO member (member_name, member_age, member_addr)" + " VALUES('" + name + "', " + age
					+ ", '" + addr + "');");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			System.out.println("입력 완료");
		}
	}

	public static void update(Scanner sc) {
		System.out.println("------수정-------");
		System.out.print("수정할 번호를 입력하세요");
		int updatenum = sc.nextInt();
		System.out.println("수정할 이름을 입력하세요.");
		String name = sc.next();
		System.out.println("수정할 나이를 입력하세요.");
		int age = sc.nextInt();
		System.out.println("수정할 사는 곳을 입력하세요.");
		String addr = sc.next();
		try {
			stmt = conn.createStatement();
			stmt.execute("UPDATE member SET " + "member_name='" + name + "', member_age=" + age + ", member_addr='"
					+ addr + "' " + "WHERE member_no=" + updatenum + ";");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			System.out.println("수정 완료");
		}
	}
	
	public static void main(String[] args) {

		Scanner sc = new Scanner(System.in);
		conn = DBConnection.getConnection();
		boolean loop=true;
		while (loop) {
			System.out.println("1.조회\t2.추가\t3.수정\t4.삭제\t9.나가기");
			int input = sc.nextInt();
			switch (input) {
			case 1:
				list();
				break;
			case 2:
				insert(sc);
				break;
			case 3:
				update(sc);
				break;
			case 4:
				delete(sc);
				break;
			case 9:
				loop=false;
				sc.close();
				try {
					stmt.close();
					rs.close();
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				break;
			}			
		}	
	}
}

 

Ex 2)

package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class DB06_allQuery {
	static Connection conn;
	static Statement stmt;

	public static void main(String[] args) {
		DB06_allQuery aq = new DB06_allQuery();// 객체 생성
		DBConnection dbc = new DBConnection();
		conn = dbc.getConnection();

		Scanner sc = new Scanner(System.in);
		boolean check = true;

		try {
			stmt = conn.createStatement();
			while (check) {
				System.out.println("회원목록입니다.");
				aq.select();
				System.out.println("원하는 작업을 선택하세요.");
				System.out.println("1.추가  2.삭제  3.수정 9.나가기");

				int input = sc.nextInt();
				switch (input) {
				case 1:
					aq.insert(sc);
					break;
				case 2:
					aq.delete(sc);
					break;
				case 3:
					aq.update(sc);
					break;
				case 9:
					System.out.println("종료합니다.");
					check = false;
					break;

				default:
					System.out.println("그런 번호는 없습니다.");
					break;
				}
			}

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

	}

	private void update(Scanner sc) {
		System.out.println("수정입니다.");
		System.out.println("수정할 번호를 입력하세요.");
		int number = sc.nextInt();
		System.out.println("수정할 이름을 입력하세요.");
		String name = sc.next();
		System.out.println("수정할 나이를 입력하세요.");
		int age = sc.nextInt();
		System.out.println("수정할 사는 곳을 입력하세요.");
		String addr = sc.next();

		String sql = "UPDATE member SET member_name='" + name + "', member_age=" + age + ", member_addr='" + addr
				+ "' WHERE member_no=" + number;

		try {
			stmt.execute(sql);
		} catch (SQLException e) {
			System.out.println("심각한 문제가 발생하였습니다.");
		}

		System.out.println("수정하였습니다.");

	}

	private void insert(Scanner sc) {
		System.out.println("신규 등록입니다.");
		System.out.println("이름을 입력하세요.");
		String name = sc.next();
		System.out.println("나이를 입력하세요.");
		int age = sc.nextInt();
		System.out.println("사는 곳을 입력하세요.");
		String addr = sc.next();

		String sql = "INSERT INTO member (member_name, member_age, member_addr) " + " VALUES('" + name + "', " + age
				+ ", '" + addr + "')";

		try {
			stmt.execute(sql);
		} catch (SQLException e) {
			System.out.println("심각한 문제가 발생하였습니다.");
		}

		System.out.println("완료하였습니다.");

	}

	public void delete(Scanner sc) {
		System.out.println("삭제할 번호를 입력하세요.");
		int input = sc.nextInt();
		try {
			// stmt = conn.createStatement();
			stmt.execute("DELETE FROM member WHERE member_no=" + input);
		} catch (SQLException e) {
		}
	}

	public void select() {
		ResultSet rs = null;

		try {
			rs = stmt.executeQuery("SELECT * FROM member LIMIT 10");
			System.out.println("┌──────┬──────────┬──────┬───────────┐");
			System.out.println("│ 번호 │  이  름  │ 나이 │  사는 곳  │");
			System.out.println("├──────┼──────────┼──────┼───────────┤");
			while (rs.next()) {
				String no = rs.getString("member_no");
				String name = sortString(rs.getString("member_name"));
				String age = rs.getString("member_age");
				String addr = sortString(rs.getString("member_addr"));
				
				System.out.printf("│%5.5s │%5s│%5.5s │%5.5s │\n",
						no, name, age, addr);
			}
			System.out.println("└──────┴──────────┴──────┴───────────┘");
		} catch (Exception e) {

		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
	public static String sortString(String str) {
		if(str.length() > 5) {
			str = str.substring(0, 5);
		}else if(str.length() < 5){
			String temp = str;
			for (int i = 0; i < 5 - str.length(); i++) {
				temp = "　" + temp;
			}
			str = temp;
		}
		return str;
	}
}