jdbc之对面的女孩看过来-原创手记-慕课网

package jdbc.imooc;

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

/---------------------DBUtil 包--------------------/

public class DBUtil {
private static final String URL="jdbc:mysql://127.0.0.1:3306/god?useUnicode=true&characterEncoding=utf-8";
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;

}

}

/-----------------------------------------GoddessAction 包------------------------------/
package jdbc.imooc.action;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import jdbc.imooc.dao.Goddessdao;
import jdbc.imooc.model.Goddess;

public class GoddessAction {

public void add(Goddess goddess) throws SQLException {
    Goddessdao dao=new Goddessdao();
    goddess.setSex(1);
    goddess.setCreate_user("ADMIN");
    goddess.setUpdate_user("ADMIN");
    goddess.setIsdel(0);
    dao.addGoddess(goddess);
}

public Goddess get(Integer id) throws SQLException {
    Goddessdao dao = new Goddessdao();
    return dao.get(id);
}

public void edit(Goddess goddess) throws SQLException {
    Goddessdao dao = new Goddessdao();
    dao.updateGoddess(goddess);
}

public void del(Integer id) throws SQLException {
    Goddessdao dao = new Goddessdao();
    dao.delGoddess(id);
}

public List<Goddess> query() throws Exception {
    // TODO 自动生成的方法存根
    Goddessdao dao = new Goddessdao();
    return dao.query();
}

public List<Goddess> query(List<Map<String, Object>> params) throws Exception {
    // TODO 自动生成的方法存根
    Goddessdao dao = new Goddessdao();
    return dao.query(params);
}

}

/---------------------------------Goddessdao包 ------------------------------------/
package jdbc.imooc.dao;

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

import jdbc.imooc.DBUtil;
import jdbc.imooc.model.Goddess;

public class Goddessdao {
/*

查询
/
public void addGoddess(Goddess g) 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(),?)";
PreparedStatement ptmt = conn.prepareStatement(sql);
/
- 获取用户添加进来的信息
- /
  ptmt.setString(1, g.getUser_name());
  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.getMobile());
  ptmt.setString(7, g.getCreate_user());
  ptmt.setString(8, g.getUpdate_user());
  ptmt.setInt(9, g.getIsdel());
  ptmt.execute();
  }
  /
更新
/
public void updateGoddess(Goddess g) throws SQLException{
// 拿到数据库的连接
Connection conn=DBUtil.getConnection();
String sql="" +
" update imooc_goddess " +
" set user_name=?,sex=?,age=?,birthday=?,email=?,mobile=?, " +
" update_user=?,update_date=current_date(),isdel=? " +
" where id=? ";
PreparedStatement ptmt=conn.prepareStatement(sql);
/
- 获取用户添加更新进来的信息
- */
  ptmt.setString(1, g.getUser_name());
  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.getMobile());
  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();
//定义 “删除” 数据库语句
String sql="" +
" delete from imooc_goddess " +
" where id=? ";
PreparedStatement ptmt = conn.prepareStatement(sql);
/
- 获取用户所选要删除的信息
- */
  ptmt.setInt(1, id);
  //执行sql语句
  ptmt.execute();
  }
