为了账号安全，请及时绑定邮箱和手机立即绑定
首页 手记 JDBC实战之女神禁区功能

JDBC实战之女神禁区功能

标签：
Java MySQL 
数据库表结构：
CREATE TABLE `goddess` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(30) NOT NULL,
  `sex` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  `mobile` varchar(11) DEFAULT NULL,
  `create_user` varchar(30) DEFAULT NULL,
  `create_date` date DEFAULT NULL,
  `update_user` varchar(30) DEFAULT NULL,
  `update_date` date DEFAULT NULL,
  `isdel` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

数据库表数据：
insert  into `goddess`(`id`,`user_name`,`sex`,`age`,`birthday`,`email`,`mobile`,`create_user`,`create_date`,`update_user`,`update_date`,`isdel`) values 
(1,'小溪',1,22,'2000-12-12','xiaoxi@163.com','13911111111','ADMIN','2015-01-08','ADMIN','2015-01-08',0),
(2,'小夏',1,19,'2000-12-12','xiaoxi@163.com','13911111111','ADMIN','2015-01-08','admin','2016-11-22',0),
(3,'小凌',1,22,'2000-12-12','xiaoxi@163.com','13911111111','ADMIN','2015-01-08','ADMIN','2015-01-08',0),
(4,'小红',1,18,'2016-11-21','13838@qq.com','13838383888','admin','2016-11-21','admin','2016-11-21',0),
(5,'小小',1,20,'1990-02-02','123456@qq.com','12345678543','admin','2016-11-21','admin','2016-11-21',0),
(6,'小惜',1,22,'1994-03-03','123@qq.com','13512345678','admin','2016-11-21','admin','2016-11-21',0),
(7,'小小',1,25,'1991-03-06','1991@qq.com','13812345678','admin','2016-11-22','admin','2016-11-22',0);
/**
* JDBC工具类
* @author：凌_惜
* @date：2016年11月21日 上午9:48:28
*/
public class JDBCUtil {

    private static final String URL = "jdbc:mysql://localhost:3306/test";
    private static final String USER = "root";
    private static final String PASSWORD = "1234";
    private static Connection conn = null;

    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        } 
    }

    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection () {
        return conn;
    }

}
/**
* 女神实体类
* @author：凌_惜
* @date：2016年11月21日 上午10:20:29
*/
public class Goddess {

    private Integer id;           
    private String userName; //姓名         
    private Integer sex;  //性别        
    private Integer age;  //年龄         
    private Date birthday;  //生日         
    private String email;   //邮箱        
    private String mobile;  //手机号         
    private String createUser;  //创建人         
    private Date createDate;  //创建时间         
    private String updateUser;  //更新人         
    private Date updateDate;  //更新时间         
    private Integer isdel;   //删除标记  0：否  1：是

    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 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 getCreateUser() {
        return createUser;
    }
    public void setCreateUser(String createUser) {
        this.createUser = createUser;
    }
    public Date getCreateDate() {
        return createDate;
    }
    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }
    public String getUpdateUser() {
        return updateUser;
    }
    public void setUpdateUser(String updateUser) {
        this.updateUser = updateUser;
    }
    public Date getUpdateDate() {
        return updateDate;
    }
    public void setUpdateDate(Date updateDate) {
        this.updateDate = updateDate;
    }
    public Integer getIsdel() {
        return isdel;
    }
    public void setIsdel(Integer isdel) {
        this.isdel = isdel;
    }

}
/**
* 女神Dao层
* @author：凌_惜
* @date：2016年11月21日 上午10:30:55
*/
public class GoddessDao {

