sql05

三层架构

• 代码怎么编写

需求:查询所有的科目信息

导入jar

创建实体类

创建表现层(界面层) 展示数据的

创建业务逻辑层

命名规则  包名:com.xxx.service

类和接口的命名规则: 

     接口: 类名+Service  实现类:类名+Service+Impl

  

/**  * 业务逻辑层接口  * @author Administrator  *  */ public interface SubjectService {     List<Subject> queryAll();   }   /**  * 业务逻辑实现类  * @author Administrator  *  */ public class SubjectServiceImpl implements SubjectService {       private   SubjectDao  subjectdao=new SubjectDaoMySqlImpl();     @Override     public List<Subject> queryAll() {        //返回的是全部的科目信息包含测试数据(后台开发人员维护看的)        List<Subject> subjects=subjectdao.querySubjects();        return subjects;     }   }

 

 

 

 

 

 

 

创建数据访问层层

命名规则  包名:com.xxx.dao

类和接口的命名规则: 

     接口: 类名+Dao  实现类:类名+Dao+Impl

/**  * 数据访问层接口  * @author Administrator  *  */ public interface SubjectDao {       List<Subject> querySubjects();         }     /**  * 从mysql数据库查询数据  * @author Administrator  *  */ public class SubjectDaoMySqlImpl implements SubjectDao {       @Override     public List<Subject> querySubjects() {        //贾琏欲执事-->很繁琐--》使用c3p0进行改写                      return null;     }   }

 

• C3P0改写传统的JDBC

• 怎么使用C3P0

1. 导入jar

1. 添加c3p0的配置文件

 创建source folder的文件夹添加配置文件(配置文件的名字c3p0-config.xml 是固定的,不能瞎写)

<?xml version="1.0" encoding="UTF-8"?> <c3p0-config>     <default-config>        <property name="user">root</property>        <property name="password">root</property>        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/7505test        </property>        <property name="driverClass">com.mysql.jdbc.Driver</property>        <!-- 连接池初始化的时候的连接数 -->        <property name="initialPoolSize">5</property>        <!-- 连接池中连接的最大个数 -->        <property name="maxPoolSize">20</property>        <!-- 用户获得连接Connection的时候，如果有连接就获得，没有就等待的时间，如果超时就报异常 -->        <property name="maxIdleTime">5000</property>        <!-- 连接池中连接的最小个数 -->        <property name="minPoolSize">5</property>     </default-config> </c3p0-config>

 

1. 从连接池中获取数据源

可以编写工具类(专门获取连接对象)

包名L com.xx.utils

import javax.sql.DataSource;   import org.apache.commons.dbutils.QueryRunner;   import com.mchange.v2.c3p0.ComboPooledDataSource;   public class C3P0Util {     //queryRunner DButils框架提供通过C3P0操作数据库的一个对象 private static  QueryRunner  queryRunner;   public static QueryRunner getQueryRunner() {     //dataSource 数据源     DataSource  dataSource=new ComboPooledDataSource();     queryRunner=new QueryRunner(dataSource);         return queryRunner; }     }

1. Dao层通过C3P0实现查询

public class SubjectDaoMySqlImpl implements SubjectDao {       private QueryRunner queryRunner;       @Override     public List<Subject> querySubjects() {        // 贾琏欲执事-->很繁琐--》使用c3p0进行改写        queryRunner = C3P0Util.getQueryRunner();        String sql = "select  * from  `subject` ";        List<Subject> subjects=null;        // 直接将查询的结果转为 对象        try {            //实体类SUbject的属性名需要与列名一致             subjects = queryRunner.query(sql, new BeanListHandler<Subject>(Subject.class));        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }          return subjects;     }   }

 

 

 

 

• C3P0实现增删改查

实现新增

1. 编写表现层

static SubjectService  subjectService=new SubjectServiceImpl(); public static  void  addSUbject() {     Subject subject=new Subject();     subject.setClasshour(44);     subject.setGradeid(1);     subject.setSubjectname("测试");     //调用业务逻辑        int num= subjectService.addSubject(subject);   }

1. 编写业务逻辑层

1. 编写dao层

@Override     public int addSubject(Subject subject) {        queryRunner=C3P0Util.getQueryRunner();        String sql="insert into `subject` values(NULL,?,?,?)";        int num=0;        try {        num=   queryRunner.update(sql, subject.getSubjectname(),subject.getClasshour(),subject.getGradeid());        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return num;     }

 

实现删改查

 Dao层

接口

/**  * 数据访问层接口  * @author Administrator  *  */ public interface SubjectDao {       List<Subject> querySubjects();     int  addSubject(Subject subject);         int deleteSubjectByID(int id);     int updateSubject(Subject subject);     //根据id查询     Subject  queryByID(int id);     //查询科目数量     int subjectCount();         }

Dao实现类

public class SubjectDaoMySqlImpl implements SubjectDao {       private QueryRunner queryRunner;       @Override     public List<Subject> querySubjects() {        // 贾琏欲执事-->很繁琐--》使用c3p0进行改写        queryRunner = C3P0Util.getQueryRunner();        String sql = "select  * from  `subject` ";        List<Subject> subjects=null;        // 直接将查询的结果转为 对象        try {            //实体类SUbject的属性名需要与列名一致             subjects = queryRunner.query(sql, new BeanListHandler<Subject>(Subject.class));        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }          return subjects;     }       @Override     public int addSubject(Subject subject) {        queryRunner=C3P0Util.getQueryRunner();        String sql="insert into `subject` values(NULL,?,?,?)";        int num=0;        try {        num=   queryRunner.update(sql, subject.getSubjectname(),subject.getClasshour(),subject.getGradeid());        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return num;     }       @Override     public int deleteSubjectByID(int id) {        queryRunner=C3P0Util.getQueryRunner();        String sql="delete  from  `subject`  where SubjectId=?";        int num=0;         try {        num=   queryRunner.update(sql,id);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }               return num;     }       @Override     public int updateSubject(Subject subject) {        queryRunner=C3P0Util.getQueryRunner();        String sql="update `subject` set SubjectName=?,ClassHour=?, GradeId=? where SubjectId=?";        int num=0;        try {            num=queryRunner.update(sql,subject.getSubjectname(),subject.getClasshour(),subject.getGradeid(),subject.getSubjectid());        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return num;     }       @Override     public Subject queryByID(int id) {        queryRunner=C3P0Util.getQueryRunner();        String sql="select  * from  `subject`  where SubjectId=?";           Subject subject=null;           try {        subject=   queryRunner.query(sql, new BeanHandler<Subject>(Subject.class),id);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return subject;     }       @Override     public int subjectCount() {        queryRunner=C3P0Util.getQueryRunner();        String sql="select COUNT(*)  from  `subject`";        Long count=0l;               try {            //查询数量 new ScalarHandler(1) 返回的是Long类型        count= (Long) queryRunner.query(sql, new ScalarHandler(1));        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }               return count.intValue();     }   }

 

业务逻辑层接口

/**  * 业务逻辑层接口  * @author Administrator  *  */ public interface SubjectService {     List<Subject> queryAll();     int addSubject(Subject subject);         int deleteSubjectByID(int id);     int updateSubject(Subject subject);     //根据id查询     Subject  queryByID(int id);     //查询科目数量     int subjectCount();   }

 

业务逻辑层实现类

/**  * 业务逻辑实现类  * @author Administrator  *  */ public class SubjectServiceImpl implements SubjectService {       private   SubjectDao  subjectdao=new SubjectDaoMySqlImpl();     @Override     public List<Subject> queryAll() {        //返回的是全部的科目信息包含测试数据(后台开发人员维护看的)        List<Subject> subjects=subjectdao.querySubjects();        return subjects;     }     @Override     public int addSubject(Subject subject) {        int num=subjectdao.addSubject(subject);        return num;     }     @Override     public int deleteSubjectByID(int id) {        // TODO Auto-generated method stub        return  subjectdao.deleteSubjectByID(id);     }     @Override     public int updateSubject(Subject subject) {        // TODO Auto-generated method stub        return  subjectdao.updateSubject(subject);     }     @Override     public Subject queryByID(int id) {        // TODO Auto-generated method stub        return subjectdao.queryByID(id);     }     @Override     public int subjectCount() {        // TODO Auto-generated method stub        return subjectdao.subjectCount();     }   }

测试类

public class Test {     public static void main(String[] args) {          // addSUbject();        // deleteSUbject();        // update();        // queryByID();        queryCount();       }       static SubjectService subjectService = new SubjectServiceImpl();       public static void queryCount() {        int count = subjectService.subjectCount();        System.out.println("科目数量" + count);     }       public static void queryByID() {        Subject subject = subjectService.queryByID(9);        System.out.println(subject);     }       public static void deleteSUbject() {        subjectService.deleteSubjectByID(15);     }       public static void update() {        Subject subject = new Subject();        subject.setSubjectid(16);        subject.setClasshour(44);        subject.setGradeid(1);        subject.setSubjectname("干掉系统");        subjectService.updateSubject(subject);     }       public static void addSUbject() {        Subject subject = new Subject();        subject.setClasshour(44);        subject.setGradeid(1);        subject.setSubjectname("测试");        // 调用业务逻辑        int num = subjectService.addSubject(subject);     }       public static void queryall() {        System.out.println("所有的科目信息为:");          List<Subject> subjects = subjectService.queryAll();        for (Subject subject : subjects) {            System.out.println(subject);        }     }   }

 

 

总结：

1. 三层架构的搭建
2. C3p0实现curd

有3个方法

QueryRunner  curd 的核心

查询的结果是：对象的集合:  queryRunner.query(sql, new BeanListHandler<类名>(类名.class)

查询的结果是一个对象：qRunner.query(sql,new BeanHandler<类名>(类名.class),参数);（sql语句中需要的参数，如果没有就不要..）

查询数量：  Long  num= (Long) qRunner.query(sql,new ScalarHandler(参数),stuno,pwd);//参数写法1编写获取值的索引 写法2 列名

增删改： qRunner.update(sql, 参数1,  参数2......);