■
java6でjsonValidation
<groupId>com.github.erosb</groupId>
<artifactId>everit-json-schema-jdk6</artifactId>
<version>1.9.2</version>
</dependency>
jackson2.6.7
try {
BufferedReader reader = new BufferedReader(new FileReader(new File("/Applications/Eclipse_2019-06.app/Contents/workspace/mavenTest/src/main/java/mavenTest/person.json")));
String line = null;
StringBuilder builder = new StringBuilder();
while *1 != null) {
builder.append(line);
}
BufferedReader reader2 = new BufferedReader(new FileReader(new File("/Applications/Eclipse_2019-06.app/Contents/workspace/mavenTest/src/main/java/mavenTest/person.schema.json")));
String line2 = null;
StringBuilder builder2 = new StringBuilder();
while *2 != null) {
builder2.append(line2);
}
JSONObject jsonSchema = new JSONObject(new JSONTokener(builder2.toString()));
JSONObject jsonSubject = new JSONObject(new JSONTokener(builder.toString()));
Schema schema = SchemaLoader.load(jsonSchema);
try {
schema.validate(jsonSubject);
System.out.println("json is valid!");
}catch(ValidationException ve) {
System.out.println(ve.getMessage());
for(int i=0;i<ve.getCausingExceptions().size();i++) {
System.out.println(ve.getCausingExceptions().get(i).getMessage());
}
System.out.println("json is invalid!");
}
//return builder.toString();
} catch (IOException e) {
e.printStackTrace();
}finally {
}
json schema
{
"type": "object",
"properties": {
"name": {
"type": "string"
},
"age": {
"type": "integer",
"minimum": 0
},
"code": {
"type": "string",
"maxLength": 4
},
"birthday": {
"type": "string",
"pattern": "^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$"
},
"hobbies": {
"type": "array",
"items": {
"type": "string"
}
}
},
"required": ["address"]
}
■
SELECTSELECT c.id ,to_char(c.xdate, 'FMDD') FROM person a LEFT OUTER JOIN anken b ON a.id = b.personid LEFT OUTER JOIN kobetsu c ON b.id = c.ankenidWHERE -- c.oyako = 0 to_char(xdate, 'YYYYMM') = '201802'
■
selectselect a.id ,b.date ,b.codefrom person a inner join anken b on a.id = b.personid inner join ( select personid ,max(date) maxdate from anken group by personid )T on b.personid = T.personid and b.date = T.maxdate
■
select
b.id2
,max(date)
,sum(case when b.oyako=1 then 1 else 0 end) as oya
,sum(case when b.oyako=2 then 1 else 0 end) as ko
from
table1 a
left outer join table2 b
on a.id = b.id2
group by
b.id2
a
select
sum(
case when b.`code2` = 1 then 1 END
) as "未解決"
,sum(
case when b.`code2` = 2 then 1 END
) as "解決中"
,sum(
case when b.`code2` = 3 then 1 END
) as "解決"
from
anken a
left join jokyo b on a.`id` = b.`ankenid`
where
b.`code1` = 1
■
id name
1 anken1
2 anken2
3 anken3
id ankenid code name
1 1 1 ko1-1
2 1 2 ko1-2
3 1 3 ko1-3
4 2 2 ko2-1
5 2 3 ko2-2
6 2 5 ko2-3
id ankenid kazu
1 1 1
2 1 2
3 1 3
4 2 2
5 2 3
select
a.id, min(b.code),x.q
from
anken a left join anken2 b on a.id = b.ankenid
left join (
select
a.id as p, sum(c.kazu) as q
from
anken a left join sum c on a.id = c.ankenid
group by a.id
) as x on a.id = x.p
group by
a.id
