[35] JDBC

 

* JDBC
- 자바에서 DB연동하는 것
- 데이터베이스를 설치하고 (오라클, My-SQL)
- 자바와 DB를 연결시켜야 한다->드라이버 필요

 

[MySQL 드라이버 다운]

- http://www.mysql.com -> Downloads
   -> Mysql Connectors -> Connector/J
   -> mysql-connector-java-5.1.18.zip 다운받은 후 압축풀기

- My-SQL서버 시작
   D:\ERPJava_04\mysql\bin\mysqld.exe 실행
   My-Sql 서버가 시작된는지 확인하려면
   작업관리자->프로세스 에서 확인 가능

 

- My-SQL서버 종료
   작업관리자->프로세스 에서 종료

- D:\ERPJava_04\mysql\bin>mysql -u root -p 1234
- mysql>show databases; --데이터베이스 보기
- mysql>use erpjavadb;    --사용DB 변경하기
- mysql>exit;                  --mysql 빠져 나오기

 

 

* 이클립스에서 My-SQL 드라이버 지정--------------------

- 이클립스 -> Project -> Properties -> Java Build Path
   -> Libraries -> Add External Jars...
   -> 'mysql-connector-java-5.1.18-bin.jar' 지정

 

* 성적테이블 작성------------------------------------------

create table tb_sungjuk(
  sno  int  NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,uname varchar(20) not null
  ,kor tinyint default 0
  ,eng tinyint default 0
  ,mat tinyint default 0
  ,aver tinyint null
  ,address varchar(10) null
  ,regdt     DATETIME       NOT NULL

);

int  정수형
tinyint 작은 정수형
DATETIME 날짜형
AUTO_INCREMENT 자동으로 일련번호 부여
PRIMARY KEY 기본키, 중복되는 값이 절대로 올수 없다
now() 현재 시스템의 날짜값

 

insert into tb_sungjuk(uname,kor,eng,mat,address,regdt)
values ('홍길동',70,80,90,'안양',now());

 

-------------------------------------------------------INSERT

package jdbc;

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

public class InsertTable {
    public static void main(String[] args) {
        String url="jdbc:mysql://localhost:3306/erpjavadb?useUnicode=true&characterEncoding=euckr";
        String jdbc = "org.gjt.mm.mysql.Driver";
        String user = "root";
        String password = "1234";
       
        Connection con=null;
        Statement stmt=null;
       
        try{
            Class.forName(jdbc);
            con=DriverManager.getConnection(url, user, password);
            stmt=con.createStatement();
           
            String sql="INSERT INTO tb_sungjuk";
            sql+=" (uname,kor,eng,mat,address,regdt)";
            sql+=" VALUES('홍길동',90,90,80,'안양',now())";
           
            int rec=stmt.executeUpdate(sql);//쿼리문이 실행
            System.out.println("레코드 "+rec+"개 추가!!");         
           
           
        }catch(Exception e){
            System.out.println("SQLException: " + e.getMessage());
        }finally{
            try{
                if ( stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if ( con != null){ con.close(); }
            }catch(Exception e){}          
        }//end try
    }

}

-------------------------------------------------------UPDATE

package jdbc;

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

public class UpdateTable {
    public static void main(String[] args) {
        String url="jdbc:mysql://localhost:3306/erpjavadb?useUnicode=true&characterEncoding=euckr";
        String driver = "org.gjt.mm.mysql.Driver";
        String user = "root";
        String password = "1234";
       
        Connection con=null;
        Statement stmt=null;
       
        try{
            Class.forName(driver);
            con=DriverManager.getConnection(url,user,password);
           
            String sql="UPDATE tb_sungjuk";
            sql=sql+" SET aver=(kor+eng+mat)/3 ";      
                    
            System.out.print(sql);
           
            //DB에 가서 새롭게 명령어(쿼리문)을 인식함
            stmt=con.createStatement();
            stmt.executeUpdate(sql);
            //select -> executeQuery()
            //나머지(create,insert,update,delete)->executeUpdate();      
                       
        }catch(Exception e){
            System.out.println("SQLException: " + e.getMessage());
        }finally{
            try{
                if ( stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if ( con != null){ con.close(); }
            }catch(Exception e){}          
        }//end try

    }

}

-------------------------------------------------------DELETE

package jdbc;

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

public class DeleteTable {
    public static void main(String[] args) {
        String driver="org.gjt.mm.mysql.Driver";
        String url="jdbc:mysql://localhost:3306/erpjavadb?useUnicode=true&characterEncoding=euckr";
        String user = "root";
        String password = "1234";
       
        Connection con=null;
        Statement stmt=null;
       
        try{
            Class.forName(driver);
            con=DriverManager.getConnection(url,user,password);
           
            String sql="DELETE FROM tb_sungjuk WHERE sno=1";
            stmt=con.createStatement();
            stmt.executeUpdate(sql);//쿼리문 실행
           
        }catch(Exception e){
            System.out.println("SQLException: " + e.getMessage());
        }finally{
            try{
                if ( stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if ( con != null){ con.close(); }
            }catch(Exception e){}          
        }//end try
       
    }
}

-------------------------------------------------------CREATE

package jdbc;

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

public class CreateTable {

