一.导包
二.创建资源配置文件
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/student?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
username=********
password=********
initialSize=5
maxActive=10
maxWait=3000二.代码
创建工具类(JdbcUtils.class)
package Information_sysyem_jdbc.Utiles;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author LiangHaiBo
* @version 1.0
* @date 2021.8.21 上午 9:31
*/
public class JdbcUtils {
private static DataSource ds;
private static ThreadLocal<Connection> t1 = new ThreadLocal<>();
//构造方法私有化
private JdbcUtils() {
}
//静态代码块
static {
try {
Properties prop = new Properties();
InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("DruidJdbc.properties");
prop.load(inputStream);
ds = DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//提供静态方法,单独获取数据源
public static DataSource getDataSourse() {
return ds;
}
//获取连接对象Connection静态功能
public static Connection getConnection() {
Connection conn = null;
try {
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(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
//关闭之后,归还到连接池中,需要从当前线程中解绑
t1.remove();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(Statement stmt, Connection conn) {
close(null, stmt, conn);
}
}
2.实体类(Information)
package Information_sysyem_jdbc.entity;
/**
* @author LiangHaiBo
* @version 1.0
* @date 2021.8.21 上午 9:34
*/
public class Information {
/** id INT PRIMARY KEY,
NAME VARCHAR(20),
gender VARCHAR(10),
age INT,
address VARCHAR(50),
phone VARCHAR(20));
*/
private int id;
private String name;
private String gender;
private int age;
private String address;
private String phone;
public Information() {}
public Information(int id, String name, String gender, int age, String address, String phone) {
this.id = id;
this.name = name;
this.gender = gender;
this.age = age;
this.address = address;
this.phone = phone;
}
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 getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Information{" +
"id=" + id +
", name='" + name + '\'' +
", gender='" + gender + '\'' +
", age='" + age + '\'' +
", address='" + address + '\'' +
", phone='" + phone + '\'' +
'}';
}
}
3.服务层
(1)Informationservice接口
package Information_sysyem_jdbc.Service;
import Information_sysyem_jdbc.entity.Information;
import java.util.List;
/**
* @author LiangHaiBo
* @version 1.0
* @date 2021.8.21 上午 9:33
*/
public interface InformationService {
/**
* 查询所有信息
* @return 信息列表
*/
List<Information> selectFindAllGet();
/**
* 通过id号查询员工信息
* @param id
* @return 此id所对应的员工信息
*/
Information selectfindByidGet(int id);
/**
* 查询总记录数
* @return 总记录条数
*/
int selectAllCountGet();
//添加用户信息
int addGet(Information information);
//删除用户
int deleteGet(int id);
//通过id更新员工
int updateInformationGet(int id);
}
(2)服务层接口实现类
package Information_sysyem_jdbc.Service.impl;
import Information_sysyem_jdbc.Dao.InformationDao;
import Information_sysyem_jdbc.Dao.impl.InformationDaoimpl;
import Information_sysyem_jdbc.Service.InformationService;
import Information_sysyem_jdbc.entity.Information;
import java.util.List;
/**
* @author LiangHaiBo
* @version 1.0
* @date 2021.8.21 上午 9:34
*/
public class InformationServiceimpl implements InformationService {
@Override
public List<Information> selectFindAllGet() {
InformationDao fd = new InformationDaoimpl();
List<Information> list = fd.selectFindAll();
return list;
}
@Override
public Information selectfindByidGet(int id) {
InformationDao fd = new InformationDaoimpl();
Information information = fd.selectfindByid(id);
return information;
}
@Override
public int selectAllCountGet() {
InformationDao fd = new InformationDaoimpl();
int i = fd.selectAllCount();
return i;
}
@Override
public int addGet(Information information) {
InformationDao fd = new InformationDaoimpl();
return fd.add(information);
}
@Override
public int deleteGet(int id) {
InformationDao fd = new InformationDaoimpl();
return fd.delete(id);
}
@Override
public int updateInformationGet(int id) {
InformationDao fd = new InformationDaoimpl();
return fd.update(id);
}
}
四.数据访问层
(1).数据访问层接口
package Information_sysyem_jdbc.Dao;
import Information_sysyem_jdbc.entity.Information;
import java.util.List;
/**
* @author LiangHaiBo
* @version 1.0
* @date 2021.8.21 上午 9:35
*/
public interface InformationDao {
/**
* 查询所有信息
* @return 信息列表
*/
List<Information> selectFindAll();
/**
* 通过id号查询员工信息
* @param id
* @return 此id所对应的员工信息
*/
Information selectfindByid(int id);
/**
* 查询总记录数
* @return 总记录条数
*/
int selectAllCount();
/**
* 添加用户信息
*
*/
int add(Information information);
//删除用户
int delete(int id);
//通过id更新员工
int update(int id);
}
(2)数据访问层接口实现类
package Information_sysyem_jdbc.Dao.impl;
import Information_sysyem_jdbc.Dao.InformationDao;
import Information_sysyem_jdbc.Utiles.JdbcUtils;
import Information_sysyem_jdbc.entity.Information;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
/**
* @author LiangHaiBo
* @version 1.0
* @date 2021.8.21 上午 9:35
*/
public class InformationDaoimpl implements InformationDao {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
@Override
public List<Information> selectFindAll() {
try {
conn = JdbcUtils.getConnection();
String sql = "select * from Information ";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
List<Information> list = new ArrayList<>();
Information information = null;
while (rs.next()){
information = new Information();
int id = rs.getInt("id");
String name = rs.getString("name");
String gender = rs.getString("gender");
int age = rs.getInt("age");
String address = rs.getString("address");
String phone = rs.getString("phone");
information.setId(id);
information.setName(name);
information.setGender(gender);
information.setAge(age);
information.setAddress(address);
information.setPhone(phone);
list.add(information);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(rs,stmt,conn);
}
return null;
}
@Override
public Information selectfindByid(int id) {
Information information = new Information();
if(id<=0 || id>selectAllCount()){
return null;
}else {
try {
conn = JdbcUtils.getConnection();
String sql = "select * from Information where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, id);
rs = stmt.executeQuery();
while (rs.next()) {
int id1 = rs.getInt("id");
String name = rs.getString("name");
String gender = rs.getString("gender");
int age = rs.getInt("age");
String address = rs.getString("address");
String phone = rs.getString("phone");
information.setId(id1);
information.setName(name);
information.setGender(gender);
information.setAge(age);
information.setAddress(address);
information.setPhone(phone);
}
return information;
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(rs, stmt, conn);
}
return information;
}
}
@Override
public int selectAllCount() {
int account = 0;
conn = JdbcUtils.getConnection();
try {
String sql = "Select id from Information";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
while (rs.next()) {
account++;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(rs, stmt, conn);
}
return account;
}
@Override
public int add(Information information) {
try {
conn = JdbcUtils.getConnection();
String sql = "insert into Information (id,name,gender,age,address,phone) values(?,?,?,?,?,?)";
stmt = conn.prepareStatement(sql);
stmt.setInt(1,information.getId());
stmt.setString(2,information.getName());
stmt.setString(3,information.getGender());
stmt.setInt(4,information.getAge());
stmt.setString(5,information.getAddress());
stmt.setString(6,information.getPhone());
int i = stmt.executeUpdate();
System.out.println(i+"行受到影响");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(stmt,conn);
}
return 0;
}
@Override
public int delete(int id) {
try {
conn = JdbcUtils.getConnection();
String sql ="delete from Information where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1,id);
int i = stmt.executeUpdate();
System.out.println(i+"行受到影响");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(stmt,conn);
}
return 0;
}
@Override
public int update(int id) {
int count = 0;
if(id<=0 || id>selectAllCount()){
if(selectfindByid(id)==null){
System.out.println("用户不存在");
}
}else {
System.out.println(selectfindByid(id));
conn = JdbcUtils.getConnection();
Scanner sc = new Scanner(System.in);
System.out.println("1.姓名");
System.out.println("2.性别");
System.out.println("3,年龄");
System.out.println("4.地址");
System.out.println("5.手机号");
System.out.println("6.退出");
System.out.println("请选择要修改的内容序号:");
String choice = sc.next();
switch (choice){
case "1":
String sql = "update Information set name =? where id =?";
try {
stmt = conn.prepareStatement(sql);
updatecount(id, sc);
count = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(stmt,conn);
}
break;
case "2":
sql = "update Information set gender =? where id =?";
try {
stmt = conn.prepareStatement(sql);
updatecount(id, sc);
count = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(stmt,conn);
}
break;
case"3":
sql = "update Information set age =? where id =?";
try {
stmt = conn.prepareStatement(sql);
updatecount(id, sc);
count = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(stmt,conn);
}
break;
case"4":
sql = "update Information set address =? where id =?";
try {
stmt = conn.prepareStatement(sql);
updatecount(id, sc);
count = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(stmt,conn);
}
break;
case"5":
sql = "update Information set phone =? where id =?";
try {
stmt = conn.prepareStatement(sql);
updatecount(id, sc);
count = stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.close(stmt,conn);
}
break;
case "6":
break;
}
}
System.out.println(selectfindByid(id));
return count;
}
private void updatecount(int id, Scanner sc) throws SQLException {
System.out.println("请输入修改信息:");
stmt.setString(1, sc.next());
stmt.setInt(2, id);
}
}
五.测试类
package Information_sysyem_jdbc.Test;
import Information_sysyem_jdbc.Service.InformationService;
import Information_sysyem_jdbc.Service.impl.InformationServiceimpl;
import Information_sysyem_jdbc.entity.Information;
import java.util.List;
import java.util.Scanner;
/**
* @author LiangHaiBo
* @version 1.0
* @date 2021.8.21 下午 15:11
*/
public class mytest {
public static void main(String[] args) {
InformationService informationService = new InformationServiceimpl();
Scanner input = new Scanner(System.in);
while (true) {
System.out.println("------------欢迎访问数据库管理系统------------ \n" + "1 - 添加 \n" + "2 - 删除 \n"
+ "3 - 修改 \n" + "4 - 查看指定信息 \n" + "5 - 查看所有信息 \n" + "6 - 统计数据数据条数 \n"
+ "7 - 退出系统 \n");
System.out.println("请输入操作");
switch (input.next()) {
case "1":
System.out.println("请输入id号:");
int id = input.nextInt();
System.out.println("请输入姓名:");
String name = input.next();
System.out.println("请输入性别:");
String gender = input.next();
System.out.println("请输入年龄:");
int age = input.nextInt();
System.out.println("请输入家庭地址:");
String address = input.next();
System.out.println("请输入手机号:");
String phone = input.next();
Information information = new Information(id, name, gender, age, address, phone);
int i = informationService.addGet(information);
if (i > 0) {
System.out.println("添加成功");
}
break;
case "2":
System.out.println("请输入想要删除的id:");
int id1 = input.nextInt();
int i1 = informationService.deleteGet(id1);
if (i1 > 0) {
System.out.println("删除成功");
}
break;
case "3":
System.out.println("请输入要修改的id号:");
int id2 = input.nextInt();
int i2 = informationService.updateInformationGet(id2);
if (i2 > 0) {
System.out.println("修改成功");
}
break;
case "4":
System.out.println("请输入id号:");
int id3 = input.nextInt();
Information information1 = informationService.selectfindByidGet(id3);
System.out.println(information1);
break;
case "5":
System.out.println("以下为所有用户信息:");
List<Information> list = informationService.selectFindAllGet();
if (list != null) {
for (Information information2 : list) {
System.out.println(information2);
}
}
break;
case "6":
int i3 = informationService.selectAllCountGet();
System.out.println("统计结果为" + i3 + "条数据信息");
break;
case "7":
System.exit(0);
}
}
}
}
数据库创建表(Information)
use student;
create table Information(
id int primary key,
name varchar(20),
gender varchar(10),
age int,
address varchar(50),
phone varchar(20));
insert into Information values
(1,'**','男',20,'陕西省*****','13*****456');
INSERT INTO Information VALUES
(2,'**','男',21,'陕西省****','132****6787'),
(3,'**','男',21,'陕西省*****','1567*****797'),
(4,'***','男',20,'陕西省*****','18723****74');六.效果展示
添加三条数据信息
查询(遍历所有人员信息)
查询指定id信息
统计数据条数
指定id进行删除
版权声明：本文为Liang_it_yuan原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。