• 使用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测试'
  • 相关阅读:
    shell脚本修改文件
    腾讯企业邮箱获取客户端专用密码(授权码)
    java内存dump文件导出与查看
    为什么MySQL数据库索引选择使用B+树?
    nginx 平滑重启的实现方法
    nginx重启 平滑重启
    Nginx常用的平滑重启
    nginx reload和reopen
    转载【小程序】: 微信小程序开发---应用与页面的生命周期
    【微信小程序】用户首次进入小程序拒绝授权,如何再次调用授权页面,获取用户信息userInfo
  • 原文地址:https://www.cnblogs.com/eaglezb/p/6055305.html
一二三 - 开发者的网上家园