2018-09-07 11:54:30 1142瀏覽
今天扣丁學(xué)堂Java培訓(xùn)老師為了幫助大家熟練應(yīng)用JDBC編程，下面通過一個綜合案例來講解JDBC的基本操作，確保大家能夠深刻理解JDBC的增、刪、改、查，靈活利用JDBC完成對數(shù)據(jù)庫的各項(xiàng)操作。
1 package com.qfedu.jdbc.domain;
2 public class Student {
3 private int id;
4 private String sname;
5 private String age;
6 private String course;
7 public Student() {
8 super();
9 }
10 public Student(int id, String sname, String age, String course) {
11 super();
12 this.id = id;
13 this.sname = sname;
14 this.age = age;
15 this.course = course;
16 }
17 public int getId() {
18 return id;
19 }
20 public void setId(int id) {
21 this.id = id;
22 }
23 public String getSname() {
24 return sname;
25 }
26 public void setSname(String sname) {
27 this.sname = sname;
28 }
29 public String getAge() {
30 return age;
31 }
32 public void setAge(String age) {
33 this.age = age;
34 }
35 public String getCourse() {
36 return course;
37 }
38 public void setCourse(String course) {
39 this.course = course;
40 }
41 @Override
42 public String toString() {
43 return "Student [id=" + id + ", sname=" + sname + ", age=" + age +
44 ", course=" + course + "]";
45 }
46 }
1 import java.sql.Connection;
2 import java.sql.DriverManager;
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 public class JDBCUtils {
7 private static String url = "jdbc:mysql://localhost:3306/chapter01";
8 private static String user = "root";
9 private static String pass = "root";
10 private static Connection conn = null;
11 static{
12 try{
13 Class.forName("com.mysql.jdbc.Driver");
14 conn = DriverManager.getConnection(url, user, pass);
15 }catch(Exception ex){
16 ex.printStackTrace();
17 //數(shù)據(jù)庫連接失敗,直接停止程序,拋出運(yùn)行時期異常
18 throw new RuntimeException("數(shù)據(jù)庫連接失敗");
19 }
20 }
21 //獲取鏈接
22 public static Connection getConnecton(){
23 return conn;
24 }
25 //釋放資源
26 public static void release(Connection conn,PreparedStatement
27 pstat,ResultSet rs) {
28 if (rs!=null) {
29 try {
30 rs.close();
31 } catch (SQLException e) {
32 e.printStackTrace();
33 }
34 rs=null;
35 }
36 release(conn,pstat);
37 }
38 public static void release(Connection conn,PreparedStatement pstat) {
39 if (pstat!=null) {
40 try {
41 pstat.close();
42 } catch (SQLException e) {
43 e.printStackTrace();
44 }
45 pstat=null;
46 }
47 if (conn !=null) {
48 try {
49 conn.close();
50 } catch (SQLException e) {
51 e.printStackTrace();
52 }
53 conn=null;
54 }
55 }
56 }
1 package com.qfedu.jdbc.dao;
2 import java.sql.Connection;
3 import java.sql.PreparedStatement;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.util.ArrayList;
7 import com.qfedu.jdbc.domain.Student;
8 import com.qfedu.jdbc.utils.JDBCUtils;
9 public class StudentDao {
10 //添加Student
11 public boolean insert(Student student){
12 boolean flag =false ;
13 PreparedStatement pstat = null;
14 Connection conn = JDBCUtils.getConnecton();
15 String sql ="insert into student(sid,sname,age,course)
16 values(?,?,?,?)";
17 try {
18 pstat = conn.prepareStatement(sql);
19 pstat.setInt(1, student.getSid());
20 pstat.setString(2, student.getSname());
21 pstat.setString(3, student.getAge());
22 pstat.setString(4, student.getCourse());
23 int num = pstat.executeUpdate();
24 if (num>0) {
25 flag =true;
26 }
27 } catch (SQLException e) {
28 e.printStackTrace();
29 }finally {
30 JDBCUtils.release(conn, pstat);
31 }
32 return flag;
33 }
34 //更新Student
35 public boolean update(Student student){
36 boolean flag =false ;
37 PreparedStatement pstat = null;
38 Connection conn = JDBCUtils.getConnecton();
39 String sql ="update student set sname=?,age=?,course=? where
40 sid=? ";
41 try {
42 pstat = conn.prepareStatement(sql);
43 pstat.setInt(4, student.getSid());
44 pstat.setString(1, student.getSname());
45 pstat.setString(2, student.getAge());
46 pstat.setString(3, student.getCourse());
47 int num = pstat.executeUpdate();
48 if (num>0) {
49 flag =true;
50 }
51 } catch (SQLException e) {
52 e.printStackTrace();
53 }finally {
54 JDBCUtils.release(conn, pstat);
55 }
56 return flag;
57 }
58 //刪除Student
59 public boolean delete(Student student){
60 boolean flag =false ;
61 PreparedStatement pstat = null;
62 Connection conn = JDBCUtils.getConnecton();
63 String sql ="delete from student where sid=?";
64 try {
65 pstat = conn.prepareStatement(sql);
66 pstat.setInt(1, student.getSid());
67 int num = pstat.executeUpdate();
68 if (num>0) {
69 flag =true;
70 }
71 } catch (SQLException e) {
72 e.printStackTrace();
73 }finally {
74 JDBCUtils.release(conn, pstat);
75 }
76 return flag;
77 }
78 //查詢所有Student
79 public ArrayListselectAll(){
80 PreparedStatement pstat = null;
81 Connection conn = JDBCUtils.getConnecton();
82 String sql ="select * from student";
83 ArrayListlist = new ArrayList<>();
84 try {
85 pstat = conn.prepareStatement(sql);
86 ResultSet rs = pstat.executeQuery(sql);
87 while (rs.next()) {
88 Student newStudent = new Student();
89 newStudent.setSid(rs.getInt("sid"));
90 newStudent.setSname( rs.getString("sname"));
91 newStudent.setAge(rs.getString("age"));
92 newStudent.setCourse(rs.getString("course"));
93 list.add(newStudent);
94 }
95 } catch (SQLException e) {
96 e.printStackTrace();
97 }finally {
98 JDBCUtils.release(conn, pstat);
99 }
100 return list;
101 }
102 //查詢單個Student
103 public Student selectOne(Student student){
104 PreparedStatement pstat = null;
105 Connection conn = JDBCUtils.getConnecton();
106 String sql ="select * from student where sid = ? ";
107 Student newStudent = new Student();
108 try {
109 pstat = conn.prepareStatement(sql);
110 pstat.setInt(1, student.getSid());
111 ResultSet rs = pstat.executeQuery();
112 while (rs.next()) {
113 newStudent.setSid(rs.getInt("sid"));
114 newStudent.setSname( rs.getString("sname"));
115 newStudent.setAge(rs.getString("age"));
116 newStudent.setCourse(rs.getString("course"));
117 }
118 } catch (SQLException e) {
119 e.printStackTrace();
120 }finally {
121 JDBCUtils.release(conn, pstat);
122 }
123 return newStudent;
124 }
125 }
1 package com.qfedu.jdbc.test;
2 import com.qfedu.jdbc.dao.StudentDao;
3 import com.qfedu.jdbc.domain.Student;
4 public class TestInsert {
5 public static void main(String[] args) {
6 StudentDao studentDao = new StudentDao();
7 Student student = new Student();
8 student.setSid(10);
9 student.setSname("sunqi");
10 student.setAge("23");
11 student.setCourse("python");
12 studentDao.insert(student);
13 }
14 }
執(zhí)行TestInsert類，向數(shù)據(jù)表student中插入數(shù)據(jù)，通過SQL語句測試數(shù)據(jù)是否添加成功，執(zhí)行結(jié)果如下所示。
mysql> SELECT * FROM STUDENT;
+-----+-------------+------+--------+
| sid | sname | age | course |
+-----+-------------+------+--------+
| 1 | zhangsan | 20 | Java |
| 2 | lisi | 21 | Java |
| 3 | wangwu | 22 | Java |
| 4 | zhaoliu | 22 | Python |
| 5 | sunqi | 22 | PHP |
| 6 | zhangsansan | 22 | PHP |
| 7 | name0 | 22 | Java |
| 8 | name1 | 22 | Java |
| 9 | name2 | 22 | Java |
| 10 | sunqi | 23 | Python |
+-----+-------------+------+--------+
10 rows in set (0.00 sec)
1 package com.qfedu.jdbc.test;
2 import com.qfedu.jdbc.dao.StudentDao;
3 import com.qfedu.jdbc.domain.Student;
4 public class TestUpdate {
5 public static void main(String[] args) {
6 StudentDao studentDao = new StudentDao();
7 Student student = new Student();
8 student.setSid(10);
9 student.setSname("zhouba");
10 student.setAge("24");
11 student.setCourse("Java");
12 studentDao.update(student);
13 }
14 }
執(zhí)行TestUpdate類，更新數(shù)據(jù)庫中sid值為10的數(shù)據(jù)信息，通過SQL語句測試數(shù)據(jù)是否更新成功，執(zhí)行結(jié)果如下所示。
mysql> SELECT * FROM STUDENT;
+-----+-------------+------+--------+
| sid | sname | age | course |
+-----+-------------+------+--------+
| 1 | zhangsan | 20 | Java |
| 2 | lisi | 21 | Java |
| 3 | wangwu | 22 | Java |
| 4 | zhaoliu | 22 | Python |
| 5 | sunqi | 22 | PHP |
| 6 | zhangsansan | 22 | PHP |
| 7 | name0 | 22 | Java |
| 8 | name1 | 22 | Java |
| 9 | name2 | 22 | Java |
| 10 | zhouba | 24 | Java |
+-----+-------------+------+--------+
10 rows in set (0.00 sec)
1 package com.qfedu.jdbc.test;
2 import com.qfedu.jdbc.dao.StudentDao;
3 import com.qfedu.jdbc.domain.Student;
4 public class TestDelete {
5 public static void main(String[] args) {
6 StudentDao studentDao = new StudentDao();
7 Student student = new Student();
8 student.setSid(10);
9 studentDao.delete(student);
10 }
11 }
執(zhí)行TestDelete類，刪除數(shù)據(jù)庫中sid值為10的數(shù)據(jù)信息，通過SQL語句測試數(shù)據(jù)是否刪除成功，執(zhí)行結(jié)果如下所示。
mysql> SELECT * FROM STUDENT;
+-----+-------------+------+--------+
| sid | sname | age | course |
+-----+-------------+------+--------+
| 1 | zhangsan | 20 | Java |
| 2 | lisi | 21 | Java |
| 3 | wangwu | 22 | Java |
| 4 | zhaoliu | 22 | Python |
| 5 | sunqi | 22 | PHP |
| 6 | zhangsansan | 22 | PHP |
| 7 | name0 | 22 | Java |
| 8 | name1 | 22 | Java |
| 9 | name2 | 22 | Java |
+-----+-------------+------+--------+
9 rows in set (0.00 sec)
1 package com.qfedu.jdbc.test;
2 import com.qfedu.jdbc.dao.StudentDao;
3 import com.qfedu.jdbc.domain.Student;
4 public class TestSelectOne {
5 public static void main(String[] args) {
6 StudentDao studentDao = new StudentDao();
7 Student student = new Student();
8 student.setSid(1);
9 Student findStudent = studentDao.selectOne(student);
10 System.out.println(findStudent.toString());
11 }
12 }
1 package com.qfedu.jdbc.test;
2 import java.util.ArrayList;
3 import com.qfedu.jdbc.dao.StudentDao;
4 import com.qfedu.jdbc.domain.Student;
5 public class TestSelectAll {
6 public static void main(String[] args) {
7 StudentDao studentDao = new StudentDao();
8 ArrayListlist = studentDao.selectAll();
9 for (Student student : list) {
10 System.out.println(student.toString());
11 }
12 }
13 }
執(zhí)行TestSelectAll類，程序的運(yùn)行結(jié)果：
從以上執(zhí)行結(jié)果可以看出，程序成功查詢出表student中所有數(shù)據(jù)并輸出到控制臺，希望對學(xué)習(xí)Java開發(fā)的同學(xué)有所幫助，扣丁學(xué)堂不僅有專業(yè)的Java培訓(xùn)班供大家學(xué)習(xí)，還有與時俱進(jìn)的課程體系和大量的Java在線視頻教程讓學(xué)員免費(fèi)觀看學(xué)習(xí)，想要學(xué)好Java開發(fā)的小伙伴快到扣丁學(xué)堂來了解詳情吧?？鄱W(xué)堂Java技術(shù)交流群：670348138。
【關(guān)注微信公眾號獲取更多學(xué)習(xí)資料】
查看更多關(guān)于“Java開發(fā)資訊”的相關(guān)文章>>
全國免費(fèi)咨詢熱線
郵箱：codingke@1000phone.com
官方群：148715490
返回頂部