本文实例讲述了Java使用Statement接口执行SQL语句操作的方法。分享给大家供大家参考，具体如下：
Statement执行SQL语句：
1. 对数据库的曾删改操作时，使用stmt.executeUpdate(sql) 执行给定 SQL 语句，分别为 insert 、update、delete.
2. 对数据库做查询时，直接使用 stmt.executeQuery(sql)，返回结果可以为一个resultSet结果集。
首先做一些准备工作：
①对要进行操作的数据库表进行封装，比如说我的数据mydata中的aistu表，用AiMember.java进行封装，以便后面操作。具体如下：
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进行封装，具体如下：
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语句来实现增删改：
①增：
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("添加失败");
}
}
}
②改：
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("更新失败");
}
}
}
③删：
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相关内容感兴趣的读者可查看本站专题：《Java+MySQL数据库程序设计总结》、《Java数据结构与算法教程》、《Java文件与目录操作技巧汇总》、《Java操作DOM节点技巧总结》和《Java缓存操作技巧汇总》
希望本文所述对大家java程序设计有所帮助。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
