数据库准备:
CREATE DATABASE stus;
USE stus;
CREATE TABLE stu (
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR (20),
gender VARCHAR (5),
phone VARCHAR (20),
birthday DATE,
hobby VARCHAR(50),
info VARCHAR(200)
);
查询:
-
先写一个JSP 页面, 里面放一个超链接 。
-
写Servlet, 接收请求, 去调用 Service , 由service去调用dao
-
先写Dao , 做Dao实现。(下面是完整的代码,包括了增删改查等功能)
package com.itheima.dao.impl;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.itheima.dao.StudentDao;
import com.itheima.domain.Student;
import com.itheima.util.JDBCUtil02;
import com.itheima.util.TextUtils;
/**
* 这是StudentDao的实现。 针对前面定义的规范,做出具体的实现。
* @author xiaomi
*/
public class StudentDaoImpl implements StudentDao {
/**
* 查询所有学生
* @throws SQLException
*/
@Override
public List<Student> findAll() throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
return runner.query("select * from stu", new BeanListHandler<Student>(Student.class));
}
@Override
public void insert(Student student) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
runner.update("insert into stu values(null , ?,?,?,?,?,?)" ,
student.getSname(),
student.getGender(),
student.getPhone(),
student.getBirthday(),
student.getHobby(),
student.getInfo()
);
}
@Override
public void delete(int sid) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
runner.update("delete from stu where sid=?" ,sid);
}
@Override
public Student findStudentById(int sid) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
return runner.query("select * from stu where sid = ?", new BeanHandler<Student>(Student.class) ,sid);
}
@Override
public void update(Student student) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
runner.update("update stu set sname=? , gender=? , phone=? , birthday=? , hobby=? , info=? where sid = ?",
student.getSname(),
student.getGender(),
student.getPhone(),
student.getBirthday(),
student.getHobby(),
student.getInfo(),
student.getSid());
}
@Override
public List<Student> searchStudent(String sname, String sgender) throws SQLException {
System.out.println("现在要执行模糊查询了,收到的name ="+sname + "==genser=="+sgender);
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
//String sql = "select * from stu where sname=? or sgender=?";
/*
* 这里要分析一下:
* 如果只有姓名 ,select * from stu where sname like ? ;
* 如果只有性别 , select * from stu where gender = ?
*
* 如果两个都有 select * from stu where sname like ? and gender=?
*
* 如果两个都没有就查询所有。
*
*/
String sql = "select * from stu where 1=1 ";
List<String> list = new ArrayList<String> ();
//判断有没有姓名, 如果有,就组拼到sql语句里面
if(!TextUtils.isEmpty(sname)){
sql = sql + " and sname like ?";
list.add("%"+sname+"%");
}
//判断有没有性别,有的话,就组拼到sql语句里面。
if(!TextUtils.isEmpty(sgender)){
sql = sql + " and gender = ?";
list.add(sgender);
}
return runner.query(sql , new BeanListHandler<Student>(Student.class) ,list.toArray() );
}
@Override
public List<Student> findStudentByPage(int currentPage) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
//第一个问号,代表一页返回多少条记录 , 第二个问号, 跳过前面的多少条记录。
//5 0 --- 第一页 (1-1)*5
//5 5 --- 第二页 (2-1)*5
//5 10 --- 第三页
return runner.query("select * from stu limit ? offset ?",
new BeanListHandler<Student>(Student.class) , PAGE_SIZE , (currentPage-1)*PAGE_SIZE);
}
@Override
public int findCount() throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil02.getDataSource());
//用于处理 平均值 、 总的个数。
Long result = (Long) runner.query("SELECT COUNT(*) FROM stu" , new ScalarHandler() );
return result.intValue();
}
}
4再Service , 做Service的实现。
package com.itheima.service;
import java.sql.SQLException;
import java.util.List;
import com.itheima.domain.PageBean;
import com.itheima.domain.Student;
/**
* 这是学生的业务处理规范
* @author xiaomi
*
*/
public interface StudentService {
/**
* 查询当页的数据
* @param currentPage
* @return
* @throws SQLException
*/
PageBean findStudentByPage(int currentPage) throws SQLException ;
/**
* 查询所有学生
* @return List<Student>
*/
List<Student> findAll() throws SQLException ;
/**
* 根据ID查询单个学生对象
* @param sid
* @return
* @throws SQLException
*/
Student findStudentById(int sid) throws SQLException ;
/**
* 模糊查询, 根据姓名或者根据性别,或者两者兼有。
* @param sname
* @param sgender
* @return 集合
* @throws SQLException
*/
List<Student> searchStudent(String sname , String sgender) throws SQLException ;
/**
* 添加学生
* @param student 需要添加到数据库的学生对象
* @throws SQLException
*/
void insert(Student student) throws SQLException ;
/**
* 根据id删除学生
* @param sid
* @throws SQLException
*/
void delete(int sid) throws SQLException ;
/**
* 更新学生信息
* @param student 需要更新的学生数据
* @throws SQLException
*/
void update (Student student )throws SQLException ;
}
5在servlet 存储数据,并且做出页面响应
6在list.jsp上显示数据
补充:
TIPS:
1:最简单的查询就按照上面流程来写就行了
2:具体的功能对应不同的Servlet,也对应不同的.jsp
3:c3p0-config.xml 连接后台与数据库
Web.xml 连接前端之间跳转的页面(准确来说不同Servlet之间)
4:com.itheima.dao.impl 具体实现 com.itheima.dao
5:java deployment descriptor 部署描述符