JDBC之 “ 对岸的女孩看过来”

---

Demo完整示例：gitHub

伏法朝朝忧闷，强梁夜夜欢歌，损人利己骑马骡，正直公停挨饿，
修桥补路瞎眼，杀人放火儿多，我到西天问我佛，佛说……我也没辙！

---

1、JDBC简介

2、JDBC项目效果展示

3、JDBC实战——打通数据库

新建Java Project 项目，取名为jdbc_showGrils

新建文件夹取名lib

加载驱动程序，把驱动文件放在lib文件中。在文本最后会有实际联系代码文件下载地址，可以下载来进行复制即可。

添加进环境变量中

新建一个类

https://img-blog.youkuaiyun.com/20180209111915258?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvd3RkYXNr/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast” alt=”这里写图片描述” title=”” />ravity/SouthEast)

鼠标在“”内按F2

选择第一个

选择第三个 Exception

创建数据库

新建表

sql脚本

/*
Navicat MySQL Data Transfer

Source Server         : 本地
Source Server Version : 50615
Source Host           : localhost:3306
Source Database       : imooc

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

Date: 2015-01-14 00:06:01
*/

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=2 DEFAULT CHARSET=utf8;

Alter DATABASE imooc DEFAULT CHARACTER SET 'utf8';
SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_results='utf8';

-- ----------------------------
-- Records of imooc_goddess
-- ----------------------------
INSERT INTO `imooc_goddess` VALUES ('1', '小溪', '1', '22', '2000-12-12', 'xiaoxi@163.com', '13911111111', 'ADMIN', '2015-01-08', 'ADMIN', '2015-01-08', '0');

package com.imooc.db;

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

import com.mysql.jdbc.Statement;

public class DBUtil {

    //imooc为数据库名称
    private static final String URL="jdbc:mysql://127.0.0.1:3306/imooc";
    private static final String NAME="root";
    private static final String PASSWORD="root";

    public static void main(String[] args) throws Exception {

        //1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");

        //2.获得数据库的连接
        Connection conn =  DriverManager.getConnection(URL, NAME, PASSWORD);

        //3.通过数据库的连接操作数据库，实现增删改查
        Statement stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT user_name,age FROM imooc_gooddess");


    }
}

注意需要引入java.sql包下面的

4、JDBC实战——搭建模型层

新建包文件，类文件

package com.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 String create_date;
    private String 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 String getCreate_date() {
        return create_date;
    }
    public void setCreate_date(String create_date) {
        this.create_date = create_date;
    }
    public String getUpdate_date() {
        return update_date;
    }
    public void setUpdate_date(String update_date) {
        this.update_date = update_date;
    }
    public Integer getIsdel() {
        return isdel;
    }
    public void setIsdel(Integer isdel) {
        this.isdel = isdel;
    }


}

新建类goddessDao.java

 package com.imooc.dao;

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

import com.imooc.db.DBUtil;
import com.imooc.model.Goddess;
import com.mysql.jdbc.Statement;

public class GoddessDao {

    public void addGoddess() {
        Connection conn = DBUtil.getConnection();
    }

    public void updateGoddess() {

    }

    public void delGoddess() {

    }

    public List<Goddess> query() throws Exception{
        Connection conn = DBUtil.getConnection();
        Statement stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT user_name,age FROM imooc_goddess");

        List<Goddess> gs =new ArrayList<Goddess>();

        Goddess g =null;

        while (rs.next()) {
            g = new Goddess();
            g.setUser_name(rs.getString("user_name"));
            g.setAge(rs.getInt("age"));

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

    public Goddess get() {
        return null;
    }
}

修改DBUtil.java类

package com.imooc.db;

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

import com.mysql.jdbc.Statement;

public class DBUtil {

    // imooc为数据库名称
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/imooc";
    private static final String NAME = "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, NAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return conn;
    }

}

GoddessDao.java类

package com.imooc.dao;

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

import com.imooc.db.DBUtil;
import com.imooc.model.Goddess;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;

public class GoddessDao {

    // 添加女神信息
    public void addGoddess(Goddess g) throws Exception {
        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 = (PreparedStatement) conn.prepareStatement(sql);// 预编译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.setDate(8, (Date) g.getUpdate_date());
        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 = (PreparedStatement) conn.prepareStatement(sql);// 预编译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 = (PreparedStatement) conn.prepareStatement(sql);// 预编译sql语句

        ptmt.setInt(1, id);
        ptmt.execute();
    }

    //查找全部1
    public List<Goddess> query() throws Exception {
        Connection conn = DBUtil.getConnection();
        Statement stmt = (Statement) conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT user_name,age FROM imooc_goddess");

        List<Goddess> gs = new ArrayList<Goddess>();

        Goddess g = null;

        while (rs.next()) {
            g = new Goddess();
            g.setUser_name(rs.getString("user_name"));
            g.setAge(rs.getInt("age"));

            gs.add(g);
        }
        return gs;
    }
    //根据参数查找1
    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 = (PreparedStatement) 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;
    }

