JDBC连接MySQL数据库——案例

采用MyEcplise+Navicat工具，通过JDBC连接MyEcplise和数据库，实现在MyEcplise中对数据库进行增删改查，要有点JAVA基础和MySQL基础。

1、在数据库中创建表

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

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

2、在MyEcplise中新建一个java工程，工程目录下新建一个文件夹lib，在lib中拷入准备好的mysql驱动的jar包，然后在jar包右键选择“Build Path”

建立数据库连接类

//连接数据库
package black.jdbc.connect;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnect {

// 设置3个变量存放数据库地址、用户名和密码
// "jdbc驱动:连接的数据库类型://IP地址:端口/数据库名称
private static final String URL = "jdbc:mysql://127.0.0.1:3306/goddess";
private static final String USERNAME = "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, USERNAME, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

public static Connection getConnection(){
return conn;
    }

}

3、采用MVC三层模型架构方式，开始写我们的程序

模型层Model：用于数据处理、业务逻辑，分2个部分：对应数据库的映射、对数据库映射的抽象方法（增删改查）；
控制层Controller：控制数据流通过程，连接模型层和视图层；
视图层 View：数据展示作用，给用户展示程序运行的结果；

3.1 建立数据库表的实体类

//建立实体类，数据库中的表的映射
package black.jdbc.model;

import java.util.Date;

public class Goddess {
private Integer id;
private String username;
private Integer sex;
private Integer age;
private Date birthday;
private String email;
private String mobile;
private String create_user;
private Date create_date;
private String update_user;
private Date update_date;
private Integer isdel;

public Integer getId() {
return id;
    }

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

public String getUsername() {
return username;
    }

public void setUsername(String username) {
this.username = username;
    }

public Integer getSex() {
return sex;
    }

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

public Integer getAge() {
return age;
    }

public void setAge(Integer age) {
this.age = age;
    }

public Date getBirthday() {
return birthday;
    }

public void setBirthday(Date birthday) {
this.birthday = birthday;
    }

public String getEmail() {
return email;
    }

public void setEmail(String email) {
this.email = email;
    }

public String getMobile() {
return mobile;
    }

public void setMobile(String mobile) {
this.mobile = mobile;
    }

public String getCreate_user() {
return create_user;
    }

public void setCreate_user(String create_user) {
this.create_user = create_user;
    }

public Date getCreate_date() {
return create_date;
    }

public void setCreate_date(Date create_date) {
this.create_date = create_date;
    }

public String getUpdate_user() {
return update_user;
    }

public void setUpdate_user(String update_user) {
this.update_user = update_user;
    }

public Date getUpdate_date() {
return update_date;
    }

public void setUpdate_date(Date update_date) {
this.update_date = update_date;
    }

public Integer getIsdel() {
return isdel;
    }

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

@Override
public String toString() {
return "Goddess [id=" + id + ", username=" + username + ", 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 + "]";
    }

}

3.2 对数据库进行增删改查的方法类，供控制层调用

//操作女神数据库的一些方法，如增删改查
package black.jdbc.operate;

import java.sql.Connection;
import java.sql.Date;
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.mysql.jdbc.PreparedStatement;

import black.jdbc.connect.DBConnect;
import black.jdbc.model.Goddess;

public class GoddessOperate {

    //添加女神
    public void addGoddess(Goddess g) throws SQLException{
        //1、取得数据库连接
        Connection conn=DBConnect.getConnection();
        //2、写sql语句      “?”相当于占位符表示参数的值
        String addSql=""+
"insert into goddessmessage"+ 
"(username,sex,age,birthday,email,mobile,create_user,"+
"create_date,update_user,update_date,isdel)"+
"values("+
"?,1,?,?,?,?,'ADMIN',current_date(),'ADMIN',current_date(),1)"; 
/**
 * 3、预编译sql语句
 * 这个方法会将sql语句加载到驱动程序的执行程序中，但并不直接执行，
 * 而是当它调用execute方法是才真正执行
 */
        PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(addSql);

        //4、传参，给预编译值赋值
        ptmt.setString(1,g.getUsername());
        ptmt.setInt(2, g.getAge());
        //将java.util类型的date转化为java.sql类型的date
        ptmt.setDate(3,new Date(g.getBirthday().getTime()));
        ptmt.setString(4,g.getEmail());
        ptmt.setString(5,g.getMobile());

        //5、执行sql语句
        ptmt.execute();

    }

    //删除女神
    public void delGoddess(Integer id) throws SQLException{
        Connection conn=DBConnect.getConnection();
        String delSql=  " "
                        +" delete from goddessmessage "
                        +" where id=? ";
        PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(delSql);
        ptmt.setInt(1, id);
        ptmt.execute();

    }

