JDBC 针对数据库表的查询操作(1)

1、针对数据库表的查询操作（推荐2）

1、以数据库表customers为例

2、把要获取的数据字段封装在一个类的对象中（Customer）

package com.atguigu3.bean;

import java.sql.Date;

/*
 * ORM编程思想(object relational mapping)：
 * 一个数据表对应一个Java类
 * 表中的一记录对应Java类的一个对象
 * 表中的一个字段对应Java类的一个属性
 * 
 */
public class Customer {
	
	private int id;
	private String name;
	private String email;
	private Date birth;
	
	public Customer() {
		super();		
	}

	public Customer(int id, String name, String email, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.birth = birth;
	}

	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getBirth() {
		return birth;
	}

	public void setBirth(Date birth) {
		this.birth = birth;
	}
	
	
	
}

3、针对于customers表创建一个类（CustomerForQuery）进行查询操作

package com.atguigu2.preparedstatement.crud;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.atguigu3.bean.Customer;
import com.atguigu3.util.JDBCUtils;
/*
 * 针对于Customers表的查询操作
 * 
 */
public class CustomerForQuery {
@Test
public void testQuery1()  {
	Connection conn=null;
	PreparedStatement ps=null;	
	ResultSet resultSet=null;
	try {
		conn = JDBCUtils.getConnectio();
		String sql="select id,name,email,birth from customers where id=?";
		ps = conn.prepareStatement(sql);
		ps.setObject(1,1);
		
		//执行 并返回结果集
		resultSet = ps.executeQuery();
		//处理结果集
		if (resultSet.next()) {// next():判断结果集的下一条是否有数据，如果有数据返回true,并指针下移；如果返回false,指针不会下移
			
			//获取当前这条数据各个字段值
			int id = resultSet.getInt(1);
			String name = resultSet.getString(2);
			String email = resultSet.getString(3);
			Date birth = resultSet.getDate(4);
			
			//方式一：
//		System.out.println("id="+id+",name="+name+",email="+email+",birth="+birth);
			
			//方式二：
//		Object[] data=new Object[] {id,name,email,birth};
			
			//方式三：将数据封装为一个对象
			Customer customer = new Customer(id,name,email,birth);
			System.out.println(customer);		
		}	
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		//关闭资源
		JDBCUtils.closeResource(conn, ps,resultSet);
	}
	
}
}

4、查询结果

2、针对数据库表的通用查询操作

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.junit.Test;
import com.atguigu3.bean.Customer;
import com.atguigu3.util.JDBCUtils;


/*
 * 针对于Customers表的查询操作
 * 
 */
public class CustomerForQuery {
	@Test
	public void testqueryForQueryCustomers() {
		String sql="select id,name,birth,email from customers where id=?";
		Customer customer = queryForQueryCustomers(sql,13);
		System.out.println(customer);
		
		sql="select name,email from customers where name=?";
		Customer customer1 = queryForQueryCustomers(sql,"周杰伦");
		System.out.println(customer1);
	}
	/*
	 * 针对于customers表的通用查询操作
	 */
	public Customer queryForQueryCustomers(String sql,Object...args)  {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn = JDBCUtils.getConnectio();
			
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i+1,args[i]);
			}
			
			rs = ps.executeQuery();
			//获取结果集的元数据 
			ResultSetMetaData rsmd = rs.getMetaData();
			//通过ResultSetMetaData获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			
			if(rs.next()) {
				Customer cust=new Customer();
				//处理结果集一行数据中的每一个列
				for (int i = 0; i < columnCount; i++) {
					//获取列值
					Object columValue = rs.getObject(i+1);
					
					//获取每个列的列名
					String columnName = rsmd.getColumnName(i+1);
					
					//给cust对象指定的columnName属性，赋值为columValue,通过反射
					Field field=Customer.class.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(cust, columValue);
				}
				return cust;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtils.closeResource(conn, ps,rs);

		}
		return null;
	}
	}
	

2.查询结果