jdbc

java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Properties; /* * 性能 效率 内存使用 * 维护性 （便于修改） * 基本功能 */ public class JDBCUtil { private static Properties p = new Properties(); private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); static{ //1 读入Properties文件 InputStream is = JDBCUtil.class.getResourceAsStream("/jdbc.properties"); //2 传入Properties集合 try { p.load(is); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //创建连接 public static Connection getConnection() throws Exception{ Connection conn = tl.get(); if(conn==null){ Class.forName(p.getProperty("driver")); conn = DriverManager.getConnection(p.getProperty("url"),p.getProperty("username"),p.getProperty("password")); tl.set(conn); return conn; } return conn; } //关闭资源 public static void close(Connection conn,PreparedStatement pstmt,ResultSet rs){ if(rs!=null){try{rs.close();}catch(Exception e){}} if(pstmt!=null){try{pstmt.close();}catch(Exception e){}} if(conn!=null){try{conn.close();tl.remove();}catch(Exception e){}} } public static void close(Connection conn,PreparedStatement pstmt){ if(pstmt!=null){try{pstmt.close();}catch(Exception e){}} if(conn!=null){try{conn.close();tl.remove();}catch(Exception e){}} } }

 

1:1,一般是双向关系
建实体类时,两边都建
但建表时只有一个外键,建在不主要一方
定义表时,定义外键,做唯一
private Integer id;
private String name;
private String password;
private Date birthday;
private PassPort passport;
---------------------------
private Integer id;
private String serial;
private Integer expire;
private Person person;
----------------------------
create table t_person(
id integer primary key,
name varchar(12),
password varchar(12),
birthday date
);
create table t_passport(
id integer primary key,
serial varchar(12),
expire varchar(12),
person_id integer unique references t_person(id)
);
--------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.dzf.dao.o2o.PersonDao">

<resultMap id="personResultMap" type="Person" >
<id property="id" column="person_id"/>
<result property="name" column="person_name"/>
<result property="password" column="person_password"/>
<result property="birthday" column="person_birthday"/>
<!-- 当只有passport一个对象 -->
<association property="passport" javaType="PassPort">
<id property="id" column="passport_id"></id>
<result property="serial" column="passport_serial"/>
<result property="expire" column="passport_expire"/>
</association>
</resultMap>
<select id="queryPersonById" resultMap="personResultMap">
select p.id as person_id,p.name as person_name,p.password as person_password,p.birthday as person_birthday
,pp.id as passport_id,pp.serial as passport_serial,pp.expire as passport_expire
from t_person p inner join t_passport pp
on p.id = pp.person_id
where p.id = #{id}
</select>
</mapper>

----------------------------------------------
n:1
多对一单向
private Integer id;
private String name;
private Double salary;
private Dept dept;
private Integer id;
private String code;
private String name;
create table t_employee(
id integer primary key,
name varchar(12),
salary decimal(12,2),
dept_id integer references t_dept(id)
);
create table t_dept(
id integer primary key,
code varchar(12),
name varchar(12)
);
<resultMap id="employeeResultMap" type="Employee" >
<id property="id" column="employee_id"/>
<result property="name" column="employee_name"/>
<result property="salary" column="employee_salary"/>
<association property="dept" javaType="Dept">
<id property="id" column="dept_id"></id>
<result property="code" column="dept_code"/>
<result property="name" column="dept_name"/>
</association>
</resultMap>
<select id="queryEmployeeByName" resultMap="employeeResultMap">
select e.id as employee_id,e.name as employee_name,e.salary as employee_salary,
d.id as dept_id,d.code as dept_code,d.name as dept_name
from t_employee e inner join t_dept d
on e.dept_id = d.id
where e.name = #{name}
</select>
<select id="queryDeptById" resultType="Dept">
select id,name,code
from t_dept
where id = #{id}
</select>
--------------------------------------------------------------------------------------------------------------
1:n单向(n:1注意更换时 表不用改,表天生是双向的,俩表就一外键,在多的一方)
private Integer id;
private String name;
private Double salary;
private Integer id;
private String code;
private String name;
private List<Employee> list = new ArrayList<Employee>();记得初始化
<resultMap id ="deptResultMap" type="Dept" >
<id property="id" column = "dept_id"/>
<result property = "code" column="dept_code"/>
<result property = "name" column="dept_name"/>
<collection property="list" ofType="Employee"><!-- 集合中元素类型 -->
<id property="id" column="employee_id"/>
<result property="name" column="employee_name"/>
<result property="salary" column="employee_salary"/>
</collection>
</resultMap>
<select id="queryDeptByName" resultMap="deptResultMap">
select d.id as dept_id ,d.code as dept_code,d.name as dept_name,
e.id as employee_id,e.name as employee_name,e.salary as employee_salary
from t_dept d inner join t_employee e
on d.id = e.dept_id
where d.name = #{name}
</select>
<select id="queryEmployeeById" resultType="Employee">
select id,name,salary
from t_employee
where id= #{id}
</select>
-----------------------------------------------------------------------------------------------------------------
多对多双向,student,course
外键如何建?,不能建,
解决办法:引入第三张关系表

______________________________________________________________________

import java.sql.Connection;
import java.sql.SQLException;

/*
 *  事务管理器 用于控制事务
 */
public class TransactionManager {

    public static void begin(){
        try {
            Connection conn = JDBCUtil.getConnection();
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    public static void commit(){
        Connection conn = null;
        try {
            conn = JDBCUtil.getConnection();
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtil.close(conn, null);
        }
        
        
    }
    
    public static void rollback(){
        Connection conn = null;
        try {
            conn = JDBCUtil.getConnection();
            conn.rollback();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            JDBCUtil.close(conn, null);
        }
        
    }
    
}

 

jdbc

标签：exp   into   app   throws   use   rac   事务管理器   ring   厂商