使用Statement接口实现增，删，改操作(工作中不常用这个，而用PreparedStatement接口)

一、Statement接口

    作用：用于执行静态 SQL 语句并返回它所生成结果的对象。
    

1. 创建数据库连接类及相册实体，代码如下：

package com.learn.jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

/**
 * 封装数据库连接类
 * @author Administrator
 *
 */
public class DbUtil {
    // 数据库连接
    private static String dbUrl = "jdbc:mysql://localhost:3306/yizhuangxiu?useUnicode=true&characterEncoding=utf-8";
    // 用户名
    private static String dbUserName = "root";
    // 密码
    private static String dbUserPwd  = "123456";
    // 驱动名
    private static String jdbcName = "com.mysql.jdbc.Driver";
    
    /**
     * 连接数据库
     * @return
     * @throws Exception
     */
    public Connection getCon() throws Exception{
        Class.forName(jdbcName);
        Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbUserPwd);
        return con;
    }
    
    /**
     * 关闭连接
     * @throws Exception 
     */
    public void close(Statement stmt,Connection con) throws Exception{
        if(stmt != null){
            stmt.close();
            if(con != null){
                con.close();
            }
        }
    }
    
    /**
     * 关闭连接
     * @throws Exception 
     */
    public void close(PreparedStatement pstmt,Connection con) throws Exception{
        if(pstmt != null){
            pstmt.close();
            if(con != null){
                con.close();
            }
        }
    }
    
    
}

package com.learn.jdbc.model;

import java.io.File;

/**
 * 相册模型
 * @author Administrator
 *
 */
public class Album {
    
    private int id;
    private String name;
    private int uid;
    private long time;
    
    private File content;
    private File pic;
    
    public Album(String name, int uid, long time) {
        this.name = name;
        this.uid = uid;
        this.time = time;
    }

    
    
    public Album(int id, String name, int uid, long time) {
        this.id = id;
        this.name = name;
        this.uid = uid;
        this.time = time;
    }

    public Album(String name, int uid, long time, File content, File pic) {
        super();
        this.name = name;
        this.uid = uid;
        this.time = time;
        this.content = content;
        this.pic = pic;
    }

    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 getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public long getTime() {
        return time;
    }

    public void setTime(long time) {
        this.time = time;
    }



    public File getContent() {
        return content;
    }



    public void setContent(File content) {
        this.content = content;
    }



    public File getPic() {
        return pic;
    }



    public void setPic(File pic) {
        this.pic = pic;
    }



    @Override
    public String toString() {
        return "["+this.id+","+this.name+","+this.uid+","+this.time+"]";
    }
    
    
    
    
    
}

2. 实现数据增加、修改、删除

package com.learn.jdbc.chap03;

import java.sql.Connection;
import java.sql.Statement;

import com.learn.jdbc.model.Album;
import com.learn.jdbc.util.DbUtil;

public class Demo3 {
    private static DbUtil dbUtil=new DbUtil();
    
    /**
     * 添加相册-----用面向对象思想封装
     * @param ab
     * @return
     * @throws Exception
     */
    private static int addInfo2(Album ab) throws Exception{
        Connection con = dbUtil.getCon();
        String sql = "insert into sp_album values (null,'"+ab.getName()+"',"+ab.getUid()+","+ab.getTime()+")";
        Statement stmt = con.createStatement();
        int result=stmt.executeUpdate(sql);
        dbUtil.close(stmt, con);
        return result;
    }
    
    /**
     * 添加相册-----普通封装
     * @param name
     * @param uid
     * @param time
     * @return
     * @throws Exception
     */
    private static int addInfo(String name,int uid,long time) throws Exception{
        Connection con = dbUtil.getCon();
        String sql = "insert into sp_album values (null,'"+name+"',"+uid+","+time+")";
        Statement stmt = con.createStatement();
        int result=stmt.executeUpdate(sql);
        dbUtil.close(stmt, con);
        return result;
    }
    
    public static void main(String[] args) throws Exception{
        /*int result = addInfo("李四",8,System.currentTimeMillis());
        if(result>0){
            System.out.println("数据插入成功！");
        }else{
            System.out.println("数据插入失败！");
        }*/
        
        int result1 = addInfo2(new Album("呵呵",7,System.currentTimeMillis()));
        if(result1>0){
            System.out.println("数据插入成功！");
        }else{
            System.out.println("数据插入失败！");
        }
    }
}

package com.learn.jdbc.chap03;

import java.sql.Connection;
import java.sql.Statement;

import com.learn.jdbc.model.Album;
import com.learn.jdbc.util.DbUtil;

public class Demo4 {
    private static DbUtil dbUtil = new DbUtil();

    /**
     * 更新相册
     * @param ab
     * @return
     * @throws Exception
     */
    private static int updateInfo(Album ab) throws Exception {
        Connection con = dbUtil.getCon();
        String sql = "update sp_album set name='" + ab.getName() + "',uid="
                + ab.getUid() + ",add_time=" + ab.getTime() + " where id="
                + ab.getId();
        Statement stmt = con.createStatement();
        int result = stmt.executeUpdate(sql);
        dbUtil.close(stmt, con);
        return result;
    }

    public static void main(String[] args) throws Exception {
        int result1 = updateInfo(new Album(12,"呵呵1", 6, System.currentTimeMillis()));
        if (result1 > 0) {
            System.out.println("数据修改成功！");
        } else {
            System.out.println("数据修改失败！");
        }
    }
}

package com.learn.jdbc.chap03;

import java.sql.Connection;
import java.sql.Statement;

import com.learn.jdbc.model.Album;
import com.learn.jdbc.util.DbUtil;

public class Demo5 {
    private static DbUtil dbUtil = new DbUtil();
    /**
     * 删除数据
     * @param id
     * @return
     * @throws Exception
     */
    private static int deleteInfo(int id) throws Exception{
        Connection con = dbUtil.getCon();
        String sql = "delete from sp_album where id="+id;
        Statement stmt = con.createStatement();
        int result = stmt.executeUpdate(sql);
        dbUtil.close(stmt, con);
        return result;
    }
    
    public static void main(String[] args) throws Exception {
        int result1 = deleteInfo(13);
        if (result1 > 0) {
            System.out.println("数据删除成功！");
        } else {
            System.out.println("数据删除失败！");
        }
    }
}

所用到的表结构

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测试'