태그

Wed/Jan/15 [Java] day12. Java-SQL-HTML 연습문제2

Wed/Jan/15

[Java] day12.

Java-SQL-HTML 연습문제2


[작업할 내용 정리]
  1. txt. 읽고 책제목/ 가격 / 이미지파일명 SQL에 저장
  2. 데이터 불러와서 total price 계산하고, HTML에 table로 출력
  3. 이미지 - 책제목 - 가격 순 출력

[구조짜기]
★bold&color = 자꾸 까먹는 부분&실수★
  1. JDBC connect
    1. build path - jdbc jar file
  2. Class
    1. MainClass
    2. FileReadWriteClass
      1. DB Connection
        1. Connection = null; // import Connection.class
        2. driveName / db_uri / db_id / db_pw
        3. Class.forName(driveName);
        4. DriverManager.getConnection(uri, id, pw); // import DriverManagerClass
        5. Exceptions
      2. new DB / table - SQL
        1. use testdb;
        2. create table books (name varchar(20), price varchar(10), imgname varchar(20));
          1. alter table 테이블명 modify 컬럼명 varchar(사이즈);
          2. name varchar size 잘못 정해서 error..
        3. desc books; // check
      3. FileReader / BufferedReader - txt
        1. FileReader fr = new FileReader(txt_uri);
        2. BufferedReader br = new BufferedReader(fr);
        3. lines = "";
        4. while(!(lines==null)) { lines=br.readLine(); String x[] = line.split(","); }
        5. br.close();
        6. Exceptions
      4. insert data into SQL
        1. Statement stmt = this.conn.createStatement
        2. int n = stmt.executeUpdate("insert into books value('x[0]', 'x[1]', 'x[2]')");
      5. ResultSet
        1. ResultSet =null;
          1. try 밖에서 초기화 해줘야 return!
        2. Statement stmt = executeQuery("select * from books");
        3. return ResultSet
        4. Exception
          1. +e.getMessage()
    3. HTMLReadWriteClass
      1. public static void writeHTML(ResultSet rs){}
        1. FileWriter / BufferedWriter - HTML
        2. bw.write(<"<html>">); ~~~ bw.write(<"</html>">);
        3. bw.write("Total Price : "+totalPrice);
        4. bw.write(<table><tr><td>Book Cover</td><td>Pricce</td><td>Title</td></tr>)
        5. rs.beforeFirst();
        6. while(rs.next()) { bw.write("<tr><td> rs.getString("name")</td> <td>rs.getString("price")</td> <td>rs.getString("imgname")</td> </tr>");  
        7. int totalPrice += Integer.paseInt(rs.getString("price"));}
          • Integer.paseInt() 문법!
        8. bw.close();
        9. bw.write(</table>)
        10. Exceptions  // 일단 여기까지!
      2. public static void readHTML() {}

[My Solution]

 MainClass








1



2



3



4



5



6



7



8



9



10



11



12



13



14



15



16



17



18



19



20



21



22



23







import java.sql.ResultSet;







public class MainClass {







    public static void main(String[] args) {







        FileReadWriteClass fc = new FileReadWriteClass();



        



        fc.connect();



        System.out.println("\n----------\n");



        



        //fc.data_toSQL();



        



        //System.out.println("\n----------\n"



        //                    + "Show Result Set");



        ResultSet rs = fc.showResult();



        



        System.out.println("\n----------\n");



        HTMLWriteReadClass.writeHTML(rs);







    }







}




cs
 FileReadWriteClass








1



2



3



4



5



6



7



8



9



10



11



12



13



14



15



16



17



18



19



20



21



22



23



24



25



26



27



28



29



30



31



32



33



34



35



36



37



38



39



40



41



42



43



44



45



46



47



48



49



50



51



52



53



54



55



56



57



58



59



60



61



62



63



64



65



66



67



68



69



70



71



72



73



74



75



76



77



78



79



80



81



82



83



84



85



86



87



88



89



90



91



92



93



94



95



96



97







import java.io.BufferedReader;



import java.io.FileNotFoundException;



import java.io.FileReader;



import java.io.IOException;



import java.sql.Connection;



import java.sql.DriverManager;



import java.sql.ResultSet;



import java.sql.SQLException;



import java.sql.Statement;







public class FileReadWriteClass {







    Connection conn = null;







    public FileReadWriteClass() {



    }







