1 packagedao;2
3 importjava.sql.Connection;4 importjava.sql.PreparedStatement;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.util.ArrayList;8 importjava.util.List;9
10 importcom.sun.xml.internal.bind.v2.runtime.Name;11
12 importmodel.User;13 importutil.DBUtil;14 importutil.UserException;15
16
17 public class UserImp implementsIUser{18
19
20 public voidadd(User user) {21
22 Connection connection=DBUtil.getConnection();23
24 String sql = "select count(*) from t_user where name = ?";//准备sql语句
25
26 PreparedStatement preparedStatement = null;27 ResultSet resultSet = null;28
29 try{30 preparedStatement =connection.prepareStatement(sql);31 preparedStatement.setString(1, user.getName());32
33 resultSet =preparedStatement.executeQuery();34
35 while(resultSet.next()) {36 if (resultSet.getInt(1) > 0) {37 throw new UserException("用户已存在") ;38 }39 }40
41 String sql1 = "insert into t_user(name,password) value (?,?)";42 preparedStatement =connection.prepareStatement(sql1);43 preparedStatement.setString(1, user.getName());44 preparedStatement.setString(2, user.getPassword());45 preparedStatement.executeUpdate();46 } catch(SQLException e) {47
48 e.printStackTrace();49 }finally{50
51 DBUtil.close(resultSet);52 DBUtil.close(preparedStatement);53 DBUtil.close(connection);54 }55
56 }57
58
59 public void delete(intid) {60 Connection connection =DBUtil.getConnection();61 String sql = "delete from t_user where id = ?";62 PreparedStatement preparedStatement = null;63
64 try{65 preparedStatement =connection.prepareStatement(sql);66 preparedStatement.setInt(1, id);67 preparedStatement.executeUpdate();68 } catch(SQLException e) {69
70 e.printStackTrace();71 }finally{72 DBUtil.close(preparedStatement);73 DBUtil.close(connection);74 }75
76
77 }78
79
80 public voidupdate(User user) {81
82 Connection connection =DBUtil.getConnection();83 //准备sql语句
84 String sql = "update t_user set name = ? , password=? where id = ?";85 //创建语句传输对象
86 PreparedStatement preparedStatement = null;87 try{88 preparedStatement =connection.prepareStatement(sql);89 preparedStatement.setString(1, user.getName());90 preparedStatement.setString(2, user.getPassword());91 preparedStatement.setInt(3, user.getId());92 preparedStatement.executeUpdate();93 } catch(SQLException e) {94
95 e.printStackTrace();96 }finally{97 DBUtil.close(preparedStatement);98 DBUtil.close(connection);99 }100
101 }102
103 public User load(intid) {104 Connection connection =DBUtil.getConnection();105 //准备sql语句
106 String sql = "select * from t_user where id = ?";107 //创建语句传输对象
108 PreparedStatement preparedStatement = null;109 ResultSet resultSet = null;110 User user = null;111 try{112 preparedStatement =connection.prepareStatement(sql);113
114 preparedStatement.setInt(1, id);115 resultSet =preparedStatement.executeQuery();116 while(resultSet.next()) {117 user = newUser();118 user.setId(id);119 user.setName(resultSet.getString("name"));;120 user.setPassword(resultSet.getString("password"));121
122 }123 } catch(SQLException e) {124
125 e.printStackTrace();126 }finally{127 DBUtil.close(resultSet);128 DBUtil.close(preparedStatement);129 DBUtil.close(connection);130 }131 returnuser;132 }133
134
135 @Override136 public Listload() {137 Connection connection =DBUtil.getConnection();138 //准备sql语句
139 String sql = "select * from t_user ";140 //创建语句传输对象
141 PreparedStatement preparedStatement = null;142 ResultSet resultSet = null;143 //集合中只能放入user对象
144 List users = new ArrayList();145 User user = null;146 try{147
148 preparedStatement =connection.prepareStatement(sql);149
150
151
152 resultSet =preparedStatement.executeQuery();153 while(resultSet.next()) {154 user = newUser();155 user.setId(resultSet.getInt("id"));156 user.setName(resultSet.getString("name"));157 user.setPassword(resultSet.getString("password"));158
159 users.add(user);160 }161 } catch(SQLException e) {162
163 e.printStackTrace();164 }finally{165 DBUtil.close(resultSet);166 DBUtil.close(preparedStatement);167 DBUtil.close(connection);168 }169 returnusers;170 }171
172
173 @Override174 public List load(String content) {175 Connection connection =DBUtil.getConnection();176 //准备sql语句
177 String sql = "select * from t_user ";178 //创建语句传输对象
179 PreparedStatement preparedStatement = null;180 ResultSet resultSet = null;181 //集合中只能放入user对象
182 List users = new ArrayList();183 User user = null;184 try{185 if (content == null || "".equals(content)) {186 preparedStatement =connection.prepareStatement(sql);187 }else{188 sql += "where name like ? ";189 preparedStatement =connection.prepareStatement(sql);190 preparedStatement.setString(1, "%"+ content +"%");191
192 }193 resultSet =preparedStatement.executeQuery();194 while(resultSet.next()) {195 user = newUser();196 user.setId(resultSet.getInt("id"));197 user.setName(resultSet.getString("name"));198 user.setPassword(resultSet.getString("password"));199
200 users.add(user);201 }202 } catch(SQLException e) {203
204 e.printStackTrace();205 }finally{206 DBUtil.close(resultSet);207 DBUtil.close(preparedStatement);208 DBUtil.close(connection);209 }210 returnusers;211 }212 }