增删改查的核心

增删改查的核心

本人因为经常要写增删改查，所以把它的核心发下帖，供大家参考：

这是一个工具包(简化代码)：

package com.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


import com.bean.Emp;
public class DbOperation {
protected Connection conn;//连接
protected PreparedStatement psmt;//预编译
protected ResultSet rs;//结果集
private final String URL="jdbc:mysql://127.0.0.1:3306/ff";
private final String UNAME="root";
private final String PSW="root";

//连接数据库
public void getConn(){
//加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
//获得链接
conn=DriverManager.getConnection(URL,UNAME, PSW);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//关闭连接
public void closeAll(){
try {
if(conn!=null){
conn.close();
}
if(psmt!=null){
psmt.close();
}
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//查询操作
public void extQuery(String sql,Object...obj){
try {
psmt =conn.prepareStatement(sql);//查询sql语句
for(int i=0;i<obj.length;i++){
psmt.setObject((i+1), obj[i]);
}
rs=psmt.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//更新操作
public void extUpdate(String sql, Object...obj){
try {
psmt = conn.prepareStatement(sql);
for(int i=0; i<obj.length;i++){
psmt.setObject((i+1), obj[i]);
}
psmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//删除操作
public int extDelete(String sql, Object...obj){
//查询失败
int count=-1;
try {
psmt = conn.prepareStatement(sql);
for(int i=0; i<obj.length;i++){
psmt.setObject((i+1), obj[i]);
}
count=psmt.executeUpdate();


} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}
}

我认为Dao里面的知识也是经常用到的：

package com.dao;


import java.sql.SQLException;
import java.util.ArrayList;


import com.bean.Emp;
import com.controller.deleteEmp;
import com.util.DbOperation;


public class EmpDao extends DbOperation{
//查询数据
public ArrayList select(int nowpage,int pagesize,String param){
ArrayList al=new ArrayList();
getConn();//连接数据库
if(param==null||param.equals("")){
String sql="select * from emp limit ?,?";//查询sql语句
//给第一个？赋值
int start=(nowpage-1)*pagesize;
extQuery(sql, start, pagesize);
}else{
String sql="select * from emp where ename like ? limit ?,?";//查询sql语句
//给第一个？赋值
int start=(nowpage-1)*pagesize;
extQuery(sql,"%"+param+"%", start, pagesize);
}
//处理结果集
try {
while(rs.next()){
Emp e=new Emp();
e.setEmpno(rs.getInt("empno"));
e.setEname(rs.getString("ename"));
e.setJob(rs.getString("job"));
e.setMgr(rs.getInt("mgr"));
e.setHiredate(rs.getDate("hiredate"));
e.setSal( rs.getInt("sal"));
e.setComm( rs.getInt("comm"));
e.setDeptno( rs.getInt("deptno"));
al.add(e);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return al;
}
//查询数据总条数
public int getCount(String param){
int count=0;
getConn();
if(param==null||param.equals("")){
String sql="select count(*) from emp";
extQuery(sql);
}else{
String sql="select count(*) from emp where ename like ?";
extQuery(sql,"%"+param+"%");
}
try {
while(rs.next()){
count=rs.getInt(1);
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
closeAll();
return count;
} 
//得到最大页码号  中铁
public int getMax(int pagesize,String param){
int maxpage=0;
maxpage=(int)Math.ceil((double)getCount(param)/(double)pagesize);
return maxpage;
}
//新增
public void add(Emp e){
//连接数据库
getConn();
//执行sql
String sql="insert into emp(ename,job,mgr,hiredate,sal,comm,deptno) value(?,?,?,?,?,?,?)";
extUpdate(sql,e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno());
//关闭连接
closeAll();
}
//删除
public int delete(int empno){
//连接数据库
getConn();
return extDelete("delete from emp where empno=?", empno);
}
//修改
//先查询
public Emp get(int empno){
getConn();
String sql="select * from emp where empno=?";
extQuery(sql, empno);
Emp e=new Emp();
try {
if(rs.next()){
e.setComm(rs.getInt("comm"));
e.setDeptno(rs.getInt("deptno"));
e.setEmpno(rs.getInt("empno"));
e.setEname(rs.getString("ename"));
e.setHiredate(rs.getDate("hiredate"));
e.setJob(rs.getString("job"));
e.setMgr(rs.getInt("mgr"));
e.setSal(rs.getInt("sal"));
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
closeAll();
return e;
}

public void update(Emp e){
getConn();
String sql="update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,comm=?,deptno=? where empno=?";
extUpdate(sql,e.getEname(),e.getJob(),e.getMgr(),e.getHiredate(),e.getSal(),e.getComm(),e.getDeptno(),e.getEmpno());
closeAll();
}
}

最后再分享一个关于Date的小插件：My97DatePicker

下面是一个具体的案例：

1.把My97DatePicker工具放在webroot下面

2.调用它：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>


<html>
  <head>
  <title>新增</title>
  <script language="javascript" type="text/javascript" src="My97DatePicker/WdatePicker.js"></script>
  </head>
  <body>
    <form action="${pageContext.request.contextPath }/addEmp" method="post">
    雇员姓名<input type="text" name="ename"/><br/>
      雇员职位<select name="job">
    <option value="CLERK">普通雇员</option>
    <option value="MANAGER">经理</option>
    <option value="ANALYST">分析师</option>
    <option value="SALESMAN">销售员</option>
    <option value="PRESIDENT">总裁</option>
    </select><br/>
    雇员上级<input type="text" name="mgr"/><br/>
    入职日期<inputclass="Wdate" type="text" onClick="WdatePicker()" name="hiredate"  /><br/>
    雇员薪水<input type="text" name="sal"/><br/>
    雇员奖金<input type="text" name="comm"/><br/>
    部门编号<select name="deptno">
    <option value="10">10号部门</option>
    <option value="20">20号部门</option>
    <option value="30">30号部门</option>
    <option value="40">40号部门</option>
    </select><br/>
    <input type="submit" value="新增">
    </form>
  </body>
</html>

string换成Date类型，期间项目会报错，解决方法：

emp.class:

public Date getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
try {
this.hiredate=sdf.parse(hiredate);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void setHiredate(Date hiredate) {
this.hiredate=hiredate;
}
再添加数据页面中：

package com.controller;


import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;


import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


import com.bean.Emp;
import com.dao.EmpDao;
import com.util.WebUtil;


public class addEmp extends HttpServlet {
EmpDao ed=new EmpDao();

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request,response);
}

public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//处理中文乱码
request.setCharacterEncoding("utf-8");
//获取表单数据  并封装
Emp e=WebUtil.request2bean(request, Emp.class);
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
try {
e.setHiredate(sdf.parse(request.getParameter("hiredate")));
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}


//把数据封装到一个对象  重定向跳转
ed.add(e);
response.sendRedirect("selectEmp");
}
}

此时，运行项目即可。如果有不明白的，请回复我，感谢你的观看！