dao

package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBUtils {
static{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException
{
String url="jdbc:oracle:thin:@localhost:1521:teach";
Connection conn = null;
conn= DriverManager.getConnection(url,"scott","tiger");
return conn;
}
public static void close(Connection conn){
if(conn !=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(PreparedStatement conn){
if(conn !=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(Statement st){
if(st !=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(ResultSet conn){
if(conn !=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}


package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.struts.util.LabelValueBean;

import com.web.form.AdminForm;

public class AdminDao {
private int nowPage =1;
private int allPage =0;
private int pageSize=3;
public int getNowPage() {
return nowPage;
}
public void setNowPage(int nowPage) {
this.nowPage = nowPage;
}
public int getAllPage() {
return allPage;
}
public void setAllPage(int allPage) {
this.allPage = allPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public boolean insert(Map dto)throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try{
conn = DBUtils.getConnection();
String sql = "insert into admin values(seq_admin.nextval,?,?)";
//更改rs为可滚动的
ps = conn.prepareStatement(sql);
ps.setObject(1,dto.get("name"));
ps.setObject(2,dto.get("password"));
if(ps.executeUpdate()>0)
return true;
}finally{
DBUtils.close(ps);
DBUtils.close(conn);
}
return false;
}
public boolean delete(String ids[])throws Exception{
Connection conn = null;
PreparedStatement ps = null;
//事务处理
try{
conn = DBUtils.getConnection();
conn.setAutoCommit(false);
String sql = "delete from admin where id=?";
ps = conn.prepareStatement(sql);
for(int i=0;i<ids.length;i++){
ps.setString(1,ids[i]);
ps.addBatch();
}
ps.executeBatch();
//if(ps.executeUpdate() >0)
// return true;
conn.commit();
}catch(Exception e){
conn.rollback();
throw e;
}finally{
DBUtils.close(ps);
DBUtils.close(conn);
}
return false;
}

public boolean update(Map dto)throws Exception{
Connection conn = null;
PreparedStatement ps = null;
try{
conn = DBUtils.getConnection();
String sql = "update admin set name=?,password=? where id=?";
ps = conn.prepareStatement(sql);
ps.setObject(1,dto.get("name"));
ps.setObject(2,dto.get("password"));
ps.setObject(3,dto.get("id"));
if(ps.executeUpdate() >0)
return true;
}finally{
DBUtils.close(ps);
DBUtils.close(conn);
}
return false;
}
public Map queryById(String id){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List rows = new ArrayList();
Map f = null;
try {
conn = DBUtils.getConnection();
String sql = "select * from admin where id=?";
ps = conn.prepareStatement(sql);
ps.setString(1, id);
rs = ps.executeQuery();
if(rs.next()){
f = new HashMap();
f.put("id",rs.getString("ID"));
f.put("name",rs.getString("NAME"));
f.put("password",rs.getString("PASSWORD"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.close(ps);
DBUtils.close(conn);
}
return f;
}

public List queryAll(Map dto){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
List rows = new ArrayList();
String id=(String)dto.get("id");
String name=(String)dto.get("name");
String pass=(String)dto.get("password");
String nowpage = (String)dto.get("nowPage");
if(nowpage !=null){
this.nowPage = Integer.parseInt(nowpage);
}
try {
conn = DBUtils.getConnection();
StringBuffer sql = new StringBuffer();
sql.append("select * from admin where 1=1");
//根据用户输入的条件，进行动态改变sql
if(id!=null && !id.equals("")){
sql.append(" and id='"+id+"'");
}
if(name!=null && !name.equals("")){
sql.append(" and name like '%"+name+"%'");
}
if(pass!=null && !pass.equals("")){
sql.append(" and password='"+pass+"'");
}
//将rs变为可滚动结果集
ps = conn.prepareStatement(
sql.toString(),
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = ps.executeQuery();
//1.计算总页数
rs.last();
this.allPage= (rs.getRow()+this.pageSize-1)/this.pageSize;
//2.根据当前页滚动rs
rs.beforeFirst();
for(int i=0;i<(this.nowPage-1)*this.pageSize;i++){
rs.next();
}
//3.取当前页的数据
Map f = null;
for(int k=0;k<this.pageSize;k++)
{
if(!rs.next())
break;
f = new HashMap();
f.put("id",rs.getString("ID"));
f.put("name",rs.getString("NAME"));
f.put("password",rs.getString("PASSWORD"));

rows.add(f);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtils.close(ps);
DBUtils.close(conn);
}
return rows;
}

public Collection getPageOption(){
Collection pages = new ArrayList();
for(int i=1; i<=this.allPage;i++){
LabelValueBean opt =
new LabelValueBean("第"+i+"页",i+"");
pages.add(opt);
}
return pages;
}

}