JDBC编程（MVC三层架构）（一）

JDBC编程
功能：通过MVC三层架构，实现增、删除、改和查；
环境：JDK1.6，Mysql 5.5.48
表结构（表名：imooc_goddess）：

1、工具类：连接数据库

package com.imooc.db;

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

/**
 * 数据库连接工具类
 */
public class DBUtil {
    private static final String URL = "jdbc:mysql://192.168.12.118:3306/imooc";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    private static Connection conn = null;

    static {
        try {
            // 1.加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");

            // 2.获得数据库连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获得连接
    public static Connection getConnection() {
        return conn;
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        // 3.操作数据库：增删改查
        Statement stat = conn.createStatement();

        ResultSet rsSet = stat.executeQuery("SELECT user_name,age FROM imooc_goddess;");

        while (rsSet.next()) {
            System.out.println("姓名：" + rsSet.getString("user_name") + "，年龄：" + rsSet.getInt("age"));

        }

    }

}

2、模型层，定义女神类，属性与表结构一致；

package com.imooc.model;

import java.util.Date;

public class Goddess {
    private Integer id;
    private String user_name;
    private Integer sex;
    private Integer age;
    private Date birthday;
    private String email;
    private String mobile;
    private String create_user;
    private Date create_date;
    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 getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public Integer getSex() {
        return sex;
    }

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

    public Integer getAge() {
        return age;
    }

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

    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 getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getCreate_user() {
        return create_user;
    }

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

    public Date getCreate_date() {
        return create_date;
    }

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

    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 + ", user_name=" + user_name + ", sex="
                + sex + ", age=" + age + ", birthday=" + birthday + ", email="
                + email + ", mobile=" + mobile + ", create_user=" + create_user
                + ", create_date=" + create_date + ", update_user="
                + update_user + ", update_date=" + update_date + ", isdel="
                + isdel + "]";
    }

}

3、屌丝类，主要操作数据库

package com.imooc.loser;

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 java.util.Map;

import com.imooc.db.DBUtil;
import com.imooc.model.Goddess;

/**
 * 模型层 屌丝Loser
 * 
 * @author Administrator
 * 
 */
public class Loser {

    /**
     * 新增女神资料
     * 
     * @param goddess
     * @throws SQLException
     */
    public void addGoddess(Goddess goddess) throws SQLException {
        // 获得连接
        Connection conn = DBUtil.getConnection();
        String sql = "INSERT INTO imooc_goddess(user_name,sex,age,birthday,email,mobile,create_user"
                + ",create_date,update_user,update_date,isdel) VALUES(?,?,?,?,?,   ?,?,current_date(),?,current_date(),   ?)";
        // 预声明SQL语句，并检查
        PreparedStatement preparedStatement = conn.prepareStatement(sql);

        // 参数值设置
        preparedStatement.setString(1, goddess.getUser_name());
        preparedStatement.setInt(2, 1);
        preparedStatement.setInt(3, goddess.getAge());
        preparedStatement.setDate(4, new Date(goddess.getBirthday().getTime()));
        preparedStatement.setString(5, goddess.getEmail());
        preparedStatement.setString(6, goddess.getMobile());
        preparedStatement.setString(7, goddess.getCreate_user());
        preparedStatement.setString(8, goddess.getUpdate_user());
        preparedStatement.setInt(9, 0);

        // 执行SQL语句
        preparedStatement.execute();

    }

