已有数据库jdbc,表名为user,表中已有数据id、name、email、password、birthday,如何使用JDBC完成对数据库中用户信息的增删改查操作
时间: 2023-06-18 12:02:29 浏览: 374
使用JDBC进行数据库操作的基本步骤如下:
1. 加载数据库驱动程序
2. 建立数据库连接
3. 创建Statement对象或PrepareStatement对象
4. 执行SQL语句
5. 处理结果集
6. 关闭连接
下面是对用户信息的增删改查操作的示例代码:
```java
import java.sql.*;
public class UserDAO {
// 建立数据库连接
public Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8";
String username = "root";
String password = "123456";
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
// 添加用户
public void addUser(User user) {
Connection conn = getConnection();
PreparedStatement pstmt = null;
String sql = "insert into user(name, email, password, birthday) values(?,?,?,?)";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getEmail());
pstmt.setString(3, user.getPassword());
pstmt.setDate(4, new java.sql.Date(user.getBirthday().getTime()));
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 删除用户
public void deleteUser(int id) {
Connection conn = getConnection();
PreparedStatement pstmt = null;
String sql = "delete from user where id=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 更新用户信息
public void updateUser(User user) {
Connection conn = getConnection();
PreparedStatement pstmt = null;
String sql = "update user set name=?, email=?, password=?, birthday=? where id=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getEmail());
pstmt.setString(3, user.getPassword());
pstmt.setDate(4, new java.sql.Date(user.getBirthday().getTime()));
pstmt.setInt(5, user.getId());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
// 查询用户信息
public User findUserById(int id) {
Connection conn = getConnection();
PreparedStatement pstmt = null;
ResultSet rs = null;
User user = null;
String sql = "select * from user where id=?";
try {
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setEmail(rs.getString("email"));
user.setPassword(rs.getString("password"));
user.setBirthday(rs.getDate("birthday"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return user;
}
}
```
其中,User类表示用户信息,包括id、name、email、password、birthday等属性。在上面的代码中,addUser方法用于添加用户,deleteUser方法用于删除用户,updateUser方法用于更新用户信息,findUserById方法用于查询指定id的用户信息。其他操作类似,可以根据需要进行扩展。
阅读全文