MyBatisの練習
package hogehoge; import java.io.InputStream; import java.util.List; import java.util.Map; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class Main { public static void main(String[] args) throws Exception { try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) { SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); try (SqlSession session = factory.openSession()) { List<Map<String, Object>> result = session.selectList("sample.mybatis.selectTest", 1); result.forEach(row -> { System.out.println("---------------"); row.forEach((columnName, value) -> { System.out.printf("columnName=%s, value=%s%n", columnName, value); }); }); } } } } ||< >|java| <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <environments default="sample_id"> <environment id="sample_id"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="org.postgresql.Driver" /> <property name="url" value="jdbc:postgresql://localhost:5432/postgres" /> <property name="username" value="postgres" /> <property name="password" value="root" /> </dataSource> </environment> </environments> <mappers> <mapper resource="sample_mapper.xml" /> </mappers> </configuration> ||< >|java| <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="sample.mybatis"> <select id="selectTest" resultType="map"> select * from test_table where id=#{id} or '#{sam}' = '#{sam}' </select> </mapper>
jqを利用してjsonからcsvに変換する
cat << EOF > data.json { "responces": [ { "contentId": "1000", "results" : [ { "basicinfo": { "data_date": "2018-05-23", }, "metrics": { "cost": 300 } } ] } } EOF cat data.json | \ jq -r '.responces[] | {contentId, val:(.results[] | {date:.basicinfo.data_date, value:.metrics.cost})}' | \ jq -r '[.contentId, .value.date, .val.value]' | \ jq -r '@csv'
mavenの練習
src/resources配下
version=${project.version} env=${env} config=${config.directory}
pom.xmlの内容
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>jp.co.yunoshin.sample</groupId> <artifactId>maven-prac</artifactId> <version>0.0.1-SNAPSHOT</version> <profiles> <profile> <id>local</id> <activation> <activeByDefault>true</activeByDefault> </activation> <properties> <env>local</env> <config.directory>${env}.properties</config.directory> </properties> </profile> <profile> <id>stg</id> <properties> <env>stg</env> <config.directory>${env}.properties</config.directory> </properties> </profile> <profile> <id>product</id> <properties> <env>product</env> <config.directory>${env}.properties</config.directory> </properties> </profile> </profiles> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <java.version>1.8</java.version> </properties> <build> <outputDirectory>${project.build.directory}/classes</outputDirectory> <testOutputDirectory>${project.build.directory}/test-classes</testOutputDirectory> <resources> <resource> <directory>${project.basedir}/src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>true</filtering> </resource> </resources> <testResources> <testResource> <directory>${project.basedir}/src/test/resources</directory> </testResource> </testResources> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.6.1</version> <configuration> <source>${java.version}</source> <target>${java.version}</target> </configuration> </plugin> </plugins> </build> <dependencies> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> </dependency> </dependencies> </project>
logback.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE logback> <configuration> <root level="trace"> </root> <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender"> <encoder> <pattern>%msg%n</pattern> </encoder> </appender> <root> <appender-ref ref="STDOUT" /> </root> </configuration>
GASスニペット
function myFunction() { getFileWhile(); var root = DriveApp.getRootFolder() var key = root.getId(); Logger.log('key=' + key); } function getFileWhile() { var files = DriveApp.getFolderById('').getFiles(); Logger.log('hello1'); while(files.hasNext()) { var file = files.next(); var filename = file.getName() Logger.log(filename); } Logger.log('hello2'); } var file_name = 'file.txt'; var folder_name = 'dir1'; var folderID = DriveApp.getFoldersByName(folder_name).next().getId(); var fileIT = DriveApp.getFolderById(folderID).getFilesByName(file_name).next(); var textdata = fileIT.getBlob().getDataAsString('utf8'); Logger.log(textdata);
簡易 テーブル→xml変換
Sub Main() Dim sh As Worksheet Set sh = Sheets("シート名") Dim i As Integer Dim j As Integer i = 2 j = 1 Do While sh.Cells(i, 1) <> "" Dim line As String Dim header As String Dim v As String j = 2 line = "<record>" & vbCrLf Do While sh.Cells(1, j) <> "" header = sh.Cells(1, j) v = sh.Cells(i, j) line = line & " " & "<" & header & ">" & v & "</" & header & ">" & vbCrLf j = j + 1 Loop line = line & "</record>" & vbCrLf i = i + 1 OutputFile (line) Loop End Sub Sub OutputFile(str As String) Dim intFF As Integer intFF = FreeFile Open "path\to\data.xml" For Append As #intFF Print #intFF, str; Close #intFF End Sub
vba メッセージ構築処理まわり
文字列操作系の関数
'------------------------------------------------------------------------------ ' ファイル名 '------------------------------------------------------------------------------ ' 説明 '------------------------------------------------------------------------------ ' Date Name Desc '------------------------------------------------------------------------------ ' YYYY/MM/DD vb tarou 新規作成 '------------------------------------------------------------------------------ Option Explicit Public Function MyJoin(arr As Variant) Dim i As Integer Dim res As String For i = 0 To UBound(arr) If arr(i) <> "" Then res = res & arr(i) & "," End If Next If Len(res) > 0 Then res = Mid(res, 1, Len(res) - 1) End If MyJoin = res End Function Public Function MessageBuilder(str As String, param As Variant) As String Dim i As Integer Dim res As String res = str For i = 0 To UBound(param) res = Replace(res, "{" & i & "}", param(i)) Next i MessageBuilder = res End Function
csvからjsonに変換するマクロ
csvからjsonに変換するvba。
BigQueryのスキーマ定義用のjsonを作成する用に開発。エラーハンドリングなどは仕込んでいない。
Option Explicit Dim oFSO As Variant Public Sub Main() Dim targetBook As String targetBook = GetConfig("定義ファイル") Dim book As Workbook Set book = OpenBook(targetBook) Dim sh As Worksheet Set oFSO = CreateObject("Scripting.FileSystemObject") For Each sh In book.Worksheets readSchemaFromSheet sh Next sh book.Close Set oFSO = Nothing End Sub Public Function OpenBook(filename As String) Dim buf As String Dim book As Workbook Dim openedBook As Workbook buf = Dir(filename) If buf = "" Then Set OpenBook = Nothing Exit Function End If For Each book In Workbooks If book.name = buf Then Set OpenBook = book Exit Function End If Next book Application.ScreenUpdating = False Set OpenBook = Workbooks.Open(filename) Application.ScreenUpdating = True End Function Public Function GetConfig(key As String, Optional sheetName = "設定") Dim sh As Worksheet Dim findValue As String Set sh = ThisWorkbook.Worksheets(sheetName) findValue = sh.Range("B:B").Find(What:=key).Offset(0, 1).Value GetConfig = Trim(findValue) End Function Private Function readSchemaFromSheet(sh As Worksheet) Dim tableContent As String Dim record As String Dim no As String Dim name As String Dim types As String Dim mode As String Dim line As String Dim row As Integer row = 2 tableContent = "[" Do no = sh.Cells(row, 1).Value name = sh.Cells(row, 2).Value types = convertType(sh.Cells(row, 3).Value) mode = sh.Cells(row, 4).Value line = "" If no = "" Then Exit Do End If line = line + """name"": """ + name + """, ""type"": """ + types + """, ""mode"": """ + mode + """" record = "{" + line + "}" tableContent = tableContent + record + "," row = row + 1 Loop While no <> "" tableContent = Mid(tableContent, 1, Len(tableContent) - 1) tableContent = tableContent + "]" writeJsonFile sh.name, tableContent End Function Private Function writeJsonFile(filename As String, content As String) Dim path As String Dim fileNo As String Dim i As Integer path = oFSO.BuildPath(ThisWorkbook.path, filename + ".json") fileNo = FreeFile Open path For Output As fileNo Print #fileNo, content Close fileNo End Function Private Function convertType(t As String) Dim result As String Dim wk As String wk = Preprocessing(t) If wk = "文字列" Then convertType = "string" ElseIf wk = "数値" Then convertType = "integer" Else '何も変換しない convertType = wk End If End Function Private Function Preprocessing(v As String) Preprocessing = Trim(v) End Function
