Matrix42

不饱食以终日,不弃功于寸阴

JDBC之: ORM基本思想(七)

2018年11月16日 0条评论 1,244次阅读 0人点赞

ORM(Object RelationShip Mapping)的基本思想

  • 表结构跟类对应;表中的字段和类的属性对应;表中的记录和对象对应

  • 让javabean的属性名和类型尽量和数据库保持一致

  • 一条记录对应一个对象,将这些查询到的对象放到容器中

  • 将表中的一条记录封装到Object数组中

  • 将表中的一条记录封装到map中

  • 将表中一条记录封装到javabean对象中

封装连接和关闭

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {

    //用于读取资源文件中的信息
    static Properties pros = null;

    //加载JDBCUtil类的时候调用
    static {
        pros = new Properties();
        try {
            pros.load(Thread.currentThread().getContextClassLoader()
.getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        try {
            Class.forName(pros.getProperty("Driver"));
            return DriverManager.getConnection(pros.getProperty("Url"),
                pros.getProperty("User"),
                pros.getProperty("Password"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void close(ResultSet rs,Statement st,Connection conn){

        try {
            if(rs!=null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(st!=null){
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}

使用Object数组封装一条记录

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 测试使用Object[]来封装一条记录
 * 使用List<Object[]>存储多条记录
 * @author Matrix42
 *
 */
public class Demo01 {
    public static void main(String[] args) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Object[] obj = null;
        List<Object[]> list = new ArrayList<Object[]>();
        //Alt+Shift+Z  add try-catch block
        try {
            ps = conn.prepareStatement("select empname,salary,age from emp where id = ?");
            ps.setObject(1, 1);
            rs = ps.executeQuery();

            while(rs.next()){
                //System.out.println(rs.getString("empname"));
                obj = new Object[3];
                //一个Object封装一条数据
                //多条可以用容器
                obj[0] = rs.getObject(1);
                obj[1] = rs.getObject(2);
                obj[2] = rs.getObject(3);
                list.add(obj);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtil.close(rs, ps, conn);
        }
        //连接关闭后还可以获取内容
        for (Object[] objs : list) {
            System.out.println(""+objs[0]+"\n"+objs[1]+"\n"+objs[2]);
        }    
    }
}

使用Map封装一条记录

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 测试使用Map来封装一条记录
 * 使用List<Map>存储多条记录
 * @author Matrix42
 *
 */
public class Demo02 {
    public static void main(String[] args) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        //使用一个Map封装一条记录
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

        //Alt+Shift+Z  add try-catch block
        try {
            ps = conn.prepareStatement("select empname,salary,age from emp where id < ?");
            ps.setObject(1, 3);
            rs = ps.executeQuery();

            while(rs.next()){
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("empname", rs.getObject(1));
                row.put("salary", rs.getObject(2));
                row.put("age", rs.getObject(3));
                list.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtil.close(rs, ps, conn);
        }
        //连接关闭后还可以获取内容
        for (Map<String, Object> map : list) {
            System.out.println(map.get("empname"));
            System.out.println(map.get("salary"));
            System.out.println(map.get("age"));
        }
    }

}

使用javabean对象封装一条记录

import java.sql.Date;

//表和类对应
/**
 * emp表的javabean
 * @author Matrix42
 *
 */
public class Emp {

    private Integer id;
    private String empname;
    private Double salary;
    private Date birthday;
    private Integer age;
    private Integer deptId;

    //javabean一定要有空构造器
    public Emp() {
        super();

    }

    public Emp(String empname, Double salary, Date birthday, 
            Integer age, Integer deptId) {
        super();
        this.empname = empname;
        this.salary = salary;
        this.birthday = birthday;
        this.age = age;
        this.deptId = deptId;
    }

    public Emp(Integer id, String empname, Double salary, Date birthday, 
            Integer age, Integer deptId) {
        super();
        this.id = id;
        this.empname = empname;
        this.salary = salary;
        this.birthday = birthday;
        this.age = age;
        this.deptId = deptId;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmpname() {
        return empname;
    }

    public void setEmpname(String empname) {
        this.empname = empname;
    }

    public Double getSalary() {
        return salary;
    }

    public void setSalary(Double salary) {
        this.salary = salary;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Integer getDeptId() {
        return deptId;
    }

    public void setDeptId(Integer deptId) {
        this.deptId = deptId;
    }

    //方便打印
    @Override
    public String toString() {
        return "Emp [id=" + id + ", empname=" + empname + ", salary=" + salary
                + ", birthday=" + birthday + ", age=" + age + ", deptId="
                + deptId + "]";
    }

}

/**
 * dept表的javabean
 * @author Matrix42
 *
 */
public class Dept {

    private Integer id;
    private String address;
    private String dname;
    public Dept() {
        super();

    }
    public Dept(String address, String dname) {
        super();
        this.address = address;
        this.dname = dname;
    }
    public Dept(Integer id, String address, String dname) {
        super();
        this.id = id;
        this.address = address;
        this.dname = dname;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getDname() {
        return dname;
    }
    public void setDname(String dname) {
        this.dname = dname;
    }

}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 测试使用javabean来封装一条记录
 * 使用List<javabean>存储多条记录
 * @author Matrix42
 *
 */
public class Demo03 {
    public static void main(String[] args) {
        Connection conn = JDBCUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        //使用一个Map封装一条记录
        List<Emp> list = new ArrayList<Emp>();

        //Alt+Shift+Z  add try-catch block
        try {
            ps = conn.prepareStatement("select empname,salary,age from emp where id < ?");
            ps.setObject(1, 3);
            rs = ps.executeQuery();

            while(rs.next()){
                Emp emp = new Emp(rs.getString(1),rs.getDouble(2),null,rs.getInt(3),null);
                list.add(emp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtil.close(rs, ps, conn);
        }
        //连接关闭后还可以获取内容
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

}
点赞

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

15 + 7 =