jsp&servlet教程doc文档

package org.px.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import org.px.dao.AddressDAO; import org.px.dao.common.JdbcUtils; import org.px.entity.Address; //AddressDAOImpl版本2 public class AddressDAOImpl2 implements AddressDAO { public Address addAddress(Address addr) { String sql="insert into address(uid,pname,phone,age,email,address) values(?,?,?,?,?,?)"; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { con = JdbcUtils.getDataSource().getConnection(); ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setInt(1, addr.getUid()); ps.setString(2, addr.getPname()); ps.setString(3, addr.getPhone()); ps.setInt(4, addr.getAge()); ps.setString(5, addr.getEmail()); ps.setString(6, addr.getAddress()); int count = ps.executeUpdate(); if (count == 1) { rs = ps.getGeneratedKeys(); if (rs.next()) { int returnKey = rs.getInt(1); addr.setId(returnKey); } } } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtils.closeConnection(rs, ps, con); } return addr; } public int deleteById(int addrId, int uid) { String sql = "delete from address where id=? and uid=? "; int result=-1; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { con = JdbcUtils.getDataSource().getConnection(); ps = con.prepareStatement(sql); ps.setInt(1, addrId); ps.setInt(2, uid); result=ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtils.closeConnection(rs, ps, con); } return result; } public List<Address> getAddressByPage(int startRow, int pageSize, int uid) { String sql = "select * from (select row_number() over(order by id) as RowNum ,* from address where uid=?)as r where r.RowNum >= ? and r.RowNum<?"; List<Address> list=new ArrayList<Address>(); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { con = JdbcUtils.getDataSource().getConnection(); ps = con.prepareStatement(sql); ps.setInt(1, uid); ps.setInt(2, startRow); ps.setInt(3, startRow+pageSize); rs = ps.executeQuery(); while (rs.next()) { Address address = new Address(); address.setId(rs.getInt("id")); address.setUid(rs.getInt("uid")); address.setPname(rs.getString("pname")); address.setPhone(rs.getString("phone")); address.setAge(rs.getInt("age")); address.setEmail(rs.getString("email")); address.setAddress(rs.getString("address")); list.add(address); } } catch (SQLException e) { throw new RuntimeException(e); } finally { JdbcUtils.closeConnection(rs, ps, con); } return list; } public int getCount(int uid) { String sql="select count(*) from address where uid=?"; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; int count = 0; try { con = JdbcUtils.getDataSource().getConnection(); ps = con.prepareStatement(sql); ps.setInt(1, uid); rs = ps.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtils.closeConnection(rs, ps, con); } return count; } public Address selectById(int addrId) { String sql = "select * from address where id=?"; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; Address address = null; try { con = JdbcUtils.getDataSource().getConnection(); ps = con.prepareStatement(sql); ps.setInt(1, addrId); rs = ps.executeQuery(); if (rs.next()) { address = new Address(); address.setId(rs.getInt("id")); address.setUid(rs.getInt("uid")); address.setPname(rs.getString("pname")); address.setPhone(rs.getString("phone")); address.setAge(rs.getInt("age")); address.setEmail(rs.getString("email")); address.setAddress(rs.getString("address")); } } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtils.closeConnection(rs, ps, con); } return address; } public int updateAddress(Address addr) { String sql = "update address set pname=?,phone=?,age=?,email=?,address=? where id=? "; int result=0; Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { con = JdbcUtils.getDataSource().getConnection(); ps = con.prepareStatement(sql); ps.setString(1, addr.getPname()); ps.setString(2, addr.getPhone()); ps.setInt(3, addr.getAge()); ps.setString(4, addr.getEmail()); ps.setString(5, addr.getAddress()); ps.setInt(6, addr.getId()); result=ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } finally { JdbcUtils.closeConnection(rs, ps, con); } return result; } public List<Address> getAddressByPage(int startRow, int pageSize) { return null; } public int getCount() { return 0; } }