JDBC - 学习4 -PreparedStatement - 查询

 

  oracle中的示例表Emp表抽象 – JavaBean

给下面内容的使用

public class Emp {
	Integer mgr;
	Integer empno;
	String ename;
	String job;
	Date hiredate;
	Integer sal;
	Integer comm;
	Integer deptno;
	public Emp(Integer mgr, Integer empno, String ename, String job, Date hiredate, Integer sal, Integer comm,
			Integer deptno) {
		super();
		this.mgr = mgr;
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}
	public Emp() {
		super();
	}
	public Integer getMgr() {
		return mgr;
	}
	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}
	public Integer getEmpno() {
		return empno;
	}
	public void setEmpno(Integer empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public Integer getSal() {
		return sal;
	}
	public void setSal(Integer sal) {
		this.sal = sal;
	}
	public Integer getComm() {
		return comm;
	}
	public void setComm(Integer comm) {
		this.comm = comm;
	}
	public Integer getDeptno() {
		return deptno;
	}
	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}
	@Override
	public String toString() {
		return "Emp [mgr=" + mgr + ", empno=" + empno + ", ename=" + ename + ", job=" + job + ", hiredate=" + hiredate
				+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}
}

 
 

1. 针对一个表

我以Emp表来举例 - 就是上面Emp类

	public List<Emp> select(String sql, Object... args) {

		Connection conn = null;

		PreparedStatement ps = null;

		ResultSet rs = null;

		List<Emp> empList = new ArrayList<Emp>();
		try {
			
			// 1. 获取数据库的Connection连接
			conn = ConnectionTest.getConnection5();
			
			// 2. 预编译SQL语句、并填充占位符
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			
			// 3. 执行SQL语句
			rs = ps.executeQuery();
			
			// 4. 获取得多的结果集的一些属性
			ResultSetMetaData rsmd = rs.getMetaData();
			int columns = rsmd.getColumnCount();
			
			// 5. 得到的每个结果转变成 Emp对象，并将其放入List容器中
			while (rs.next()) {
				Emp emp = new Emp();
				for (int i = 0; i < columns; i++) {
					Object value = rs.getObject(i + 1);
					
					// oracle的number类型，java获取的是 BigDecimal类型，故需强转
					if (value instanceof BigDecimal)
						value = ((BigDecimal) value).intValue();
					
					// 获取列的别名，如果没有列别名，则就是表定义时的列名  ---  oracle获取的列名全都是大写 -- 故需转小写
					String columnName = rsmd.getColumnLabel(i + 1);
					Field field = Emp.class.getDeclaredField(columnName.toLowerCase());
					
					// 获取对象字段的修改权限
					field.setAccessible(true);
					field.set(emp, value);
				}
				empList.add(emp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 6. 关闭资源
			ConnectionTest.closeResource(conn, ps, rs);
		}

		return empList;
	}

 

2. 针对不同表 - 不考虑事务

public <T> List<T> select(Class<T> type, String sql, Object... args) {

		Connection conn = null;
		PreparedStatement ps = null;
		List<T> list = null;
		ResultSet rs = null;
		try {
			conn = ConnectionTest.getConnection5();

			ps = conn.prepareStatement(sql);

			list = new ArrayList<T>();

			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();

			while (rs.next()) {
				T t = type.newInstance();

				for (int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					
					// oracle的number类型返回的是 BigDecimal -- 故需要转型
					if (columnValue instanceof BigDecimal)
						columnValue = ((BigDecimal) columnValue).intValue();
					String columnName = rsmd.getColumnLabel(i + 1).toLowerCase();
					Field field = type.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnValue);
				}

				list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ConnectionTest.closeResource(conn, ps, rs);
		}

		return list;
}

 
 

3. 针对不同表 - 考虑事务 — 建议使用

public <T> List<T> select(Connection conn, Class<T> type, String sql, Object...args)  {
		
		PreparedStatement ps = null;
		List<T> list = new ArrayList<T>();
		ResultSet rs = null;
		try {
			// 1. 编译SQL语句
			ps = conn.prepareStatement(sql);
			
			// 2. 填充SQL语句的占位符
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);
			}
			
			// 3.执行SQL语句、并获取结果集的一些信息
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int column = rsmd.getColumnCount();  // 获取结果集属性列列数
			
			// 4. 将结果集每个行记录转为 Class的实例，并将其放入List容器中
			while(rs.next()) {
				T t = type.newInstance();
				for(int i = 0; i < column; i++) {
					String fieldName = rsmd.getColumnLabel(i+1).toLowerCase();
					Field field = type.getDeclaredField(fieldName);
					Object fieldValue = rs.getObject(i+1);
					
					field.setAccessible(true);
					
					if(fieldValue instanceof BigDecimal)
						fieldValue = ((BigDecimal)fieldValue).intValue();
					
					field.set(t, fieldValue);
					
				}
				
				list.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 5. 关闭资源
			ConnectionTest.closeResource(null, ps, rs);
		}
		return list;
}