mysql sun.jdbc_Java 学习笔记 一 -- JDBC的SUN标准规范

1 import java.sql.*;2 import java.util.*;3

4 classDb{5 String url = "jdbc:mysql://localhost:3306/dbtao";6 String username = "root";7 String password = "123465";8 String sql;9 PreparedStatement pst = null;10 ResultSet rs = null;11 Connection conn = null;12

13 publicDb(){14 this("");15 }16 publicDb(String sql){17 this.sql =sql;18 }19 public booleanDbclose(){20 boolean f = true;21 if(rs != null){22 try{23 rs.close();24 }catch(SQLException e) {25 e.printStackTrace();26 f = false;27 }28 rs = null;29 }30 if(pst != null){31 try{32 pst.close();33 }catch(SQLException e) {34 e.printStackTrace();35 f = false;36 }37 pst = null;38 }39 if(conn != null){40 try{41 conn.close();42 }catch(SQLException e) {43 e.printStackTrace();44 f = false;45 }46 conn = null;47 }48 returnf;49 }50

51 public booleanDbPrepared(){52 if(sql.isEmpty()){53 System.out.println("SQL语句未设置!");54 return false;55 }56 try{57 Class.forName("com.mysql.jdbc.Driver");58 conn =DriverManager.getConnection(url, username, password);59 pst =conn.prepareStatement(sql);60 return true;61 } catch(Exception e) {62 e.printStackTrace();63 Dbclose();64 return false;65 }66 }67 }68

