2. 10 接口自动化 ---- 数据库连接 与查询（DBUtils使用）

一、 前提了解

1.  JDBC 驱动程序

    JDBC(java database connectivity)驱动程序是对 JDBC 规范完整的实现，它的存在在 Java 程序与数据库系统之间建立了一条通信的渠道

2.  Maven坐标

   <!-- mysql数据库驱动 -->
   <dependency>
	    <groupId>mysql</groupId>
	    <artifactId>mysql-connector-java</artifactId>
	    <version>8.0.21</version>
   </dependency>
     
    <!-- 数据库链接工具包 -->
   <dependency>
	    <groupId>commons-dbutils</groupId>
	    <artifactId>commons-dbutils</artifactId>
	    <version>1.6</version>
   </dependency>

3. DBUtils介绍

DBUtils是java编程中的数据库操作实用工具，小巧简单实用。DBUtils封装了对JDBC的操作，简化了JDBC操作，可以少写代码。Dbutils三个核心功能介绍

1>. QueryRunner中提供对sql语句（insert，update，delete）操作的API; ---- 核心类
2>. ResultSetHandler接口，用于定义select操作后，怎样封装结果集.;
3>. DbUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法                                     

 ResultSetHandler结果集处理类如下：

二 、代码实现 

1.  数据库连接

public class JDBCUtils {

	public static Connection getConnection() {
		//定义数据库连接对象
		Connection conn = null;
		try {
			//你导入的数据库驱动包， mysql。
			conn = DriverManager.
					getConnection(Constants.JDBC_URL, Constants.JDBC_USER,Constants.JDBC_PWD);
		}catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
}

2. 数据查询

1)    单条查询 :  new ArrayHandle

QueryRunner qr=new  QueryRunner(JDBCUtils.getConnection())
String sql = "SELECT *FROM member a LIMIT 2;";
Object[] query = qr.query(conn, sql, new ArrayHandler());
for(Object obj:query) {
    System.out.println(obj);}

2）   单条数据查询：new BeanHandle<>()

    a.  先定义一个实体类：  

package com.lujier.pojo;

import java.math.BigDecimal;
import java.sql.Timestamp;

import org.apache.poi.hpsf.Decimal;

public class Member {
	private int id;
	private String reg_name;
	private String mobile_phone; 
	private BigDecimal leave_amount;
	private Timestamp reg_time;
	private String pwd;
	private int type;
	
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public int getType() {
		return type;
	}
	public void setType(int type) {
		this.type = type;
	}

	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getReg_name() {
		return reg_name;
	}
	public void setReg_name(String reg_name) {
		this.reg_name = reg_name;
	}
	public String getMobile_phone() {
		return mobile_phone;
	}
	public void setMobile_phone(String mobile_phone) {
		this.mobile_phone = mobile_phone;
	}
	public BigDecimal getLeave_amount() {
		return leave_amount;
	}
	public void setLeave_amount(BigDecimal leave_amount) {
		this.leave_amount = leave_amount;
	}
	public Timestamp getReg_time() {
		return reg_time;
	}
	public void setReg_time(Timestamp reg_time) {
		this.reg_time = reg_time;
	}
	public Member() {
		super();
	}
	public Member(int id, String reg_name, String mobile_phone, BigDecimal leave_amount, Timestamp reg_time, String pwd,
			int type) {
		super();
		this.id = id;
		this.reg_name = reg_name;
		this.mobile_phone = mobile_phone;
		this.leave_amount = leave_amount;
		this.reg_time = reg_time;
		this.pwd = pwd;
		this.type = type;
	}
	@Override
	public String toString() {
		return "Member [id=" + id + ", reg_name=" + reg_name + ", mobile_phone=" + mobile_phone + ", leave_amount="
				+ leave_amount + ", reg_time=" + reg_time + ", pwd=" + pwd + ", type=" + type + "]";
	}
	
}

b.  new BeanHandler

QueryRunner qr=new  QueryRunner(JDBCUtils.getConnection())
String sql = "SELECT *FROM member a LIMIT 2;";
Member mem = qr.query(conn, sql,
   	    		new BeanHandler<Member>(Member.class))
System.out.println(mem);

c. 结果

3）  单字段值查询： ScalarHandle

QueryRunner qr = new QueryRunner(JDBCUtils.getConnection());
String sql1 = "SELECT reg_name FROM member a WHERE a.id=10;";
String  name = qr.query(conn, sql1, new ScalarHandler<String>());
System.out.println("name :" + name);

 

4） 多条数据查询： new ArrayListHandler()

Connection conn = JDBCUtils.getConnection();
       QueryRunner qr=new  QueryRunner();
       String sql = "SELECT *FROM member a LIMIT 2;";
       try {
           List<Object[]> students =  qr.query(conn, sql, new ArrayListHandler());
    	   for(Object[] student:students) {
    		  System.out.print("学生信息 " + student+":");
    		    for (Object one:student) {
    		    	System.out.print (","+one);
    		    }
  		      System.out.println()；

    	  }

       } catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	    }

 

5） 多条数据查询： new BeanListHandler<Member>(Member.class)

 Connection conn = JDBCUtils.getConnection();
       QueryRunner qr=new  QueryRunner();
       String sql = "SELECT *FROM member a LIMIT 2;";
       try {
           List<Member> mems = qr.query(conn, sql,
  	    		new BeanListHandler<Member>(Member.class));
    	   for(Member mem:mems) {
    		    System.out.println(mem);

    	   }

       } catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	    }
       

 

 

目前自动化中，最常用的也就是这几种方法，其他方法可以自行练习尝试