JDBC：对岸女孩看过来项目实例-原创手记-慕课网

本实例通过采用JDBC+MySQL+Navicat for MySQL连接了女神数据库，并可在控制台页面对数据库进行增删改查操作。
DB数据库：

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

/**
 * 数据库连接
 * @throws ClassNotFoundException 
 */

public class DBUtil {
  private static final String url="jdbc:mysql://localhost:3306/imooc";
  private static final String user="root";
  private static final String password="root";
  private static Connection conn=null;
  public static Connection getConnection(){
      return conn;
  }
    //静态块
  static{
      //1.加载驱动程序
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2.获得数据库的连接
             conn=DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

  }

}

模型层：

package 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 Goddess(){

    }

    @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 + "]";
    }

    public Goddess(String user_name, Integer sex, Integer age, Date birthday,
            String email, String mobile,String create_user,String update_user,Integer isdel) {
        super();
        this.user_name = user_name;
        this.sex = sex;
        this.age = age;
        this.birthday = birthday;
        this.email = email;
        this.mobile = mobile;
        this.create_user=create_user;
        this.update_user=update_user;
        this.isdel = isdel;
    }
    public Integer getIsdel() {
        return isdel;
    }
    public void setIsdel(Integer isdel) {
        this.isdel = 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;
    }

}

增删改查GoddessDao类

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

import db.DBUtil;

import Model.Goddess;

/**
 * 对Goddess数据库的操作（增删改查）
 * @author Administrator
 *
 */
public class GoddessDao {

    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(),?)";
        PreparedStatement ptmt=conn.prepareStatement(sql);
        ptmt.setString(1,goddess.getUser_name());
        ptmt.setInt(2, goddess.getSex());
        ptmt.setInt(3,goddess.getAge());
        ptmt.setDate(4,new Date(goddess.getBirthday().getTime()));
        ptmt.setString(5,goddess.getEmail());
        ptmt.setString(6, goddess.getMobile());
        ptmt.setString(7, goddess.getCreate_user());
        ptmt.setString(8, goddess.getUpdate_user());
        ptmt.setInt(9,goddess.getIsdel());

        ptmt.execute();
    }
    //得到数据库中的所有条数据
    public void get() throws SQLException{
        Connection conn = DBUtil.getConnection();
        StringBuilder sb=new StringBuilder(" select * from imooc_goddess ");
        PreparedStatement ptmt=conn.prepareStatement(sb.toString());
        System.out.println(sb.toString());
         ResultSet rs = ptmt.executeQuery();
        List<Goddess> gs =new ArrayList<Goddess>();
        while (rs.next()) {
            Goddess goddess=new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setSex(rs.getInt("sex"));
            goddess.setAge(rs.getInt("age"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));

            gs.add(goddess);
        }
        for(Goddess g:gs){
            System.out.println(g.toString());
        }
    }
    //删除数据库中的某条数据
    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);
        ptmt.execute();
    }
    //更新数据库
    public void updateGoddess(Goddess goddess) 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,goddess.getUser_name());
        ptmt.setInt(2, goddess.getSex());
        ptmt.setInt(3,goddess.getAge());
        ptmt.setDate(4,new Date(goddess.getBirthday().getTime()));
        ptmt.setString(5,goddess.getEmail());
        ptmt.setString(6, goddess.getMobile());
        ptmt.setString(7, goddess.getUpdate_user());
        ptmt.setInt(8,goddess.getIsdel());
        ptmt.setInt(9, goddess.getId());
        ptmt.execute();
    }
    //查询数据库(根据用户名)
    public void query(String name) throws SQLException{
        Connection conn = DBUtil.getConnection();
        StringBuilder sb=new StringBuilder(" select * from imooc_goddess");
        sb.append(" where user_name like ? ");
        PreparedStatement ptmt=conn.prepareStatement(sb.toString());
        ptmt.setString(1, "%"+name+"%");
//      ptmt.setString(2,"%"+mobile+"%");
        System.out.println(sb.toString());
         ResultSet rs = ptmt.executeQuery();
        List<Goddess> gs =new ArrayList<Goddess>();
        while (rs.next()) {
            Goddess goddess=new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setSex(rs.getInt("sex"));
            goddess.setAge(rs.getInt("age"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));

            gs.add(goddess);
        }
        for(Goddess g:gs){
            System.out.println(g.toString());
        }
    }
    //根据List列表来查询
    public List<Goddess> query(List<Map<String,Object>> params) throws SQLException{
        Connection conn = DBUtil.getConnection();
        //若查询条件之间的关系是and,则1=1，若查询条件之间是or,则1=0，
        StringBuilder sb=new StringBuilder(" select * from imooc_goddess where 1=1");
        if(params!=null&&params.size()>0){
            for(Map<String,Object> map:params){
                sb.append(" and "+map.get("name")+" "+map.get("rela")+" "+map.get("value")+" ");
            }
        }
        System.out.println(sb.toString());
        PreparedStatement ptmt=conn.prepareStatement(sb.toString());
         ResultSet rs = ptmt.executeQuery();
        List<Goddess> gs =new ArrayList<Goddess>();
        while (rs.next()) {
            Goddess goddess=new Goddess();
            goddess.setId(rs.getInt("id"));
            goddess.setUser_name(rs.getString("user_name"));
            goddess.setSex(rs.getInt("sex"));
            goddess.setAge(rs.getInt("age"));
            goddess.setBirthday(rs.getDate("birthday"));
            goddess.setEmail(rs.getString("email"));
            goddess.setMobile(rs.getString("mobile"));
            goddess.setCreate_user(rs.getString("create_user"));
            goddess.setCreate_date(rs.getDate("create_date"));
            goddess.setUpdate_user(rs.getString("update_user"));
            goddess.setUpdate_date(rs.getDate("update_date"));
            goddess.setIsdel(rs.getInt("isdel"));
            gs.add(goddess);
        }
        return gs;

    }
    //查询单个女神的详细信息
    public Goddess queryOne(Integer id) throws SQLException{
        Goddess goddess=null;
        Connection conn=DBUtil.getConnection();
        String sql =""+
                  " select * from imooc_goddess "+
                  " where id=? ";
        PreparedStatement ptmt=conn.prepareStatement(sql);
        ptmt.setInt(1, id);
        ResultSet rs=ptmt.executeQuery();

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

        }
        return goddess;
    }
}

