JDBC连接MySQL数据库——案例

时间:2021-04-21 13:12:29

采用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”

JDBC连接MySQL数据库——案例

建立数据库连接类

//连接数据库
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”全部保存。