/*
 * MessageDB.java
 *
 * Created on den 10 september 2001, 14:48
 */

package ip1.u6;

import java.util.*;
import java.sql.*;
/**
 *
 * @author  h&m
 * @version 
 */
public class MessageDB {
    private java.sql.Connection connection;
    
    final private String driver = "twz1.jdbc.mysql.jdbcMysqlDriver";
    final private String url = "jdbc:z1MySQL://atlas.dsv.su.se/db_01_henrik_j";
    final private String usr = "db_01_henrik_j";
    final private String pw = "274758";
    
    /** Creates new MessageDB */
    public MessageDB() {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, usr, pw);
        } catch(ClassNotFoundException e) {
            System.err.println(e.getMessage());
        } catch(SQLException e) {
            System.out.println("MessageDB - SQLException");
            System.err.println(e.getMessage());
        }
    }
    
    private void createDB() {
        try {
            Statement statement = connection.createStatement();
            String query = "CREATE TABLE messages(time TIMESTAMP, name VARCHAR(30), email VARCHAR(40), web VARCHAR(40), comment varchar(255))";
            statement.execute(query);
        } catch(SQLException e) {
            System.out.println("createDB - SQLException");
            System.err.println(e.getMessage());
        }        
    }
    
    public Collection getMessages() {
        try {
            Statement statement = connection.createStatement();
            String query = "SELECT * FROM messages";
            ResultSet result = statement.executeQuery(query);
            Collection msg = new TreeSet();
            while(result.next()) {
                Message m = new Message(result.getTimestamp(1), result.getString(2), result.getString(3), result.getString(4), result.getString(5));
                msg.add(m);
            }
            return msg;
        } catch(SQLException e) {
            System.out.println("getMessages - SQLException");
            System.err.println(e.getMessage());
            createDB();
        }
        return null;
    }
    
    public void addMessage(Message msg) {
        try {
            Statement statement = connection.createStatement();
            String query = "INSERT INTO messages VALUES ('"+msg.getTime()+"', '"+msg.getName()+
                "', '"+msg.getMail()+"', '"+msg.getWebpage()+"', '"+msg.getComment()+"')";
            statement.execute(query);
        } catch(SQLException e) {
            System.err.println(e.getMessage());
        } 
    }
}