    //更新女神
    public void updateGoddess(Goddess g) throws SQLException{
        Connection conn=DBConnect.getConnection();
        String updateSql= " "+
" update goddessmessage "+
" set username=?,sex=?,age=?,birthday=?,email=?,mobile=?, "+
" update_user=?,update_date=current_date(),isdel=? "+
" where id=? ";
        PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(updateSql);
        ptmt.setString(1,g.getUsername());
        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 Goddess queryGoddess(Integer id) throws SQLException{
        Connection conn=DBConnect.getConnection();
        String querySql=" select * from goddessmessage "
                        + " where id=?";
        PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(querySql);
        ptmt.setInt(1, id);
        //这里是查询，不用execute方法，execute是用于数据库的增删改
        //ResultSet返回一个结果集
        ResultSet rs=ptmt.executeQuery();
        Goddess g=null;
        while(rs.next()){
            g=new Goddess();
            g.setId(rs.getInt("id"));
            g.setUsername(rs.getString("username"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
/**
 * 写入数据库，如果是java.util类型的date，需要转化成java.sql类型
 * 但通过数据库查询出来的java.sql类型就不需要转了
 * 因为java.sql.Date类型是java.util.Date类型的一个子集
 */
            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;
    }

    //查询所有女神
    public List<Goddess> query() throws Exception{

        Connection conn=DBConnect.getConnection();
        Statement stmt=conn.createStatement();
        ResultSet rs=stmt.executeQuery("select id,username,age from goddessmessage");
        List<Goddess> goddessList=new ArrayList<Goddess>();
        Goddess g=null;

        while(rs.next()){
            g=new Goddess();
            g.setId(rs.getInt("id"));
            g.setUsername(rs.getString("username"));
            g.setAge(rs.getInt("age"));
            goddessList.add(g);
        }

        return goddessList;
    }

    //参数查询：当传入的参数很多的时候，采用集合的形式传入
    public List<Goddess> query(List<Map<String,Object>> params) throws Exception{

        Connection conn=DBConnect.getConnection();
        StringBuilder sb=new StringBuilder();
        sb.append("select * from goddessmessage 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("key")+map.get("rela")+map.get("value"));
                }
            }

//      //查询条件为或
//      if(params!=null&&params.size()>0){
//          Map<String,Object> map=params.get(0);
//          sb.append(" and "+map.get("key")+map.get("rela")+map.get("value"));
//          if(params.size()>1){
//              for(int i=1;i<params.size();i++){
//                  map=params.get(i);
//                  sb.append(" or "+map.get("key")+map.get("rela")+
//                          map.get("value"));
//              }
//          }
//      }

        PreparedStatement ptmt=(PreparedStatement) 
                conn.prepareStatement(sb.toString());

        ResultSet rs=ptmt.executeQuery();
        System.out.println(sb);
        List<Goddess> goddessList=new ArrayList<Goddess>();
        Goddess g=null;

        while(rs.next()){
            g=new Goddess();

            g.setId(rs.getInt("id"));
            g.setUsername(rs.getString("username"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            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"));

            goddessList.add(g);
        }

        return goddessList;
    }


/* //按传入的参数查询女神
 public List<Goddess> query(String username,String mobile) throws Exception{

 Connection conn=DBConnect.getConnection();
 StringBuilder sb=new StringBuilder();
 sb.append(" select * from goddessmessage ");
 sb.append(" where username like ? and mobile like ? ");
 System.out.println(sb);
 PreparedStatement ptmt=(PreparedStatement) 
 conn.prepareStatement(sb.toString());
 ptmt.setString(1, "%"+username+"%");
 ptmt.setString(2, "%"+mobile+"%");
 ResultSet rs=ptmt.executeQuery();
 List<Goddess> goddessList=new ArrayList<Goddess>();
 Goddess g=null;

 while(rs.next()){
 g=new Goddess();

 g.setId(rs.getInt("id"));
 g.setUsername(rs.getString("username"));
 g.setSex(rs.getInt("sex"));
 g.setAge(rs.getInt("age"));
 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"));

 goddessList.add(g);
 }

 return goddessList;
 }*/

}

3.3 控制层

//控制层
package black.jdbc.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 javax.swing.text.StyledEditorKit.ForegroundAction;

import black.jdbc.model.Goddess;
import black.jdbc.operate.GoddessOperate;

public class GoddessAction {

//增加女神
public void add(Goddess goddess) throws SQLException{
        GoddessOperate go=new GoddessOperate();
        go.addGoddess(goddess);
    }

//删除女神
public void del(Integer id) throws SQLException{
        GoddessOperate go=new GoddessOperate();
        go.delGoddess(id);
    }

//更新女神
public void update(Goddess goddess) throws SQLException{
        GoddessOperate go=new GoddessOperate();
        go.updateGoddess(goddess);
    }

//查询单个女神
public Goddess get(Integer id) throws SQLException{
        GoddessOperate go=new GoddessOperate();
return go.queryGoddess(id);
    }

//查询所有女神
public List<Goddess> query() throws Exception{
        GoddessOperate go=new GoddessOperate();
return go.query();
    }

//参数查询
public List<Goddess> search(List<Map<String,Object>> params) throws Exception{
        GoddessOperate go=new GoddessOperate();
return go.query(params);
    }
}

3.4 视图层

package black.jdbc.view;

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

import black.jdbc.action.GoddessAction;
import black.jdbc.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";


//main方法启动控制台
public static void main(String[] args) {

        System.out.println(CONTEXT);

        Scanner scan=new Scanner(System.in);

        Goddess goddess=new Goddess();

        GoddessAction ga=new GoddessAction();

//标记
        String previous=null;
int 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_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.setUsername(in);
                    System.out.println("请输入女神年龄：");

                }else if(step==3){
                    goddess.setAge(new Integer(in));
                    System.out.println("请输入女神出生日期（格式如 yyyy-MM-dd）：");
                }else if(step==4){
                    SimpleDateFormat sfd=new SimpleDateFormat("yyyy-MM-dd");
                    Date birthday=null;
try {
                        birthday=sfd.parse(in);
                        goddess.setBirthday(birthday);
                    } catch (ParseException e) {
                        System.out.println("输入的日期格式有误，请重新输入：");
                        step=4;
                    }
                    System.out.println("请输入女神邮箱：");
                }else if(step==5){
                    goddess.setEmail(in);
                    System.out.println("请输入女神手机号码：");
                }else if(step==6){
                    goddess.setMobile(in);
try {
                        ga.add(goddess);
                        System.out.println("新增女神成功！");
                    } catch (SQLException e) {
                        System.out.println("新增女神失败！");
                    }
                }
if(OPERATION_ADD.equals(previous)){
                    step++;
                }
//删除女神功能
            }else if(OPERATION_DELETE.equals(in.toUpperCase())||
                    OPERATION_DELETE.substring(0, 1).equals(in.toUpperCase())||
                    OPERATION_DELETE.equals(previous)){

                previous=OPERATION_DELETE;

if(step==1){
                    System.out.println("请输入要删除的女神的ID：");
                }else if(step==2){
try {
                        ga.del(new Integer(in));
                        System.out.println("成功删除ID为"+in+"的女神！");
                    } catch (Exception e) {
                        System.out.println("删除女神失败！");
                    } 
                }

if(previous.equals(OPERATION_DELETE)){
                    step++;
                }
//更新女神功能
            }else if(OPERATION_UPDATE.equals(in.toUpperCase())||
                    OPERATION_UPDATE.substring(0, 1).equals(in.toUpperCase())||
                    OPERATION_UPDATE.equals(previous)){
                previous=OPERATION_UPDATE;
if(step==1){
                    System.out.println("请输入要更新的女神ID：");
                }else if(step==2){
                    goddess.setId(new Integer(in));
                    System.out.println("请输入女神姓名：");
                }else if(step==3){
                    goddess.setUsername(in);
                    System.out.println("请输入女神年龄：");
                }else if(step==4){
                    goddess.setAge(new Integer(in));
                    System.out.println("请输入女神出生日期（格式如：yyyy-MM-dd）：");
                }else if(step==5){
                    SimpleDateFormat sfd=new SimpleDateFormat("yyyy-MM-dd");
                    Date birthday=null;
try {
                        birthday=sfd.parse(in);
                        goddess.setBirthday(birthday);
                    } catch (ParseException e) {
                        System.out.println("输入的日期格式有误，请重新输入：");
                        step=5;
                    }
                    System.out.println("请输入女神邮箱：");
                }else if(step==6){
                    goddess.setEmail(in);
                    System.out.println("请输入女神手机号码：");
                }else if(step==7){
                    goddess.setMobile(in);
                    System.out.println("请输入更新者：");
                }else if(step==8){
                    goddess.setUpdate_user(in);
try {
                        ga.update(goddess);
                        System.out.println("成功更新女神！");
                    } catch (SQLException e) {
                        System.out.println("更新女神失败！");
                    }
                }

if(previous.equals(OPERATION_UPDATE)){
                    step++;
                }

//查询所有女神功能
            }else if(OPERATION_QUERY.equals(in.toUpperCase())||
                    OPERATION_QUERY.substring(0, 1).equals(in.toUpperCase())){
                List<Goddess> goddessList;
try {
                    goddessList = ga.query();
for (Goddess goddess2 : goddessList) {
                        System.out.println(goddess2.toString());
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }

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

        }

    }
}

PS：每次修改完，运行程序前要“Ctrl+Shift+s”全部保存。