我一直在try 使用BETWEEN语句从表‘Produccion’中获取数据,该表有一个名为‘dia’的日期字段.
我想要的SQL语句是:
SELECT * FROM produccion WHERE julianday(dia) BETWEEN julianday('2023-11-01') AND julianday('2023-11-31');
个
Java代码很难读懂,因为我试着让代码对所有东西都有效.我已经强制使用了外键,但我不会走出表外,所以这应该无关紧要.
private ResultSet resultado;
private PreparedStatement prepSentencia;
public ResultSet pedirDatos (String[] campos, String tabla, String[][] join, String[] condicionColumna, Object[] condicionValor, String operador, String orden) throws SQLException {
String select = "SELECT ";
resultado=null;
if (campos != null && tabla !=null) {
for (int x=0; x<campos.length;x++) {
select = select + campos[x];
if (x+1<campos.length)
select = select + ", ";
}
select = select + " FROM "+tabla;
if (join != null) {
for (int tupla=0; tupla<join.length; tupla++) {
select = select + " INNER JOIN " + join[tupla][0] + " ON "+ tabla + "." + join[tupla][1] + " = " + join[tupla][0] + "." + join[tupla][2];
}
}
// This is the where part
if (condicionColumna!=null) {
select = select + " WHERE " ;
for (int i =0; i<condicionColumna.length;i++) {
// Usual where
if (condicionColumna[i] != "" && condicionColumna.length==condicionValor.length)
select = select + condicionColumna[i]+" = ? ";
// Between code goes trough here
else if (condicionColumna[i] != "" && condicionColumna.length==1 && condicionValor.length==2)
select = select+ condicionColumna[i]+" BETWEEN julianday(?) AND julianday(?) ";
// Verifico si hay más rotaciones y añado el and o el OR
if (((i+1)<condicionColumna.length))
select = select + " "+operador+" ";
}
}
// Checks for orders
if (!(orden==null || orden==""))
select = select + " ORDER BY ? ";
// Create the sentence
prepSentencia = conexion.prepareStatement(select+";", PreparedStatement.RETURN_GENERATED_KEYS);
// Fill the data
if (condicionValor!=null ) {
for (int i =0; i<condicionValor.length;i++) {
if (condicionValor[i] != "")
prepSentencia.setObject((i+1),condicionValor[i]);
if (((i+1)==condicionValor.length) && !(orden==null || orden==""))
prepSentencia.setString(i+2,orden);
}
} else if (!(orden==null || orden==""))
prepSentencia.setString(1,orden);
resultado = prepSentencia.executeQuery();
}
else
throw new SQLException ("I need input data");
return resultado;
}
输入日期:
pedirDatos(new String[] {"*"}, "produccion",null,new String[] {"julianday(dia)"},new String[] {"'2023-11-01'","'2023-11-31'"},"AND",null);
个
该表在此之前使用如下语句形成:
regularstatement.execute("CREATE TABLE IF NOT EXISTS produccion(\n"
+ " id_produccion INTEGER PRIMARY KEY,\n"
+ " dia TEXT NOT NULL,\n"
+ " hora TEXT NOT NULL,\n"
+ " cantidad REAL NOT NULL,\n"
+ " num_animales INTEGER NOT NULL,\n"
+ " media_por_animal INTEGER,\n"
+ " incidencias TEXT \n"
+ ");");
我知道SQLite中有日期字段,所以我将其设置为文本.
我正在调试,我发现程序通过所有代码都运行正常,调试结果集包含带有以下信息的stmt部分:
SELECT * FROM produccion其中julianday(dia,'utc')BETWEEN julianday(?)和朱利安迪(?); 参数='2023 -11- 01','2023-11- 31']
我已验证表中有两行日期为2023-11-17
因此,我似乎在准备的报表中正确地引用了数据,我觉得这句话是正确的.我不知道为什么我没有得到结果,因为我已经成功地使用了这个项目的其余部分.
我还try 使用julianday(data,‘UTC’)、strftime()、date()和direclty the date.我看了看https://sqlite.org/lang_datefunc.html,但没能让它工作.
什么杀了我,是使用在线查看器(https://inloop.github.io/sqlite-viewer/),我可以使SQL工作,但当运行Java时,它返回没有数据时,它应该.
编辑:澄清一下,我开始有这个问题时,我try 了SELECT * FROM 'produccion' WHERE dia BETWEEN '2023-11-01' AND '2023-11-30'
,并没有工作.
版本2:将朱利安迪更正为福帕斯 comments .仍然没有返回任何数据. 添加了数据库文件中存在数据的证明 link to image个