Java使用Statement接口执行SQL语句操作实例分析
程序员文章站
2024-02-16 23:36:28
本文实例讲述了java使用statement接口执行sql语句操作的方法。分享给大家供大家参考，具体如下：
statement执行sql语句：
1. 对数据库的曾删改操...
本文实例讲述了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程序设计有所帮助。