package com.dao;
import java.sql.*;
import java.util.*;
import com.vo.Person;
public class PersonDAO implements PersonDAOImpl {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String url = "jdbc:mysql://localhost:3306/basicjsp?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";
String id = "jspid";
String pass = "jsppass";
String sql = "";
public void connectDB() {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url, id, pass);
}
catch (Exception e) {
e.printStackTrace();
}
}
@Override
public List<HashMap<String, Object>> selectAll() {
List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
try {
sql = "SELECT id, name, sex, age, DATE_FORMAT(date, '%Y-%m-%d') AS date, rcount FROM persons";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
connectDB();
HashMap<String, Object> params = new HashMap<String, Object>();
params.put("id", rs.getInt("id"));
params.put("name", rs.getString("name"));
params.put("sex", rs.getString("sex"));
params.put("age", rs.getInt("age"));
params.put("date", rs.getShort("date"));
params.put("rcount", rs.getInt("rcount"));
params.put("column", "id");
params.put("sort", "ASC");
list.add(params);
}
conn.close();
pstmt.close();
rs.close();
}
catch (Exception e) {
e.printStackTrace();
}
return list;
}
@Override
public List<HashMap<String, Object>> selectAllLimit(int offset, int recordsPerPage,
String sortItem, String sortMethod) {
List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
try {
connectDB();
sql = "SELECT id, name, sex, age, DATE_FORMAT(date, '%Y-%m-%d') AS date, rcount FROM persons ";
if (sortItem != null && sortItem.equals("id")) {
sql += "ORDER BY id ";
}
else if (sortItem != null && sortItem.equals("name")) {
sql += "ORDER BY name ";
}
else
sql += "ORDER BY age ";
if (sortMethod != null && sortMethod.equals("ASC")) {
sql += "ASC ";
}
else {
sql += "DESC ";
}
sql += "LIMIT " + offset + ", " + recordsPerPage;
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
HashMap<String, Object> params = new HashMap<String, Object>();
params.put("id", rs.getInt("id"));
params.put("name", rs.getString("name"));
params.put("sex", rs.getString("sex"));
params.put("age", rs.getInt("age"));
params.put("date", rs.getString("date"));
params.put("rcount", rs.getInt("rcount"));
params.put("offset", offset);
params.put("recordsPerPage", recordsPerPage);
params.put("sortItem", sortItem);
params.put("sortMethod", sortMethod);
list.add(params);
}
conn.close();
pstmt.close();
rs.close();
}
catch (Exception e) {
e.printStackTrace();
}
return list;
}
@Override
public Person selectById(int id) {
Person person = new Person();
try {
connectDB();
sql = "SELECT * FROM persons WHERE id = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, id);
rs = pstmt.executeQuery();
while (rs.next()) {
person.setId(rs.getInt(1));
person.setName(rs.getString(2));
person.setSex(rs.getString(3));
person.setAge(rs.getInt(4));
person.setDate(rs.getString(5));
person.setrCount(rs.getInt(6));
}
conn.close();
pstmt.close();
rs.close();
} catch (Exception e) { }
return person;
}
@Override
public int selectCount() {
int n = 0;
try {
connectDB();
String sql = "SELECT COUNT(*) FROM persons";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
rs.next();
n = rs.getInt(1);
pstmt.close();
rs.close();
} catch (Exception e) { }
return n;
}
@Override
public int insert(Person person) {
int id = -1;
sql = "INSERT INTO persons (name, sex, age, rcount) VALUES (?, ?, ?, 0)";
try {
connectDB();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, person.getName());
pstmt.setString(2, person.getSex());
pstmt.setInt(3, person.getAge());
pstmt.executeUpdate();
pstmt.close();
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}
return id;
}
@Override
public void update(Person person) {
sql = "UPDATE persons SET name = ? , sex = ?, age = ? WHERE id = ?";
try {
connectDB();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, person.getName());
pstmt.setString(2, person.getSex());
pstmt.setInt(3, person.getAge());
pstmt.setInt(4, person.getId());
pstmt.executeUpdate();
pstmt.close();
conn.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
@Override
public void updateCount(int id) {
sql = "UPDATE persons SET rcount = rcount + 1 WHERE id = ?";
try {
connectDB();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
pstmt.close();
conn.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
@Override
public void delete(int id) {
sql = "DELETE FROM persons WHERE id = ?";
try {
connectDB();
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
pstmt.close();
conn.close();
}
catch (Exception e) {
e.printStackTrace();
}
}
}
