使用Statement接口实现增，删，改操作(工作中不常用这个，而用PreparedStatement接口)
一、Statement接口
作用：用于执行静态 SQL 语句并返回它所生成结果的对象。
1. 创建数据库连接类及相册实体，代码如下：
1 package com.learn.jdbc.util; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.PreparedStatement; 6 import java.sql.Statement; 7 8 /** 9 * 封装数据库连接类 10 * @author Administrator 11 * 12 */ 13 public class DbUtil { 14 // 数据库连接 15 private static String dbUrl = "jdbc:mysql://localhost:3306/yizhuangxiu?useUnicode=true&characterEncoding=utf-8"; 16 // 用户名 17 private static String dbUserName = "root"; 18 // 密码 19 private static String dbUserPwd = "123456"; 20 // 驱动名 21 private static String jdbcName = "com.mysql.jdbc.Driver"; 22 23 /** 24 * 连接数据库 25 * @return 26 * @throws Exception 27 */ 28 public Connection getCon() throws Exception{ 29 Class.forName(jdbcName); 30 Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbUserPwd); 31 return con; 32 } 33 34 /** 35 * 关闭连接 36 * @throws Exception 37 */ 38 public void close(Statement stmt,Connection con) throws Exception{ 39 if(stmt != null){ 40 stmt.close(); 41 if(con != null){ 42 con.close(); 43 } 44 } 45 } 46 47 /** 48 * 关闭连接 49 * @throws Exception 50 */ 51 public void close(PreparedStatement pstmt,Connection con) throws Exception{ 52 if(pstmt != null){ 53 pstmt.close(); 54 if(con != null){ 55 con.close(); 56 } 57 } 58 } 59 60 61 }
1 package com.learn.jdbc.model; 2 3 import java.io.File; 4 5 /** 6 * 相册模型 7 * @author Administrator 8 * 9 */ 10 public class Album { 11 12 private int id; 13 private String name; 14 private int uid; 15 private long time; 16 17 private File content; 18 private File pic; 19 20 public Album(String name, int uid, long time) { 21 this.name = name; 22 this.uid = uid; 23 this.time = time; 24 } 25 26 27 28 public Album(int id, String name, int uid, long time) { 29 this.id = id; 30 this.name = name; 31 this.uid = uid; 32 this.time = time; 33 } 34 35 public Album(String name, int uid, long time, File content, File pic) { 36 super(); 37 this.name = name; 38 this.uid = uid; 39 this.time = time; 40 this.content = content; 41 this.pic = pic; 42 } 43 44 public int getId() { 45 return id; 46 } 47 48 public void setId(int id) { 49 this.id = id; 50 } 51 52 public String getName() { 53 return name; 54 } 55 56 public void setName(String name) { 57 this.name = name; 58 } 59 60 public int getUid() { 61 return uid; 62 } 63 64 public void setUid(int uid) { 65 this.uid = uid; 66 } 67 68 public long getTime() { 69 return time; 70 } 71 72 public void setTime(long time) { 73 this.time = time; 74 } 75 76 77 78 public File getContent() { 79 return content; 80 } 81 82 83 84 public void setContent(File content) { 85 this.content = content; 86 } 87 88 89 90 public File getPic() { 91 return pic; 92 } 93 94 95 96 public void setPic(File pic) { 97 this.pic = pic; 98 } 99 100 101 102 @Override 103 public String toString() { 104 return "["+this.id+","+this.name+","+this.uid+","+this.time+"]"; 105 } 106 107 108 109 110 111 }
2. 实现数据增加、修改、删除
1 package com.learn.jdbc.chap03; 2 3 import java.sql.Connection; 4 import java.sql.Statement; 5 6 import com.learn.jdbc.model.Album; 7 import com.learn.jdbc.util.DbUtil; 8 9 public class Demo3 { 10 private static DbUtil dbUtil=new DbUtil(); 11 12 /** 13 * 添加相册-----用面向对象思想封装 14 * @param ab 15 * @return 16 * @throws Exception 17 */ 18 private static int addInfo2(Album ab) throws Exception{ 19 Connection con = dbUtil.getCon(); 20 String sql = "insert into sp_album values (null,'"+ab.getName()+"',"+ab.getUid()+","+ab.getTime()+")"; 21 Statement stmt = con.createStatement(); 22 int result=stmt.executeUpdate(sql); 23 dbUtil.close(stmt, con); 24 return result; 25 } 26 27 /** 28 * 添加相册-----普通封装 29 * @param name 30 * @param uid 31 * @param time 32 * @return 33 * @throws Exception 34 */ 35 private static int addInfo(String name,int uid,long time) throws Exception{ 36 Connection con = dbUtil.getCon(); 37 String sql = "insert into sp_album values (null,'"+name+"',"+uid+","+time+")"; 38 Statement stmt = con.createStatement(); 39 int result=stmt.executeUpdate(sql); 40 dbUtil.close(stmt, con); 41 return result; 42 } 43 44 public static void main(String[] args) throws Exception{ 45 /*int result = addInfo("李四",8,System.currentTimeMillis()); 46 if(result>0){ 47 System.out.println("数据插入成功！"); 48 }else{ 49 System.out.println("数据插入失败！"); 50 }*/ 51 52 int result1 = addInfo2(new Album("呵呵",7,System.currentTimeMillis())); 53 if(result1>0){ 54 System.out.println("数据插入成功！"); 55 }else{ 56 System.out.println("数据插入失败！"); 57 } 58 } 59 }
1 package com.learn.jdbc.chap03; 2 3 import java.sql.Connection; 4 import java.sql.Statement; 5 6 import com.learn.jdbc.model.Album; 7 import com.learn.jdbc.util.DbUtil; 8 9 public class Demo4 { 10 private static DbUtil dbUtil = new DbUtil(); 11 12 /** 13 * 更新相册 14 * @param ab 15 * @return 16 * @throws Exception 17 */ 18 private static int updateInfo(Album ab) throws Exception { 19 Connection con = dbUtil.getCon(); 20 String sql = "update sp_album set name='" + ab.getName() + "',uid=" 21 + ab.getUid() + ",add_time=" + ab.getTime() + " where id=" 22 + ab.getId(); 23 Statement stmt = con.createStatement(); 24 int result = stmt.executeUpdate(sql); 25 dbUtil.close(stmt, con); 26 return result; 27 } 28 29 public static void main(String[] args) throws Exception { 30 int result1 = updateInfo(new Album(12,"呵呵1", 6, System.currentTimeMillis())); 31 if (result1 > 0) { 32 System.out.println("数据修改成功！"); 33 } else { 34 System.out.println("数据修改失败！"); 35 } 36 } 37 }
1 package com.learn.jdbc.chap03; 2 3 import java.sql.Connection; 4 import java.sql.Statement; 5 6 import com.learn.jdbc.model.Album; 7 import com.learn.jdbc.util.DbUtil; 8 9 public class Demo5 { 10 private static DbUtil dbUtil = new DbUtil(); 11 /** 12 * 删除数据 13 * @param id 14 * @return 15 * @throws Exception 16 */ 17 private static int deleteInfo(int id) throws Exception{ 18 Connection con = dbUtil.getCon(); 19 String sql = "delete from sp_album where id="+id; 20 Statement stmt = con.createStatement(); 21 int result = stmt.executeUpdate(sql); 22 dbUtil.close(stmt, con); 23 return result; 24 } 25 26 public static void main(String[] args) throws Exception { 27 int result1 = deleteInfo(13); 28 if (result1 > 0) { 29 System.out.println("数据删除成功！"); 30 } else { 31 System.out.println("数据删除失败！"); 32 } 33 } 34 }
所用到的表结构
CREATE TABLE `sp_album_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT '' COMMENT '相册名称', `uid` int(11) DEFAULT '0' COMMENT '用户id', `add_time` bigint(13) DEFAULT '0' COMMENT '创建时间', `content` longtext COMMENT '简介', `pic` longblob COMMENT '图像', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='相册1' CREATE TABLE `sp_account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `accountName` varchar(255) DEFAULT '' COMMENT '转账用户', `accountBalance` double DEFAULT '0' COMMENT '转账金额', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='银行转账--Java测试'
