nhibernate 怎么动态组合sql

发布网友发布时间：2022-05-01 14:39

共2个回答

懂视网时间：2022-05-01 19:00

<?xml version="1.0" encoding="utf-8"?> 2 <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 3 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 4  7 <hibernate-mapping> 8 <class name="hp.pojo.Member" table="member" catalog="hedb"> 9 <id name="mid" type="java.lang.String"> 10 <column name="mid" length="50" /> 11 <generator class="assigned"></generator> 12 </id> 13 <property name="name" type="java.lang.String"> 14 <column name="name" length="50" /> 15 </property> 16 <property name="age" type="java.lang.Integer"> 17 <column name="age" /> 18 </property> 19 <property name="salary" type="java.lang.Double"> 20 <column name="salary" precision="22" scale="0" /> 21 </property> 22 <property name="birthday" type="java.util.Date"> 23 <column name="birthday" length="0" /> 24 </property> 25 <property name="note" type="java.lang.String"> 26 <column name="note" length="65535" /> 27 </property> 28 </class> 29 </hibernate-mapping>

POJO类如下:

 1 package hp.pojo;
 2 
 3 import java.util.Date;
 4 
 5 /**
 6  * Member entity. @author MyEclipse Persistence Tools
 7 */
 8 
 9 @SuppressWarnings("serial")
 10 public class Member implements java.io.Serializable
 11 {
 12 
 13 // Fields
 14 
 15 private String mid;
 16 private String name;
 17 private Integer age;
 18 private Double salary;
 19 private Date birthday;
 20 private String note;
 21 
 22 // Constructors
 23 
 24 /** default constructor */
 25 public Member()
 26  {
 27  }
 28 
 29 /** minimal constructor */
 30 public Member(String mid)
 31  {
 32  this.mid = mid;
 33  }
 34 
 35 /** full constructor */
 36 public Member(String mid, String name, Integer age, Double salary, Date birthday, String note)
 37  {
 38  this.mid = mid;
 39  this.name = name;
 40  this.age = age;
 41  this.salary = salary;
 42  this.birthday = birthday;
 43  this.note = note;
 44  }
 45 
 46 // Property accessors
 47 
 48 public String getMid()
 49  {
 50  return this.mid;
 51  }
 52 
 53 public void setMid(String mid)
 54  {
 55  this.mid = mid;
 56  }
 57 
 58 public String getName()
 59  {
 60  return this.name;
 61  }
 62 
 63 public void setName(String name)
 64  {
 65  this.name = name;
 66  }
 67 
 68 public Integer getAge()
 69  {
 70  return this.age;
 71  }
 72 
 73 public void setAge(Integer age)
 74  {
 75  this.age = age;
 76  }
 77 
 78 public Double getSalary()
 79  {
 80  return this.salary;
 81  }
 82 
 83 public void setSalary(Double salary)
 84  {
 85  this.salary = salary;
 86  }
 87 
 88 public Date getBirthday()
 89  {
 90  return this.birthday;
 91  }
 92 
 93 public void setBirthday(Date birthday)
 94  {
 95  this.birthday = birthday;
 96  }
 97 
 98 public String getNote()
 99  {
100  return this.note;
101  }
102 
103 public void setNote(String note)
104  {
105  this.note = note;
106  }
107 
108 }