    //根据参数查找2
    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 = (PreparedStatement) 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 = (PreparedStatement) conn.prepareStatement(sql);// 预编译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"));
            g.setIsdel(rs.getInt("isdel"));
        }
        return g;
    }
}

GoddessAction.java测试类

package com.imooc.action;

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

import com.imooc.dao.GoddessDao;
import com.imooc.model.Goddess;

public class GoddessAction {

    public static void main(String[] args) throws Exception {
        GoddessDao g = new GoddessDao();

//      List<Goddess> result = g.query("夏","18500355555","xiao");

        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", "LIKE");
        param.put("value", "'%18500355555%'");
        params.add(param);

        List<Goddess> result = g.query(params);

        for (int i = 0; i < result.size(); i++) {
            System.out.println(result.get(i).toString());
        }

        Goddess g1 =new Goddess();

        g1.setUser_name("小美");
        g1.setAge(24);
        g1.setSex(1);
        g1.setBirthday(new Date());
        g1.setEmail("xiaoxia@imooc.com");
        g1.setMobile("18500355555");
        g1.setUpdate_user("ADMIN");
        g1.setIsdel(1);
        g1.setId(3);


//      Goddess g2= g.get(1);
//      System.out.println(g2.toString());
//      g.delGoddess(3);
//      g.updateGoddess(g1);
//      g.addGoddess(g1);
    }
}

5、JDBC实战——搭建控制层

修改GoddessAction.java类，作为控制层

package com.imooc.action;

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.imooc.dao.GoddessDao;
import com.imooc.model.Goddess;

public class GoddessAction {

    public void add(Goddess goddess) throws Exception {
        GoddessDao dao = new GoddessDao();
        dao.addGoddess(goddess);
    }

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

    public void edit(Goddess goddess) throws Exception {
        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 {
        GoddessDao dao = new GoddessDao();
        return dao.query();
    }

    public List<Goddess> query(List<Map<String, Object>> params) throws Exception {
        GoddessDao dao = new GoddessDao();
        return dao.query(params);
    }


}

新建com.imooc.test包TestAction.java测试类进行测试

package com.imooc.test;

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

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

public class TestAction {

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

        Goddess g = new Goddess();
        g.setUser_name("小白");
        g.setSex(1);
        g.setAge(18);
        g.setBirthday(new Date());
        g.setEmail("xiaoqing@imooc.com");
        g.setMobile("18500468829");
        g.setIsdel(0);
        g.setId(6);
//      action.add(g);
        action.edit(g);
        /*
         * 查询
         */
        List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();
        Map<String, Object> map =  new HashMap<String,Object>();

        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());
        }
    }
}

6、JDBC实战——搭建视图层

新建包com.imooc.view,新建类View.java

package com.imooc.view;

import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;

import com.imooc.action.GoddessAction;
import com.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";// 返回主菜单

    private static Scanner scan;

    public static void main(String[] args) {

        System.out.println(CONTEXT);

        // 怎么保持程序一直运行

        scan = new Scanner(System.in);

        Goddess goddess = new Goddess();
        GoddessAction action = new GoddessAction();
        String prenious = null;
        Integer step = 1;
        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_MAIN.equals(in.toUpperCase())
                    || OPERATION_MAIN.substring(0, 1).equals(in.toUpperCase())) {
                System.out.println(CONTEXT);
                prenious = null;
                step = 1;
            } else if (OPERATION_GET.equals(in.toUpperCase())
                    || OPERATION_GET.substring(0, 1).equals(in.toUpperCase())) {
                prenious = OPERATION_GET;

                System.out.println("请输入女神【Id】:");
                Goddess go;
                try {
                    go = action.get(Integer.valueOf(scan.next().toString()));
                    System.out.println(go.getId() + ",姓名：" + go.getUser_name());
                    System.out.println(CONTEXT);
                    prenious = null;
                    step = 1;
                } catch (NumberFormatException | SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }

                if (OPERATION_GET.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) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.println("您输入的格式有误，请重新输入");
                        step = 3;
                    }
                } else if (5 == step) {
                    goddess.setEmail(in);
                    System.out.println("请输入女神【性别】:");
                } else if (6 == step) {
                    goddess.setSex(Integer.valueOf(in));
                    System.out.println("请输入女神【是否删除】:");
                } else if (7 == step) {
                    goddess.setIsdel(Integer.valueOf(in));
                    System.out.println("请输入女神【手机号】:");
                } else if (8 == step) {
                    goddess.setMobile(in);
                    try {
                        action.add(goddess);
                        System.out.println("新增女神成功！");
                        step = 1;
                    } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.println("新增女神失败！");
                        step = 1;
                    }
                }
                if (OPERATION_ADD.equals(prenious)) {
                    step++;
                }

            } else {
                System.out.println(prenious);
                System.out.println("您输入的值为：" + in.toString());
            }
        }
    }
}