JDBC之增删改查

本文采用的mvc模型

1.模型层

package com.suse.model;

import java.util.Date;

public class Goddess {
    private Integer id;

    private String username;

    private Integer age;

    private Integer sex;

    private Date birthday;

    private String email;

    private String mobil;

    private Date create_date;

    private String create_user;

    private String update_user;

    private Date update_date;

    private Integer isdel;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getMobil() {
        return mobil;
    }

    public void setMobil(String mobil) {
        this.mobil = mobil;
    }

    public Date getCreate_date() {
        return create_date;
    }

    public void setCreate_date(Date create_date) {
        this.create_date = create_date;
    }

    public String getCreate_user() {
        return create_user;
    }

    public void setCreate_user(String create_user) {
        this.create_user = create_user;
    }

    public String getUpdate_user() {
        return update_user;
    }

    public void setUpdate_user(String update_user) {
        this.update_user = update_user;
    }

    public Date getUpdate_date() {
        return update_date;
    }

    public void setUpdate_date(Date update_date) {
        this.update_date = update_date;
    }

    public Integer getIsdel() {
        return isdel;
    }

    public void setIsdel(Integer isdel) {
        this.isdel = isdel;
    }

    @Override
    public String toString() {
        return "Goddess [id=" + id + ", username=" + username + ", age=" + age
                + ", sex=" + sex + ", birthday=" + birthday + ", email="
                + email + ", mobil=" + mobil + ", create_date=" + create_date
                + ", create_user=" + create_user + ", update_user="
                + update_user + ", update_date=" + update_date + ", isdel="
                + isdel + "]";
    }

}

2.业务逻辑层

package com.suse.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.suse.db.DButil;
import com.suse.model.Goddess;

public class GoddessDao {
    // 增加元素
    public void addGoddess(Goddess g) throws SQLException {

        Connection conn = DButil.getConnection();// 获得mysql数据库的连接
        String sql = "" + "insert into imooc"
                + "(id,username,sex,age,birthday,email,mobil,"
                + "create_user,create_date,update_user,update_date,isdel)"
                + "values("
                + "?,?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setInt(1, g.getId());
        ptmt.setString(2, g.getUsername());
        ptmt.setInt(3, g.getSex());
        ptmt.setInt(4, g.getAge());
        ptmt.setDate(5, new Date(g.getBirthday().getTime()));
        ptmt.setString(6, g.getEmail());
        ptmt.setString(7, g.getMobil());
        ptmt.setString(8, g.getCreate_user());
        ptmt.setString(9, g.getUpdate_user());
        ptmt.setInt(10, g.getIsdel());

        ptmt.execute();

    }

    // 更新元素
    public void updateGoddess(Goddess g) throws SQLException {
        Connection conn = DButil.getConnection();// 获得mysql数据库的连接
        String sql = "" + "update imooc"
                + " set username=?,sex=?,age=?,birthday=?,email=?,mobil=?, "
                + "update_user=?,update_date=current_date(),isdel=? "
                + " where id=?";

        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setString(1, g.getUsername());
        ptmt.setInt(2, g.getSex());
        ptmt.setInt(3, g.getAge());
        ptmt.setDate(4, new Date(g.getBirthday().getTime()));
        ptmt.setString(5, g.getEmail());
        ptmt.setString(6, g.getMobil());

        ptmt.setString(7, g.getUpdate_user());
        ptmt.setInt(8, g.getIsdel());
        ptmt.setInt(9, g.getId());
        ptmt.execute();

    }

    // 删除元素
    public void delGoddess(Integer id) throws SQLException {
        Connection conn = DButil.getConnection();// 获得mysql数据库的连接
        String sql = "" + "delete FROM  imooc" + " where id=?";

        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setInt(1, id);
        ptmt.execute();
    }

