jdbc连接数据库 增删改查

Person类

​package vo;

import java.sql.Date;

public class Person {
   private Integer personid;
   private  String personname;
   private String degree;
    private Date birth;
    private Integer sal;

    public Person() {
    }

    public Person(Integer personid, String personname, String degree, Date birth, Integer sal) {
        this.personid = personid;
        this.personname = personname;
        this.degree = degree;
        this.birth = birth;
        this.sal = sal;
    }

    public void setPersonid(Integer personid) {
        this.personid = personid;
    }

    public void setPersonname(String personname) {
        this.personname = personname;
    }

    public void setDegree(String degree) {
        this.degree = degree;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    public void setSal(Integer sal) {
        this.sal = sal;
    }

    public Integer getPersonid() {
        return personid;
    }

    public String getPersonname() {
        return personname;
    }

    public String getDegree() {
        return degree;
    }

    public Date getBirth() {
        return birth;
    }

    public Integer getSal() {
        return sal;
    }

    @Override
    public String toString() {
        return "Person{" +
                "personid=" + personid +
                ", personname='" + personname + '\'' +
                ", degree='" + degree + '\'' +
                ", birth=" + birth +
                ", sal=" + sal +
                '}';
    }
}
​

 

方法接口

package dao;


import vo.Person;

import java.util.List;

public interface PersonDao {
    public  void insert(Person person)throws Exception;
    public void update (Person person)throws Exception;
    public void delete(int[] ids)throws Exception;
    public List<Person> getAll()throws Exception;
    public List<Person> getPersonForPage(int pagesize,int pageNumber)throws Exception;
    public Person getPersonById(int id)throws Exception;
    public List<Person> getpersonByLikeWithLimit(String name,Integer startRow,Integer pageSize)throws  Exception;
}

 

实现类

package daoimpl;

import dao.PersonDao;
import util.DBtools;
import vo.Person;


import javax.swing.plaf.synth.SynthOptionPaneUI;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.Date;

public class PersonDaoImpl implements PersonDao {
    @Override
    //插入一个人员
    public void insert(Person person) throws Exception {
        String src ="insert into p(personid,personname,degree,birth,sal)values ("+person.getPersonid()+",'"+person.getPersonname()+"','"+person.getDegree()+"',"+person.getBirth()+","+person.getSal()+")";
        Statement getsta = DBtools.getsta(DBtools.getconnection());
        getsta.executeUpdate(src);
        System.out.println("插入数据执行成功");
    }
    //修改一个人员
    @Override
    public void update(Person person) throws Exception {
        String src ="update  p set personname='"+person.getPersonname()+"' where personid="+person.getPersonid()+"";
        Statement sta =DBtools.getsta(DBtools.getconnection());
        sta.executeUpdate(src);
        System.out.println("修改人员执行成功");
    }
    //根据人员编号删除多个人员
    @Override
    public void delete(int[] ids) throws Exception {
        for(int i=0;i<ids.length;i++){
            String src ="delete  from p where personid="+ids[i]+"";
            Statement sta =DBtools.getsta(DBtools.getconnection());
            sta.executeUpdate(src);
            System.out.println("删除成功");
        }
    }
    //查询所有的人员，将查询的人员存储在List
    @Override
    public List<Person> getAll() throws Exception {

        List<Person> list =new ArrayList<>();
        String src = "select * from p";
        Statement statement =DBtools.getsta(DBtools.getconnection());
        ResultSet resultSet = statement.executeQuery(src);
        while(resultSet.next()){
            Person person =new Person();
            person.setPersonid(resultSet.getInt("personid"));
            person.setPersonname( resultSet.getString("personname"));
            person.setDegree(resultSet.getString("degree"));
            person.setBirth(resultSet.getDate("birth"));
            person.setSal(resultSet.getInt("sal"));
            list.add(person);
        }
        return list;
    }
    //查询某一页的人员
    @Override
    public List<Person> getPersonForPage(int pagesize, int pageNumber) throws Exception {
        List<Person> list =new ArrayList<>();
        int v =(pageNumber-1)*pagesize;
        String src ="select * from p limit "+v+","+pagesize+"";
        Statement sta =DBtools.getsta(DBtools.getconnection());
        ResultSet resultSet = sta.executeQuery(src);
        while(resultSet.next()){
            Person person=new Person();
            person.setPersonid(resultSet.getInt("personid"));
            person.setPersonname(resultSet.getString("personname"));
           person.setDegree(resultSet.getString("degree"));
           person.setBirth(resultSet.getDate("birth"));
            person.setSal(resultSet.getInt("sal"));
            list.add(person);
        }
        return list;
    }
    //根据人员编号，查询出此编号对应的人员
    @Override
    public Person getPersonById(int id) throws Exception {
        Person person =new Person();
        String str ="select * from p where personid="+id+"";
        Statement sta =DBtools.getsta(DBtools.getconnection());
        ResultSet resultSet = sta.executeQuery(str);
        while(resultSet.next()) {
            person.setPersonname(resultSet.getString("personname"));
            person.setDegree(resultSet.getString("degree"));
            person.setBirth(resultSet.getDate("birth"));
            person.setSal(resultSet.getInt("sal"));
        }
        return person;
    }
    //模糊查询带分页
    @Override
    public List<Person> getpersonByLikeWithLimit(String name, Integer startRow, Integer pageSize) throws Exception {
        List<Person> list =new ArrayList<>();
        String src ="select * from p where personname like '"+name+"%"+"' limit "+startRow+","+pageSize+"";
        Statement statement =DBtools.getsta(DBtools.getconnection());
        ResultSet resultSet = statement.executeQuery(src);
        while(resultSet.next()){
            Person person=new Person();
            person.setPersonid(resultSet.getInt("personid"));
            person.setPersonname(resultSet.getString("personname"));
            person.setDegree(resultSet.getString("degree"));
            person.setBirth(resultSet.getDate("birth"));
            person.setSal(resultSet.getInt("sal"));
            list.add(person);
        }
        return list;
    }
}

工具类

package util;

import java.sql.*;

public class DBtools {
    private static final String conn="jdbc:mysql://localhost:3306/java2008";
    private static final String user="root";
    private static final String password="999222";
    static{
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getconnection()throws Exception{
        Connection connection = DriverManager.getConnection(conn, user, password);
        return connection;
    }
    public static Statement getsta(Connection connection)throws Exception{
        Statement statement= connection.createStatement();
        return statement;
    }
    public void closeconn(Connection conn, Statement sta){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }if(sta!=null) {
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

package Test;

import daoimpl.PersonDaoImpl;
import vo.Person;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;

public class Test1 {
    public static void main(String[] args) throws Exception{
        Person person =new Person();
        PersonDaoImpl d =new PersonDaoImpl();
       person.setPersonid(8);
        person.setPersonname("孙悟空");
        //DateFormat df =new SimpleDateFormat("yyyy-MM-dd");
       // String birthday =df.format("1920-20-15");
         person.setDegree("本科");
         person.setBirth(null);
        person.setSal(12);
       // d.insert(person);//插入成功
        //int [ ] a ={2,3};
        //d.delete(a);//删除成功
        //d.update(person);//修改成功
        //List<Person> list = d.getAll();//查询成功
        //for (Person person1:list) {
            //System.out.println(person1);
        //}
        //List<Person> list =d.getPersonForPage(3,2);//分页查询成功
        /*for (Person person2:list) {
            System.out.println(person2);
        }*/
      // Person person2= d.getPersonById(3);//按id查询成功
       //System.out.println(person2);

       List<Person> list2= d.getpersonByLikeWithLimit("孙",0,1);
        for (Person person4:list2
             ) {
            System.out.println( person4);
        }
    }
}