第17课 hibernate树形结构(重点)
树形结构：也就是目录结构，有父目录、子目录、文件等信息，而在程序中树形结构只是称为节点。
一棵树有一个根节点，而根节点也有一个或多个子节点，而一个子节点有且仅有一个父节点(当前除根节点外)，而且也存在一个或多个子节点。
也就是说树形结构，重点就是节点，也就是我们需要关心的节点对象。
节点：一个节点有一个ID、一个名称、它所属的父节点(根节点无父节点或为null)，有一个或多的子节点等其它信息。
Hibernate将节点抽取出成实体类，节点相对于父节点是“多对一”映射关系，节点相对于子节点是“一对多”映射关系。
一、 节点实体类：
/** * 节点*/
public class Node {
private int id; //标识符
private String name; //节点名称
private int level; //层次，为了输出设计
private boolean leaf; //是否为叶子节点，这是为了效率设计，可有可无
//父节点：因为多个节点属于一个父节点，因此用hibernate映射关系说是“多对一”
private Node parent;
//子节点：因为一个节点有多个子节点，因此用hibernate映射关系说是“一对多”
private Set children;
public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getName() {return name;}
public void setName(Stringname) { this.name = name;}
public int getLevel() { return level;}
public void setLevel(int level) {this.level = level;}
public boolean isLeaf() {return leaf;}
public void setLeaf(boolean leaf) {this.leaf = leaf;}
public Node getParent() {return parent;}
public void setParent(Nodeparent) {this.parent = parent;}
public SetgetChildren() {return children;}
public void setChildren(Setchildren) {this.children = children;}}
二、 xml方式：映射文件：
<class name="com.wjt276.hibernate.Node" table="t_node">
<id name="id" column="id">
<generator class="native"/>
</id>
<property name="name"/>
<property name="level"/>
<property name="leaf"/>
<!— 一对多：加入一个外键，参照当前表t_node主键， 而属性parent类型为Node，也就是当前类，则会在同一个表中加入这个字段，参照这个表的主键-->
<many-to-one name="parent" column="pid"/>
<!--<set>标签是映射一对多的方式，加入一个外键，参照主键。-->
<set name="children" lazy="extra"inverse="true">
<key column="pid"/>
<one-to-many class="com.wjt276.hibernate.Node"/>
</set>
</class>
三、 annotation注解
因为树型节点所有的数据，在数据库中只是存储在一个表中，而对于实体类来说，节点对子节点来说是一对多的关系，而对于父节点来说是多对一的关系。因此可以在一个实体类中注解。如下
@Entity
public class Node {
private int id; // 标识符
private String name; // 节点名称
private int level; // 层次，为了输出设计
private boolean leaf; // 是否为叶子节点，这是为了效率设计，可有可无
// 父节点：因为多个节点属于一个父节点，因此用hibernate映射关系说是“多对一”
private Node parent;
// 子节点：因为一个节点有多个子节点，因此用hibernate映射关系说是“一对多”
private Set<Node> children = newHashSet<Node>();
@Id
@GeneratedValue
public int getId() {return id;}
@OneToMany(mappedBy="parent")
public Set<Node>getChildren() {return children;}
@ManyToOne
@JoinColumn(name="pid")
public Node getParent() {return parent;}
四、 测试代码：
public class NodeTest extends TestCase {
//测试节点的存在
public void testSave1(){
NodeManage.getInstanse().createNode("F:\\hibernate\\hibernate_training_tree");
}
//测试节点的加载
public void testPrintById(){
NodeManage.getInstanse().printNodeById(1);
}}
五、 相应的类代码：
public classNodeManage {
private static NodeManage nodeManage= newNodeManage();
private NodeManage(){}//因为要使用单例，所以将其构造方法私有化
//向外提供一个接口
public static NodeManage getInstanse(){
return nodeManage;
}
/**
* 创建树
*@param filePath 需要创建树目录的根目录
*/
public void createNode(String dir) {
Session session = null;
try {
session =HibernateUtils.getSession();
session.beginTransaction();
File root = new File(dir);
//因为第一个节点无父节点，因为是null
this.saveNode(root, session, null,0);
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
} finally {
HibernateUtils.closeSession(session);
}}
/**
* 保存节点对象至数据库
*@param file 节点所对应的文件
*@param session session
*@param parent 父节点
*@param level 级别
*/
public void saveNode(File file, Sessionsession, Node parent, int level) {
if (file == null ||!file.exists()){return;}
//如果是文件则返回true,则表示是叶子节点，否则为目录，非叶子节点
boolean isLeaf = file.isFile();
Node node = new Node();
node.setName(file.getName());
node.setLeaf(isLeaf);
node.setLevel(level);
node.setParent(parent);
session.save(node);
//进行循环迭代子目录
File[] subFiles = file.listFiles();
if (subFiles != null &&subFiles.length > 0){
for (int i = 0; i <subFiles.length ; i++){
this.saveNode(subFiles[i],session, node, level + 1);
}}}
/**
* 输出树结构
*@param id
*/
public void printNodeById(int id) {
Session session = null;
try {
session =HibernateUtils.getSession();
session.beginTransaction();
Node node =(Node)session.get(Node.class, 1);
printNode(node);
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
session.getTransaction().rollback();
} finally {
HibernateUtils.closeSession(session);
}
}
private void printNode(Node node) {
if (node == null){ return; }
int level = node.getLevel();
if (level > 0){
for (int i = 0; i < level; i++){
System.out.print(" |");
}
System.out.print("--");
}
System.out.println(node.getName() +(node.isLeaf() ? "" : "[" + node.getChildren().size() +"]"));
Set children = node.getChildren();
for (Iterator iter = children.iterator();iter.hasNext(); ){
Node child = (Node)iter.next();
printNode(child);
}}}
第18课 作业-学生、课程、分数的映射关系
一、 设计
1、 实体类(表)
2、 导航(编程方便)
a) 通过学生 取出 学生所先的课程
b) 但是通过课程 取出 学该课程的 学生不好。学的学生太多
c) 确定编程的方式
3、 可以利用联合主键映射可以，
a) 学生生成一个表
b) 课程生成一个表
c) 再生成一个表，主键是联合主键(学生ID、课程ID) + 学生共生成一个表
4、 也可以利用一对多，多对多 都可以(推荐)
a) 学生生成一个表
b) 课程生成一个表
c) 分数生成一个表，并且有两个外键，分别指向学生、课程表
二、 代码：
* 课程
@Entity
public class Course {
private int id;
private String name;
@Id
@GeneratedValue
public int getId() {return id;}
public void setId(int id) { this.id = id;}
public String getName() {return name;}
public void setName(Stringname) {this.name = name;}}
* 分数
@Entity
@Table(name = "score")
public class Score {
private int id;
private int score;
private Student student;
private Course course;
@Id
@GeneratedValue
public int getId() {return id;}
@ManyToOne
@JoinColumn(name = "student_id")
public StudentgetStudent() {return student;}
@ManyToOne
@JoinColumn(name = "score_id")
public Course getCourse(){ return course;}
public int getScore() { return score;}
public void setScore(int score) {this.score = score;}
public void setStudent(Studentstudent) {this.student = student;}
public void setCourse(Coursecourse) {this.course = course;}
public void setId(int id) { this.id = id;}}
* 学生通过课程可以导航到分数
@Entity
public class Student {
private int id;
private String name;
private Set<Course> courses = newHashSet<Course>();
@Id
@GeneratedValue
public int getId() {return id;}
@ManyToMany
@JoinTable(name = "score", //此表就是Score实体类在数据库生成的表叫score
joinColumns= @JoinColumn(name = "student_id"),
inverseJoinColumns= @JoinColumn(name = "course_id")
)
public Set<Course>getCourses() {return courses;}
public voidsetCourses(Set<Course> courses) {this.courses = courses;}
public void setId(int id) { this.id = id;}
public String getName() {return name;}
public void setName(Stringname) {this.name = name;}}
三、 注意
在Student实体类中的使用的第三方表使用了两个字段，而hibernate会使这两个字段生成联合主键，这并不是我们需要的结果，因为我们需要手动到数据库中修改。这样才可以存储数据，否则数据存储不进去。这可能是hibernate的一个小bu
————————————————————————————————————————————————————
树状结构 （ 重点）
面向对象的模型： 这么去做映射----> 再去做增删改查
package com.demo.hibernate;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
@Entity
public class Org {
private int id;
private String name;
private Set<Org> chirden = new HashSet<Org>();
private Org parent;
@Id
@GeneratedValue
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;
}
// @OneToMany(cascade=CascadeType.ALL,mappedBy="parent",fetch=FetchType.EAGER) //设置对手Org @ManyToOne 的那个字段 即 parent
@OneToMany(cascade=CascadeType.ALL,mappedBy="parent") //设置对手Org @ManyToOne 的那个字段 即 parent
public Set<Org> getChirden() {
return chirden;
}
public void setChirden(Set<Org> chirden) {
this.chirden = chirden;
}
@ManyToOne
@JoinColumn(name="parent_id")
public Org getParent() {
return parent;
}
public void setParent(Org parent) {
this.parent = parent;
}
}
@Test
public void testSaveOrg() {
Org o = new Org();
o.setName("总公司");
Org o1 = new Org();
o1.setName("分公司1");
Org o2 = new Org();
o2.setName("分公司2");
Org o11 = new Org();
o11.setName("分公司1下部门1");
Org o12 = new Org();
o12.setName("分公司1下部门2");
o.getChirden().add(o1);
o.getChirden().add(o2);
o1.getChirden().add(o11);
o1.getChirden().add(o12);
o11.setParent(o1);
o12.setParent(o1);
o1.setParent(o);
o2.setParent(o);
Session session = sf.getCurrentSession();
session.beginTransaction();
/*session.save(o); 第一种存储
session.save(o1);
session.save(o2);
session.save(o11);
session.save(o12);*/
//第二种存储 @OneToMany(cascade=CascadeType.ALL,mappedBy="parent") 用级联
session.save(o);
session.getTransaction().commit();
}
@Test
public void testLoad() {
testSaveOrg();
Session session = sf.getCurrentSession();
session.beginTransaction();
Org o = (Org)session.load(Org.class, 1);
//fetch=FetchType.EAGER 读取的时候可以 但是打印还是要写递归,如果这个树非常大就不合适用EAGER了，树大用ajax，远程异步的访问
print(o,0); //0 是从根节点开始读取
session.getTransaction().commit();
}
// 树的递归
private void print(Org o,int level) {
String preStr = "";
for(int i=0; i<level; i++) {
preStr += "====";
}
System.out.println(preStr + o.getName());
for(Org child : o.getChirden()) {
print(child,level+1);
}
}
作业-学生、课程、分数的映射关系
一、 设计
1、 实体类(表)
2、 导航(编程方便)
a) 通过学生 取出 学生所先的课程
b) 但是通过课程 取出 学该课程的 学生不好。学的学生太多
c) 确定编程的方式
3、 可以利用联合主键映射可以，
a) 学生生成一个表
b) 课程生成一个表
c) 再生成一个表，主键是联合主键(学生ID、课程ID) + 学生共生成一个表
4、 也可以利用一对多，多对多 都可以(推荐)
a) 学生生成一个表
b) 课程生成一个表
c) 分数生成一个表，并且有两个外键，分别指向学生、课程表
————————————————————————————————————————————————————————————————
create table score (
id int primary key auto_increment,
score int,
course_id int references course(id),
student_id int references student(id)
)package com.demo.hibernate;
import java.util.HashSet;
import java.util.Set;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
@Entity
public class Student {
private int id;
private String name;
private Set<Course> courses = new HashSet<Course>();
@ManyToMany
@JoinTable(name = "score",
joinColumns = @JoinColumn(name="student_id"),
inverseJoinColumns = @JoinColumn(name="course_id")
)
/* 生成表语句有问题
* 手动插入
* create table score (
id int primary key auto_increment,
score int,
course_id int references course(id),
student_id int references student(id)
)
*
* */
public Set<Course> getCourses() {
return courses;
}
public void setCourses(Set<Course> courses) {
this.courses = courses;
}
@Id
@GeneratedValue
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;
}
}
package com.demo.hibernate;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
@Entity
@Table(name="score")
public class Score {
private int id;
private int score;
private Student student;
private Course course;
@Id
@GeneratedValue
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getScore() {
return score;
}
public void setScore(int score) {
this.score = score;
}
@ManyToOne
@JoinColumn(name="student_id")
public Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
@ManyToOne
@JoinColumn(name="course_id")
public Course getCourse() {
return course;
}
public void setCourse(Course course) {
this.course = course;
}
}
package com.demo.hibernate;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
@Entity
public class Course {
private int id;
private String name;
@Id
@GeneratedValue
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;
}
}
@Test
public void testSave() {
Student s = new Student();
s.setName("lishi");
Course c = new Course();
c.setName("java");
Score score = new Score();
score.setScore(100);
score.setStudent(s);
score.setCourse(c);
Session session = sf.getCurrentSession();
session.beginTransaction();
session.save(s);
session.save(c);
session.save(score);
session.getTransaction().commit();
}
@Test
public void testLoad() {
testSave();
Session session = sf.getCurrentSession();
session.beginTransaction();
Student s = (Student)session.load(Student.class, 1);
for(Course c : s.getCourses()) {
System.out.println(c.getName());
}
session.getTransaction().commit();
}第19课 Hibernate查询(Query Language)
HQL VS EJBQL
一、 Hibernate可以使用的查询语言
1、 NativeSQL：本地语言(数据库自己的SQL语句)
2、 HQL ：Hibernate自带的查询语句，可以使用HQL语言，转换成具体的方言
3、 EJBQL：JPQL 1.0，可以认为是HQL的一个子节(重点)
4、 QBC：Query By Cretira
5、 QBE：Query By Example
注意：上面的功能是从1至5的比较，1的功能最大，5的功能最小
二、 实例一
1、 版块
/** 版块*/
@Entity
public class Category {
private int id;
private String name;
@Id
@GeneratedValue
public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getName() {return name;}
public void setName(Stringname) {this.name = name;}}
2、 主题
/**主题*/
@Entity
public class Topic {
private int id;
private String title;
private Category category;
//private Category category2;
private Date createDate;
public DategetCreateDate() {return createDate;}
public void setCreateDate(DatecreateDate) {this.createDate = createDate;}
@ManyToOne(fetch=FetchType.LAZY)
public CategorygetCategory() { return category;}
public voidsetCategory(Category category) {this.category = category; }
@Id
@GeneratedValue
public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getTitle(){return title;}
public void setTitle(Stringtitle) {this.title = title;}}
3、 主题回复
/**主题回复*/
@Entity
public class Msg {
private int id;
private String cont;
private Topic topic;
@ManyToOne
public Topic getTopic() {return topic;}
public void setTopic(Topictopic) {this.topic = topic;}
@Id
@GeneratedValue
public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getCont() {return cont;}
public void setCont(Stringcont) {this.cont = cont;}}
4、 临时类
/**临时类 */
public class MsgInfo { //VO DTO Value Object username p1 p2UserInfo->User->DB
private int id;
private String cont;
private String topicName;
private String categoryName;
public MsgInfo(int id, String cont,String topicName, String categoryName) {
super();
this.id = id;
this.cont = cont;
this.topicName = topicName;
this.categoryName =categoryName;
}
public StringgetTopicName() {return topicName;}
public voidsetTopicName(String topicName) {this.topicName = topicName;}
public StringgetCategoryName() {return categoryName;}
public voidsetCategoryName(String categoryName) {
this.categoryName =categoryName;
}
public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getCont() {return cont;}
public void setCont(Stringcont) {this.cont = cont;}}
三、 实体一测试代码：
//初始化数据
@Test
public void testSave() {
Sessionsession = sf.openSession();
session.beginTransaction();
for(int i=0; i<10; i++){
Categoryc = new Category();
c.setName("c" + i);
session.save(c);
}
for(int i=0; i<10; i++){
Categoryc = new Category();
c.setId(1);
Topic t= new Topic();
t.setCategory(c);
t.setTitle("t" + i);
t.setCreateDate(new Date());
session.save(t);
}
for(int i=0; i<10; i++){
Topic t= new Topic();
t.setId(1);
Msg m = new Msg();
m.setCont("m" + i);
m.setTopic(t);
session.save(m);
}
session.getTransaction().commit();
session.close();
}
/** QL:from + 实体类名称 */
Query q = session.createQuery("from Category");
List<Category>categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
/* 可以为实体类起个别名，然后使用它 */
Query q =session.createQuery("from Category c wherec.name > 'c5'");
List<Category>categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
//排序
Query q =session.createQuery("from Category c orderby c.name desc");
List<Category>categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
* 为加载上来的对象属性起别名，还可以使用
Query q =session.createQuery("select distinct c fromCategory c order by c.name desc");
List<Category>categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
/*Query q = session.createQuery("from Category c where c.id > :minand c.id < :max");
//q.setParameter("min",2);
//q.setParameter("max",8);
q.setInteger("min",2);
q.setInteger("max",8);*/
* 可以使用冒号(：),作为占位符，来接受参数使用。如下(链式编程)
Query q =session.createQuery("from Category c wherec.id > :min and c.id < :max")
.setInteger("min", 2)
.setInteger("max", 8);
List<Category>categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getId()+ "-" + c.getName());
}
Query q =session.createQuery("from Category c wherec.id > ? and c.id < ?");
q.setParameter(0, 2)
.setParameter(1, 8);
// q.setParameter(1, 8);
List<Category>categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getId()+ "-" + c.getName());
}
//分页
Query q =session.createQuery("from Category c orderby c.name desc");
q.setMaxResults(4);//每页显示的最大记录数
q.setFirstResult(2);//从第几条开始显示，从0开始
List<Category>categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getId()+ "-" + c.getName());
}
Query q =session.createQuery("select c.id, c.name from Category c order by c.namedesc");
List<Object[]>categories = (List<Object[]>)q.list();
for(Object[] o : categories) {
System.out.println(o[0] + "-" + o[1]);
}
//设定fetch type 为lazy后将不会有第二条sql语句
Query q =session.createQuery("from Topic t where t.category.id= 1");
List<Topic>topics = (List<Topic>)q.list();
for(Topic t : topics) {
System.out.println(t.getTitle());
//System.out.println(t.getCategory().getName());
}
//设定fetch type 为lazy后将不会有第二条sql语句
Query q =session.createQuery("from Topic t wheret.category.id = 1");
List<Topic>topics = (List<Topic>)q.list();
for(Topic t : topics) {
System.out.println(t.getTitle());
}
Query q =session.createQuery("from Msg m wherem.topic.category.id = 1");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getCont());
}
//了解即可
//VO Value Object
//DTO data transfer object
Query q =session.createQuery("select newcom.bjsxt.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name)from Msg");
for(Object o : q.list()) {
MsgInfo m = (MsgInfo)o;
System.out.println(m.getCont());
}
//动手测试left right join
//为什么不能直接写Category名，而必须写t.category
//因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接
Query q =session.createQuery("select t.title, c.namefrom Topic t join t.category c "); //join Category c
for(Object o : q.list()) {
Object[] m = (Object[])o;
System.out.println(m[0] + "-" + m[1]);
}
//学习使用uniqueResult
Query q =session.createQuery("from Msg m where m =:MsgToSearch "); //不重要
Msgm = new Msg();
m.setId(1);
q.setParameter("MsgToSearch", m);
Msg mResult =(Msg)q.uniqueResult();
System.out.println(mResult.getCont());
Query q =session.createQuery("select count(*) fromMsg m");
long count = (Long)q.uniqueResult();
System.out.println(count);
Query q = session.createQuery("select max(m.id), min(m.id), avg(m.id), sum(m.id)from Msg m");
Object[] o =(Object[])q.uniqueResult();
System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]);
Query q = session.createQuery("from Msg m where m.id between 3 and 5");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getId()+ "-" + m.getCont());
}
Query q =session.createQuery("from Msg m where m.idin (3,4, 5)");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getId()+ "-" + m.getCont());
}
//is null 与 is notnull
Query q =session.createQuery("from Msg m where m.contis not null");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getId()+ "-" + m.getCont());
}
四、 实例二
注意：实体二，实体类，只是在实体一的基础上修改了Topic类，添加了多对一的关联关系
@Entity
@NamedQueries({
@NamedQuery(name="topic.selectCertainTopic", query="from Topic t where t.id = :id")
})
/*@NamedNativeQueries(
{
@NamedNativeQuery(name="topic.select2_5Topic",query="select * from topic limit 2, 5")
})*/
public class Topic {
private int id;
private String title;
private Category category;
private Date createDate;
private List<Msg> msgs = newArrayList<Msg>();
@OneToMany(mappedBy="topic")
public List<Msg> getMsgs() {return msgs;}
public void setMsgs(List<Msg> msgs) {this.msgs = msgs;}
public Date getCreateDate() {return createDate;}
public void setCreateDate(Date createDate) {this.createDate = createDate; }
@ManyToOne(fetch=FetchType.LAZY)
public Category getCategory() { return category;}
public void setCategory(Category category) {this.category = category;}
@Id
@GeneratedValue
public int getId() {return id;}
public void setId(int id) {this.id = id;}
public String getTitle() { return title;}
public void setTitle(String title) {this.title = title;}}
五、 实例二测试代码
注意：测试数据是实例一的测试数据
//is empty and is not empty
Query q =session.createQuery("from Topic t wheret.msgs is empty");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getId()+ "-" + t.getTitle());
}
Query q =session.createQuery("from Topic t wheret.title like '%5'");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getId()+ "-" + t.getTitle());
}
Query q =session.createQuery("from Topic t wheret.title like '_5'");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getId()+ "-" + t.getTitle());
}
//不重要
Query q =session.createQuery("selectlower(t.title)," +
"upper(t.title)," +
"trim(t.title)," +
"concat(t.title,'***')," +
"length(t.title)" +
" from Topict ");
for(Object o : q.list()) {
Object[] arr =(Object[])o;
System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] + "-" + arr[3] + "-" + arr[4] + "-");
}
Query q =session.createQuery("select abs(t.id)," +
"sqrt(t.id)," +
"mod(t.id,2)" +
" from Topict ");
for(Object o : q.list()) {
Object[] arr = (Object[])o;
System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] );
}
Query q = session.createQuery("selectcurrent_date, current_time, current_timestamp, t.id from Topic t");
for(Object o : q.list()) {
Object[] arr =(Object[])o;
System.out.println(arr[0] + " | " + arr[1] + " | " + arr[2] + " | " + arr[3]);
}
Query q =session.createQuery("from Topic t wheret.createDate < :date");
q.setParameter("date", new Date());
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
Query q =session.createQuery("select t.title,count(*) from Topic t group by t.title") ;
for(Object o : q.list()) {
Object[] arr =(Object[])o;
System.out.println(arr[0] + "|" + arr[1]);
}
Query q = session.createQuery("selectt.title, count(*) from Topic t group by t.title having count(*) >= 1") ;
for(Object o : q.list()) {
Object[] arr =(Object[])o;
System.out.println(arr[0] + "|" + arr[1]);
}
Query q =session.createQuery("from Topic t where t.id< (select avg(t.id) from Topic t)") ;
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
Query q =session.createQuery("from Topic t where t.id< ALL (select t.id from Topic t where mod(t.id, 2)= 0) ") ;
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
//用in 可以实现exists的功能
//但是exists执行效率高
// t.id not in (1)
Query q =session.createQuery("from Topic t where notexists (select m.id from Msg m where m.topic.id=t.id)") ;
// Query q =session.createQuery("from Topic t where exists (select m.id from Msg mwhere m.topic.id=t.id)") ;
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
//update and delete
//规范并没有说明是不是要更新persistent object，所以如果要使用，建议在单独的trasaction中执行
Query q =session.createQuery("update Topic t sett.title = upper(t.title)") ;
q.executeUpdate();
q = session.createQuery("from Topic");
for(Object o : q.list()) {
Topic t = (Topic)o;
System.out.println(t.getTitle());
}
session.createQuery("update Topic t set t.title = lower(t.title)")
.executeUpdate();
//不重要
Query q =session.getNamedQuery("topic.selectCertainTopic");
q.setParameter("id", 5);
Topic t =(Topic)q.uniqueResult();
System.out.println(t.getTitle());
//Native（了解）
SQLQuery q =session.createSQLQuery("select *from category limit 2,4").addEntity(Category.class);
List<Category>categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
public void testHQL_35() {
//尚未实现JPA命名的NativeSQL
}
——————————————————————————————————————————————————————————————————
以BBS为例子
package com.demo.hibernate;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
// 模板
@Entity
public class Category {
private int id;
private String name;
@Id
@GeneratedValue
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;
}
}
package com.demo.hibernate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
/*
* HQL语句支持使用英文问号（？）作为参数占位符，这与JDBC的参数占位符一致；也使用命名参数占位符号，
* 方法在参数名前加英文冒号（：），例如:start_date等
* */
@Entity
// 取了名字的查询
@NamedQueries(
{
@NamedQuery(name="topic.selectCertainTopic", query="from Topic t where t.id = :id")
}
)
/*
@NamedNativeQueries(
{
@NamedNativeQuery(name="topic.select2_5Topic", query="select * from topic limit 2, 5")
}
)
*/
// 主题
public class Topic {
private int id;
private String title;
private Category category;
private Date createDate;
private List<Msg> msgs = new ArrayList<Msg>();
@OneToMany(mappedBy="topic")
public List<Msg> getMsgs() {
return msgs;
}
public void setMsgs(List<Msg> msgs) {
this.msgs = msgs;
}
@Id
@GeneratedValue
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
@ManyToOne(fetch=FetchType.LAZY)
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
}
package com.demo.hibernate;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
// 回复跟帖信息数
@Entity
public class Msg {
private int id;
private String count;
private Topic topic;
@Id
@GeneratedValue
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCount() {
return count;
}
public void setCount(String count) {
this.count = count;
}
@ManyToOne
public Topic getTopic() {
return topic;
}
public void setTopic(Topic topic) {
this.topic = topic;
}
}
package com.demo.hibernate;
import javax.persistence.Entity;
// 回复信息统计
public class MsgInfo {
private int id;
private String count;
private String topicName;
private String categoryName;
public MsgInfo(int id, String count, String topicName, String categoryName) {
super();
this.id = id;
this.count = count;
this.topicName = topicName;
this.categoryName = categoryName;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getCount() {
return count;
}
public void setCount(String count) {
this.count = count;
}
public String getTopicName() {
return topicName;
}
public void setTopicName(String topicName) {
this.topicName = topicName;
}
public String getCategoryName() {
return categoryName;
}
public void setCategoryName(String categoryName) {
this.categoryName = categoryName;
}
}
package com.demo.hibernate;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.FetchType;
import javax.persistence.OneToMany;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class HibernateTreeTest {
private static SessionFactory sf = null;
@BeforeClass
public static void beforClass() {
// new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); //自动创建SQL语句
try {
sf = new AnnotationConfiguration().configure().buildSessionFactory();
} catch (Exception e) {
e.printStackTrace();
}
}
@AfterClass
public static void afterClass() {
sf.close();
}
@Test
public void testSchemaExport() { //生成建表语句
// new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); //自动创建SQL语句
}
@Test
public void testSave() {
Session session = sf.openSession();
session.beginTransaction();
// 存入10个板块
for(int i=0; i<10; i++) {
Category c = new Category();
c.setName("c"+i);
session.save(c);
}
//在c1板块下面，又有10个主题
for(int i=0; i<10; i++) {
Category c = new Category();
c.setId(1);
Topic t = new Topic();
t.setCategory(c);
t.setTitle("t" + i);
t.setCreateDate(new Date());
session.save(t);
}
// 在t1 在这个主题下面有又10个回复信息
for(int i=0; i<10; i++) {
Topic t = new Topic();
t.setId(1);
Msg m = new Msg();
m.setCount("m" + i);
m.setTopic(t);
session.save(m);
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_01() {
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Category");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL_02() {
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Category c where c.name > 'c5'");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL_03() {
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("from Category c order by c.name desc");
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL_04() {
Session session = sf.getCurrentSession();
session.beginTransaction();
Query q = session.createQuery("select distinct c from Category c order by c.name desc");
/*SQL SELECT DISTINCT 语句
在表中，可能会包含重复值。这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。
关键词 DISTINCT 用于返回唯一不同的值。
*/
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getName());
}
session.getTransaction().commit();
}
@Test
public void testHQL_05() {
Session session = sf.openSession();
session.beginTransaction();
/*Query q = session.createQuery("from Category c where c.id > :min and c.id < :max");
//q.setParameter("min", 2);
//q.setParameter("max", 8);
q.setInteger("min", 2);
q.setInteger("max", 8);*/
Query q = session.createQuery("from Category c where c.id > :min and c.id < :max")
.setInteger("min", 2)
.setInteger("max", 8); //下标值大于2开始取值到第8个id
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getId() + "-" + c.getName());
}
session.getTransaction().commit();
session.close();
}
//分页
@Test
public void testHQL_07() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Category c order by c.name desc"); //降序
q.setMaxResults(4); // 设置检索的行数
q.setFirstResult(2); //设置开始检索位置
List<Category> categories = (List<Category>)q.list();
for(Category c : categories) {
System.out.println(c.getId() + "-" + c.getName());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_08() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select c.id, c.name from Category c order by c.name desc");
List<Object[]> categories = (List<Object[]>)q.list();
for(Object[] o : categories) {
System.out.println(o[0] + "-" + o[1]);
}
session.getTransaction().commit();
session.close();
}
//设定fetch type 为lazy后将不会有第二条sql语句
@Test
public void testHQL_09() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.category.id = 1");
List<Topic> topics = (List<Topic>)q.list();
for(Topic t : topics) {
// System.out.println(t.getTitle());
System.out.println(t.getCategory().getName());
}
session.getTransaction().commit();
session.close();
}
//设定fetch type 为lazy后将不会有第二条sql语句
@Test
public void testHQL_10() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Topic t where t.category.id = 1");
List<Topic> topics = (List<Topic>)q.list();
for(Topic t : topics) {
System.out.println(t.getTitle());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_11() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.topic.category.id = 1");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getCount());
}
session.getTransaction().commit();
session.close();
}
//了解即可
//VO Value Object
//DTO data transfer object
@Test
public void testHQL_12() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select new com.demo.hibernate.MsgInfo(m.id, m.count, m.topic.title, m.topic.category.name) from Msg m");
//前提必须是在MsgInfo里面有一个构造方法
for(Object o : q.list()) {
MsgInfo m = (MsgInfo)o;
System.out.println(m.getId()+"--"+m.getCount()+"--"+m.getTopicName()+"--"+m.getCategoryName());
}
session.getTransaction().commit();
session.close();
}
//动手测试left right join
//为什么不能直接写Category名，而必须写t.category
//因为有可能存在多个成员变量（同一个类），需要指明用哪一个成员变量的连接条件来做连接
@Test
public void testHQL_13() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select t.title, c.name from Topic t join t.category c "); //join Category c
for(Object o : q.list()) {
Object[] m = (Object[])o;
System.out.println(m[0] + "-" + m[1]);
}
session.getTransaction().commit();
session.close();
}
//学习使用uniqueResult
@Test
public void testHQL_14() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m = :MsgToSearch "); //不重要
Msg m = new Msg();
m.setId(1);
q.setParameter("MsgToSearch", m);
Msg mResult = (Msg)q.uniqueResult();
System.out.println(mResult.getCount());
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_15() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select count(*) from Msg m");
long count = (Long)q.uniqueResult();
System.out.println(count);
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_16() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("select max(m.id), min(m.id), avg(m.id), sum(m.id) from Msg m");
Object[] o = (Object[])q.uniqueResult();
System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]); //数组分别代表查询的第几个数
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_17() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.id between 3 and 5");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getId() + "-" + m.getCount());
}
session.getTransaction().commit();
session.close();
}
@Test
public void testHQL_18() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.id in (3,4, 5)");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getId() + "-" + m.getCount());
}
session.getTransaction().commit();
session.close();
}
//is null 与 is not null
@Test
public void testHQL_19() {
Session session = sf.openSession();
session.beginTransaction();
Query q = session.createQuery("from Msg m where m.count is not null");
for(Object o : q.list()) {
Msg m = (Msg)o;
System.out.println(m.getId() + "-" + m.getCount());
}
session.getTransaction().commit();
session.close();
}
public static void main(String[] args) {
beforClass();
}
}
package com.demo.hibernate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
/*
* HQL语句支持使用英文问号（？）作为参数占位符，这与JDBC的参数占位符一致；也使用命名参数占位符号，
* 方法在参数名前加英文冒号（：），例如:start_date等
* */
@Entity
// 取了名字的查询
@NamedQueries(
{
@NamedQuery(name="topic.selectCertainTopic", query="from Topic t where t.id = :id")
}
)
/*
@NamedNativeQueries(
{
@NamedNativeQuery(name="topic.select2_5Topic", query="select * from topic limit 2, 5")
}
)
*/
// 主题
public class Topic {
private int id;
private String title;
private Category category;
private Date createDate;
private List<Msg> msgs = new ArrayList<Msg>();
@OneToMany(mappedBy="topic")
public List<Msg> getMsgs() {
return msgs;
}
public void setMsgs(List<Msg> msgs) {
this.msgs = msgs;
}
@Id
@GeneratedValue
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
@ManyToOne(fetch=FetchType.LAZY)
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
public Date getCreateDate() {
return createDate;
}
public void setCreateDate(Date createDate) {
this.createDate = createDate;
}
}
QBC
package com.demo.hibernate;
import java.util.Date;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.criterion.Restrictions;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class HibernateTreeTest {
private static SessionFactory sf = null;
@BeforeClass
public static void beforClass() {
// new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); //自动创建SQL语句
try {
sf = new AnnotationConfiguration().configure().buildSessionFactory();
} catch (Exception e) {
e.printStackTrace();
}
}
@AfterClass
public static void afterClass() {
sf.close();
}
@Test
public void testSchemaExport() { //生成建表语句
// new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); //自动创建SQL语句
}
@Test
public void testSave() {
Session session = sf.openSession();
session.beginTransaction();
// 存入10个板块
for(int i=0; i<10; i++) {
Category c = new Category();
c.setName("c"+i);
session.save(c);
}
//在c1板块下面，又有10个主题
for(int i=0; i<10; i++) {
Category c = new Category();
c.setId(1);
Topic t = new Topic();
t.setCategory(c);
t.setTitle("t" + i);
t.setCreateDate(new Date());
session.save(t);
}
// 在t1 在这个主题下面有又10个回复信息
for(int i=0; i<10; i++) {
Topic t = new Topic();
t.setId(1);
Msg m = new Msg();
m.setCount("m" + i);
m.setTopic(t);
session.save(m);
}
session.getTransaction().commit();
session.close();
}
//is empty and is not empty
@Test
public void testQBC() {
Session session = sf.openSession();
session.beginTransaction();
//criterion 标准/准则/约束
Criteria c = session.createCriteria(Topic.class) //from Topic
.add(Restrictions.gt("id", 2)) //greater than = id > 2
.add(Restrictions.lt("id", 8)) //little than = id < 8
.add(Restrictions.like("title", "t_"))
.createCriteria("category")
.add(Restrictions.between("id", 3, 5)) //category.id >= 3 and category.id <=5
;
//DetachedCriterea 自己创建 完了之后绑定到session上面
for(Object o : c.list()) {
Topic t = (Topic)o;
System.out.println(t.getId() + "-" + t.getTitle());
}
session.getTransaction().commit();
session.close();
}
public static void main(String[] args) {
beforClass();
}
}
package com.demo.hibernate;
import java.util.Date;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Restrictions;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
public class HibernateTreeTest {
private static SessionFactory sf = null;
@BeforeClass
public static void beforClass() {
// new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); //自动创建SQL语句
try {
sf = new AnnotationConfiguration().configure().buildSessionFactory();
} catch (Exception e) {
e.printStackTrace();
}
}
@AfterClass
public static void afterClass() {
sf.close();
}
@Test
public void testSchemaExport() { //生成建表语句
// new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); //自动创建SQL语句
}
@Test
public void testSave() {
Session session = sf.openSession();
session.beginTransaction();
// 存入10个板块
for(int i=0; i<10; i++) {
Category c = new Category();
c.setName("c"+i);
session.save(c);
}
//在c1板块下面，又有10个主题
for(int i=0; i<10; i++) {
Category c = new Category();
c.setId(1);
Topic t = new Topic();
t.setCategory(c);
t.setTitle("t" + i);
t.setCreateDate(new Date());
session.save(t);
}
// 在t1 在这个主题下面有又10个回复信息
for(int i=0; i<10; i++) {
Topic t = new Topic();
t.setId(1);
Msg m = new Msg();
m.setCount("m" + i);
m.setTopic(t);
session.save(m);
}
session.getTransaction().commit();
session.close();
}
//is empty and is not empty
//query by criteria query by example
@Test
public void testQBE() {
Session session = sf.openSession();
session.beginTransaction();
Topic tExample = new Topic();
tExample.setTitle("T_");
Example e = Example.create(tExample)
.ignoreCase().enableLike(); //ignoreCase 忽略大小写 使用like
Criteria c = session.createCriteria(Topic.class)
.add(Restrictions.gt("id", 2))
.add(Restrictions.lt("id", 8))
.add(e)
;
for(Object o : c.list()) {
Topic t = (Topic)o;
System.out.println(t.getId() + "-" + t.getTitle());
}
session.getTransaction().commit();
session.close();
}
public static void main(String[] args) {
beforClass();
}
}
191
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
