JDBC完结

于 2021-10-11 23:51:58 发布
阅读量181 收藏
点赞数
文章标签： 数据库 sql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Chen_JH001/article/details/120714315
版权

一:Druid数据库连接池的使用

package Dbutils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import Bean.Customer;
import JDBCutil4.JDBCconn3;
import JDbcutil1.JDBCconn1;
/**
 * 
 *commons-dbutils 是Apache组织提供的一个开源JDBC工具类库，封装了针对了对于数据库的增删改查操作
 *增删改查操作主要用QueryRunner的对象调用QueryRunner中定义的方法
 *QueryRunner queryRunner = new QueryRunner();
 *调用对象queryRunner里面QueryRunner定义的update()方法实现增删改
 *
 * close()/closeQuitly() 实现资源关闭
 * @Description
 * @author Cjh
 * @date 2021年10月9日下午8:33:51
 *
 */
public class QuetyRunnerTest {
@Test
public void testInsert()  {
	Connection conn=null;
	try {
		QueryRunner queryRunner = new QueryRunner();
		conn = JDBCconn3.getConn();
		//增
//		String sql="insert into customers(name,email,birth)values(?,?,?)";
//		int update = queryRunner.update(conn, sql, "无话说","wuhuashuo@126.com","1997-01-01");
		//改
//		String sql="update customers set name = ? where id =?";
//		int update = queryRunner.update(conn,sql,"蔡徐坤",26);
		//删
		String sql = "delete from customers where id = ?";
		int update = queryRunner.update(conn,sql,27);
		System.out.println(update);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		JDBCconn1.closeResource(conn, null, null);
	}
	
}
@Test 
/*
 * 查询测试：
 *  
 * BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
 * 
 * */
public void testQuety() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id = ?";
	BeanHandler<Customer> beam=new BeanHandler<Customer>(Customer.class);
	Customer query = queryRunner.query(conn, sql, beam, 6);
	System.out.println(query);
}
/**
 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装背中的多条记录构成的集合
 * @throws SQLException
 */
@Test 
public void testQuety1() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
//	BeanHandler<Customer> beam=new BeanHandler<Customer>(Customer.class);
	BeanListHandler<Customer> beam=new BeanListHandler<Customer>(Customer.class);
	List<Customer> query = queryRunner.query(conn, sql, beam, 10);
	query.forEach(System.out::println);
	
}
/**
 * MapHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录以键值对的形式//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}
 * 将字段及其相应字段的值作为map中的key和value
 * @throws SQLException
 */
@Test 
public void testQuety2() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
    MapHandler handler = new MapHandler();
    Map<String, Object> query = queryRunner.query(conn, sql, handler, 6);
	System.out.println(query);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}
/**
 * MapListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录 以键值对的形式//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}.....
 * 将字段及其相应字段的值作为map中的key和value,将这些map添加到List中
 * @throws SQLException
 */
@Test 
public void testQuety3() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
    MapListHandler handler = new MapListHandler();
    List<Map<String, Object>> query = queryRunner.query(conn, sql, handler, 6);
	query.forEach(System.out::println);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}
/**
 * ScalarHandler：是ResultSetHandler接口的实现类,用于查询分组函数，即特殊需求
 * 包括查询数量，最大值，最小值平均数，和等
 * mysql中：sum(),avg(),Min(),max(),count(*),
 * @throws SQLException
 */
@Test 
public void testQuety4() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select count(*) from customers ";
	ScalarHandler handler = new ScalarHandler();
     Object query = queryRunner.query(conn, sql, handler);
	System.out.println(query);

}
//如果ResultSetHandler中的接口都不满足我们的需求
//我们可以自定义ResultSetHandler
@Test 
public void testQuety5() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id = ?";
	ResultSetOrther handler = new ResultSetOrther();
    Customer query = queryRunner.query(conn, sql, handler,6);
	System.out.println(query);

}
}

这里我们也面临着于上述的数据库连接池同样的问题：

处理：

