Question

各们JAVA高手帮我看看下面这题，给点提示谢谢。

设计：通过JDBC完成Student(id, name, age, sex, email, address)的增、删、改、查 的功能。
要求：
1、在数据库中建立学生信息表；
2、通过JDBC对表中的信息进行增、删、改、查4项操作，分别使用一个界面实现；
3、实现各种组合查询操作，并把查询结果用表格显示。
4、系统通过菜单方式调用 增、删、改、查等各项操作

Answer 1

类似于下面那样连接数据库：
package com.bjsxt.drp.util;

import java.sql.*;

public class DB {

public static Connection getConn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/drp?user=root&password=root");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

return conn;
}

public static Statement getStatement(Connection conn) {
Statement stmt = null;
try {
if(conn != null) {
stmt = conn.createStatement();
}
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}

public static ResultSet getResultSet(Statement stmt, String sql) {
ResultSet rs = null;
try {
if(stmt != null) {
rs = stmt.executeQuery(sql);
}
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}

public static void closeConn(Connection conn) {
try {
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public static void closeStmt(Statement stmt) {
try {
if(stmt != null) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}

public static void closeRs(ResultSet rs) {
try {
if(rs != null) {
rs.close();
rs = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

下面是个用户Bean：

package com.bjsxt.drp.sysmgr;

import java.util.Date;

/**
* 用户实体类
* @author Administrator
*
*/
public class User {

//用户代码
private String userId;

//用户名称
private String userName;

//密码
private String password;

//联系电话
private String contactTel;

//email
private String email;

//创建日期
private Date createDate;

public String getContactTel() {
return contactTel;
}

public void setContactTel(String contactTel) {
this.contactTel = contactTel;
}

public Date getCreateDate() {
return createDate;
}

public void setCreateDate(Date createDate) {
this.createDate = createDate;
}

public String getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getUserId() {
return userId;
}

public void setUserId(String userId) {
this.userId = userId;
}

public String getUserName() {
return userName;
}

public void setUserName(String userName) {
this.userName = userName;
}
}

下面是一个用户管理类来对用户进行增删改查：

package com.bjsxt.drp.sysmgr;

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

import com.bjsxt.drp.util.DB;
import com.bjsxt.drp.util.PageModel;

/**
* 对用户完成增删改查的类
* @author Administrator
*
*/
public class UserManager {

// private static UserManager instance;
//
// private UserManager() {
//
// }
//
// public static synchronized UserManager getInstance() {
// if (instance == null) {
// instance = new UserManager();
// }
// return instance;
// }

private static UserManager instance = new UserManager();

private UserManager() {
}

public static UserManager getInstance() {
return instance;
}

/**
* 增加用户
* @param user user用户对象
*/
public void addUser(User user) {
String sql ="insert into t_user(user_id, user_name, password, contact_tel, email, create_date) " +
"values(?, ?, ?, ?, ?, ?)";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DB.getConn();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getUserId());
pstmt.setString(2, user.getUserName());
pstmt.setString(3, user.getPassword());
pstmt.setString(4, user.getContactTel());
pstmt.setString(5, user.getEmail());
pstmt.setTimestamp(6, new Timestamp(user.getCreateDate().getTime()));
pstmt.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
DB.closeStmt(pstmt);
DB.closeConn(conn);
}
}

/**
* 根据用户代码查询
* @param userId 用户代码
* @return user对象
*/
public User findUserById(String userId) {
String sql = "select * from t_user where user_id=?";
System.out.println("findUserById()==>>sql=" + sql);
User user = null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DB.getConn();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userId);
rs = pstmt.executeQuery();
if (rs.next()) {
user = new User();
user.setUserId(rs.getString("user_id"));
user.setUserName(rs.getString("user_name"));
user.setPassword(rs.getString("password"));
user.setContactTel(rs.getString("contact_tel"));
user.setEmail(rs.getString("email"));
user.setCreateDate(rs.getTimestamp("create_date"));
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DB.closeRs(rs);
DB.closeStmt(pstmt);
DB.closeConn(conn);
}
return user;
}

/**
* 分页查询所有的用户数据
* @param pageNo 第几页
* @param pageSize 每页显示多少条
* @return pageModel对象
*/
public PageModel findAllUser(int pageNo, int pageSize) {
PageModel pageModel = null;
String sql ="select * from t_user where user_id <> 'root' order by user_id " +
"limit " + (pageNo - 1) * pageSize + ", " + pageSize;
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DB.getConn();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
List userList = new ArrayList();
while (rs.next()) {
User user = new User();
user.setUserId(rs.getString("user_id"));
user.setUserName(rs.getString("user_name"));
user.setPassword(rs.getString("password"));
user.setContactTel(rs.getString("contact_tel"));
user.setEmail(rs.getString("email"));
user.setCreateDate(rs.getTimestamp("create_date"));
userList.add(user);
}
int totalRecords = getTotalRecords(conn);
pageModel = new PageModel();
pageModel.setPageNo(pageNo);
pageModel.setPageSize(pageSize);
pageModel.setTotalRecords(totalRecords);
pageModel.setList(userList);
}catch(SQLException e) {
e.printStackTrace();
}finally {
DB.closeRs(rs);
DB.closeStmt(stmt);
DB.closeConn(conn);
}
return pageModel;
}

/**
* 返回所有的用户数
* @param conn
* @return 记录数
*/
private int getTotalRecords(Connection conn) {
String sql = "select count(*) from t_user where user_id <> 'root'";
int totalRecords = 0;
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
totalRecords = rs.getInt(1);
}
}catch(SQLException e) {
e.printStackTrace();
}finally {
DB.closeRs(rs);
DB.closeStmt(stmt);
}
return totalRecords;
}

/**
* 根据用户ID删除用户
* @param userId userId
*/
public void deleteUserById(String userId) {
String sql = "delete from t_user where user_id=?";
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DB.getConn();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userId);
pstmt.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}finally {
DB.closeStmt(pstmt);
DB.closeConn(conn);
}
}

/**
* 根据UserId的集合进行删除
* @param userIds userId的集合
*
* 可以把jsp中的userId数组传过来，形成如下SQL：
* delete from t_user where user_id in ('a0001', 'a0002')
*/
public void deleteUserByIds(String[] userIds) {

}

}