天道酬勤,学无止境

sql

Hibernate Result Transformer cannot instantiate resultclass

I have a SQL query that I want to map to a class. I'm using Hibernate's createSQLQuery and then using a Result Transformer for the class I'd like to map to. But Hibernate is throwing this error: ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[localhost].[/mypackage].[MyPackageServlet]] Servlet.service() for servlet MyPackageServlet threw exception org.hibernate.HibernateException: Could not instantiate resultclass: com.myapp.mypackage.context.report.MonthlyReportContext$MonthlyReport at org.hibernate.transform.AliasToBeanResultTransformer.transformTuple(AliasToBeanResultTransformer

2022-01-19 02:38:40    分类:问答    java   sql   hibernate

Which SQL pattern is faster to avoid inserting duplicate rows?

I know of two ways to insert without duplication. The first is using a WHERE NOT EXISTS clause: INSERT INTO table_name (col1, col2, col3) SELECT %s, %s, %s WHERE NOT EXISTS ( SELECT * FROM table_name AS T WHERE T.col1 = %s AND T.col2 = %s) the other is doing a LEFT JOIN: INSERT INTO table_name (col1, col2, col3) SELECT %s, %s, %s FROM ( SELECT %s, %s, %s ) A LEFT JOIN table_name B ON B.COL1 = %s AND B.COL2 = %s WHERE B.id IS NULL LIMIT 1 Is there a general rule as to one being faster than the other, or does it depend on the tables? Is there a different way which is better than both?

2022-01-19 02:20:27    分类:问答    sql   database   postgresql   query-optimization

How to optimize retrieval of most occurring values (hundreds of millions of rows)

I'm trying to retrieve some most occurring values from a SQLite table containing a few hundreds of millions of rows. The query so far may look like this: SELECT value, COUNT(value) AS count FROM table GROUP BY value ORDER BY count DESC LIMIT 10 There is a index on the value field. However, with the ORDER BY clause, the query takes so much time I've never seen the end of it. What could be done to drastically improve such queries on such big amount of data? I tried to add a HAVING clause (e.g: HAVING count > 100000) to lower the number of rows to be sorted, without success. Note that I don't

2022-01-19 01:57:04    分类:问答    java   sql   sqlite   optimization   query-optimization

Getting Error: "Error converting data type nvarchar to numeric" in SQL

I am passing 4 Parameters to an asp.net Webservice. This is my Code so far: Webmethod: [ScriptMethod(ResponseFormat = ResponseFormat.Json)] [WebMethod] public List<RaumHelper.RAUM> Raum(string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID) { return RaumHelper.Raum(RAUMKLASSE_ID, STADT_ID, GEBAEUDE_ID, REGION_ID); } Helperclass: public class RaumHelper { public class RAUM { public string RaumName { get; set; } public string RaumID { get; set; } } internal static List<RAUM> Raum( string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID) { List<RAUM>

2022-01-19 01:30:19    分类:问答    c#   sql   web-services   sql-server-2008

SQL Server logical grouping most recent time

I am looking for the best approach to this issue and have not had any luck so far. I am using Microsoft SQL Server 2008. So here is sample data of a user: ====================== Name Status Date ====================== BOB Active 2011-03-07 BOB Active 2011-03-11 BOB Disabled 2011-03-15 BOB Disabled 2011-03-21 BOB Active 2011-03-23 BOB Active 2011-03-28 ====================== I want to have this grouped so that it will show when the user actually changed the status and not the next time they were checked if the result stayed the same so what the query returns would be as follows: ===============

2022-01-19 01:27:45    分类:问答    sql   sql-server-2008   grouping

Is it possible to set foreign key between two excel sheets?

Is it possible to set foreign key between two excel sheets and query records from the two sheets? I got an excel sheet of Student Details and another sheet consists of the total marks. Fields common to both the sheet is the RegID. I need to display the Name and Marks from the two sheets on a grid...How can it be done? please help.... Query = "SELECT Status from [Viewer$] as a LEFT JOIN [UI$] as b ON a.[Responsible Person] = b.[Responsible Person] where b.[Responsible Person] = null" ; This query is not returning the records to a dataset...

2022-01-19 01:24:28    分类:问答    c#   sql   sql-server-2005   excel

SQL query returns no result from Java although it returns a result in Access

When I run this query: select character from tbl_Unknown where format(fw,'.###')='48.143' and code='0001' it returns a result in the Access query interface but when I try to run it from Java it doesn't return a result. My table (tbl_Unknown): char_id: autonumber value:1 fw: short text value:'48.1425' Hint:after format it become '48.143'. code: short text value:'0001' character: short text value: 'x' My java code: public static String getLostedCharacter(String font,String fw, String code) { Connection conn = ConnectDB.getConnection(); String character = null; try { Statement statement = conn

2022-01-19 01:10:18    分类:问答    java   sql   ms-access   jdbc   ucanaccess

Select most recent results based on composite of fields

I'm using MySQL, but I think this is a basic SQL question. I don't know how else to ask but to give an example. Say I have this data in my table: id date_time foreign_key key value 1 2010-01-01 00:00:00 1 'temperature' 84 2 2010-01-01 00:00:01 1 'humidity' 34 3 2010-01-01 00:00:02 2 'temperature' 45 4 2010-01-01 00:00:03 2 'humidity' 23 5 2010-01-01 00:00:04 2 'dew_point' 78 6 2010-01-01 00:00:05 3 'temperature' 57 7 2010-01-01 00:00:06 3 'humidity' 41 8 2010-01-01 00:00:07 4 'temperature' 19 9 2010-01-01 00:00:08 4 'humidity' 35 10 2010-01-01 00:00:09 4 'dew_point' 24 11 2010-01-01 00:00:10 1

2022-01-19 01:06:06    分类:问答    sql   mysql   greatest-n-per-group

MySQL multi-level parent selection/join question

I have a hopefully simple MySQL query question which is eluding me at late at night. I'm trying to do a SELECT which counts the number of instances of a set of data (orders) and groups those instances by a value which exists in a parent a couple levels above the order itself. For example: CREATE TABLE `so_test`.`categories` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=572395 DEFAULT CHARSET=latin1; CREATE TABLE `so_test`.`product_group` ( `id` int(10) unsigned NOT NULL auto_increment, `category_id` int(10) unsigned NOT NULL auto_increment

2022-01-19 01:02:49    分类:问答    sql   syntax   select

Add a column to a table with check constraint SQL

I want to add a column to a table, then add a check constraint to make sure its greater than 0. I cant seem to get this to run in oracle sl developer. Alter TABLE store101 add column Base_salary Number(7,2) constraint store101_Base_salary_ck check (Base_salary > 0); Error report - SQL Error: ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier"

2022-01-19 00:55:25    分类:问答    sql   oracle11g   constraints   alter