1 /**
2 *3 */
4 packagecom.hlcui.dao;5
6 importjava.sql.Connection;7 importjava.sql.PreparedStatement;8 importjava.sql.ResultSet;9 importjava.sql.SQLException;10 importjava.util.ArrayList;11 importjava.util.List;12
13 importcom.hlcui.Constants;14 importcom.hlcui.datasource.DataSourceUtil;15 importcom.hlcui.entity.Student;16
17 /**
18 *@authorAdministrator19 *20 */
21 public classDBUtil {22
23 /**
24 * 查询所有学生信息25 *26 *@return
27 */
28 public static ListgetAllStuInfo() {29 Connection conn = null;30 PreparedStatement ps = null;31 ResultSet rs = null;32 List stus = new ArrayList();33 try{34 conn =DataSourceUtil.getConnection(Constants.URL,35 Constants.USERNAME, Constants.PASSWORD);36 String sql = "select * from student";37 ps =conn.prepareStatement(sql);38 rs =ps.executeQuery();39 while(rs.next()) {40 int id = rs.getInt("id");41 String name = rs.getString("name");42 int age = rs.getInt("age");43 double score = rs.getDouble("score");44 Student s = newStudent(id, name, age, score);45 stus.add(s);46 }47
48 } catch(Exception e) {49 e.printStackTrace();50 } finally{51
52 try{53 if (null !=rs) {54 rs.close();55 }56 if (null !=ps) {57 ps.close();58 }59 if (null !=conn) {60 DataSourceUtil.closeConnection(conn);61 }62 } catch(SQLException e) {63 e.printStackTrace();64 }65
66 }67 returnstus;68 }69
70 /**
71 * 根据id查询学生的信息72 */
73 public static Student getStuInfoById(intid) {74 Connection conn = null;75 PreparedStatement ps = null;76 ResultSet rs = null;77 Student s = null;78 try{79 conn =DataSourceUtil.getConnection(Constants.URL,80 Constants.USERNAME, Constants.PASSWORD);81 String sql = "SELECT * FROM student where id = ?";82 ps =conn.prepareStatement(sql);83 ps.setInt(1, id);84 rs =ps.executeQuery();85 while(rs.next()) {86 String name = rs.getString("name");87 int age = rs.getInt("age");88 double score = rs.getDouble("score");89 s = newStudent(id, name, age, score);90 }91 } catch(Exception e) {92 e.printStackTrace();93 } finally{94 try{95 if (null !=rs) {96 rs.close();97 }98 if (null !=ps) {99 ps.close();100 }101 if (null !=conn) {102 DataSourceUtil.closeConnection(conn);103 }104 } catch(Exception e2) {105 }106 }107 returns;108 }109
110 /**
111 * 增加学生信息112 */
113 public static voidsaveStuInfo(Student stu) {114 Connection conn = null;115 PreparedStatement ps = null;116 try{117 conn =DataSourceUtil.getConnection(Constants.URL,118 Constants.USERNAME, Constants.PASSWORD);119 String sql = "insert into student (id,name,age,score) values (?,?,?,?)";120 ps =conn.prepareStatement(sql);121 ps.setInt(1, stu.getId());122 ps.setString(2, stu.getName());123 ps.setInt(3, stu.getAge());124 ps.setDouble(4, stu.getScore());125 int insertCount =ps.executeUpdate();126 System.out.println(isSuccess(insertCount));127 } catch(Exception e) {128 e.printStackTrace();129 } finally{130 try{131 if (null !=ps) {132 ps.close();133 }134 if (null !=conn) {135 conn.close();136 }137 } catch(Exception e2) {138 e2.printStackTrace();139 }140 }141 }142
143 /**
144 * 根据id删除学生信息145 */
146 public static void deleteStuInfo(intid) {147 Connection conn = null;148 PreparedStatement ps = null;149 try{150 conn =DataSourceUtil.getConnection(Constants.URL,151 Constants.USERNAME, Constants.PASSWORD);152 String sql = "delete from student where id = ?";153 ps =conn.prepareStatement(sql);154 ps.setInt(1, id);155 int deleteCount =ps.executeUpdate();156 System.out.println(isSuccess(deleteCount));157 } catch(Exception e) {158 e.printStackTrace();159 } finally{160 try{161 if (null !=ps) {162 ps.close();163 }164 if (null !=conn) {165 conn.close();166 }167 } catch(Exception e2) {168 e2.printStackTrace();169 }170 }171 }172
173 /**
174 * 根据id修改学生信息175 */
176 public static voidmodifyStuInfo(Student stu) {177 Connection conn = null;178 PreparedStatement ps = null;179 try{180 conn =DataSourceUtil.getConnection(Constants.URL,181 Constants.USERNAME, Constants.PASSWORD);182 String sql = "update student set name = ?,age = ? ,score = ? where id = ?";183 ps =conn.prepareStatement(sql);184 ps.setString(1, stu.getName());185 ps.setInt(2, stu.getAge());186 ps.setDouble(3, stu.getScore());187 ps.setInt(4, stu.getId());188 int count =ps.executeUpdate();189 System.out.println(isSuccess(count));190 } catch(Exception e) {191 e.printStackTrace();192 } finally{193 try{194 if (null !=ps) {195 ps.close();196 }197 if (null !=conn) {198 conn.close();199 }200 } catch(Exception e2) {201 e2.printStackTrace();202 }203 }204 }205
206 /**
207 * 判断操作是否成功208 */
209 public static String isSuccess(intcount) {210 if (count > 0) {211 return "操作成功!";212 } else{213 return "操作失败!";214 }215 }216 }