JDBC基于MVC架构项目实例-实现对MySQL数据表的增删改查、调用数据表中的存储过程和函数

---

江畔何人初见月，江月何年初照人？—张若虚《春江花月夜》

---

一 需求分析

目的：实现 Java 程序对 MySQL 数据库的增删改查操作,……。
工具： MySQL 数据库，Eclipse，Navicat for MySQL数据库管理工具
设计模式：
MVC 三层架构

模型层 对应数据库的映射，对数据库映射的抽象方法（增删改查）
控制层 控制数据的流通，把数据拼装给视图层
视图层 数据的显示
从下往上依次开发

代码分离，分工协作

二 编写 Java Project

新建项目 jdbc ，在新建目录 lib 下存放驱动 mysql-connector-java-5.1.39-bin.jar,添加为 Build Path。
JDBC-MySQL驱动下载
JDK版本：1.8.0_91
项目目录：

三 建立数据库层 （db层）

建立数据库的语句：

数据库名： peng
表名：imooc_goddess

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50712
Source Host           : localhost:3306
Source Database       : peng

Target Server Type    : MYSQL
Target Server Version : 50712
File Encoding         : 65001

Date: 2016-10-04 20:53:44
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for imooc_goddess
-- ----------------------------
DROP TABLE IF EXISTS `imooc_goddess`;
CREATE TABLE `imooc_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=15 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of imooc_goddess
-- ----------------------------
INSERT INTO `imooc_goddess` VALUES ('2', '小彭', null, '23', null, null, null, null, null, null, null, null);
INSERT INTO `imooc_goddess` VALUES ('6', '胖子', '0', '78', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('7', '小溪', '0', '34', '2016-10-03', 'hongming@qq.com', '1211555599', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('9', '小霞', null, '23', '1990-09-09', 'xiaoxia@qq.com', '232445455', null, '2016-10-03', null, '2016-10-03', null);
INSERT INTO `imooc_goddess` VALUES ('10', 'hh', '1', '23', '1990-09-09', 'jkjfskf', '12323', 'Admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('11', '平', '1', '23', '2998-04-09', 'jjjj@ww.com', '1323', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('12', '航母', '1', '23', '2333-09-09', 'jkksjkjf', '1232', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('13', '胖纸', '1', '23', '1991-09-09', 'jjijijij', '1323244', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');
INSERT INTO `imooc_goddess` VALUES ('14', '校长', '1', '18', '1998-09-09', 'jkjijij@qq.com', '112323424', 'admin', '2016-10-03', 'admin', '2016-10-03', '1');

测试数据库连接

所属包：package com.jxust.test

TestDBUtil.java

//package com.jxust.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class TestDBUtil {
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/peng?characterEncoding=utf8&useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    private static Connection conn = null;

    public static void main(String[] args) throws Exception {
        // 1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        // 2.获得数据库连接
        conn = DriverManager.getConnection(URL, USER, PASSWORD);
        // 3.通过数据库的连接操作数据库，实现增删改查

        /**
         * Statement方法 普通的不带参的查询SQL 每次执行sql语句，数据库都要执行sql语句的编译
         * 最好用于仅执行一次查询并返回结果的情形 Statement stmt = conn.createStatement();
         * ResultSet rs = stmt.executeQuery("select user_name,age from
         * imooc_goddess");
         */

        /**
         * PreparedStatement 方法 可变参数的SQL,编译一次,执行多次,效率高
         */
        // 取user_name和age数据
        PreparedStatement ptmt = conn.prepareStatement("select user_name,age from imooc_goddess");
        ResultSet rs = ptmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getString("user_name") + "," + rs.getString("age"));
        }

    }

}

执行结果：

胖子,78
小溪,34
hh,24
平,32
航母,24
胖纸,26
小那,null
晓华,null

我的数据库连接类-DBUtil

调用 getConn方法就可以的到数据库连接对象 conn。

所属包：package com.jxust.db

DBUtil.java

//package com.jxust.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/peng?characterEncoding=utf8&useSSL=false";
    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 getConn() {
        return conn;
    }
}

当前项目结构：

四 建立模型层 （model层）

创建数据库表对应实体类 Goddess 。

所属包：package com.jxust.model

Goddess.java

//package com.jxust.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;
    }

    @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
                + ", update_user=" + update_user + ", create_date=" + create_date + ", update_date=" + update_date
                + ", isdel=" + isdel + "]";
    }

}

五 建立数据库访问层 （dao层）

对应数据库增删改查的方法，用来访问数据库实现数据的持久化。

所属包：package com.jxust.dao

GoddessDao.java

//package com.jxust.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.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

//import com.jxust.db.DBUtil;
//import com.jxust.model.Goddess;

/**
 * 类中含有4个查询方法,增删改方法各一个
 * @author Peng
 *
 */
public class GoddessDao {
    /**
     * 增 
     * 向数据库添加数据
     * 
     * @param g
     * @throws SQLException
     */
    public void addGoddess(Goddess g) throws SQLException {
        Connection conn = DBUtil.getConn();
        String sql = "" + "insert into imooc_goddess" + "(user_name,sex,age,birthday,email,mobile,"
                + "create_user,create_date,update_user,update_date,isdel)"
                + "values(?,1,?,?,?,?,'admin',current_date(),'admin',current_date(),1)";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1, g.getUser_name());
        // ptmt.setInt(2, g.getSex());
        ptmt.setInt(2, g.getAge());
        ptmt.setDate(3, new Date(g.getBirthday().getTime()));
        ptmt.setString(4, g.getEmail());
        ptmt.setString(5, g.getMobile());
        // ptmt.setString(7, g.getCreate_user());
        // ptmt.setString(8, g.getUpdate_user());
        // ptmt.setInt(9, g.getIsdel());
        ptmt.execute();
    }

    /**
     * 改 
     * 通过拼接SQL更新语句，更新数据库中的数据
     * 
     * @param g
     * @throws SQLException
     * @throws ParseException
     */
    public void updateGoddess(Goddess g) throws SQLException, ParseException {
        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();
        System.out.println(g.toString());
        sb.append("update imooc_goddess set sex = 1 ");

        if (!g.getUser_name().equals("null")) {
            sb.append(" ,user_name=" + "'" + g.getUser_name() + "'");
        }
        if (!g.getAge().equals(0)) {
            sb.append(" ,age=" + g.getAge());
        }
        SimpleDateFormat sf = new SimpleDateFormat("yy-MM-dd");
        String DateStr1 = "1970-01-01";
        java.util.Date date = null;
        date = (java.util.Date) sf.parse(DateStr1);
        if (!((new Date(g.getBirthday().getTime()).compareTo(date)) == 0)) {
            sb.append(" ,birthday=" + "'" + (new Date(g.getBirthday().getTime()) + "'"));
        }
        if (!g.getEmail().equals("null")) {
            sb.append(" ,email=" + "'" + g.getEmail() + "'");
        }
        if (!g.getMobile().equals("null")) {
            sb.append(" ,mobile=" + "'" + g.getMobile() + "'");
        }
        if (!g.getId().equals("null")) {
            sb.append(" where id=" + g.getId());
        }
        // System.out.println(sb.toString());
        PreparedStatement ptmt = conn.prepareStatement(sb.toString());
        ptmt.execute();
    }

    /**
     * 删 
     * 根据id删除数据库的内容
     * 
     * @param id
     * @throws SQLException
     */
    public void delGoddess(Integer id) throws SQLException {
        Connection conn = DBUtil.getConn();
        String sql = "delete from imooc_goddess " + " where id =? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setInt(1, id);
        ptmt.execute();
    }
    /**
     * 查
     * 查询数据表中所有内容的id,user_name,age字段
     * @return
     * @throws SQLException
     */
    public List<Goddess> query() throws SQLException {
        Connection conn = DBUtil.getConn();
        List<Goddess> list = new ArrayList<Goddess>();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select id,user_name,age from imooc_goddess");
        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"));

            list.add(g);
        }

        return list;
    }
    /**
     * 查
     * 多条件查询,输入name,mobile,email,3个值，配合通配符查询
     * @param name
     * @param mobile
     * @param email
     * @return
     * @throws SQLException
     */
    public List<Goddess> query(String name, String mobile, String email) throws SQLException {
        Connection conn = DBUtil.getConn();
        List<Goddess> list = new ArrayList<Goddess>();
        StringBuilder sb = new StringBuilder();
        sb.append("select * from imooc_goddess ");
        sb.append(" where user_name like ? or mobile like ?  or email like ?");
        //or 和 and的配合使用
        //sb.append(" where user_name like ? and (mobile like ?  or email like ?)");
        PreparedStatement ptmt = conn.prepareStatement(sb.toString());
        ptmt.setString(1, "%" + name + "%");// 通配符
        ptmt.setString(2, "%" + mobile + "%");
        ptmt.setString(3, "%" + email + "%");

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

            list.add(g);
        }

        return list;
    }

    /**
     * 查 根据id值，查询所有信息
     * 
     * @param id
     * @return
     * @throws SQLException
     */
    public Goddess get(Integer id) throws SQLException {
        Goddess g = null;
        Connection conn = DBUtil.getConn();
        String sql = " select * from imooc_goddess " + " where id =? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setInt(1, id);
        ResultSet rs = ptmt.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"));
        }
        return g;
    }

    /**
     * 查 通过拼接SQL查询语句，结合通配符，实条件查询
     * 
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Goddess> query(List<Map<String, Object>> params) throws SQLException {
        List<Goddess> list = new ArrayList<Goddess>();
        Connection conn = DBUtil.getConn();
        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") + " ");
            }
        }
        // System.out.println(sb.toString());
        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"));
            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"));

            list.add(g);
        }
        return list;
    }
}

当前项目结构：

测试 dao 层代码

为 view 层调用，先行测试

所属包：package com.jxust.test

TestDao.java

//package com.jxust.test;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//import com.jxust.dao.GoddessDao;
//import com.jxust.model.Goddess;

public class TestDao {
    public static void main(String[] args) throws SQLException {
        GoddessDao gd = new GoddessDao();

        // List<Goddess> list = gd.query("胖子","3","h");
        // for(int i=0;i<list.size();i++){
        //
        // System.out.println(i+" "+ list.get(i).toString());
        // }

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

        Map<String, Object> param = new HashMap<String, Object>();
        param.put("name", "user_name");
        param.put("rela", "like");
        param.put("value", "'%胖子%'");
        params.add(param);
        param = new HashMap<String, Object>();
        param.put("name", "mobile");
        param.put("rela", "=");
        param.put("value", "'1211555599'");
        params.add(param);
        List<Goddess> list = gd.query(params);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).toString());
        }
        Goddess g = new Goddess();
        g.setUser_name("小溪");
        g.setAge(34);
        g.setSex(0);
        g.setBirthday(new Date());
        g.setEmail("hongming@qq.com");
        g.setMobile("1211555599");
        g.setCreate_user("admin");
        g.setUpdate_user("admin");
        g.setIsdel(1);
        // g.setId(5);

        // Goddess g2 = gd.get(6);

        // System.out.println(g2.toString());
        // gd.delGoddess(5);
        // gd.updateGoddess(g);
        // gd.addGoddess(g);
    }
}

六 建立控制层 （action层）

Action是一个控制器

所属包：package com.jxust.action

GoddessAction.java

//package com.jxust.action;

import java.sql.SQLException;
import java.text.ParseException;
import java.util.List;
import java.util.Map;

//import com.jxust.dao.GoddessDao;
//import com.jxust.model.Goddess;

/**
 * 控制器层 调用dao层的增删改查方法
 * @author Peng
 *
 */
public class GoddessAction {
    /**
     * 增
     * @param goddess
     * @throws SQLException
     */
    public void add(Goddess goddess) throws SQLException {
        GoddessDao dao = new GoddessDao();
        dao.addGoddess(goddess);
    }

    /**
     * 更
     * @param g
     * @throws SQLException
     * @throws ParseException
     */
    public void edit(Goddess g) throws SQLException, ParseException {
        GoddessDao dao = new GoddessDao();
        dao.updateGoddess(g);
    }

    /**
     * 删
     * @param id
     * @throws SQLException
     */
    public void del(Integer id) throws SQLException {
        GoddessDao dao = new GoddessDao();
        dao.delGoddess(id);
    }

    /**
     * 查 
     * @param id
     * @return
     * @throws SQLException
     */
    public Goddess get(Integer id) throws SQLException {
        GoddessDao dao = new GoddessDao();
        return dao.get(id);
    }

    /**
     * 查
     * @return
     * @throws SQLException
     */
    public List<Goddess> query() throws SQLException {
        GoddessDao dao = new GoddessDao();
        return dao.query();
    }

    /**
     * 查 
     * @param params
     * @return
     * @throws SQLException
     */
    public List<Goddess> query(List<Map<String, Object>> params) throws SQLException {
        GoddessDao dao = new GoddessDao();
        return dao.query(params);
    }
}

测试 action 层代码

所属包:package com.jxust.test

TestAction.java

//package com.jxust.test;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//import com.jxust.action.GoddessAction;
//import com.jxust.model.Goddess;

public class TestAction {
    public static void main(String[] args) throws SQLException {
        GoddessAction action = new GoddessAction();

        Goddess g = new Goddess();
        g.setUser_name("小青1");
        g.setAge(34);
        g.setSex(0);
        g.setBirthday(new Date());
        g.setEmail("xiaoqing@qq.com");
        g.setMobile("1101141146");
        g.setCreate_user("admin");
        g.setUpdate_user("admin");
        g.setIsdel(1);
        g.setId(8);

        //action.add(g);
        //action.edit(g);

        //action.del(8);
        List<Map<String,Object>> params = new ArrayList<>();
        Map<String,Object> map = new HashMap<>();
        map.put("name", "user_name");
        map.put("rela", "=");
        map.put("value", "'小溪'");
        params.add(map);    
        List<Goddess> result = action.query(params);
        for (int i=0;i<result.size();i++){
            System.out.println(result.get(i).getId()+":"+result.get(i).getUser_name());
        }
    }
}

七 建立视图层 （view层）

所属包： package com.jxust.view

View.java

//package com.jxust.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 com.jxust.action.GoddessAction;
//import com.jxust.model.Goddess;

/**
 * 视图层
 * 与用户进行交互的页面，这里使用控制台，没有使用jsp页面
 * 页面功能还不完善
 * @author Peng
 *
 */
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 scanner = new Scanner(System.in);
        Goddess goddess = new Goddess();
        GoddessAction action = new GoddessAction();
        String previous = null;
        Integer step = 1;
        String cha = null;
        while (scanner.hasNext()) {
            String in = scanner.next().toString();
            if (OPERATION_EXIT.equals(in.toUpperCase())// 大写
                    || OPERATION_EXIT.substring(0, 1).equals(in.toUpperCase())) {// 输入e
                System.out.println("您已成功退出女神禁区");
                break;
            }else if (OPERATION_BREAK.equals(in.toUpperCase())
                    || OPERATION_BREAK.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println(CONTEXT);
                previous = null;
                step = 1;
                cha = null;
                continue;   
            }else if (OPERATION_QUERY.equals(in.toUpperCase())
                    || OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println("下面是本禁区女神的基本信息");
                try {
                    List<Goddess> list = action.query();
                    for (Goddess go : list) {
                        System.out.println("编号：" + go.getId() + ",姓名：" + go.getUser_name());
                    }

                } catch (SQLException e) {
                    e.printStackTrace();
                }       
                continue;
            } else if (OPERATION_MAIN.equals(in.toUpperCase())
                    || OPERATION_MAIN.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println(CONTEXT);
                continue;
            } else if (OPERATION_DELETE.equals(in.toUpperCase())
                    || OPERATION_DELETE.substring(0, 1).equals(in.toUpperCase()) || OPERATION_DELETE.equals(previous)) {
                previous = OPERATION_DELETE;
                if (1 == step) {
                    System.out.println("请输入待删除女神的编号");
                    step++;
                } else if (2 == step) {
                    step = 1;
                    try {
                        action.del(Integer.parseInt(in));
                        System.out.println("删除女神成功");
                    } catch (NumberFormatException e) {
                        e.printStackTrace();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                continue;
            } else if (OPERATION_GET.equals(in.toUpperCase()) || OPERATION_GET.substring(0, 1).equals(in.toUpperCase())
                    || OPERATION_GET.equals(previous)) {
                previous = OPERATION_GET;
                if (1 == step) {
                    System.out.println("请输入女神的编号，查看某位女神的详细信息");
                    step++;
                } else if (2 == step) {
                    try {
                        step = 1;
                        Goddess go = action.get(Integer.parseInt(in));
                        System.out.println("编号：" + go.getId());
                        System.out.println("姓名：" + go.getUser_name());
                        System.out.println("年龄：" + go.getAge());
                        System.out.println("性别：" + "女");
                        System.out.println("生日：" + go.getBirthday());
                        System.out.println("邮箱：" + go.getEmail());
                        System.out.println("手机号：" + go.getMobile());
                    } catch (Exception e) {
                        System.out.println("没有找到这位女神的信息");
                    }
                }
                continue;
            } else if (OPERATION_SEARCH.equals(in.toUpperCase())
                    || OPERATION_SEARCH.substring(0, 1).equals(in.toUpperCase()) || OPERATION_SEARCH.equals(previous)
                    || "NAME".equals(cha) || "MOBILE".equals(cha)) {
                previous = OPERATION_SEARCH;
                if (1 == step) {
                    System.out.println("输入要查询的字段" + "\n姓名[NAME]" + "\n手机号[MOBILE]");
                    step++;
                } else if (2 == step) {
                    if ("NAME".equals(in.toUpperCase())) {
                        cha = "NAME";
                        System.out.println("请输入要查询的姓名：");
                    } else if ("MOBILE".equals(in.toUpperCase())) {
                        cha = "MOBILE";
                        System.out.println("请输入要查询的手机号：");
                    } else {
                        step = 1;
                        continue;
                    }
                    step++;
                } else if (3 == step) {
                    if ("NAME".equals(cha)) {
                        List<Map<String, Object>> params = new ArrayList<Map<String, Object>>();
                        Map<String, Object> param = new HashMap<String, Object>();
                        param.put("name", "user_name");
                        param.put("rela", "like");
                        param.put("value", "'%" + in + "%'");
                        params.add(param);
                        List<Goddess> list;
                        try {
                            list = action.query(params);
                            for (int i = 0; i < list.size(); i++) {
                                System.out.println("编号：" + list.get(i).getId());
                                System.out.println("姓名：" + list.get(i).getUser_name());
                                System.out.println("年龄：" + list.get(i).getAge());
                                System.out.println("性别：" + "女");
                                System.out.println("生日：" + list.get(i).getBirthday());
                                System.out.println("邮箱：" + list.get(i).getEmail());
                                System.out.println("手机号：" + list.get(i).getMobile());
                                System.out.println();
                            }
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                        step = 1;
                        continue;
                    } else if ("MOBILE".equals(cha)) {
                        List<Map<String, Object>> params = new ArrayList<Map<String, Object>>();
                        Map<String, Object> param = new HashMap<String, Object>();
                        param.put("name", "mobile");
                        param.put("rela", "like");
                        param.put("value", "'%" + in + "%'");
                        params.add(param);
                        List<Goddess> list;
                        try {
                            list = action.query(params);
                            for (int i = 0; i < list.size(); i++) {
                                System.out.println("编号：" + list.get(i).getId());
                                System.out.println("姓名：" + list.get(i).getUser_name());
                                System.out.println("年龄：" + list.get(i).getAge());
                                System.out.println("性别：" + "女");
                                System.out.println("生日：" + list.get(i).getBirthday());
                                System.out.println("邮箱：" + list.get(i).getEmail());
                                System.out.println("手机号：" + list.get(i).getMobile());
                                System.out.println();
                            }
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                        step = 1;
                        continue;
                    }
                }
            } else if (OPERATION_ADD.equals(in.toUpperCase()) || OPERATION_ADD.substring(0, 1).equals(in.toUpperCase())
                    || OPERATION_ADD.equals(previous)) {
                previous = OPERATION_ADD;
                // 新增女神
                if (1 == step) {
                    System.out.println("请输入女神的[姓名]");
                    step++;
                } else if (2 == step) {
                    goddess.setUser_name(in);
                    System.out.println("请输入女神的[年龄]");
                    step++;
                } else if (3 == step) {
                    goddess.setAge(Integer.valueOf(in));
                    System.out.println("请输入女神的[生日],格式如:yy-MM-dd");
                    step++;
                } else if (4 == step) {
                    SimpleDateFormat sf = new SimpleDateFormat("yy-MM-dd");
                    Date birthday = null;
                    try {
                        birthday = sf.parse(in);
                        goddess.setBirthday(birthday);
                        System.out.println("请输入女神的[邮箱]");
                    } catch (ParseException e) {
                        System.out.println("您输入的格式有误，请重新输入");
                        step = 3;
                    }
                    step++;
                } else if (5 == step) {
                    goddess.setEmail(in);
                    System.out.println("请输入女神的[手机号]");
                    step++;
                } else if (6 == step) {
                    goddess.setMobile(in);
                    try {
                        action.add(goddess);
                        System.out.println("新增女神成功！");

                    } catch (SQLException e) {
                        System.out.println("新增女神失败！");
                    }
                    previous=null;
                    step=1;
                    continue;
                }
            } else if (OPERATION_UPDATE.equals(in.toUpperCase()) || OPERATION_UPDATE.substring(0, 1).equals(in.toUpperCase())
                    || OPERATION_UPDATE.equals(previous)) {
                previous = OPERATION_UPDATE;                
                // 更新女神
                if (1 == step) {
                    System.out.println("请输入女神的编号");
                    step++;
                }else if (2 == step) {

                    goddess.setId(Integer.parseInt(in));
                    System.out.println("请输入女神的[姓名]，不更改输入null");
                    step++;
                } else if (3 == step) {
                    goddess.setUser_name(in);
                    System.out.println("请输入女神的[年龄],不更改输入0");
                    step++;
                } else if (4 == step) {
                    goddess.setAge(Integer.parseInt(in));
                    System.out.println("请输入女神的[生日],格式如:yy-MM-dd,不更改请输入1970-01-01");
                    step++;
                } else if (5 == step) {
                    SimpleDateFormat sf = new SimpleDateFormat("yy-MM-dd");
                    Date birthday = null;
                    try {           
                        birthday = sf.parse(in);                        
                        goddess.setBirthday(birthday);                  
                        System.out.println("请输入女神的[邮箱],不更改输入null");
                    } catch (ParseException e) {
                        System.out.println("您输入的格式有误，请重新输入");
                        step = 3;
                    }
                    step++;
                } else if (6 == step) {                 
                    goddess.setEmail(in);
                    System.out.println("请输入女神的[手机号],不更改输入null");
                    step++;
                } else if (7 == step) {
                    goddess.setMobile(in);
                    try {
                        action.edit(goddess);
                        System.out.println("更新女神成功！");

                    } catch (SQLException | ParseException e) {
                        System.out.println("更新女神失败！");
                        e.printStackTrace();
                    }
                    previous=null;
                    step=1;
                    continue;
                }
            }else {
                System.out.println("您输入的值为" + in);
            }
        }
    }
}

当前的项目结构：

在数据库上可以通过

call 

八 调用数据库上的存储过程和自定义函数

关于向数据库中添加存储过程和自定义函数：http://blog.csdn.net/Peng_Hong_fu/article/details/52737402

数据库上的存储过程

1、存储过程selectAll

CREATE PROCEDURE selectAll()
BEGIN
SELECT * FROM imooc_goddess;
END

在数据库端测试存储过程

call selectAll();

---

2、存储过程select_filter()

CREATE  PROCEDURE `select_filter`(IN `sp_name` varchar(20))
    READS SQL DATA
BEGIN
    IF sp_name IS NULL OR sp_name = '' THEN
        SELECT * FROM imooc_goddess;
    ELSE
    IF LENGTH(sp_name)>9 AND SUBSTRING(sp_name,1,1) THEN
        SELECT * FROM imooc_goddess WHERE mobile=sp_name;
        ELSE
            SELECT * FROM imooc_goddess WHERE user_name LIKE CONCAT('%',sp_name,'%');
END if;
END IF;
END

3、存储过程select_count()

CREATE  PROCEDURE `select_count`(OUT `counts` int)
BEGIN
    SELECT count(*) INTO counts FROM imooc_goddess;
END

数据库上的自定义函数

1、自定义函数returnDate()

CREATE DEFINER=`root`@`localhost` FUNCTION `returnDate`() RETURNS varchar(30) CHARSET utf8
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日:%H时:%i分:%s秒')

在 Navicat 查询里调用

select returnDate();

在 java 项目上调用

CallableStatement cs = conn.prepareCall("{?= call returnDate()}");

2、自定义函数returnFloat()

CREATE DEFINER=`root`@`localhost` FUNCTION `returnFloat`(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS float(10,2) unsigned
RETURN (num1+num2)/2

Java 项目上调用

所属包：package com.jxust.dao

ProcedureDao.java

//package com.jxust.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

//import com.jxust.db.DBUtil;
//import com.jxust.model.Goddess;

public class ProcedureDao {
    /**
     * 带输出参数的存储过程
     * 
     * @return
     * @throws SQLException
     */
    public static Integer select_count() throws SQLException {
        Integer count;
        // 获取连接
        Connection conn = DBUtil.getConn();
        // 获取CallableStatement
        CallableStatement cs = conn.prepareCall("call select_count(?)");
        cs.registerOutParameter(1, Types.INTEGER);
        cs.execute();
        count = cs.getInt(1);
        return count;
    }

    /**
     * 带输入参数的存储过程
     * 
     * @param sp_name
     * @return
     * @throws SQLException
     */
    public static List<Goddess> select_filter(String sp_name) throws SQLException {
        List<Goddess> list = new ArrayList<Goddess>();
        // 获取连接
        Connection conn = DBUtil.getConn();
        // 获取CallableStatement
        CallableStatement cs = conn.prepareCall("call select_filter(?)");
        cs.setString(1, sp_name);
        // 执行存储过程
        cs.execute();

        ResultSet rs = cs.getResultSet();
        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.setMobile(rs.getString("mobile"));

            list.add(g);

        }
        return list;
    }

    /**
     * 带无参数的存储过程
     * 
     * @throws SQLException
     */
    public static void selectAll() throws SQLException {
        // 1.获取连接
        Connection conn = DBUtil.getConn();
        // 2.获取CallableStatement
        CallableStatement cs = conn.prepareCall("call selectAll()");
        // 3.执行存储过程
        cs.execute();
        // 处理返回的结果：结果集，出参
        ResultSet rs = cs.getResultSet();
        while (rs.next()) {
            System.out.println(rs.getString("user_name") + " " + rs.getString("email") + " " + rs.getString("mobile"));
        }
    }

    /**
     * 不带参数的函数
     * 
     * @throws SQLException
     */
    public static void returnDate() throws SQLException {
        // 1.获取连接
        Connection conn = DBUtil.getConn();
        // 2.获取CallableStatement
        CallableStatement cs = conn.prepareCall("{?= call returnDate()}");
        cs.registerOutParameter(1, Types.VARCHAR);
        // or cs.registerOutParameter(1, Types.TIMESTAMP);
        // 3.执行存储过程
        cs.execute();
        String date = cs.getString(1);
        System.out.println("时间：" + date);
    }

    /**
     * 带参数的函数
     * 
     * @param num1
     * @param num2
     * @throws SQLException
     */
    public static void returnaAvg(Integer num1, Integer num2) throws SQLException {
        // 1.获取连接
        Connection conn = DBUtil.getConn();
        // 2.获取CallableStatement
        CallableStatement cs = conn.prepareCall("{?= call returnFloat(?,?)}");
        cs.registerOutParameter(1, Types.FLOAT);
        cs.setInt(2, num1);
        cs.setInt(3, num2);
        // 3.执行存储过程
        cs.execute();
        Float f = cs.getFloat(1);
        System.out.println("平均值：" + f);
    }
}

所属包：package com.jxust.test

JDBCTestProcedure.java

//package com.jxust.test;

import java.sql.SQLException;

//import com.jxust.dao.ProcedureDao;


public class JDBCTestProcedure {
    /*
     * 带输入参数的存储过程
    public static void main(String[] args)  {               
        String sp_name = "小";
        List<Goddess> result = null;
        try {
            result =select_filter(sp_name);
            showResult(result);

        } catch (SQLException e) {          
            e.printStackTrace();
        }       
    }
    public static List<Goddess> select_filter(String sp_name) throws SQLException{
        ProcedureDao dao = new ProcedureDao();
        return dao.select_filter(sp_name);
    }
    public static void showResult(List<Goddess> result){
        for (int i = 0; i < result.size(); i++) {
            System.out.println(result.get(i).getId()+result.get(i).getUser_name()+result.get(i).getMobile());
        }
    }
    */

    /*
     * 无参数的输出过程
    public static void main(String[] args) {
        ProcedureDao dao = new ProcedureDao();
        try {
            dao.selectAll();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    */
    /*
     * 带输出参数的存储过程
    public static void main(String[] args) {
        ProcedureDao dao = new ProcedureDao();
        Integer result ;
        try {
            result = dao.select_count();
            System.out.println("表的行数："+result);
        } catch (SQLException e) {          
            e.printStackTrace();
        }
    }
    */

    /*
     * 不带参数的函数
    public static void main(String[] args) {
        ProcedureDao dao = new ProcedureDao();

        try {
            dao.returnDate();

        } catch (SQLException e) {          
            e.printStackTrace();
        }
    }
    */
    /**
     * 带输入参数的函数
     * @param args
     */
    public static void main(String[] args) {
        ProcedureDao dao = new ProcedureDao();
        Integer num1 = 2;
        Integer num2 = 3;
        try {
            dao.returnaAvg(num1, num2);

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

当前项目结构：

项目源码

项目源码(没有调用存储过程和函数)下载地址：http://download.csdn.net/detail/peng_hong_fu/9646503
项目源码(增加调用存储过程和函数)下载地址：http://download.csdn.net/detail/peng_hong_fu/9646696