    /**
     * 添加女神信息
     * @throws Exception
     */
    public void addGoddess (Goddess goddess) throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "insert into goddess(user_name,sex,age,birthday,email,mobile,"
                    + "create_user,create_date,update_user,update_date,isdel) "
                    + "values(?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, goddess.getUserName());
        statement.setInt(2, goddess.getSex());
        statement.setInt(3, goddess.getAge());
        statement.setDate(4, new Date(goddess.getBirthday().getTime()));
        statement.setString(5, goddess.getEmail());
        statement.setString(6, goddess.getMobile());
        statement.setString(7, goddess.getCreateUser());
        statement.setString(8, goddess.getUpdateUser());
        statement.setInt(9, goddess.getIsdel());
        statement.execute();
    }

    /**
     * 批量删除女神信息(逻辑删除)
     * @throws Exception
     */
    public void delGoddess (Integer[] ids) throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "update goddess set isdel=1 where id=?";
        PreparedStatement statement = conn.prepareStatement(sql);
        for(int i =0 ;i<ids.length;i++){   
            statement.setInt(1,ids[i]);  
            statement.addBatch();                 
        }   
        statement.executeBatch();
    }

    /**
     * 更改女神信息
     * @throws Exception
     */
    public void updateGoddess (Goddess goddess) throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "update goddess set user_name=?,age=?,birthday=?,email=?,mobile=?,"
                    + "update_user=?,update_date=current_date() where id=?";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, goddess.getUserName());
        statement.setInt(2, goddess.getAge());
        statement.setDate(3, new Date(goddess.getBirthday().getTime()));
        statement.setString(4, goddess.getEmail());
        statement.setString(5, goddess.getMobile());
        statement.setString(6, goddess.getUpdateUser());
        statement.setInt(7, goddess.getId());
        statement.execute();
    }

    /**
     * 查询全部女神信息
     * @return
     * @throws Exception
     */
    public List<Goddess> queryGoddess () throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "select * from goddess where isdel=0";
        PreparedStatement statement = conn.prepareStatement(sql);
        ResultSet result = statement.executeQuery();
        List<Goddess> goddessList = new ArrayList<Goddess>();
        while (result.next()) {
            Goddess goddess = new Goddess();
            goddess.setId(result.getInt("id"));
            goddess.setUserName(result.getString("user_name"));
            goddessList.add(goddess);
        } 
        return goddessList;
    }

    /**
     * 根据姓名模糊查询女神信息
     * @param name
     * @return
     */
    public List<Goddess> queryGoddessByUserName (String name) throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "select * from goddess where isdel=0 and user_name like ?";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1, "%" + name + "%");
        ResultSet result = statement.executeQuery();
        List<Goddess> goddessList = new ArrayList<Goddess>();
        while (result.next()) {
            Goddess goddess = new Goddess();
            goddess.setId(result.getInt("id"));
            goddess.setUserName(result.getString("user_name"));
            goddessList.add(goddess);
        }
        return goddessList;
    }

    /**
     * 根据id查询女神信息
     * @return
     * @throws Exception
     */
    public Goddess getGoddess (Integer id) throws Exception {
        Connection conn = JDBCUtil.getConnection();
        String sql = "select * from goddess where isdel=0 and id=?";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setInt(1, id);
        ResultSet result = statement.executeQuery();
        Goddess goddess = null;
        while (result.next()) {
            goddess = new Goddess();
            goddess.setId(result.getInt("id"));
            goddess.setUserName(result.getString("user_name"));
            goddess.setSex(result.getInt("sex"));
            goddess.setAge(result.getInt("age"));
            goddess.setBirthday(result.getDate("birthday"));
            goddess.setEmail(result.getString("email"));
            goddess.setMobile(result.getString("mobile"));
        }
        return goddess;
    }

}
/**
* 女神action层
* @author：凌_惜
* @date：2016年11月21日 下午1:13:21
*/
public class GoddessAction {

    /**
     * 添加
     * @param goddess
     */
    public void add (Goddess goddess) throws Exception {
        GoddessDao dao = new GoddessDao();
        goddess.setSex(1);
        goddess.setCreateUser("admin");
        goddess.setUpdateUser("admin");
        goddess.setIsdel(0);
        dao.addGoddess(goddess);
    }

    /**
     * 删除
     * @param id
     */
    public void delete (Integer[] ids) throws Exception {
        GoddessDao dao = new GoddessDao();
        dao.delGoddess(ids);
    }

