JDBC初步学习

根据慕课网的课程JDBC之 “ 对岸的女孩看过来”进行学习。

MySQL安装及配置：
配置系统path环境变量：D:\Program Files\mysql-5.7.15-winx64\bin;
在主目录下新建my.ini配置文件，输入如下内容：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
#忽略root用户的密码
skip-grant-tables
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=D:\Program Files\mysql-5.7.15-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\Program Files\mysql-5.7.15-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB 

安装mysql服务
管理员的身份运行cmd.exe，运行mysqld install命令，接着输入 net start mysql 启动服务，如果要停止服务，运行命令net stop mysql。
如果启动服务失败，且主目录下没有data目录，运行mysqld –initialize，然后启动服务。
进入mysql命令为mysql -u root -p，密码为空

首先将JDBC驱动程序mysql-connector-java-5.1.39-bin.jar文件添加到工程中。

MySQL相关的建表语句

create database imooc;

use imooc;

create table imooc_goddess(
    id int primary key auto_increment,
    user_name varchar(30) not null,
    sex int,
    age int,
    birthday date,
    email varchar(30),
    mobile varchar(11),
    create_user varchar(30),
    create_date date,
    update_user varchar(30),
    update_date date,
    isdel int
) default charset=utf8 auto_increment=1;

alter database imooc default character set 'utf8';
set character_set_client='utf8';
set character_set_connection='utf8';
set character_set_results='utf8';

Goddess.java

package com.imooc.model;

import java.util.Date;

public class Goddess {
    private Integer id;
    private String user_name;
    private Integer sex=1;
    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=0;

    //省略了各参数的getter和setter方法

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

}

DBUtil.java

package com.imooc.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.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;
    }

    public static void main(String[] args) throws Exception {
        // 1.加载驱动程序
        Class.forName("com.mysql.jdbc.Driver");
        // 2.获得数据库的连接
        conn = DriverManager.getConnection(URL, NAME, PASSWORD);
        // 3. 通过数据库的连接操作数据库，实现增删改查
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select user_name,age from imooc_goddess");
        while (rs.next()) {
            System.out.println(rs.getString("user_name") + ":" + rs.getInt("age"));
        }
    }
}

GoddessDao.java

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.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.imooc.db.DBUtil;
import com.imooc.model.Goddess;

