-
创建工程格式
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();
}
}
}