笔记02-JDBC-ORM映射思想

• 创建工程格式
  com.oracle.util工具包
  com.oracle.pojo(plain old java object)实普通的老的java对象/com.oracle.entity(实体)/com.oracle.vo(Value Object)都是MySQL数据库的实体对象包
  com.oracle.dao(data access object)接口包
  com.oracle.daoImpl接口实现类包
  com.oracle.test测试包

• ORM映射关系
  Object RalationShip Mapping 对象关系映射思想，所有持久层操作的根本思想，持久层有：mybatis、jdbcTemplate、hibrenate、spring DataJPA

• 为什么要有ORM映射关系出现
  答：因为java是面向对象的编程语言，MySQL是关系型数据库，他们两个是完全风马牛不相及的，所以要有一种思想来包裹这两个东西，最终完成数据的映射。

• 什么是ORM映射思想？
  1.数据库表和java实体映射
  2.数据库中的记录和java实体中的对象
  3.数据库中的字段和java实体中的属性

• 案例
  1.com.oracle.pojo实体包

package com.oracle.pojo;

import java.util.Date;

public class Person {
		  //数据库的字段--->实体类的属性
	      private int personId;
	      private String personName;
	      private String degree;
	      private Date birth;
	      private int sal;
	      
	      //setter和getter方法
		public int getPersonId() {
			return personId;
		}
		public void setPersonId(int 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 int getSal() {
			return sal;
		}
		public void setSal(int sal) {
			this.sal = sal;
		}
		@Override
		public String toString() {
			return "Persom [personId=" + personId + ", personName=" + personName + ", degree=" + degree + ", birth="
					+ birth + ", sal=" + sal + "]";
		}
}

2.com.oracle.util工具包

package com.oracle.util;

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

public class DBUtils {