/*
列表

*/
public List<Goddess> query() throws Exception{
List<Goddess> result=new ArrayList<Goddess>();

Connection conn=DBUtil.getConnection();
StringBuilder sb=new StringBuilder();
sb.append("select id,user_name,age from imooc_goddess  ");

PreparedStatement ptmt=conn.prepareStatement(sb.toString());

ResultSet rs=ptmt.executeQuery();

Goddess g=null;
while(rs.next()){
    g=new Goddess();
    g.setId(rs.getInt("id"));
    g.setUser_name(rs.getString("user_name"));
    g.setAge(rs.getInt("age"));
    result.add(g);
}
return result;

}
public List<Goddess> query(String name,String mobile,String email) throws Exception{
List<Goddess> result=new ArrayList<Goddess>();

Connection conn=DBUtil.getConnection();
StringBuilder sb=new StringBuilder();
sb.append("select * from imooc_goddess  ");

sb.append(" where user_name like ? and mobile like ? and email like ?");

PreparedStatement ptmt=conn.prepareStatement(sb.toString());
ptmt.setString(1, "%"+name+"%");
ptmt.setString(2, "%"+mobile+"%");
ptmt.setString(3, "%"+email+"%");
System.out.println(sb.toString());
ResultSet rs=ptmt.executeQuery();

Goddess g=null;
while(rs.next()){
    g=new Goddess();
    g.setId(rs.getInt("id"));
    g.setUser_name(rs.getString("user_name"));
    g.setAge(rs.getInt("age"));
    g.setSex(rs.getInt("sex"));
    g.setBirthday(rs.getDate("birthday"));
    g.setEmail(rs.getString("email"));
    g.setMobile(rs.getString("mobile"));
    g.setCreate_date(rs.getDate("create_date"));
    g.setCreate_user(rs.getString("create_user"));
    g.setUpdate_date(rs.getDate("update_date"));
    g.setUpdate_user(rs.getString("update_user"));
    g.setIsdel(rs.getInt("isdel"));

    result.add(g);
}
return result;

}
public List<Goddess> query(List<Map<String, Object>> params) throws Exception{
List<Goddess> result=new ArrayList<Goddess>();

Connection conn=DBUtil.getConnection();
StringBuilder sb=new StringBuilder();
sb.append("select * from imooc_goddess where 1=1 ");

if(params!=null&&params.size()>0){
    for (int i = 0; i < params.size(); i++) {
        Map<String, Object> map=params.get(i);
        sb.append(" and  "+map.get("name")+" "+map.get("rela")+" "+map.get("value")+" ");
    }
}

PreparedStatement ptmt=conn.prepareStatement(sb.toString());

System.out.println(sb.toString());
ResultSet rs=ptmt.executeQuery();

Goddess g=null;
while(rs.next()){
    g=new Goddess();
    g.setId(rs.getInt("id"));
    g.setUser_name(rs.getString("user_name"));
    g.setAge(rs.getInt("age"));
    g.setSex(rs.getInt("sex"));
    g.setBirthday(rs.getDate("birthday"));
    g.setEmail(rs.getString("email"));
    g.setMobile(rs.getString("mobile"));
    g.setCreate_date(rs.getDate("create_date"));
    g.setCreate_user(rs.getString("create_user"));
    g.setUpdate_date(rs.getDate("update_date"));
    g.setUpdate_user(rs.getString("update_user"));
    g.setIsdel(rs.getInt("isdel"));

    result.add(g);
}
return result;

}

详细信息
/
public Goddess get(Integer id) throws SQLException{
Goddess g = null;
// 拿到数据库的连接
Connection conn = DBUtil.getConnection();
//定义 “搜索” 数据库语句
String sql="" +
" select from imooc_goddess " +
" where id=? ";
PreparedStatement ptmt = conn.prepareStatement(sql);
/*
- 获取用户所选要显示的信息
- /
  ptmt.setInt(1, id);
  //执行sql语句
  ResultSet rs = ptmt.executeQuery();
  /
- execute 实现更改数据库的方法
- executequery 实现搜索方法
- */
  //循环详细信息
  while (rs.next()){
  g = new Goddess();
  //获取详细信息
  g.setId(rs.getInt("id"));
  g.setUser_name(rs.getString("user_name"));
  g.setAge(rs.getInt("age"));
  g.setSex(rs.getInt("sex"));
  g.setBirthday(rs.getDate("birthday"));
  g.setEmail(rs.getString("email"));
  g.setMobile(rs.getString("mobile"));
  g.setCreate_date(rs.getDate("create_date"));
  g.setCreate_user(rs.getString("create_user"));
  g.setUpdate_date(rs.getDate("update_date"));
  g.setUpdate_user(rs.getString("update_user"));
  g.setIsdel(rs.getInt("isdel"));
```
    }
```
  return g;
  }
  }

/------------------------------------------------Goddess 包----------------------------------/
package jdbc.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 String update_user;
private Date create_date;
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 String getUpdate_user() {
return update_user;
}
public void setUpdate_user(String update_user) {
this.update_user = update_user;
}
public Date getCreate_date() {
return create_date;
}
public void setCreate_date(Date create_date) {
this.create_date = create_date;
}
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;
}
//tostring 可以用于打印出从数据库中获取的数据
@Override
public String toString() {
return "Goddess \n 编号：" + id + "\n 姓名：" + user_name + "\n 性别："

sex + "\n 年龄：" + age + "\n 生日：" + birthday
"\n 邮箱：" + email + "\n 手机号码：" + mobile
;
}

}

