반응형
package demo1;
import java.sql.*;
public class InsertDemo {
public static void main(String[] args) throws Exception {
//자바에서 database access하기 0. jdbc 드라이버(ojdbc6.jar)를 project에 추가
//1. 자바 어플리케이션에서 jdbc 드라이버를 등록하기
//- db access에 필요한 클래스 파일(oraclejdbcOracleDriver)을 메모리로 로딩하기
//OracleDrive.class에 정의되어 있음 -> 네트웍 기능 및 기타기능이 정의되어 있음 이놈을 제일먼저로딩해야한다.
//jdbc드라이버 로딩하기 Class.forname = 클래스이름을 적어주면 자동으로 로딩해준다. Class.forName("로딩할 클래스의 전체 경로")
Class.forName ("oracle.jdbc.OracleDriver" );//new OracleDriver(); //같은의미임
//2. Connection 객체 만들기
//- Connection은 database의 연결을 담당하는 객체다.
//DriverManager.getConnection(" url", "username", "password")메소드를 이용한다.
String url = "jdbc:oracle:thin:@localhost:1521:xe" ;// db마다 정해진 url이 다다르다 그러므로 무조건 보고해야함.
//oracle에서 재공하는 thin클래스를 이용해서 내호스트 1521포트로 연결하고싶엉
String user = "hr";
String password = "zxcv1234";
Connection con = DriverManager. getConnection( url, user, password);
//3. Statement 객체 만들기
// - Statement는 SQL의 전송과 실행을 담당하는 객체다.
// - 2번에서 획득한 Connection 객체의 createStatement() 메소드를 이용한다.
//sql을 db로 전송해준다. Statement객체 만들기
Statement stmt = con.createStatement();
//4. SQL을 db 로 전송하기
//- 3번에서 획득한 Statement객체의 excuteUpdate(SQL)메소드를 이용한다.
String sql = "INSERT INTO dept2(dcode, dname, pdept, area)" + "VALUES(1013, '제주영업팀', 1007, '제주지사')" ;
int result = stmt.executeUpdate( sql);
System. out.println( result + "개의 행이 추가되었습니다.");
//5. 연결해제하기
stmt.close();
con.close();
}
}
package demo1;
import java.sql.*;
public class DeleteDemo {
public static void main(String[] args) throws Exception {
Class. forName( "oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe" ;
String user = "hr";
String password = "zxcv1234";
Connection con = DriverManager. getConnection( url, user, password);
Statement stme = con.createStatement();
String sql = "DELETE FROM dept2 WHERE DCODE = '1013'";
int result = stme.executeUpdate( sql);
System. out.println( result + "개의 행이 삭제되었습니다.");
stme.close();
con.close();
}
}
package demo2.book;
import java.awt.EventQueue;
import java.awt.TextArea;
import javax.swing.JFrame;
import javax.swing.JLabel;
import java.awt.Font;
import javax.swing.JOptionPane;
import javax.swing.JTextField;
import javax.swing.JButton;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class BookMgr {
private JFrame frame;
private JTextField noField;
private JTextField titleField;
private JTextField authorField;
private JTextField publisherstextField;
private JTextField priceField;
private JTextField pubdatafield;
private JTextField textField;
/**
* Launch the application.
*/
public static void main(String[] args) {
EventQueue. invokeLater( new Runnable() {
public void run() {
try {
BookMgr window = new BookMgr();
window. frame.setVisible( true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}
/**
* Create the application.
*/
public BookMgr() {
initialize();
}
/**
* Initialize the contents of the frame.
*/
private void initialize() {
frame = new JFrame();
frame.setBounds(100, 100, 332, 340);
frame.setDefaultCloseOperation(JFrame. EXIT_ON_CLOSE );
frame.getContentPane().setLayout( null);
JLabel lblNewLabel = new JLabel("\uCC45\uB4F1\uB85D" );
lblNewLabel.setFont( new Font( "굴림" , Font.BOLD , 18));
lblNewLabel.setBounds(131, 10, 77, 33);
frame.getContentPane().add( lblNewLabel);
JLabel label = new JLabel("\uC77C\uB828\uBC88\uD638" );
label.setBounds(22, 53, 55, 15);
frame.getContentPane().add( label);
JLabel label_1 = new JLabel( "\uC81C\uBAA9");
label_1.setBounds(22, 78, 44, 15);
frame.getContentPane().add( label_1);
JLabel label_2 = new JLabel( "\uC800\uC790");
label_2.setBounds(22, 106, 44, 15);
frame.getContentPane().add( label_2);
JLabel label_3 = new JLabel( "\uCD9C\uD310\uC0AC" );
label_3.setBounds(22, 131, 44, 15);
frame.getContentPane().add( label_3);
JLabel label_4 = new JLabel( "\uAC00\uACA9");
label_4.setBounds(22, 156, 44, 15);
frame.getContentPane().add( label_4);
JLabel label_5 = new JLabel( "\uCD9C\uD310\uC77C" );
label_5.setBounds(22, 181, 44, 15);
frame.getContentPane().add( label_5);
JLabel label_6 = new JLabel( "\uC124\uBA85");
label_6.setBounds(22, 206, 44, 15);
frame.getContentPane().add( label_6);
noField = new JTextField();
noField.setBounds(89, 53, 200, 15);
frame.getContentPane().add( noField);
noField.setColumns(10);
titleField = new JTextField();
titleField.setColumns(10);
titleField.setBounds(89, 78, 200, 15);
frame.getContentPane().add( titleField);
authorField = new JTextField();
authorField.setColumns(10);
authorField.setBounds(89, 106, 200, 15);
frame.getContentPane().add( authorField);
publisherstextField = new JTextField();
publisherstextField.setColumns(10);
publisherstextField.setBounds(89, 131, 200, 15);
frame.getContentPane().add( publisherstextField);
priceField = new JTextField();
priceField.setColumns(10);
priceField.setBounds(89, 156, 200, 15);
frame.getContentPane().add( priceField);
pubdatafield = new JTextField();
pubdatafield.setColumns(10);
pubdatafield.setBounds(89, 181, 200, 15);
frame.getContentPane().add( pubdatafield);
JButton btnNewButton = new JButton( "\uB4F1\uB85D");
btnNewButton.addActionListener( new ActionListener() {
public void actionPerformed(ActionEvent e) {
int no = Integer.parseInt( noField.getText());
String title = titleField.getText();
String author = authorField.getText();
String publisher = publisherstextField .getText();
int price = Integer.parseInt( priceField.getText());
String pubdate = pubdatafield.getText();
String summary = textField.getText();
try{
Class. forName( "oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe" ;
String user = "hr";
String password = "zxcv1234";
Connection con = DriverManager.getConnection( url, user, password);
//Statement stmt = con.createStatement();//connection이 연결되어 있는곳에sql을 전송
//PreparedStatement -> sql 인젝션을 완벽히 방어함 / SQL / 값
//String sql = "INSERT INTO sample_book (BOOK_NO, BOOK_TITLE, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_PRICE, BOOK_PUBDATE, BOOK_SUMMARY)"+
//"VALUES("+ no +", '" + title +"', '" + author +"', '" + publisher +"', " + price +", to_date('" + pubdate +"', ' yyyy-mm- dd'), '" + summary +"')";
String sql = "INSERT INTO sample_book "
+ "(BOOK_NO, BOOK_TITLE, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_PRICE, BOOK_PUBDATE, BOOK_SUMMARY)"
+ "VALUES"
+ "(?, ?, ?, ?, ?, to_date(?, 'yyyy-mm-dd'), ?)";
System. out.println( sql);
PreparedStatement pstmt = con.prepareStatement( sql);
//int result = stmt.executeUpdate(sql);
//System.out.println(result + "개의 행이 추가되었습니다.");
pstmt.setInt(1, no);
pstmt.setString(2, title);
pstmt.setString(3, author);
pstmt.setString(4, publisher);
pstmt.setInt(5, price);
pstmt.setString(6, pubdate);
pstmt.setString(7, summary);
System. out.println( pstmt.executeUpdate() + "개의 만화 등록이 완료 되었습니다.");
//executeQuery - ResultSet이란 객체가 얻어진다. 조회결과 쿼리//테이블의 형태가 들어있다.
//Result Set - 커서가 내장되어 있다. 커서가 위치한 행에서 값을 추출할수 있다. 커서를 이동시킬수 있다.
//첫번째 데이타 위에 커서가 있다라고 하면서 그렇게 만들어 놓았다고 한다.
//next() 실행결과가 반환 값이 있다면 true가 계속 나온다~ 값을 추출하기위한 메서드를 가지고 있다 - resultsetgetInt("cat_no"); / resultset.getString("cat_name");
pstmt.close();
con.close();
} catch(Exception ex){
ex.printStackTrace();
JOptionPane. showMessageDialog( null, "책 등록 실패" );
}
}
});
btnNewButton.setBounds(89, 269, 200, 23);
frame.getContentPane().add( btnNewButton);
textField = new JTextField();
textField.setColumns(10);
textField.setBounds(89, 206, 194, 53);
frame.getContentPane().add( textField);
}
}
package demo1;
import java.sql.*;
public class InsertDemo2 {
public static void main(String[] args) throws Exception{
String sql = "INSERT INTO sample_book "
+ "(BOOK_NO, BOOK_TITLE, BOOK_AUTHOR, BOOK_PUBLISHER, BOOK_PRICE, BOOK_PUBDATE, BOOK_SUMMARY)"
+ "VALUES"
+ "(?, ?, ?, ?, ?, to_date(?, 'yyyy-mm-dd'), ?)";
Class. forName( "oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:xe" ;
String user = "hr";
String password = "zxcv1234";
Connection con = DriverManager. getConnection( url, user, password);
PreparedStatement pstmt = con.prepareStatement( sql);
pstmt.setInt(1, 23);
pstmt.setString(2, "나루토" );
pstmt.setString(3, "일본의 누군가" );
pstmt.setString(4, "점프" );
pstmt.setInt(5, 6500);
pstmt.setString(6, "2012-11-24");
pstmt.setString(7, "닌자 관련 이야기를 다룬 만화" );
pstmt.executeQuery();
pstmt.close();
con.close();
//결과는 db에서 인서트 된것을 확인 할수 있다.
}
}
package demo2.book;
import java.sql.*;
public class SelectDemo {
public static void main(String[] args) throws Exception{
String sql = "SELECT * "
+ "FROM sample_book "
+ "ORDER BY book_no ASC " ;
Class. forName( "oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = con.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");
Date pubdate = rs.getDate( "book_pubdate");
System. out.printf( "%d, %s, %s, %s, %d, %s\n" , no, title, author, publisher, price,pubdate);
}
/**결과화면 __________________________________
* 1, 원피스, 오다이이치로, 점프, 6500, 2010 -01 -07
2, 블리치, 일본의 누군가, 챔프, 6700, 2015 -02 -08
4, 헌터x헌터, 일본의 누군가, 점프, 7500, 2011 -04 -30
23, 나루토, 일본의 누군가, 점프, 6500, 2012 -11 -24
*/
rs.close();
pstmt.close();
con.close();
}
}
package demo2.book;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
public class SelectDemo2 {
public static void main(String[] args) throws Exception {
//부서번호(10~270)를 입력하면 부서에 소속된 사원정보 출력
//사원번호, 이름, 직종아이디, 부서명 출력
Scanner s = new Scanner(System. in);
System. out.print( "원하는 부서번호 입력 (10~270) 10단위로 입력 하세요 : ");
int id = Integer. parseInt( s.next());
String sql = "SELECT E.EMPLOYEE_ID, E.LAST_NAME, S.DEPARTMENT_NAME "
+ "FROM EMPLOYEES E JOIN DEPARTMENTS S "
+ "ON E.DEPARTMENT_ID = S.DEPARTMENT_ID "
+ "WHERE E.department_id = ?" ;
Class. forName( "oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = con.prepareStatement( sql);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
//E.EMPLOYEE_ID, E.LAST_NAME, S.DEPARTMENT_NAME
int num = 1;
while( rs.next()){
int employeeId = rs.getInt( "employee_id");
String name = rs.getString( "last_name");
String deptName = rs.getString( "department_name" );
System. out.printf( "%d\t%d\t%s\t%s\n" , num , employeeId, name, deptName);
num++;
}
rs.close();
pstmt.close();
con.close();
}
}
package demo2.book;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class SelectDemo3 {
public static void main(String[] args) throws Exception {
String sql = "select * from sample_book" ;
Class. forName( "oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
PreparedStatement pstmt = con.prepareStatement( sql);
ResultSet rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for( int i=1; i<= columnCount; i++){
System. out.print( rsmd.getColumnName( i)+ "\t");
}
System. out.println();
while( rs.next()){
for( int i=1; i<= columnCount; i++){
//각컬럼의 타입 알아내기
String columnType = rsmd.getColumnTypeName( i);
if( columnType.startsWith( "NUMBER")){
int value = rs.getInt( i);
System. out.print( value+ "\t");
} else if( columnType.startsWith( "VARCHAR")){
String value = rs.getString( i);
System. out.print( value+ "\t");
} else if( columnType.startsWith( "VARCHAR")){
Date value = rs.getDate( i);
System. out.print( value+ "\t");
}
System. out.println();
}
}
rs.close();
pstmt.close();
con.close();
/**
* BOOK_NO BOOK_TITLE BOOK_AUTHOR BOOK_PUBLISHER BOOK_PRICE BOOK_PUBDATE BOOK_SUMMARY
23
나루토
일본의 누군가
점프
6500
닌자 관련 이야기를 다룬 만화
2
블리치
일본의 누군가
챔프
6700
악마와 싸우는 만화
1
원피스
오다이이치로
점프
6500
최고의 만화입니다.
4
헌터x헌터
일본의 누군가
점프
7500
헌터가 되기위해서 수련하는 만화
*/
}
}
package demo2.book;
import java.awt.EventQueue;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextField;
import javax.swing.JButton;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Bookmgr2 {
private JFrame frame;
private JTextField noFiled;
private JTextField titleField;
private JTextField authorField;
private JTextField publishersField;
private JTextField priceField;
private JTextField pubdateFiled;
/**
* Launch the application.
*/
public static void main(String[] args) {
EventQueue. invokeLater( new Runnable() {
public void run() {
try {
Bookmgr2 window = new Bookmgr2();
window. frame.setVisible( true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}
/**
* Create the application.
*/
public Bookmgr2() {
initialize();
}
/**
* Initialize the contents of the frame.
*/
private void initialize() {
JTextArea textArea = new JTextArea();
frame = new JFrame();
frame.setBounds(100, 100, 372, 251);
frame.setDefaultCloseOperation(JFrame. EXIT_ON_CLOSE );
frame.getContentPane().setLayout( null);
JLabel lblNewLabel = new JLabel("\uCC45\uBC88\uD638" );
lblNewLabel.setBounds(12, 10, 46, 15);
frame.getContentPane().add( lblNewLabel);
JLabel label = new JLabel( "\uC81C\uBAA9");
label.setBounds(12, 35, 33, 15);
frame.getContentPane().add( label);
JLabel label_1 = new JLabel( "\uC800\uC790");
label_1.setBounds(12, 60, 33, 15);
frame.getContentPane().add( label_1);
JLabel label_2 = new JLabel( "\uCD9C\uD310\uC0AC" );
label_2.setBounds(12, 85, 46, 15);
frame.getContentPane().add( label_2);
JLabel label_3 = new JLabel( "\uAC00\uACA9");
label_3.setBounds(12, 110, 40, 15);
frame.getContentPane().add( label_3);
JLabel label_4 = new JLabel( "\uCD9C\uD310\uC77C" );
label_4.setBounds(12, 135, 46, 15);
frame.getContentPane().add( label_4);
JLabel label_5 = new JLabel( "\uC124\uBA85");
label_5.setBounds(12, 163, 33, 15);
frame.getContentPane().add( label_5);
noFiled = new JTextField();
noFiled.setBounds(64, 10, 200, 15);
frame.getContentPane().add( noFiled);
noFiled.setColumns(10);
titleField = new JTextField();
titleField.setColumns(10);
titleField.setBounds(64, 35, 200, 15);
frame.getContentPane().add( titleField);
authorField = new JTextField();
authorField.setColumns(10);
authorField.setBounds(64, 60, 200, 15);
frame.getContentPane().add( authorField);
publishersField = new JTextField();
publishersField.setColumns(10);
publishersField.setBounds(64, 85, 200, 15);
frame.getContentPane().add( publishersField);
priceField = new JTextField();
priceField.setColumns(10);
priceField.setBounds(64, 110, 200, 15);
frame.getContentPane().add( priceField);
pubdateFiled = new JTextField();
pubdateFiled.setColumns(10);
pubdateFiled.setBounds(64, 135, 200, 15);
frame.getContentPane().add( pubdateFiled);
JButton btnNewButton = new JButton( "\uAC80\uC0C9");
btnNewButton.addActionListener( new ActionListener() {
public void actionPerformed(ActionEvent e) {
String sql = "SELECT book_no, book_title, book_author, book_publisher, book_price, book_pubdate, book_summary "
+ "FROM sample_book "
+ "WHERE book_no = ? " ;
try{
Class. forName( "oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe", "hr", "zxcv1234");
System. out.println( sql);
PreparedStatement pstmt = con.prepareStatement( sql);
pstmt.setInt(1, Integer.parseInt( noFiled.getText()));
ResultSet rs = pstmt.executeQuery();
while( rs.next()){
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");
String summary = rs.getString( "book_summary");
titleField.setText( title);
authorField.setText( author);
publishersField.setText( publisher);
priceField.setText(Integer. toString( price));
pubdateFiled.setText(String. valueOf( pubdate));
textArea.setText( summary);
}
} catch(Exception ex){
ex.printStackTrace();
JOptionPane. showMessageDialog( null, "책 검색 실패" );
}
}
});
btnNewButton.setBounds(276, 6, 68, 23);
frame.getContentPane().add( btnNewButton);
JScrollPane scrollPane = new JScrollPane();
scrollPane.setBounds(64, 242, 200, -77);
frame.getContentPane().add( scrollPane);
textArea.setBounds(64, 159, 200, 50);
frame.getContentPane().add( textArea);
}
}
반응형
