JDBC完结

一: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;

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 

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 beam=new BeanHandler(Customer.class);
	Customer query = queryRunner.query(conn, sql, beam, 6);
	System.out.println(query);
}

@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 beam=new BeanHandler(Customer.class);
	BeanListHandler beam=new BeanListHandler(Customer.class);
	List query = queryRunner.query(conn, sql, beam, 10);
	query.forEach(System.out::println);
	
}

@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 query = queryRunner.query(conn, sql, handler, 6);
	System.out.println(query);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}

@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> query = queryRunner.query(conn, sql, handler, 6);
	query.forEach(System.out::println);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}

@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;

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 

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 beam=new BeanHandler(Customer.class);
	Customer query = queryRunner.query(conn, sql, beam, 6);
	System.out.println(query);
}

@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 beam=new BeanHandler(Customer.class);
	BeanListHandler beam=new BeanListHandler(Customer.class);
	List query = queryRunner.query(conn, sql, beam, 10);
	query.forEach(System.out::println);
	
}

@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 query = queryRunner.query(conn, sql, handler, 6);
	System.out.println(query);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}

@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> query = queryRunner.query(conn, sql, handler, 6);
	query.forEach(System.out::println);
	//{name=汪峰, birth=2010-02-02, id=1, email=wf@126.com}

}

@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 {

   @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.具体做法

JDBC完结

Java相关栏目本月热门文章