每日博客

package bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bean.info;
import bean.db;



public class dao {
    
    info activity = new info();

    public  ArrayList<info> select(String nameOfhouseholder){//查找
        Connection con=null;
        PreparedStatement psts=null;
        ResultSet rs=null;
        ArrayList<info> ll=new ArrayList<info>();
        try {
            con=db.getCon();
            String sql="select * from population where nameOfhouseholder=?";
            psts=con.prepareStatement(sql);
            psts.setString(1, nameOfhouseholder);
            rs=psts.executeQuery();
            if(rs.next()){
                String residenceType = rs.getString("residenceType");
                String housingType=rs.getString("housingType");
                String housingArea=rs.getString("housingArea");
                String numberOfrooms=rs.getString("numberOfrooms");
                String idCard=rs.getString("idCard");
                String sex=rs.getString("sex");
                String nation=rs.getString("nation");
                String education=rs.getString("education");
                
                info activity=new info(residenceType,housingType,housingArea,numberOfrooms,nameOfhouseholder,idCard,sex,nation,education);
                ll.add(activity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return ll;
    }
    

    //根据名称进行查询
        @SuppressWarnings("static-access")
        public static info selectName(String nameOfhouseholder) {
            //String tablename = "population";
            System.out.println("select info where nameOfhouseholder = "+nameOfhouseholder);
            info info=null;
            db db=new db();
            Connection con = db.getCon();
            try {
                Statement stm = con.createStatement();
                
                ResultSet rs = stm.executeQuery("select * from population where nameOfhouseholder='" + nameOfhouseholder + "'");
                
                if(rs.next())
                {
                    info = new info();
                    System.out.println("select the info from mysql");
                    info.setResidenceType(rs.getString("residenceType"));
                    info.setHousingType(rs.getString("housingType"));
                    info.setHousingArea(rs.getString("housingArea"));
                    info.setNumberOfrooms(rs.getString("numberOfrooms"));
                    info.setNameOfhouseholder(rs.getString("nameOfhouseholder"));
                    info.setIdCard(rs.getString("idCard"));
                    info.setSex(rs.getString("sex"));
                    info.setNation(rs.getString("nation"));
                    info.setEducation(rs.getString("education"));
                    
                    System.out.println("name of the info is "+rs.getString("nameOfhouseholder"));
                }
                rs.close();
                stm.close();
                con.close();
                //db.close(rs,stm, con);
            }catch(Exception e) {
                e.printStackTrace();
            }
            return info;
        }
    
    public   int add(info activity){
        Connection con=null;
        PreparedStatement psts=null;
        int a=0;
        try {
            con=db.getCon();
            String sql="insert into population(residenceType,housingType,housingArea,numberOfrooms,nameOfhouseholder,idCard,sex,nation,education) values(?,?,?,?,?,?,?,?,?)";
            psts=con.prepareStatement(sql);
            psts.setString(1, activity.getResidenceType());
            psts.setString(2, activity.getHousingType());
            psts.setString(3, activity.getHousingArea());
            psts.setString(4, activity.getNumberOfrooms());
            psts.setString(5, activity.getNameOfhouseholder());
            psts.setString(6, activity.getIdCard());
            psts.setString(7, activity.getSex());
            psts.setString(8, activity.getNation());
            psts.setString(9, activity.getEducation());
            
            a=psts.executeUpdate();
            psts.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return a;
    
    }
    public  int delete(String nameOfhouseholder){
        Connection con=null;
        PreparedStatement psts=null;
        int a=0;
        
        try {
            con=db.getCon();
            String sql="delete from population where idCard=?";
            psts=con.prepareStatement(sql);
            psts.setString(1, nameOfhouseholder);
            a=psts.executeUpdate();//记录影响的数据的数量
            psts.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return a;
    }    
    public int change(info activity){//修改
        Connection con=null;
        PreparedStatement psts=null;
        int a=0;
        
        try {
            con=db.getCon();
            String sql="update population set residenceType=?housingType=?,housingArea=?,numberOfrooms=?,idCard=?,sex=?,nation=?,education=? where nameOfhouseholder=?";
            psts=con.prepareStatement(sql);
            psts.setString(9, activity.getNameOfhouseholder());
            psts.setString(1, activity.getResidenceType());
            psts.setString(2, activity.getHousingType());
            psts.setString(3, activity.getHousingArea());
            psts.setString(4, activity.getNumberOfrooms());
            psts.setString(5, activity.getIdCard());
            psts.setString(6, activity.getSex());
            psts.setString(7, activity.getNation());
            psts.setString(8, activity.getEducation());
            
            a=psts.executeUpdate();
            psts.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        
        return a;
    }
    
    
    
    @SuppressWarnings("static-access")
    public boolean update(info activity){
        Connection con=null;
        PreparedStatement psts=null;
        int a=0;
        //Connection con=null;
        //PreparedStatement psts=null;
        //db db=new db();
        try {
            con=db.getCon();
            String sql="update population set idCard=?,sex=?,nation=?,education=? where nameOfhouseholder=?";
            
            psts=con.prepareStatement(sql);
            psts.setString(5, activity.getNameOfhouseholder());
            psts.setString(1, activity.getIdCard());
            psts.setString(2, activity.getSex());
            psts.setString(3, activity.getNation());
            psts.setString(4, activity.getEducation());
            System.out.print(sql);
            psts.executeUpdate();
            
            a=psts.executeUpdate();
            psts.close();
            con.close();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            //return false;
        }
        return true;
    }
    
    public  boolean findName(String nameOfhouseholder){//查找姓名
        boolean flag=false;//找不到
        Connection con=null;
        PreparedStatement psts=null;
        ResultSet rs=null;
        try {
            con=db.getCon();
            String sql="select * from population where nameOfhouseholder=?";
            psts=con.prepareStatement(sql);
            psts.setString(1, nameOfhouseholder);
            rs=psts.executeQuery();
            while(rs.next()){
                flag=true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return flag;
    }
    public ArrayList<info> getAllinformation(){
        Connection con=null;
        PreparedStatement psts=null;
        ResultSet rs=null;
        ArrayList<info> ll=new ArrayList<info>();
        try {
            con=db.getCon();
            String sql="select * from population";
            psts=con.prepareStatement(sql);
            rs=psts.executeQuery();//记录返回的结果
            while(rs.next()){
            
                String residenceType=rs.getString("residenceType");
                String housingType=rs.getString("housingType");
                String housingArea=rs.getString("housingArea");
                String numberOfrooms=rs.getString("numberOfrooms");
                String nameOfhouseholder=rs.getString("nameOfhouseholder");
                String idCard=rs.getString("idCard");
                String sex=rs.getString("sex");
                String nation=rs.getString("nation");
                String education=rs.getString("education");
                info activity = new info(residenceType,housingType,housingArea,numberOfrooms,nameOfhouseholder,idCard,sex,nation,education);
                ll.add(activity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ll;
    }
    
    public static List<info> loadview(String sql) {
        Connection con=null;
        PreparedStatement psts=null;

        System.out.println("loadview??sql:"+sql);
        String sql1="select * from population "+ sql;
        System.out.println("loadview??sql1:"+sql1);
        List<info> list =new ArrayList<info>();
        Connection conn = db.getCon();
        Statement state = null;
        ResultSet rs = null;
        info yi=null;
            try 
            {
                state = conn.createStatement();
                rs = state.executeQuery(sql1);
                while(rs.next()) 
                {
                    
                    String residenceType=rs.getString("residenceType");
                    String housingType=rs.getString("housingType");
                    String housingArea=rs.getString("housingArea");
                    String numberOfrooms=rs.getString("numberOfrooms");
                    String nameOfhouseholder=rs.getString("nameOfhouseholder");
                    String idCard=rs.getString("idCard");
                    String sex=rs.getString("sex");
                    String nation=rs.getString("nation");
                    String education=rs.getString("education");
                    
                    System.out.println("nameOfhouseholder"+nameOfhouseholder);
                 
                    yi=new info(residenceType,housingType,housingArea,numberOfrooms,nameOfhouseholder,idCard,sex,nation,education);
                    list.add(yi);
                    System.out.println("name1"+yi.getNameOfhouseholder());
                }
            
                ///db.close(rs, state, conn);
                rs.close();
                state.close();
                conn.close();
                
            } catch (SQLException e) {
                
                e.printStackTrace();
            } 

        return list;
    }
    
}

package bean;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


public class db {
    private static String mysqlname = "population";
    private static Connection con;
    private static Statement sta;
    private static ResultSet re;
    private static String coursename = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/"+mysqlname+"?useSSL=false&characterEncoding=utf8";
    
    //注册驱动
    public static Connection getCon() {
        try {
            Class.forName(coursename);
            System.out.println("驱动加载成功");
        }catch(ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(url,"root","fx30001225");
            System.out.println("连接成功");
        }catch(Exception e){
            e.printStackTrace();
            con = null;
        }
        return con;
    }
    
    public static void close(Statement sta,Connection connection) {
        if(sta!=null) {
            try {
                ((Connection) sta).close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null) {
            try {
                connection.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
        
    //关闭连接
    public static void close(ResultSet re,Statement sta,Connection connection) {
        if(re!=null) {
            try {
                re.close();
            }catch(SQLException e) {
            e.printStackTrace();
            }
        }
        if(sta!=null) {
            try {
                ((Connection) sta).close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null) {
            try {
                connection.close();
            }catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void main(String[] args)
    {
        getCon();
    }
}

package bean;

public class info {

    private String residenceType;
    private String housingType;
    private String housingArea;
    private String numberOfrooms;
    private String nameOfhouseholder;
    private String idCard;
    private String sex;
    private String nation;
    private String education;
    
    public String getResidenceType() {
        return residenceType;
    }
    public void setResidenceType(String residenceType) {
        this.residenceType = residenceType;
    }
    public String getHousingType() {
        return housingType;
    }
    public void setHousingType(String housingType) {
        this.housingType = housingType;
    }
    public String getHousingArea() {
        return housingArea;
    }
    public void setHousingArea(String housingArea) {
        this.housingArea = housingArea;
    }
    public String getNumberOfrooms() {
        return numberOfrooms;
    }
    public void setNumberOfrooms(String numberOfrooms) {
        this.numberOfrooms = numberOfrooms;
    }
    public String getNameOfhouseholder() {
        return nameOfhouseholder;
    }
    public void setNameOfhouseholder(String nameOfhouseholder) {
        this.nameOfhouseholder = nameOfhouseholder;
    }
    public String getIdCard() {
        return idCard;
    }
    public void setIdCard(String idCard) {
        this.idCard = idCard;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getNation() {
        return nation;
    }
    public void setNation(String nation) {
        this.nation = nation;
    }
    public String getEducation() {
        return education;
    }
    public void setEducation(String education) {
        this.education = education;
    }
    public info(String residenceType,String housingType, String housingArea, String numberOfrooms, String nameOfhouseholder, String idCard,
            String sex, String nation, String education) {
        super();
        this.residenceType = residenceType;
        this.housingType = housingType;
        this.housingArea = housingArea;
        this.numberOfrooms = numberOfrooms;
        this.nameOfhouseholder = nameOfhouseholder;
        this.idCard = idCard;
        this.sex = sex;
        this.nation = nation;
        this.education = education;
    }
    public info() {
        super();
        // TODO Auto-generated constructor stub
    }
    @Override
    public String toString() {
        return "info [residenceType=" + residenceType + ", housingType=" + housingType + ", housingArea=" + housingArea
                + ", numberOfrooms=" + numberOfrooms + ", nameOfhouseholder=" + nameOfhouseholder + ", idCard=" + idCard
                + ", sex=" + sex + ", nation=" + nation + ", education=" + education + "]";
    }
    
    
}

以上算是根据学姐源码扒的一个bean包（包括数据库的连接及增删改查、信息类等）模板吧……