研究了常用的Java基本数据类型在mysql和oracle数据库的映射类型。
1.常见数据类型在Mysql数据库的映射
实体:
packagecn.qlq.domain;importjava.sql.Time;importjava.util.Date;public classTestType {privateLong id;privateInteger age;privateCharacter sex;privateBoolean isPerson;privateDate birth;privateTime birthTime;privateString name;publicLong getId() {returnid;
}public voidsetId(Long id) {this.id =id;
}publicInteger getAge() {returnage;
}public voidsetAge(Integer age) {this.age =age;
}publicCharacter getSex() {returnsex;
}public voidsetSex(Character sex) {this.sex =sex;
}publicBoolean getIsPerson() {returnisPerson;
}public voidsetIsPerson(Boolean isPerson) {this.isPerson =isPerson;
}publicDate getBirth() {returnbirth;
}public voidsetBirth(Date birth) {this.birth =birth;
}publicTime getBirthTime() {returnbirthTime;
}public voidsetBirthTime(Time birthTime) {this.birthTime =birthTime;
}publicString getName() {returnname;
}public voidsetName(String name) {this.name =name;
}
@OverridepublicString toString() {return "TestType [id=" + id + ", age=" + age + ", sex=" + sex + ", isPerson=" + isPerson + ", birth=" +birth+ ", birthTime=" + birthTime + ", name=" + name + "]";
}
}
xml:
/p>
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
Mysql映射的类型:
mysql> desctesttype;+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| age | int(11) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| isPerson | bit(1) | YES | | NULL | |
| birth | datetime | YES | | NULL | |
| birthTime | time | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)
结果:
Long------------------------------bigint
Integer----------------------- int
Character--------------------- char
Bolean---------------------------bit
java.util.Date;--------------datetime
java.sql.Time;------------------time
String----------------------------varchar(255)
插入数据:
public static voidmain(String[] args) {//3.3以及之前的版本构建会话工厂对象//SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();//5.0之后获取SessionFactory//创建服务注册对象
ServiceRegistry serviceRegistry = newStandardServiceRegistryBuilder().configure().build();//创建会话工厂对象
SessionFactory sessionFactory = newMetadataSources(serviceRegistry).buildMetadata().buildSessionFactory();//获取session对象
Session session =sessionFactory.openSession();//开启事务
Transaction tx =session.beginTransaction();//保存对象
TestType t1 = newTestType();
TestType t2= newTestType();
t1.setAge(22);
t2.setAge(23);
t1.setName("zhangsan");
t2.setName("zhangsanhaha");
t1.setSex(‘1‘);
t2.setSex(‘2‘);
t1.setBirth(newDate());
t2.setBirth(newDate());
t1.setIsPerson(true);
t2.setIsPerson(false);
session.save(t1);
session.save(t2);
tx.commit();//关闭流
session.close();
sessionFactory.close();
}
结果:
mysql> select * fromtesttype;+----+------+------+----------+---------------------+-----------+--------------+
| id | age | sex | isPerson | birth | birthTime | name |
+----+------+------+----------+---------------------+-----------+--------------+
| 1 | 22 | 1 | | 2018-08-10 22:39:19 | NULL | zhangsan |
| 2 | 23 | 2 | | 2018-08-10 22:39:19 | NULL | zhangsanhaha |
+----+------+------+----------+---------------------+-----------+--------------+
2 rows in set (0.00 sec)
查询
(1)基本查询:
@Test//HQL查询所有数据
public voidfun1() {//1 获得session
Session session =HibernateUtil.openSession();//2.书写HQL语句//String hql = "from cn.qlq.domain.Customer";//from 类名全路径
String hql = "from TestType";//如果整个项目中只有这一个类名可以直接写名字//3.根据hql创建查询对象
Query query =session.createQuery(hql);//4.根据查询对象获取查询结果
List list =query.list();
System.out.println(list);
}
结果:
[TestType [id=1, age=22, sex=1, isPerson=true, birth=2018-08-10 22:39:19.0, birthTime=null, name=zhangsan], TestType [id=2, age=23, sex=2, isPerson=false, birth=2018-08-10 22:39:19.0, birthTime=null, name=zhangsanhaha]]
(2)条件查询:----针对上面的类型进行条件查询
@Test//HQL查询所有数据
public voidfun1() {//1 获得session
Session session =HibernateUtil.openSession();//2.书写HQL语句//String hql = "from cn.qlq.domain.Customer";//from 类名全路径
String hql = "from TestType where age = 22 and sex = 1 and isPerson = true and name = ‘zhangsan‘ and birth like ‘2018-08-10%‘";//如果整个项目中只有这一个类名可以直接写名字//3.根据hql创建查询对象
Query query =session.createQuery(hql);//4.根据查询对象获取查询结果
List list =query.list();
System.out.println(list);
}
结果:
selecttesttype0_.idasid1_0_,
testtype0_.ageasage2_0_,
testtype0_.sexassex3_0_,
testtype0_.isPersonasisPerson4_0_,
testtype0_.birthasbirth5_0_,
testtype0_.birthTimeasbirthTim6_0_,
testtype0_.nameasname7_0_fromtesttype testtype0_wheretesttype0_.age=22
and testtype0_.sex=1
and testtype0_.isPerson=1
and testtype0_.name=‘zhangsan‘
and(
testtype0_.birthlike ‘2018-08-10%‘)[TestType [id=1, age=22, sex=1, isPerson=true, birth=2018-08-10 22:39:19.0, birthTime=null, name=zhangsan]]
补充:Mysql的boolean类型也可以用true_false表示，数据类型会变为char(1)，存的是T和F:
2.常见数据类型在Oracle数据库的映射
Oracle映射上面直接映射会报错，解决办法: 将boolean映射为hibernate的true_false (原理都是在数据库存0或者1，0为false，1为true)
第一种: boolean映射为yes_no
/p>
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
结果:
总结:
Long------------------------------number
Integer----------------------- number
Character--------------------- char
Bolean---------------------------char
java.util.Date;--------------date
java.sql.Time;------------------date
String----------------------------varchar(255)
添加数据:
packagecn.qlq.util;importjava.util.Date;importorg.hibernate.Session;importorg.hibernate.SessionFactory;importorg.hibernate.Transaction;importorg.hibernate.boot.MetadataSources;importorg.hibernate.boot.registry.StandardServiceRegistryBuilder;importorg.hibernate.service.ServiceRegistry;importcn.qlq.domain.TestType;public classTestSave {public static voidmain(String[] args) {//3.3以及之前的版本构建会话工厂对象//SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();//5.0之后获取SessionFactory//创建服务注册对象
ServiceRegistry serviceRegistry = newStandardServiceRegistryBuilder().configure().build();//创建会话工厂对象
SessionFactory sessionFactory = newMetadataSources(serviceRegistry).buildMetadata().buildSessionFactory();//获取session对象
Session session =sessionFactory.openSession();//开启事务
Transaction tx =session.beginTransaction();//保存对象
TestType t1 = newTestType();
TestType t2= newTestType();
t1.setAge(22);
t2.setAge(23);
t1.setName("zhangsan");
t2.setName("zhangsanhaha");
t1.setSex(‘1‘);
t2.setSex(‘2‘);
t1.setBirth(newDate());
t2.setBirth(newDate());
t1.setIsPerson(true);
t2.setIsPerson(false);
session.save(t1);
session.save(t2);
tx.commit();//关闭流
session.close();
sessionFactory.close();
}
}
结果:
查询所有:
@Test//HQL查询所有数据
public voidfun1() {//1 获得session
Session session =HibernateUtil.openSession();//2.书写HQL语句
String hql = "from cn.qlq.domain.TestType";//from 类名全路径//3.根据hql创建查询对象
Query query =session.createQuery(hql);//4.根据查询对象获取查询结果
List list =query.list();
System.out.println(list);
}
结果:
Hibernate:selecttesttype0_.idasid1_0_,
testtype0_.ageasage2_0_,
testtype0_.sexassex3_0_,
testtype0_.isPersonasisPerson4_0_,
testtype0_.birthasbirth5_0_,
testtype0_.birthTimeasbirthTim6_0_,
testtype0_.nameasname7_0_fromtesttype testtype0_[TestType [id=15, age=22, sex=1, isPerson=true, birth=2018-08-10 23:19:33.0, birthTime=null, name=zhangsan], TestType [id=16, age=23, sex=2, isPerson=false, birth=2018-08-10 23:19:33.0, birthTime=null, name=zhangsanhaha]]
按上面的条件查询:
@Test//HQL查询所有数据
public voidfun1() {//1 获得session
Session session =HibernateUtil.openSession();//2.书写HQL语句
String hql = "from TestType where age = 22 and sex = 1 and isPerson = true and name = ‘zhangsan‘";//如果整个项目中只有这一个类名可以直接写名字//3.根据hql创建查询对象
Query query =session.createQuery(hql);//4.根据查询对象获取查询结果
List list =query.list();
System.out.println(list);
}
结果:(日期不能直接like了)
Hibernate:selecttesttype0_.idasid1_0_,
testtype0_.ageasage2_0_,
testtype0_.sexassex3_0_,
testtype0_.isPersonasisPerson4_0_,
testtype0_.birthasbirth5_0_,
testtype0_.birthTimeasbirthTim6_0_,
testtype0_.nameasname7_0_fromtesttype testtype0_wheretesttype0_.age=22
and testtype0_.sex=1
and testtype0_.isPerson=‘T‘
and testtype0_.name=‘zhangsan‘
[TestType [id=15, age=22, sex=1, isPerson=true, birth=2018-08-10 23:19:33.0, birthTime=null, name=zhangsan]]
总结:
对于mysql和oracle的boolean的通用类型就是true_false，hibernate会将字段类型设置为char(1)，然后true的时候存T，false的时候存F。
