Demo完整示例:gitHub
伏法朝朝忧闷,强梁夜夜欢歌,损人利己骑马骡,正直公停挨饿,
修桥补路瞎眼,杀人放火儿多,我到西天问我佛,佛说……我也没辙!
1、JDBC简介
2、JDBC项目效果展示
3、JDBC实战——打通数据库
新建Java Project 项目,取名为jdbc_showGrils
新建文件夹取名lib
加载驱动程序,把驱动文件放在lib文件中。在文本最后会有实际联系代码文件下载地址,可以下载来进行复制即可。
添加进环境变量中
新建一个类
https://img-blog.csdn.net/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());
}
}
}
}