用myeclipse对mysql进行查找_JAVA-使用MyEclipse连接MySQL进行增删改查操作

1 packagecom.sariel.Service;2

3 importjava.sql.ResultSet;4 importjava.util.ArrayList;5 importjava.util.List;6 importcom.mysql.jdbc.Connection;7 importcom.mysql.jdbc.PreparedStatement;8 importcom.sariel.Bean.Customer_Information;9 importcom.sariel.Utils.DBUtils;10

11 /**客户信息逻辑*/

12 public classCustomer_Information_Service {13

14 public static voidmain(String[] args) {15 //Customer_Information_Service cs = new Customer_Information_Service();16 //cs.selectCustomerInfoById("YXG001");//根据客户的cus_id查询指定客户资料信息

17 }18

19 /**新增客户信息*/

20 public booleanaddCustomerInfo(String cus_id, String cus_account_number,21 String cus_sex, String cus_personality_signature, String cus_hobby,22 String cus_psssworld, String cus_email, String cus_phone_number,23 String cus_address, String cus_head_portrait) {24 boolean flag = false;25 Connection conn = null;26 PreparedStatement pstmt = null;27 String sql = "insert into customer_information value(null,?,?,?,?,?,?,?,?,?,?)";28 try{29 if(addEmpty(cus_id, cus_account_number, cus_sex,30 cus_personality_signature, cus_hobby, cus_psssworld,31 cus_email, cus_phone_number, cus_address, cus_head_portrait)) {32 conn =DBUtils.getCon();33 pstmt =(PreparedStatement) conn.prepareStatement(sql);34 pstmt.setString(1, cus_id);35 pstmt.setString(2, cus_account_number);36 pstmt.setString(3, cus_sex);37 pstmt.setString(4, cus_personality_signature);38 pstmt.setString(5, cus_hobby);39 pstmt.setString(6, cus_psssworld);40 pstmt.setString(7, cus_email);41 pstmt.setString(8, cus_phone_number);42 pstmt.setString(9, cus_address);43 pstmt.setString(10, cus_head_portrait);44

45 int num =pstmt.executeUpdate();46 if (num > 0) {47 System.out.println("新增数据成功");48 } else{49 System.out.println("新增数据失败");50 }51 } else{52 System.out.println("新增客户信息时，有必输项为空，新增失败");53 }54

55 } catch(Exception e) {56 e.printStackTrace();57 System.out.println("新增出现异常");58 } finally{59 DBUtils.closeCon(conn, pstmt, null);60 }61 returnflag;62 }63

64 /**按账号修改客户信息*/

65 public booleanupdateCustomerInfo(String cus_id, String cus_account_number,66 String cus_sex, String cus_personality_signature, String cus_hobby,67 String cus_psssworld, String cus_email, String cus_phone_number,68 String cus_address, String cus_head_portrait) {69 boolean flag = false;70 Connection conn = null;71 PreparedStatement pstmt = null;72 String sql = "update customer_information set cus_account_number=?,cus_sex=?,cus_personality_signature=?,cus_hobby=?,cus_psssworld=?,cus_email=?,cus_phone_number=?,cus_address=?,cus_head_portrait=?,cus_id=? where cus_id = ?";73 try{74 if (cus_id != null || !"".equals(cus_id)) {75 Customer_Information oldInfo =selectCustomerInfoById(cus_id);76 if (cus_id == null || "".equals(cus_id)) {77 cus_id =oldInfo.getCus_account_number();78 }79 if (cus_account_number == null || "".equals(cus_account_number)) {80 cus_account_number =oldInfo.getCus_account_number();81 }82 if (cus_sex == null || "".equals(cus_sex)) {83 cus_sex =oldInfo.getCus_sex();84 }85 if (cus_personality_signature == null

86 || "".equals(cus_personality_signature)) {87 cus_personality_signature =oldInfo88 .getCus_personality_signature();89 }90 if (cus_hobby == null || "".equals(cus_hobby)) {91 cus_hobby =oldInfo.getCus_hobby();92 }93 if (cus_psssworld == null || "".equals(cus_psssworld)) {94 cus_psssworld =oldInfo.getCus_psssworld();95 }96 if (cus_email == null || "".equals(cus_email)) {97 cus_email =oldInfo.getCus_email();98 }99 if (cus_phone_number == null || "".equals(cus_phone_number)) {100 cus_phone_number =oldInfo.getCus_phone_number();101 }102 if (cus_address == null || "".equals(cus_address)) {103 cus_address =oldInfo.getCus_address();104 }105 if (cus_head_portrait == null || "".equals(cus_head_portrait)) {106 cus_head_portrait =oldInfo.getCus_head_portrait();107 }108 conn =DBUtils.getCon();109 pstmt =(PreparedStatement) conn.prepareStatement(sql);110 pstmt.setString(1, cus_account_number);111 pstmt.setString(2, cus_sex);112 pstmt.setString(3, cus_personality_signature);113 pstmt.setString(4, cus_hobby);114 pstmt.setString(5, cus_psssworld);115 pstmt.setString(6, cus_email);116 pstmt.setString(7, cus_phone_number);117 pstmt.setString(8, cus_address);118 pstmt.setString(9, cus_head_portrait);119 pstmt.setString(10, cus_id);120 pstmt.setString(11, cus_id);121

122 int num =pstmt.executeUpdate();123 if (num > 0) {124 System.out.println("修改数据成功"

125 + "\n"

126 +selectCustomerInfoById(cus_id)127 .getCus_account_number() + "\t"

128 +selectCustomerInfoById(cus_id).getCus_address());129 flag = true;130 } else{131 System.out.println("修改数据失败");132 //flag = false;

133 }134 } else{135 System.out.println("[cus_id]客户ID不能为空,数据修改失败");136 }137 } catch(Exception e) {138 e.printStackTrace();139 } finally{140 DBUtils.closeCon(conn, pstmt, null);141 }142 returnflag;143 }144

145 /**删除指定ID的客户信息*/

146 public booleandeleteCustomerInfo(String cus_id) {147 boolean flag = false;148 Connection conn = null;149 PreparedStatement pstmt = null;150 String sql = "delete from customer_information where cus_id = ?";151 try{152 if (cus_id != null && !"".equals(cus_id)) {153 conn =DBUtils.getCon();154 pstmt =(PreparedStatement) conn.prepareStatement(sql);155 pstmt.setString(1, cus_id);156 int num =pstmt.executeUpdate();157 if (num > 0) {158 flag = true;159 System.out.println("删除指定客户数据成功");160 } else{161 flag = false;162 System.out.println("删除指定客户数据失败");163 }164 } else{165 System.out.println("[cus_id]客户ID不能为空,数据删除失败");166 }167 } catch(Exception e) {168 e.printStackTrace();169 } finally{170 DBUtils.closeCon(conn, pstmt, null);171 }172 returnflag;173 }174

175 /**查询所有客户信息*/

176 public ListselectCustomerInfo() {177 List list_cus = new ArrayList();178 Connection conn = null;179 PreparedStatement pstmt = null;180 ResultSet rs = null;181 String sql = "select * from customer_information";182 if (sql != null) {183 try{184 conn =DBUtils.getCon();185 pstmt =(PreparedStatement) conn.prepareStatement(sql);186 rs =pstmt.executeQuery();187 if (rs != null) {188 System.out.println("进入result数据集写入方法");189 while(rs.next()) {190 Customer_Information cus = newCustomer_Information();191 cus.setCus_id(rs.getString(2));192 cus.setCus_account_number(rs.getString(3));193 cus.setCus_sex(rs.getString(4));194 cus.setCus_personality_signature(rs.getString(5));195 cus.setCus_hobby(rs.getString(6));196 cus.setCus_psssworld(rs.getString(7));197 cus.setCus_email(rs.getString(8));198 cus.setCus_phone_number(rs.getString(9));199 cus.setCus_address(rs.getString(10));200 cus.setCus_head_portrait(rs.getString(11));201 list_cus.add(cus);202 }203 System.out.println("将所有客户信息装入List集合后，集合长度是:"

204 +list_cus.size());205 } else{206 System.out.println("查询的Resultset为空");207 }208 } catch(Exception e) {209 e.printStackTrace();210 System.out.println("查询所有客户信息出现异常");211 } finally{212 DBUtils.closeCon(conn, pstmt, rs);213 }214 }215 returnlist_cus;216 }217

218 /**按账号查询指定客户信息*/

219 publicCustomer_Information selectCustomerInfoById(String cus_id) {220 Customer_Information cus = null;221 Connection conn = null;222 PreparedStatement pstmt = null;223 ResultSet rs = null;224 String sql = "select * from customer_information where cus_id = ?";225 try{226 conn =DBUtils.getCon();227 pstmt =(PreparedStatement) conn.prepareStatement(sql);228 pstmt.setString(1, cus_id);229 rs =pstmt.executeQuery();230 if (rs != null || !"".equals(rs)) {231 System.out.println("查到指定账号信息");232 while(rs.next()) {233 cus = newCustomer_Information();234 cus.setCus_id(rs.getString(2));235 cus.setCus_account_number(rs.getString(3));236 cus.setCus_sex(rs.getString(4));237 cus.setCus_personality_signature(rs.getString(5));238 cus.setCus_hobby(rs.getString(6));239 cus.setCus_psssworld(rs.getString(7));240 cus.setCus_email(rs.getString(8));241 cus.setCus_phone_number(rs.getString(9));242 cus.setCus_address(rs.getString(10));243 cus.setCus_head_portrait(rs.getString(11));244 }245 } else{246 System.out.println("未找到指定账号的客户信息");247 }248 } catch(Exception e) {249 e.printStackTrace();250 } finally{251 DBUtils.closeCon(conn, pstmt, rs);252 }253 returncus;254 }255

256 /**验证输入的客户必输字段的信息数据是否为空*/

257 public booleanaddEmpty(String cus_id, String cus_account_number,258 String cus_sex, String cus_personality_signature, String cus_hobby,259 String cus_psssworld, String cus_email, String cus_phone_number,260 String cus_address, String cus_head_portrait) {261 boolean flag = false;262 if (cus_id != null || !"".equals(cus_id)) {263 flag = true;264 } else{265 flag = false;266 System.out.println("[cus_id]为空");267 }268

269 if (cus_account_number != null || !"".equals(cus_account_number)) {270 flag = true;271 } else{272 flag = false;273 System.out.println("[cus_account_number]为空");274 }275

276 if (cus_sex != null || !"".equals(cus_sex)) {277 flag = true;278 } else{279 flag = false;280 System.out.println("[cus_sex]为空");281 }282

283 if (cus_personality_signature != null

284 || !"".equals(cus_personality_signature)) {285 flag = true;286 } else{287 flag = false;288 System.out.println("[cus_personality_signature]为空");289 }290

291 if (cus_hobby != null || !"".equals(cus_hobby)) {292 flag = true;293 } else{294 flag = false;295 System.out.println("[cus_hobby]为空");296 }297

298 if (cus_psssworld != null || !"".equals(cus_psssworld)) {299 flag = true;300 } else{301 flag = false;302 System.out.println("[cus_psssworld]为空");303 }304

305 if (cus_email != null || !"".equals(cus_email)) {306 flag = true;307 } else{308 flag = false;309 System.out.println("[cus_email]为空");310 }311

312 if (cus_phone_number != null || !"".equals(cus_phone_number)) {313 flag = true;314 } else{315 flag = false;316 System.out.println("[cus_phone_number]为空");317 }318

319 if (cus_address != null || !"".equals(cus_address)) {320 flag = true;321 } else{322 flag = false;323 System.out.println("[cus_address]为空");324 }325 returnflag;326 }327 }