package db.utils;
import java.sql.Connection;
import java.sql.DriverManager;
public class ConnectionUtils {
public static Connection getConnection() throws Exception {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "hr";
String password = "zxcv1234";
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
}자바/jdbc
utils
2019. 6. 10. 09:53
demo3
2019. 6. 10. 09:53
package db.demo3;
import java.sql.Date;
public class UserVO {
private String id;
private String name;
private String pwd;
private String phone;
private String email;
private int point;
private Date createDate;
public UserVO(String id, String name, String pwd, String phone, String email, int point, Date createDate) {
super();
this.id = id;
this.name = name;
this.pwd = pwd;
this.phone = phone;
this.email = email;
this.point = point;
this.createDate = createDate;
}
public UserVO() {}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public int getPoint() {
return point;
}
public void setPoint(int point) {
this.point = point;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
}
package db.demo3;
import java.util.ArrayList;
public class UserService {
UserDAO userDao = new UserDAO();
public void removeUser(String id) throws Exception {
UserVO user = userDao.getUserById(id);
if (user == null) {
throw new Exception("사용자 정보가 존재하지 않습니다.");
}
userDao.deleteUser(id);
}
public void modifyUserInfo(UserVO newUser) throws Exception {
UserVO user = userDao.getUserById(newUser.getId());
if (user == null) {
throw new Exception("사용자 정보가 존재하지 않습니다.");
}
user.setName(newUser.getName());
user.setPhone(newUser.getPhone());
user.setEmail(newUser.getEmail());
userDao.updateUser(user);
}
public UserVO findUserById(String id) throws Exception {
UserVO user = userDao.getUserById(id);
if (user == null) {
throw new Exception("[" + id + "]에 해당하는 사용자 정보를 찾을 수 없습니다.");
}
return user;
}
public ArrayList<UserVO> searchUsers(String name) throws Exception {
ArrayList<UserVO> users = userDao.searchUsersByName(name);
if (users.isEmpty()) {
throw new Exception("[" + name + "]에 해당하는 사용자 정보를 찾을 수 없습니다.");
}
// 검색된 정보가 있을 경우에만 정보를 반환함
return users;
}
public ArrayList<UserVO> getAllUsers() throws Exception {
ArrayList<UserVO> users = userDao.getAllUsers();
return users;
}
public void addNewUser(UserVO user) throws Exception {
// 사용자가 입력한 아이디로 사용자 정보를 조회한다.
UserVO dbUser = userDao.getUserById(user.getId());
// 이미 등록된 사용자 정보가 존재하는지 체크한다.
if (dbUser != null) {
throw new Exception("동일한 아이디를 가진 사용자가 이미 존재합니다.");
}
// 등록된 사용자 정보가 없으면 신규 사용자로 등록한다.
userDao.addUser(user);
}
}
package db.demo3;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import db.utils.ConnectionUtils;
public class UserDAO {
// 사용자 정보 변경하기
public void updateUser(UserVO user) throws Exception {
String sql = "update store_users set user_name = ? , user_phone = ?, user_email = ? where user_id = ?";
Connection con = ConnectionUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getPhone());
pstmt.setString(3, user.getEmail());
pstmt.setString(4, user.getId());
pstmt.executeUpdate();
pstmt.close();
con.close();
}
// 새로운 사용자 정보 추가하기
public void addUser(UserVO user) throws Exception {
String sql = "insert into store_users (user_id, user_name, user_pwd, user_phone, user_email, user_point) "
+ "values (?, ?, ?, ?, ?, 0)";
Connection con = ConnectionUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, user.getId());
pstmt.setString(2, user.getName());
pstmt.setString(3, user.getPwd());
pstmt.setString(4, user.getPhone());
pstmt.setString(5, user.getEmail());
pstmt.executeUpdate();
pstmt.close();
con.close();
}
// 지정된 아이디에 해당하는 사용자 정보 삭제하기
public void deleteUser(String id) throws Exception {
String sql = "delete from store_users where user_id = ?";
Connection con = ConnectionUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.executeUpdate();
pstmt.close();
con.close();
}
// 지정된 이름에 해당하는 사용자 정보 조회하기
public ArrayList<UserVO> searchUsersByName(String name) throws Exception {
ArrayList<UserVO> users = new ArrayList<UserVO>();
String sql = "select * from store_users where user_name = ?";
Connection con = ConnectionUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
UserVO user = new UserVO();
user.setId(rs.getString("user_id"));
user.setName(rs.getString("user_name"));
user.setPwd(rs.getString("user_pwd"));
user.setPhone(rs.getString("user_phone"));
user.setEmail(rs.getString("user_email"));
user.setPoint(rs.getInt("user_point"));
user.setCreateDate(rs.getDate("user_create_date"));
users.add(user);
}
rs.close();
pstmt.close();
con.close();
return users;
}
// 지정된 아이디에 해당하는 사용자 정보 조회하기 1
public UserVO getUserById (String id) throws Exception {
UserVO user = null;
String sql = "select * from store_users where user_id = ?";
Connection con = ConnectionUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setString(1, id);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
user = new UserVO();
//UserVO user = new UserVO();
user.setId(rs.getString("user_id"));
user.setName(rs.getString("user_name"));
user.setPwd(rs.getString("user_pwd"));
user.setPhone(rs.getString("user_phone"));
user.setEmail(rs.getString("user_email"));
user.setPoint(rs.getInt("user_point"));
user.setCreateDate(rs.getDate("user_create_date"));
}
rs.close();
pstmt.close();
con.close();
return user;
}
// 전체 사용자 정보 조회하기
public ArrayList<UserVO> getAllUsers() throws Exception {
ArrayList<UserVO> users = new ArrayList<UserVO>();
String sql = "select * from store_users order by user_id asc";
Connection con = ConnectionUtils.getConnection();
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String id = rs.getString("user_id");
String name = rs.getString("user_name");
String pwd = rs.getString("user_pwd");
String phone = rs.getString("user_phone");
String email = rs.getString("user_email");
int point = rs.getInt("user_point");
Date createDate = rs.getDate("user_create_date");
UserVO user = new UserVO();
user.setId(id);
user.setName(name);
user.setPwd(pwd);
user.setPhone(phone);
user.setEmail(email);
user.setPoint(point);
user.setCreateDate(createDate);
users.add(user);
}
rs.close();
pstmt.close();
con.close();
return users;
}
}
package db.demo3;
import java.util.ArrayList;
import java.util.Scanner;
public class UserApp {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
UserService service = new UserService();
while (true) {
try {
System.out.println("1. 가입 2. 전체 조회 3. 이름 검색 4. 아이디 검색 5. 삭제 6. 경로 변경 0. 종료");
System.out.println("메뉴 선택> ");
int menu = scanner.nextInt();
if (menu == 1) {
System.out.println("[회원 가입]");
System.out.println("아이디 입력> ");
String id = scanner.next();
System.out.println("이름 입력> ");
String name = scanner.next();
System.out.println("비밀번호 입력> ");
String pwd = scanner.next();
System.out.println("전화번호 입력> ");
String phone = scanner.next();
System.out.println("이메일 입력> ");
String email = scanner.next();
UserVO user = new UserVO();
user.setId(id);
user.setName(name);
user.setPwd(pwd);
user.setPhone(phone);
user.setEmail(email);
service.addNewUser(user);
} else if (menu == 2) {
System.out.println("[전체 조회]");
ArrayList<UserVO> users = service.getAllUsers();
if (users.isEmpty()) {
System.out.println("조회된 사용자 정보가 없습니다.");
} else {
for (UserVO user : users) {
System.out.println("아이디: " + user.getId());
System.out.println("이름: " + user.getName());
System.out.println("연락처: " + user.getPhone());
System.out.println();
}
}
} else if (menu == 3) {
System.out.println("[이름으로 검색하기]");
System.out.println("검색할 이름 입력> ");
String name = scanner.next();
ArrayList<UserVO> users = service.searchUsers(name);
for (UserVO user : users) {
System.out.println("아이디: " + user.getId());
System.out.println("이름: " + user.getName());
System.out.println("연락처: " + user.getPhone());
System.out.println();
}
} else if (menu == 4) {
System.out.println("[아이디로 검색하기]");
System.out.println("검색할 아이디 입력> ");
String id = scanner.next();
UserVO user = service.findUserById(id);
System.out.println("아이디: " + user.getId());
System.out.println("이름: " + user.getName());
System.out.println("연락처: " + user.getPhone());
System.out.println("이메일: " + user.getEmail());
System.out.println("포인트: " + user.getPoint());
System.out.println("가입일: " + user.getCreateDate());
System.out.println();
} else if (menu == 5) {
System.out.println("[사용자 정보 삭제]");
System.out.println("삭제 대상 아이디 입력> ");
String id = scanner.next();
service.removeUser(id);
} else if (menu == 6) {
System.out.println("[사용자 정보 업데이트]");
System.out.println("변경 대상 아이디 입력> ");
String id = scanner.next();
System.out.println("새 이름 입력> ");
String name = scanner.next();
System.out.println("새 연락처 입력> ");
String phone = scanner.next();
System.out.println("새 이메일 입력> ");
String email = scanner.next();
UserVO user = new UserVO();
user.setId(id);
user.setName(name);
user.setPhone(phone);
user.setEmail(email);
service.modifyUserInfo(user);
} else if (menu == 0) {
break;
}
} catch (Exception e) {
e.printStackTrace();
}
System.out.println();
}
scanner.close();
}
}
demo2
2019. 6. 10. 09:48
package db.demo2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.Scanner;
public class BookService {
Scanner scanner = new Scanner(System.in);
// 모든 책 정보를 조회하는 기능
public void selectAllBooks() throws Exception {
/*
* 1. SQL 정의
* 2. 드라이브 메모리 로딩
* 3. Connection 획득
* 4. PreparedStatement 획득
* (5. ?에 값 설정)
* 6. 실행
* (7. ResultSet 처리)
* 8. 자원 해제
*/
String sql = "select book_no, book_title, book_author, book_publisher, book_price, book_pubdate, book_create_date from books";
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
int no = rs.getInt("book_no");
String title = rs.getString("book_title");
String author = rs.getString("book_author");
String publisher = rs.getString("book_publisher");
int price = rs.getInt("book_price");
String pubdate = rs.getString("book_pubdate");
Date createdate = rs.getDate("book_create_date");
System.out.println(no + ", " + author + ", " + publisher + ", " + price + ", " + pubdate + ", " + createdate);
}
rs.close();
pstmt.close();
conn.close();
}
// 제목을 전달받아서 그 제목에 해당하는 책 정보를 조회하는 기능
public void selectBooksByTitle(String keyword) throws Exception {
String sql = "select book_no, book_title, book_author, book_publisher, book_price, book_pubdate, book_create_date from books where book_title like '%' || ? || '%' ";
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, keyword);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
int no = rs.getInt("book_no");
String title = rs.getString("book_title");
String author = rs.getString("book_author");
String publisher = rs.getString("book_publisher");
int price = rs.getInt("book_price");
String pubdate = rs.getString("book_pubdate");
Date createdate = rs.getDate("book_create_date");
System.out.println(no + ", " + title + ", " + author + ", " + publisher + ", " + price + ", " + pubdate + ", " + createdate);
}
rs.close();
pstmt.close();
conn.close();
}
// 책 정보를 전달받아서 db에 저장하는 기능
public void insertNewBook(int no, String title, String author, String publisher, int price, String pubdate) throws Exception {
String sql = "insert into books (book_no, book_title, book_author, book_publisher, book_price, book_pubdate) "
+ " values (?, ?, ?, ?, ?, ?)";
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, no);
pstmt.setString(2, title);
pstmt.setString(3, author);
pstmt.setString(4, publisher);
pstmt.setInt(5, price);
pstmt.setString(6, pubdate);
int rs = pstmt.executeUpdate();
System.out.println(rs + "개의 행이 저장되었습니다.");
pstmt.close();
conn.close();
}
// 삭제할 책 번호를 전달받아서 DB에서 책 정보를 삭제하는 기능
public void deleteBookByNo(int no) throws Exception {
String sql = "delete from books where book_no = ?";
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, no);
int rs = pstmt.executeUpdate();
System.out.println(rs + "개의 행이 삭제되었습니다.");
pstmt.close();
conn.close();
}
// 책 정보를 전달받아서 변경하는 기능
public void updateBook(int no, String title, String author, String publisher, int price, String pubdate) throws Exception {
String sql = "update books set book_title = ? , book_author = ?, book_publisher = ?, book_price = ?, book_pubdate = ? where book_no = ?";
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, title);
pstmt.setString(2, author);
pstmt.setString(3, publisher);
pstmt.setInt(4, price);
pstmt.setString(5, pubdate);
pstmt.setInt(6, no);
int rs = pstmt.executeUpdate();
System.out.println(rs + "개의 행이 변경되었습니다.");
pstmt.close();
conn.close();
}
}
package db.demo2;
import java.util.Scanner;
public class BookApp {
public static void main(String[] args) throws Exception {
Scanner scanner = new Scanner(System.in);
BookService service = new BookService();
while (true) {
System.out.println("1. 전체 조회 2. 제목 검색 3. 책 등록 4. 책 삭제 5. 책 정보 변경 0. 종료");
System.out.println("메뉴 선택> ");
int selectNo = scanner.nextInt();
if (selectNo == 1) {
// 모든 책 정보를 조회해서 출력한다.
service.selectAllBooks();
} else if (selectNo == 2) {
// 제목을 입력받아서 제목을 포함하고 있는 책 정보를 조회해서 출력한다.
System.out.println("조회할 책 제목 입력> ");
String keyword = scanner.next();
service.selectBooksByTitle(keyword);
} else if (selectNo == 3) {
// 번호, 제목, 저자, 출판사, 가격, 출판일을 입력받아서 DB에 저장한다.
System.out.println("저장할 책 번호 입력>");
int no = scanner.nextInt();
System.out.println("저장할 책 제목 입력>");
String title = scanner.next();
System.out.println("저장할 책 저자 입력>");
String author = scanner.next();
System.out.println("저장할 책 출판사 입력>");
String publisher= scanner.next();
System.out.println("저장할 책 가격 입력>");
int price = scanner.nextInt();
System.out.println("저장할 책 출판일 입력>");
String pubdate = scanner.next();
service.insertNewBook(no, title, author, publisher, price, pubdate);
} else if (selectNo == 4) {
// 책 번호를 입력받아서 책 정보를 삭제한다.
System.out.println("삭제할 책 번호 입력> ");
int no = scanner.nextInt();
service.deleteBookByNo(no);
} else if (selectNo == 5) {
// 수정할 책 번호, 변경할 정보(제목, 저자, 출판사, 가격, 출판일)을 입력받아서
// 책 번호에 해당하는 책 정보를 변경한다.
System.out.println("변경할 책 번호 입력> ");
int no = scanner.nextInt();
System.out.println("변경할 책 제목 입력> ");
String title = scanner.next();
System.out.println("변경할 책 저자 입력> ");
String author = scanner.next();
System.out.println("변경할 책 출판사 입력> ");
String publisher = scanner.next();
System.out.println("변경할 책 가격 입력> ");
int price = scanner.nextInt();
System.out.println("변경할 책 출판일 입력> ");
String pubdate = scanner.next();
service.updateBook(no, title, author, publisher, price, pubdate);
} else if (selectNo == 0) {
System.out.println("종료");
break;
}
}
}
}
demo1
2019. 6. 10. 09:47
package db.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class DeleteDemo {
public static void main(String[] args) throws Exception {
String sql = "delete from user_contacts where user_name = ?";
Class.forName("oracle.jdbc.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "ㅁ마ㅁㅁ마");
int rowCount = pstmt.executeUpdate();
System.out.println(rowCount + "개의 행이 삭제되었습니다.");
pstmt.close();
connection.close();
}
}
package db.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class InsertDemo1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Scanner scanner = new Scanner(System.in);
System.out.println("주소록 등록 프로그램");
System.out.println("순번입력> ");
int no = scanner.nextInt();
System.out.println("이름입력> ");
String name = scanner.next();
System.out.println("연락처입력> ");
String phone = scanner.next();
// 1. JDBC 드라이버 메모리 로딩
Class.forName("oracle.jdbc.OracleDriver");
// 2. Database와 연결을 유지하는 Connection 객체 획득하기
String url1 = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "hr";
String password = "zxcv1234";
Connection connection = DriverManager.getConnection(url1, user, password);
// 3. 실행할 쿼리 정의
String sql = "insert into user_contacts"
+ "(user_no, user_name, user_phone, user_create_date)"
+ "values"
+ "(?, ?, ?, sysdate)";
// 4. 쿼리를 데이터베이스로 전송하는 PreparedStatement 객체 획득하기
PreparedStatement pstmt = connection.prepareStatement(sql);
// 5. PreparedStatement의 setXXX(인덱스, 값) 메소드를 사용해서 ?와 치환될 값 설정하기
pstmt.setInt(1, no);
pstmt.setString(2, name);
pstmt.setString(3, phone);
// 6. 쿼리 실행
int rowCount = pstmt.executeUpdate();
System.out.println(rowCount + "개의 행이 추가되었습니다.");
// 7. 자원 해제 (필수!, 자원 해제는 역순으로!)
pstmt.close();
connection.close();
}
}
package db.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class SelectDemo {
public static void main(String[] args) throws Exception {
String sql = "select * from user_contacts";
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs= pstmt.executeQuery();
while (rs.next()) {
int no = rs.getInt("user_no");
String name = rs.getString("user_name");
String phone = rs.getString("user_phone");
System.out.println(no + ", " + name + ", " + phone);
}
rs.close();
pstmt.close();
conn.close();
}
}
package db.demo1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;
public class UpdateDemo {
public static void main(String[] args) throws Exception {
String sql = "update user_contacts "
+ "set "
+ "user_name = ?, "
+ "user_phone = ? "
+ "where user_no = ? ";
Scanner scanner = new Scanner(System.in);
System.out.println("수정할 사용자 번호 입력> ");
int no = scanner.nextInt();
System.out.println("새 이름 입력> ");
String name = scanner.next();
System.out.println("새 연락처 입력> ");
String phone = scanner.next();
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setString(2, phone);
pstmt.setInt(3, no); // 3은 열 번호. 5 입력시 부적절한 인덱스라며 에러
int rowCount = pstmt.executeUpdate();
System.out.println(rowCount + "개의 행이 변경되었습니다.");
pstmt.close();
conn.close();
}
}