69 classEmp {70 public intempno;71 publicString ename;72 publicString job;73 public intmgr;74 publicTimestamp hiredate;75 public doublesal;76 public doublecomm;77 public intdeptno;78

79 public Emp(int empno, String ename, String job, int mgr, Timestamp hiredate, double sal, double comm, intdeptno){80 this.empno =empno;81 this.ename =ename;82 this.job =job;83 this.mgr =mgr;84 this.hiredate =hiredate;85 this.sal =sal;86 this.comm =comm;87 this.deptno =deptno;88 }89

90 publicEmp(){91 this(0,null,null,0,null,0,0,0);92 }93

94 publicString toString(){95 return empno + "\t" + ename + "\t" + job + "\t" + mgr + "\t" +

96 hiredate + "\t" + sal + "\t" + comm + "\t" +deptno;97 }98

99 public staticDb db;100 public static Emp getByEmpno(intempno){101 db = new Db("SELECT * FROM emp WHERE empno=?");102 if(db.DbPrepared()){103 try{104 db.pst.setObject(1, empno);105 db.rs =db.pst.executeQuery();106 if(db.rs.next()){107 Emp emp = newEmp();108 emp.empno = db.rs.getInt("empno");109 emp.ename = db.rs.getString("ename");110 emp.job = db.rs.getString("job");111 emp.mgr = db.rs.getInt("mgr");112 emp.hiredate = db.rs.getTimestamp("hiredate");113 emp.sal = db.rs.getDouble("sal");114 emp.comm = db.rs.getDouble("comm");115 emp.deptno = db.rs.getInt("deptno");116

117 db.Dbclose();118 returnemp;119 }120

121 } catch(SQLException e) {122 db.Dbclose();123 e.printStackTrace();124 System.out.println(e.getMessage());125 return null;126 }127 }128 System.out.println("未查询到该编号的员工。");129 return null;130 }131

132 public static ListgetByEname(String ename){133 db = new Db("SELECT * FROM emp WHERE ename like ?");134 if(db.DbPrepared()){135 try{136 db.pst.setObject(1, "%"+ename+"%");137 db.rs =db.pst.executeQuery();138 List list = new LinkedList();139 while(db.rs.next()){140 Emp emp = newEmp();141 emp.empno = db.rs.getInt("empno");142 emp.ename = db.rs.getString("ename");143 emp.job = db.rs.getString("job");144 emp.mgr = db.rs.getInt("mgr");145 emp.hiredate = db.rs.getTimestamp("hiredate");146 emp.sal = db.rs.getDouble("sal");147 emp.comm = db.rs.getDouble("comm");148 emp.deptno = db.rs.getInt("deptno");149 list.add(emp);150 }151 db.Dbclose();152 returnlist;153

154 } catch(SQLException e) {155 db.Dbclose();156 e.printStackTrace();157 System.out.println(e.getMessage());158 return null;159 }160 }161 System.out.println("未查询到该编号的员工。");162 return null;163 }164

165 public static intadd(Emp emp){166 db = new Db("insert into emp values(?,?,?,?,?,?,?,?)");167 if(db.DbPrepared()){168 try{169 db.pst.setObject(1, emp.empno);170 db.pst.setObject(2, emp.ename);171 db.pst.setObject(3, emp.job);172 db.pst.setObject(4, emp.mgr);173 db.pst.setObject(5, emp.hiredate);174 db.pst.setObject(6, emp.sal);175 db.pst.setObject(7, emp.comm);176 db.pst.setObject(8, emp.deptno);177 int t =db.pst.executeUpdate();178

179 db.Dbclose();180 returnt;181

182 } catch(SQLException e) {183 db.Dbclose();184 e.printStackTrace();185 System.out.println(e.getMessage());186 return 0;187 }188 }189 System.out.println("插入失败。");190 return 0;191 }192

193 public static int updateByEmpno(Emp emp, intempno){194 db = new Db("update emp set ename=?,job=?,mgr=?,hiredate=?,"+

195 "sal=?,comm=?,deptno=? where empno=?");196 if(db.DbPrepared()){197 try{198 db.pst.setObject(8, emp.empno);199 db.pst.setObject(1, emp.ename);200 db.pst.setObject(2, emp.job);201 db.pst.setObject(3, emp.mgr);202 db.pst.setObject(4, emp.hiredate);203 db.pst.setObject(5, emp.sal);204 db.pst.setObject(6, emp.comm);205 db.pst.setObject(7, emp.deptno);206 int t =db.pst.executeUpdate();207

208 db.Dbclose();209 returnt;210

211 } catch(SQLException e) {212 db.Dbclose();213 e.printStackTrace();214 System.out.println(e.getMessage());215 return 0;216 }217 }218 System.out.println("更新失败。");219 return 0;220 }221 public static int deleteByEmpno(intempno){222 db = new Db("delete from emp where empno=?");223 if(db.DbPrepared()){224 try{225 db.pst.setObject(1, empno);226 int t =db.pst.executeUpdate();227

228 db.Dbclose();229 returnt;230

231 } catch(SQLException e) {232 db.Dbclose();233 e.printStackTrace();234 System.out.println(e.getMessage());235 return 0;236 }237 }238 System.out.println("删除失败。");239 return 0;240 }241 }242

243 public classJDBC {244

245 public static voidmain(String[] args) {246 String head = "编号\t姓名\t职位\t领导编号\t入职时间\t\t\t薪资\t提成\t部门\n";247

248 Emp e = newEmp();249 e.empno = 1;250 e.ename = "dks";251 e.job = "dj";252 e.mgr = 16;253 e.hiredate = newTimestamp(System.currentTimeMillis());254 e.sal = 1.1;255 e.comm = 1.2;256 e.deptno = 0;257

258 System.out.println("插入 "+Emp.add(e)+" 条数据");259

260 System.out.println(head);261 System.out.println(Emp.getByEmpno(1));262

263 e.deptno = 5;264 System.out.println("更新 "+Emp.updateByEmpno(e, 1)+" 条数据");265

266 List list = Emp.getByEname("s");267 System.out.println(head);268 for(int i = 0; i < list.size(); i++){269 System.out.println(list.get(i));270 }271

272 System.out.println("删除 "+Emp.deleteByEmpno(1)+" 条数据");273 list = Emp.getByEname("s");274 System.out.println(head);275 for(int i = 0; i < list.size(); i++){276 System.out.println(list.get(i));277 }278

279 }280

281 }