JDBC技术实现增删改查
数据库文件
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 8.0.15 : Database - study
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
USE `study`;
/*Table structure for table `customers` */
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(15) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`birth` date DEFAULT NULL,
`photo` mediumblob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*Data for the table `customers` */
insert into `customers`(`id`,`name`,`email`,`birth`,`photo`) values (1,'汪峰','wf@126.com','2010-02-02',NULL),(2,'王菲','wangf@163.com','1988-12-26',NULL),(3,'林志玲','linzl@gmail.com','1984-06-12',NULL),(4,'汤唯','tangw@sina.com','1986-06-13',NULL),(5,'成龙','Jackey@gmai.com','1955-07-14',NULL),(6,'迪丽热巴','reba@163.com','1983-05-17',NULL),(7,'刘亦菲','liuyifei@qq.com','1991-11-14',NULL),(8,'陈道明','bdf@126.com','2014-01-17',NULL),(10,'周杰伦','zhoujl@sina.com','1979-11-15',NULL),(12,'黎明','LiM@126.com','1998-09-08',NULL),(13,'张学友','zhangxy@126.com','1998-12-21',NULL),(16,'朱茵','zhuyin@126.com','2014-01-16',NULL),(18,'贝多芬','beidf@126.com','2014-01-17',NULL);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
Customer类
package bean;
import java.sql.Date;
public class Customer {
private Integer id;
private String name;
private String email;
private Date birth;
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 setDate(Date birth) {
this.birth = birth;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getId() {
return id;
}
@Override
public String toString() {
return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
}
public Customer() {
}
public Customer(Integer id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
}
CustomersDAO接口
package DAO;
import java.util.List;
import bean.Customer;
public interface CustomersDAO {
public int insert(Customer cust);
public int delete(Customer cust);
public int alter(Customer cust);
public List<Customer> query(int start);
}
JDBCUtils类
package Utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @ClassName: JDBCUtils
* @Description:
* @author LYL
* @date 2021-02-15 12:39:17
*/
public class JDBCUtils {
//获取数据库连接
public static Connection getConnection() {
Connection conn = null;
try {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
Class.forName(driverClass);
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
// 关闭连接资源和statement操作
public static void closeResource(Connection conn, Statement ps) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeResource(Connection conn, Statement ps, ResultSet rs) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void closeResource(Statement ps) {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
CustomerDAOImpl实现类
package DAO;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import Utils.JDBCUtils;
import bean.Customer;
public class CustomerDAOImpl implements CustomersDAO {
Connection conn = JDBCUtils.getConnection();
@Override
public int insert(Customer cust) {
String sql = "insert into customers(name,email,birth) values(?,?,?)";
String name = cust.getName();
String email = cust.getEmail();
Date birth = cust.getBirth();
int index = update(sql, name, email, birth);
if (index > 0) {
System.out.println("插入成功~");
} else {
System.out.println("插入失败~");
}
return index;
}
@Override
public int delete(Customer cust) {
String sql = "delete from customers where id = ?";
int id = cust.getId();
int index = update(sql, id);
if (index > 0) {
System.out.println("删除成功~");
} else {
System.out.println("删除失败~");
}
return index;
}
@Override
public int alter(Customer cust) {
String sql = "update customers set name=?,email=?,birth=? where id=?";
int id = cust.getId();
String name = cust.getName();
String email = cust.getEmail();
Date birth = cust.getBirth();
int index = update(sql, name, email, birth, id);
if (index > 0) {
System.out.println("修改成功~");
} else {
System.out.println("修改失败~");
}
return index;
}
@Override
public List<Customer> query(int start) {
List<Customer> list = new ArrayList();
String sql = "select * from customers limit ?,5";
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, start);
rs = ps.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
Customer cust = new Customer(id, name, email, birth);
list.add(cust);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs);
}
return list;
}
/**
*
* @Description: 通用的增删改操作
* @author LYL
* @date 2021-03-15 17:45:06
*/
private int update(String sql, Object... args) {
PreparedStatement ps = null;
int index = 0;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
index = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(ps);
}
return index;
}
}
测试类
package test;
import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Scanner;
import DAO.CustomerDAOImpl;
import DAO.CustomersDAO;
import bean.Customer;
public class Test {
/**
* 1、通过分层方式实现商品的添加、修改、删除、查询 2、扩展作业： 通过商品表实现分页查询。 mysql -- > limit关键字
*/
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
CustomersDAO cd = new CustomerDAOImpl();
for (;;) {
System.out.println("1.添加、2.修改、3.删除、4.查询、5.退出");
int index = sc.nextInt();
if (1 == index) {
int id = 1;
System.out.print("输入入用户姓名：");
String name = sc.next();
System.out.print("输入入用户邮箱：");
String email = sc.next();
System.out.print("输入入用户生日：");
String date = sc.next();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date parse = null;
try {
long num = sdf.parse(date).getTime();
parse = new Date(num);
} catch (ParseException e) {
e.printStackTrace();
}
Customer cust = new Customer(id, name, email, parse);
cd.insert(cust);
} else if (2 == index) {
System.out.println("请输入需要更改的用户id");
int id = sc.nextInt();
System.out.print("输入入用户姓名：");
String name = sc.next();
System.out.print("输入入用户邮箱：");
String email = sc.next();
System.out.print("输入入用户生日：");
String date = sc.next();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date parse = null;
try {
long num = sdf.parse(date).getTime();
parse = new Date(num);
} catch (ParseException e) {
e.printStackTrace();
}
Customer cust = new Customer(id, name, email, parse);
cd.alter(cust);
} else if (3 == index) {
System.out.println("请输入需要删除的用户id");
int id = sc.nextInt();
Customer cust = new Customer();
cust.setId(id);
cd.delete(cust);
} else if (4 == index) {
System.out.println("请输入需要查询第几页（每页5位用户）");
int start = 0;
int num = sc.nextInt();
if (1 == num) {
} else {
start = num * 5 - 6;
}
List<Customer> list = cd.query(start);
for (Customer cust : list) {
System.out.println(cust);
}
} else if(5 == index){
System.out.println("程序结束~");
System.exit(0);
}
else {
System.out.println("输入错误，请重新输入！");
}
}
}
}
