一、DBUtils通用增删改查代码
- 第一种:没有释放资源,有if条件语句
- object…args与onject[]args的区别
以问号个数为准
- 元数据(Meata data)
描述数据的数据 String sql,描述这份sql字符串的数据叫做元数据
数据库元数据:DatabaseMetaData
参数元数据:ParameterMetaData
结果集元数据:ResultSetMetaData
二、开发模式
- 1)开发模式一:
在jsp里面直接写java代码 - 2)开发模式二:MVC设计模式
M:model(模型层)封装数据JavaBean
V:View(视图层)jsp专注于显示
C:Controller(控制层)Servlet接收页面的请求,找模型层去处理,然后响应数据输出
对于大型项目:分层,逻辑比较清楚,便于维护,拓展项目 - 3)三层架构
三、学生管理系统
1)准备阶段
Eclipse “new”“other”“New Dynamic Web Project”
“WebContent”“new”“JSP file”
“Java Resources”“src”“new”“servlet”
日期对象时倒入util包
Eclipse导入jar包:
右击项目“properties”
- DBUtils:
DbUtils是一种 JDBC Utility Component (翻译过来大概就是:JDBC实用部件),故名思意,和数据库操作有关(DbUtils是一个工具类库,用来进行数据库通信的)
在 DbUtils 中的核心类/接口为 QueryRunner 和 ResultSetHandler
QueryRunner
QueryRunner中一共有6种方法:
execute(执行SQL语句)
batch(批量处理语句)
insert(执行INSERT语句)
insertBatch(批量处理INSERT语句)
query(SQL中 SELECT 语句)
update(SQL中 INSERT, UPDATE, 或 DELETE 语句)
源码:
导入c3p0的jar包和c3p0的配置文件后,配置数据源:
//获取数据源
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
//QueryRunner需要一个数据源作为参数
private static QueryRunner queryRunner = new QueryRunner(dataSource);
private static void showAll(){
try{
String sql = "SELECT * FROM test;";
List<Object[]> list = queryRunner.query(sql, new ArrayListHandler());
for (Object[] aList : list) {
System.out.println(Arrays.toString(aList) + " ");
}
}catch (SQLException e){
throw new RuntimeException(e);
}
}
- 获取单选框或者复选框的值
1)利用js得到其value的值并将其赋值给name,传到servlet
单选框:
复选框:
2)利用jstl来来判断单选框的值或者复选框中是否包含某值,再将它定义为checked
单选框
复选框
- 分页功能
物理分页:来数据库查询的时候,只查一页的数据就返回了
逻辑分页:一口去把所有的数据全部查询出来,然后放置在内存中
2)学生管理系统
Dao
StudentDao接口
public interface StudentDao {
//接口里定义的都是常量,一页显示多少条数据
int PAGE_SIZE = 5;
//分页查询
List<Student> findStudentByPage (int currentPage) throws SQLException;
//查询所有学生
List<Student> findAll()throws SQLException;
//根据学生id查询单个学生对象
Student findStudentById(int sid)throws SQLException;
//根据学生姓名或者id模糊查询,返回值可能是list
List<Student>searchStudent(String sname,String sgender) throws SQLException;
//添加信息
void insert(Student student)throws SQLException;
//根据id删除学生
void delete(int sid) throws SQLException;
//更新学生信息
void update(Student student)throws SQLException;
//查询总的学生记录
int findCount()throws SQLException;
}
StudentDaoImpl
public class StudentDaoImpl implements StudentDao {
//分页查询
@Override
public List<Student> findStudentByPage(int currentPage) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
//第一个问号表示一页返回多少条记录,第二个问号表示跳过前面多少条记录
//5 0 第一页(1-1)*5
//5 5 第二页(2-1)*5
//5 10 第三页(3-1)*5
//从第几条(第二个问号)开始读取几条(第一个问号)
return runner.query("select * from stu limit ? offset ?", new BeanListHandler<Student>(Student.class),PAGE_SIZE,(currentPage-1)*PAGE_SIZE);
}
//全部查询
@Override
public List<Student> findAll() throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
return runner.query("select * from stu", new BeanListHandler<Student>(Student.class));
}
//根据ID查询
@Override
public Student findStudentById(int sid) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
return runner.query("select * from stu where sid = ?",new BeanHandler<Student>(Student.class),sid);
}
//模糊查询(根据name或者性别查询)
@Override
public List<Student> searchStudent(String sname, String sgender) throws SQLException {
System.out.println("现在要执行模糊查询了,收到的name ="+sname + "==gender=="+sgender);
QueryRunner runner = new QueryRunner(JDBCUtil.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 void insert(Student student) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.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(JDBCUtil.getDataSource());
runner.update("delete from stu where sid=?",sid);
}
//更新数据
@Override
public void update(Student student) throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.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 int findCount() throws SQLException {
QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
//用于处理 平均值 、 总的个数。
Long result = (Long) runner.query("SELECT COUNT(*) FROM stu" , new ScalarHandler() );
return result.intValue();
}
}
Service层
StudentService
public interface StudentService {
List<Student> findAll() throws SQLException;
PageBean findStudentByPage (int currentPage) throws SQLException;
Student findStudentById(int sid)throws SQLException;
List<Student>searchStudent(String sname,String sgender) throws SQLException;
void insert(Student student)throws SQLException;
void delete(int sid) throws SQLException;
void update(Student student)throws SQLException;
}
StudentServiceImpl继承StudentService接口
public class StudentServiceImpl implements StudentService {
@Override
public List<Student> findAll() throws SQLException {
StudentDao dao = new StudentDaoImpl();
return dao.findAll();
}
@Override
public PageBean findStudentByPage(int currentPage) throws SQLException {
//封装分页的该页数据
PageBean<Student> pageBean = new PageBean<Student>();
int pageSize = StudentDao.PAGE_SIZE ;
pageBean.setCurrentPage(currentPage); //设置当前页
pageBean.setPageSize(pageSize); //设置每页显示多少记录
StudentDao dao = new StudentDaoImpl() ;
List<Student> list =dao .findStudentByPage(currentPage);
pageBean.setList(list); //设置这一页的学生数据
//总的记录数, 总的页数。
int count = dao.findCount();
pageBean.setTotalSize(count); //设置总的记录数
//200 , 10 ==20 201 , 10 = 21 201 % 10 == 0 ?201 / 10 :201 % 10 + 1
pageBean.setTotalPage(count % pageSize==0 ? count / pageSize : (count / pageSize) + 1); //总页数
return pageBean;
}
@Override
public Student findStudentById(int sid) throws SQLException {
StudentDaoImpl dao = new StudentDaoImpl();
return dao.findStudentById(sid);
}
@Override
public List<Student> searchStudent(String sname, String sgender) throws SQLException {
StudentDaoImpl dao = new StudentDaoImpl();
return dao.searchStudent(sname, sgender);
}
@Override
public void insert(Student student) throws SQLException {
StudentDao dao = new StudentDaoImpl();
dao.insert(student);
}
@Override
public void delete(int sid) throws SQLException {
StudentDao dao = new StudentDaoImpl();
dao.delete(sid);
}
@Override
public void update(Student student) throws SQLException {
StudentDao dao = new StudentDaoImpl();
dao.update(student);
}
}
具体功能实现的Servlet
-
增
query方法1.T query(String sql, ResultSetHandler rsh)
不需要替换参数来执行已给予的SELECT语句
2. T query(String sql, ResultSetHandler rsh, Object… params)
需要一个或多个替换参数来执行已给予的SELECT语句,并返回一种对象结果
//添加纪录
private static void add(){
String id, name, gender;
for (int i = 0; i < 10; i++) {
id = String.valueOf(i);
name = "name" + String.valueOf(i);
gender = i % 2 == 0 ? "male" : "female";
try{
String sql = "INSERT INTO test VALUES(?,?,?);";
Object[] params = {id, name, gender};
queryRunner.update(sql, params);
}catch(SQLException e){
throw new RuntimeException(e);
}
}
}
try {
//1.获取客户端提取上来的数据
String sname = req.getParameter("sname");
String gender = req.getParameter("gender");
String phone = req.getParameter("phone");
String birthday = req.getParameter("birthday");
String []h = req.getParameterValues("hobby");
String info = req.getParameter("info");
//将数组类型的数据转换成string类型 遍历输出
String hobby = Arrays.toString(h);
hobby = hobby.substring(1, hobby.length()-1);
//2.添加到数据库
//string--date将string类型数据转换成为date型数据
Date data = new SimpleDateFormat("yyyy-MM-dd").parse(birthday);
Student student = new Student(sname, gender, phone,hobby, info, data);
StudentServiceImpl service = new StudentServiceImpl();
service.insert(student);
//3.跳转到列表页
//再查一次数据库,然后再装到作用域中,然后在跳转
// req.getRequestDispatcher("list.jsp").forward(req,resp);
//servelt除了能跳jsp以外还能跳servlet
req.getRequestDispatcher("List").forward(req,resp);
} catch (Exception e) {
e.printStackTrace();
}
- 删
//按照给定ID删除信息
private static void delete(String id){
try{
String sql = "DELETE FROM test WHERE id = ?";
queryRunner.update(sql, id);
}catch (SQLException e){
throw new RuntimeException(e);
}
}
public static void main(String[] args){
delete("0");
}
try {
//接收id
int sid = Integer.parseInt(req.getParameter("sid"));
//执行删除
StudentService service = new StudentServiceImpl();
service.delete(sid);
//跳转到列表页
req.getRequestDispatcher("List").forward(req,resp);
} catch (SQLException e) {
e.printStackTrace();
}
-
改
update方法int update(Connection conn, String sql)
不需要替换参数来执行INSERT, UPDATE
DELETE语句int update(Connection conn, String sql, Object… params)
需要一个或多个替换参数以及一个数据库连接来执行INSERT, UPDATE
DELETE语句int update(Connection conn, String sql, Object param)
需要一个替换参数,以及一个数据库连接来执行INSERT, UPDATE
DELETE语句int update(String sql)
不需要替换参数来执行已给予的INSERT, UPDATE
DELETE语句int update(String sql, Object… params)
需要一个或多个替换参数来执行INSERT, UPDATE
DELETE语句(参数无需带有连接)int update(String sql, Object param)
不需要替换参数来执行INSERT, UPDATE
DELETE语句(参数无需带有连接)
//按照给定ID编辑信息
private static void edit(String id){
try{
String sql = "UPDATE test SET name = ?, gender = ? WHERE id = ?;";
Object[] params = {"change", "female", id};
queryRunner.update(sql,params);
}catch (SQLException e){
throw new RuntimeException(e);
}
}
public static void main(String[] args){
edit("0");
}
try {
//1.获取客户端提取上来的数据
int sid = Integer.parseInt(req.getParameter("sid"));
String sname = req.getParameter("sname");
String gender = req.getParameter("gender");
String phone = req.getParameter("phone");
String birthday = req.getParameter("birthday");
String []h = req.getParameterValues("hobby");
String info = req.getParameter("info");
//将数组类型的数据转换成string类型 遍历输出
String hobby = Arrays.toString(h);
hobby = hobby.substring(1, hobby.length()-1);
//2.添加到数据库
//string--date将string类型数据转换成为date型数据
Date data = new SimpleDateFormat("yyyy-MM-dd").parse(birthday);
Student student = new Student(sid,sname, gender, phone,hobby, info, data);
StudentServiceImpl service = new StudentServiceImpl();
service.update(student);
//3.跳转到列表页
//再查一次数据库,然后再装到作用域中,然后在跳转
// req.getRequestDispatcher("list.jsp").forward(req,resp);
//servelt除了能跳jsp以外还能跳servlet
req.getRequestDispatcher("List").forward(req,resp);
} catch (Exception e) {
e.printStackTrace();
}
- 查
1)查询所有学生的信息
try {
//1.查询出所有学生的信息
StudentService service = new StudentServiceImpl();
List<Student> list = service.findAll();
//2.先把数据存到作用域中
request.setAttribute("list",list);
//3.跳转页面
request.getRequestDispatcher("list.jsp").forward(request,response);
} catch (SQLException e) {
e.printStackTrace();
}
2)模糊查询
try {
req.setCharacterEncoding("utf-8");
resp.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=UTF-8");
//1.取到查询的关键数据:姓名和性别
String sname = req.getParameter("sname");
String sgender = req.getParameter("sgender");
//2.找service去查询
StudentServiceImpl service = new StudentServiceImpl();
List<Student> list = service.searchStudent(sname, sgender);
System.out.println("list的大小是:"+list.size());
for(Student student : list){
System.out.println("stu=:"+student);
}
req.setAttribute("list",list);
//3.跳转界面,带着list中的值赋值给前端页面的list
req.getRequestDispatcher("list.jsp").forward(req,resp);
} catch (SQLException e) {
e.printStackTrace();
}
3)分页查询
try{
//1.获取需要显示的页码数
int currentPage = Integer.parseInt(req.getParameter("currentPage"));
//2.根据指定的页数,去获取该页的数据回来
StudentServiceImpl service = new StudentServiceImpl();
PageBean pageBean = service.findStudentByPage(currentPage);
req.setAttribute("pageBean",pageBean);
//3.跳转页面
req.getRequestDispatcher("list_page.jsp").forward(req,resp);
}catch (SQLException e){
e.printStackTrace();
}
PageBean的domain类:封装了分页的数据
public class PageBean<T> {
private int currentPage;//当前页
private int totalPage;//总页数
private int pageSize;//每页的记录数
private int totalSize;//总的记录数
private List<Student> list;//当前页的学生集合
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalSize() {
return totalSize;
}
public void setTotalSize(int totalSize) {
this.totalSize = totalSize;
}
public List<Student> getList() {
return list;
}
public void setList(List<Student> list) {
this.list = list;
}
}