一、数据库操作框架

1、ORM：Object Relation Mapping

　　Hibernate：非常流行

　　JPA：Java Persistent API.ORM标准

　　MyBatis：2010年开始。之前叫做iBatis（重视）

2、JDBC封装框架

　　DBUtils

　　Spring JDBC Template

二、Apache的DBUtils框架（会用：练习作业）

1、基本的使用

DBCPutil

public class DBCPUtil {    private static DataSource dataSource;    static{        try {            InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");            Properties props = new Properties();            props.load(in);            dataSource = BasicDataSourceFactory.createDataSource(props);        } catch (Exception e) {            throw new ExceptionInInitializerError(e);        }    }         public static DataSource getDataSource(){        return dataSource;    }         public static Connection getConnection(){        try {            return dataSource.getConnection();        } catch (SQLException e) {            throw new RuntimeException(e);        }    }}

dbcpconfig.properties 配置文件对dbcp进行配置

QueryRunner 的使用 CRUD

/*create database day18;use day18;create table student(    id int primary key,    name varchar(100),    birthday date); */public class DBUtilCRUD {    private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());    @Test    public void testAdd() throws SQLException{        qr.update("insert into student values(?,?,?)", 1,"杨洋",new Date());    }    @Test    public void testUpdate() throws SQLException{        qr.update("update student set birthday=? where id=?", "1993-08-01",1);    }    @Test    public void testDel() throws SQLException{        qr.update("delete from student where id=?", 1);    }    //批处理插入10条    @Test    public void testBatch() throws SQLException{        Object params[][] = new Object[10][];//高维：记录的条数。低维：每条记录需要的参数        for(int i=0;i

2、各种结果处理器的使用 ResultSetHandler

public class ResultSetHandlerDemo {    private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());    //ArrayHandler:适合结果只有一条的情况。把第一条记录的每列的值封装到一个Object[]数组中    @Test    public void test1() throws Exception{        Object[] objs = qr.query("select * from student", new ArrayHandler());        for(Object obj:objs)            System.out.println(obj);    }    //ArrayListHandler:适合结果有多条的情况。把每列的值封装到Object[]数组中，把Object[]放到List中    @Test    public void test2() throws Exception{        List
    