/----------------------------------------View 包------------------------------------/
package jdbc.imooc.view;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

import jdbc.imooc.action.GoddessAction;
import jdbc.imooc.model.Goddess;

public class View {

private static final String CONTEXT="欢迎来到用户禁区：\n" +
        "下面是用户禁区的功能列表：\n" +
        "[MAIN/M]:主菜单\n" +
        "[QUERY/Q]:查看全部用户的信息\n" +
        "[GET/G]:查看某位用户的详细信息\n" +
        "[ADD/A]:添加用户信息\n" +
        "[UPDATE/U]:更新用户信息\n" +
        "[DELETE/D]:删除用户信息\n" +
        "[SEARCH/S]:查询用户信息(根据姓名、手机号来查询)\n" +
        "[EXIT/E]:退出用户禁区\n" +
        "[BREAK/B]:退出当前功能，返回主菜单";

private static final String OPERATION_MAIN="MAIN";
private static final String OPERATION_QUERY="QUERY";
private static final String OPERATION_GET="GET";
private static final String OPERATION_ADD="ADD";
private static final String OPERATION_UPDATE="UPDATE";
private static final String OPERATION_DELETE="DELETE";
private static final String OPERATION_SEARCH="SEARCH";
private static final String OPERATION_EXIT="EXIT";
private static final String OPERATION_BREAK="BREAK";

public static void main(String[] args) {

    System.out.println(CONTEXT);
    //怎么保持程序一直运行

    Scanner scan=new Scanner(System.in);
    Goddess goddess=new Goddess();
    GoddessAction action=new GoddessAction();
    String prenious=null; //记忆标记
    Integer step=1;

    List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();

    Map<String,Object> param=new HashMap<String,Object>();
    while(scan.hasNext()){
        String in=scan.next().toString();
        /*---------------------退出功能---------------------------------*/
        if(OPERATION_EXIT.equals(in.toUpperCase())
                ||OPERATION_EXIT.substring(0, 1).equals(in.toUpperCase())){
            System.out.println("您已成功退出用户禁区。");
            break;
        }
        /*---------------------搜索功能-----------------------------------*/
        else if(OPERATION_QUERY.equals(in.toUpperCase())
                ||OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())){
            try {
                List<Goddess> list=action.query();
                for (Goddess go : list) {
                    System.out.println(go.getId()+",姓名："+go.getUser_name());
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
        /*--------------------------返回主菜单功能------------------------------*/
        else if(OPERATION_BREAK.equals(in.toUpperCase())
                ||OPERATION_BREAK.substring(0, 1).equals(in.toUpperCase())){
            System.out.println("退出当前功能，返回主菜单");
            System.out.println(CONTEXT);
            continue;
        }
        /*---------------------------模糊查询功能--------------------------------*/
        else if(OPERATION_SEARCH.equals(in.toUpperCase())
                ||OPERATION_SEARCH.substring(0,1).equals(in.toUpperCase())
                ||OPERATION_SEARCH.equals(prenious)){
            prenious=OPERATION_SEARCH;
            System.out.println("查询用户信息（根据姓名，手机号来查询）");
            if(1==step){
                System.out.println("输入用户姓名：");
            }else if(2==step){

                param.put("name", "user_name");
                param.put("rela", "=");
                param.put("value", in);
                params.add(param);
                System.out.println("输入用户手机");
            }else if(3==step){

                param.put("name", "mobile");
                param.put("rela", "=");
                param.put("value", in);
                params.add(param);

                List<Goddess> result;
                try {
                    result = action.query(params);
                    for(int i=0;i<result.size();i++){
                        System.out.println(result.get(i).toString());
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if(OPERATION_SEARCH.equals(prenious)){
                step++;
            }

        }
        /*----------------------------详细查询功能--------------------------*/
        else if(OPERATION_GET.equals(in.toUpperCase())
                ||OPERATION_GET.substring(0,1).equals(in.toUpperCase())
                ||OPERATION_GET.equals(prenious)){
            prenious=OPERATION_GET;

                if(1==step){
                System.out.println("请输入用户编号：");
                }else if(2==step){
                    try {
                    Integer id=new Integer(in);
                    Goddess gs=action.get(id);
                    System.out.println(gs.toString());
                        System.out.println("查询完成！");
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if(OPERATION_GET.equals(prenious)){
                    step++;
                }

        }
        /*---------------------------显示主菜单功能-------------------------------------*/
        else if(OPERATION_MAIN.equals(in.toUpperCase())
                ||OPERATION_MAIN.substring(0,1).equals(in.toUpperCase())){
            System.out.println("显示主菜单");
            System.out.println(CONTEXT);
        }
        /*-------------------------------删除功能------------------------------------*/
        else if(OPERATION_DELETE.equals(in.toUpperCase())
                ||OPERATION_DELETE.substring(0,1).equals(in.toUpperCase())
                ||OPERATION_DELETE.equals(prenious)){
            prenious=OPERATION_DELETE;
            System.out.println("删除用户信息");
            if(1==step){
                System.out.println("输入用户id");
            }else if(2==step){
                Integer id =new Integer(in);
                try {
                    action.del(id);
                    System.out.println("删除用户成功");
                } catch (SQLException e) {
                    // TODO 自动生成的 catch 块
                    e.printStackTrace();
                }
            }
            if(OPERATION_DELETE.equals(prenious)){
                step++;
            }
        }
        /*----------------------------------更新功能----------------------------------*/
        else if(OPERATION_UPDATE.equals(in.toUpperCase())
                ||OPERATION_UPDATE.substring(0,1).equals(in.toUpperCase())
                ||OPERATION_UPDATE.equals(prenious)){
            prenious=OPERATION_UPDATE;
            System.out.println("更新用户信息");
            if(1==step){
                System.out.println("请输入用户编号：");
            }else if(2==step){
                Integer id =new Integer(in);
                goddess.setId(id);
                System.out.println("输入用户姓名：");
            }else if(3==step){
                goddess.setUser_name(in);
                System.out.println("输入用户手机");
            }else if(4==step){
                goddess.setMobile(in);
                System.out.println("输入用户邮箱");
            }else if(5==step){
                goddess.setEmail(in);
                System.out.println("输入用户年龄");
            }else if (6==step){
                Integer age=new Integer(in);
                goddess.setAge(age);
                System.out.println("输入用户生日，格式如：yyyy-mm-dd");
            }else if(7==step){
                SimpleDateFormat sf=new SimpleDateFormat("yyyy-mm-dd");
                Date brithday=null;
                try {
                    brithday = sf.parse(in);
                    goddess.setBirthday(brithday);

                    action.edit(goddess);
                    System.out.println("修改用户成功");
                } catch (ParseException e) {
                    e.printStackTrace();
                    System.out.println("您输入的格式有误，请重新输入");
                    step=6;
                } catch (SQLException e) {
                    // TODO 自动生成的 catch 块
                    e.printStackTrace();
                }
            }
            if(OPERATION_UPDATE.equals(prenious)){
                step++;
            }

        }
        /*-----------------------------------添加功能--------------------------------*/
        else if(OPERATION_ADD.equals(in.toUpperCase())
                ||OPERATION_ADD.substring(0, 1).equals(in.toUpperCase())
                ||OPERATION_ADD.equals(prenious)){
            prenious=OPERATION_ADD;
            //新增用户

            if(1==step){
                System.out.println("请输入用户的［姓名］");
            }else if(2==step){
                goddess.setUser_name(in);
                System.out.println("请输入用户的［年龄］");
            }else if(3==step){
                goddess.setAge(Integer.valueOf(in));
                System.out.println("请输入用户的［生日］，格式如：yyyy-MM-dd");
            }else if(4==step){
                SimpleDateFormat sf=new SimpleDateFormat("yyyy-MM-dd");
                Date birthday=null;
                try {
                    birthday = sf.parse(in);
                    goddess.setBirthday(birthday);
                    System.out.println("请输入用户的［邮箱］");
                } catch (ParseException e) {
                    e.printStackTrace();
                    System.out.println("您输入的格式有误，请重新输入");
                    step=3;
                }
            }else if(5==step){
                goddess.setEmail(in);
                System.out.println("请输入用户的［手机号］");
            }else if(6==step){
                goddess.setMobile(in);

                try {
                    action.add(goddess);
                    System.out.println("新增用户成功");
                } catch (Exception e) {
                    e.printStackTrace();
                    System.out.println("新增用户失败");
                }
            }
            if(OPERATION_ADD.equals(prenious)){
                step++;                 
            }
        }else{
            System.out.println("您输入的值为:"+in);               
        }

    }
}

}