java连接数据库实现查询操作

查询并接收返回结果

package ThirdPassage;
/**
 * 针对customers表的查询工作
 * @author user
 *
 */

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Properties;

import org.junit.Test;

import been.Customers;


public class Query {
	@Test
	public void test1() throws IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
		//1.读取配置信息
		InputStream inputStream=Query.class.getClassLoader().getResourceAsStream("jdbc.properties");
		Properties prop	=new Properties();
		prop.load(inputStream);
		
		
		String user=prop.getProperty("user");
		String password =prop.getProperty("password");
		String url=prop.getProperty("url");
		String driver=prop.getProperty("driver");
		
		//获取Driver实例类对象
		Class clazz	=Class.forName(driver);
		Driver driver2=	(Driver)clazz.newInstance();
		
		//注册驱动
		DriverManager.registerDriver(driver2);
		//获取连接
		Connection conn = DriverManager.getConnection(url, user, password);
		
		
		
		String sql="select id,name,email,birth from customers where id =?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, 1);
		//执行并返回结果集 
		ResultSet query = ps.executeQuery();
		//处理结果集	
		if(query.next()) {
			int id = query.getInt(1);
			String name = query.getString(2);
			String email = query.getString(3);
			Date birth = query.getDate(4);
			
			Customers customers = new Customers(id,name,email,birth);
			System.out.println(customers.toString());
			
		}
		
		
		//关闭资源
		query.close();
		conn.close();
		ps.close();
		
	
		
		
		
	}
	
	
	//针对于Customers表通用的查询操作
	
	public static Customers Qurey1(String sql,Object ...args) throws Exception {
	//连接数据库
	Connection conn	=DBUtil.getConnection();
	//预编译
	PreparedStatement ps = conn.prepareStatement(sql);
	
	
	
	//填充占位符
	for(int i=0;i<args.length;i++) {
		ps.setObject(i+1, args[i]);
		
	}
	//执行操作并返回结果
	ResultSet resultSet=	ps.executeQuery();
	//获取结果集的元数据
	ResultSetMetaData metaData = resultSet.getMetaData();
	//获取列数
	int count = metaData.getColumnCount();
	if(resultSet.next()) {
		Customers cust=new Customers();
		//处理结果集一行数据中的每一列
		for(int i=0;i<count;i++) {
			//获取列值
			Object objectValue = resultSet.getObject(i+1);
			//获取每个列的列名
			String columnName = metaData.getColumnName(i+1);
			//通过反射获取columnName属性
			Field declaredField = Customers.class.getDeclaredField(columnName);
			//避免是私有属性，设置可访问
			declaredField.setAccessible(true);
			
			declaredField.set(cust, objectValue);
		}
		return cust;
		
		
		
	}
	DBUtil.closeConnection(conn, ps);
	return null;
	
		
	}
	@Test
	public void test2() throws Exception {
		String sql="select id,name,email,birth from customers where id=?";
		Customers customers=Query.Qurey1(sql, 12);
		System.out.println(customers);
		
		
	}
	

}

可以适用于多个表数据查询的代码，只能返回一条数据

package ThirdPassage;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import been.Customers;
import been.Order;

/**
 * 使用PrepareStatement实现针对不同表的通用查询操作,返回表中的一条数据
 * @author user
 *
 */
public class Dif_Tables_21 {


	public static <T> T Query_diff(Class<T>  clazz,String sql,Object...args) throws Exception {
		Connection conn = DBUtil.getConnection();
		
		//预编译
		PreparedStatement ps = conn.prepareStatement(sql);
		
		//填充占位符
		for(int i=0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		
		//执行
		ResultSet rs = ps.executeQuery();
		//获取元素据
		ResultSetMetaData metaData = rs.getMetaData();
		//获取列数
		int columnCount = metaData.getColumnCount();
		
		if(rs.next()) {
			T t=clazz.newInstance();
			for(int i=0;i<columnCount;i++) {
				//获取每一列的值
				Object value = rs.getObject(i+1);
				//获取每一列的字段名
				String columnName = metaData.getColumnName(i+1);
				
				Field field = clazz.getDeclaredField(columnName);
				field.setAccessible(true);
				field.set(t, value);
			}
			rs.close();
			conn.close();
			ps.close();
			return t;
		}
		rs.close();
		conn.close();
		ps.close();
		return null;
		
		
		
		
		
		
		
		
		
		
		
		
	}
	public static void main(String[] args) throws Exception {
		String sql="select order_id,order_name,order_date from `order` where order_id=?";
		Order query_diff = Query_diff(Order.class, sql, 1);
		System.out.println(query_diff);
	}

}

可以适用于多个表数据查询的代码，返回多条数据

用到泛型和反射

package ThirdPassage;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import been.Customers;



public class Duo_Query {
	public static void main(String[] args) throws Exception {
		String sql="select id,name,email,birth from customers where id<?";
		List<Customers> test1 = Duo_Query.test1(Customers.class, sql, 37);
//		for (Customers customers : test1) {
//			System.out.println(customers);
//			
//		}
		test1.forEach(System.out::println);
		
		
	}
	public static <T> List<T> test1(Class<T> clazz,String sql,Object...args) throws Exception{
		ArrayList<T> arrayList = new ArrayList<T>();
		Connection conn = DBUtil.getConnection();
		//预编译
		PreparedStatement ps = conn.prepareStatement(sql);
		//填充占位符
		for(int i=0;i<args.length;i++) {
			ps.setObject(i+1, args[i]);
		}
		
		//执行并返回结果
		ResultSet rs = ps.executeQuery();
		
		//获取描述结果的元数据
		ResultSetMetaData metaData = rs.getMetaData();
		//获取元数据的列数
		int columnCount = metaData.getColumnCount();
		while(rs.next()) {
			T t=clazz.newInstance();
			
			for(int i=0;i<columnCount;i++) {
				//获取列名
				String columnName = metaData.getColumnName(i+1);
				//获取列值
				Object value = rs.getObject(i+1);
				
				
				Field field = clazz.getDeclaredField(columnName);
				field.setAccessible(true);
				field.set(t, value);
				
			}
			arrayList.add(t);
			
		}
		conn.close();
		ps.close();
		rs.close();
		return arrayList;
	}
	

	
}