服务器之家:专注于服务器技术及软件下载分享
分类导航

PHP教程|ASP.NET教程|Java教程|ASP教程|编程技术|正则表达式|C/C++|IOS|C#|Swift|Android|JavaScript|易语言|

服务器之家 - 编程语言 - Java教程 - Java使用Statement接口执行SQL语句操作实例分析

Java使用Statement接口执行SQL语句操作实例分析

2021-05-13 12:09Young-xy Java教程

这篇文章主要介绍了Java使用Statement接口执行SQL语句操作,结合实例形式详细分析了Java使用Statement接口针对mysql数据库进行连接与执行SQL语句增删改查等相关操作技巧与注意事项,需要的朋友可以参考下

本文实例讲述了java使用statement接口执行sql语句操作的方法。分享给大家供大家参考,具体如下:

statement执行sql语句:

1. 对数据库的曾删改操作时,使用stmt.executeupdate(sql)  执行给定 sql 语句,分别为 insert updatedelete.

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

延伸 · 阅读

精彩推荐