JDBC复写上课代码（对岸的女生看过来篇）

这个demo我没有关闭connection，需要借鉴的朋友注意自己关闭。关于这个demo大家有什么意见请在下方留言或者私信我，谢谢各位。

程序代码如下：
女神类

package com.imooc.pojo;

import java.util.Date;

public class Goddness {
    private int id;
    private String user_name;
    private String sex;
    private int 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 int isdel;

    public Goddness() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Goddness(int id, String user_name, int age, Date birthday,
            String mobile) {
        super();
        this.id = id;
        this.user_name = user_name;
        this.age = age;
        this.birthday = birthday;
        this.mobile = mobile;
    }

    public Goddness( String user_name, String sex, int age,
            Date birthday, String email, String mobile, String create_user,
            Date create_date, String update_user, Date update_date, int 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.create_date = create_date;
        this.update_user = update_user;
        this.update_date = update_date;
        this.isdel = isdel;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUser_name() {
        return user_name;
    }

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

    public String getSex() {
        return sex;
    }

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

    public int getAge() {
        return age;
    }

    public void setAge(int 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 int getIsdel() {
        return isdel;
    }

    public void setIsdel(int isdel) {
        this.isdel = isdel;
    }

    @Override
    public String toString() {
        return "Goddness [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 + "]";
    }
}

JDBC工具类

package com.imooc.JDBCUtil;

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

public class JDBCUtil {
    private final static String URL = "jdbc:mysql://localhost:3306/imooc?useUnicode=true&characterEncoding=gbk";
    private final static String USERNAME = "dengdi";
    private final static String PASSWORD = "root";
    private static Connection connection = null;
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return connection;
    }
}

dao层实现类

package com.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 com.imooc.JDBCUtil.JDBCUtil;
import com.imooc.pojo.Goddness;

