一、数据库准备和Java环境配置
（一）安装MySQL、Navicat、JDK、Eclipse
（二）配置Java环境变量
（三）导入 jar（mysql-connection-java -> Build Path）
二、SUN标准规范
（一）加载驱动
将 Driver 类加载到 jvm 内存中，初始化驱动管理器 DriverManager
Class.forName("com.mysql.jdbc.Driver"); //DriverManager
（二）通过驱动管理器获取数据库连接
1String url = "jdbc:mysql://127.0.0.1:3306/databaseDemo?useSSL=false"; 2String user = "root"; 3String password = "123456";
（三）获取数据库连接，执行 SQL语句并获取结果集
1Connection conn = DriverManager.getConnection(url, user, password); 2 3//存在SQL注入 4//String sql = "select * from table where id=" + id; 5//Statement st = conn.createStatement(); 6//ResultSet rs = st.executeQuery(sql); 7 8String sql = "select * from table where id=?"; 9PreparedStatement pst = conn.preparedStatement(sql); 10st.setObject(1, id);//int rs = pst.executeUpdate(); //增删改 11ResultSet rs = pst.executeQuery(); //查
（四）对结果集进行处理(获取日期时间时用时间戳 Timestamp)
1while(rs.next()){ 2 System.out.println(rs.getInt("id")); 3 System.out.println(rs.getString("name")); 4 System.out.println(rs.getTimestamp("datetime")); //getDate 只能获取年月日，getTime 只能获取时分秒 5}
（五）释放数据库连接(后开的先关)
1//前面还是try一下，SUN标准规范就是这么恶心 2finally{ 3 if(rs != null){ 4 try{ 5 rs.close(); 6 }catch(SQLException e) { 7 e.printStackTrace(); 8 } 9 rs = null; 10 } 11 if(stmt != null){ 12 try{ 13 stmt.close(); 14 }catch(SQLException e) { 15 e.printStackTrace(); 16 } 17 stmt = null; 18 } 19 if(conn != null){ 20 try{ 21 conn.close(); 22 }catch(SQLException e) { 23 e.printStackTrace(); 24 } 25 conn = null; 26 } 27}
三、一些需要注意的地方
（一）Java传入MySQL datetime类型
1//java传入String 2st.setString(1, "2019-05-28 20:10:10"); 3 4//java传入Date 5//只改日期 6st.setDate(1, new java.sql.Date(System.currentTimeMillis())); 7//改日期和时刻 8st.setTimestamp(1, new Timestamp(System.currentTimeMillis())); 9 10//Java 中Timestamp 类型传入MySQL数据库5.7以上版本时 11Timestamp t = new Timestamp(System.currentTimeMillis()); 12//此时 t 的默认toString方法因为有毫秒数，高版本MySQL会限制解析 13//传入时必须用（"yyy-MM-dd hh-mm-ss"），例： 14t.toLocaleString() 15t.toString().substring(0, 19)
（二）PreparedStatement 使用 like 模糊查询
使用 PreparedStatement 进行模糊查找时，不能直接在 sql语句中写入 "---like '%?%'"，需要在 set值时添加 %符
1String sql = "SELECT * FROM emp WHERE ename like ?"; 2pst.setObject(1, "%"+ename+"%");
四、SUN标准规范 实例
（一）源码
1 1 import java.sql.*; 2 2 import java.util.*; 3 3 4 4 class Db{ 5 5 String url = "jdbc:mysql://localhost:3306/dbtao"; 6 6 String username = "root"; 7 7 String password = "123465"; 8 8 String sql; 9 9 PreparedStatement pst = null; 10 10 ResultSet rs = null; 11 11 Connection conn = null; 12 12 13 13 public Db(){ 14 14 this(""); 15 15 } 16 16 public Db(String sql){ 17 17 this.sql = sql; 18 18 } 19 19 public boolean Dbclose(){ 20 20 boolean f = true; 21 21 if(rs != null){ 22 22 try{ 23 23 rs.close(); 24 24 }catch(SQLException e) { 25 25 e.printStackTrace(); 26 26 f = false; 27 27 } 28 28 rs = null; 29 29 } 30 30 if(pst != null){ 31 31 try{ 32 32 pst.close(); 33 33 }catch(SQLException e) { 34 34 e.printStackTrace(); 35 35 f = false; 36 36 } 37 37 pst = null; 38 38 } 39 39 if(conn != null){ 40 40 try{ 41 41 conn.close(); 42 42 }catch(SQLException e) { 43 43 e.printStackTrace(); 44 44 f = false; 45 45 } 46 46 conn = null; 47 47 } 48 48 return f; 49 49 } 50 50 51 51 public boolean DbPrepared(){ 52 52 if(sql.isEmpty()){ 53 53 System.out.println("SQL语句未设置!"); 54 54 return false; 55 55 } 56 56 try { 57 57 Class.forName("com.mysql.jdbc.Driver"); 58 58 conn = DriverManager.getConnection(url, username, password); 59 59 pst = conn.prepareStatement(sql); 60 60 return true; 61 61 } catch (Exception e) { 62 62 e.printStackTrace(); 63 63 Dbclose(); 64 64 return false; 65 65 } 66 66 } 67 67 } 68 68 69 69 class Emp { 70 70 public int empno; 71 71 public String ename; 72 72 public String job; 73 73 public int mgr; 74 74 public Timestamp hiredate; 75 75 public double sal; 76 76 public double comm; 77 77 public int deptno; 78 78 79 79 public Emp(int empno, String ename, String job, int mgr, Timestamp hiredate, double sal, double comm, int deptno){ 80 80 this.empno = empno; 81 81 this.ename = ename; 82 82 this.job = job; 83 83 this.mgr = mgr; 84 84 this.hiredate = hiredate; 85 85 this.sal = sal; 86 86 this.comm = comm; 87 87 this.deptno = deptno; 88 88 } 89 89 90 90 public Emp(){ 91 91 this(0,null,null,0,null,0,0,0); 92 92 } 93 93 94 94 public String toString(){ 95 95 return empno + "\t" + ename + "\t" + job + "\t" + mgr + "\t" + 96 96 hiredate + "\t" + sal + "\t" + comm + "\t" + deptno; 97 97 } 98 98 99 99 public static Db db; 100100 public static Emp getByEmpno(int empno){ 101101 db = new Db("SELECT * FROM emp WHERE empno=?"); 102102 if(db.DbPrepared()){ 103103 try { 104104 db.pst.setObject(1, empno); 105105 db.rs = db.pst.executeQuery(); 106106 if(db.rs.next()){ 107107 Emp emp = new Emp(); 108108 emp.empno = db.rs.getInt("empno"); 109109 emp.ename = db.rs.getString("ename"); 110110 emp.job = db.rs.getString("job"); 111111 emp.mgr = db.rs.getInt("mgr"); 112112 emp.hiredate = db.rs.getTimestamp("hiredate"); 113113 emp.sal = db.rs.getDouble("sal"); 114114 emp.comm = db.rs.getDouble("comm"); 115115 emp.deptno = db.rs.getInt("deptno"); 116116 117117 db.Dbclose(); 118118 return emp; 119119 } 120120 121121 } catch (SQLException e) { 122122 db.Dbclose(); 123123 e.printStackTrace(); 124124 System.out.println(e.getMessage()); 125125 return null; 126126 } 127127 } 128128 System.out.println("未查询到该编号的员工。"); 129129 return null; 130130 } 131131 132132 public static List<Emp> getByEname(String ename){ 133133 db = new Db("SELECT * FROM emp WHERE ename like ?"); 134134 if(db.DbPrepared()){ 135135 try { 136136 db.pst.setObject(1, "%"+ename+"%"); 137137 db.rs = db.pst.executeQuery(); 138138 List<Emp> list = new LinkedList<Emp>(); 139139 while(db.rs.next()){ 140140 Emp emp = new Emp(); 141141 emp.empno = db.rs.getInt("empno"); 142142 emp.ename = db.rs.getString("ename"); 143143 emp.job = db.rs.getString("job"); 144144 emp.mgr = db.rs.getInt("mgr"); 145145 emp.hiredate = db.rs.getTimestamp("hiredate"); 146146 emp.sal = db.rs.getDouble("sal"); 147147 emp.comm = db.rs.getDouble("comm"); 148148 emp.deptno = db.rs.getInt("deptno"); 149149 list.add(emp); 150150 } 151151 db.Dbclose(); 152152 return list; 153153 154154 } catch (SQLException e) { 155155 db.Dbclose(); 156156 e.printStackTrace(); 157157 System.out.println(e.getMessage()); 158158 return null; 159159 } 160160 } 161161 System.out.println("未查询到该编号的员工。"); 162162 return null; 163163 } 164164 165165 public static int add(Emp emp){ 166166 db = new Db("insert into emp values(?,?,?,?,?,?,?,?)"); 167167 if(db.DbPrepared()){ 168168 try { 169169 db.pst.setObject(1, emp.empno); 170170 db.pst.setObject(2, emp.ename); 171171 db.pst.setObject(3, emp.job); 172172 db.pst.setObject(4, emp.mgr); 173173 db.pst.setObject(5, emp.hiredate); 174174 db.pst.setObject(6, emp.sal); 175175 db.pst.setObject(7, emp.comm); 176176 db.pst.setObject(8, emp.deptno); 177177 int t = db.pst.executeUpdate(); 178178 179179 db.Dbclose(); 180180 return t; 181181 182182 } catch (SQLException e) { 183183 db.Dbclose(); 184184 e.printStackTrace(); 185185 System.out.println(e.getMessage()); 186186 return 0; 187187 } 188188 } 189189 System.out.println("插入失败。"); 190190 return 0; 191191 } 192192 193193 public static int updateByEmpno(Emp emp, int empno){ 194194 db = new Db("update emp set ename=?,job=?,mgr=?,hiredate=?,"+ 195195 "sal=?,comm=?,deptno=? where empno=?"); 196196 if(db.DbPrepared()){ 197197 try { 198198 db.pst.setObject(8, emp.empno); 199199 db.pst.setObject(1, emp.ename); 200200 db.pst.setObject(2, emp.job); 201201 db.pst.setObject(3, emp.mgr); 202202 db.pst.setObject(4, emp.hiredate); 203203 db.pst.setObject(5, emp.sal); 204204 db.pst.setObject(6, emp.comm); 205205 db.pst.setObject(7, emp.deptno); 206206 int t = db.pst.executeUpdate(); 207207 208208 db.Dbclose(); 209209 return t; 210210 211211 } catch (SQLException e) { 212212 db.Dbclose(); 213213 e.printStackTrace(); 214214 System.out.println(e.getMessage()); 215215 return 0; 216216 } 217217 } 218218 System.out.println("更新失败。"); 219219 return 0; 220220 } 221221 public static int deleteByEmpno(int empno){ 222222 db = new Db("delete from emp where empno=?"); 223223 if(db.DbPrepared()){ 224224 try { 225225 db.pst.setObject(1, empno); 226226 int t = db.pst.executeUpdate(); 227227 228228 db.Dbclose(); 229229 return t; 230230 231231 } catch (SQLException e) { 232232 db.Dbclose(); 233233 e.printStackTrace(); 234234 System.out.println(e.getMessage()); 235235 return 0; 236236 } 237237 } 238238 System.out.println("删除失败。"); 239239 return 0; 240240 } 241241 } 242242 243243 public class JDBC { 244244 245245 public static void main(String[] args) { 246246 String head = "编号\t姓名\t职位\t领导编号\t入职时间\t\t\t薪资\t提成\t部门\n"; 247247 248248 Emp e = new Emp(); 249249 e.empno = 1; 250250 e.ename = "dks"; 251251 e.job = "dj"; 252252 e.mgr = 16; 253253 e.hiredate = new Timestamp(System.currentTimeMillis()); 254254 e.sal = 1.1; 255255 e.comm = 1.2; 256256 e.deptno = 0; 257257 258258 System.out.println("插入 "+Emp.add(e)+" 条数据"); 259259 260260 System.out.println(head); 261261 System.out.println(Emp.getByEmpno(1)); 262262 263263 e.deptno = 5; 264264 System.out.println("更新 "+Emp.updateByEmpno(e, 1)+" 条数据"); 265265 266266 List<Emp> list = Emp.getByEname("s"); 267267 System.out.println(head); 268268 for(int i = 0; i < list.size(); i++){ 269269 System.out.println(list.get(i)); 270270 } 271271 272272 System.out.println("删除 "+Emp.deleteByEmpno(1)+" 条数据"); 273273 list = Emp.getByEname("s"); 274274 System.out.println(head); 275275 for(int i = 0; i < list.size(); i++){ 276276 System.out.println(list.get(i)); 277277 } 278278 279279 } 280280 281281 }
（二）运行结果