    /**
     * 更新
     * @param goddess
     */
    public void update (Goddess goddess) throws Exception {
        GoddessDao dao = new GoddessDao();
        goddess.setUpdateUser("admin");
        dao.updateGoddess(goddess);
    }

    /**
     * 查询全部
     * @return
     */
    public List<Goddess> query () throws Exception {
        GoddessDao dao = new GoddessDao();
        List<Goddess> list = dao.queryGoddess();
        return list;
    }

    /**
     * 根据名字模糊查询
     * @param name
     * @return
     */
    public List<Goddess> queryByUsername (String name) throws Exception {
        GoddessDao dao = new GoddessDao();
        return dao.queryGoddessByUserName(name);
    }

    /**
     * 根据id查询
     * @param id
     * @return
     */
    public Goddess getGoddess (Integer id) throws Exception {
        GoddessDao dao = new GoddessDao();
        return dao.getGoddess(id);
    }

}
/**
* 女神view层
* @author：凌_惜
* @date：2016年11月21日 下午2:52:48
*/
public class GodessView {

    private static final String CONTEXT = "=====欢迎来到女神禁区，下面是女神禁区功能列表=====\n"
                                        + "字母[m]：主菜单\n"
                                        + "字母[q]：查看全部女神信息\n"
                                        + "字母[g]：查看某位女神详细信息\n"
                                        + "字母[a]：添加女神信息\n"
                                        + "字母[u]：更新女神信息\n"
                                        + "字母[d]：删除女神信息\n"
                                        + "字母[s]：根据名字模糊查询女神信息\n"
                                        + "字母[b]：退出当前功能，返回主菜单\n"
                                        + "字母[e]：退出女神禁区\n"
                                        + "请选择：";
    private static final String OPERATION_MAIN = "m";
    private static final String OPERATION_QUERY = "q";
    private static final String OPERATION_GET = "g";
    private static final String OPERATION_ADD = "a";
    private static final String OPERATION_UPDATE = "u";
    private static final String OPERATION_DELETE = "d";
    private static final String OPERATION_SEARCH = "s";
    private static final String OPERATION_BREAK = "b";
    private static final String OPERATION_EXIT = "e";

