cp30实现数据库增删查改
时间: 2023-08-17 14:32:35 浏览: 52
CP30是一个开源的JDBC连接池,它可以更好地管理数据库连接。下面给出一个使用CP30实现数据库增删查改的示例代码。
首先,需要在pom.xml中添加CP30的依赖:
```xml
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
```
然后,创建一个DBUtil类,用于创建和管理连接池:
```java
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
private static DataSource dataSource;
static {
dataSource = new ComboPooledDataSource("mysql");
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void close(Connection conn, Statement stmt, ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
```
接下来,可以创建一个User类,用于表示数据库中的一条记录:
```java
public class User {
private int id;
private String name;
private int age;
public User(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
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 int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
```
然后,可以创建一个UserDAO类,用于实现数据库的增删查改:
```java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDAO {
public void addUser(User user) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = DBUtil.getConnection();
stmt = conn.prepareStatement("INSERT INTO user(name, age) VALUES (?, ?)");
stmt.setString(1, user.getName());
stmt.setInt(2, user.getAge());
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn, stmt, null);
}
}
public void deleteUser(int id) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = DBUtil.getConnection();
stmt = conn.prepareStatement("DELETE FROM user WHERE id = ?");
stmt.setInt(1, id);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn, stmt, null);
}
}
public void updateUser(User user) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = DBUtil.getConnection();
stmt = conn.prepareStatement("UPDATE user SET name = ?, age = ? WHERE id = ?");
stmt.setString(1, user.getName());
stmt.setInt(2, user.getAge());
stmt.setInt(3, user.getId());
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn, stmt, null);
}
}
public List<User> getUsers() {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
List<User> users = new ArrayList<>();
try {
conn = DBUtil.getConnection();
stmt = conn.prepareStatement("SELECT * FROM user");
rs = stmt.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
User user = new User(id, name, age);
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn, stmt, rs);
}
return users;
}
}
```
最后,可以在Main类中测试以上的代码:
```java
public class Main {
public static void main(String[] args) {
UserDAO dao = new UserDAO();
// 添加一条记录
dao.addUser(new User(1, "张三", 20));
// 更新一条记录
dao.updateUser(new User(1, "李四", 21));
// 删除一条记录
dao.deleteUser(1);
// 查询所有记录
List<User> users = dao.getUsers();
for (User user : users) {
System.out.println(user.getId() + " " + user.getName() + " " + user.getAge());
}
}
}
```
以上代码实现了使用CP30连接池的数据库增删查改。