JDBC的增删改查
package com.xasxt.dao.impl;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;
import com.xasxt.bean.User;
import com.xasxt.dao.IUserDao;
/**
* @author
* @category 用户的数据访问层
*/
public class UserDaoImpl extends BaseDao implements IUserDao {
/*
* 通过用户名和密码访问数据库,查询出结果,如果正确,返回用户,否则为null
*/
public User find(String userName, String passWord) {
User user = null;
try {
super.regist();
super.connection();
String sql = "select * from User where name=? and pass=?";
super.getPst(sql);
pstmt.setString(1, userName);
pstmt.setString(2, passWord);
super.rs = pstmt.executeQuery();
while (rs.next()) {
user = new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setPass(rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
/*
* 添加用户
*/
public int save(User user) {
int i = 0;
try {
super.regist();
super.connection();
String sql = "insert into user (name,pass,inputdate) values (?,?,?)";
super.getPst(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getPass());
pstmt.setDate(3, new Date(System.currentTimeMillis()));
i = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
/*
* 添加用户详情
*/
public int saveInfo(User user) {
int i = 0;
try {
super.regist();
super.connection();
String sql = "insert into user (name,pass,birthday,age,sex,tel,provinceId,cityId,areaId,inputdate,ip) values (?,?,?,?,?,?,?,?,?,?,?)";
super.getPst(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getPass());
pstmt.setDate(3, new Date(user.getBirthday().getTime()));
pstmt.setInt(4, user.getAge());
pstmt.setString(5, user.getSex());
pstmt.setString(6, user.getTel());
pstmt.setString(7, user.getProvinceId());
pstmt.setString(8, user.getCityId());
pstmt.setString(9, user.getAreaId());
pstmt.setDate(10, new Date(user.getInputdate().getTime()));
pstmt.setString(11, user.getIp());
i = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
public List<User> findUserList() {
List<User> list = new ArrayList<User>();
try {
super.regist();
super.connection();
String sql = "select id,name from user where id<>1 order by id desc";
super.getPst(sql);
rs = super.pstmt.executeQuery();
while (rs.next()) {
User user = new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
list.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
public User find(int id) {
User user = null;
try {
super.regist();
super.connection();
String sql = "select id,name,pass,sex,birthday,age,tel,provinceId,cityId,areaId,inputdate,ip from user where id=?";
super.getPst(sql);
pstmt.setInt(1, id);
rs = super.pstmt.executeQuery();
while (rs.next()) {
user = createUser(rs);
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
public int delete(int id) {
int i = 0;
try {
super.regist();
super.connection();
String sql = "delete from user where id = ?";
super.getPst(sql);
pstmt.setInt(1, id);
i = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
/*
* 更新用户信息
*/
public int update(User user) {
int i = 0;
try {
super.regist();
super.connection();
String sql = "update user set name=?,pass=?,birthday=?,age=?,sex=?,tel=?,provinceId=?,cityId=?,areaId=? where id=?";
super.getPst(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getPass());
pstmt.setDate(3, new Date(user.getBirthday().getTime()));
pstmt.setInt(4, user.getAge());
pstmt.setString(5, user.getSex());
pstmt.setString(6, user.getTel());
pstmt.setString(7, user.getProvinceId());
pstmt.setString(8, user.getCityId());
pstmt.setString(9, user.getAreaId());
pstmt.setInt(10, user.getId());
i = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
public User find(String name) {
User user = null;
try {
super.regist();
super.connection();
String sql = "select id,name,pass,sex,birthday,age,tel,provinceId,cityId,areaId,inputdate,ip from user where name=?";
super.getPst(sql);
pstmt.setString(1, name);
rs = super.pstmt.executeQuery();
while (rs.next()) {
user = createUser(rs);
}
} catch (SQLException e) {
e.printStackTrace();
}
return user;
}
/**
* 创建用户对象
*/
private User createUser(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setPass(rs.getString(3));
user.setSex(rs.getString(4));
user.setBirthday(rs.getDate(5));
user.setAge(rs.getInt(6));
user.setTel(rs.getString(7));
user.setProvinceId(rs.getString(8));
user.setCityId(rs.getString(9));
user.setAreaId(rs.getString(10));
user.setInputdate(rs.getDate(11));
user.setIp(rs.getString(12));
return user;
}
}