当前位置：首页 > news >正文

基于JavaScript的DBUtils增删改查操作实验

news 2025/7/9 23:21:04

1、实验目的

学习和掌握数据库连接池的配置与管理。使用DBUtils进行增删改查操作。按照步骤，掌握并实现使用DBUtils实现增删改查的全过程。

2、实验所用方法

上机实践

3、实验步骤及截图

创建一个数据库表，使用下面sql语句创建数据库表并插入数据：

CREATE TABLE USER(
id INT(3) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
PASSWORD VARCHAR(20) NOT NULL
);INSERT INTO USER(NAME,PASSWORD) VALUES ('zhangsan','123456');
INSERT INTO USER(NAME,PASSWORD) VALUES ('lisi','123456');
INSERT INTO USER(NAME,PASSWORD) VALUES ('wangwu','123456');

在项目chapter11的目录下，创建一个名为cn.itcast.jdbc.javabean的包，创建实体类User，用于封装User对象：

package cn.itcast.jdbc.javabean;
public class User {private int id;private String name;private String password;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 String getPassword() {return password;}public void setPassword(String password) {this.password = password;}
}

在项目chapter11的src目录下，创建一个名为cn.itcast.jdbc.utils的包，然后在该包下创建C3p0Utils类，用于创建数据源：

package cn.itcast.jdbc.utils;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Utils {private static DataSource ds;static {ds = new ComboPooledDataSource();}public static DataSource getDataSource() {return ds;}
}

连接数据库不要忘记写JDBCUtils.java：

package cn.itcast.chapter11.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {// 加载驱动，并建立数据库连接public static Connection getConnection() throws SQLException,ClassNotFoundException {Class.forName("com.mysql.cj.jdbc.Driver");String url = "jdbc:mysql://localhost:3306/jdbc?serverTimezone=GMT%2B8";String username = "root";String password = "root";Connection conn = DriverManager.getConnection(url, username,password);return conn;}// 关闭数据库连接，释放资源public static void release(Statement stmt, Connection conn) {if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}stmt = null;}if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}conn = null;}}public static void release(ResultSet rs, Statement stmt,Connection conn){if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}rs = null;}release(stmt, conn);}
}

在项目chapter11的src目录下，创建一个名为cn.itcast.jdbc.dao的包，然后在该包下创建一个InsertDao类，实现对user表插入数据的操作：

package cn.itcast.jdbc.dao;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import cn.itcast.jdbc.javabean.User;
import cn.itcast.jdbc.utils.C3p0Utils;
public class InsertDao {public static void main(String[] args)throws SQLException{// 创建QueryRunner对象QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());String sql = "insert into user (name,password) values ('hello1',123456)";int num = runner.update(sql);if (num > 0){System.out.println("添加成功！");}else{System.out.println("添加失败！");}}
}

文件InsertDao的运行结果：

在cn.itcast.jdbc.dao包下创建一个UpdateDao类，实现对user表数据的修改操作。UpdateDao类的实现如下所示:

package cn.itcast.jdbc.dao;
import cn.itcast.jdbc.javabean.User;
import cn.itcast.jdbc.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import java.sql.SQLException;
public class UpdateDao {public static void main(String[] args)throws SQLException {// 创建QueryRunner对象QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());// 写SQL语句String sql = "update user set name='hello2',password=111111 where name='hello1'";// 调用方法int num = runner.update(sql);if (num > 0){System.out.println("修改成功！");}else{System.out.println("修改失败！");}}
}

文件UpdateDao的运行结果如下图所示:

在cn.itcast.jdbc.dao包下创建一个DeleteDao类，实现对user表数据的删除操作:

package cn.itcast.jdbc.dao;
import cn.itcast.jdbc.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import java.sql.SQLException;
public class DeleteDao {public static void main(String[] args)throws SQLException {// 创建QueryRunner对象QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());// 写SQL语句String sql = "delete from user where name='hello2'";// 调用方法int num = runner.update(sql);if (num > 0){System.out.println("删除成功！");}else{System.out.println("删除失败！");}}
}

在cn.itcast.jdbc.dao包下创建一个QueryDao类，实现对user表中单条数据的查询操作:

package cn.itcast.jdbc.dao;
import cn.itcast.jdbc.javabean.User;
import cn.itcast.jdbc.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;import java.sql.SQLException;
import java.util.List;public class QueryDao {public static void main(String[] args)throws SQLException {// 创建QueryRunner对象QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());// 写SQL语句String sql = "select * from user where id=2";// 调用方法User user = (User) runner.query(sql,new BeanHandler(User.class));System.out.println(user.getId()+","+user.getName()+","+user.getPassword());}
}

修改后：

package cn.itcast.jdbc.dao;
import cn.itcast.jdbc.javabean.User;
import cn.itcast.jdbc.utils.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;import java.sql.SQLException;
import java.util.List;public class QueryDao {public static void main(String[] args)throws SQLException {// 创建QueryRunner对象QueryRunner runner = new QueryRunner(C3p0Utils.getDataSource());// 写SQL语句String sql = "select * from user";// 调用方法List<User> list = (List) runner.query(sql,new BeanListHandler(User.class));for(User user : list){System.out.println(user.getId()+","+user.getName()+","+user.getPassword());}}
}