1.首先导入mysql connector包
2.编写接口类
定义好连接数据库要的属性:
private static final String driver="com.mysql.cj.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/jianshengfang?useSSL=false&serverTimezone=UTC";
private static final String USERNAME = "root";
private static final String PASSWORD = "123456";
把数据库名字,账号密码都改成自己的。
返回所有数据
public List<Member> getAll() {
String sql = "SELECT * FROM members";
Connection connection = null;
PreparedStatement statement = null;//预编译
ResultSet resultSet = null;//结果
List<Member> members = new ArrayList<>();
try {
// 加载JDBC驱动程序
Class.forName(driver);
// 建立连接
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// 创建PreparedStatement
statement = connection.prepareStatement(sql);
// 执行查询
resultSet = statement.executeQuery();
// 处理结果
while (resultSet.next()) {
// 从结果集中获取数据
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String level = resultSet.getString("level");
// 根据需要添加更多字段
// 处理或显示数据
System.out.println("用户ID:" + id + ",用户名:" + name+",等级:"+level);
// 根据需要添加更多处理
Member member = new Member(id,name,level);
members.add(member);
}
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace(); // 在实际应用程序中适当处理错误
} finally {
// 以相反的顺序关闭资源,以避免泄漏
try {
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return members;
}
增加一条数据
public void addMember(String memberName, String memberLevel) {
String sql = "INSERT INTO members (name, level) VALUES (?, ?)";
Connection connection = null;
PreparedStatement statement = null;
try {
// 加载JDBC驱动程序
Class.forName(driver);
// 建立连接
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// 创建PreparedStatement
statement = connection.prepareStatement(sql);
// 设置参数
statement.setString(1, memberName);
statement.setString(2, memberLevel);
// 执行更新
int rowsInserted = statement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("新成员已成功添加!");
}
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace(); // 在实际应用程序中适当处理错误
} finally {
// 以相反的顺序关闭资源,以避免泄漏
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
删除一条数据
public void deleteMember(int id) {
String sql = "DELETE FROM members WHERE id = ?";
Connection connection = null;
PreparedStatement statement = null;
try {
// 加载JDBC驱动程序
Class.forName(driver);
// 建立连接
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// 创建PreparedStatement
statement = connection.prepareStatement(sql);
// 设置参数第一个问好
statement.setInt(1, id);
// 执行更新
int rowsDeleted = statement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("会员已成功删除!");
}
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace(); // 在实际应用程序中适当处理错误
} finally {
// 以相反的顺序关闭资源,以避免泄漏
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
更新一条数据
public void updateMember(Member member) {
Connection connection = null;
PreparedStatement statement = null;
try {
// 加载JDBC驱动程序
Class.forName(driver);
// 建立连接
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// 检查name属性是否为空,如果不为空则更新
if (member.getName() != null) {
String sql = "UPDATE members SET name = ? WHERE id = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, member.getName());
statement.setInt(2, member.getId());
statement.executeUpdate();
System.out.println("更新name");
}
// 检查level属性是否为空,如果不为空则更新
if (member.getLevel() != null) {
String sql = "UPDATE members SET level = ? WHERE id = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, member.getLevel());
statement.setInt(2, member.getId());
statement.executeUpdate();
System.out.println("更新level");
}
System.out.println("会员信息已成功更新!");
} catch (SQLException | ClassNotFoundException e) {
e.printStackTrace(); // 在实际应用程序中适当处理错误
} finally {
// 以相反的顺序关闭资源,以避免泄漏
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3.总结
总的来说就是先定义连接数据库要的属性,然后编写sql语句,加载驱动,建立连接,建立preparestatement(用于执行sql语句),查询就用executequery,更新就用executeupdate,最后返回查询结果,根据结果来提取自己需要的信息。