下面代码演示了利用反射以及XML解析，动态生成SQL语句，并且利用JDBC技术执行SQL语句的过程：

 1 package hp.test;
 2 
 3 import java.io.File;
 4 import java.lang.reflect.Field;
 5 import java.lang.reflect.Method;
 6 import java.sql.Connection;
 7 import java.sql.DriverManager;
 8 import java.util.ArrayList;
 9 import java.util.Date;
 10 import java.util.List;
 11 
 12 
 13 
 14 
 15 
 16 import org.dom4j.Document;
 17 import org.dom4j.Element;
 18 import org.dom4j.io.SAXReader;
 19 
 20 import com.mysql.jdbc.PreparedStatement;
 21 
 22 public class MyHibernate
 23 {
 24 private class Column
 25  {
 26  private String name;
 27  private String length;
 28  }
 29 
 30 private class Generator
 31  {
 32  private String type;
 33  }
 34 
 35 private class Id
 36  {
 37  private String name;
 38  private String type;
 39  
 40  private Column column;
 41  private Generator generator;
 42  }
 43 private class Property
 44  {
 45  private String name;
 46  private String type;
 47  private Column column;
 48  }
 49 
 50 private class Class
 51  {
 52  private String name;
 53  private String table;
 54  private String catalog;
 55  
 56  private Id id;
 57  private List<Property> allProperties;
 58  }
 59 private class KeyValuePair
 60  {
 61  private String type;
 62  private Object value;
 63  
 64  private KeyValuePair(String type,Object value)
 65  {
 66  this.type = type;
 67  this.value = value;
 68  }
 69  }
 70 
 71 private String configPath ;
 72 private Object obj = null;
 73 private Document document;
 74 private Class voClass ;
 75 private String sql;
 76 private List<KeyValuePair> allValues;
 77 
 78 
 79 public MyHibernate(String configPath)
 80  {
 81  this.configPath = configPath;
 82  }
 83 
 84 public int save(Object obj) throws Exception
 85  {
 86  this.obj = obj; 
 87  this.load();
 88  this.format();
 89  this.generateSql();
 90  return this.saveToDb();
 91  }
 92 
 93 public void load() throws Exception
 94  {
 95  SAXReader saxReader = new SAXReader();
 96  this.document = saxReader.read(new File(this.configPath));
 97  }
 98 public void format() throws Exception
 99  {
100  Class _class = new Class();
101  org.dom4j.Element root = this.document.getRootElement();
102  Element classElement = root.element("class");
103  _class.name = classElement.attributeValue("name");
104  _class.table = classElement.attributeValue("table");
105  _class.catalog = classElement.attributeValue("catalog");
106  
107  Element idElement = classElement.element("id");
108  _class.id = new Id();
109  _class.id.name = idElement.attributeValue("name");
110  _class.id.type = idElement.attributeValue("type");
111  
112  Element columnElementInId = idElement.element("column");
113  _class.id.column = new Column();
114  _class.id.column.name = columnElementInId.attributeValue("name");
115  _class.id.column.length = columnElementInId.attributeValue("length");
116  
117  Element generatorElement = idElement.element("generator");
118  _class.id.generator = new Generator();
119  _class.id.generator.type = generatorElement.attributeValue("class");
120  
121  List<Property> allProperties = new ArrayList<MyHibernate.Property>();
122  _class.allProperties = allProperties;
123  
124  List<Element> allPropertiesElements = classElement.elements("property");
125  for(Element item : allPropertiesElements)
126  {
127  Property property = new Property();
128  property.name = item.attributeValue("name");
129  property.type = item.attributeValue("type");
130  
131  Element columnElement = item.element("column");
132  property.column = new Column();
133  property.column.name = columnElement.attributeValue("name");
134  property.column.length = columnElement.attributeValue("length");
135   allProperties.add(property);
136  //System.out.println("name: " + property.name);
137  }
138  this.voClass = _class;
139  }
140 
141 public void generateSql() throws Exception
142  {
143  this.allValues = new ArrayList<MyHibernate.KeyValuePair>();
144  StringBuffer columns = new StringBuffer();
145  StringBuffer values = new StringBuffer();
146  StringBuffer sql = new StringBuffer(" INSERT INTO ");
147  sql.append(this.voClass.table).append("( ");
148  
149  if("assigned".equals(this.voClass.id.generator.type))
150  {
151  //需要用户提供主键列
152  columns.append(this.voClass.id.column.name).append(",");
153  values.append("?,");
154  
155  Field field = this.obj.getClass().getDeclaredField(this.voClass.id.name);
156  field.setAccessible(true);
157  this.allValues.add(new KeyValuePair(this.voClass.id.type, field.get(this.obj)));
158  }
159  
160  for(Property property : this.voClass.allProperties)
161  {
162  columns.append(property.column.name).append(",");
163  values.append("?,");
164  
165  Field field = this.obj.getClass().getDeclaredField(property.name);
166  field.setAccessible(true);
167  this.allValues.add(new KeyValuePair(property.type, field.get(this.obj)));
168  }
169  
170  columns.delete(columns.length()-1, columns.length());
171  values.delete(values.length()-1, columns.length());
172  
173  sql.append(columns.toString()).append(") VALUES (");
174  sql.append(values.toString()).append(")");
175  this.sql = sql.toString();
176  System.out.println(this.sql);
177  }
178 
179 public int saveToDb() throws Exception
180  {
181  java.lang.Class.forName("org.gjt.mm.mysql.Driver");
182  Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/hedb", "root", "admin");
183  java.sql.PreparedStatement ps = conn.prepareStatement(this.sql);
184  
185  for(int i = 0; i < this.allValues.size(); i++)
186  {
187  //System.out.println("***type:" + this.allValues.get(i).type);
188  if(String.class.getName().toString().equals(this.allValues.get(i).type))
189   {
190   ps.setString(i+1, (String)this.allValues.get(i).value);
191   }
192  else if(Integer.class.getName().toString().equals(this.allValues.get(i).type))
193   {
194   ps.setInt(i+1, (Integer)this.allValues.get(i).value);
195  }else if(Double.class.getName().toString().equals(this.allValues.get(i).type))
196   {
197   ps.setDouble(i+1, (Double)this.allValues.get(i).value);
198  }else if(Date.class.getName().toString().equals(this.allValues.get(i).type))
199   {
200   ps.setDate(i+1, new java.sql.Date(((Date)this.allValues.get(i).value).getTime()));
201   }
202  
203  //System.out.println("设置第" + (i+1) + "个值("+this.allValues.get(i).type+","+this.allValues.get(i).value+")");
204  }
205  return ps.executeUpdate();
206  
207  } 
208 }

