JAVA实训第三天

学生类
package net.hw.student.dao.impl;

import net.hw.student.bean.Student;
import net.hw.student.dao.StudentDao;
import net.hw.student.dbutil.ConnectionManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

public class StudentDaolmpl implements StudentDao {
@Override
public int insert(Student student) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = “insert into t_student (id, name, sex, age, department, class, telephone)”
+ " values (?, ?, ?, ?, ?, ?, ?)";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, student.getId());
pstmt.setString(2, student.getName());
pstmt.setString(3, student.getSex());
pstmt.setInt(4, student.getAge());
pstmt.setString(5,student.getDepartment());
pstmt.setString(6, student.getClazz());
pstmt.setString(7, student.getTelephone());
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
@Override
public int deleteById(String id) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = “delete from t_student where id = ?”;
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1,id);
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
@Override
public int deleteByClass(String clazz) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = “delete from t_student where class = ?”;
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, clazz);
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
@Override
public int deleteByDepartment(String department) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = “delete from t_student where department = ?”;
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, department);
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
@Override
public int update(Student student) {
int count = 0;
Connection conn = ConnectionManager.getConnection();
String strSQL = “update t_student set name = ?, sex = ?, age = ?,”
+ " department = ?, class = ?, telephone = ? where id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setInt(3, student.getAge());
pstmt.setString(4, student.getDepartment());
pstmt.setString(5, student.getClazz());
pstmt.setString(6, student.getTelephone());
pstmt.setString(7, student.getId());
count = pstmt.executeUpdate();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return count;
}
@Override
public Student findById(String id) {
Student student = null;
Connection conn = ConnectionManager.getConnection();
String strSQL = “select * from t_student where id = ?”;
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
student = new Student();
student.setId(rs.getString(“id”));
student.setName(rs.getString(“name”));
student.setSex(rs.getString(“sex”));
student.setAge(rs.getInt(“age”));
student.setDepartment(rs.getString(“department”));
student.setClazz(rs.getString(“class”));
student.setTelephone(rs.getString(“telephone”));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return student;
}
@Override
public List findByName(String name) {
List students = new ArrayList();
Connection conn = ConnectionManager.getConnection();
String strSQL = “select * from t_student where name like ?”;
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, name + “%”);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setId(rs.getString(“id”));
student.setName(rs.getString(“name”));
student.setSex(rs.getString(“sex”));
student.setAge(rs.getInt(“age”));
student.setDepartment(rs.getString(“department”));
student.setClazz(rs.getString(“class”));
student.setTelephone(rs.getString(“telephone”));
students.add(student);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
@Override
public List findByClass(String clazz) {
List students = new ArrayList();
Connection conn = ConnectionManager.getConnection();
String strSQL = “select * from t_student where class like ?”;
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, clazz + “%”);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setId(rs.getString(“id”));
student.setName(rs.getString(“name”));
student.setSex(rs.getString(“sex”));
student.setAge(rs.getInt(“age”));
student.setDepartment(rs.getString(“department”));
student.setClazz(rs.getString(“class”));
student.setTelephone(rs.getString(“telephone”));
students.add(student);
}
rs.close();
pstmt.close();
;
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
@Override
public List findByDepartment(String department) {
List students = new ArrayList();
Connection conn = ConnectionManager.getConnection();
String strSQL = “select * from t_student where department like ?”;
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, department + “%”);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
Student student = new Student();
student.setId(rs.getString(“id”));
student.setName(rs.getString(“name”));
student.setSex(rs.getString(“sex”));
student.setAge(rs.getInt(“age”));
student.setDepartment(rs.getString(“department”));
student.setClazz(rs.getString(“class”));
student.setTelephone(rs.getString(“telephone”));
students.add(student);
}
rs.close();
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
@Override
public List findAll() {
List students = new ArrayList();
Connection conn = ConnectionManager.getConnection();
String strSQL = “select * from t_student”;
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
Student student = new Student();
student.setId(rs.getString(“id”));
student.setName(rs.getString(“name”));
student.setSex(rs.getString(“sex”));
student.setAge(rs.getInt(“age”));
student.setDepartment(rs.getString(“department”));
student.setClazz(rs.getString(“class”));
student.setTelephone(rs.getString(“telephone”));
students.add(student);
}
rs.close();
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return students;
}
@Override
public Vector findRowsBySex() {
Vector rows = new Vector();
Connection conn = ConnectionManager.getConnection();
String strSQL = “select sex as ‘性别’, count() as ‘人数’"
+ " from t_student group by sex order by sex desc";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
Vector currentRow = new Vector();
currentRow.addElement(rs.getString(“性别”));
currentRow.addElement(rs.getInt(“人数”) + “”);
rows.addElement(currentRow);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return rows;
}
@Override
public Vector findRowsByClass() {
Vector rows = new Vector();
Connection conn = ConnectionManager.getConnection();
String strSQL = "select class as ‘班级’, count() as ‘人数’”
+ " from t_student group by class order by class desc";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
Vector currentRow = new Vector();
currentRow.addElement(rs.getString(“班级”));
currentRow.addElement(rs.getInt(“人数”) + “”);
rows.addElement(currentRow);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return rows;
}
@Override
public Vector findRowsByDepartment() {
Vector rows = new Vector();
Connection conn = ConnectionManager.getConnection();
String strSQL = “select department as ‘系部’, count(*) as ‘人数’”
+ " from t_student group by department order by department desc";
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
Vector currentRow = new Vector();
currentRow.addElement(rs.getString(“系部”));
currentRow.addElement(rs.getInt(“人数”) + “”);
rows.addElement(currentRow);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return rows;
}
}