/**
 * 模型层
 * 
 * */

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=conn.prepareStatement(sql);

        ptmt.setString(1, g.getUser_name());
        ptmt.setInt(2, g.getSex());
        ptmt.setInt(3, g.getAge());
        ptmt.setDate(4, new Date(g.getBirthday().getTime()));
        ptmt.setString(5, g.getEmail());
        ptmt.setString(6, g.getMobile());
        ptmt.setString(7, g.getCreate_user());
        ptmt.setString(8, g.getUpdate_user());
        ptmt.setInt(9, g.getIsdel());

        ptmt.execute();
    }

    public void updateGoddess(Goddess g) throws SQLException{
        Connection conn=DBUtil.getConnection();
        String sql=""+
                " update imooc_goddess "+
                " set user_name=?,sex=?,age=?,birthday=?,email=?,mobile=?, "+
                " update_user=?,update_date=current_date(),isdel=? "+
                " where id=? ";
        PreparedStatement ptmt=conn.prepareStatement(sql);

        ptmt.setString(1, g.getUser_name());
        ptmt.setInt(2, g.getSex());
        ptmt.setInt(3, g.getAge());
        ptmt.setDate(4, new Date(g.getBirthday().getTime()));
        ptmt.setString(5, g.getEmail());
        ptmt.setString(6, g.getMobile());
        ptmt.setString(7, g.getUpdate_user());
        ptmt.setInt(8, g.getIsdel());
        ptmt.setInt(9, g.getId());

        ptmt.execute();
    }
    public void delGoddess(Integer id) throws SQLException{
        Connection conn=DBUtil.getConnection();
        String sql=""+
                " delete from imooc_goddess "+
                " where id=? ";
        PreparedStatement ptmt=conn.prepareStatement(sql);

        ptmt.setInt(1, id);

        ptmt.execute();
    }

    /**
     * 查询所有的基本信息
     * */
    public List<Goddess> query() throws Exception{
        Connection conn=DBUtil.getConnection();
        Statement stmt=conn.createStatement();
        ResultSet rs=stmt.executeQuery("select id,user_name,age from imooc_goddess");

        List<Goddess> gs=new ArrayList<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"));

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

    public List<Goddess> query(String name) 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=?");


        PreparedStatement ptmt=conn.prepareStatement(sb.toString());
        ptmt.setString(1, name);

        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_user(rs.getString("create_user"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setUpdate_user(rs.getString("update_user"));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setIsdel(rs.getInt("isdel"));

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


    /**
     * 参数化查询方式
     * 每个参数包含三部分
     * name为参数名称
     * rela为=或like等
     * value为参数值
     * */
    public List<Goddess> query(List<Map<String, Object>> params) throws Exception{
        List<Goddess> result=new ArrayList<Goddess>();

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

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

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

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

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

    public Goddess get(Integer id) throws SQLException{
        Goddess g=null;
        Connection conn=DBUtil.getConnection();
        String sql=""+
                " select * from imooc_goddess "+
                " where id=? ";
        PreparedStatement ptmt=conn.prepareStatement(sql);

        ptmt.setInt(1, id);

        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_user(rs.getString("create_user"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setUpdate_user(rs.getString("update_user"));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setIsdel(rs.getInt("isdel"));
        }
        return g;
    }
}

GoddessAction.java

package com.imooc.action;

import java.sql.SQLException;
import java.util.ArrayList;
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 void edit(Goddess goddess) throws SQLException{
        GoddessDao dao=new GoddessDao();
        dao.updateGoddess(goddess);
    }

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

    public Goddess get(Integer id) throws SQLException{
        GoddessDao dao=new GoddessDao();
        return dao.get(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);
    }

    public static void main(String[] args) throws Exception {
        GoddessDao g=new GoddessDao();
        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);

        List<Goddess> result=g.query(params);
        for (Goddess goddess : result) {
            System.out.println(goddess);
        }

//      Goddess g1=new Goddess();
//      g1.setUser_name("小夏");
//      g1.setAge(23);
//      g1.setSex(1);
//      g1.setBirthday(new Date());
//      g1.setMobile("18756780000");
//      g1.setEmail("xiaoxia@imooc.com");
//      g1.setUpdate_user("ADMIN");
//      g1.setIsdel(1);
//      g1.setId(4);

//      g.delGoddess(3);
//      g.updateGoddess(g1);
//      g.addGoddess(g1);

//      List<Goddess> gs=g.query();
//      for(Goddess goddess:gs){
//          System.out.println(goddess);
//      }
//      
//      Goddess sGoddess=g.get(1);
//      System.out.println(sGoddess);
    }
}

View.java

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

    public static void main(String[] args) {
        System.out.println(CONTEXT);
        Scanner scan = new Scanner(System.in);

        Goddess goddess=new Goddess();
        GoddessAction action=new GoddessAction();
        String previous=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 godd:list) {
                        System.out.println(godd.getId()+","+godd.getUser_name());
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            } else if (OPERATION_ADD.equals(in.toUpperCase())
                    || OPERATION_ADD.substring(0, 1).equals(in.toUpperCase())
                    || OPERATION_ADD.equals(previous)) {
                previous=OPERATION_ADD;

                if(step==1){
                    System.out.println("请输入女神的[姓名]");
                } else if (step==2){
                    goddess.setUser_name(in);
                    System.out.println("请输入女神的[年龄]");
                } else if (step==3){
                    goddess.setAge(Integer.valueOf(in));
                    System.out.println("请输入女神的[生日]，格式如：yyyy-MM-dd");
                } else if (step==4){
                    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 (step==5){
                    goddess.setEmail(in);
                    System.out.println("请输入女神的[手机号]");
                } else if (step==6){
                    goddess.setMobile(in);
                    try {
                        action.add(goddess);
                        System.out.println("新增女神成功");
                    } catch (Exception e) {
                        e.printStackTrace();
                        System.out.println("新增女神失败");
                    }
                } if(OPERATION_ADD.equals(previous)){
                    step++;
                }
            } else {
                System.out.println("您输入的值为：" + in);
            }
        }
    }
}