JDBC框架技术

JDBC框架技术

POJO类

POJO类就是简单的、基本的类
这个类有自己的属性，有setter和getter方法
这样的类我们叫做POJO类

在与数据库交互过程中，我们需要写POJO类来对应数据表中每一列的属性。
举个例子：
现在有个数据库表格，属性如下：
student

我们对应的POJO类就要这样写
student.java

package my.db3; import af.sql.annotation.AFCOLUMNS; 
import af.sql.annotation.AFTABLE; 
import java.util.Date; @AFTABLE(name="student")  
@AFCOLUMNS() 
public class Student 
{ public Integer id ; public String name ; public Boolean sex ; public String phone ; public Date birthday ; public void setId(Integer id){this.id=id;}public Integer getId(){return this.id;}public void setName(String name){this.name=name;}public String getName(){return this.name;}public void setSex(Boolean sex){this.sex=sex;}public Boolean getSex(){return this.sex;}public void setPhone(String phone){this.phone=phone;}public String getPhone(){return this.phone;}public void setBirthday(Date birthday){this.birthday=birthday;}public Date getBirthday(){return this.birthday;}} 

总结：

1. POJO类中的属性要和数据库表格一致
2. 一定要用封装类不能用简化类（用integer不用int，用Double不用double）
3. POJO类各个属性都要有setter和getter方法

如果你觉得写POJO类麻烦，你也可以用下我授业恩师（邵发）写的POJO生成器
POJO生成器会帮你生成这些POJO类，按照操作一步一步来就好

JDBC框架

这个JDBC框架十分强大
一般的JDBC返回的是结果集，而我们JDBC框架可以直接返回对象
这意味着什么？意味着方便
因为Java语言是面向对象开发语言，直接返回对象我们可以很方便的取用数据
这里我们有一个JDBC框架可以提供大家下载使用 JDBC框架
放在src文件夹下面就可以使用
然后我们应用几个例子：
在应用之前，我们还是给出我们的数据库
这些sql语句运行下就有数据库了