    /**
     * 更新女神资料
     * 
     * @param goddess
     */
    public void updateGoddess(Goddess goddess) {
        // 获得连接
        Connection conn = DBUtil.getConnection();

        // SQL
        String sql = " UPDATE imooc_goddess  " + " SET " + "   user_name = ?, "
                + "    age = ?, " + "    birthday = ?, " + "   email = ?, "
                + "    mobile = ?, " + "    update_user = ?, "
                + "   update_date =current_date()  " + " WHERE id = ? ";

        try {
            // 预声明SQL语句，并检查
            PreparedStatement preparedStatement = conn.prepareStatement(sql);

            // 参数值设置
            preparedStatement.setString(1, goddess.getUser_name());
            preparedStatement.setInt(2, goddess.getAge());
            preparedStatement.setDate(3, new Date(goddess.getBirthday()
                    .getTime()));
            preparedStatement.setString(4, goddess.getEmail());
            preparedStatement.setString(5, goddess.getMobile());
            preparedStatement.setString(6, goddess.getUpdate_user());
            preparedStatement.setInt(7, goddess.getId());

            // 执行SQL语句
            preparedStatement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除女神资料
     * 
     * @param id
     */
    public void delGoddess(Integer id) {
        // 获得连接
        Connection conn = DBUtil.getConnection();
        String sql = " DELETE FROM imooc_goddess  " + " WHERE id = ? ";
        try {
            // 预声明SQL语句，并检查
            PreparedStatement preparedStatement = conn.prepareStatement(sql);

            // 参数值设置
            preparedStatement.setInt(1, id);

            // 执行SQL语句
            preparedStatement.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 获得全部女神资料(完全自定义查询条件)
     * 
     * @return
     */
    public List<Goddess> query(List<Map<String, Object>> params) {
        // 创建List对象
        List<Goddess> list = new ArrayList<Goddess>();

        // 获得连接
        Connection conn = DBUtil.getConnection();

        try {

            // 创建StringBuilder对象
            StringBuilder sb = new StringBuilder();

            // SQL
            String sql = "SELECT *FROM imooc_goddess WHERE 1=1 ";

            // 添加字符串
            sb.append(sql);

            // 参数非空，即WHERE有条件
            if (params != null && params.size() > 0) {
                for (int i = 0; i < params.size(); i++) {
                    Map<String, Object> map = params.get(i);
                    // connection连接谓词
                    // name 字段名
                    // relation 关系词
                    // value 字段值
                    sb.append(map.get("connection") + " " + map.get("name")
                            + " " + map.get("relation") + " "
                            + map.get("value") + " ");
                }
            }

            // 创建Statement对象
            PreparedStatement st = conn.prepareStatement(sb.toString());

            // 创建女神Goddess对象
            Goddess goddess = null;

            // 获得查询结果集
            ResultSet set = st.executeQuery();

            // 循环存储结果
            while (set.next()) {

                // 创建一个对象
                goddess = new Goddess();

                // 设置女神对象goddess的属性
                goddess.setId(set.getInt("id"));
                goddess.setUser_name(set.getString("user_name"));
                goddess.setSex(set.getInt("sex"));
                goddess.setAge(set.getInt("age"));
                goddess.setBirthday(set.getDate("birthday"));
                goddess.setEmail(set.getString("email"));
                goddess.setMobile(set.getString("mobile"));
                goddess.setCreate_user(set.getString("create_user"));
                goddess.setCreate_date(set.getDate("create_date"));
                goddess.setUpdate_user(set.getString("update_user"));
                goddess.setUpdate_date(set.getDate("update_date"));
                goddess.setIsdel(set.getInt("isdel"));

                // 列表添加女神对象
                list.add(goddess);

                // 清空女神对象
                goddess = null;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 获得全部女神资料
     * 
     * @return
     */
    public List<Goddess> query() {
        // 创建List对象
        List<Goddess> list = new ArrayList<Goddess>();

        // 获得连接
        Connection conn = DBUtil.getConnection();

        try {
            // 创建Statement对象
            Statement st = conn.createStatement();

            // 创建女神Goddess对象
            Goddess goddess = null;

            // 获得查询结果集
            ResultSet set = st
                    .executeQuery("SELECT id,user_name,sex,age,birthday,email,mobile,create_user,"
                            + "create_date,update_user,update_date,isdel FROM imooc_goddess ORDER BY id");

            // 循环存储结果
            while (set.next()) {

                // 创建一个对象
                goddess = new Goddess();

                // 设置女神对象goddess的属性
                goddess.setId(set.getInt("id"));
                goddess.setUser_name(set.getString("user_name"));
                goddess.setSex(set.getInt("sex"));
                goddess.setAge(set.getInt("age"));
                goddess.setBirthday(set.getDate("birthday"));
                goddess.setEmail(set.getString("email"));
                goddess.setMobile(set.getString("mobile"));
                goddess.setCreate_user(set.getString("create_user"));
                goddess.setCreate_date(set.getDate("create_date"));
                goddess.setUpdate_user(set.getString("update_user"));
                goddess.setUpdate_date(set.getDate("update_date"));
                goddess.setIsdel(set.getInt("isdel"));

                // 列表添加女神对象
                list.add(goddess);

                // 清空女神对象
                goddess = null;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 通过姓名获得女神资料
     * 
     * @return
     */
    public List<Goddess> query(String name) {
        // 创建List对象
        List<Goddess> list = new ArrayList<Goddess>();

        // 获得连接
        Connection conn = DBUtil.getConnection();

        try {

            // 创建StringBuilder对象
            StringBuilder sb = new StringBuilder(
                    "SELECT id,user_name,sex,age,birthday,email,mobile,create_user,"
                            + "create_date,update_user,update_date,isdel FROM imooc_goddess ");

            // 添加WHERE条件
            sb.append("WHERE user_name=?");

            // 创建PreparedStatement对象
            PreparedStatement ps = conn.prepareStatement(sb.toString());

            // 设置参数
            ps.setString(1, name);

            // 获得查询结果集
            ResultSet set = ps.executeQuery();

            // 创建女神Goddess对象
            Goddess goddess = null;

            // 循环存储结果
            while (set.next()) {

                // 创建一个对象
                goddess = new Goddess();

                // 设置女神对象goddess的属性
                goddess.setId(set.getInt("id"));
                goddess.setUser_name(set.getString("user_name"));
                goddess.setSex(set.getInt("sex"));
                goddess.setAge(set.getInt("age"));
                goddess.setBirthday(set.getDate("birthday"));
                goddess.setEmail(set.getString("email"));
                goddess.setMobile(set.getString("mobile"));
                goddess.setCreate_user(set.getString("create_user"));
                goddess.setCreate_date(set.getDate("create_date"));
                goddess.setUpdate_user(set.getString("update_user"));
                goddess.setUpdate_date(set.getDate("update_date"));
                goddess.setIsdel(set.getInt("isdel"));

                // 列表添加女神对象
                list.add(goddess);

                // 清空女神对象
                goddess = null;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 通过ID获得女神资料
     * 
     * @param id
     * @return
     */
    public Goddess get(Integer id) {
        // 获得连接
        Connection conn = DBUtil.getConnection();

        // 创建女神Goddess对象
        Goddess goddess = null;

        try {
            // SQL
            String sql = "SELECT id,user_name,sex,age,birthday,email,mobile,create_user,"
                    + "create_date,update_user,update_date,isdel FROM imooc_goddess WHERE id=? ";

            // 创建Statement对象
            PreparedStatement ps = conn.prepareStatement(sql);

            // 设置参数
            ps.setInt(1, id);

            // 获得查询结果集
            ResultSet set = ps.executeQuery();

            while (set.next()) {
                // 创建一个对象
                goddess = new Goddess();

                // 设置女神对象goddess的属性
                goddess.setId(set.getInt("id"));
                goddess.setUser_name(set.getString("user_name"));
                goddess.setSex(set.getInt("sex"));
                goddess.setAge(set.getInt("age"));
                goddess.setBirthday(set.getDate("birthday"));// 类java.sql.Date是类java.util.Date的子类
                goddess.setEmail(set.getString("email"));
                goddess.setMobile(set.getString("mobile"));
                goddess.setCreate_user(set.getString("create_user"));
                goddess.setCreate_date(set.getDate("create_date"));
                goddess.setUpdate_user(set.getString("update_user"));
                goddess.setUpdate_date(set.getDate("update_date"));
                goddess.setIsdel(set.getInt("isdel"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goddess;
    }
}