    public void connect() {



        String driveName = "com.mysql.jdbc.Driver";



        String db_url = "jdbc:mysql://localhost:3306/testdb";



        String db_id = "root";



        String db_pw = "1234";







        try {



            Class.forName(driveName);



            System.out.println("Success__Driver Connection_");







            conn = DriverManager.getConnection(db_url, db_id, db_pw);



            System.out.println("Success__DB Loading_");







        } catch (ClassNotFoundException e) {



            System.out.println("ERR : Driver Connection ERROR_" + e.getMessage());



        } catch (SQLException e) {



            System.out.println("ERR : DB Loading ERROR_" + e.getMessage());



        }







    } // connect()







    public void data_toSQL() {



        String txt_uri = "C:/Users/USER/Desktop/JAVA/book_data.txt";







        String line = "";



        try {



            FileReader fr = new FileReader(txt_uri);



            BufferedReader br = new BufferedReader(fr);







            Statement stmt = this.conn.createStatement();







            while (!((line = br.readLine()) == null)) {







                // System.out.println(line);







                String[] words = line.split(",");







                // System.out.println(words[0]+"\t"+words[1]+"\t"+words[2]);



                // System.out.println(words[0].length()+"\t"+words[1].length()+"\t"+words[2].length());







                int n = stmt.executeUpdate(



                        "insert into books value('" + words[0] + "', '" + words[1] + "', '" + words[2] + "')");



                // System.out.println("number of data input : "+ n );



            }







            br.close();







        } catch (FileNotFoundException e) {



            System.out.println("ERR__File doesn't EXIST_" + e.getMessage());



        } catch (SQLException e) {



            System.out.println("ERR__can't WRITE_" + e.getMessage());



        } catch (IOException e) {



            System.out.println("ERR__File can't be READ_" + e.getMessage());



        }







    } // data_toSQL







    public ResultSet showResult() {







        ResultSet rs = null;







        try {



            Statement stmt = this.conn.createStatement();



            rs = stmt.executeQuery("select * from books");







            // for check



            /*



            while (rs.next()) {



                System.out.println(rs.getString("name") + rs.getString("price") + rs.getString("imgname"));



            }



            */







        } catch (SQLException e) {



            System.out.println("ERR_Can't Create Statement");



        }







        return rs;



    } // showResult()







}




cs


HTMLWriteReadClass








1



2



3



4



5



6



7



8



9



10



11



12



13



14



15



16



17



18



19



20



21



22



23



24



25



26



27



28



29



30



31



32



33



34



35



36



37



38



39



40



41



42



43



44



45



46



47



48



49



50



51



52



53



54



55



56



57



58



59



60



61



62



63



64



65



66



67



68



69



70



71



72







import java.io.BufferedWriter;



import java.io.FileWriter;



import java.io.IOException;



import java.sql.ResultSet;



import java.sql.SQLException;







public class HTMLWriteReadClass {







    static String html_url = "C:/filetest/books.html";







    public HTMLWriteReadClass() {



    }







    public static void writeHTML(ResultSet rs) {







        try {



            FileWriter fw = new FileWriter(html_url);



            BufferedWriter bw = new BufferedWriter(fw);







            bw.write("<html>");



            bw.newLine();







            bw.write("<head><title> \"Hello, Books!\"</title></head>");



            bw.newLine();







            bw.write("<body bgcolor=#cc99ff>");



            bw.newLine();







            rs.beforeFirst();



            int sum = 0;



            while(rs.next()){



                sum += Integer.parseInt(rs.getString("price"));



            }



            bw.write("<h2>Total Price = " + sum+"</h2>");



            bw.newLine();



            



            bw.write("<table border=20 bgcolor=#f2e6ff> <tr> <th>number</th>    <th>Book Cover</th>    <th>Pricce</th>    <th>Title</th>    </tr>");



            bw.newLine();







            rs.beforeFirst();



            int i=1;



            while (rs.next()) {



                bw.write("<tr>"



                        + "<td>"    +i+ "</td>"



                        + "<td><img src=\"./booksimages/"+rs.getString("imgname")+"\"></td>"



                        + "<td>"    +rs.getString("price")+    "</td>"



                        + "<td>"    +rs.getString("name")+    "</td>"



                        + "</tr>");



                bw.newLine();



            }







            bw.write("</table>");



            bw.newLine();







            bw.write("</body>");



            bw.newLine();







            bw.write("</html>");



            bw.newLine();







            bw.close();



            



            System.out.println("Finish the file writing");



        } catch (IOException e) {



            System.out.println("ERR_Can't make the FILE__" + e.getMessage());



        } catch (SQLException e) {



            System.out.println("ERR_Can't Read Result Set__" + e.getMessage());



        }







    }







}




cs

라벨:

댓글

댓글 쓰기

가장 많이 본 글