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("[e-mail]");        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("[e-mail]");        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"));     *      * }     */    // }}