commons-dbutils-1.6.jar
1.commons-dbutils-1.6.jar
操作JDBC连接数据库 并且执行sql语句 处理返回的结果集
三个核心类
DbUtils: 关闭资源的方法(安静的关闭 不用异常处理)
QueryRunner: 执行sql语句 并且返回你想要的结果集
参数: ResultSetHandler是接口(使用它的实现类 想要什么结果集 就传入什么该类的接口)
private static void insert() throw SQLException{
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "insert into sort values (null, '显示器', 5,'这是一台显示器');
int row = qr.update(connection.sql);
if(row > 0){
System.out.println("插入成功");
}
DbUtil.closeQuietly(connection);
}
private static void update() throw SQLException{
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "update sort set sname=?, sprice=?, sdesc=? where sid=5";
Object[] params = {"咖啡",1000,"java里的咖啡"};
int row = qr.update(connection, sql, "咖啡1",2000,"java里的咖啡");
if (row > 0) {
System.out.println("修改成功");
}
DbUtils.closeQuietly(connection);
}
2.使用QueryRunner进行查询操作 给QueryRunner传入的参数不同 返回的结果集也不同
@Test
public void select1() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Object[] objects = qr.query(connection, sql, new ArrayHandler());
for (Object object : objects) {
System.out.println(object);
}
DbUtils.closeQuietly(connection);
}
@Test
public void select2() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Object[]> list = qr.query(connection, sql, new ArrayListHandler());
for (Object[] objects : list) {
for (Object object : objects) {
System.out.println(object);
}
System.out.println();
}
DbUtils.closeQuietly(connection);
}
@Test
public void select3() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Sort sort = qr.query(connection, sql, new BeanHandler<>(Sort.class));
if (sort == null) {
System.out.println("没有该数据");
}
System.out.println(sort);
DbUtils.closeQuietly(connection);
}
@Test
public void select4() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Sort> list = qr.query(connection, sql, new BeanListHandler<>(Sort.class));
for (Sort sort : list) {
System.out.println(sort);
}
DbUtils.closeQuietly(connection);
}
@Test
public void select5() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<String> list = qr.query(connection, sql, new ColumnListHandler<String>("sname"));
for (String string : list) {
System.out.println(string);
}
DbUtils.closeQuietly(connection);
}
@Test
public void select6() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select avg(sid) from sort";
BigDecimal query = qr.query(connection, sql, new ScalarHandler<BigDecimal>());
System.out.println(query);
DbUtils.closeQuietly(connection);
}
@Test
public void select7() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
Map<String, Object> map = qr.query(connection, sql, new MapHandler());
for (String key : map.keySet() ) {
System.out.println(key + " " + map.get(key));
}
DbUtils.closeQuietly(connection);
}
@Test
public void select8() throws SQLException {
QueryRunner qr = new QueryRunner();
Connection connection = JDBCUtil.getConnection();
String sql = "select * from sort";
List<Map<String,Object>> list = qr.query(connection, sql, new MapListHandler());
for (Map<String, Object> map : list) {
for (String key : map.keySet()) {
System.out.println(key + " " + map.get(key));
}
}
DbUtils.closeQuietly(connection);
}
}
连接池
java为了解决频繁创建数据库连接和释放数据库连接的问题
使用数据库连接池来解决 并且出了一套规范
数据库连接规范(接口)
javax.sql.DataSource java扩展包
由数据库厂商来提供的
常用数据库连接池 DBCP C3P0
需要导入的jia包(dbcp中依赖pool jia包种的类)
commons-dbcp-1.4.jar
commons-pool-1.5.6.jar
BasicDataSource 是 DataSource(规范类) 的实现类
基础设置 (要使用数据库连接池 必须要设置的内容)
mysql驱动类 com.mysql.jdbc.Driver
访问数据库连接地址
数据库访问用户名 和 密码
public class DataSourceUtil {
private static BasicDataSource dataSource = new BasicDataSource();
static {
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/myjdbc");
dataSource.setUsername("root");
dataSource.setPassword("123456");
dataSource.setInitialSize(10);
dataSource.setMaxActive(8);
dataSource.setMaxIdle(5);
dataSource.setMinIdle(2);
}
public static DataSource getDataSource() {
return dataSource;
}
}
public static void main(String[] args) throws SQLException {
QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
String sql = "insert into sort values (null, ?, ?, ?)";
Object[] params = {"试卷", 10, "一份万分试卷"};
int row = qr.update(sql, params);
if (row > 0) {
System.out.println("插入成功");
}
}