JDBC——综合案例【工人信息管理 登录增删改查】
1.如何操作数据库
使用客户端工具访问数据库,需要手工建立连接,输入用户名和密码登录,编写 SQL 语句,点击执行,查看操作结果(结果集或受影响行数)。
在实际开发中,当用户的数据发生改变时,不可能通过客户端操作执行 SQL 语句,因为操作量过大,无法保证效率和正确性。
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/2863b053920ffc808563da5a5ed9227b.png)
2.、JDBC(Java Database Connectivity: Java数据库连接)
什么是 JDBC?
JDBC(Java Database Connectivity) Java 连接数据库的规范(标准),可以使用 Java 语言连接数 据库完成CRUD 操作。
JDBC 核心思想
Java 中定义了访问数据库的接口,可以为多种关系型数据库提供统一的访问方式。由数据库厂商提供驱动实
现类(Driver 数据库驱动)。
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/b911706af27c3c35c39f057f8b072d8e.png)
3.MySQL 数据库驱动
mysql-connector-java-5.1.X 适用于 5.X 版本
mysql-connector-java-8.0.X 适用于 8.X版本
4.环境搭建
在项目下新建 lib 文件夹,用于存放 jar 文件。
将 mysql 驱动mysql-connector-java-5.1.X复制到项目的 lib 文件夹中。
选中 lib 文件夹右键 Add as Libraay,点击 OK。
5.数据库操作
获取数据库链接:Connection
通过 DriverManager.getConnection(url,user,password) 获取数据库连接对象
URL:jdbc:mysql://localhost:3306/database(要链接的数据库)
username:root(用户名)
password:1234(密码)
执行sql语句:Statement
Statement对象用于执行sql语句,有以下3种:
(1)Statement对象用于执行不带参数的简单的SQL语句;
(2)PerparedStatement对象用于执行带或不带参数的预编译SQL语句;
(3)CallableStatement对象用于执行对数据库已存储过程的调用;
Statement的常用方法:
(1)executeQuery()方法:运行查询语句,返回ReaultSet对象。
(2)executeUpdata()方法:运行增,删,改操作,返回更新的行数。
(3)addBatch(String sql) :把多条sql语句放到一个批处理中。
(4)executeBatch():向数据库发送一批sql语句执行。
结果集:ResultSet
执行executeQuery()方法后返回的结果集
ResultSet 以表(table)结构进行临时结果的存储,需要通过 JDBC API 将其中数据进行依次获取。
数据行指针:初始位置在第一行数据前,每调用一次 boolean next()方法ResultSet 的指针向下移动一
行,结果为 true,表示当前行有数据。
rs.getXxx(整数);代表根据列的编号顺序获得,从 1 开始。
rs.getXxx("列名");代表根据列名获得。
常用方法:
getString(String columnName):获得当前行的某一string类型的字段
getFloat(String columnName):获得当前行的某一string类型的字段
getDate(String columnName):获得当前行的某一date类型的字段
getBoolean(String columnName):获得在当前行的某一Boolean类型的字段
getObject(String columnName):获取当前行的某一任意类型的字段
next():移动到下一行
6.封装工具类
在实际JDBC的使用中,存在着大量的重复代码:例如连接数据库、关闭数据库等这些操作!
我们需要把传统的JDBC代码进行重构,抽取出通用的JDBC工具类!以后连接任何数据库、释放资源都
可以使用这个工具类。
封装获取连接、释放资源两个方法。
提供public static Connection getConnection(){}方法。
提供public static void closeAll(Connection conn , Statement sm , ResultSet rs){}方 法。
7.实际代码
首先在src 目录下新建 druid.properties 文件
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/home517
username=root
password=root
initialSize=5
maxActive=10
maxWait=3000
接着写一个连接数据库的通用工具类:DruidJdbcUtils
package com.qf.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class DruidJdbcUtils {
private static ThreadLocal<Connection> t1 = new ThreadLocal<>() ;
private static DataSource ds ;
private DruidJdbcUtils(){}
static{
try {
Properties prop = new Properties() ;
InputStream inputStream = DruidJdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
prop.load(inputStream) ;
ds = DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static DataSource getDataSource(){
return ds;
}
public static Connection getConnection(){
try {
Connection conn = t1.get();
if(conn==null){
conn = ds.getConnection() ;
t1.set(conn);
}
return conn ;
} catch (SQLException e) {
e.printStackTrace();
}
return null ;
}
public static void close(PreparedStatement ps,Connection conn){
close(null,ps,conn);
}
public static void close(ResultSet rs, PreparedStatement ps,Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
t1.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
pojo层,通常就是用于放置这个系统中,与数据库中的表,一一对应起来的Java的实体类
Emp类
package com.qf.pojo;
public class Emp {
private int id;
private String name;
private int age;
private String gender;
private String address;
private double salary;
public Emp() {
}
public Emp(int id, String name, int age, String gender, String address, double salary) {
this.id = id;
this.name = name;
this.age = age;
this.gender = gender;
this.address = address;
this.salary = salary;
}
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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public double getSalary() {
return salary;
}
public void setSalary(double salary) {
this.salary = salary;
}
@Override
public String toString() {
return "员工{" +
"员工编号:" + id +
", 员工姓名:" + name + '\'' +
", 员工年龄:" + age +
", 员工性别:'" + gender + '\'' +
", 员工地址:" + address + '\'' +
", 员工工资:" + salary + '\'' +
'}';
}
}
Exam类
package com.qf.pojo;
public class Exam {
private String username;
private int password;
public Exam() {
}
public Exam(String username, int password) {
this.username = username;
this.password = password;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getPassword() {
return password;
}
public void setPassword(int password) {
this.password = password;
}
@Override
public String toString() {
return "exam{" +
"username='" + username + '\'' +
", password=" + password +
'}';
}
}
DAO(Data Access Object) 是数据访问层
EmployeeDao接口类(针对员工的数据访问接口层)
package com.qf.dao;
import com.qf.pojo.Emp;
import com.qf.pojo.Exam;
import java.sql.SQLException;
import java.util.List;
public interface EmployeeDao {
List<Emp> findAll() throws SQLException;
Emp findEmployeeById(int id) throws SQLException;
void addEmployee(Emp emp) throws SQLException;
void updateEmployee(Emp empl) throws SQLException;
int deleteEmp(int id) throws SQLException;
boolean Login(String username,int password) throws SQLException;
void addExam(Exam exam) throws SQLException;
int sumEmd() throws SQLException;
}
EmployeeDaolmpl类(针对员工的数据访问接口实现层)
package com.qf.dao.impl;
import com.qf.dao.EmployeeDao;
import com.qf.pojo.Emp;
import com.qf.pojo.Exam;
import com.qf.utils.DruidJdbcUtils;
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.*;
import java.util.List;
public class EmployeeDaolmpl implements EmployeeDao {
@Override
public List<Emp> findAll() throws SQLException {
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
String sql = "select * from emp" ;
List<Emp> list = qr.query(sql,
new BeanListHandler<Emp>(Emp.class));
return list;
}
@Override
public Emp findEmployeeById(int id) throws SQLException {
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
String sql = "select * from emp where id = ?";
Emp emp = qr.query(sql, new BeanHandler<Emp>(Emp.class), id);
return emp;
}
@Override
public void addEmployee(Emp emp) throws SQLException {
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
String sql = "insert into emp(NAME,age,gender,address,salary)value (?,?,?,?,?);";
int count = qr.update(sql, emp.getName(),
emp.getAge(),
emp.getGender(),
emp.getAddress(),
emp.getSalary()
);
System.out.println("成功添加"+count+"条工人信息。");
}
@Override
public void updateEmployee(Emp empl) throws SQLException {
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
String sql = "update emp set name = ?,age = ? ,gender =?,address = ?,salary = ?where id = ?";
int count = qr.update(sql, empl.getName(),
empl.getAge(),
empl.getGender(),
empl.getAddress(),
empl.getSalary(),
empl.getId()
);
System.out.println("成功修改"+count+"条的工人信息。");
}
@Override
public int deleteEmp(int id) throws SQLException {
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
String sql = "delete from emp where id = ?";
int count = qr.update(sql, id);
System.out.println("成功删除"+count+"条的工人信息。");
return count;
}
@Override
public boolean Login(String username, int password) throws SQLException {
ResultSet rs = null;
Connection conn = null;
PreparedStatement ps =null;
try {
conn = DruidJdbcUtils.getConnection();
String sql = "select * from exam where username = ? and password = ?";
ps = conn.prepareStatement(sql);
ps.setString(1,username);
ps.setInt(2,password);
rs = ps.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DruidJdbcUtils.close(rs, ps, conn);
}
return false;
}
@Override
public void addExam(Exam exam) throws SQLException {
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
String sql = "insert into exam(username,password)value (?,?);";
int count = qr.update(sql, exam.getUsername(),exam.getPassword()
);
System.out.println("注册成功,成功添加"+count+"条信息。");
}
@Override
public int sumEmd() throws SQLException {
QueryRunner qr = new QueryRunner(DruidJdbcUtils.getDataSource());
String sql = "select count(id) from emp";
Object o = qr.query(sql, new ScalarHandler<>());
String s = String.valueOf(o);
int sum = Integer.parseInt(s);
return sum;
}
}
service层:service层叫服务层,被称为服务,肯定是相比之下比较高层次的一层结构,相当于将几种操作封装起来
EmployeeTest类
package com.qf.test;
import com.qf.dao.impl.EmployeeDaolmpl;
import com.qf.pojo.Emp;
import com.qf.pojo.Exam;
import java.sql.SQLException;
import java.util.List;
import java.util.Scanner;
public class EmployeeTest {
public static void main(String[] args) throws SQLException {
EmployeeTest employeeTest = new EmployeeTest();
EmployeeDaolmpl employeeDaolmpl = new EmployeeDaolmpl();
Scanner s = new Scanner(System.in);
while (true) {
System.out.println("------------欢迎进入员工管理系统------------");
System.out.print("是否拥有登录账号(是/否):");
String next = s.next();
if (next.equals("是")) {
while (true) {
System.out.println("系统登录!");
System.out.print("请输入用户名:");
String username = s.next();
System.out.print("请输入密码:");
int password = s.nextInt();
boolean login = employeeDaolmpl.Login(username, password);
if (login) {
System.out.println("登录成功!");
employeeTest.gongneng();
return;
} else {
System.out.println("登录失败,请重新登录!");
}
}
} else {
System.out.println("没有登录账号,进入系统注册!");
employeeTest.addexam();
}
}
}
public void gongneng() throws SQLException {
EmployeeTest employeeTest = new EmployeeTest();
Scanner s = new Scanner(System.in);
System.out.println("------------欢迎使用员工管理系统------------");
while (true){
System.out.println("---------请选择要对员工信息做的操作!-------");
System.out.println("1.查询 2.添加 3.删除 4.修改 5.退出");
System.out.print("输入你选择的编号:");
String bh = s.next();
switch (bh){
case "1":
employeeTest.chaxun();
break;
case "2":
employeeTest.addEmp();
break;
case "3":
employeeTest.deletmp();
break;
case "4":
employeeTest.updateEmp();
break;
case "5":
System.out.println("退出系统,欢迎再次使用!");
return;
default:
System.out.println("没有给选项,请重新选择!");
break;
}
}
}
public void findall() throws SQLException {
EmployeeDaolmpl emp = new EmployeeDaolmpl();
List<Emp> list = emp.findAll();
for (Emp emp1 : list) {
System.out.println(emp1);
}
}
public void findEmployeebyid() throws SQLException {
Scanner sc = new Scanner(System.in);
System.out.println("根据员工编号获取员工!");
System.out.print("请输入员工编号:");
int id = sc.nextInt();
EmployeeDaolmpl emp1 = new EmployeeDaolmpl();
Emp byId = emp1.findEmployeeById(id);
if (byId != null){
System.out.println(byId);
}else {
System.out.println("该工人信息不存在!");
}
}
@SuppressWarnings("all")
public void addEmp() throws SQLException {
EmployeeDaolmpl emp2 = new EmployeeDaolmpl();
Scanner sc1 = new Scanner(System.in);
Emp emp1 = new Emp();
System.out.println("添加工人信息!");
System.out.print("请输入工人姓名:");
String name = sc1.next();
System.out.print("请输入工人年龄:");
int age = sc1.nextInt();
System.out.print("请输入工人性别:");
String gender = sc1.next();
System.out.print("请输入工人地址:");
String address = sc1.next();
System.out.print("请输入工人工资:");
double salary = sc1.nextDouble();
emp1.setName(name);
emp1.setAge(age);
emp1.setGender(gender);
emp1.setAddress(address);
emp1.setSalary(salary);
emp2.addEmployee(emp1);
}
@SuppressWarnings("all")
public void updateEmp() throws SQLException {
Scanner sc1 = new Scanner(System.in);
Emp emp3 = new Emp();
System.out.println("修改工人信息!");
System.out.print("请输入修改工人信息的工人工号:");
int id2 = sc1.nextInt();
System.out.print("请输入工人姓名:");
String name1 = sc1.next();
System.out.print("请输入工人年龄:");
int age1 = sc1.nextInt();
System.out.print("请输入工人性别:");
String gender1 = sc1.next();
System.out.print("请输入工人地址:");
String address1 = sc1.next();
System.out.print("请输入工人工资:");
double salary1 = sc1.nextDouble();
emp3.setId(id2);
emp3.setName(name1);
emp3.setAge(age1);
emp3.setGender(gender1);
emp3.setAddress(address1);
emp3.setSalary(salary1);
EmployeeDaolmpl empupdat = new EmployeeDaolmpl();
empupdat.updateEmployee(emp3);
}
public void deletmp() throws SQLException {
Scanner scanner = new Scanner(System.in);
EmployeeDaolmpl daolmpl = new EmployeeDaolmpl();
System.out.println("根据员工编号删除员工信息!");
System.out.print("请输入要删除员工信息的员工工号:");
int id3 = scanner.nextInt();
daolmpl.deleteEmp(id3);
}
public void addexam() throws SQLException {
EmployeeDaolmpl daolmpl = new EmployeeDaolmpl();
Scanner s2 = new Scanner(System.in);
System.out.println("------注册功能------");
System.out.print("请输入用户名:");
String username1 = s2.next();
System.out.print("请输入密码:");
int password1 = s2.nextInt();
Exam exam = new Exam();
exam.setUsername(username1);
exam.setPassword(password1);
daolmpl.addExam(exam);
}
public void chaxun() throws SQLException {
EmployeeTest employeeTest = new EmployeeTest();
EmployeeDaolmpl daolmpl = new EmployeeDaolmpl();
Scanner sca = new Scanner(System.in);
while (true){
System.out.println("----查询功能----");
System.out.println("1.查询所有员工 2.根据员工编号查询员工 3.获取员工表中总记录数 4.返回");
System.out.print("输入选择的编号:");
String next = sca.next();
switch (next){
case "1":
employeeTest.findall();
break;
case "2":
employeeTest.findEmployeebyid();
break;
case "3":
System.out.println("员工总人数为:"+daolmpl.sumEmd());
break;
case "4":
return;
default:
System.out.println("没有给选项,请重新选择!");
break;
}
}
}
}
我的文件结构,注意要导入相应的包,大家需要自行下载依赖包
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/8f7707d4cc09256a85e772c0e4d875b4.png)
运行截图
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/198f57d8c7b909592cba029e573238b7.png)