java.sql.statement_Java使用Statement接口执行SQL语句操作实例分析

本文实例讲述了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程序设计有所帮助。