package dao;
import java.sql.*;import java.util.*;import JDBC.JDBC;import Message.Message;
/** * 只对数据库中的表message进行操作 * * @author Administrator * */public class messageDao {
private static Connection conn = null; private static Message messageInfo = null;
public messageDao() { JDBC j = new JDBC(); conn = j.getConnection(); messageInfo = new Message(); }
/** * 新增一条留言记录(成功) */
public int insertMessage(Connection conn, Message message) { String sql = "INSERT INTO message (id,title,context,auth,createTime,bak1) VALUES (?,?,?,?,?,?)"; String maxSql = "SELECT MAX(cast(id as unsigned)) FROM message"; ResultSet rs; PreparedStatement ps = null; String maxID = ""; int insertFlag = 0; Timestamp d = new Timestamp(System.currentTimeMillis());// 获取当前系统时间
// 账号的处理：如果没有账号则设置为0,如果有则取出最大的； try { ps = conn.prepareStatement(maxSql); rs = ps.executeQuery(); rs.next(); if (null == rs.getString(1)) { maxID = "0"; } else { maxID = rs.getString(1); }
ps = conn.prepareStatement(sql); ps.setString(1, "" + ((Double.parseDouble(maxID) + 1))); ps.setString(2, message.getTitle()); ps.setString(3, message.getContext()); ps.setString(4, message.getAuth()); ps.setTimestamp(5, d); ps.setString(6, message.getBak1());
insertFlag = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally {
if (null != ps) { try { ps.close(); ps = null; } catch (SQLException e) { e.printStackTrace(); } } }
return insertFlag;
}
/** * 删除一条留言记录 */
public int deleteMessage(Connection conn, String id) { int deleteFlag = 0; PreparedStatement ps = null; String sql = "DELETE FROM message WHERE id = ?"; try { ps = conn.prepareStatement(sql);
ps.setString(1, id);
deleteFlag = ps.executeUpdate();
} catch (SQLException e) { e.printStackTrace(); } finally { if (null != ps) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } }
} return deleteFlag;
}
/** * 修改一条留言记录 */
public int updateMessage(Connection conn, Message message) { int updateFlag = 0; PreparedStatement ps = null; Timestamp d = new Timestamp(System.currentTimeMillis());// 获取当前系统时间 String sql = "UPDATE message SET title = ?,context = ?,auth = ?,createTime = ?,bak1=? WHERE id = ?"; try { ps = conn.prepareStatement(sql); ps.setString(1, message.getTitle()); ps.setString(2, message.getContext()); ps.setString(3, message.getAuth()); ps.setTimestamp(4, d); ps.setString(5, message.getBak1()); ps.setString(6, message.getId()); updateFlag = ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } finally {
if (null != ps) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } }
return updateFlag;
}
/** * 查询所有留言记录（成功） */
public List<Message> selectMessage(Connection conn) { String sql = "SELECT id,title,context,auth,createTime,bak1 FROM message"; PreparedStatement ps = null; Message message = null; ResultSet rs = null; List<Message> list = new ArrayList<Message>(); try { ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while (rs.next()) { message = new Message(); message.setId(rs.getString("id")); message.setTitle(rs.getString("title")); message.setContext(rs.getString("context")); message.setAuth(rs.getString("auth")); message.setCreateTime(rs.getString("createTime")); message.setBak1(rs.getString("bak1")); list.add(message); }
} catch (SQLException e) { e.printStackTrace(); } finally { if (null != rs) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (null != ps) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } } return list; }
/** * 查询一条留言记录 */
public Message selectMessageSingle(Connection conn, String id) { String sql = "SELECT title,context,auth,createTime,bak1 FROM message WHERE id = ?"; PreparedStatement ps = null; Message message = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); ps.setString(1, id); rs = ps.executeQuery(); if (rs.next()) { message = new Message(); // message.setId(rs.getString("id")); message.setTitle(rs.getString("title")); message.setContext(rs.getString("context")); message.setAuth(rs.getString("auth")); message.setCreateTime(rs.getString("createTime")); message.setBak1(rs.getString("bak1")); }
} catch (SQLException e) { e.printStackTrace(); } finally { if (null != rs) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (null != ps) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } } return message; }
public static void main(String[] args) { messageDao m = new messageDao(); JDBC j = new JDBC(); Connection conn = j.getConnection(); System.out.println(m.deleteMessage(conn, "2.0"));// List<Message> list = m.selectMessage(conn); }
}
