查询分为两种:全查和模糊查询
一。在UserDao接口中定义两个方法:
//4.查询用户
public List<User> selectAll();
//根据名字进行模糊查询 如果有list.size()>0 没有list.size()==0
public List<User> selectByName(String namecondition);
二。在UserDaoImp中实现着两个方法:
public List<User> selectAll() {
System.out.println("在dao层执行selectAll方法");
List<User> list=new ArrayList<User>();
try {
con=db.getCon();
if(con==null){
System.out.println("dao中数据库连接不成功");
return list;
}
//选pstmt 给pstmt赋值时ddl命令已给,给占位符确定值,只要执行 ; stmt 先给stmt赋值,然后执行时发送ddl命令
String sql="select * from tb_users"; ///
stmt=(Statement) con.createStatement();
rs=stmt.executeQuery(sql);
while(rs.next()){
System.out.println("dao中找到一行");
User user=new User();
user.setUid(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
user.setSex(rs.getString(4));
user.setAge(rs.getInt(5));
user.setTelphone(rs.getString(6));
user.setAddress(rs.getString(7));
user.setMyright(rs.getInt(8));
}
rs.close();
stmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
public List<User> selectByName(String namecondition) {
System.out.println("在dao层执行selectByName方法");
List<User> list=new ArrayList<User>();
try {
con=db.getCon();
if(con==null){
System.out.println("数据库连接失败");
return list;
}
String sql="select *from tb_users where username Like ?";
//选pstmt 给pstmt赋值时ddl命令已给,给占位符确定值,只要执行 ; stmt 先给stmt赋值,然后执行时发送ddl命令
pstmt=(PreparedStatement) con.prepareStatement(sql);
pstmt.setString(1, "%"+namecondition+"%");
rs=pstmt.executeQuery();
if(rs.next()){
User user=new User();
//最重要 id pstmt.setXXX(1,"sfdsdf"),rs.getXXX(1)
user.setUid(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
user.setSex(rs.getString(4));
user.setAge(rs.getInt(5));
user.setTelphone(rs.getString(6));
user.setAddress(rs.getString(7));
user.setMyright(rs.getInt(8));
list.add(user);
}
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
三。在UserServelt中,代码如下:
if(action.trim().equals("mohu")){
System.out.println("控制端执行模糊操作");
String namecondition = request.getParameter("namecondition");
namecondition = EncodingChange.toString(namecondition);
udi = new UserDaoImp();
List<User> list = null;
if (namecondition.equals("")) {
list = udi.selectAll();
} else {
list = udi.selectByName(namecondition);
}
if (list.size() > 0) {
System.out.println("控制端找到用户");
session.setAttribute("userlist", list);
path = "/demo0313/user/showuser.jsp";
} else {
System.out.println("控制端未找到用户");
path = "/demo0313/UserServelt?action=showall";
}
}
response.sendRedirect(path);
}
if(action.trim().equals("showall")){
System.out.println("控制端执行showall功能");
// 1 查询所有的用户 6步
// 1、2、3省略
// 4 创建数据访问层,并调用CRUD方法
udi=new UserDaoImp();
List<User> userlist=udi.selectAll();
if(userlist.size()>0){
System.out.println("控制端查询到数据");
//5.保存数据
session.setAttribute("userlist", userlist);
//6.页面转向
path="/demo0313/user/showuser.jsp";
}else{
path="/demo0313/user/register.jsp";
}
}
四。showuser.jsp中:
<form action="/demo0313/UserServelt">
//同理,这里的action在项目运行时,无法获取
按照:<input type="text" name="namecondition"><input type="hidden" name="action" value="mohu">
<input type="submit" value="查询">
</form></div>