    /**
     * 程序入口
     * @param args
     */
    public static void main(String[] args) {
        System.out.println(CONTEXT);
        Scanner scan = new Scanner(System.in);  
        GoddessAction goddessAction = new GoddessAction();
        Goddess goddess = null;
        List<Goddess> list = null;
        String pervious = null;
        Integer step = 1;
        while (scan.hasNext()) {
            String flag = scan.next().trim();
            //退出
            if (OPERATION_EXIT.equalsIgnoreCase(flag)) {
                System.out.println("你已成功退出女神禁区！");
                break;
            }
            //主菜单
            if (OPERATION_MAIN.equalsIgnoreCase(flag)) {
                step = 1;
                pervious = null;
                System.out.println(CONTEXT);
            }
            //退出当前功能，返回主菜单
            if (OPERATION_BREAK.equalsIgnoreCase(flag)) {
                step = 1;
                pervious = null;
                System.out.println(CONTEXT);
            }
            //查看全部女神信息
            if (OPERATION_QUERY.equalsIgnoreCase(flag)) {
                try {
                    list = goddessAction.query();
                    if (list != null) {
                        System.out.println("---本禁区全部女神基本信息如下---");
                        for (Goddess g : list) {
                            System.out.println("编号：" + g.getId() + "，姓名：" + g.getUserName());
                        }
                    } else {
                        System.out.println("查询的女神不存在！");
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    System.out.println("查询女神基本信息失败！");
                }
            }
            //查看某个女神详细信息
            if (OPERATION_GET.equalsIgnoreCase(flag) || OPERATION_GET.equals(pervious)) {
                pervious = OPERATION_GET;
                if (step == 1) {
                    System.out.println("请输入要查看的女神[ID]：");
                } else if (step == 2) {
                    try {
                        goddess = goddessAction.getGoddess(Integer.valueOf(flag));
                        if (goddess != null) {
                            step = 1;
                            pervious = null;
                            System.out.println("编号：" + goddess.getId() + "\n姓名：" + goddess.getUserName() 
                                            + "\n性别：" + (goddess.getSex() == 1 ? "女":"男") + "\n年龄：" + goddess.getAge() 
                                            + "\n生日：" + goddess.getBirthday() + "\n邮箱：" + goddess.getEmail() 
                                            + "\n手机号：" + goddess.getMobile());
                        } else {
                            step = 1;
                            pervious = null;
                            System.out.println("查询的女神不存在！");
                        }
                    } catch (NumberFormatException e) {
                        step = 1;
                        pervious = null;
                        System.out.println("请输入正确的女神ID：");
                    } catch (Exception e) {
                        e.printStackTrace();
                        step = 1;
                        pervious = null;
                        System.out.println("查询女神详细信息失败！");
                    }
                }
                if(OPERATION_GET.equals(pervious)){
                    step ++;
                }
            }
            //根据名字模糊查询女神信息
            if (OPERATION_SEARCH.equalsIgnoreCase(flag) || OPERATION_SEARCH.equals(pervious)) {
                pervious = OPERATION_SEARCH;
                if (step == 1) {
                    System.out.println("请输入任意女神名字的一个或多个字：");
                } else if (step == 2) {
                    try {
                        list = goddessAction.queryByUsername(flag);
                        if (list != null) {
                            for (Goddess g : list) {
                                System.out.println("编号：" + g.getId() + "，姓名：" + g.getUserName());
                            }
                            step = 1;
                            pervious = null;
                        } else {
                            step = 1;
                            pervious = null;
                            System.out.println("查询的女神不存在！");
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        step = 1;
                        pervious = null;
                        System.out.println("查询女神信息失败！");
                    }
                }
                if(OPERATION_SEARCH.equals(pervious)){
                    step ++;
                }
            }
            //添加女神信息
            if (OPERATION_ADD.equalsIgnoreCase(flag) || OPERATION_ADD.equals(pervious)) {
                pervious = OPERATION_ADD;
                if (step == 1) {
                    System.out.println("请输入女神[姓名]：");
                } else if (step == 2) {
                    goddess = new Goddess();
                    goddess.setUserName(flag);
                    System.out.println("请输入女神[年龄]：");
                } else if (step == 3) {
                    try {
                        goddess.setAge(Integer.valueOf(flag));
                        System.out.println("请输入女神[生日]，格式为yyyy-MM-dd：");
                    } catch (NumberFormatException e) {
                        step = 2;
                        System.out.println("请输入正确的女神的[年龄]：");
                    }
                } else if (step == 4) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    try {
                        goddess.setBirthday(sdf.parse(flag));
                    } catch (ParseException e) {
                        step = 3;
                        System.out.println("请输入正确的女神的[生日]：");
                    }
                    System.out.println("请输入女神[邮箱]：");
                } else if (step == 5) {
                    goddess.setEmail(flag);
                    System.out.println("请输入女神[手机号]：");
                } else if (step == 6) {
                    goddess.setMobile(flag);
                    try {
                        goddessAction.add(goddess);
                        System.out.println("添加女神信息成功！");
                        step = 1;
                        pervious = null;
                    } catch (Exception e) {
                        e.printStackTrace();
                        step = 1;
                        pervious = null;
                        System.out.println("添加女神信息失败！");
                    }
                } 
                if (OPERATION_ADD.equals(pervious)) {
                    step ++;
                }
            }
            //更新女神信息
            if (OPERATION_UPDATE.equalsIgnoreCase(flag) || OPERATION_UPDATE.equals(pervious)) {
                pervious = OPERATION_UPDATE;
                if (step == 1) {
                    System.out.println("请输入要更新的女神的[ID]：");
                } else if (step == 2) { 
                    try {
                        Integer id = Integer.valueOf(flag);
                        goddess = goddessAction.getGoddess(id);
                        if (goddess == null) {
                            step = 1;
                            System.out.println("输入的女神[ID]不存在哦！");
                        }
                    } catch (NumberFormatException e) {
                        step = 1;
                        System.out.println("请输入正确的女神[ID]]：");
                    } catch (Exception e) {
                        step = 1;
                        System.out.println("输入的女神[ID]不存在哦！");
                    }
                    System.out.println("请输入女神[姓名]：(如不更新该字段，则输入null)");
                } else if (step == 3) {
                    if (!"null".equalsIgnoreCase(flag)) {
                        goddess.setUserName(flag);
                    }
                    System.out.println("请输入女神[年龄]：(如不更新该字段，则输入null)");
                } else if (step == 4) {
                    try {
                        if (!"null".equalsIgnoreCase(flag)) {
                            Integer age = Integer.valueOf(flag);
                            goddess.setAge(age);
                        }
                        System.out.println("请输入女神[生日]，格式为yyyy-MM-dd：(如不更新该字段，则输入null)");
                    } catch (NumberFormatException e) {
                        step = 3;
                        System.out.println("请输入正确的女神[年龄]：");
                    }
                } else if (step == 5) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    try {
                        if (!"null".equalsIgnoreCase(flag)) {
                            goddess.setBirthday(sdf.parse(flag));
                        }
                        System.out.println("请输入女神[邮箱]：(如不更新该字段，则输入null)");
                    } catch (ParseException e) {
                        step = 4;
                        System.out.println("请输入正确的女神的[生日]：");
                    }
                } else if (step == 6) {
                    if (!"null".equalsIgnoreCase(flag)) {
                        goddess.setEmail(flag);
                    }
                    System.out.println("请输入女神[手机号]：(如不更新该字段，则输入null)");
                } else if (step == 7) {
                    if (!"null".equalsIgnoreCase(flag)) {
                        goddess.setMobile(flag);
                    }
                    try {
                        goddessAction.update(goddess);
                        System.out.println("更新女神信息成功！");
                        step = 1;
                        pervious = null;
                    } catch (Exception e) {
                        e.printStackTrace();
                        step = 1;
                        pervious = null;
                        System.out.println("更新女神信息失败！");
                    }
                } 
                if (OPERATION_UPDATE.equals(pervious)) {
                    step ++;
                }
            }
            //删除女神信息
            if (OPERATION_DELETE.equalsIgnoreCase(flag) || OPERATION_DELETE.equals(pervious)) {
                pervious = OPERATION_DELETE;
                if (step == 1) {
                    System.out.println("请输入要删除的女神[ID]：(删除多个用逗号隔开，如1,2)");
                } else if (step == 2) {
                    try {
                        String[] ss = flag.split(",");
                        Integer[] ids = new Integer[ss.length];
                        for (int i = 0; i < ss.length; i++) {
                            ids[i] = Integer.parseInt(ss[i]);
                        }
                        goddessAction.delete(ids);
                        System.out.println("删除女神信息成功！");
                        step = 1;
                        pervious = null;
                    } catch (NumberFormatException e) {
                        step = 1;
                        System.out.println("请输入正确的女神[ID]：");
                    } catch (Exception e) {
                        e.printStackTrace();
                        step = 1;
                        pervious = null;
                        System.out.println("删除女神信息失败！");
                    }
                }
                if(OPERATION_DELETE.equals(pervious)){
                    step ++;
                }
            }
        }
    }

}

图片描述

点击查看更多内容
发表于 2016.11.22 10:37， 共 4918 人浏览

本文原创发布于慕课网 ，转载请注明出处，谢谢合作

举报
35人点赞

若觉得本文不错，就分享一下吧！

评论

作者其他优质文章

正在加载中

凌_惜

JAVA开发工程师
手记
篇
粉丝
20
获赞与收藏
245

关注作者，订阅最新文章

阅读免费教程

感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消