/*
SQLyog Ultimate v11.24 (32 bit)
MySQL - 5.6.39 : Database - af_school
*********************************************************************
*//*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`af_school` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `af_school`;/*Table structure for table `exam` */DROP TABLE IF EXISTS `exam`;CREATE TABLE `exam` (`id` int(11) NOT NULL COMMENT '学号',`chinese` int(11) DEFAULT NULL COMMENT '语文成绩',`english` int(11) DEFAULT NULL COMMENT '英语成绩',`math` int(11) DEFAULT NULL COMMENT '数学成线',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `exam` */insert  into `exam`(`id`,`chinese`,`english`,`math`) values (20180001,89,90,98),(20180002,78,82,93),(20180003,90,73,95),(20180004,88,98,83),(20180005,96,79,75),(20180006,77,98,82);/*Table structure for table `leave_event` */DROP TABLE IF EXISTS `leave_event`;CREATE TABLE `leave_event` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '请假记录ID',`stuId` int(11) NOT NULL COMMENT '学生ID',`daysFrom` date DEFAULT NULL COMMENT '哪天开始',`daysTo` date DEFAULT NULL COMMENT '哪天结束',`type` tinyint(4) DEFAULT NULL COMMENT '类型，0病假，1事假',`reason` varchar(256) DEFAULT NULL COMMENT '事由',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;/*Data for the table `leave_event` */insert  into `leave_event`(`id`,`stuId`,`daysFrom`,`daysTo`,`type`,`reason`) values (1,20180001,'2018-01-02','2018-01-03',0,'感冒'),(2,20180001,'2018-03-06','2018-03-08',0,'发烧'),(3,20180003,'2018-03-01','2018-03-07',1,'出国旅游'),(4,20180005,'2018-03-07','2018-03-07',1,'家里有事'),(5,20180003,'2018-03-17','2018-03-18',0,'不舒服'),(6,20180004,'2018-03-24','2018-03-30',2,'出国'),(7,20180004,'2018-03-24','2018-03-30',2,'出国'),(8,20180004,'2018-03-24','2018-03-30',2,'出国');/*Table structure for table `student` */DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`id` int(11) NOT NULL COMMENT '学号',`name` varchar(32) NOT NULL COMMENT '姓名',`sex` tinyint(1) DEFAULT NULL COMMENT '性别',`phone` varchar(16) DEFAULT '13800000000' COMMENT '手机号',`birthday` date DEFAULT NULL COMMENT '生日',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;/*Data for the table `student` */insert  into `student`(`id`,`name`,`sex`,`phone`,`birthday`) values (20180001,'盖聂',1,'1409900089','1982-03-09'),(20180002,'卫庄',1,'1282399999','1993-10-01'),(20180003,'张良',1,NULL,'1996-03-11'),(20180004,'伏念',1,NULL,'1982-09-24'),(20180005,'颜路',1,'13699292899','1983-11-21'),(20180006,'赤练',0,'13819289890','1998-03-12'),(20180007,'端木蓉',0,'13800000000','1978-05-12'),(20180008,'盗跖',1,'13410012908','1993-09-10'),(20180009,'白凤',1,'13509890090','1994-04-20'),(20180010,'天明',1,'18799891829','2002-04-19'),(20180011,'月儿',0,'13882938990','2003-06-10');/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

有了数据库之后，我们使用下我们的JDBC框架
1.自动查询记录，并返回POJO对象–>student
直接返回student对象，这是个好东西。方便面向对象开发
POJO 对象Student
Student.java

package my;import java.util.Date;/* POJO类，与数据库表中的字段一一对应 * 1 类名与表名一致* 2 属性与表字段一致*   - 属性的名称与表字段名相同*   - 属性的类型与表字段属性一致，且使用包装类型，如 Long, Integer等* 3 生成 Getter/Setter方法*/
public class Student
{public Integer id;   public String name;public Boolean sex;public String phone;public Date birthday;public Integer getId(){return id;}public void setId(Integer id){this.id = id;}public String getName(){return name;}public void setName(String name){this.name = name;}public Boolean getSex(){return sex;}public void setSex(Boolean sex){this.sex = sex;}public String getPhone(){return phone;}public void setPhone(String phone){this.phone = phone;}public Date getBirthday(){return birthday;}public void setBirthday(Date birthday){this.birthday = birthday;}}

JDBC框架返回查询后的Student对象
Test.java

package my;import java.util.List;
import mysql.AfSqlConnection;public class Test
{public static void testQuery() throws Exception{// 连接MySQL服务器AfSqlConnection conn = new AfSqlConnection();t("127.0.0.1", 3306, "af_school", "root", "");// 简化查询String sql = "SELECT *  FROM student";List<Student> rows = conn.query(sql, Student.class);for(Student stu: rows){System.out.println(stu.id + "t" + stu.name + ", " + stu.birthday);}conn.close();System.out.println("关闭连接!");}public static void main(String[] args){		try{testQuery();} catch (Exception e){// TODO Auto-generated catch blocke.printStackTrace();}}}

2.POJO对象直接插入数据表
直接把LeaveEvent作为对象插入到表格中，很方便
POJO类
LeaveEvent.java

package my;import java.util.Date;import mysql.annotation.AFCOLUMNS;
import mysql.annotation.AFTABLE;@AFTABLE(name="leave_event")
@AFCOLUMNS(generated="id")
public class LeaveEvent
{public Long id;public Integer stuId;public Date daysFrom;public Date daysTo;public Byte type;public String reason;public Long getId(){return id;}public void setId(Long id){this.id = id;}public Integer getStuId(){return stuId;}public void setStuId(Integer stuId){this.stuId = stuId;}public Date getDaysFrom(){return daysFrom;}public void setDaysFrom(Date daysFrom){this.daysFrom = daysFrom;}public Date getDaysTo(){return daysTo;}public void setDaysTo(Date daysTo){this.daysTo = daysTo;}public Byte getType(){return type;}public void setType(Byte type){pe = type;}public String getReason(){return reason;}public void setReason(String reason){ason = reason;}}

JDBC框架将对象直接插入列表
Test.java

package my;DateFormat;import mysql.AfSql;
import mysql.AfSqlConnection;public class Test
{public static void testInsert() throws Exception{// 连接MySQL服务器AfSqlConnection conn = new AfSqlConnection();t("127.0.0.1", 3306, "af_school", "root", "");// 日期格式化工具DateFormat df = AfSql.dateFormat();// 创建POJO对象并赋值LeaveEvent row = new LeaveEvent();row.setStuId(20190001);row.setDaysFrom( df.parse("2019-07-05"));row.setDaysTo( df.parse("2019-07-10"));row.setType((byte)1);row.setReason("休息");// 将POJO插入到数据库conn.insert( row );System.out.println("主键ID=" + Id());conn.close();System.out.println("关闭连接!");}public static void main(String[] args){		try{testInsert();} catch (Exception e){// TODO Auto-generated catch blocke.printStackTrace();}}}

3.返回数据表中一条记录为POJO对象
返回其中一条记录，返回类型为POJO对象
比如我想查询学号为20180001的学生对象
我就可以用JDBC框架解决问题
返回这个对象，用Student承接然后再慢慢操作

POJO类Student
Student.java

package my;import java.util.Date;/* POJO类，与数据库表中的字段一一对应 * 1 类名与表名一致* 2 属性与表字段一致*   - 属性的名称与表字段名相同*   - 属性的类型与表字段属性一致，且使用包装类型，如 Long, Integer等* 3 生成 Getter/Setter方法*/
public class Student
{public Integer id;   public String name;public Boolean sex;public String phone;public Date birthday;public Integer getId(){return id;}public void setId(Integer id){this.id = id;}public String getName(){return name;}public void setName(String name){this.name = name;}public Boolean getSex(){return sex;}public void setSex(Boolean sex){this.sex = sex;}public String getPhone(){return phone;}public void setPhone(String phone){this.phone = phone;}public Date getBirthday(){return birthday;}public void setBirthday(Date birthday){this.birthday = birthday;}}

JDBC框架获取一条记录为Student对象
Test.java

package my;import mysql.AfSqlConnection;public class Test
{public static void testQuery() throws Exception{// 连接MySQL服务器AfSqlConnection conn = new AfSqlConnection();t("127.0.0.1", 3306, "af_school", "root", "");// 查询并获取单条记录		String sql = "select * from student where id=20180001";Student stu = ((sql, Student.class);if(stu != null){System.out.println(stu.id + "t" + stu.name + ", " + stu.birthday);}conn.close();System.out.println("关闭连接!");}public static void main(String[] args){		try{testQuery();} catch (Exception e){// TODO Auto-generated catch blocke.printStackTrace();}}}