UserDaolmpl
package net.hw.student.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import net.hw.student.bean.User;
import net.hw.student.dao.UserDao;
import net.hw.student.dbutil.ConnectionManager;

public class UserDaolmpl implements UserDao {
@Override
public int deleteById(int id){
return 0;
}
@Override
public List findAll() {
return null;
}
@Override
public User findById(int id){
User user = null;
//获取数据库连接对象
Connection conn = ConnectionManager.getConnection();
String strSQL = “SELECT * FROM t_user WHERE id = ?”;
try {
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setInt(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
user = new User();
user.setId(rs.getInt(“id”));
user.setUsername(rs.getString(“username”));
user.setPassword(rs.getString(“password”));
user.setTelephone(rs.getString(“telephone”));
user.setRegisterTime(rs.getTimestamp(“register_time”));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
ConnectionManager.closeConnection(conn);
}
return user;
}
@Override
public int insert(User user){
int count = 0;

    Connection conn = ConnectionManager.getConnection();
    String strSQL = "INSERT INTO t_user(username,password,telephone, register_time))"
            + "VALUES(?,?,?,?)";
    //创建预备语句对象
    try {
        PreparedStatement pstmt = conn.prepareStatement(strSQL);
        pstmt.setString(1, user.getUsername());
        pstmt.setString(2, user.getPassword());
        pstmt.setString(3,user.getTelephone());
        pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
        count = pstmt.executeUpdate();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        ConnectionManager.closeConnection(conn);
    }
    return count;
}
@Override
public User login(String username,String password){
    User user = null;
    Connection conn = ConnectionManager.getConnection();
    String strSQL = "SELECT * FROM t_user WHERE username = ? AND password = ?";
    try {
        PreparedStatement pstmt = conn.prepareStatement(strSQL);
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setTelephone(rs.getString("telephone"));
            user.setRegisterTime(rs.getTimestamp("register_time"));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        ConnectionManager.closeConnection(conn);
    }
    return user;
}
@Override
public int update(User user){
    int count = 0;
    Connection conn = ConnectionManager.getConnection();
    String strSQL = "UPDATE t_user SET username = ?, password = ?, telephone = ?,"
            + " register_time = ? WHERE id = ?";
    try {
        PreparedStatement pstmt = conn.prepareStatement(strSQL);
        pstmt.setString(1, user.getUsername());
        pstmt.setString(2, user.getPassword());
        pstmt.setString(3, user.getTelephone());
        pstmt.setTimestamp(4, new Timestamp(user.getRegisterTime().getTime()));
        pstmt.setInt(5, user.getId());
        count = pstmt.executeUpdate();
        pstmt.close();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        ConnectionManager.closeConnection(conn);
    }
    return count;
}

}





学校服务接口实现类CollegeServiceImpl