java-oracle（查询，删除，修改）

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import jdbc.Person;

public class OracleDemo11 {
 private Connection conn;
 private Statement state;
 private ResultSet rs;

 public void addsqll(Person p) {
  try {
   conn = DBTools1.getConnection();
   state = conn.createStatement();
   /*String sql = "insert into person values(" + p.getId() + ","
     + p.getName() + "," + p.getAddress() + ")";*/
   String sql = "insert into person values(" + getMax()+ ","
   + p.getName() + "," + p.getAddress() + ")";
   System.out.println(sql);
   state.executeUpdate(sql);
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {

   DBTools1.closeConnection(conn);
   DBTools1.closeStatement(state);
  }

 }

 public void Update(Person p) {
  try {
   conn = DBTools1.getConnection();
   state = conn.createStatement();
   String sql = "update person set name='" + p.getName()
     + "',address='" + p.getAddress() + "'where id=" + p.getId();
   System.out.println(sql);
   state.execute(sql);
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   DBTools1.closeConnection(conn);
   DBTools1.closeStatement(state);
  }
 }

 public void deleteById(int id) {

  try {
   conn = DBTools1.getConnection();
   state = conn.createStatement();
   String sql = "delete from person where id=" + id;
   System.out.println(sql);
   state.execute(sql);
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } finally {
   DBTools1.closeConnection(conn);
   DBTools1.closeConnection(conn);
  }
 }
/*
 public String findPersonById(int id) {
  String personInfo = "";
  try {
   conn = DBTools1.getConnection();
   state = conn.createStatement();
   String sql = "select * from Person where id=" + id;
   rs = state.executeQuery(sql);
   rs.next();
   // rs.getInt(1);//传int类型进去代表是第几列（1）
   int pid = rs.getInt("id");// 代表id=1；
   String pname = rs.getString("name");
   String paddress = rs.getString("address");
   personInfo += pid + pname + paddress;
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   DBTools1.closeResultSet(rs);
   DBTools1.closeStatement(state);
   DBTools1.closeConnection(conn);
  }
  return personInfo;
 }*/
 
 public Person findPersonById1(int id){
//  String message="";
  Person p=null;
  try{
   
   conn=DBTools1.getConnection();
   state=conn.createStatement();
//   String sql="select id ii，name mm,address dd from person where id="+id;
   String sql="select * from person where id="+id;
   System.out.println(sql);
   rs=state.executeQuery(sql);
   rs.next();
   if(rs.next()){
   p=new Person();
//   int pid=rs.getInt("id");
  /* int pid=rs.getInt("id");
   String pname=rs.getString("name");
   String paddress=rs.getString("address");*/
   p.setId(rs.getInt("id"));
   p.setName(rs.getString("name"));
   p.setAddress(rs.getString("address"));
//   message=pid+pname+paddress;
   }
  }catch(Exception e){
   e.printStackTrace();
  }
  finally{
   
   DBTools1.closeResultSet(rs);
   DBTools1.closeStatement(state);
   DBTools1.closeConnection(conn);
  }
  return p;
 }
 
 public int getMax(){
 
  int a=0;
  Connection conn=null;
  Statement state=null;
  ResultSet rs=null;
  try {
   conn = DBTools1.getConnection();
   state = conn.createStatement();
   String sql = "select max(id) max from person";
   System.out.println(sql);
   rs=state.executeQuery(sql);
   if(rs.next()){
    int temp=rs.getInt("max");
    a=temp;
   }
   
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   DBTools1.closeConnection(conn);
   DBTools1.closeStatement(state);
   DBTools1.closeResultSet(rs);
  }

  return ++a;
  
 }
 //查询所有人的信息
 public ArrayList listAllPerson(){
  ArrayList al=new ArrayList();
  try{
   conn=DBTools1.getConnection();
   state=conn.createStatement();
   String sql="select * from person";
   System.out.println(sql);
   rs=state.executeQuery(sql);
   while(rs.next()){
    Person p=new Person();
    p.setId(rs.getInt("id"));
    p.setName(rs.getString("name"));
    p.setAddress(rs.getString("address"));
    al.add(p);
   }
  }catch(Exception e){
   e.printStackTrace();
  }
  
  return al;
  
 }
 
 //根据用户提供的sql语句进行查询
 public ArrayList findPersonBySql(String sql){
  ArrayList al=new ArrayList();
  try{
   conn=DBTools1.getConnection();
   state=conn.createStatement();
   System.out.println(sql);
   rs=state.executeQuery(sql);
   while(rs.next()){
    Person p=new Person();
    p.setId(rs.getInt("id"));
    p.setName(rs.getString("name"));
    p.setAddress(rs.getString("address"));
    al.add(p);
   }
  }catch(Exception e){
   e.printStackTrace();
  }
  
  return al;
  
 }
 
 
 public static void main(String[] args) {
 /* OracleDemo11 o = new OracleDemo11();
  Person maxp=new Person();
  maxp.setAddress("bn");
  maxp.setName("bq");
  o.addsqll(maxp);
  */
  
 /* OracleDemo11 ol=new OracleDemo11();
  ArrayList al=ol.listAllPerson();
  for(int i=0;i<al.size();i++){
   Object oo=al.get(i);
   Person p12=(Person)oo;
   System.out.println(p12.getName());*/ //---查询person表中所有的名字
  
  
  
  OracleDemo11 o=new OracleDemo11();
  String sql="select * from person where age>=20 and age=<20";
  o.findPersonBySql(sql);
  }
  
 }
 
//  Person pp = new Person();
//  pp.setId(101);
//  pp.setName("'dd'");
//  pp.setAddress("'hh'");
//  o.addsqll(pp);

  /*
   * o.deleteById(11); o.Update(pp);
   */
  // o.addsqll(100, "'k'", "'c'");
//  String m = o.findPersonById1(101);
  
 /* 
  Person n=o.findPersonById1(111);
  if(n!=null){
  System.out.println(n.getName());
 }
  else{
   System.out.println("查无此人！");
  }
  Person p1=new Person(1,"aa","hh");
  Person p2=new Person();
  Person p3=new Person();
  Person p4=new Person();
  Person[] pp=new Person[]{p1,p2,p3,p4};
  Person ppp=pp[2];
  //**************************集合类//
  //第一步：创建集合对象
  ArrayList a1=new ArrayList();
  //第二步：存储
  a1.add(p1);
  a1.add(p2);
  a1.add(p3);
  a1.add(p4);
  //第三步：取出对象
  Object oo=a1.get(0);//取出的下标为n-1
  //向下转型
  Person pppp=(Person) oo;
  System.out.println(pppp.getAddress());
 }

 private static Person Person() {
  // TODO Auto-generated method stub
  return null;
 }
 

}
*/