	public static final String URL = "jdbc:mysql:///java210601?characterencoding = UTF8";
	public static final String USER = "root";
	public static final String PSSWORD = "root";
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	public static Connection getConnection(){
		Connection conn= null;
		try {
			conn = DriverManager.getConnection(URL, USER, PSSWORD);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return conn;
	}
	
	public static void close(Connection conn, Statement state ,ResultSet rs){
			try {
				if(rs != null){
					rs.close();
				}
				if(state != null){
					state.close();
				}
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
	}
}

3.com.oracle.dao接口包

package com.oracle.dao;

import java.util.List;

import com.oracle.pojo.Person;

public interface PersonDao {

	//插入
	public int insert(Person person) throws Exception;
	
	//修改一个人员
	public int update(Person person) throws Exception;
	
	//删除一个人员名单
	public int deleteById(int id) throws Exception;

	//根据人员编号删除多个人员名单
	public int deleteBuIds(int[] ids) throws Exception;
	
	//查询所有人员名单
	public List<Person> getAll() throws Exception;
	
	//查询一页人员名单
	public List<Person> getPersonByLimit(int startRow, int pageSize) throws Exception;
	
	//通过id查找人
	public Person getPersonById(int id) throws Exception;
	
	//模糊查询
	public List<Person> getPersonByLike(String str) throws Exception;
	
	//模糊查询和分页
	public List<Person> getPersonByLikeWithLimit(String str, Integer start, Integer pageSize) throws Exception;

}

4.com.oracle.daoImpl实现接口包

package com.oracle.daoImpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.oracle.dao.PersonDao;
import com.oracle.pojo.Person;
import com.oracle.util.DBUtils;

public class PersonDaoImpl implements PersonDao{

	
	//插入
	@Override
	public int insert(Person person) throws Exception {
	//sql语法
		String sql = "insert into Person (personName,degree,birth,sal) values"
				+ "('"+person.getPersonName()+"','"+person.getDegree()+"','"
				+person.getBirth()+"','"+person.getSal()+"') ";
		System.out.println(sql);
		//获取链接
		Connection conn = DBUtils.getConnection();
		//得到Statement对象，加载和执行sql语句
		Statement state = conn.createStatement();
		//执行sql
		int a = state.executeUpdate(sql);
		//关闭资源
		DBUtils.close(conn, state, null);
		return a;
	}

	//修改
	@Override
	public int update(Person person) throws Exception {

		String sql = "update person set personName = '"+person.getPersonName()+"',degree = '"
					+person.getDegree()+"',birth = '"+person.getBirth()+"', sal = '"+person.getSal()
					+"' where personid = "+person.getPersonId();
		
		Connection conn = DBUtils.getConnection();
		Statement state = conn.createStatement();
		int a = state.executeUpdate(sql);
		DBUtils.close(conn, state, null);
		return a;
	}

	//删除
	@Override
	public int deleteById(int id) throws Exception {
		String sql = "delete from person where personid = " + id;
		System.out.println(sql);
		Connection conn = DBUtils.getConnection();
		Statement state = conn.createStatement();
		int a = state.executeUpdate(sql);
		DBUtils.close(conn, state, null);
		return a;
	}

	//批量删除
	@Override
	public int deleteBuIds(int[] ids) throws Exception {
		String sql = "delete from person where personid in(";
		for (int i = 0; i < ids.length; i++) {
			if(i < ids.length-1){
				sql += ids[i] + ",";
			}else{
				sql += ids[i] + ")";
			}
		}
		
		Connection conn = DBUtils.getConnection();
		Statement state = conn.createStatement();
		int a = state.executeUpdate(sql);
		DBUtils.close(conn, state, null);
		return a;
	}

	//查看所有
	@Override
	public List<Person> getAll() throws Exception {
		String sql = "select * from person";
		Connection conn = DBUtils.getConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery(sql);
		
		Person person = null;
		List<Person> list = new ArrayList<>();
		
		while(rs.next()){
			person = new Person();
			person.setPersonId(rs.getInt("personid"));
			person.setPersonName(rs.getString("personName"));
			person.setDegree(rs.getString("degree"));
			person.setBirth(rs.getDate("birth"));
			person.setSal(rs.getInt("sal"));
			list.add(person);		
		}
		DBUtils.close(conn, state, rs);
		return list;
	}

	//分页
	@Override
	public List<Person> getPersonByLimit(int startRow, int pageSize) throws Exception {
		String sql = "select * from person limit "+ startRow+","+pageSize;
		System.out.println(sql);
		Connection conn = DBUtils.getConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery(sql);
		
		List<Person> list = new ArrayList<>();
		Person person =null;
		while(rs.next()){
			person = new Person();
			person.setPersonId(rs.getInt("PersonId"));
			person.setPersonName(rs.getString("PersonName"));
			person.setDegree(rs.getString("degree"));
			person.setBirth(rs.getDate("birth"));
			person.setSal(rs.getInt("sal"));
			
			list.add(person);
			}
		
		DBUtils.close(conn, state, rs);
		return list;
	}

	//通过ID查名字
	@Override
	public Person getPersonById(int id) throws Exception {
		String sql = "select * from person where  Personid =" + id;
		Connection conn = DBUtils.getConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery(sql);
		
		Person person = null;
		while(rs.next()){
			person = new Person();
			person.setPersonId(rs.getInt("PersonId"));
			person.setPersonName(rs.getString("PersonName"));
			person.setDegree(rs.getString("degree"));
			person.setBirth(rs.getDate("birth"));
			person.setSal(rs.getInt("sal"));
		}
		DBUtils.close(conn, state, rs);
		return person;
	}

	//模糊查询
	@Override
	public List<Person> getPersonByLike(String str) throws Exception {

		String sql = "select * from person where personName like '%"+str+"%'";
		System.out.println(sql);
		Connection conn = DBUtils.getConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery(sql);
		
		Person person = null;
		List<Person> list = new ArrayList<>();
		
		while(rs.next()){
			person = new Person();
			person.setPersonId(rs.getInt("PersonId"));
			person.setPersonName(rs.getString("PersonName"));
			person.setDegree(rs.getString("degree"));
			person.setBirth(rs.getDate("birth"));
			person.setSal(rs.getInt("sal"));
			list.add(person);
		}
		
		DBUtils.close(conn, state, rs);
		return list;
	}

	//模糊查询和分页
	@Override
	public List<Person> getPersonByLikeWithLimit(String str, Integer pageNo, Integer pageSize) throws Exception {
		int startRow = (pageNo-1)*pageSize;
		String sql = "select * from person where 1=1 ";
		if(str !=null && !"".equals(str)){
			sql += " and personName like '%"+str+"%'";
		}
		if(pageNo != null){
			sql += " and limit "+pageNo+","+pageSize+"";
		}

		System.out.println(sql);
		Connection conn = DBUtils.getConnection();
		Statement state = conn.createStatement();
		ResultSet rs = state.executeQuery(sql);
		
		Person person = null;
		List<Person> list = new ArrayList();
		
		while(rs.next()){
			person = new Person();
			setInfo(rs, person);
			list.add(person);
		}
		
		DBUtils.close(conn, state, rs);
		return list;
	}

	//封装方法
	public void setInfo(ResultSet rs , Person person){
		try {
			//获取数据库的数据，转给实体类
			person.setPersonId(rs.getInt("PersonId"));
			person.setPersonName(rs.getString("PersonName"));
			person.setDegree(rs.getString("degree"));
			person.setBirth(rs.getDate("birth"));
			person.setSal(rs.getInt("sal"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
}