测试代码：

 1 package hp.test;
 2 
 3 import java.util.Date;
 4 
 5 import org.hibernate.Session;
 6 import org.hibernate.SessionFactory; 
 7 
 8 import hp.pojo.Member;
 9 import hp.utils.HibernateHelper;
10 
11 public class Main
12 {
13 
14 public static void main(String[] args) 
15  {
16  Member vo = new Member();
17  vo.setMid("admin4");
18  vo.setAge(24);
19  vo.setBirthday(new Date());
20  vo.setName("sheldon4");
21  vo.setNote("a good person4");
22  vo.setSalary(44444.44);
23  
24  try
25  {
26  MyHibernate mh = new MyHibernate("C:\D\code\resource\mapping.xml"); 
27  int count = mh.save(vo);
28  System.out.println("成功插入" + count + "行数据");
29  }
30  catch(Exception e)
31  {
32   e.printStackTrace();
33  
34  }
35  System.out.println("main done//~");
36  }
37 
38 }

模拟Hibernate动态生成SQL语句

标签：span clip ping session entity tcl list else exec

热心网友时间：2022-05-01 16:08

NHibernate 查看生成的sql语句：
其实就是Interceptor的应用，源码中Interceptor的默认实现是EmptyInterceptor，
[Serializable]
public class EmptyInterceptor : IInterceptor
{ //前面省略n行代码
public SqlString OnPrepareStatement(SqlString sql)
{
return sql;
}
} public class MyInterceptor : EmptyInterceptor
{
public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
{
return base.OnPrepareStatement(sql);
}
} 我们要做的就是继承EmptyInterceptor，重写OnPrepareStatement()方法，重写方法里面就是你大展拳脚的地方了，
想写文件写文件，想输出到页面就输出到页面，什么都不做都可以，如上
怎么用呢？
public override void Update(Admin entity)
{
ISession session = HibernateTemplate.SessionFactory.OpenSession(new MyIntercepotr);
session.Update(entity);
session.Flush();
}
在你想要查看的操作中，打开session 的时候添加上自定义的*就可以了，想给所有的操作都配置的话就要用到全局配置文件了。
重写OnPrepareStatement()的时候一句话都不改，我只是在这里打一个断点而已，只要看看生成的sql语句就行了，然后去修改配置文件再来debug，确认怎么配置生产的sql最优。