【代码仓库】sql数据库查询【增、改、查、删】通用写法

package functionClass;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;

public class baseFunction {
    public static Connection co;
    public static Statement conn;
    public boolean connect(){
        String address = "jdbc:derby://localhost:1527/DBDB";
        try
        {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            co  = DriverManager.getConnection(address,"DBDB","DBDB");
            conn = co.createStatement();
            return true ;
        }catch(SQLException s){ System.out.println("SQL Error : " + s.toString() );  }
        catch (Exception e){System.out.println("Error :" + e.toString());    }  
        return false;
    }
    public boolean disconnect(){
        try
        {
            conn.close();
            return true ;
        }catch(SQLException s){ System.out.println("SQL Error : " + s.toString() );  }
        catch (Exception e){System.out.println("Error :" + e.toString());    }  
        return false;
    }

    public class Model{
        String string[]=new String[11];//string[0]不用
    }
    public int zeng(String biaoming,int num,Model model)throws Exception{
        String str1="insert into "+biaoming+" values ";
        String str2="(?";    for(int i=1;i<num;i++){str2=str2+",?";        }    str2=str2+")";
        String  sql1=str1+str2;
        PreparedStatement pre=co.prepareStatement(sql1);
        for(int i=1;i<=num;i++){pre.setString(i,model.string[i]);}
        return pre.executeUpdate();
    }
    public int gai(String biaoming,int neirongPairs,Model neirong,int tiaojianPiars,Model tiaojian)throws Exception{
        String str1="update "+biaoming+" ";
        String str2="set "+neirong.string[1]+" = ? ";for(int i=1;i<neirongPairs;i++){str2=str2+", "+neirong.string[i*2+1]+" = ? ";}
        String str3="where "+tiaojian.string[1]+" = ? ";for(int i =1;i<tiaojianPiars;i++){str3=str3+" and "+tiaojian.string[i*2+1]+" = ? ";}
        String sql2=str1+str2+str3;//String sql2="update xueshengInf set mima='222' where xuehao='111' ";  
        PreparedStatement pre=co.prepareStatement(sql2);
        for(int i=1;i<=neirongPairs;i++){pre.setString(i, neirong.string[i*2]);}
        for(int i=1;i<=tiaojianPiars;i++){pre.setString(neirongPairs+i,tiaojian.string[i*2]);}
        
        return pre.executeUpdate();
    }
    public int chaLines(String biaoming)throws Exception{
        String sql3="select * from "+biaoming;
        PreparedStatement pre=co.prepareStatement(sql3);
        ResultSet result=pre.executeQuery();
        int count=0;while(result.next()){++count;}; 
        return count;
    }
    public ArrayList<Model> cha(String biaoming,int tiaojianPairs,Model model)throws Exception{
        String str1="select * from "+biaoming+" ";
        String str2="where "+model.string[1]+" = ? ";for(int i=1;i<tiaojianPairs;i++){str2=str2+"and "+model.string[i*2+1]+" = ?";}
        String sql3;//String sql3="select * from xueshengInf";
        if(tiaojianPairs==0){sql3=str1;}
        else sql3=str1+str2;
        PreparedStatement pre=co.prepareStatement(sql3);
        for(int i=1;i<=tiaojianPairs;i++){pre.setString(i,model.string[i*2]);}
        ResultSet result=pre.executeQuery();

        
        ResultSetMetaData rsmd = result.getMetaData() ;
        int columns=rsmd.getColumnCount();//获取列数
        ArrayList<Model> model_list = new ArrayList<Model>();//System.out.println( "查"+  result.getString("xuehao")+result.getString("mima") );
        while(result.next()){
            Model mode =new Model();
            for(int i=1;i<=columns;i++){mode.string[i]=result.getString(i);        }
            model_list.add(mode);
        }
        return model_list;
    }
    public int shan(String biaoming,int tiaojianPairs,Model model)throws Exception{
        String str1="delete from "+biaoming+" ";
        String str2="where "+model.string[1]+" = ? ";for(int i=1;i<tiaojianPairs;i++){str2=str2+"and "+model.string[i*2+1]+" = ? ";}
        String sql4=str1+str2;//String sql4="delete from xueshengInf where xuehao='111'"; 
        PreparedStatement pre=co.prepareStatement(sql4);
        for(int i=1;i<=tiaojianPairs;i++){pre.setString(i,model.string[i*2]);}
        return pre.executeUpdate();
    }
}

【注：‘’查 ‘’ 功能读取ArrayList的配套代码】

        System.out.println("connect:"+ basef.connect() );
        System.out.println("cha :"+basef.cha("xueshengInf",2,model2) );
        ArrayList<baseFunction.Model> model_list= new ArrayList<baseFunction.Model>();
        model_list=basef.cha("xueshengInf",0,model2);
        
        for(int k=0;k<model_list.size();k++){
            model1=model_list.get(k);
            for(int i=0;i<10;i++)System.out.print(model1.string[i]+"  ");
            System.out.println();
        }