    // 多值查询
    public List<Goddess> query() throws SQLException {
        Connection conn = DButil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select username,age from imooc");// 查询executeQuery
        List<Goddess> gs = new ArrayList<Goddess>();// 返回多个数据
        Goddess g = null;
        while (rs.next()) {
            g = new Goddess();
            g.setUsername(rs.getString("username"));
            g.setAge(rs.getInt("age"));

            gs.add(g);
        }
        return gs;
    }

    // 单个元组的所有信息查询
    public Goddess get(Integer id) throws SQLException {
        Connection conn = DButil.getConnection();
        String sql = "" + " select * from  imooc" + " where id=?";

        PreparedStatement ptmt = conn.prepareStatement(sql);

        ptmt.setInt(1, id);
        ResultSet rs = ptmt.executeQuery();
        Goddess gs = new Goddess();
        while (rs.next()) {

            gs.setId(rs.getInt("id"));
            gs.setUsername(rs.getString("username"));
            gs.setAge(rs.getInt("age"));
            gs.setSex(rs.getInt("sex"));
            gs.setBirthday(rs.getDate("birthday"));
            gs.setEmail(rs.getString("email"));
            gs.setMobil(rs.getString("mobil"));
            gs.setCreate_date(rs.getDate("create_date"));
            gs.setCreate_user(rs.getString("create_user"));
            gs.setUpdate_date(rs.getDate("update_date"));
            gs.setUpdate_user(rs.getString("update_user"));
            gs.setIsdel(rs.getInt("isdel"));

        }
        return gs;

    }
}

3.控制层

package com.suse.action;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import com.suse.dao.GoddessDao;
import com.suse.model.Goddess;

public class GoddessAction {
    public static void main(String[] args) throws SQLException {
        GoddessDao g = new GoddessDao();
        // 调用查询方法
        /*
         * 查询测试
         * 
         * 
         * 
         * List<Goddess> gs = g.query(); for (Goddess goddess : gs) {// 遍历所获数据
         * System.out.println(goddess.getUsername() + "," + goddess.getAge());
         * 
         * }
         */
        Goddess gl = new Goddess();
        /*
         * 插入数据测试
         */
        gl.setId(6);
        gl.setUsername("小美");
        gl.setAge(20);
        gl.setSex(1);
        gl.setBirthday(new Date());
        gl.setEmail("xiaomei@suse.com");
        gl.setMobil("123456789");
        gl.setCreate_user("admin");
        gl.setUpdate_user("admin");
        gl.setIsdel(1);
        // 调用插入方法
        g.addGoddess(gl);

        gl.setUsername("小美");
        gl.setAge(20);
        gl.setSex(1);
        gl.setBirthday(new Date());
        gl.setEmail("xiaomei@suse.com");
        gl.setMobil("18882020596");

        gl.setUpdate_user("admin");
        gl.setIsdel(1);
        gl.setId(3);
        //调用更新方法
        g.updateGoddess(gl);
        //调用删除方法
        // g.delGoddess(3);
        //调用查询单个元组的所有信息方法
        Goddess g2 = g.get(6);

        System.out.println(g2.toString());

    }
}
Jdbc工具类：
package com.suse.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DButil {

    private static final String URL = "jdbc:mysql://localhost/imooc?useUnicode=true&characterEncoding=utf-8";// 解决中文乱码问题

    private static final String USER = "root";

    private static final String PASSWORD = "数据库密码";

    private static Connection conn = null;
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        // 获得数据库的连接
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return conn;
    }
    /*
     * public static void main(String[] args) throws Exception { // 1.加载驱动程序
     * 
     * Class.forName("com.mysql.jdbc.Driver"); // 获得数据库的连接 Connection conn =
     * DriverManager.getConnection(URL, USER, PASSWORD); // 3通过数据库的连接，实现增删改查
     * Statement stmt = conn.createStatement(); ResultSet rs =
     * stmt.executeQuery("select username,age from imooc");// 查询executeQuery
     * while (rs.next()) { System.out.println(rs.getString("username") +
     * rs.getInt("age"));
     * 
     * }
     */
    // }

}