使用JDBC完成数据的增删改查
一、 设计思路(实现原理)
1) 创建一个用于封装数据的JavaBean。
2) 将加载驱动和获取数据连接等操作封装在一个工具类中,以供其他类调用。
3) 创建一个实现了对数据库添删改查操作的DAO
4) 分别创建用于执行对数据表添删改查操作的测试类。
5) 执行程序,测试结果。
二、案例实现
1**.创建JavaBean**(User)
创建一个用于保存用户数据的User类,User类的具体实现方式如下所示。
package com.gd.ban;
import java.util.Date;
/**
* @author LFXIANG
* description TODO
* @title User
* date 2024/4/8 18:31
*/
public class User {
private int id;
private String username;
private String password;
private String email;
private Date birthday;
public User() {
}
public User(int id, String username, String password, String email, Date birthday) {
this.id = id;
this.username = username;
this.password = password;
this.email = email;
this.birthday = birthday;
}
public User(String username, String password, String email, Date birthday) {
this.username = username;
this.password = password;
this.email = email;
this.birthday = birthday;
}
public int getId() {
return id;
}
public void setId(int 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;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", email='" + email + '\'' +
", birthday=" + birthday +
'}';
}
}
2**.创建工具类**
由于每次操作数据库时,都需要加载数据库驱动、建立数据库连接以及关闭数据库连接,为了避免代码的重复书写,下面建立一个专门用于数据库相关操作的工具类JDBCUtils,JDBCUtils的具体实现方式如下所示。
package com.gd.utile;
import java.sql.*;
/**
* @author LFXIANG
* description TODO
* @title jdbcutils
* date 2024/4/8 18:32
*/
public class Jdbcutils {
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection(){
Connection connection=null;
try {
//这里我的数据库端口号为3306 数据库名为ex 账号(root) 密码(***)
connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/ex?characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false","root","***");
} catch (SQLException e) {
throw new RuntimeException(e);
}
return connection;
}
public static void release(Connection connection, PreparedStatement statement, ResultSet resultSet){
try {
if (resultSet!=null){
resultSet.close();
}
if (statement!=null){
statement.close();
}
if (resultSet!=null){
resultSet.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
3**.创建****DAO**
这里我的数据库到表为user
创建一个名称为UsersDao的类,该类中封装了对表users的添加、查询、删除和更新等操作,具体实现方式如下所示。
package com.gd.Dao;
import com.gd.ban.User;
import com.gd.utile.Jdbcutils;
import java.sql.*;
import java.util.ArrayList;
/**
* @author LFXIANG
* description TODO
* @title UserDao
* date 2024/4/8 18:31
*/
public class UserDao {
public boolean insert(User user){
Connection connection = null;
PreparedStatement st=null;
try {
connection = Jdbcutils.getConnection();
String str=("INSERT into user (username,password,email,birthday) values (?,?,?,?)");
st = connection.prepareStatement(str);
st.setString(1,user.getUsername());
st.setString(2,user.getPassword());
st.setString(3,user.getEmail());
java.sql.Date date = new java.sql.Date(user.getBirthday().getTime());
st.setDate(4, date);
int i = st.executeUpdate();
return i>0? true:false;
} catch (SQLException e) {
return false;
}finally {
Jdbcutils.release(connection,st,null);
}
}
public ArrayList<User> findAll(){
Connection connection=null;
PreparedStatement st=null;
ResultSet rs=null;
ArrayList<User> userslist = new ArrayList<>();
try {
connection = Jdbcutils.getConnection();
String str=("select * from user");
st=connection.prepareStatement(str);
rs = st.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
String email = rs.getString("email");
Date birthday = rs.getDate("birthday");
User user = new User(id, username, password, email, birthday);
userslist.add(user);
}
return userslist;
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
Jdbcutils.release(connection,st,rs);
}
}
public User find(int id){
Connection connection=null;
PreparedStatement st=null;
ResultSet rs=null;
User user=null;
try {
connection = Jdbcutils.getConnection();
String str=("select * from user where id=?");
st = connection.prepareStatement(str);
st.setInt(1,id);
rs = st.executeQuery();
while (rs.next()){
int id1 = rs.getInt("id");
String username = rs.getString("username");
String password = rs.getString("password");
String email = rs.getString("email");
Date birthday = rs.getDate("birthday");
user = new User(id, username, password, email, birthday);
}
return user;
} catch (SQLException e) {
throw new RuntimeException(e);
}finally {
Jdbcutils.release(connection,st,rs);
}
}
public boolean delete(int id){
Connection connection=null;
PreparedStatement st=null;
try {
connection = Jdbcutils.getConnection();
String str=("delete from user where id= ?");
st=connection.prepareStatement(str);
st.setInt(1,id);
int i = st.executeUpdate();
return i>0? true:false;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
Jdbcutils.release(connection,st,null);
}
}
public boolean update(User user){
Connection connection=null;
PreparedStatement st=null;
try {
connection=Jdbcutils.getConnection();
String str=("UPDATE user SET username=?,password=?,email=?,birthday=? WHERE Id= ?");
st = connection.prepareStatement(str);
st.setString(1,user.getUsername());
st.setString(2,user.getPassword());
st.setString(3,user.getEmail());
java.sql.Date date = new java.sql.Date(user.getBirthday().getTime());
st.setDate(4,date);
st.setInt(5,user.getId());
int i = st.executeUpdate();
return i>0? true:false;
} catch (SQLException e) {
return false;
} finally {
Jdbcutils.release(connection,st,null);
}
}
}
4**.创建测试类**
(1)编写测试类JdbcInsertTest,实现向user表中添加一条数据
package com.gd.test;
import com.gd.Dao.UserDao;
import com.gd.ban.User;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* @author LFXIANG
* description TODO
* @title JdbcInsertTest
* date 2024/4/8 20:08
*/
public class JdbcInsertTest {
public static void main(String[] args) throws ParseException {
UserDao userDao = new UserDao();
String str="2001-3-3";
Date date = new SimpleDateFormat("yyyy-MM-dd").parse(str);
User user = new User("xiaogang","123","xh@aa.com",date);
boolean insert = userDao.insert(user);
if (insert){
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
}
}
运行测试
(2)编写测试类FindAllUsersTest,该类用于实现读取user表中所有的数据
package com.gd.test;
import com.gd.Dao.UserDao;
import com.gd.ban.User;
import java.util.ArrayList;
/**
* @author LFXIANG
* description TODO
* @title FindAllUsersTest
* date 2024/4/8 20:08
*/
public class FindAllUsersTest {
public static void main(String[] args) {
UserDao userDao = new UserDao();
ArrayList<User> userlist = userDao.findAll();
for (int i=0;i<userlist.size();i++){
System.out.println("第"+(i+1)+"条数据的username值为" + userlist.get(i).getUsername());
}
}
}
运行测试
(3)编写测试类FindUserByIdTest,该类实现了读取user表中指定的数据
package com.gd.test;
import com.gd.Dao.UserDao;
import com.gd.ban.User;
/**
* @author LFXIANG
* description TODO
* @title FindUserByIdTest
* date 2024/4/8 20:13
*/
public class FindUserByIdTest {
public static void main(String[] args) {
UserDao userDao = new UserDao();
User user = userDao.find(5);
System.out.println("id为5的User对象的username值为 " + user.getUsername());
}
}
(4)编写测试类UpdateUserTest,该类实现了修改users表中数据的操作
package com.gd.test;
import com.gd.Dao.UserDao;
import com.gd.ban.User;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* @author LFXIANG
* description TODO
* @title UpdateUserTest
* date 2024/4/8 20:44
*/
public class UpdateUserTest {
public static void main(String[] args) throws ParseException {
UserDao userDao = new UserDao();
Date parse = new SimpleDateFormat("yyy-MM-dd").parse("2000-3-2");
User user = new User(20,"老徐","333","lx@.com",parse);
boolean update = userDao.update(user);
if (update){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
}
}
运行测试
(5)编写测试类DeleteUserTest,该类实现了删除user表中数据的操作
package com.gd.test;
import com.gd.Dao.UserDao;
/**
* @author LFXIANG
* description TODO
* @title DeleteUserTest
* date 2024/4/8 20:51
*/
public class DeleteUserTest {
public static void main(String[] args) {
UserDao userDao = new UserDao();
boolean delete = userDao.delete(28);
if (delete){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
}
}
更改要删除的id,运行测试删除