本文实例讲述了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, "15xliu@stu.edu.cn", "13411957776", 8000.00, "2015-09-10"); if(result==1){ system.out.println("添加成功"); }else{ system.out.println("添加失败"); }*/ //多行注释,ctrl+shift+/ aimember mem = new aimember( "李娜" , 6 , 25 , "15nli@stu.edu.cn" , "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 , "14elao@stu.edu.cn" , "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