DBUtils 实现增删查改

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

需要引入jar包

![20181109111324507.png][]

文章链接--------》[C3P0连接池][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();
    		}
    	}
    }

[20181109111324507.png]: /images/20220418/99fd991203ae4beda9c7a666e48e3ee1.png
[C3P0]: https://blog.csdn.net/qq_41566772/article/details/83894195
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTY2Nzcy_size_16_color_FFFFFF_t_70]: /images/20220418/4199af1a10874f61a49af2d3dd6d8c70.png