本文實例講述了java使用statement接口執行sql語句操作的方法。分享給大家供大家參考，具體如下：
statement執行sql語句：
1. 對數據庫的曾刪改操作時，使用stmt.executeupdate(sql) 執行給定 sql 語句，分別為 insert 、update、delete.
2. 對數據庫做查詢時，直接使用 stmt.executequery(sql)，返回結果可以為一個resultset結果集。
首先做一些準備工作：
①對要進行操作的數據庫表進行封裝，比如說我的數據mydata中的aistu表，用aimember.java進行封裝，以便后面操作。具體如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
package com.mysqltest.jdbc.model;/** * 定義一個model * 成員模型 * @author ai_stu * */public class aimember { private string name; private int id; private int age; private string email; private string tel; private double salary; private string riqi; /** * alt+shift+s 添加構造函數generating constructor using fields. * @param name * @param id * @param age * @param email * @param tel * @param salary * @param riqi */ public aimember(string name, int id, int age, string email, string tel, double salary, string riqi) { super(); this.name = name; this.id = id; this.age = age; this.email = email; this.tel = tel; this.salary = salary; this.riqi = riqi; } //重構 public aimember(int id) { super(); this.id = id; } public string getname() { return name; } public void setname(string name) { this.name = name; } public int getid() { return id; } public void setid(int id) { this.id = id; } public int getage() { return age; } public void setage(int age) { this.age = age; } public string getemail() { return email; } public void setemail(string email) { this.email = email; } public string gettel() { return tel; } public void settel(string tel) { this.tel = tel; } public double getsalary() { return salary; } public void setsalary(double salary) { this.salary = salary; } public string getriqi() { return riqi; } public void setriqi(string riqi) { this.riqi = riqi; }} |
②對連接mysql數據庫，和關閉連接方法進行封裝，這里用dbutil.java進行封裝，具體如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
package com.mysqltest.jdbc.modelcomp;public class compmember { private int id; private string name; private int age; private double salary; /** * 構造函數1 * @param name * @param age * @param salary */ public compmember(string name, int age, double salary) { super(); this.name = name; this.age = age; this.salary = salary; } /** * 重載構造函數 * @param id * @param name * @param age * @param salary */ public compmember(int id, string name, int age, double salary) { super(); this.id = id; this.name = name; this.age = age; this.salary = salary; } /** * get,set方法 */ public int getid() { return id; } public void setid(int id) { this.id = id; } public string getname() { return name; } public void setname(string name) { this.name = name; } public int getage() { return age; } public void setage(int age) { this.age = age; } public double getsalary() { return salary; } public void setsalary(double salary) { this.salary = salary; } @override /** * 改寫tostring,使得顯示更好 */ public string tostring() { return "["+this.id+"]"+this.name+","+this.age+","+this.salary; }} |
準備工作做好了，下面開始使用statement接口執行sql語句來實現增刪改：
①增：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
package com.mysqltest.jdbc.two2;import java.sql.connection;import java.sql.statement;import com.mysqltest.jdbc.model.aimember;import com.mysqltest.jdbc.util.dbutil;public class demo3 { /** * 添加成員到表中1 * @param name * @param id * @param age * @param email * @param tel * @param salary * @param riqi * @return * @throws exception */ @suppresswarnings("unused") private static int addmember(string name,int id,int age,string email,string tel,double salary,string riqi) throws exception{ dbutil dbutil = new dbutil();//之前封裝好的 connection con = dbutil.getcon(); //獲取數據庫連接 string sql = "insert into aistu values('"+name+"',"+id+",'"+age+"','"+email+"','"+tel+"','"+salary+"','"+riqi+"')"; statement stmt = con.createstatement();//獲取statement int result = stmt.executeupdate(sql); dbutil.close(stmt, con); return result; } /** * 添加成員到表中2方法 * @param mem * @return * @throws exception */ private static int addmember2(aimember mem) throws exception{ //aimember也是之前封裝好的// mem.getname(); dbutil dbutil = new dbutil();//之前封裝好的 connection con = dbutil.getcon(); //獲取數據庫連接 string sql = "insert into aistu values('"+mem.getname()+"',"+mem.getid()+",'"+mem.getage()+"','"+mem.getemail()+"','"+mem.gettel()+"','"+mem.getsalary()+"','"+mem.getriqi()+"')"; statement stmt = con.createstatement();//獲取statement int result = stmt.executeupdate(sql); dbutil.close(stmt, con); return result; }// private static int addmenber2() public static void main(string[] args) throws exception { /*int result = addmember("劉翔", 4, 28, "[email protected]", "13411957776", 8000.00, "2015-09-10"); if(result==1){ system.out.println("添加成功"); }else{ system.out.println("添加失敗"); }*/ //多行注釋，ctrl+shift+/ aimember mem = new aimember("李娜", 6, 25, "[email protected]", "13411957775", 8000.00, "2015-09-03"); int result = addmember2(mem); if(result==1){ system.out.println("添加成功"); }else{ system.out.println("添加失敗"); } }} |
②改：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
package com.mysqltest.jdbc.two3;import java.sql.connection;import java.sql.statement;import com.mysqltest.jdbc.model.aimember;import com.mysqltest.jdbc.util.dbutil;public class demo4 { private static dbutil dbutil = new dbutil();// @suppresswarnings("unused") /** * 修改成員 * @param mem * @return * @throws exception */ private static int updatemember(aimember mem) throws exception { connection con = dbutil.getcon(); // 獲取數據庫連接 string sql = "update aistu set name='" + mem.getname() + "',id=" + mem.getid() + ",age='" + mem.getage() + "',email='" + mem.getemail() + "',tel='" + mem.gettel() + "',salary='" + mem.getsalary() + "',riqi='" + mem.getriqi() + "' where id=" + mem.getid(); //格式化，ctrl+a全選，然后ctrl+shift+f格式化 statement stmt = con.createstatement();// 獲取statement int result = stmt.executeupdate(sql); dbutil.close(stmt, con); return result;// return 0; } public static void main(string[] args) throws exception { aimember mem = new aimember("勞爾", 6, 24, "[email protected]", "13411957770", 18000.00, "2014-09-03"); int result = updatemember(mem); if (result==1) { system.out.println("更新成功"); } else { system.out.println("更新失敗"); } }} |
③刪：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
package com.mysqltest.jdbc.two4;import java.sql.connection;import java.sql.statement;import com.mysqltest.jdbc.model.aimember;import com.mysqltest.jdbc.util.dbutil;public class demo5 { private static dbutil dbutil = new dbutil(); public static int deletmember(aimember mem) throws exception{ connection con = dbutil.getcon(); // 獲取數據庫連接 string sql = "delete from aistu where id="+mem.getid(); statement stmt = con.createstatement();// 獲取statement int result = stmt.executeupdate(sql); dbutil.close(stmt, con); return result; } public static void main(string[] args) throws exception { aimember mem = new aimember(5); int result = deletmember(mem); if (result==1) { system.out.println("成功刪除成員"); } else { system.out.println("刪除成員失敗"); } }} |
希望本文所述對大家java程序設計有所幫助。
原文鏈接：https://blog.csdn.net/u010986080/article/details/51812936
