JDBC练习题—Dao练习

最新推荐文章于 2024-04-26 17:17:26 发布

choosezzz

最新推荐文章于 2024-04-26 17:17:26 发布

阅读量2.3k

点赞数 3

分类专栏： mysql学习总结文章标签： Dao jdbc 方法封装

本文链接：https://blog.csdn.net/dingse/article/details/78383959

版权

mysql学习总结专栏收录该内容

16 篇文章 0 订阅

订阅专栏

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]
]
-------------------------------------------------