1.添加
public int addept(department dept) {
conn=dao.getConnection();
int count=0;
String sql="insert into t_dept(p_name) values(?)";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,dept.getPname());
count=pstmt.executeUpdate();
if(count!=0){
System.out.println("** 添加成功 **");
}
else{
System.out.println("** 添加失败 **");
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
2.删除
public int deldept(department dept) {
conn=dao.getConnection();
int count=0;
String sql="delete from t_dept where p_id=?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,dept.getPid());
count=pstmt.executeUpdate();
if(count!=0){
System.out.println("** 删除成功 **");
}
else{
System.out.println("** 删除失败 **");
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
3.根据ID查询信息
public Map alldeptById(department dept) {
conn=dao.getConnection();
Map map=new HashMap();
String sql="select * from t_dept where p_id=?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1,dept.getPid());
rs=pstmt.executeQuery();
while(rs.next()){
map.put("pid",rs.getInt("p_id"));
map.put("pname",rs.getString("p_name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
return map;
}
4.修改
public int uppdept(department dept) {
conn=dao.getConnection();
int count=0;
String sql="update t_dept set p_name=? where p_id=?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setString(1,dept.getPname());
pstmt.setInt(2,dept.getPid());
count=pstmt.executeUpdate();
if(count!=0){
System.out.println("** 修改成功 **");
}
else{
System.out.println("** 修改失败 **");
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
5.全查询
public List<department> alldept() {
conn=dao.getConnection();
String sql="select * from t_dept";
List<department> list=new ArrayList<department>();
try {
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
department dept=new department();
dept.setPid(rs.getInt("p_id"));
dept.setPname(rs.getString("p_name"));
list.add(dept);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
6.统计总记录
public int getTotalPage() {
int count=0;
conn=dao.getConnection();
String sql="select count(*) from t_dept";
try {
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
count=rs.getInt(1);
}
count=(int)Math.ceil((count+1.0-1.0)/DATA_PER_PAGE);//4
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}
7.分页查询
public List<department> getpadingByPage(int cur){
conn=dao.getConnection();
List<department> list=new ArrayList<department>();
String sql="select * from t_dept limit ?,?";
try {
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, (cur - 1) * DATA_PER_PAGE);
pstmt.setInt(2, DATA_PER_PAGE);
rs=pstmt.executeQuery();
while(rs.next()){
department dept=new department();
dept.setPid(rs.getInt("p_id"));
dept.setPname(rs.getString("p_name"));
list.add(dept);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
8.用户登录
public int logemp(String uname, String pwd) {
int count=0;
conn=dao.getConnection();
String sql="select count(*) from t_user where t_username='"+uname+"' and t_password='"+pwd+"'";
try {
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
count=rs.getInt(1);
if(count!=0){
System.out.println("** 登录成功 **");
}
else{
System.out.println("** 登录失败 **");
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return count;
}