Mysql Dao

  1 package dao;

  2

  3 import java.sql.Connection;

  4 import java.sql.PreparedStatement;

  5 import java.sql.ResultSet;

  6 import java.sql.SQLException;

  7 import java.util.ArrayList;

  8 import java.util.List;

  9

 10 import entity.User;

 11 import util.DButils;

 12

 13 public class UserDAO {

 14

 15     //DAO类：用于封装数据访问逻辑

 16

 17     public User find(String username) throws Exception {

 18         User user = null;

 19         Connection conn=null;

 20         try {

 21             conn=DButils.getConnection();

 22             String sql="SELECT * FROM t_user WHERE username = ?";

 23             PreparedStatement ps=conn.prepareStatement(sql);

 24             ps.setString(1, username);

 25             ResultSet rs=ps.executeQuery();

 26

 27             while(rs.next()) {

 28

 29                 user=new User();

 30                 user.setUsername(username);

 31                 user.setId(rs.getInt("id"));

 32                 user.setPhone(rs.getString("phone"));

 33                 user.setPwd(rs.getString("pwd"));

 34

 35             }

 36

 37         } catch (SQLException e) {

 38             e.printStackTrace();

 39             throw new RuntimeException(e);

 40         }finally {

 41             DButils.closeConnection(conn);

 42         }

 43         return user;

 44     }

 45

 46     public void delete(int id) throws Exception {

 47         Connection conn=null;

 48

 49         try {

 50             conn=DButils.getConnection();

 51             String sql="delete from t_user where id=?";

 52             PreparedStatement ps=conn.prepareStatement(sql);

 53             ps.setInt(1, id);

 54             ps.executeUpdate();

 55

 56         } catch (SQLException e) {

 57             e.printStackTrace();

 58             throw new RuntimeException(e);

 59         }

 60     }

 61

 62     public void save(User user) throws Exception {

 63         Connection conn=null;

 64

 65         try {

 66             conn=DButils.getConnection();

 67             String sql="insert into t_user"

 68                       +"(username,pwd,phone)"

 69                       +"VALUES(?,?,?)";

 70             PreparedStatement ps=conn.prepareStatement(sql);

 71             ps.setString(1, user.getUsername());

 72             ps.setString(2, user.getPwd());

 73             ps.setString(3, user.getPhone());

 74             ps.executeUpdate();

 75         } catch (SQLException e) {

 76             e.printStackTrace();

 77             throw new RuntimeException(e);

 78         }finally {

 79             DButils.closeConnection(conn);

 80         }

 81     }

 82

 83     public List<User> findAll() throws Exception{

 84         List<User> users=new ArrayList<User>();

 85         Connection conn=null;

 86

 87         try {

 88             conn=DButils.getConnection();

 89             String sql="select * from t_user";

 90             PreparedStatement ps=conn.prepareStatement(sql);

 91             ResultSet rs=ps.executeQuery();

 92

 93             while(rs.next()){

 94                 int id=rs.getInt("id");

 95                 String username=rs.getString("username");

 96                 String pwd=rs.getString("pwd");

 97                 String phone=rs.getString("phone");

 98

 99                 User user=new User();

100                 user.setId(id);

101                 user.setUsername(username);

102                 user.setPwd(pwd);

103                 user.setPhone(phone);

104

105                 users.add(user);

106             }

107         } catch (SQLException e) {

108             e.printStackTrace();

109             throw new RuntimeException(e);

110         }finally{

111             DButils.closeConnection(conn);

112         }

113         return users;

114     }

115

116 }