public class GoddnessDaoImp implements GoddessnDao {
    /*
     * (non-Javadoc)
     * 
     * @see com.imooc.dao.GoddessnDao#insert(com.imooc.pojo.Goddness)
     */
    @Override
    public void insert(Goddness god) {
        Connection con = JDBCUtil.getConnection();
        String sql = "INSERT INTO imooc_goddess(user_name,sex,age,birthday,"
                + "email,mobile,create_user,create_date,"
                + "update_user,update_date,isdel) "
                + "VALUES(?,'女',?,?,?,?,'Administrator',CURRENT_DATE(),'Administrator',CURRENT_DATE(),1)";
        PreparedStatement pre = null;
        try {
            pre = con.prepareStatement(sql);
            pre.setString(1, god.getUser_name());
            pre.setInt(2, god.getAge());
            pre.setDate(3, new Date(god.getBirthday().getTime()));
            pre.setString(4, god.getEmail());
            pre.setString(5, god.getMobile());
            pre.execute();
            pre.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.imooc.dao.GoddessnDao#delete(int)
     */
    @Override
    public void delete(int id) {
        Connection con = JDBCUtil.getConnection();
        String sql = "DELETE FROM imooc_goddess WHERE id=? ";
        PreparedStatement pre = null;
        try {
            pre = con.prepareStatement(sql);
            pre.setInt(1, id);
            pre.execute();
            pre.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    /*
     * (non-Javadoc)
     * 
     * @see com.imooc.dao.GoddessnDao#update(java.lang.String, int)
     */
    @Override
    public void update(String user_name, int id) {
        Connection con = JDBCUtil.getConnection();
        String sql = "UPDATE imooc_goddess SET user_name=? WHERE id=?";
        PreparedStatement pre = null;
        try {
            pre = con.prepareStatement(sql);
            pre.setString(1, user_name);
            pre.setInt(2, id);
            pre.executeUpdate();
            pre.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.imooc.dao.GoddessnDao#find(int)
     */
    @Override
    public Goddness find(int id) {
        Goddness goddness = null;
        Connection con = JDBCUtil.getConnection();
        String sql = "SELECT * FROM imooc_goddess WHERE id=?";
        PreparedStatement pre = null;
        ResultSet rs = null;
        try {
            pre = con.prepareStatement(sql);
            pre.setInt(1, id);
            rs = pre.executeQuery();
            while (rs.next()) {
                goddness = new Goddness(rs.getString("user_name"),
                        rs.getString("sex"), rs.getInt("age"),
                        rs.getDate("birthday"), rs.getString("email"),
                        rs.getString("mobile"), rs.getString("create_user"),
                        rs.getDate("create_date"), rs.getString("update_user"),
                        rs.getDate("update_date"), rs.getInt("isdel"));
            }
            rs.close();
            pre.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return goddness;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.imooc.dao.GoddessnDao#get()
     */
    @Override
    public List<Goddness> query() {
        List<Goddness> list = new ArrayList<Goddness>();
        Connection con = JDBCUtil.getConnection();
        String sql = "SELECT id,user_name,age,birthday,mobile FROM imooc_goddess";
        PreparedStatement pre = null;
        ResultSet rs = null;
        try {
            pre = con.prepareStatement(sql);
            rs = pre.executeQuery();
            while (rs.next()) {
                Goddness goddness = new Goddness(rs.getInt("id"),
                        rs.getString("user_name"), rs.getInt("age"),
                        rs.getDate("birthday"), rs.getString("mobile"));
                list.add(goddness);
            }
            rs.close();
            pre.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }
}

dao接口类

package com.imooc.dao;

import java.util.List;

import com.imooc.pojo.Goddness;

public interface GoddessnDao {

    public abstract void insert(Goddness god);

    public abstract void delete(int id);

    public abstract void update(String user_name, int id);

    public abstract Goddness find(int id);

    public abstract List<Goddness> query();
}

服务层(也就是action层)实现类

package com.imooc.service;

import java.util.List;

import com.imooc.dao.GoddessnDao;
import com.imooc.dao.GoddnessDaoImp;
import com.imooc.pojo.Goddness;

public class GoddessnServiceImp implements GoddnessService {
    private GoddessnDao gd;

    public GoddessnServiceImp() {
        gd=new GoddnessDaoImp();
    }

    /* (non-Javadoc)
     * @see com.imooc.service.GoddnessService#insert(com.imooc.pojo.Goddness)
     */
    @Override
    public void insert(Goddness god) {
        gd.insert(god);
    }

    /* (non-Javadoc)
     * @see com.imooc.service.GoddnessService#delete(int)
     */
    @Override
    public void delete(int id) {
        gd.delete(id);
    }

    /* (non-Javadoc)
     * @see com.imooc.service.GoddnessService#update(java.lang.String, int)
     */
    @Override
    public void update(String user_name, int id) {
        gd.update(user_name, id);
    }

    /* (non-Javadoc)
     * @see com.imooc.service.GoddnessService#find(int)
     */
    @Override
    public Goddness find(int id) {
        return gd.find(id);
    }

    /* (non-Javadoc)
     * @see com.imooc.service.GoddnessService#get()
     */
    @Override
    public List<Goddness> query() {
        return gd.query();
    }
}

服务层接口类

package com.imooc.service;

import java.util.List;

import com.imooc.pojo.Goddness;

public interface GoddnessService {

    public abstract void insert(Goddness god);

    public abstract void delete(int id);

    public abstract void update(String user_name, int id);

    public abstract Goddness find(int id);

    public abstract List<Goddness> query();
}

视图层（在后台输出的）

package com.imooc.view;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

import com.imooc.pojo.Goddness;
import com.imooc.service.GoddessnServiceImp;
import com.imooc.service.GoddnessService;

public class GoddnessView {
    private static final String CONTEXT = "欢迎来到女神禁区:\n" + "下面是女神禁区的功能列表:\n"
            + "[MIAN/M]主菜单:\n" + "[QUERY/Q]查看全部女神的信息:\n"
            + "[GET/G]查看某位女神的详细信息:\n" + "[ADD/A]添加自己心仪的女神:\n"
            + "[UPDATE/U]更新女神信息:\n" + "[DELETE/D]删除女神信息:\n"
            + "[EXIT/E]退出女神禁区:";

    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";

    public static void main(String[] args) {
        System.out.println(CONTEXT);
        GoddnessService gs=new GoddessnServiceImp();
        Scanner sc = new Scanner(System.in);
        String prenious=null;
        Goddness goddness=new Goddness();
        int step=1;
        while (true) {
            String in = sc.next();

            if (OPERATION_MAIN.equals(in.toUpperCase())
                    || OPERATION_MAIN.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println(CONTEXT);
            }

            else if (OPERATION_QUERY.equals(in.toUpperCase())
                    || OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())) {
                List<Goddness> list=gs.query();
                for (Goddness goddness01 : list) {
                    System.out.println("ID:"+goddness01.getId());
                    System.out.println("姓名:"+goddness01.getUser_name());
                    System.out.println("年龄:"+goddness01.getAge());
                    System.out.println("生日:"+goddness01.getBirthday());
                    System.out.println("电话:"+goddness01.getMobile());
                }
            }

            else if(OPERATION_GET.equals(in.toUpperCase())
                    || OPERATION_GET.substring(0, 1).equals(in.toUpperCase())
                    || OPERATION_GET.equals(prenious)){
                prenious=OPERATION_GET;
                int id=0;
                if(step==1){
                    System.out.println("请输入你想要查询的女神ID:");
                }
                if(step==2){
                    id=Integer.valueOf(in);
                    Goddness g=gs.find(id);
                    System.out.println(g);
                    step=0;
                    prenious=null;
                }
                step++;
//              System.out.println(step);
            }
            else if(OPERATION_ADD.equals(in.toUpperCase())
                    || OPERATION_ADD.substring(0, 1).equals(in.toUpperCase())
                    || OPERATION_ADD.equals(prenious)){
                prenious=OPERATION_ADD;

                if(step==1){
                    System.out.println("请输入女神姓名:");
                }
                if(step==2){
                    goddness.setUser_name(in);
                    System.out.println("请输入女神年龄:");
                }
                if(step==3){
                    goddness.setAge(Integer.valueOf(in));
                    System.out.println("请输入女神生日(格式为:yyyy-MM-dd):");
                }
                if(step==4){
                    Date date=null;
                    try {
                        date=new SimpleDateFormat("yyyy-MM-dd").parse(in);
                    } catch (ParseException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                    goddness.setBirthday(date);
                    System.out.println("请输入女神邮箱:");
                }
                if(step==5){
                    goddness.setEmail(in);
                    System.out.println("请输入女神电话:");
                }
                if(step==6){
                    goddness.setMobile(in);
                    gs.insert(goddness);
                    System.out.println("创建女神成功！");
                    step=0;
                    prenious=null;
                }
                step++;
            }
            else if(OPERATION_UPDATE.equals(in.toUpperCase())
                    || OPERATION_UPDATE.substring(0, 1).equals(in.toUpperCase())
                    || OPERATION_UPDATE.equals(prenious)){
                prenious=OPERATION_UPDATE;
                int id=0;
                String name;
                if(step==1){
                    System.out.println("请输入你想要修改的女神ID:");
                }
                if(step==2){
                    id=Integer.valueOf(in);
                    System.out.println("请输入女神修改的名字:");
                }
                if(step==3){
                    name=in;
                    gs.update(name, id);
                    System.out.println("更新成功!");
                    step=0;
                    prenious=null;
                }
                step++;
            }
            else if(OPERATION_DELETE.equals(in.toUpperCase())
                    || OPERATION_DELETE.substring(0, 1).equals(in.toUpperCase())
                    || OPERATION_DELETE.equals(prenious)){
                prenious=OPERATION_DELETE;
                int id=0;
                if(step==1){
                    System.out.println("请输入你想要删除的女神ID:");
                }
                if(step==2){
                    id=Integer.valueOf(in);
                    gs.delete(id);
                    System.out.println("删除成功!");
                    step=0;
                    prenious=null;
                }
                step++;
            }
            else if (OPERATION_EXIT.equals(in.toUpperCase())
                    || OPERATION_EXIT.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println("您已经成功退出女神禁区");
                break;
            }else{
                System.out.println("您输入的值出错了！");
            }

        }

    }
}