一、取别名
SELECT A.ID,A.NAME as A_Name,B.NAME as B_Name FROM TABLEA AS A INNER JOIN TABLEB AS B ON A.ID=B.ID
String name1=rs.getString("A_NAME");
String name2=rs.getString("B_NAME");
二、ResultSet基本使用
1.直接使用get方法获取对应的类型的数据,当然需要结合while(resultset.next())
ResultSet rs = ps.executeQuery();
if(rs.next()){
employee=new Employee(rs.getLong(1),rs.getString(2),rs.getString(3),rs.getInt(4));
}
2.使用getMetaData()方法,得到表结构,包括列名,列的个数,列的数据类型。然后遍历
主要使用:列的个数,列名,通过列名可以使用resultSet getObject(name) 获取对应的值,然后通过反射得到对应的属性,通过属性给对象赋值。
ResultSetMetaData rsmd = rs.getMetaData();// 得到记录集,元素对象。
// 通过此对象可以得到表的结构,包括,列名,列的个数,列数据类型
while(rs.next()){
T m = cls.newInstance();// 通过反射得到实体对象。
for(int i=0;i<rsmd.getColumnCount();i++){
String col_name = rsmd.getCatalogName(i+1);// 获取列名
Object value = rs.getObject(col_name);//获取列对应的值。
Field field = cls.getDeclaredField(col_name);//获取对象对应的名称属性,然后给属性赋值
field.setAccessible(true);// 让可以访问私有属性
field.set(m,value);// 给对象私有属性赋值
}
list.add(m);
个人笔记:(略)
项目例子:
//从人员表中获取信息
@Override
public void getMerberAllInformation() throws Exception {
DataSet dsHr=new DataSet("???");
dsHr.beginTran();
ResultSet rs = null;
String sql="select distinct e.fcode,e.fname,s.fsex,e.ftel,e.FID_NO,d.FFULL_NAME,t.FNAME,c.FNAME,g.FNAME "
+ "from HR_EMPLOYEE e left join hr_dept d on e.FDEPT = d.FDEPT_ID "
+ "left join hr_hire_type t on e.FHIRE_TYPE = t.FCODE "
+ "left join hr_sex s on e.FSEX = s.fcode "
+ "left join hr_lend_comp c on e.FLEND_COMP = c.FID "
+ "left join HR_WORKER_SUB_GROUP g on e.FSUB_GROUP = g.FCODE "
+ "where e.FSTATUS = '1'";
rs = dsHr.executeQuery(sql);
//获取人员的所有信息
int count = rs.getMetaData().getColumnCount();
while(rs.next()) {//遍历行,next()方法返回值为Boolean,当存在数据行返回true,反之false
for(int i = 1; i <= count; i++) {//遍历列
System.out.print(rs.getString(i));
if(i < count) {
System.out.print("---");//为数据隔开美观,没实际意义
}
}
System.out.println();
}
dsHr.commit();
dsHr.close();
}
//从人员表中,根据身份证号,获取工号,姓名,性别,类型
@Override
public List getMerberInformation(String fidNo) throws Exception {
List memberInformation=new ArrayList();
DataSet dsHr=new DataSet("????");
dsHr.beginTran();
ResultSet rs = null;
String sql="select distinct e.fcode,e.fname,e.ftel,s.fsex,e.FID_NO,d.FFULL_NAME,t.FNAME,c.FNAME,g.FNAME as persontyle"
+ "from HR_EMPLOYEE e left join hr_dept d on e.FDEPT = d.FDEPT_ID "
+ "left join hr_hire_type t on e.FHIRE_TYPE = t.FCODE "
+ "left join hr_sex s on e.FSEX = s.fcode "
+ "left join hr_lend_comp c on e.FLEND_COMP = c.FID "
+ "left join HR_WORKER_SUB_GROUP g on e.FSUB_GROUP = g.FCODE "
+ "where e.FSTATUS = '1' and e.FID_NO='" + fidNo + "'";
rs = dsHr.executeQuery(sql);
while (rs.next()) {
memberInformation.add(rs.getString("fcode"));
memberInformation.add(rs.getString("fname"));
memberInformation.add(rs.getString("fsex"));
memberInformation.add(rs.getString("FFULL_NAME"));
memberInformation.add(rs.getString("ftel"));
memberInformation.add(rs.getString("persontyle"));
}
dsHr.commit();
dsHr.close();
return memberInformation;
}