Controller控制层


```package Action;

import java.sql.SQLException;
import java.text.DateFormat;
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 Model.Goddess;
import dao.GoddessDao;

public class GoddessAction {

    /**
     * 控制类
     * @throws SQLException 
     */
    //增加
    public void add(Goddess goddess) throws SQLException{
        GoddessDao gd=new GoddessDao();
        goddess.setSex(1);
        goddess.setCreate_user("admin");
        goddess.setUpdate_user("admin");
        goddess.setIsdel(0);
        gd.addGoddess(goddess);
    }
    //更新
    public void update(Goddess goddess) throws SQLException{
        GoddessDao gd=new GoddessDao();
        gd.updateGoddess(goddess);
    }
    //查询
    public void queryOne(Integer id) throws SQLException{
        GoddessDao gd=new GoddessDao();
        Goddess goddess=gd.queryOne(id);
        System.out.println(goddess.toString());
    }
    //查询所有
    public void get() throws SQLException{
        GoddessDao gd = new GoddessDao();
        gd.get();
    }
    //
    public void query(List<Map<String,Object>> params) throws SQLException{
        GoddessDao gd=new GoddessDao();
        List<Goddess>gs=gd.query(params);
        for(Goddess goddess:gs){
            System.out.println(goddess.toString());
        }
    }
    //删除
    public void del(Integer id) throws SQLException{
        GoddessDao gd=new GoddessDao();
        gd.delGoddess(id);
    }

}

视图层View
把各个功能用函数封装再调用，防止主函数逻辑太乱

package 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 Action.GoddessAction;
import Model.Goddess;

public class View {
    /**
     * View层
     */
    private static final String context = "" + 
     "欢迎来到女神禁区:\n" + 
     "下面是女神禁区的功能列表:\n"+ 
     "[MAIN/M]:主菜单\n" + 
     "[GET/G]查看全部女神的信息\n"+ 
     "[QUERYONE/QU]查看某位女神的详细信息\n" + 
     "[ADD/A]:添加女神信息\n"+ 
     "[UPDATE/U]:更新女神信息\n" + 
     "[DELETE/D]删除女神信息\n"+ 
     "[QUERYLIST/QUE]查询女神信息(根据姓名和电话号码查询)\n" + 
     "[EXIT/E]退出女神禁区\n"+ 
     "[BREAK/B]退出当前功能，返回主菜单";
    private static final String OPERATOR_MAIN="Main";
    private static final String OPERATOR_GET="GET";
    private static final String OPERATOR_QUERYONE="QUERYONE";
    private static final String OPERATOR_ADD="ADD";
    private static final String OPERATOR_UPDATE="UPDATE";
    private static final String OPERATOR_DELETE="DELETE";
    private static final String OPERATOR_QUERYLIST="QUERYLIST";
    private static final String OPERATOR_EXIT="EXIT";
    private static final String OPERATOR_BREAK="BREAK";

    static GoddessAction action=new GoddessAction();

    public static void main(String[] args) {
        System.out.println(context);
        Scanner console=new Scanner(System.in);
        while(true){
            System.out.println("请输入您的选择:");
            String in=console.next();
            System.out.println("您输入的值为:"+in);
            if(OPERATOR_EXIT.equals(in.toUpperCase())
               ||OPERATOR_EXIT.substring(0, 1).equals(in.toUpperCase())){
                System.out.println("您已退出女神禁区！");
                System.exit(0);
            }else if(OPERATOR_MAIN.equals(in.toUpperCase())
                   ||OPERATOR_MAIN.substring(0, 1).equals(in.toUpperCase())){
                System.out.println("*****返回主菜单*****");
                System.out.println(context);
                continue;
                }else if(OPERATOR_BREAK.equals(in.toUpperCase())
                   ||OPERATOR_BREAK.substring(0, 1).equals(in.toUpperCase())){
                    System.out.println("*****退出当前功能,返回主菜单*****");
                System.out.println(context);
                continue;
                }else if(OPERATOR_QUERYONE.equals(in.toUpperCase())
                   ||OPERATOR_QUERYONE.substring(0, 2).equals(in.toUpperCase())){
                    System.out.println("*****根据id查询某位女神的详细信息：*****");
                   System.out.println("请输入女神[id]:");
                   Integer id=console.nextInt();
                    QueryOne(id);
                    continue;
                }else if(OPERATOR_UPDATE.equals(in.toUpperCase())
                       ||OPERATOR_UPDATE.substring(0, 1).equals(in.toUpperCase())){
                    System.out.println("*****根据id更新某位女神信息：*****");
                    update();
                    continue;
                }else if(OPERATOR_DELETE.equals(in.toUpperCase())
                       ||OPERATOR_DELETE.substring(0, 1).equals(in.toUpperCase())){
                    System.out.println("*****根据id删除某位女神信息：*****");
                    delete();
                    continue;
                }else if(OPERATOR_QUERYLIST.equals(in.toUpperCase())
                           ||OPERATOR_QUERYLIST.substring(0, 3).equals(in.toUpperCase())){
                    System.out.println("*****根据姓名和电话号码查询：*****");
                        queryList();
                        continue;
                }else if(OPERATOR_GET.equals(in.toUpperCase())
                       ||OPERATOR_GET.substring(0, 1).equals(in.toUpperCase())){
                        System.out.println("*****全部女神信息如下:*****");
                        get();
                        continue;
                }else if(OPERATOR_ADD.equals(in.toUpperCase())
                   ||OPERATOR_ADD.substring(0, 1).equals(in.toUpperCase())){
                    System.out.println("*****添加某位女神：*****");
                    add();
                    continue;
                }

        }
    }
    public static void queryList() {
        List<Map<String,Object>> params=new ArrayList<Map<String,Object>>();
        Map<String,Object> map=new HashMap<String,Object>();
        Scanner in=new Scanner(System.in);
        map.put("name","user_name" );
        System.out.println("请输入姓名关系(=或like)");
        String relation=in.next();
        map.put("rela",relation);
        System.out.println("请输入要查询的女神['姓名']");
        String name=in.next();
        map.put("value",name);
        params.add(map);
        map=new HashMap<String,Object>();
        map.put("name","mobile" );
        System.out.println("请输入电话关系(=或like)");
        String relation2=in.next();
        map.put("rela",relation2);
        System.out.println("请输入要查询的女神['电话号码']");
        String mobile=in.next();
        map.put("value",mobile);
        params.add(map);
        try {
            action.query(params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //删除女神信息
    public static void delete() {
        System.out.println("请输入要删除女神的[id]");
        Scanner in=new Scanner(System.in);
        Integer id=in.nextInt();
        try {
            action.del(id);
            System.out.println("删除女神成功！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //更新女神信息
    public static void update() {
        Goddess goddess=new Goddess();
        Scanner console=new Scanner(System.in);
        System.out.println("请输入女神[姓名]:");
        String username=console.next();
        goddess.setUser_name(username);
        System.out.println("请输入女神[性别]:");
        Integer sex=console.nextInt();
        goddess.setSex(sex);
        System.out.println("请输入女神[年龄]:");
        Integer age=console.nextInt();
        goddess.setAge(age);
        System.out.println("请输入女神[生日]:如(yyyy-mm-dd)");
        String str=console.next();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-mm-dd");
            Date birthday=null;
            try {
                birthday = sdf.parse(str);
                goddess.setBirthday(birthday);
                System.out.println("请输入女神[邮箱]:");
                String email=console.next();
                goddess.setEmail(email);
                System.out.println("请输入女神[电话号码]:");
                String mobile=console.next();
                goddess.setMobile(mobile);
                System.out.println("请输入女神[更新人]:");
                String update_user=console.next();
                goddess.setUpdate_user(update_user);
                System.out.println("请输入女神[是否删除]:");
                Integer isdel=console.nextInt();
                goddess.setIsdel(isdel);
                System.out.println("请输入要更新的女神[id]:");
                Integer id=console.nextInt();
                goddess.setId(id);
                action.update(goddess);
                System.out.println("更新女神成功！");
            } catch (ParseException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }

    }
    //根据id查询女神信息
    public static void QueryOne(Integer id) {

        try {
            System.out.println("id=="+id+"的女神信息如下：");
            action.queryOne(id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //查询所有女神信息
    public static void get() {
        try {
            action.get();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //添加女神
    public static void add() {
    Goddess goddess=new Goddess();
//      GoddessAction action=new GoddessAction();
        Scanner console=new Scanner(System.in);
        System.out.println("请输入女神[姓名]:");
        String username=console.next();
        goddess.setUser_name(username);
        System.out.println("请输入女神[年龄]:");
        Integer age=console.nextInt();
        goddess.setAge(age);
        System.out.println("请输入女神[生日]:如(yyyy-mm-dd)");
        String str=console.next();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-mm-dd");
        try {
            Date birthday=sdf.parse(str);
            goddess.setBirthday(birthday);
            System.out.println("请输入女神[邮箱]:");
            String email=console.next();
            goddess.setEmail(email);
            System.out.println("请输入女神[电话号码]:");
            String mobile=console.next();
            goddess.setMobile(mobile);
            action.add(goddess);
            System.out.println("添加女神成功！");
        } catch (ParseException e) {
            e.printStackTrace();
            System.out.println("添加女神失败！");
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

运行结果截图：
图片描述