时间:2021-05-20
本文实例讲述了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 { //多行注释,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程序设计有所帮助。
声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。
Statement对象是用来执行SQL语句的PreparedStatement:预编译的Statement对象,是Statement的子接口。一.性能和代码编写
本文实例展示了Java采用setAsciiStream()方法检索数据库的实例代码。使用参数查询必须在SQL语句执行之前对参数进行赋值,赋值是使用Prepare
Oraclesql执行流程图_SQL执行过程一、sql语句的执行步骤:1)语法分析,分析语句的语法是否符合规范,衡量语句中各表达式的意义。2)语义分析,检查语句
执行SQL语句本功能为用SQL语句对MYSQL直接操作。一、执行SQL语句:(一)、登录后台,单击“系统”菜单,选择“执行S
本文实例讲述了ThinkPHP框架获取最后一次执行SQL语句及变量调试简单操作。分享给大家供大家参考,具体如下:ThinkPHP中获取最后一次执行sql语句的方