private static DataSource data =null;
      static{
      try {
          Properties pros = new Properties();
          InputStream is = ClassLoader.getSystemClassLoader().getSystemResourceAsStream("Druid.properties");
          pros.load(is);
          //createDataSource为一个静态方法 
          data = DruidDataSourceFactory.createDataSource(pros);
    } catch (Exception e) {
        e.printStackTrace();
    }

将这段代码写到类中而不是方法中，从而解决调用时创建多个数据库池子

用Druid数据库连接池提供链接：

package Druid;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.junit.Test;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mysql.jdbc.PreparedStatement;

public class DruidTest {
	  private static DataSource data =null;
	  static{
      try {
		  Properties pros = new Properties();
		  InputStream is = ClassLoader.getSystemClassLoader().getSystemResourceAsStream("Druid.properties");
		  pros.load(is);
		  //createDataSource为一个静态方法 
		  data = DruidDataSourceFactory.createDataSource(pros);
	} catch (Exception e) {
		e.printStackTrace();
	}
	  }
	  @Test
  public static void getConnection() throws Exception {	
	//方一：
//	  DruidDataSource source1 = new DruidDataSource();
//	  source1.setUrl(null);.....
	  //方式二:
//	  DruidDataSourceFactory source = new DruidDataSourceFactory();
//	  Properties pros = new Properties();
//	  InputStream is = ClassLoader.getSystemClassLoader().getSystemResourceAsStream("Druid.properties");
//	  pros.load(is);
//	  DataSource data = source.createDataSource(pros);
	  Connection conn = data.getConnection();
	  
	  System.out.println(conn);
  }
	  //用dbutils.jar中提供的工具类实现资源的关闭 closeQuietly()/close()
	  public static void closeResourse1(Connection conn,Statement ps,ResultSet rs) {
		  try {
			DbUtils.close(conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  try {
			DbUtils.close(ps);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		  try {
			DbUtils.close(rs);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	  }
	  public static void closeResourse2(Connection conn,Statement ps,ResultSet rs) {
		  DbUtils.closeQuietly(conn);
		  DbUtils.closeQuietly(ps);
		  DbUtils.closeQuietly(rs);
	  }
}

 

二.Dbutils工具类的使用 :

package Dbutils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

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.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import Bean.Customer;
import JDBCutil4.JDBCconn3;
import JDbcutil1.JDBCconn1;
/**
 * 
 *commons-dbutils 是Apache组织提供的一个开源JDBC工具类库，封装了针对了对于数据库的增删改查操作
 *增删改查操作主要用QueryRunner的对象调用QueryRunner中定义的方法
 *QueryRunner queryRunner = new QueryRunner();
 *调用对象queryRunner里面QueryRunner定义的update()方法实现增删改
 *
 * close()/closeQuitly() 实现资源关闭
 * @Description
 * @author Cjh
 * @date 2021年10月9日下午8:33:51
 *
 */
public class QuetyRunnerTest {
@Test
public void testInsert()  {
	Connection conn=null;
	try {
		QueryRunner queryRunner = new QueryRunner();
		conn = JDBCconn3.getConn();
		//增
//		String sql="insert into customers(name,email,birth)values(?,?,?)";
//		int update = queryRunner.update(conn, sql, "无话说","wuhuashuo@126.com","1997-01-01");
		//改
//		String sql="update customers set name = ? where id =?";
//		int update = queryRunner.update(conn,sql,"蔡徐坤",26);
		//删
		String sql = "delete from customers where id = ?";
		int update = queryRunner.update(conn,sql,27);
		System.out.println(update);
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		JDBCconn1.closeResource(conn, null, null);
	}
	
}
@Test 
/*
 * 查询测试：
 *  
 * BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录
 * 
 * */
public void testQuety() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id = ?";
	BeanHandler<Customer> beam=new BeanHandler<Customer>(Customer.class);
	Customer query = queryRunner.query(conn, sql, beam, 6);
	System.out.println(query);
}
/**
 * BeanListHandler:是ResultSetHandler接口的实现类，用于封装背中的多条记录构成的集合
 * @throws SQLException
 */
@Test 
public void testQuety1() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
//	BeanHandler<Customer> beam=new BeanHandler<Customer>(Customer.class);
	BeanListHandler<Customer> beam=new BeanListHandler<Customer>(Customer.class);
	List<Customer> query = queryRunner.query(conn, sql, beam, 10);
	query.forEach(System.out::println);
	
}
/**
 * MapHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录以键值对的形式//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}
 * 将字段及其相应字段的值作为map中的key和value
 * @throws SQLException
 */
@Test 
public void testQuety2() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
    MapHandler handler = new MapHandler();
    Map<String, Object> query = queryRunner.query(conn, sql, handler, 6);
	System.out.println(query);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}
/**
 * MapListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录 以键值对的形式//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}.....
 * 将字段及其相应字段的值作为map中的key和value,将这些map添加到List中
 * @throws SQLException
 */
@Test 
public void testQuety3() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id <= ?";
    MapListHandler handler = new MapListHandler();
    List<Map<String, Object>> query = queryRunner.query(conn, sql, handler, 6);
	query.forEach(System.out::println);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}
/**
 * ScalarHandler：是ResultSetHandler接口的实现类,用于查询分组函数，即特殊需求
 * 包括查询数量，最大值，最小值平均数，和等
 * mysql中：sum(),avg(),Min(),max(),count(*),
 * @throws SQLException
 */
@Test 
public void testQuety4() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select count(*) from customers ";
	ScalarHandler handler = new ScalarHandler();
     Object query = queryRunner.query(conn, sql, handler);
	System.out.println(query);

}
//如果ResultSetHandler中的接口都不满足我们的需求
//我们可以自定义ResultSetHandler
@Test 
public void testQuety5() throws SQLException {
	QueryRunner queryRunner = new QueryRunner();
	Connection conn = JDBCconn3.getConn();
	String sql="select name,email,id,birth from customers where id = ?";
	ResultSetOrther handler = new ResultSetOrther();
    Customer query = queryRunner.query(conn, sql, handler,6);
	System.out.println(query);

}
}

自定义ResultHandler类:以Customers类为例子

package Dbutils;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbutils.ResultSetHandler;

import Bean.Customer;

public class ResultSetOrther implements ResultSetHandler<Customer> {

    @Override
    public Customer handle(ResultSet rs) throws SQLException {
        if(rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String email = rs.getString("email");
            Date birth = rs.getDate("birth");
            return new Customer(id, name, email, birth);
        }
        return null;
    }

}
 

总结：

1.字符串拼串写法

 

2.处理Blob

 

 

3.Preparestatement于Statement

 

4.事务

5.具体做法 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Hyong~~

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值