簡單的增刪查改

package com.pro.dao.impl;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.Types;
import java.util.Vector;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;

import com.pro.dao.EmpDao;
import com.pro.dao.comm.DBConfigUtil;
import com.pro.dao.comm.DBConnection;
import com.pro.pojo.Emp;

public class EmpDaoImpl implements EmpDao {

@Override
public boolean insert(Emp emp) {
boolean isSuccess = false;
Connection connection = DBConnection.getConnection();

String sql = "insert into emp values(?,?,?,?,?,?,?,?)";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);

// 向占位符填充實際數據
statement.setInt(1, emp.getEmpno());
statement.setString(2, emp.getEname());
statement.setString(3, emp.getJob());
if (emp.getMgr() == 0) {
statement.setObject(4, null);
} else {
statement.setInt(4, emp.getMgr());
}
statement.setDate(5, new Date(emp.getHiredate().getTime()));
statement.setDouble(6, emp.getSal());
statement.setDouble(7, emp.getComm());
statement.setInt(8, emp.getDeptno());

int result = statement.executeUpdate();

if (result > 0) {
isSuccess = true;
System.out.println("插入成功 ! " + result);
} else {
System.out.println("插入失敗!");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConnection.close(null, statement, connection);
}

return isSuccess;
}

@Override
public boolean update(String exp) {
// TODO Auto-generated method stub
return false;
}

@Override
public boolean delete(String exp) {
boolean isSuccess = false;
Connection connection = DBConnection.getConnection();

String sql = "delete from emp where " + exp;
Statement statement = null;
try {
statement = connection.createStatement();
int result = statement.executeUpdate(sql);

if (result > 0) {
isSuccess = true;
System.out.println("刪除成功 ! " + result);
} else {
System.out.println("刪除失敗!");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConnection.close(null, statement, connection);
}

return isSuccess;
}

@Override
public boolean delete() {
// boolean isSuccess = false;
// Connection connection = DBConnection.getConnection();
//
// String sql = "delete from emp";
// Statement statement = null;
// try {
// statement = connection.createStatement();
// int result = statement.executeUpdate(sql);
//
// if (result > 0) {
// isSuccess = true;
// System.out.println("刪除成功 ! " + result);
// } else {
// System.out.println("刪除失敗!");
// }
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } finally {
// DBConnection.close(null, statement, connection);
// }

return DBConnection.executeUpdate("delete from emp", null) > 0 ? true
: false;
}

@Override
public Emp search(String exp) {
// TODO Auto-generated method stub
return null;
}

@Override
public Vector<Emp> searchMuil(String exp) {
// TODO Auto-generated method stub
return null;
}

@Override
public Vector<Emp> search() {

Vector<Emp> allEmp = new Vector<Emp>();
// Connection connection = DBConnection.getConnection();
//
// String sql =
// "select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
// Statement statement = null;
ResultSet resultSet = null;
try {
// statement = connection.createStatement();
// resultSet = statement.executeQuery(sql);

resultSet = DBConnection.executeQuery(
"select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp",
null);
while (resultSet.next()) {
Emp emp = new Emp();
emp.setEmpno(resultSet.getInt(1));
emp.setEname(resultSet.getString("ename"));
emp.setJob(resultSet.getString("job"));
emp.setMgr(resultSet.getInt("mgr"));
emp.setHiredate(resultSet.getDate("hiredate"));
emp.setSal(resultSet.getDouble("sal"));
emp.setComm(resultSet.getDouble("comm"));
emp.setDeptno(resultSet.getInt("deptno"));
allEmp.add(emp);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// finally {
// DBConnection.close(null, statement, connection);
// }

return allEmp;
}

@Override
public Vector<Emp> search(int pageNum) {
Vector<Emp> allEmp = new Vector<Emp>();
Connection connection = DBConnection.getConnection();

String sql = "{call data_control.pagedata('SCOTT.EMP',?,?,?)}";
OracleCallableStatement statement = null;
ResultSet resultSet = null;

try {
statement = (OracleCallableStatement) connection.prepareCall(sql);
statement.setInt(1, pageNum);
statement.setInt(2, Integer.parseInt(DBConfigUtil
.getProperty("page.default.count")));

statement.registerOutParameter(3, OracleTypes.CURSOR);
statement.execute();

resultSet = statement.getCursor(3);
while (resultSet.next()) {
Emp emp = new Emp();
emp.setEmpno(resultSet.getInt(1));
emp.setEname(resultSet.getString("ename"));
emp.setJob(resultSet.getString("job"));
emp.setMgr(resultSet.getInt("mgr"));
emp.setHiredate(resultSet.getDate("hiredate"));
emp.setSal(resultSet.getDouble("sal"));
emp.setComm(resultSet.getDouble("comm"));
emp.setDeptno(resultSet.getInt("deptno"));
allEmp.add(emp);
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConnection.close(resultSet, statement, connection);
}

return allEmp;
}

}