一、数据库准备和Java环境配置
(一)安装MySQL、Navicat、JDK、Eclipse
(二)配置Java环境变量
(三)导入 jar(mysql-connection-java -> Build Path)
二、SUN标准规范
(一)加载驱动
将 Driver 类加载到 jvm 内存中,初始化驱动管理器 DriverManager
Class.forName("com.mysql.jdbc.Driver"); //DriverManager
(二)通过驱动管理器获取数据库连接
String url = "jdbc:mysql://127.0.0.1:3306/databaseDemo?useSSL=false";
String user = "root";
String password = "123456";
(三)获取数据库连接,执行 SQL语句并获取结果集
Connection conn = DriverManager.getConnection(url, user, password);
//存在SQL注入
//String sql = "select * from table where id=" + id;
//Statement st = conn.createStatement();
//ResultSet rs = st.executeQuery(sql);
String sql = "select * from table where id=?";
PreparedStatement pst = conn.preparedStatement(sql);
st.setObject(1, id);//int rs = pst.executeUpdate(); //增删改
ResultSet rs = pst.executeQuery(); //查
(四)对结果集进行处理(获取日期时间时用时间戳 Timestamp)
while(rs.next()){
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getTimestamp("datetime")); //getDate 只能获取年月日,getTime 只能获取时分秒
}
(五)释放数据库连接(后开的先关)
//前面还是try一下,SUN标准规范就是这么恶心
finally{
if(rs != null){
try{
rs.close();
}catch(SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt != null){
try{
stmt.close();
}catch(SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null){
try{
conn.close();
}catch(SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
三、一些需要注意的地方
(一)Java传入MySQL datetime类型
//java传入String
st.setString(1, "2019-05-28 20:10:10");
//java传入Date
//只改日期
st.setDate(1, new java.sql.Date(System.currentTimeMillis()));
//改日期和时刻
st.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
//Java 中Timestamp 类型传入MySQL数据库5.7以上版本时
Timestamp t = new Timestamp(System.currentTimeMillis());
//此时 t 的默认toString方法因为有毫秒数,高版本MySQL会限制解析
//传入时必须用("yyy-MM-dd hh-mm-ss"),例:
t.toLocaleString()
t.toString().substring(0, 19)
(二)PreparedStatement 使用 like 模糊查询
使用 PreparedStatement 进行模糊查找时,不能直接在 sql语句中写入 "---like '%?%'",需要在 set值时添加 %符
String sql = "SELECT * FROM emp WHERE ename like ?";
pst.setObject(1, "%"+ename+"%");
四、SUN标准规范 实例
(一)源码
1 import java.sql.*;
2 import java.util.*;
3
4 class Db{
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 public Db(){
14 this("");
15 }
16 public Db(String sql){
17 this.sql = sql;
18 }
19 public boolean Dbclose(){
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 return f;
49 }
50
51 public boolean DbPrepared(){
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 class Emp {
70 public int empno;
71 public String ename;
72 public String job;
73 public int mgr;
74 public Timestamp hiredate;
75 public double sal;
76 public double comm;
77 public int deptno;
78
79 public Emp(int empno, String ename, String job, int mgr, Timestamp hiredate, double sal, double comm, int deptno){
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 public Emp(){
91 this(0,null,null,0,null,0,0,0);
92 }
93
94 public String toString(){
95 return empno + "\t" + ename + "\t" + job + "\t" + mgr + "\t" +
96 hiredate + "\t" + sal + "\t" + comm + "\t" + deptno;
97 }
98
99 public static Db db;
100 public static Emp getByEmpno(int empno){
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 = new Emp();
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 return emp;
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 List<Emp> getByEname(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<Emp> list = new LinkedList<Emp>();
139 while(db.rs.next()){
140 Emp emp = new Emp();
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 return list;
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 int add(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 return t;
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, int empno){
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 return t;
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(int empno){
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 return t;
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 class JDBC {
244
245 public static void main(String[] args) {
246 String head = "编号\t姓名\t职位\t领导编号\t入职时间\t\t\t薪资\t提成\t部门\n";
247
248 Emp e = new Emp();
249 e.empno = 1;
250 e.ename = "dks";
251 e.job = "dj";
252 e.mgr = 16;
253 e.hiredate = new Timestamp(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<Emp> 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 }