//sql

Sql代码

1.– MySQL dump 10.13 Distrib 5.1.55, for Win32 (ia32)

2.–

3.– Host: localhost Database: hibernate_demo

4.– ——————————————————

5.– Server version 5.1.55-community

6.

7./*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

8./*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

9./*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

10./*!40101 SET NAMES utf8 */;

11./*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

12./*!40103 SET TIME_ZONE=’+00:00′ */;

13./*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

14./*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

15./*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;

16./*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

17.

18.–

19.– Table structure for table `category`

20.–

21.

22.DROP TABLE IF EXISTS `category`;

23./*!40101 SET @saved_cs_client = @@character_set_client */;

24./*!40101 SET character_set_client = utf8 */;

25.CREATE TABLE `category` (

26. `id` int(11) NOT NULL AUTO_INCREMENT,

27. `name` varchar(30) NOT NULL,

28. `description` varchar(30) NOT NULL,

29. PRIMARY KEY (`id`)

30.) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

31./*!40101 SET character_set_client = @saved_cs_client */;

32.

33.–

34.– Dumping data for table `category`

35.–

36.

37.LOCK TABLES `category` WRITE;

38./*!40000 ALTER TABLE `category` DISABLE KEYS */;

39.INSERT INTO `category` VALUES (1,’fruit2′,’fruit category2′),(2,’fruit2′,’fruit category2′),(3,’fruit2′,’fruit category2′);

40./*!40000 ALTER TABLE `category` ENABLE KEYS */;

41.UNLOCK TABLES;

42.

43.–

44.– Table structure for table `product`

45.–

46.

47.DROP TABLE IF EXISTS `product`;

48./*!40101 SET @saved_cs_client = @@character_set_client */;

49./*!40101 SET character_set_client = utf8 */;

50.CREATE TABLE `product` (

51. `id` int(11) NOT NULL AUTO_INCREMENT,

52. `name` varchar(30) NOT NULL,

53. `price` int(11) NOT NULL,

54. `description` varchar(30) NOT NULL,

55. `category_id` int(11) DEFAULT NULL,

56. PRIMARY KEY (`id`),

57. KEY `category_id` (`category_id`)

58.) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

59./*!40101 SET character_set_client = @saved_cs_client */;

60.

61.–

62.– Dumping data for table `product`

63.–

64.

65.LOCK TABLES `product` WRITE;

66./*!40000 ALTER TABLE `product` DISABLE KEYS */;

67.INSERT INTO `product` VALUES (1,’orige2′,20,’orige2′,1),(2,’apple2′,10,’apple2′,1),(3,’orige2′,20,’orige2′,2),(4,’apple2′,10,’apple2′,2),(5,’orige2′,20,’orige2′,3),(6,’apple2′,10,’apple2′,3);

68./*!40000 ALTER TABLE `product` ENABLE KEYS */;

69.UNLOCK TABLES;

70./*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

71.

72./*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

73./*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

74./*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

75./*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

76./*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

77./*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

78./*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

79.

80.– Dump completed on 2012-08-22 23:27:56

– MySQL dump 10.13 Distrib 5.1.55, for Win32 (ia32)

–

– Host: localhost Database: hibernate_demo

– ——————————————————

– Server version 5.1.55-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE=’+00:00′ */;

/*!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 */;

–

– Table structure for table `category`

–

DROP TABLE IF EXISTS `category`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `category` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`description` varchar(30) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

–

– Dumping data for table `category`

–

LOCK TABLES `category` WRITE;

/*!40000 ALTER TABLE `category` DISABLE KEYS */;

INSERT INTO `category` VALUES (1,’fruit2′,’fruit category2′),(2,’fruit2′,’fruit category2′),(3,’fruit2′,’fruit category2′);

/*!40000 ALTER TABLE `category` ENABLE KEYS */;

UNLOCK TABLES;

–

– Table structure for table `product`

–

DROP TABLE IF EXISTS `product`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `product` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`price` int(11) NOT NULL,

