package dao.imp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Array
List;
import java.util.
List;
import common.Pager;
import dao.BaseDao;
import dao.UserDao;
import entity.User;
public class UserDaoImpl extends BaseDao implements UserDao {
//根据SQL查询所有用户信息
public
List<User> getusersBySql(String sql,Object...args){
List<User>
list = new Array
List<User>();
Connection conn =
null;
PreparedStatement pstmt =
null;
ResultSet rs =
null;
try {
conn = getConn();
pstmt = conn.prepareStatement(sql);
if(args!=
null&&args.length>0){
for(int i = 0;i<args.length;i++){
pstmt.setObject(i+1, args[i]);
}
}
rs = pstmt.executeQuery();
while(rs.next()){
User user = new User();
user.setUserno(rs.getInt("USERNO"));
user.setUsername(rs.getString("USERNAME"));
user.setSex(rs.getString("SEX"));
user.setAge(rs.getInt("AGE"));
user.setTellphone(rs.getString("TELLPHONE"));
user.setAddress(rs.getString("ADDRESS"));
user.setPermission(rs.getInt("permission"));
user.setPassword(rs.getString("password"));
list.add(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
closeAll(rs, pstmt, conn);
}return
list;
}
//分页查询用户
public
List<User> getUsersByPager(Pager pager,User condition){
String conditionSql = "";
String sql = "select * from (select t1.*,rownum rn from userinfo t1 where rownum<=?";
if (condition.getUsername()!=
null&&!condition.getUsername().trim().equals("")) {
conditionSql="and username like '%"+condition.getUsername()+"%'";
}
sql+=conditionSql;
sql+=") where rn>?";
List<User>
list = getusersBySql(sql,
pager.getCurrentPage()*pager.getPage
Size(),
(pager.getCurrentPage()-1)*pager.getPage
Size());
String countSql = "select count(*) from userinfo where 1=1"+conditionSql;
pager.setTotalCount(getUserCount(countSql));
return
list;
}
//查询条数
public int getUserCount(String sql){
int count = 0;
Connection conn =
null;
PreparedStatement pstmt =
null;
ResultSet rs =
null;
try {
conn = getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()){
count = rs.getInt(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
closeAll(rs, pstmt, conn);
}
return count;
}
//查询所有用户信息
public
List<User> getAllUsers(){
String sql = "select * from userinfo";
return getusersBySql(sql);
}
//根据用户名查询
public User getUserByUsernameandP(String username,String password){
String sql = "select * from userinfo where username = ? and password=?";
List<User>
list = getusersBySql(sql, username,password);
return
list !=
null &&
list.
size()>0 ?
list.get(0) :
null;
}
//根据编号查询
public User getUserByUserno(int userno){
String sql = "select * from userinfo where userno = ?";
List<User>
list = getusersBySql(sql, userno);
return
list !=
null &&
list.
size()>0 ?
list.get(0) :
null;
}
public
List<User> getUserByUserno1(int userno){
String sql = "select * from userinfo where userno = ?";
List<User>
list = getusersBySql(sql, userno);
return
list;
}
//增加
public int addUser(User user){
String sql = "insert into userinfo values(seq_user.nextval,?,?,?,?,?,?,?)";
return super.executeSql(sql,user.getUsername(),user.getSex(),user.getAge(),user.getTellphone(),user.getAddress(),user.getPermission(),user.getPassword());
}
//修改
public int updateUser(User user){
String sql = "update userinfo set username=?,sex=?,age=?,tellphone=?,address=?,permission=? ,password=? where userno=?";
return super.executeSql(sql, user.getUsername(),user.getSex(),user.getAge(),user.getTellphone(),user.getAddress(),user.getPermission(),user.getPassword(),user.getUserno());
}
//删除
public int deleteUser(String username){
String sql = "delete from userinfo where username=?";
return super.executeSql(sql, username);
}
public int deleteUser(int userno){
String sql = "delete from userinfo where userno=?";
return super.executeSql(sql, userno);
}
public int updatePassword(String password,String username){
String sql="update userinfo set password=? where username=?";
return super.executeSql(sql, password,username);
}
public User getUserByUsername(String username){
String sql="select * from userinfo where username=?";
List<User>
list=getusersBySql(sql, username);
return
list !=
null &&
list.
size()>0 ?
list.get(0) :
null;
}
public
List<User> getUserByUsername1(String username){
String sql="select * from userinfo where username=?";
List<User>
list=getusersBySql(sql, username);
return
list;
}
}
276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
