自行设计表结构,字段至少5个,尽量采用不同数据类型的数据。通过关系数据库实现数据库的创建及删除功能,增、删、改、查数据的功能。要求:增、删、改、查操作中的数据要通过界面输入完成,不能直接在程序中写。
时间: 2024-03-05 22:51:59 浏览: 14
好的,我可以为您提供一份示例表结构和相应的增删改查功能代码。这里我采用MySQL作为关系数据库,Java语言进行编写。
首先,我们可以设计一个用户表,表名为"user",包含以下字段:
| 字段名 | 类型 | 说明 |
| --- | --- | --- |
| id | int | 用户ID,主键 |
| name | varchar(20) | 用户名 |
| age | int | 年龄 |
| gender | char(1) | 性别 |
| email | varchar(50) | 邮箱 |
接下来,我们可以编写Java程序实现数据库的创建和删除功能,以及增删改查数据的功能。具体实现代码如下:
```java
import java.sql.*;
public class UserDatabase {
private Connection connection;
private Statement statement;
// 构造函数,连接数据库
public UserDatabase(String url, String user, String password) throws SQLException {
connection = DriverManager.getConnection(url, user, password);
statement = connection.createStatement();
}
// 创建用户表
public void createTable() throws SQLException {
String sql = "CREATE TABLE IF NOT EXISTS user (" +
"id INT NOT NULL AUTO_INCREMENT PRIMARY KEY," +
"name VARCHAR(20) NOT NULL," +
"age INT NOT NULL," +
"gender CHAR(1) NOT NULL," +
"email VARCHAR(50) NOT NULL" +
");";
statement.executeUpdate(sql);
}
// 删除用户表
public void dropTable() throws SQLException {
String sql = "DROP TABLE IF EXISTS user;";
statement.executeUpdate(sql);
}
// 添加用户
public void addUser(User user) throws SQLException {
String sql = "INSERT INTO user (name, age, gender, email) VALUES (?, ?, ?, ?);";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, user.getName());
preparedStatement.setInt(2, user.getAge());
preparedStatement.setString(3, user.getGender());
preparedStatement.setString(4, user.getEmail());
preparedStatement.executeUpdate();
}
// 删除用户
public void deleteUser(int id) throws SQLException {
String sql = "DELETE FROM user WHERE id = ?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
preparedStatement.executeUpdate();
}
// 修改用户
public void updateUser(User user) throws SQLException {
String sql = "UPDATE user SET name = ?, age = ?, gender = ?, email = ? WHERE id = ?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, user.getName());
preparedStatement.setInt(2, user.getAge());
preparedStatement.setString(3, user.getGender());
preparedStatement.setString(4, user.getEmail());
preparedStatement.setInt(5, user.getId());
preparedStatement.executeUpdate();
}
// 查询用户
public User getUser(int id) throws SQLException {
String sql = "SELECT * FROM user WHERE id = ?;";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setName(resultSet.getString("name"));
user.setAge(resultSet.getInt("age"));
user.setGender(resultSet.getString("gender"));
user.setEmail(resultSet.getString("email"));
return user;
}
return null;
}
}
```
上面的代码中,我们通过Connection和Statement创建连接和执行SQL语句,通过PreparedStatement实现预编译,防止SQL注入攻击。同时,我们定义了一个User类,用于封装用户的信息。
最后,我们可以在主程序中调用上述函数实现数据库的创建和操作。例如:
```java
public class Main {
public static void main(String[] args) throws SQLException {
UserDatabase userDatabase = new UserDatabase("jdbc:mysql://localhost:3306/test", "root", "password");
userDatabase.createTable();
// 添加用户
User user1 = new User("Alice", 20, "F", "alice@example.com");
userDatabase.addUser(user1);
// 修改用户
User user2 = new User(1, "Bob", 25, "M", "bob@example.com");
userDatabase.updateUser(user2);
// 查询用户
User user3 = userDatabase.getUser(1);
System.out.println(user3);
// 删除用户
userDatabase.deleteUser(1);
userDatabase.dropTable();
userDatabase.close();
}
}
```
以上就是一个简单的示例,您可以根据自己的实际需求进行修改和扩展。希望可以对您有所帮助!