VO:Person类
package com.test.vo;
import java.util.Date;
/**
* 对应person表的所有字段
* @author dingshuangen
*
*/
public class Person {
private Integer personId;
private String personName;
private String degree;
private Date birth;
private Integer sal;
public Integer getPersonId() {
return personId;
}
public void setPersonId(Integer personId) {
this.personId = personId;
}
public String getPersonName() {
return personName;
}
public void setPersonName(String personName) {
this.personName = personName;
}
public String getDegree() {
return degree;
}
public void setDegree(String degree) {
this.degree = degree;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
public Integer getSal() {
return sal;
}
public void setSal(Integer sal) {
this.sal = sal;
}
public Person() {
super();
}
public Person(String personName, String degree, Date birth, Integer sal) {
super();
this.personName = personName;
this.degree = degree;
this.birth = birth;
this.sal = sal;
}
@Override
public String toString() {
return "Person [personId=" + personId + ", personName=" + personName + ", degree=" + degree + ", birth=" + birth
+ ", sal=" + sal + "]";
}
}
BaseDao类
package com.test.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 数据库的通用操作类
* @author dingshuangen
*
*/
public class BaseDao {
/**
* 获得连接
* @return
*/
public Connection getConnection() {
Connection conn=null;
try {
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","0325");
return conn;
}catch(Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 关闭实现AutoCloseable接口的资源
* @param auto
*/
public void close(AutoCloseable auto) {
if(auto!=null) {
try {
auto.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 根据给定的sql及参数执行sql语句
* @param sql
* @param objs参数
*/
public void executeSql(String sql,Object...objs) {
Connection conn=this.getConnection();
PreparedStatement ps=null;
try {
ps=conn.prepareStatement(sql);
//为sql语句中的?占位符设置具体参数
for(int i=0;i<objs.length;i++) {
ps.setObject(i+1,objs[i]);
}
//执行
ps.execute();
System.out.println("*****执行( "+sql+" )成功*****");
}catch(Exception e) {
e.printStackTrace();
}finally {
this.close(ps);
this.close(conn);
}
}
/**
* 获得查询出来的数据表
* @param sql
* @param objs
* @return
*/
public List<Map<String,Object>> executQuery(String sql,Object...objs){
List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
//获得连接
conn=this.getConnection();
//创建声明
ps=conn.prepareStatement(sql);
//设置参数
for(int i=0;i<objs.length;i++) {
ps.setObject(i+1, objs[i]);
}
//获得查询得到的集合
rs=ps.executeQuery();
//获得元数据集合
ResultSetMetaData rsmd=rs.getMetaData();
while(rs.next()) {
//创建一个Map用来存储查询到的信息,列名为键,数据为值
Map<String,Object> map=new HashMap<String,Object>();
for(int i=1;i<=rsmd.getColumnCount();i++) {
//将数据添加到map中
map.put(rsmd.getColumnLabel(i), rs.getObject(i));
}
//将map添加到list中
list.add(map);
}
return list;
}catch(Exception e) {
e.printStackTrace();
}finally {
this.close(rs);
this.close(ps);
this.close(conn);
}
return list;
}
}
Dao接口:
package com.test.dao;
import java.util.List;
import com.test.vo.Person;
public interface PersonDao {
/**
* 插入一个人员
* @param person:被插入的人员对象
*/
public void insert(Person person);
/**
* 修改一个人员:
* @param person:被修改的人员对象,人员对象只含有人员编号,修改此编号的员工信息
*/
public void update(Person person);
/**
* 根据人员编号删除多个人员
* @param ids:被删除的人员编号数组
*/
public void delete(int[] ids);
/**
* 查询所有的人员,将查询出的人员存储在List中
* @return :所有人员对象的集合
*/
public List<Person> getAll();
/**
* 查询某一页的人员
* @param pagesize:每页的记录条数
* @param pageOrder:页号(即第几页)
* @return
*/
public List<Person> getPersonForPage(int pagesize,int pageOrder);
/**
* 根据人员编号,查询出此编号对应的人员
* @return
*/
public Person getPersonById(int id) ;
}
Dao接口的实现类:
package com.test.dao;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import com.test.vo.Person;
public class PersonDaoImpl extends BaseDao implements PersonDao {
@Override
public void insert(Person person) {
this.executeSql("insert into Person values(null,?,?,?,?)", person.getPersonName(),person.getDegree(),person.getBirth(),person.getSal());
}
@Override
public void update(Person person) {
this.executeSql("update person set personname=?,degree=?,birth=?,sal=? where personid=?", person.getPersonName(),person.getDegree(),person.getBirth(),person.getSal(),person.getPersonId());
}
@Override
public void delete(int[] ids) {
for(int i=0;i<ids.length;i++) {
this.executeSql("delete from person where personid=?", ids[i]);
}
}
@Override
public List<Person> getAll() {
List<Map<String,Object>> l=this.executQuery("select * from person order by personid");
return this.convert(l);
}
@Override
public List<Person> getPersonForPage(int pagesize, int pageOrder) {
return this.convert(this.executQuery("select * from person limit ?,? ", (pageOrder-1)*pagesize,pagesize));
}
@Override
public Person getPersonById(int id) {
Person p=null;
List<Person> list= this.convert(this.executQuery("select * from person where personid=?", id));
if(list!=null) {
p=list.get(0);
}
return p;
}
/**
* 转换方法,将List<Map<String,Object>>转换成List<Person>
* @param l 查询得到的初始数据
* @return
*/
private List<Person> convert(List<Map<String,Object>> l){
List<Person> list=new ArrayList<Person>();
for (Map<String, Object> map : l) {
Person p=new Person();
//将所有的数据添加到一个Person中
p.setPersonId((Integer)map.get("personid"));
p.setPersonName((String)map.get("personname"));
p.setBirth((Date)map.get("birth"));
p.setDegree((String)map.get("degree"));
p.setSal((Integer)map.get("sal"));
list.add(p);
}
return list;
}
}
测试方法:
package com.test.test;
import java.sql.Date;
import com.test.dao.PersonDao;
import com.test.dao.PersonDaoImpl;
import com.test.vo.Person;
public class PersonDaoTest {
public static void main(String[] args) {
PersonDao dao=new PersonDaoImpl();
System.out.println("当前所有的数据:\n"+dao.getAll());
System.out.println("-------------------------------------------------");
Date d=Date.valueOf("1997-02-20");
dao.insert(new Person("小花","大专",d,5820));
System.out.println("-------------------------------------------------");
dao.insert(new Person("小强","硕士",Date.valueOf("1998-03-18"),9852));
System.out.println("-------------------------------------------------");
System.out.println("插入新的数据之后:\n"+dao.getAll());
System.out.println("-------------------------------------------------");
System.out.println("根据id查找:\n"+dao.getPersonById(2));
System.out.println("-------------------------------------------------");
System.out.println("更新信息:");
Person p=new Person("小红","高中",Date.valueOf("2000-8-9"),6300);
p.setPersonId(2);
dao.update(p);
System.out.println("根据id查找:\n"+dao.getPersonById(2));
System.out.println("-------------------------------------------------");
System.out.println("分页查找:");
System.out.println(dao.getPersonForPage(5, 2));
System.out.println("-------------------------------------------------");
System.out.println("执行批量删除:");
dao.delete(new int[]{1,2,3});
System.out.println("-------------------------------------------------");
System.out.println("删除数据之后所有数据:\n"+dao.getAll());
System.out.println("-------------------------------------------------");
}
}
当前所有的数据:
[Person [personId=1, personName=gggg, degree=博士, birth=1996-03-15, sal=9690]
, Person [personId=2, personName=hhhh, degree=本科, birth=1989-03-26, sal=8900]
, Person [personId=3, personName=iiii, degree=本科, birth=1990-06-08, sal=7800]
, Person [personId=4, personName=XIAO, degree=SS, birth=1997-08-09, sal=5800]
, Person [personId=5, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=6, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=7, personName=大红, degree=小学, birth=1998-08-09, sal=6000]
, Person [personId=8, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=9, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=10, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=11, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
]
-------------------------------------------------
*****执行( insert into Person values(null,?,?,?,?) )成功*****
-------------------------------------------------
*****执行( insert into Person values(null,?,?,?,?) )成功*****
-------------------------------------------------
插入新的数据之后:
[Person [personId=1, personName=gggg, degree=博士, birth=1996-03-15, sal=9690]
, Person [personId=2, personName=hhhh, degree=本科, birth=1989-03-26, sal=8900]
, Person [personId=3, personName=iiii, degree=本科, birth=1990-06-08, sal=7800]
, Person [personId=4, personName=XIAO, degree=SS, birth=1997-08-09, sal=5800]
, Person [personId=5, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=6, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=7, personName=大红, degree=小学, birth=1998-08-09, sal=6000]
, Person [personId=8, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=9, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=10, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=11, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=15, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=16, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
]
-------------------------------------------------
根据id查找:
Person [personId=2, personName=hhhh, degree=本科, birth=1989-03-26, sal=8900]
-------------------------------------------------
更新信息:
*****执行( update person set personname=?,degree=?,birth=?,sal=? where personid=? )成功*****
根据id查找:
Person [personId=2, personName=小红, degree=高中, birth=2000-08-09, sal=6300]
-------------------------------------------------
分页查找:
[Person [personId=6, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=7, personName=大红, degree=小学, birth=1998-08-09, sal=6000]
, Person [personId=8, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=9, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=10, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
]
-------------------------------------------------
执行批量删除:
*****执行( delete from person where personid=? )成功*****
*****执行( delete from person where personid=? )成功*****
*****执行( delete from person where personid=? )成功*****
-------------------------------------------------
删除数据之后所有数据:
[Person [personId=4, personName=XIAO, degree=SS, birth=1997-08-09, sal=5800]
, Person [personId=5, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=6, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=7, personName=大红, degree=小学, birth=1998-08-09, sal=6000]
, Person [personId=8, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=9, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=10, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=11, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
, Person [personId=15, personName=小花, degree=大专, birth=1997-02-20, sal=5820]
, Person [personId=16, personName=小强, degree=硕士, birth=1998-03-18, sal=9852]
]
-------------------------------------------------