数据库与JDBC实现增删改查
一、功能说明:
一、向customers表中插入数据,效果如下:
请输入编号:55
请输入姓名:上官红
请输入邮箱:shangguan@126.com
请输入生日(要求按xxxx-xx-xx格式):1988-11-11
插入成功!
二、修改指定客户
请输入待修改的客户编号:3
请输入新的客户姓名:林小玲
修改成功!
三、查询所有客户信息
三、根据编号,查询客户的详细信息,效果如下:
请输入编号:1
---------------------------------------------------------------------------------
编号 姓名 邮箱 生日
1 汪峰 wf@126.com 2010-2-2
四、根据姓名,查询客户的详细信息,效果如下:
请输入姓名:王菲
---------------------------------------------------------------------------------
查无此人
二、实现技术
JDBC、DAO(数据访问对象层)、德鲁伊(Druid)
三、分析
四、数据库准备
1、数据库——girls
2、表
customers表:
CREATE TABLE `customers` (
`id` INT(10) NOT NULL DEFAULT '0',
`name` VARCHAR(15) CHARACTER SET gb2312 DEFAULT NULL,
`email` VARCHAR(20) CHARACTER SET gb2312 DEFAULT NULL,
`birth` DATE DEFAULT NULL,
`photo` MEDIUMBLOB
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `customers`(`id`,`name`,`email`,`birth`,`photo`)
VALUES (2,'李易峰','lyf@126.com','2010-02-02',NULL)
admin表:
boys表:
五、实现代码
1、添加jar包
druid-1.1.10.jar
2、druid.properties
#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/girls?rewriteBatchedStatements=true
#url=jdbc:mysql://localhost:3306/mysqldb
username=root
password=root
initialSize=10
minIdle=5
maxActive=20
maxWait=5000
3、建立bean类
实体类与数据库中的数据的字段保持一致
1)Admin类
package com.kuang.studentManage.bean;
public class Admin {
private Integer id;
private String username;
private String password;
public Admin() {
}
public Admin(Integer id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
@Override
public String toString() {
return "Admin{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
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 String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
2)Boys类
package com.kuang.studentManage.bean;
public class Boys {
private int id;
private String boyName;
private String userCP;
public Boys() {
}
public Boys(int id, String boyName, String userCP) {
this.id = id;
this.boyName = boyName;
this.userCP = userCP;
}
@Override
public String toString() {
return "Boys{" +
"id=" + id +
", boyName='" + boyName + '\'' +
", userCP='" + userCP + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBoyName() {
return boyName;
}
public void setBoyName(String boyName) {
this.boyName = boyName;
}
public String getUserCP() {
return userCP;
}
public void setUserCP(String userCP) {
this.userCP = userCP;
}
}
3)Customers类
package com.kuang.studentManage.bean;
import java.util.Date;
public class Customers {
private int id;
private String name;
private String email;
private Date birth;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public Customers(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public Customers() {
super();
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Customers [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
}
}
4、Utils包
使用德鲁伊的数据库连接池
package com.kuang.studentManage.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* 通过德鲁伊数据库连接池获取连接
*
*/
public class JDBCUtilsByDruid {
static DataSource ds = null;
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("src\\druid.properties"));
//1、创建一个指定参数的数据库连接池
ds = DruidDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws Exception {
//2、从数据库连接池中获取可用的连接对象
return ds.getConnection();
}
/**
* 释放资源
*/
public static void close(ResultSet set, Statement statement, Connection connection){
try {
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
if(set!=null) {
set.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5、DAO层
1)基础的DAO层——BasicDAO
抽取了公共的增删改查方法
- update方法:通用的增删改方法:针对于任何表
- querySingle方法:返回单条记录
- queryMulti方法:返回多条记录
- queryScalar方法:返回单个值
package com.kuang.studentManage.dao;
import com.kuang.studentManage.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.util.List;
public class BasicDao<T> {
Connection connection = null;
QueryRunner qr = new QueryRunner();
/**
* 通用的增删改方法:针对于任何表
*/
public int update(String sql,Object...params){
try{
connection = JDBCUtilsByDruid.getConnection();
int update = qr.update(connection, sql, params);
return update;
}catch (Exception e){
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
/**
* 返回单条记录
* @param sql
* @param clazz 传过来对象类型
* @param params
* @param <T>
* @return
*/
public <T> T querySingle(String sql,Class<T> clazz,Object...params){
try{
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection,sql,new BeanHandler<T>(clazz),params);
}catch (Exception e){
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
/**
* 返回多条记录
* @param sql
* @param clazz 传过来对象类型
* @param params
* @param <T>
* @return
*/
public <T> List<T> queryMulti(String sql, Class<T> clazz, Object...params){
try{
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection,sql,new BeanListHandler<T>(clazz),params);
}catch (Exception e){
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
/**
* 返回单个值
* @param sql
* @param params
* @return
*/
public Object queryScalar(String sql,Object...params){
try{
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection,sql,new ScalarHandler(),params);
}catch (Exception e){
throw new RuntimeException(e);
}finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
}
2)针对具体的表建立各自的DAO
继承BasicDAO,它们就有了父类BasicDao中的方法,也可以扩展子类特有的方法
AdminDao
package com.kuang.studentManage.dao;
import com.kuang.studentManage.bean.Admin;
public class AdminDao extends BasicDao<Admin> {
}
BoysDao
package com.kuang.studentManage.dao;
import com.kuang.studentManage.bean.Boys;
public class BoysDao extends BasicDao<Boys> {
}
CustomersDao
package com.kuang.studentManage.dao;
import com.kuang.studentManage.bean.Customers;
public class CustomersDao extends BasicDao<Customers> {
}
6、Service层
AdminService.java
service层调用DAO层
- login方法:用于登录验证
- queryCustomers方法: 从customers表中查询所有客户(通过调用CustomerDAO)
- insertCustomer方法:向customers表中插入用户信息
- updateCustomer方法:修改指定的用户(根据id修改名字)
- queryById方法:根据编号查询客户信息
- queryByName方法:根据姓名查询客户信息
package com.kuang.studentManage.service;
import com.kuang.studentManage.bean.Customers;
import com.kuang.studentManage.dao.AdminDao;
import com.kuang.studentManage.dao.CustomersDao;
import java.util.List;
//service层调dao层
public class AdminService {
AdminDao adminDao = new AdminDao();
CustomersDao customersDao = new CustomersDao();
/**
* 登录验证
* 查询admin表中的用户信息
* @param username view层传过来的
* @param password
*/
public boolean login(String username,String password){
Object count = adminDao.queryScalar("select count(*) from admin where username =? and password=?", username, password);
return Integer.parseInt(count+"")>0;
}
/**
* 从customers表中查询所有客户
*/
public void queryCustomers(){
List<Customers> list = customersDao.queryMulti("select * from customers", Customers.class);
for (Customers customers : list) {
System.out.println(customers);
}
}
/**
* 功能:向customers表中插入用户信息
* @param customers
*/
public void insertCustomer(Customers customers){
customersDao.update("insert into customers values(?,?,?,?,null)",customers.getId(),customers.getName(),customers.getEmail(),customers.getBirth());
System.out.println("插入成功");
}
/**
* 功能:修改指定的用户
* 根据id修改名字
*/
public void updateCustomer(String name,Integer id){
customersDao.update("update customers set name = ? where id = ?",name,id);
System.out.println("修改成功");
}
/**
* 功能:根据编号,查询客户的详细信息
*/
public void queryById(Integer id){
Customers customers = customersDao.querySingle("select * from customers where id = ?", Customers.class, id);
if(customers != null){
System.out.println(customers);
System.out.println("根据id查询成功!");
}else{
System.out.println("查无此人!");
}
}
/**
* 根据姓名,查询客户的详细信息
*/
public void queryByName(String name){
Customers customers = customersDao.querySingle("select * from customers where name = ?", Customers.class, name);
if(customers != null){
System.out.println(customers);
System.out.println("根据name查询成功!");
}else{
System.out.println("查无此人!");
}
}
}
7、View层(视图层)
功能:
- 显示可操作菜单,根据选项,执行对应的操作
- 然后调用service执行
StudentView.java
package com.kuang.studentManage.view;
import com.kuang.studentManage.bean.Customers;
import com.kuang.studentManage.service.AdminService;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
public class StudentView {
AdminService service = new AdminService();
public static void main(String[] args) throws ParseException {
//new StudentView().login();
//new StudentView().queryCustomers();
new StudentView().showMainMenu();
}
public void login() throws ParseException {
Scanner input = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = input.next();
System.out.println("请输入密码:");
String password = input.next();
if(service.login(username,password)){
System.out.println("登录成功");
showMainMenu();
}else{
System.out.println("登录失败");
}
}
//查询所有客户
public void queryCustomers(){
service.queryCustomers();
}
//插入客户信息
public void insertCustomer() throws ParseException {
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户id:");
int id = scanner.nextInt();
System.out.println("请输入用户name:");
String name = scanner.next();
System.out.println("请输入用户email:");
String email = scanner.next();
System.out.println("请输入用户birth:");
String birth = scanner.next();
SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
Date date = ft.parse(birth);
Customers customers = new Customers(id,name,email,date);
service.insertCustomer(customers);
}
/**
* 功能:修改指定的用户
* 根据id修改名字
*/
public void updateCustomer(){
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要修改的用户id:");
int id = scanner.nextInt();
System.out.println("请输入更改后的用户name:");
String name = scanner.next();
service.updateCustomer(name,id);
}
/**
* 根据id进行查询
*/
public void queryById(){
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要查询的用户id:");
int id = scanner.nextInt();
service.queryById(id);
}
/**
* 根据姓名进行查询
*/
public void queryByName(){
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要查询的用户姓名:");
String name = scanner.next();
service.queryByName(name);
}
/**
* 显示主菜单
*/
private void showMainMenu() throws ParseException {
System.out.println("主菜单:");
System.out.println("0 :登录验证");
System.out.println("1 :查询所有客户");
System.out.println("2 :插入用户");
System.out.println("3 :修改用户");
System.out.println("4 :根据id查询客户");
System.out.println("5 :根据姓名查询客户");
System.out.println("6 :退出操作");
Scanner scanner = new Scanner(System.in);
System.out.println("请输入操作序号:");
int num = scanner.nextInt();
switch (num){
case 0:
login();
case 1:
queryCustomers();
showMainMenu();
break;
case 2:
insertCustomer();
showMainMenu();
break;
case 3:
updateCustomer();
showMainMenu();
break;
case 4:
queryById();
showMainMenu();
break;
case 5:
queryByName();
showMainMenu();
break;
case 6:
System.out.println("退出操作");
break;
}
}
}
7、效果:
演示步骤:
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
0
请输入用户名:
John1
请输入密码:
9999
九月 21, 2020 4:01:21 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
登录成功
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
1
Customers [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
Customers [id=2, name=李易峰, email=lyf@126.com, birth=2010-02-02]
Customers [id=3, name=Jenny, email=lisa@163.com, birth=1998-11-11]
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
2
请输入用户id:
4
请输入用户name:
lala
请输入用户email:
lala@163.com
请输入用户birth:
1998-9-9
插入成功
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
3
请输入要修改的用户id:
4
请输入更改后的用户name:
Mike
修改成功
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
4
请输入要查询的用户id:
3
Customers [id=3, name=Jenny, email=lisa@163.com, birth=1998-11-11]
根据id查询成功!
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
5
请输入要查询的用户姓名:
Mike
Customers [id=4, name=Mike, email=lala@163.com, birth=1998-09-09]
根据name查询成功!
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
6
退出操作
Customers [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
Customers [id=2, name=李易峰, email=lyf@126.com, birth=2010-02-02]
Customers [id=3, name=Jenny, email=lisa@163.com, birth=1998-11-11]
Customers [id=4, name=Mike, email=lala@163.com, birth=1998-09-09]
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
6
退出操作
2.插入:
3.修改:
4.根据id查询客户
5.根据姓名查询
6.退出操作
户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
5
请输入要查询的用户姓名:
Mike
Customers [id=4, name=Mike, email=lala@163.com, birth=1998-09-09]
根据name查询成功!
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
6
退出操作
Customers [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
Customers [id=2, name=李易峰, email=lyf@126.com, birth=2010-02-02]
Customers [id=3, name=Jenny, email=lisa@163.com, birth=1998-11-11]
Customers [id=4, name=Mike, email=lala@163.com, birth=1998-09-09]
主菜单:
0 :登录验证
1 :查询所有客户
2 :插入用户
3 :修改用户
4 :根据id查询客户
5 :根据姓名查询客户
6 :退出操作
请输入操作序号:
6
退出操作
2.插入:
[外链图片转存中...(img-nBQwFglA-1600675792311)]
3.修改:
[外链图片转存中...(img-RuDIOjlx-1600675792312)]
4.根据id查询客户
[外链图片转存中...(img-yBpxm1Or-1600675792314)]
5.根据姓名查询
[外链图片转存中...(img-QVcfEtVN-1600675792317)]
6.退出操作