DBUtils 实现增删查改-蒲公英云

DBUtils 实现增删查改

野性酷女 2022-04-18 05:46 416阅读 0赞

需要引入jar包

文章链接————》C3P0连接池

package WEB10_JDBC_Utils;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Utils {
    private static ComboPooledDataSource dataSource= new ComboPooledDataSource();
    public static DataSource getDataSource() {
        return dataSource;
    }
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException();
        }
    }
}
package JDBCTest;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import WEB10_JDBC_Utils.C3P0Utils;
public class TestDBUtils1 {
    @Test
    public void add() {
        try {
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            String sql = "insert into user1 values(null,?,?)";
            Object[] params = {"古话","123"};
            int row = qr.update(sql, params);
            if (row > 0) {
                System.out.println("yes");
            } else {
                System.out.println("no");
            }
        } catch (Exception e) {
            throw new RuntimeException();
        }
    }
    @Test
    public void UpdateById() {
        try {
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            String sql = "update user1 set password=? where uid=?";
            Object[] params = {"古话","1"};
            int row = qr.update(sql, params);
            if (row > 0) {
                System.out.println("yes");
            } else {
                System.out.println("no");
            }
        } catch (Exception e) {
            throw new RuntimeException();
        }
    }
    @Test
    public void DeleteById() {
        try {
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            String sql = "delete from user1 where uid=?";
            Object[] params = {1};
            int row = qr.update(sql, params);
            if (row > 0) {
                System.out.println("yes");
            } else {
                System.out.println("no");
            }
        } catch (Exception e) {
            throw new RuntimeException();
        }
    }
}

查询需要 bean：

package JDBCBean;
public class User {
    private int uid;
    private String name;
    private String password;
    public int getUid() {
        return uid;
    }
    public void setUid(int uid) {
        this.uid = uid;
    }
    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;
    }
}

处理结果集：

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTY2Nzcy_size_16_color_FFFFFF_t_70

package JDBCTest;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import JDBCBean.User;
import WEB10_JDBC_Utils.C3P0Utils;
public class DBUtils2 {
    @Test
    public void selectAll() {
        try {
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            String sql = "select * from user1";
            List<User> list = qr.query(sql,new BeanListHandler<User>(User.class));
            for(User user : list) {
                System.out.println(user.getName()+":"+user.getPassword());
            }
        } catch (Exception e) {
            throw new RuntimeException();
        }
    }
    @Test
    public void selectById() {
        try {
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            String sql = "select * from user1 where uid=?";
            Object[] params = {3};
            User u = qr.query(sql,new BeanHandler<User>(User.class),params);
            System.out.println(u.getName()+":"+u.getPassword());
        } catch (Exception e) {
            throw new RuntimeException();
        }
    }
    @Test
    public void selectCount() {
        try {
            QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
            String sql = "select count(*) from user1";
            Object u = qr.query(sql, new ScalarHandler());
            System.out.println(u);
        } catch (Exception e) {
            throw new RuntimeException();
        }
    }
}