      list = qr.query("select * from student", new ArrayListHandler());        for(Object[] objs:list){            System.out.println("----------------");            for(Object obj:objs){                System.out.println(obj);            }        }    }    //ColumnListHandler:适合取某列的值。把取到值封装到List中    @Test    public void test3() throws Exception{        List

三、实际开发中事务如何管理(非常好)

对事务的控制

1、写4个版本的代码：AOP

1.0

public interface AccountDao {    /**     * 转账     * @param sourceAccountName 转出账户     * @param targetAccontName 转入账户     * @param money 交易金额     */    void transfer(String sourceAccountName,String targetAccontName,float money);}

/*create table account(    id int primary key auto_increment,    name varchar(40),    money float)character set utf8 collate utf8_general_ci; insert into account(name,money) values('aaa',1000);insert into account(name,money) values('bbb',1000);insert into account(name,money) values('ccc',1000); */public class AccountDaoImpl implements AccountDao {         private QueryRunner qr = new QueryRunner();         public void transfer(String sourceAccountName, String targetAccontName,            float money) {        Connection conn = null;        try {            conn = DBCPUtil.getConnection();            conn.setAutoCommit(false);//开启事务            qr.update(conn,"update account set money=money-? where name=?", money,sourceAccountName);//          int i=1/0;            qr.update(conn,"update account set money=money+? where name=?", money,targetAccontName);        } catch (Exception e) {            if(conn!=null){                try {                    conn.rollback();                } catch (SQLException e1) {                    e1.printStackTrace();                }            }            e.printStackTrace();        }finally{            if(conn!=null){                try {                    conn.commit();                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }             } }

public interface BusinessService {    /**     * 转账     * @param sourceAccountName 转出账户     * @param targetAccontName 转入账户     * @param money 交易金额     */    void transfer(String sourceAccountName,String targetAccontName,float money);}

public class BusinessServiceImpl implements BusinessService {    private AccountDao dao = new AccountDaoImpl();    public void transfer(String sourceAccountName, String targetAccontName,            float money) {        dao.transfer(sourceAccountName, targetAccontName, money);    } }

public class Client {     public static void main(String[] args) {        BusinessService s = new BusinessServiceImpl();        s.transfer("aaa", "bbb", 100);    } }

2.0

//DAO层：不能牵扯到任何业务有关的逻辑。//DAO:只负责CRUDpublic interface AccountDao {    /**     * 根据户名查询账户     * @param accountName     * @return     */    Account findByName(String accountName);    /**     * 更新账户     * @param account     */    void updateAcount(Account account);}

public class AccountDaoImpl implements AccountDao {    private QueryRunner qr = new QueryRunner();    private Connection conn;    public AccountDaoImpl(Connection conn){        this.conn  = conn;    }    public Account findByName(String accountName) {        try {            return qr.query(conn,"select * from account where name=?", new BeanHandler
    
     (Account.class),accountName);        } catch (SQLException e) {            throw new RuntimeException(e);        }    }     public void updateAcount(Account account) {        try {            qr.update(conn,"update account set money=? where id=?", account.getMoney(),account.getId());        } catch (SQLException e) {            throw new RuntimeException(e);        }    }      }

public class Account {    private int id;    private String name;    private float money;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public float getMoney() {        return money;    }    public void setMoney(float money) {        this.money = money;    }     }

public interface BusinessService {    /**     * 转账     * @param sourceAccountName 转出账户     * @param targetAccontName 转入账户     * @param money 交易金额     */    void transfer(String sourceAccountName,String targetAccontName,float money);}

//业务层控制事务public class BusinessServiceImpl implements BusinessService {    public void transfer(String sourceAccountName, String targetAccontName,            float money) {        Connection conn = null;        try {            conn = DBCPUtil.getConnection();            conn.setAutoCommit(false);            AccountDao dao = new AccountDaoImpl(conn);            Account sAccount = dao.findByName(sourceAccountName);            Account tAccount = dao.findByName(targetAccontName);            sAccount.setMoney(sAccount.getMoney() - money);            tAccount.setMoney(tAccount.getMoney() + money);            dao.updateAcount(sAccount);            // int i=1/0;            dao.updateAcount(tAccount);        } catch (Exception e) {            if (conn != null) {                try {                    conn.rollback();                } catch (SQLException e1) {                    e1.printStackTrace();                }            }            e.printStackTrace();        } finally {            if (conn != null) {                try {                    conn.commit();                    conn.close();                } catch (SQLException e) {                    e.printStackTrace();                }            }        }    } }

public class Client {     public static void main(String[] args) {        BusinessService s = new BusinessServiceImpl();        s.transfer("aaa", "bbb", 100);    } }

3.0

//封装了所有与事务有关的方法public class TransactionManager {    private static ThreadLocal
    
      tl = new ThreadLocal
     
      ();    public static Connection getConnection(){        Connection conn = tl.get();        if(conn==null){//从当前线程中获取链接            conn = DBCPUtil.getConnection();            tl.set(conn);        }        return conn;    }    public static void startTransaction(){        try {            Connection conn = getConnection();            conn.setAutoCommit(false);        } catch (SQLException e) {            e.printStackTrace();        }    }    public static void rollback(){        try {            Connection conn = getConnection();            conn.rollback();        } catch (SQLException e) {            e.printStackTrace();        }    }    public static void commit(){        try {            Connection conn = getConnection();            conn.commit();        } catch (SQLException e) {            e.printStackTrace();        }    }    public static void release(){        try {            Connection conn = getConnection();            conn.close();            tl.remove();//从当前线程中解绑。  与服务器实现有关：服务器采用线程池。        } catch (SQLException e) {            e.printStackTrace();        }    }}

//业务层控制事务public class BusinessServiceImpl implements BusinessService {    private AccountDao dao = new AccountDaoImpl();    public void transfer(String sourceAccountName, String targetAccontName,            float money) {        try {            TransactionManager.startTransaction();            Account sAccount = dao.findByName(sourceAccountName);            Account tAccount = dao.findByName(targetAccontName);            sAccount.setMoney(sAccount.getMoney() - money);            tAccount.setMoney(tAccount.getMoney() + money);            dao.updateAcount(sAccount);             int i=1/0;            dao.updateAcount(tAccount);        } catch (Exception e) {            TransactionManager.rollback();            e.printStackTrace();        } finally {            TransactionManager.commit();            TransactionManager.release();        }    } }

public class AccountDaoImpl implements AccountDao {    private QueryRunner qr = new QueryRunner();         public Account findByName(String accountName) {        try {            return qr.query(TransactionManager.getConnection(),"select * from account where name=?", new BeanHandler
    
     (Account.class),accountName);        } catch (SQLException e) {            throw new RuntimeException(e);        }    }     public void updateAcount(Account account) {        try {            qr.update(TransactionManager.getConnection(),"update account set money=? where id=?", account.getMoney(),account.getId());        } catch (SQLException e) {            throw new RuntimeException(e);        }    }      }

4.0 AOP（面向切面编程）

//AOPpublic class BeanFactory {    public static BusinessService getBusinessService(){        final BusinessService s = new BusinessServiceImpl();                 BusinessService proxyS = (BusinessService)Proxy.newProxyInstance(s.getClass().getClassLoader(),                s.getClass().getInterfaces(),                new InvocationHandler() {                    public Object invoke(Object proxy, Method method, Object[] args)                            throws Throwable {                        try {                            TransactionManager.startTransaction();                            Object rtValue = method.invoke(s, args);                            return rtValue;                        } catch (Exception e) {                            TransactionManager.rollback();                            throw new RuntimeException(e);                        } finally {                            TransactionManager.commit();                            TransactionManager.release();                        }                    }                });                 return proxyS;    }}

2、ThreadLocal（很重要）

//特点：一个线程存的东西，只有该线程才能取出来。线程局部变量。

//模拟

public class ThreadLocal{

//类似Map的结构

private Map<Runnable,Object> map = new HashMap<Runnable,Object>();

public void set(Object obj){

map.put(Thread.currentThread(),obj);

}

public void remove(){

map.remove(Thread.currentThread());

}

public Object get(){

map.get(Thread.currentThread());

}

四、利用DBUtils框架进行多表的读取

1、一对多顾客对订单

mysql表创建：

use day18;create table customers(    id int primary key,    name varchar(100),    city varchar(100));create table orders(    id int primary key,    num varchar(100),    price float(10,2),    customer_id int,    constraint customer_id_fk foreign key(customer_id) references customers(id));

顾客类:

public class Customer {    private int id;    private String name;    private String city;         private List
    
      orders = new ArrayList
     
      ();         public List
      
        getOrders() {        return orders;    }    public void setOrders(List
       
         orders) {        this.orders = orders;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getCity() {        return city;    }    public void setCity(String city) {        this.city = city;    }    @Override    public String toString() {        return "Customer [id=" + id + ", name=" + name + ", city=" + city + "]";    }     }

订单类：

public class Order {    private int id;    private String num;    private float price;         private Customer customer;         public Customer getCustomer() {        return customer;    }    public void setCustomer(Customer customer) {        this.customer = customer;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getNum() {        return num;    }    public void setNum(String num) {        this.num = num;    }    public float getPrice() {        return price;    }    public void setPrice(float price) {        this.price = price;    }    @Override    public String toString() {        return "Order [id=" + id + ", num=" + num + ", price=" + price + "]";    }          }

实现

public class CustomerDaoImpl {    private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());    public void saveCustomer(Customer c){        try {            qr.update("insert into customers (id,name,city) values(?,?,?)",                    c.getId(),c.getName(),c.getCity());            //保存订单的信息：级联保存            List
    
      os = c.getOrders();            if(os.size()>0){                for(Order o:os){                    qr.update("insert into orders (id,num,price,customer_id) values(?,?,?,?)",                        o.getId(),o.getNum(),o.getPrice(),c.getId());                }            }        } catch (SQLException e) {            throw new RuntimeException(e);        }    }    //客户如果找到的话，它的订单要不要查询出来呢？ 看需求    // 查询客户时把对应的订单也查询出来（立即加载）    public Customer findCustomerById(int customerId){        try {            Customer c = qr.query("select * from customers where id=?", new BeanHandler
     
      (Customer.class),customerId);            if(c!=null){                //查订单                List
      
        os = qr.query("select * from orders where customer_id=?", new BeanListHandler
       
        (Order.class),customerId);                c.setOrders(os);            }            return c;        } catch (SQLException e) {            throw new RuntimeException(e);        }    }}

测试：

public class CustomerDaoImplTest {    private CustomerDaoImpl dao = new CustomerDaoImpl();    @Test    public void testSaveCustomer() {        Customer c = new Customer();        c.setId(1);        c.setName("范青霞");        c.setCity("北京");                 Order o1 = new Order();        o1.setId(1);        o1.setNum("001");        o1.setPrice(10000);                  Order o2 = new Order();        o2.setId(2);        o2.setNum("002");        o2.setPrice(100000);                 //建立关联关系        c.getOrders().add(o1);        c.getOrders().add(o2);                 dao.saveCustomer(c);    }     @Test    public void testFindCustomerById() {        Customer c = dao.findCustomerById(1);        System.out.println("客户姓名："+c.getName()+"买了以下商品：");        for(Order o:c.getOrders()){            System.out.println(o);        }    } }

2、多对多老师对学生

mysql 创建表sql语句

create table teachers(    id int primary key,    name varchar(100),    salary float(8,2));create table students(    id int primary key,    name varchar(100),    grade varchar(10));create table teachers_students(    t_id int,    s_id int,    primary key(t_id,s_id),    constraint t_id_fk foreign key(t_id) references teachers(id),    constraint s_id_fk foreign key(s_id) references students(id));

老师类：

public class Teacher {    private int id;    private String name;    private float salary;         private List
    
      students = new ArrayList
     
      ();     public int getId() {        return id;    }     public void setId(int id) {        this.id = id;    }     public String getName() {        return name;    }     public void setName(String name) {        this.name = name;    }     public float getSalary() {        return salary;    }     public void setSalary(float salary) {        this.salary = salary;    }     public List
      
        getStudents() {        return students;    }     public void setStudents(List
       
         students) {        this.students = students;    }     @Override    public String toString() {        return "Teacher [id=" + id + ", name=" + name + ", salary=" + salary                + "]";    }     }

学生类：

public class Student {    private int id;    private String name;    private String grade;         private List
    
      teachers = new ArrayList
     
      ();     public int getId() {        return id;    }     public void setId(int id) {        this.id = id;    }     public String getName() {        return name;    }     public void setName(String name) {        this.name = name;    }     public String getGrade() {        return grade;    }     public void setGrade(String grade) {        this.grade = grade;    }     public List
      
        getTeachers() {        return teachers;    }     public void setTeachers(List
       
         teachers) {        this.teachers = teachers;    }     @Override    public String toString() {        return "Student [id=" + id + ", name=" + name + ", grade=" + grade                + "]";    }     }

实现：

public class TeacherDaoImpl {    private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());    public void saveTeacher(Teacher t){        try{        //保存老师的基本信息            qr.update("insert into teachers values(?,?,?)", t.getId(),t.getName(),t.getSalary());        //查看老师有没有关联的学生信息            List
    
      students = t.getStudents();        //如果有：遍历            for(Student s:students){                //先查询学生信息是否已经存在                Student dbStudent = qr.query("select * from students where id=?", new BeanHandler
     
      (Student.class),s.getId());                    //不存在：插入学生信息                if(dbStudent==null){                    qr.update("insert into students values (?,?,?)", s.getId(),s.getName(),s.getGrade());                }                //在第三方表中建立关联                qr.update("insert into teachers_students values(?,?)", t.getId(),s.getId());            }        }catch(Exception e){            throw new RuntimeException(e);        }    }    public Teacher findTeacherById(int teacherId){        try{            Teacher t = qr.query("select * from teachers where id=?", new BeanHandler
      
       (Teacher.class),teacherId);            if(t!=null){//              String sql = "select * from students where id in (select s_id from teachers_students where t_id=?)";//              String sql = "select * from students s,teachers_students ts where s.id=ts.s_id and ts.t_id=?";                String sql = "select * from students s inner join teachers_students ts on s.id=ts.s_id where ts.t_id=?";                List
       
         students = qr.query(sql, new BeanListHandler
        
         (Student.class),teacherId);                t.setStudents(students);            }            return t;        }catch(Exception e){            throw new RuntimeException(e);        }    }}

测试：

public class TeacherDaoImplTest {    private TeacherDaoImpl dao = new TeacherDaoImpl();    @Test    public void testSaveTeacher() {                 Teacher t1 = new Teacher();        t1.setId(1);        t1.setName("任瞳");        t1.setSalary(10000);                 Teacher t2 = new Teacher();        t2.setId(2);        t2.setName("王昭珽");        t2.setSalary(11000);                          Student s1 = new Student();        s1.setId(1);        s1.setName("张新朋");        s1.setGrade("A");                 Student s2 = new Student();        s2.setId(2);        s2.setName("张湾");        s2.setGrade("A");                 //建立关系        t1.getStudents().add(s1);        t1.getStudents().add(s2);                 t2.getStudents().add(s1);        t2.getStudents().add(s2);                 dao.saveTeacher(t1);        dao.saveTeacher(t2);                                        }     @Test    public void testFindTeacherById() {        Teacher t = dao.findTeacherById(2);        System.out.println(t);        for(Student s:t.getStudents())            System.out.println(s);    } }

3、一对一 Person 和身份证

musql 表创建 sql语句

create table persons(    id int primary key,    name varchar(100));create table id_card(    id int primary key,    num varchar(100),    constraint person_id_fk foreign key(id) references persons(id));

Person类：

//粗粒度：表的定义应该粗。少//细粒度：类的定义尽量的细。多public class Person {    private int id;    private String name;         private IdCard idcard;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public IdCard getIdcard() {        return idcard;    }    public void setIdcard(IdCard idcard) {        this.idcard = idcard;    }     }

IdCard类：

public class IdCard {    private int id;    private String num;         private Person person;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getNum() {        return num;    }    public void setNum(String num) {        this.num = num;    }    public Person getPerson() {        return person;    }    public void setPerson(Person person) {        this.person = person;    }     }

实现：

public class PersonDaoImpl {    private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());         public void savePerson(Person p){        try{            qr.update("insert into persons values(?,?)", p.getId(),p.getName());            IdCard idcard = p.getIdcard();            if(idcard!=null){                qr.update("insert into id_card (id,num) values (?,?)", p.getId(),idcard.getNum());            }        }catch(Exception e){            throw new RuntimeException(e);        }    }    //查询人信息是，要不要查对应的idcard呢？ 建议查出来。    public Person findPersonById(int personId){        try{            Person p = qr.query("select * from persons where id=?", new BeanHandler
    
     (Person.class),personId);            if(p!=null){                IdCard idcard = qr.query("select * from id_card where id=?",  new BeanHandler
     
      (IdCard.class),personId);                p.setIdcard(idcard);            }            return p;        }catch(Exception e){            throw new RuntimeException(e);        }    }}

测试：

public class PersonDaoImplTest {    private PersonDaoImpl dao = new PersonDaoImpl();    @Test    public void testSavePerson() {        Person p = new Person();        p.setId(1);        p.setName("韦文停");                 IdCard idcard = new IdCard();        idcard.setNum("4101");                 //建立关系        p.setIdcard(idcard);                 dao.savePerson(p);    }     @Test    public void testFindPersonById() {        Person p = dao.findPersonById(1);        System.out.println(p.getName()+"身份证号："+p.getIdcard().getNum());    } }