    public static void main(String[] args) {
        String driver="org.gjt.mm.mysql.Driver";
        String url="jdbc:mysql://localhost:3306/erpjavadb?useUnicode=true&characterEncoding=euckr";
        String user = "root";
        String password = "1234";
       
        Connection con=null;
        Statement stmt=null;
       
        try{
            Class.forName(driver);//mysql 연결통로 지정. 드라이버로딩
            con=DriverManager.getConnection(url,user,password);
           
            String sql="CREATE TABLE tb_sungjuk(";
            sql=sql+" sno   int  NOT NULL AUTO_INCREMENT PRIMARY KEY";
            sql=sql+",uname VARCHAR(20) not null";
            sql=sql+",kor   tinyint default 0";
            sql=sql+",eng   tinyint default 0";
            sql=sql+",mat   tinyint default 0";
            sql=sql+",aver  tinyint null";
            sql=sql+",address varchar(10) null";
            sql=sql+",regdt DATETIME NOT NULL";
            sql=sql+")";
            //sql+="~~~"
            //System.out.print(sql);
            stmt=con.createStatement();//새로운명령어라고 인식
            stmt.executeUpdate(sql);//쿼리문 실행
            //실행 executeUpdate->create,insert,delete,update,drop
            //실행 executeQuery->select
           
        }catch(Exception e){
            System.out.print(e.getMessage());
        }finally{
            try{
                if ( stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if ( con != null){ con.close(); }
            }catch(Exception e){}          
        }//end try

    }

}
-------------------------------------------------------SELECT

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectRecOne {
    public static void main(String[] args) {
        String driver="org.gjt.mm.mysql.Driver";
        String url="jdbc:mysql://localhost:3306/erpjavadb?useUnicode=true&characterEncoding=euckr";
        String user = "root";
        String password = "1234";
       
        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;
       
        try{
            Class.forName(driver);
            con=DriverManager.getConnection(url,user,password);
           
            String sql="SELECT * FROM tb_sungjuk order by sno";
           
            stmt=con.createStatement();
            rs=stmt.executeQuery(sql);//select결과값 저장
           
            //rs.next() 첫번째 레코드를 가르킴
            //만일 가르키는 레코드가 있으면 true반환
            //없으면 false반환
            if(rs.next())
            {
                System.out.print(rs.getInt(1)+" ");//1번째 칼럼 sno
                System.out.print(rs.getString(2)+" ");//uname
                System.out.print(rs.getInt(3)+" ");//kor
                System.out.print(rs.getInt(4)+" ");//eng
                System.out.print(rs.getInt(5)+" ");//mat
                System.out.print(rs.getInt(6)+" ");//aver
                System.out.print(rs.getString(7)+" ");//address
                System.out.print(rs.getString(8)+" ");//regdt
               
                System.out.println();
                System.out.print(rs.getInt("sno")+" ");
                System.out.print(rs.getString("uname")+" ");//uname
                System.out.print(rs.getInt("kor")+" ");//kor
                System.out.print(rs.getInt("eng")+" ");//eng
                System.out.print(rs.getInt("mat")+" ");//mat
                System.out.print(rs.getInt("aver")+" ");//aver
                System.out.print(rs.getString("address")+" ");//address
                System.out.print(rs.getString("regdt")+" ");//regdt
            }
            else
            {
                System.out.print("조회 행 없음!!");
            }
           
        }catch(Exception e){
            System.out.print(e.getMessage());
        }finally{
            try{
                if ( stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if ( con != null){ con.close(); }
            }catch(Exception e){}          
        }//end try

    }

}
-------------------------------------------------------SELECT

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectRec {
    public static void main(String[] args) {
        String driver="org.gjt.mm.mysql.Driver";
        String url="jdbc:mysql://localhost:3306/erpjavadb?useUnicode=true&characterEncoding=euckr";
        String user = "root";
        String password = "1234";
       
        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;
       
        try{
            Class.forName(driver);
            con=DriverManager.getConnection(url,user,password);
           
            String sql="SELECT * FROM tb_sungjuk order by sno";
           
            stmt=con.createStatement();
            rs=stmt.executeQuery(sql);//select결과값 저장
                       
            while(rs.next())
            {
                System.out.print(rs.getInt("sno")+" ");
                System.out.print(rs.getString("uname")+" ");//uname
                System.out.print(rs.getInt("kor")+" ");//kor
                System.out.print(rs.getInt("eng")+" ");//eng
                System.out.print(rs.getInt("mat")+" ");//mat
                System.out.print(rs.getInt("aver")+" ");//aver
                System.out.print(rs.getString("address")+" ");//address
                System.out.print(rs.getString("regdt")+" ");//regdt
                System.out.println();
            }          
        }catch(Exception e){
            System.out.print(e.getMessage());
        }finally{
            try{
                if ( rs != null){ rs.close(); }
            }catch(Exception e){}           
            try{
                if ( stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if ( con != null){ con.close(); }
            }catch(Exception e){}          
        }//end try
       

    }

}

-----------------------------------------------------[문제]

문)aver들의 평균보다 잘한 레코드 출력

 

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectEx1 {
    public static void main(String[] args) {
        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;
        try{
            Class.forName("org.gjt.mm.mysql.Driver");
            String url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=euckr";
            String user="root";
            String password="1234";
            con=DriverManager.getConnection(url,user,password);
            stmt=con.createStatement();

            //문)aver들의 평균보다 잘한 레코드 출력
            String sql="";
            sql=sql+" SELECT * FROM tb_sungjuk ";
            sql=sql+" WHERE aver>= ";
            sql=sql+"             (SELECT AVG(aver) FROM tb_sungjuk) ";

            rs=stmt.executeQuery(sql);

            while(rs.next()){
                System.out.print(rs.getInt("sno")+" ");
                System.out.print(rs.getString("uname")+" ");
                System.out.print(rs.getInt("kor")+" ");
                System.out.print(rs.getInt("eng")+" ");
                System.out.print(rs.getInt("mat")+" ");
                System.out.print(rs.getInt("aver")+" ");
                System.out.print(rs.getString("address")+" ");             
                System.out.print(rs.getString("regdt").substring(0,10)+" ");
                System.out.println();
            }
        }
        catch(Exception e){
            System.out.println(e);
        }
        finally{
            try{
                if (rs != null){ rs.close(); }
            }catch(Exception e){}           
            try{
                if (stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if (con != null){ con.close(); }
            }catch(Exception e){}  
        }

    }

}

-----------------------------------------------------[문제]

문)각 주소별 학생의 인원수 내림차순으로 출력

 

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectEx2 {
    public static void main(String[] args) {
        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;
        try{
            Class.forName("org.gjt.mm.mysql.Driver");
            String url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=euckr";
            String user="root";
            String password="1234";
            con=DriverManager.getConnection(url,user,password);
            stmt=con.createStatement();

            //문)aver들의 평균보다 잘한 레코드 출력
            String sql="";    
            sql=sql+" SELECT address,count(*) as cnt ";
            sql=sql+" FROM tb_sungjuk ";
            sql=sql+" GROUP BY address ";
            sql=sql+" ORDER BY count(*) DESC ";

            rs=stmt.executeQuery(sql);

            while(rs.next()){
                System.out.print(rs.getString("address")+" ");
                System.out.print(rs.getInt("cnt")+" ");
                System.out.println();
            }
        }
        catch(Exception e){
            System.out.println(e);
        }
        finally{
            try{
                if (rs != null){ rs.close(); }
            }catch(Exception e){}           
            try{
                if (stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if (con != null){ con.close(); }
            }catch(Exception e){}  
        }

    }

}

-----------------------------------------------------[문제]

문)sno 역순으로 정렬한뒤 위에서부터 5개 레코드만 출력

 

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectEx3 {

    public static void main(String[] args) {
        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;
        try{
            Class.forName("org.gjt.mm.mysql.Driver");
            String url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=euckr";
            String user="root";
            String password="1234";
            con=DriverManager.getConnection(url,user,password);
            stmt=con.createStatement();

            //문)
            String sql="";    
            sql=sql+" SELECT * ";
            sql=sql+" FROM tb_sungjuk ";
            sql=sql+" ORDER BY sno DESC limit 0,5 ";
           
            rs=stmt.executeQuery(sql);

            while(rs.next()){
                System.out.print(rs.getInt("sno")+" ");
                System.out.print(rs.getString("uname")+" ");
                System.out.print(rs.getInt("kor")+" ");
                System.out.print(rs.getInt("eng")+" ");
                System.out.print(rs.getInt("mat")+" ");
                System.out.print(rs.getInt("aver")+" ");
                System.out.print(rs.getString("address")+" ");             
                System.out.print(rs.getString("regdt").substring(0,10)+" ");
                System.out.println();
            }
        }
        catch(Exception e){
            System.out.println(e);
        }
        finally{
            try{
                if (rs != null){ rs.close(); }
            }catch(Exception e){}           
            try{
                if (stmt != null){ stmt.close(); }
            }catch(Exception e){}           
            try{
                if (con != null){ con.close(); }
            }catch(Exception e){}  
        }

    }

}