`description` varchar(30) NOT NULL,

`category_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `category_id` (`category_id`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

–

– Dumping data for table `product`

–

LOCK TABLES `product` WRITE;

/*!40000 ALTER TABLE `product` DISABLE KEYS */;

INSERT INTO `product` VALUES (1,’orige2′,20,’orige2′,1),(2,’apple2′,10,’apple2′,1),(3,’orige2′,20,’orige2′,2),(4,’apple2′,10,’apple2′,2),(5,’orige2′,20,’orige2′,3),(6,’apple2′,10,’apple2′,3);

/*!40000 ALTER TABLE `product` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

– Dump completed on 2012-08-22 23:27:56

 

//Category.java

Java代码

1.@Entity

2.@Table(name=”category”)

3.public class Category implements java.io.Serializable {

4.

5. // Fields

6. private static final long serialVersionUID = 3763960444574701564L;

7.

8. @Id

9. @GenericGenerator(name=”incrementGenerator”, strategy=”increment”)

10. @GeneratedValue(generator=”incrementGenerator”, strategy=GenerationType.IDENTITY)

11. @Column(name=”id”)

12. private Integer id;

13.

14. @Column(name=”name”, nullable=false)

15. private String name;

16.

17. @Column(name=”description”, nullable=false)

18. private String description;

19.

20. @OneToMany(targetEntity=Product.class, cascade=CascadeType.ALL, mappedBy=”category”)

21. @JoinColumn(name=”category_id”)//可有可无，告诉一端category_id是外键

22. private Set<Product> products = new HashSet<Product>();

@Entity

@Table(name=”category”)

public class Category implements java.io.Serializable {

// Fields

private static final long serialVersionUID = 3763960444574701564L;

 

@Id

@GenericGenerator(name=”incrementGenerator”, strategy=”increment”)

@GeneratedValue(generator=”incrementGenerator”, strategy=GenerationType.IDENTITY)

@Column(name=”id”)

private Integer id;

 

@Column(name=”name”, nullable=false)

private String name;

 

@Column(name=”description”, nullable=false)

private String description;

@OneToMany(targetEntity=Product.class, cascade=CascadeType.ALL, mappedBy=”category”)

@JoinColumn(name=”category_id”)//可有可无，告诉一端category_id是外键

private Set<Product> products = new HashSet<Product>();

//Product.java

Java代码

1.@Entity

2.@Table(name=”product”)

3.public class Product implements java.io.Serializable {

4.

5. // Fields

6. private static final long serialVersionUID = -7292036796553578847L;

7.

8. @Id

9. @GenericGenerator(name=”incrementGenerator”, strategy=”increment”)

10. @GeneratedValue(generator=”incrementGenerator”, strategy=GenerationType.IDENTITY)

11. @Column(name=”id”)

12. private Integer id;

13.

14. @Column(name=”name”, nullable=false)

15. private String name;

16.

17. @Column(name=”price”, nullable=false)

18. private Integer price;

19.

20. @Column(name=”description”,nullable=false)

21. private String description;

22.

23. @ManyToOne(targetEntity=Category.class, cascade=CascadeType.ALL)

24. //指出category_id是外键，参考其他表的主键列id

25. @JoinColumn(name=”category_id”, referencedColumnName=”id”,nullable=false)

26. private Category category;

@Entity

@Table(name=”product”)

public class Product implements java.io.Serializable {

// Fields

private static final long serialVersionUID = -7292036796553578847L;

 

@Id

@GenericGenerator(name=”incrementGenerator”, strategy=”increment”)

@GeneratedValue(generator=”incrementGenerator”, strategy=GenerationType.IDENTITY)

@Column(name=”id”)

private Integer id;

 

@Column(name=”name”, nullable=false)

private String name;

 

@Column(name=”price”, nullable=false)

private Integer price;

 

@Column(name=”description”,nullable=false)

private String description;

 

@ManyToOne(targetEntity=Category.class, cascade=CascadeType.ALL)

//指出category_id是外键，参考其他表的主键列id

@JoinColumn(name=”category_id”, referencedColumnName=”id”,nullable=false)

private Category category;

//HibernateTest.java

Java代码

1.package com.zyp.examples;

2.

3.import java.util.HashSet;

4.import java.util.Set;

5.

6.import org.hibernate.Session;

7.import org.hibernate.Transaction;

8.

9.public class HibernateTest {

10.

11. public static void main(String[] args) {

12.

13. //同时保存分类和产品

14. addAll();

15.

16.// deleteByCategory();

17.

18.

19. }

20.

21. public static void deleteByCategory()

22. {

23. Session session = HibernateUtil.getSessionFactory().openSession();

24. Transaction tx = session.beginTransaction();

25. Category cat = (Category) session.load(Category.class, new Integer(3));

26. session.delete(cat);

27.

28. tx.commit();

29. session.close();

30. HibernateUtil.shutdown();

31. }

32.

33. public static void addAll()

34. {

35. Category category = new Category();

36. category.setDescription(“fruit category2″);

37. category.setName(“fruit2″);

38.

39. Product product = new Product();

40. product.setDescription(“apple2″);

41. product.setName(“apple2″);

42. product.setPrice(10);

43. product.setCategory(category);

44.

45.

46. Product product1 = new Product();

47. product1.setDescription(“orige2″);

48. product1.setName(“orige2″);

49. product1.setPrice(20);

50. //之前忘记加的关联

51. product1.setCategory(category);

52.

53. Set<Product> products = new HashSet<Product>();

54. products.add(product);

55. products.add(product1);

56. //之前忘记加的关联

57. category.setProducts(products);

58.

59. Session session = HibernateUtil.getSessionFactory().openSession();

60. Transaction tx = session.beginTransaction();

61. session.save(category);

62.// session.save(product1);

63.// session.save(product);

64. //启用级联操作

65.// session.save(product);

66.// session.save(product1);

67. tx.commit();

68. session.close();

69. HibernateUtil.shutdown();

70.

71. }

72.

73. public static void addCategory()

74. {

75. Category category = new Category();

76. category.setName(“fruit”);

77. category.setDescription(“fruit category”);

78.

79. Session session = HibernateUtil.getSessionFactory().openSession();

80. Transaction tx = session.beginTransaction();

81. session.save(category);

82. tx.commit();

83. HibernateUtil.shutdown();

84. }

85.

86.

87. public static void addProduct()

88. {

89. Product product = new Product();

90. product.setDescription(“banana”);

91. product.setName(“banana”);

92. product.setPrice(10);

93.

94. Product product1 = new Product();

95. product1.setDescription(“tom”);

96. product1.setName(“tom”);

97. product1.setPrice(20);

98.

99. Session session = HibernateUtil.getSessionFactory().openSession();

100. Transaction tx = session.beginTransaction();

101. Category category = (Category)session.load(Category.class, new Integer(1));

102.

103. Set<Product> products = new HashSet<Product>();

104. products.add(product);

105. products.add(product1);

106.

107. category.setProducts(products);

108.

109. session.save(category);

110. //启用级联操作

111.// session.save(product);

112.// session.save(product1);

113. tx.commit();

114. session.close();

115. HibernateUtil.shutdown();

116.

117.

118. }

119.}

package com.zyp.examples;

import java.util.HashSet;

import java.util.Set;

import org.hibernate.Session;

import org.hibernate.Transaction;

public class HibernateTest {

public static void main(String[] args) {

 

//同时保存分类和产品

addAll();

 

// deleteByCategory();

 

}

 

public static void deleteByCategory()

{

Session session = HibernateUtil.getSessionFactory().openSession();

Transaction tx = session.beginTransaction();

Category cat = (Category) session.load(Category.class, new Integer(3));

session.delete(cat);

tx.commit();

session.close();

HibernateUtil.shutdown();

}

 

public static void addAll()

{

Category category = new Category();

category.setDescription(“fruit category2″);

category.setName(“fruit2″);

 

Product product = new Product();

product.setDescription(“apple2″);

product.setName(“apple2″);

product.setPrice(10);

product.setCategory(category);

 

 

Product product1 = new Product();

product1.setDescription(“orige2″);

product1.setName(“orige2″);

product1.setPrice(20);

//之前忘记加的关联

product1.setCategory(category);

 

Set<Product> products = new HashSet<Product>();

products.add(product);

products.add(product1);

//之前忘记加的关联

category.setProducts(products);

 

Session session = HibernateUtil.getSessionFactory().openSession();

Transaction tx = session.beginTransaction();

session.save(category);

// session.save(product1);

// session.save(product);

//启用级联操作

// session.save(product);

// session.save(product1);

tx.commit();

session.close();

HibernateUtil.shutdown();

 

}

 

public static void addCategory()

{

Category category = new Category();

category.setName(“fruit”);

category.setDescription(“fruit category”);

 

Session session = HibernateUtil.getSessionFactory().openSession();

Transaction tx = session.beginTransaction();

session.save(category);

tx.commit();

HibernateUtil.shutdown();

}

 

 

public static void addProduct()

{

Product product = new Product();

product.setDescription(“banana”);

product.setName(“banana”);

product.setPrice(10);

 

Product product1 = new Product();

product1.setDescription(“tom”);

product1.setName(“tom”);

product1.setPrice(20);

 

Session session = HibernateUtil.getSessionFactory().openSession();

Transaction tx = session.beginTransaction();

Category category = (Category)session.load(Category.class, new Integer(1));

 

Set<Product> products = new HashSet<Product>();

products.add(product);

products.add(product1);

 

category.setProducts(products);

 

session.save(category);

//启用级联操作

// session.save(product);

// session.save(product1);

tx.commit();

session.close();

HibernateUtil.shutdown();

 

 

}

}

 

 

之前在保存数据的时候没有在product中保存category，导致外键列总是空的。反复弄之后才发现。。晕。

 

 

基于外键关联的一对多

在多对一双向关联关系中，几乎总是双向关联中的多端作为主体(owner)端, 而一的这段关联注解为@OneToMany( mappedBy=… )

此时保存数据只需要3条sql（两条max(id)除外），1条插入cateogry, 2条插入product.

也就是说，在一对多关系中，最好有多端来维护关联关系，这样可以减少两条update语句。

我们需要在从表（即多段配置@joinColumn，标志外键关系，而不是在多端配置）

而如果我们把关联关系交给1端来维护，即Category来维护，则我们需要多出两条update语句。

此时，我们的实体类应该这样配置。

对于一对多的双向映射,如果要一对多这一端维护关联关系, 你需要删除mappedBy元素并将多对一这端的 @JoinColumn的insertable和updatable设置为false. 很明显,这种方案不会得到什么明显的优化,而且还会增加一些附加的UPDATE语句.

 

 

Java代码

1.@Entity

2.public class Troop {

3. @OneToMany

4. @JoinColumn(name=”troop_fk”) //we need to duplicate the physical information

5. public Set<Soldier> getSoldiers() {

6. …

7.}

8.

9.@Entity

10.public class Soldier {

11. @ManyToOne

12. @JoinColumn(name=”troop_fk”, insertable=false, updatable=false)

13. public Troop getTroop() {

14. …

15.}

@Entity

public class Troop {

@OneToMany

@JoinColumn(name=”troop_fk”) //we need to duplicate the physical information

public Set<Soldier> getSoldiers() {

…

}

@Entity

public class Soldier {

@ManyToOne

@JoinColumn(name=”troop_fk”, insertable=false, updatable=false)

public Troop getTroop() {

…

}

hibernate_annotation_1ton_foreignKey_two.rar (5.7 MB)

下载次数: 7