package com.lh.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.lh.bean.Message;

public class MessageDao {
	private static MessageDao instance = null;
	private static Connection conn = null;
	private final static String DRIVER = "com.mysql.jdbc.Driver";
	private final static String URL = "jdbc:mysql://localhost:3306/db_database12";
	private final static String USER = "root";
	private final static String PASSWORD = "root";
	public static MessageDao getInstance(){
		if(instance==null){
			instance = new MessageDao();
		}
		return instance;
	}
	public static Connection getConn(){
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		return conn;
	}
	 public void saveMsg(Message msg){
		 Connection conn = null;
		 PreparedStatement stmt = null;
		 try{
			 conn = getConn();
			 String sql = "insert into tb_message(user,message,time) values(?,?,?);";
			 stmt = conn.prepareStatement(sql);
			 stmt.setString(1, msg.getUser());
			 stmt.setString(2, msg.getMsg());
			 stmt.setLong(3, msg.getTime());
			 stmt.executeUpdate();
			 stmt.close();
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 try {
				 stmt.close();
				 conn.close();
			} catch (SQLException e) {	
				e.printStackTrace();
			}
			
		 }
	 }
	 public Message getMsg(long time){
		 Connection conn = null;
		 PreparedStatement stmt = null;
		 Message msg =null;
		 try{
			 conn = getConn();
			 String sql = "select * from tb_message where time=?;";
			 stmt = conn.prepareStatement(sql);
			 stmt.setLong(1, time);
			 ResultSet rs = stmt.executeQuery();
			 
			 if(rs.next()){
				 msg = new Message();
				 msg.setId(rs.getInt(1));
				 msg.setUser(rs.getString(2));
				 msg.setMsg(rs.getString(3));
				 msg.setTime(rs.getLong(4));
			 }
			 stmt.close();
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 try {
				 stmt.close();
				 conn.close();
			} catch (SQLException e) {	
				e.printStackTrace();
			}
			
		 }
		 return msg;
	 }
	 public int selectMsgCount(){
		 Connection conn = null;
		 PreparedStatement stmt = null;
		 int rows = 0;
		 try{
			 conn = getConn();
			 String sql = "select count(id) from tb_message;";
			 stmt = conn.prepareStatement(sql);
			 ResultSet rs = stmt.executeQuery();
			 if(rs.next()){
				 rows = rs.getInt(1);
			 }
			 rs.close();
			 stmt.close();
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 try {
				 stmt.close();
				 conn.close();
			} catch (SQLException e) {	
				e.printStackTrace();
			}
			
		 }
		 return rows;
	 }
	 public void deleteMsg(int rows){
		 Connection conn = null;
		 PreparedStatement stmt = null;
		 try{
			 conn = getConn();
			 String delSql = "delete from tb_message limit ?";
			 stmt = conn.prepareStatement(delSql);
			 stmt.setInt(1, rows-10);
			 stmt.executeUpdate();
			 stmt.close();
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 try {
				 stmt.close();
				 conn.close();
			} catch (SQLException e) {	
				e.printStackTrace();
			}
			
		 }
	 }
	 public boolean isHasMsgByUserAndText(String user,String text){
		 Connection conn = null;
		 PreparedStatement stmt = null;
		 boolean res = false;
		 try{
			 conn = getConn();
			 String sql = "select id from tb_message where user = ? and message = ?;";
			 stmt = conn.prepareStatement(sql);
			 stmt.setString(1, user);
			 stmt.setString(2, text);
			 ResultSet rs =  stmt.executeQuery();
			 if(rs.next()){
				res = true;
			 }
			 rs.close();
			 stmt.close();
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 try {
				 stmt.close();
				 conn.close();
			} catch (SQLException e) {	
				e.printStackTrace();
			}	
		 }
		 return res;
	 }
	 public List<Message> getMsg(){
		 List<Message> list = new ArrayList<Message>();
		 Connection conn = null;
		 PreparedStatement stmt = null;
		 try{
			 conn = getConn();
			 String sql = "select * from tb_message order by id;";
			 stmt = conn.prepareStatement(sql);	
			 ResultSet rs = stmt.executeQuery();
			 while(rs.next()){
				 Message msg = new Message();
				 msg.setId(rs.getInt(1));
				 msg.setUser(rs.getString(2));
				 msg.setMsg(rs.getString(3));
				 msg.setTime(rs.getLong(4));
				 list.add(msg);
			 }
			 rs.close();
			 stmt.close();
		 }catch(Exception ex){
			 ex.printStackTrace();
		 }finally{
			 try {
				 stmt.close();
				 conn.close();
			} catch (SQLException e) {	
				e.printStackTrace();
			}
			
		 }
		 return list;
	 }
}
