JDBC中典型的五种查询方式

//第一种查询方式,返回一个ArrayList集合,集合里面的数据类型只能为Empinfo类类型
public ArrayList<Empinfo> select01(){
ArrayList<Empinfo> list = new ArrayList<Empinfo>();//定义一个类类型的集合
conn = DBHelper.getConnection();//获取数据库连接
String sql = "select * from empinfo";//查询语句
try{
stmt = conn.createStatement();//创建Statement对象
rs = stmt.executeQuery(sql);//执行sql语句
while(rs.next()){
Empinfo e = new Empinfo();//创建一个Empinfo类对象，将从数据库当中获取到的值赋给对应的成员变量
e.setUid(rs.getInt("uid"));//或者为e.setUid(rs.getInt(1));
e.setDid(rs.getInt(2));//2表示数据库表当中的第2列,即name列
e.setName(rs.getString(3));
e.setSex(rs.getString("sex"));
e.setSalary(rs.getString(5));
list.add(e);
}
// for(int i = 0; i < list.size(); i++){
// System.out.println(list.get(i));//打印出集合里面的数据,一般用在外部调用方法中
// }
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
try{
if(rs!=null){
rs.close();
rs = null;
}
if(stmt!=null){
stmt.close();
stmt = null;
}
}catch(Exception e){
e.printStackTrace();
}
}
}//
//第二种查询方式,返回一个Vector集合,集合里面数据类型为Empinfo,带条件查询的单表查询
public Vector<Empinfo> select02(int did,String sex){
Vector<Empinfo> v = new Vector<Empinfo>();
conn = DBHelper.getConnection();
String sql = "select * from empinfo where did = ? and sex = ?";
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, did);//设置第一个问号的值
pstmt.setString(2, sex);//设置第二个问号的值
rs = pstmt.executeQuery();
while(rs.next()){
Empinfo e = new Empinfo();//创建一个Empinfo类对象，将从数据库当中获取到的值赋给对应的成员变量
e.setUid(rs.getInt(1));//或者为e.setUid(rs.getInt(1));
e.setDid(rs.getInt(2));//2表示数据库表当中的第2列,即name列
e.setName(rs.getString(3));
e.setSex(rs.getString(4));
e.setSalary(rs.getString(5));
v.add(e);
}
for(int i = 0; i < v.size(); i++){
System.out.println(v.get(i));
}
return v;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
try{
if(rs!=null){
rs.close();
rs = null;
}
if(pstmt!=null){
pstmt.close();
pstmt = null;
}
}catch(Exception e){
e.printStackTrace();
}
}
}//
//第三种查询方式,返回一个Object类型,多表查询的结果
public Vector<Object> select03(String sql){
Vector<Object> v = new Vector<Object>();
conn = DBHelper.getConnection();
try{
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next()){
Empinfo e = new Empinfo();
Departinfo d = new Departinfo();
e.setUid(rs.getInt(1));
e.setName(rs.getString(3));
e.setDid(rs.getInt(2));
e.setSex(rs.getString(4));
e.setSalary(rs.getString(5));
d.setDid(rs.getInt(6));
d.setName(rs.getString(7));
v.add(e);
v.add(d);
}
for(int i = 0; i < v.size(); i++){
System.out.println(v.get(i));
}
return v;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
try{
if(rs!=null){
rs.close();
rs = null;
}
if(pstmt!=null){
pstmt.close();
pstmt = null;
}
}catch(Exception e){
e.printStackTrace();
}
}
}//
//第四种查询方式,返回一个二维集合Vector<Vector<String>>,相当于一个二维数组,可以取出每一条里面每一个字段的值
public Vector<Vector<String>> select04(int did){
Vector<Vector<String>> v = new Vector<Vector<String>>();
Vector<String> v1 = new Vector<String>();
conn = DBHelper.getConnection();
String sql = "SELECT e.uid,d.name,e.name,e.sex,e.salary FROM" +
" empinfo e,departinfo d  WHERE e.did = d.did and d.did = ?";
try{
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,did);
rs = pstmt.executeQuery();
rsmd = rs.getMetaData();
for(int i = 0;i < rsmd.getColumnCount();i++){
v1.add(rsmd.getColumnName(i+1));     //取出列名
}
v.add(v1); //将列名这一行加入到大集合当中
while(rs.next()){
Vector<String> v2 = new Vector<String>();
for(int i = 0;i < rsmd.getColumnCount();i++){
v2.add(rs.getString(i+1));      //取出每一列对应的值
}
v.add(v2); //将每一行的值加入到大集合当中
}
for(int i = 0; i < v.size(); i++){
System.out.println(v.get(i));
}
return v;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
try{
if(rs!=null){
rs.close();
rs = null;
}
if(pstmt!=null){
pstmt.close();
pstmt = null;
}
}catch(Exception e){
e.printStackTrace();
}
}
}//
//第五种查询方式,模糊查询,返回一个集合ArrayList<ArrayList<String>>
public ArrayList<ArrayList<String>> select05(String[] s){
ArrayList<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
ArrayList<String> list01 = new ArrayList<String>();
conn = DBHelper.getConnection();
String uid = s[0];
String did = s[1];
String name = s[2];
String sex = s[3];
String salary_min = s[4];
String salary_max = s[5];
if(uid.equals("")){
uid="%";
}
if(did.equals("")){
did="%";
}
if(name.equals("")){
name="%";
}
if(sex.equals("不限")){
sex="%";
}
if(salary_min.equals("")){
salary_min="0";
}
if(salary_max.equals("")){
salary_max="999999999";
}
String sql = "select * from empinfo e, departinfo d where e.uid like '"+uid+"' and " +
" e.did like '"+did+"' and e.name like '%"+name+"%' and sex like '"+sex+"'" +
"and e.salary>'"+salary_min+"' and e.salary<'"+salary_max+"' " +
" and e.did=d.did order by e.uid";
try{
pstmt = conn.prepareStatement(sql);
//pstmt.setInt(1,did);
rs = pstmt.executeQuery();
rsmd = rs.getMetaData();
for(int i = 0;i < rsmd.getColumnCount();i++){
list01.add(rsmd.getColumnName(i+1));     //取出列名
}
list.add(list01);//将列名这一行加入到大集合当中
while(rs.next()){
ArrayList<String> v2 = new ArrayList<String>();
for(int i = 0;i < rsmd.getColumnCount();i++){
v2.add(rs.getString(i+1));      //取出每一列对应的值
}
list.add(v2); //将每一行的值加入到大集合当中
}
for(int i = 0; i < list.size(); i++){
System.out.println(list.get(i));
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
try{
if(rs!=null){
rs.close();
rs = null;
}
if(pstmt!=null){
pstmt.close();
pstmt = null;
}
}catch(Exception e){
e.printStackTrace();
}
}
}
public static void main(String[] args) {
Test02 t = new Test02();
t.select05(new String[]{"","","","不限","",""});
}