POI 라이브러리 사용 Excel Loader 구현

Programming/Java 2017.02.28 22:18 Posted by 파란크리스마스

ExcelUtils

package com.intel4.poi;

import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtils {
	
	// 엑셀로드
	public static Workbook loalDocument(String filename,InputStream is) throws IOException {
		if (filename.endsWith(".xlsx")) {
			return new XSSFWorkbook(is);
		}
		else   if (filename.endsWith("xls")) {
			return new HSSFWorkbook(is);
		}
		else {
			throw new IllegalArgumentException("Unsupported extension for resource [" + filename + "]");
		}
	}
	
	// 문자열 반환
	public static String getStringValue(Row row, int col) {
		
		if (row == null) return null;
		if (row.getCell(col) == null) return null;
		
		String value = "";
		if (row.getCell(col).getCellType()==Cell.CELL_TYPE_NUMERIC) {
			double doubleValue = row.getCell(col).getNumericCellValue();
			if ((doubleValue == Math.floor(doubleValue)) && !Double.isInfinite(doubleValue)) {
				value = String.valueOf((int)row.getCell(col).getNumericCellValue());
			} else {
				value = String.valueOf(row.getCell(col).getNumericCellValue());
			}
			//System.out.println("doubleValue = " + doubleValue + "/" + value);
		} else if (row.getCell(col).getCellType()==Cell.CELL_TYPE_STRING) {
			try {
				value = row.getCell(col).getStringCellValue();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return value;
	}
	
	// int 값 반환
	public static int getIntegerValue(Row row, int col) {
		
		if (row == null) return 0;
		if (row.getCell(col) == null) return 0;
		
		int value = 0;
		if (row.getCell(col).getCellType()==Cell.CELL_TYPE_NUMERIC) {
			double doubleValue = row.getCell(col).getNumericCellValue();
			if ((doubleValue == Math.floor(doubleValue)) && !Double.isInfinite(doubleValue)) {
				value = (int)row.getCell(col).getNumericCellValue();
			} else {
				value = (int)row.getCell(col).getNumericCellValue();
			}
			//System.out.println("doubleValue = " + doubleValue + "/" + value);
		} else if (row.getCell(col).getCellType()==Cell.CELL_TYPE_STRING) {
			try {
				String stringValue = row.getCell(col).getStringCellValue();
				if (stringValue==null || stringValue.trim().length()==0 || !isNumeric(stringValue)) return 0;
				value = Integer.parseInt(row.getCell(col).getStringCellValue());
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return value;
	}
	
	// 숫자여부 판단
	public static boolean isNumeric(String str) {
	  return str.matches("-?\\d+(\\.\\d+)?");  //match a number with optional '-' and decimal.
	}

}

ExcelLoader

package com.intel4.poi;

import java.io.File;
import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelLoader {
	
	public static void main(String[] args) {
		ExcelLoader loader = new ExcelLoader();
		loader.load("WebContent\\sample.xlsx");
	}
	
	public void load(String filename) {
		int start_row = 1;
		
		FileInputStream inputStream = null;
		try {
			inputStream = new FileInputStream(new File(filename));
			Workbook workbook = ExcelUtils.loalDocument(filename, inputStream);
			
			// Excel 시트
			Sheet sheet = workbook.getSheetAt(0);
			
			// 해당 시트의 마지막 Row까지 반복
			for (int row_index=start_row; row_index<=sheet.getLastRowNum(); row_index++) {
				// Excel Row
				Row row = sheet.getRow(row_index);
				// row가 null인 경우 다음 row로...
				if (row == null) continue;
				
				int basic_seq = ExcelUtils.getIntegerValue(row, 0);
				int seq = ExcelUtils.getIntegerValue(row, 1);
				int problem = ExcelUtils.getIntegerValue(row, 2);
				String answer = ExcelUtils.getStringValue(row, 3);
				int hint  = ExcelUtils.getIntegerValue(row, 4);
				String subject1 = ExcelUtils.getStringValue(row, 5);
				String subject2 = ExcelUtils.getStringValue(row, 6);
				int level = ExcelUtils.getIntegerValue(row, 7);
				
				System.out.println(basic_seq + "/" + seq + "/" + problem +"/"+ answer + "/" + hint + "/" + subject1 + "/" + subject2 + "/" +  level);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (inputStream!=null) {
				try {
					inputStream.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}

	}

}
저작자 표시
신고

Banana PI - BPI-M2+

OS/Raspberry Pi 2017.02.26 22:23 Posted by 파란크리스마스

Banana PI - BPI-M2+

출처 : BPI-M2+
【BPI-M2+】之mjpg-streamer测试和体验- Bananap讨论区- 烽火社区 ...
http://forum.banana-pi.org/c/Banana-pi-BPI-M2

SD 확장

SD 확장전 용량확인

$ df -l
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/root        7156088 4680932   2111636  69% /
devtmpfs          380800       0    380800   0% /dev
tmpfs             512036     800    511236   1% /dev/shm
tmpfs             512036   20248    491788   4% /run
tmpfs               5120       4      5116   1% /run/lock
tmpfs             512036       0    512036   0% /sys/fs/cgroup
/dev/mmcblk0p1    261868  213188     48680  82% /boot
tmpfs             102408      36    102372   1% /run/user/1000

SD FDisk

pi@bpi-iot-ros-ai:~$ sudo fdisk /dev/mmcblk0

Welcome to fdisk (util-linux 2.27.1).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.


Command (m for help): p
Disk /dev/mmcblk0: 14.7 GiB, 15811477504 bytes, 30881792 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x0006c94a

Device         Boot  Start      End  Sectors  Size Id Type
/dev/mmcblk0p1 *    204800   729087   524288  256M  c W95 FAT32 (LBA)
/dev/mmcblk0p2      729088 15269887 14540800    7G 83 Linux

Command (m for help): d
Partition number (1,2, default 2): 2

Partition 2 has been deleted.

Command (m for help): n
Partition type
   p   primary (1 primary, 0 extended, 3 free)
   e   extended (container for logical partitions)
Select (default p): p
Partition number (2-4, default 2): 2
First sector (2048-30881791, default 2048): 729088
Last sector, +sectors or +size{K,M,G,T,P} (729088-30881791, default 30881791): 

Created a new partition 2 of type 'Linux' and of size 14.4 GiB.

Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Re-reading the partition table failed.: Device or resource busy

The kernel still uses the old table. The new table will be used at the next reboot or after you run partprobe(8) or kpartx(8).

pi@bpi-iot-ros-ai:~$ sudo shutdown -r now

SD 확장

$ sudo resize2fs /dev/mmcblk0p2
resize2fs 1.42.13 (17-May-2015)
Filesystem at /dev/mmcblk0p2 is mounted on /; on-line resizing required
old_desc_blocks = 1, new_desc_blocks = 1
The filesystem on /dev/mmcblk0p2 is now 3769088 (4k) blocks long.

SD 확장 확인

$ df -l
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/root       14839632 4653484   9513188  33% /
devtmpfs          380800       0    380800   0% /dev
tmpfs             512036     316    511720   1% /dev/shm
tmpfs             512036    7456    504580   2% /run
tmpfs               5120       4      5116   1% /run/lock
tmpfs             512036       0    512036   0% /sys/fs/cgroup
/dev/mmcblk0p1    261868  213188     48680  82% /boot
tmpfs             102408      28    102380   1% /run/user/1000

How to burn Linux image to eMMC

출처 : How to burn Linux image to eMMC
bpi-copy command

root@bpi-iot-ros-ai:~# uname -a
Linux bpi-iot-ros-ai 3.4.112-sun8i #2 SMP PREEMPT Mon May 30 20:34:33 CST 2016 armv7l armv7l armv7l GNU/Linux
root@bpi-iot-ros-ai:~# fdisk -l
Disk /dev/mmcblk0: 14.7 GiB, 15811477504 bytes, 30881792 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x0006c94a

Device         Boot  Start      End  Sectors  Size Id Type
/dev/mmcblk0p1 *    204800   729087   524288  256M  c W95 FAT32 (LBA)
/dev/mmcblk0p2      729088 30881791 30152704 14.4G 83 Linux


Disk /dev/mmcblk1: 7.3 GiB, 7818182656 bytes, 15269888 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x00000000


Disk /dev/mmcblk1boot1: 4 MiB, 4194304 bytes, 8192 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mmcblk1boot0: 4 MiB, 4194304 bytes, 8192 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

이미지 복원

이미지 복원이 완료되면 SD 메모리를 제거하고 재부팅

$ sudo dd if=2016-07-21-ubuntu-mate-16.04-desktop-armhf-raspberry-pi-bpi-m2p-sd-emmc.img of=/dev/mmcblk1 bs=10MB
781+1 records in
781+1 records out
7818182656 bytes (7.8 GB, 7.3 GiB) copied, 1023.46 s, 7.6 MB/s

eMMC 부팅(SD동시 사용 - 적용실패)

출처 : Banana Pi M2+ with system on emmc, data on sd card - Allwinner H2/H3
Boot from eMMC with blank SD in slot (BPI-M3)

$ cat /etc/fstab
proc            /proc           proc    defaults          0       0
/dev/mmcblk0p2  /               ext4   defaults,noatime  0       1
/dev/mmcblk0p1  /boot/          vfat    defaults          0       2
$ lsblk
NAME         MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
mmcblk0boot0 179:16   0    4M  1 disk 
mmcblk0boot1 179:32   0    4M  1 disk 
mmcblk0      179:0    0  7.3G  0 disk 
쒋mmcblk0p1  179:1    0  256M  0 part /boot
붴mmcblk0p2  179:2    0    7G  0 part /
$ cat /boot/boot.cmd
#setenv device mmc
#setenv partition 0:1
setenv bpi bananapi
setenv board bpi-m64
setenv chip a64
setenv service linux
setenv kernel Image
setenv initrd initrd.img
setenv dtb pine64-plus.dtb
 
setenv bootargs "console=ttyS0,115200n8 no_console_suspend earlycon=uart,mmio32,0x01c28000 mac_addr=${ethaddr} board=${board} root=${root} rootwait panic=10 consoleblank=0 enforcing=0 loglevel=2"
 
 
setenv fdt_filename "${bpi}/${board}/${service}/${dtb}"
setenv kernel_filename "${bpi}/${board}/${service}/${kernel}"
setenv initrd_filename "${bpi}/${board}/${service}/${initrd}"
 
run load_dtb load_kernel load_initrd boot_kernel
 
## Recompile with:
## mkimage -C none -A arm -T script -d /boot/boot.cmd /boot/boot.scr
## mkimage -C none -A arm -T script -d boot.cmd boot.scr

generate boot.src

$ sudo mkimage -C none -A arm -T script -d /boot/boot.cmd /boot/boot.scr
Image Name:   
Created:      Sun Feb  26 11:55:34 2017
Image Type:   ARM Linux Script (uncompressed)
Data Size:    805 Bytes = 0.79 kB = 0.00 MB
Load Address: 00000000
Entry Point:  00000000
Contents:
   Image 0: 797 Bytes = 0.78 kB = 0.00 MB
$ sudo shutdown -r now

GPIO사용 (BPI-WiringPi)

출처 : Supports BananaPi BPI -M1 / M1Plus / M2 / M2P / M3

기존 설치된 wiringpi 삭제

$ sudo apt-get remove wiringpi
$ sudo apt autoremove

wiringpi 설치

$ git clone https://github.com/BPI-SINOVOIP/BPI-WiringPi.git -b BPI_M2P
$ cd BPI-WiringPi
$ chmod +x ./build
$ sudo ./build

GPIO 확인

$ gpio -v
gpio version: 2.26
Copyright (c) 2012-2015 Gordon Henderson
This is free software with ABSOLUTELY NO WARRANTY.
For details type: gpio -warranty
 
Banana Pi Details:
  Type: Model BM, Revision: 1.2, Memory: 1024MB, Maker: BPI 
$ gpio readall
 +-----+-----+---------+------+---+---Pi ---+---+------+---------+-----+-----+
 | CPU | wPi |    Name   | Mode | V | Physical | V | Mode |   Name    | wPi | CPU |
 +-----+-----+-----------+------+---+----++----+---+------+-----------+-----+-----+
 |     |     |      3.3v |      |   |  1 || 2  |   |      | 5v        |     |     |
 |  12 |   8 |     SDA.1 | ALT5 | 0 |  3 || 4  |   |      | 5V        |     |     |
 |  11 |   9 |     SCL.1 | ALT5 | 0 |  5 || 6  |   |      | GND       |     |     |
 |   6 |   7 |      PWM1 | ALT3 | 0 |  7 || 8  | 0 | ALT4 | UART3-TX  | 15  | 13  |
 |     |     |       GND |      |   |  9 || 10 | 0 | ALT4 | UART3-RX  | 16  | 14  |
 |   1 |   0 |  UART2-RX | ALT5 | 0 | 11 || 12 | 0 | OUT  | UART3-CTS | 1   | 16  |
 |   0 |   2 |  UART2-TX | ALT5 | 0 | 13 || 14 |   |      | GND       |     |     |
 |   3 |   3 | UART2-CTS | ALT5 | 0 | 15 || 16 | 1 | ALT4 | UART3-RTS | 4   | 15  |
 |     |     |      3.3v |      |   | 17 || 18 | 0 | ALT3 | GPIO.PC04 | 5   | 68  |
 |  64 |  12 | SPI0_MOSI | ALT4 | 0 | 19 || 20 |   |      | GND       |     |     |
 |  65 |  13 | SPI0_MISO | ALT4 | 0 | 21 || 22 | 0 | ALT5 | UART2-RTS | 6   | 2   |
 |  66 |  14 |  SPI0_CLK | ALT4 | 0 | 23 || 24 | 0 | ALT4 | SPI0-CS   | 10  | 67  |
 |     |     |       GND |      |   | 25 || 26 | 0 | ALT3 | GPIO.PL07 | 11  | 71  |
 |  19 |  30 |     SDA.0 | ALT4 | 0 | 27 || 28 | 0 | ALT4 | SCL.0     | 31  | 18  |
 |   7 |  21 | GPIO.PA07 | ALT3 | 0 | 29 || 30 |   |      | GND       |     |     |
 |   8 |  22 | GPIO.PA08 | ALT3 | 0 | 31 || 32 | 0 | ALT3 | GPIO.PL02 | 26  | 354 |
 |   9 |  23 | GPIO.PA09 | ALT3 | 0 | 33 || 34 |   |      | GND       |     |     |
 |  10 |  24 | GPIO.PA10 | ALT3 | 0 | 35 || 36 | 0 | ALT3 | GPIO.PL04 | 27  | 356 |
 |  17 |  25 | SPDIF-OUT | ALT3 | 0 | 37 || 38 | 0 | ALT3 | GPIO.PA21 | 28  | 21  |
 |     |     |       GND |      |   | 39 || 40 | 0 | ALT3 | GPIO.PA20 | 29  | 20  |
 +-----+-----+-----------+------+---+----++----+---+------+-----------+-----+-----+
 | CPU | wPi |    Name   | Mode | V | Physical | V | Mode |   Name    | wPi | CPU |
 +-----+-----+---------+------+---+---Pi ---+---+------+---------+-----+-----+

detect I2C chips

출처 : i2cdetect(8): detect I2C chips - Linux man page

$ ls /dev/*i2c*
/dev/i2c-0  /dev/i2c-1
$ i2cdetect -y 0
     0  1  2  3  4  5  6  7  8  9  a  b  c  d  e  f
00:          -- -- -- -- -- -- -- -- -- -- -- -- -- 
10: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
20: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
30: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
40: 40 -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
50: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
60: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
70: 70 -- -- -- -- -- -- --  

jdk1.8 설치

출처 : 우분투(Ubuntu) Oracle(Sun) Java JDK 설치 - 사르기스

OpenJDK 제거

$ sudo apt-get purge openjdk*

repository 추가

$ sudo add-apt-repository ppa:webupd8team/java

repository index 업데이트

$ sudo apt-get update

JDK 설치

$ sudo apt-get install oracle-java8-installer

JAVA_HOME 환경변수 추가

$ sudo vi /etc/profile

내용 추가 export JAVA_HOME=/usr/lib/jvm/java-8-oracle

export JAVA_HOME=/usr/lib/jvm/java-8-oracle

pi4j

촐처 : Java I/O library for Raspberry Pi (GPIO, I2C, SPI, UART) http://www.pi4j.com
BUILDING AN EXECUTABLE PI4J JAR

$ sudo apt install maven
$ git clone https://github.com/Pi4J/pi4j.git
$ cd pi4j/pi4j-native
$ mvn package

pi4j pwm 오류

촐처 : How to: banana pi BPI-M3 install BPI-WiringPi - Linux - banana pi ...

PWM 관련 오류가 있어 수정해 보려고 했으나 포기

val pwmWrite 0 <= X <= 1024
Or you can set new range by yourself by pwmSetRange(range
저작자 표시
신고

Spring - Drag & Drop 파일 업로드

Programming/Java 2017.02.26 18:42 Posted by 파란크리스마스

Spring - Drag & Drop 파일 업로드

출처 : Husk's repository :: 드래그 앤 드롭 파일 업로드 스프링 예제

관련 라이브러리 추가

commons-io-2.4.jar
commons-fileupload-1.3.jar

WebContent\WEB-INF\iot-servlet.xml

	<!-- ========================= Multipart Form-Data Resolver ========================= -->
	<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
		<!-- max upload size in bytes -->
		<property name="maxUploadSize" value="20971520" /> <!-- 20MB -->
		<!-- max size of file in memory (in bytes) -->
		<property name="maxInMemorySize" value="1048576" /> <!-- 1MB -->
	</bean>

업로드 jsp (WebContent\WEB-INF\jsp\dragdrop\fileUpload.jsp)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page session="false" %>
<html>
    <head>
        <title>Test</title>
        <script type="text/javascript" src="http://code.jquery.com/jquery-1.11.3.js"></script>
        <style>
            .dragAndDropDiv {
                border: 2px dashed #92AAB0;
                width: 650px;
                height: 200px;
                color: #92AAB0;
                text-align: center;
                vertical-align: middle;
                padding: 10px 0px 10px 10px;
                font-size:200%;
                display: table-cell;
            }
            .progressBar {
                width: 200px;
                height: 22px;
                border: 1px solid #ddd;
                border-radius: 5px; 
                overflow: hidden;
                display:inline-block;
                margin:0px 10px 5px 5px;
                vertical-align:top;
            }
              
            .progressBar div {
                height: 100%;
                color: #fff;
                text-align: right;
                line-height: 22px; /* same as #progressBar height if we want text middle aligned */
                width: 0;
                background-color: #0ba1b5; border-radius: 3px; 
            }
            .statusbar{
                border-top:1px solid #A9CCD1;
                min-height:25px;
                width:99%;
                padding:10px 10px 0px 10px;
                vertical-align:top;
            }
            .statusbar:nth-child(odd){
                background:#EBEFF0;
            }
            .filename{
                display:inline-block;
                vertical-align:top;
                width:250px;
            }
            .filesize{
                display:inline-block;
                vertical-align:top;
                color:#30693D;
                width:100px;
                margin-left:10px;
                margin-right:5px;
            }
            .abort{
                background-color:#A8352F;
                -moz-border-radius:4px;
                -webkit-border-radius:4px;
                border-radius:4px;display:inline-block;
                color:#fff;
                font-family:arial;font-size:13px;font-weight:normal;
                padding:4px 15px;
                cursor:pointer;
                vertical-align:top
            }
        </style>
        <script type="text/javascript">
            $(document).ready(function(){
                var objDragAndDrop = $(".dragAndDropDiv");
                 
                $(document).on("dragenter",".dragAndDropDiv",function(e){
                    e.stopPropagation();
                    e.preventDefault();
                    $(this).css('border', '2px solid #0B85A1');
                });
                $(document).on("dragover",".dragAndDropDiv",function(e){
                    e.stopPropagation();
                    e.preventDefault();
                });
                $(document).on("drop",".dragAndDropDiv",function(e){
                     
                    $(this).css('border', '2px dotted #0B85A1');
                    e.preventDefault();
                    var files = e.originalEvent.dataTransfer.files;
                 
                    handleFileUpload(files,objDragAndDrop);
                });
                 
                $(document).on('dragenter', function (e){
                    e.stopPropagation();
                    e.preventDefault();
                });
                $(document).on('dragover', function (e){
                  e.stopPropagation();
                  e.preventDefault();
                  objDragAndDrop.css('border', '2px dotted #0B85A1');
                });
                $(document).on('drop', function (e){
                    e.stopPropagation();
                    e.preventDefault();
                });
                 
                function handleFileUpload(files,obj)
                {
                   for (var i = 0; i < files.length; i++) 
                   {
                        var fd = new FormData();
                        fd.append('file', files[i]);
                  
                        var status = new createStatusbar(obj); //Using this we can set progress.
                        status.setFileNameSize(files[i].name,files[i].size);
                        sendFileToServer(fd,status);
                  
                   }
                }
                 
                var rowCount=0;
                function createStatusbar(obj){
                         
                    rowCount++;
                    var row="odd";
                    if(rowCount %2 ==0) row ="even";
                    this.statusbar = $("<div class='statusbar "+row+"'></div>");
                    this.filename = $("<div class='filename'></div>").appendTo(this.statusbar);
                    this.size = $("<div class='filesize'></div>").appendTo(this.statusbar);
                    this.progressBar = $("<div class='progressBar'><div></div></div>").appendTo(this.statusbar);
                    this.abort = $("<div class='abort'>중지</div>").appendTo(this.statusbar);
                     
                    obj.after(this.statusbar);
                  
                    this.setFileNameSize = function(name,size){
                        var sizeStr="";
                        var sizeKB = size/1024;
                        if(parseInt(sizeKB) > 1024){
                            var sizeMB = sizeKB/1024;
                            sizeStr = sizeMB.toFixed(2)+" MB";
                        }else{
                            sizeStr = sizeKB.toFixed(2)+" KB";
                        }
                  
                        this.filename.html(name);
                        this.size.html(sizeStr);
                    }
                     
                    this.setProgress = function(progress){       
                        var progressBarWidth =progress*this.progressBar.width()/ 100;  
                        this.progressBar.find('div').animate({ width: progressBarWidth }, 10).html(progress + "% ");
                        if(parseInt(progress) >= 100)
                        {
                            this.abort.hide();
                        }
                    }
                     
                    this.setAbort = function(jqxhr){
                        var sb = this.statusbar;
                        this.abort.click(function()
                        {
                            jqxhr.abort();
                            sb.hide();
                        });
                    }
                }
                 
                function sendFileToServer(formData,status)
                {
                    var uploadURL = "<c:url value="/dragdrop/fileUpload/post.iot"/>"; //Upload URL
                    var extraData ={}; //Extra Data.
                    var jqXHR=$.ajax({
                            xhr: function() {
                            var xhrobj = $.ajaxSettings.xhr();
                            if (xhrobj.upload) {
                                    xhrobj.upload.addEventListener('progress', function(event) {
                                        var percent = 0;
                                        var position = event.loaded || event.position;
                                        var total = event.total;
                                        if (event.lengthComputable) {
                                            percent = Math.ceil(position / total * 100);
                                        }
                                        //Set progress
                                        status.setProgress(percent);
                                    }, false);
                                }
                            return xhrobj;
                        },
                        url: uploadURL,
                        type: "POST",
                        contentType:false,
                        processData: false,
                        cache: false,
                        data: formData,
                        success: function(data){
                            status.setProgress(100);
                  
                            //$("#status1").append("File upload Done<br>");           
                        }
                    }); 
                  
                    status.setAbort(jqXHR);
                }
                 
            });
        </script>
    </head>
     
    <body>
        <div id="fileUpload" class="dragAndDropDiv">Drag & Drop Files Here</div>
    </body>
</html>

Controller

package com.iot.controller;

import java.io.File;
import java.util.Iterator;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

/**
 * Handles requests for the application home page.
 */

@Controller
public class FileUploadDragDropController {

	@RequestMapping(value = "/dragdrop/fileUpload.iot", method = RequestMethod.GET)
	public String dragAndDrop(Model model) {
		return "/dragdrop/fileUpload";
	}

	@RequestMapping(value = "/dragdrop/fileUpload/post.iot") // ajax에서 호출하는 부분
	@ResponseBody
	public String upload(MultipartHttpServletRequest multipartRequest) { // Multipart로
																																				// 받는다.

		Iterator<String> itr = multipartRequest.getFileNames();

		String filePath = "C:/test"; // 설정파일로 뺀다.

		while (itr.hasNext()) { // 받은 파일들을 모두 돌린다.

			/*
			 * 기존 주석처리 MultipartFile mpf = multipartRequest.getFile(itr.next());
			 * String originFileName = mpf.getOriginalFilename(); System.out.println(
			 * "FILE_INFO: "+originFileName); //받은 파일 리스트 출력'
			 */

			MultipartFile mpf = multipartRequest.getFile(itr.next());

			String originalFilename = mpf.getOriginalFilename(); // 파일명

			String fileFullPath = filePath + "/" + originalFilename; // 파일 전체 경로

			try {
				// 파일 저장
				mpf.transferTo(new File(fileFullPath)); // 파일저장 실제로는 service에서 처리

				System.out.println("originalFilename => " + originalFilename);
				System.out.println("fileFullPath => " + fileFullPath);

			} catch (Exception e) {
				System.out.println("postTempFile_ERROR======>" + fileFullPath);
				e.printStackTrace();
			}

		}

		return "success";
	}

}

업로드된 폴더 웹에서 접근하기

Tomcat 설정파일 server.xml에 <Context docBase="C:/test" path="/test" reloadable="true"/> 를 Host 테그 전에 추가

        <Context docBase="C:/test" path="/test" reloadable="true"/>
      </Host>
    </Engine>
  </Service>
</Server>

Spring - Drag & Drop 파일 업로드 (img_type, img_id 속성 추가)

한 페이지내에서 여러개의 파일 업로드를 구현하기 위해서 img_type, img_id 속성을 추가해서 여러개의 업로드가 발생하더라고 구분 할 수 있게 구현

html 페이지

<div class="dragAndDropDiv" img_type="uploadarea" img_id="1">
<div class="dragAndDropDiv" img_type="uploadarea" img_id="2">

파일 올려진 경우 이벤트에서 img_type, img_id 속성 가져오기 - javascript

	$(document).on("drop",".dragAndDropDiv",function(e){
		$(this).css('border', '2px dotted #0B85A1');
		e.preventDefault();
		var files = e.originalEvent.dataTransfer.files;
		
		// img_type, img_id 속성 가져오기
		var img_type = $(e.target).attr('img_type');
		var img_id = $(e.target).attr('img_id');
		
		handleFileUpload(img_type, img_id, files,objDragAndDrop);
	});

FormData 구성 img_type, img_id 포함 - javascript

	function handleFileUpload(img_type, img_id, files,obj) {
		for (var i = 0; i < files.length; i++) {
			var fd = new FormData();
			fd.append('img_type', img_type);
			fd.append('img_id', img_id);
			fd.append('file', files[i]);
			
			var status = undefined;
			//var status = new createStatusbar(obj); //Using this we can set progress.
			//status.setFileNameSize(files[i].name,files[i].size);
			sendFileToServer(fd,status);
		}
	}

업로드 완료 처리 - javascript

img_type이 request인 경우 img테그의 src 속성 값 변경 처리

function sendFileToServer(formData,status) {
	var uploadURL = "<c:url value="/dragdrop/fileUpload/post.do"/>"; //Upload URL

... 생략 ...

		data: formData,
		success: function(data) { // 업로드가 완료되면 호출되는 메소드
			if (status != undefined) {
				status.setProgress(100);
			}
			
			// 
			console.log('img_type = ' + data.img_type);
			console.log('img_id = ' + data.img_id);
			$.each(data.files, function(index, file) {
				console.log('filename = ' + file);
				
				if (data.img_type=='request') {
					var targetObj = $(".dragAndDropDiv[img_type='"+data.img_type+"'][img_id='"+data.img_id+"']");
					targetObj.attr('src', '/test/' + file);
					targetObj.attr('uuid', file);
				}
			});
		}
	});

javascript 전체

$(document).ready(function() {
	
	var objDragAndDrop = $(".dragAndDropDiv");
	   
	$(document).on("dragenter",".dragAndDropDiv",function(e){
		e.stopPropagation();
		e.preventDefault();
		$(this).css('border', '2px solid #0B85A1');
	});
	$(document).on("dragover",".dragAndDropDiv",function(e){
		e.stopPropagation();
		e.preventDefault();
	});
	$(document).on("drop",".dragAndDropDiv",function(e){
		$(this).css('border', '2px dotted #0B85A1');
		e.preventDefault();
		var files = e.originalEvent.dataTransfer.files;
		
		// img_type, img_id 속성 가져오기
		var img_type = $(e.target).attr('img_type');
		var img_id = $(e.target).attr('img_id');
		
		handleFileUpload(img_type, img_id, files,objDragAndDrop);
	});
	$(document).on('dragenter', function (e){
		e.stopPropagation();
		e.preventDefault();
	});
	$(document).on('dragover', function (e){
		e.stopPropagation();
		e.preventDefault();
		objDragAndDrop.css('border', '2px dotted #0B85A1');
	});
	$(document).on('drop', function (e){
		e.stopPropagation();
		e.preventDefault();
	});
	  
	function handleFileUpload(img_type, img_id, files,obj) {
		for (var i = 0; i < files.length; i++) {
			var fd = new FormData();
			fd.append('img_type', img_type);
			fd.append('img_id', img_id);
			fd.append('file', files[i]);
			
			var status = undefined;
			//var status = new createStatusbar(obj); //Using this we can set progress.
			//status.setFileNameSize(files[i].name,files[i].size);
			sendFileToServer(fd,status);
		}
	}
	  
	var rowCount=0;
	function createStatusbar(obj){
		
		rowCount++;
		var row="odd";
		if(rowCount %2 ==0) row ="even";
		this.statusbar = $("<div class='statusbar "+row+"'></div>");
		this.filename = $("<div class='filename'></div>").appendTo(this.statusbar);
		this.size = $("<div class='filesize'></div>").appendTo(this.statusbar);
		this.progressBar = $("<div class='progressBar'><div></div></div>").appendTo(this.statusbar);
		this.abort = $("<div class='abort'>중지</div>").appendTo(this.statusbar);
		
		obj.after(this.statusbar);
		
		this.setFileNameSize = function(name,size){
			var sizeStr="";
			var sizeKB = size/1024;
			if(parseInt(sizeKB) > 1024) {
				var sizeMB = sizeKB/1024;
				sizeStr = sizeMB.toFixed(2)+" MB";
			} else {
				sizeStr = sizeKB.toFixed(2)+" KB";
			}
			
			this.filename.html(name);
			this.size.html(sizeStr);
		}
		  
		this.setProgress = function(progress){      
			var progressBarWidth =progress*this.progressBar.width()/ 100; 
			this.progressBar.find('div').animate({ width: progressBarWidth }, 10).html(progress + "% ");
			if(parseInt(progress) >= 100) {
				this.abort.hide();
			}
		}
		  
		this.setAbort = function(jqxhr){
			var sb = this.statusbar;
			this.abort.click(function() {
				jqxhr.abort();
				sb.hide();
			});
		}
	}
	  
	function sendFileToServer(formData,status) {
		var uploadURL = "<c:url value="/dragdrop/fileUpload/post.do"/>"; //Upload URL
		var extraData ={}; //Extra Data.
		var jqXHR=$.ajax({
				xhr: function() {
				var xhrobj = $.ajaxSettings.xhr();
				if (xhrobj.upload) {
					xhrobj.upload.addEventListener('progress', function(event) {
						var percent = 0;
						var position = event.loaded || event.position;
						var total = event.total;
						if (event.lengthComputable) {
							percent = Math.ceil(position / total * 100);
						}
						//Set progress
						if (status != undefined) {
							status.setProgress(percent);
						}
					}, false);
				}
				return xhrobj;
			},
			url: uploadURL,
			type: "POST",
			dataType : 'json',
			contentType:false,
			processData: false,
			cache: false,
			data: formData,
			success: function(data) { // 업로드가 완료되면 호출되는 메소드
				if (status != undefined) {
					status.setProgress(100);
				}
				
				// 
				console.log('img_type = ' + data.img_type);
				console.log('img_id = ' + data.img_id);
				$.each(data.files, function(index, file) {
					console.log('filename = ' + file);
					
					if (data.img_type=='request') {
						var targetObj = $(".dragAndDropDiv[img_type='"+data.img_type+"'][img_id='"+data.img_id+"']");
						targetObj.attr('src', '/test/' + file);
						targetObj.attr('uuid', file);
					} else {
						var uploadTbody = $('#uploadTbody');
						var row = $('<span style="display : block;"></span>');
						row.text(file);
						uploadTbody.append(row);
						
						// 배열 초기화
						var uploadFilenames = new Array(9);
						for (i=0; i<uploadFilenames.length; i++) {
							uploadFilenames[i] = '';
						}
						var t_fileList = $('#uploadTbody>span');
						
						/*
						for (i=1; i<=t_fileList.length; i++) {
							var file_value = $('#uploadTbody>span:nth-child('+i+')');
							console.log(i + '/' + file_value.text());
							uploadFilenames[i-1] = file_value.text();
						}
						*/
						
						console.log(t_fileList.length);
						$.each( t_fileList, function( key, value ) {
							console.log( key + ": " + $(value).text() );
							uploadFilenames[key] = $(value).text();
						});
						
						console.log(uploadFilenames[0]);
						console.log(uploadFilenames[1]);
						console.log(uploadFilenames[2]);
						console.log(uploadFilenames[3]);
					}
				});
			
				//$("#status1").append("File upload Done<br>");          
			}
		});
		
		if (status != undefined) {
			status.setAbort(jqXHR);
		}
	}
});

업로드 메소드

	@RequestMapping(value = "/dragdrop/fileUpload/post.do") // ajax에서 호출하는 부분
	@ResponseBody
	public String upload(
			@RequestParam(value = "img_type", required = false, defaultValue = "") String img_type, 
			@RequestParam(value = "img_id", required = false, defaultValue = "-1") int img_id, 
			MultipartHttpServletRequest multipartRequest) // Multipart로 받는다.
	{ 

		System.out.println("img_type = " + img_type + "/img_id = " + img_id);

		Iterator<String> itr = multipartRequest.getFileNames();

		String filePath = "C:/test"; // 설정파일로 뺀다.
		String targetFilename = "";
		
		JSONArray filenames = new JSONArray();

		while (itr.hasNext()) { // 받은 파일들을 모두 돌린다.

			/*
			 * 기존 주석처리 MultipartFile mpf = multipartRequest.getFile(itr.next());
			 * String originFileName = mpf.getOrigin'alFilename(); System.out.println(
			 * "FILE_INFO: "+originFileName); //받은 파일 리스트 출력
			 */

			MultipartFile mpf = multipartRequest.getFile(itr.next());

			String originalFilename = mpf.getOriginalFilename(); // 파일명

			// 파일 확장자 추출
			String fileExt = FileUtils.getFileExt(originalFilename);

			UUID uuid = UUID.randomUUID();
			targetFilename = uuid.toString() + "." + fileExt.toLowerCase();

			String fileFullPath = filePath + "/" + targetFilename; // 파일 전체 경로

			try {
				// 파일 저장
				mpf.transferTo(new File(fileFullPath)); // 파일저장 실제로는 service에서 처리

				System.out.println("originalFilename => " + originalFilename);
				System.out.println("fileFullPath => " + fileFullPath);

			} catch (Exception e) {
				System.out.println("postTempFile_ERROR======>" + fileFullPath);
				e.printStackTrace();
			}
			
			filenames.add(targetFilename);

		}
		
		JSONObject jobj = new JSONObject();
		jobj.put("img_type", img_type);
		jobj.put("img_id", img_id);
		jobj.put("files", filenames);

		return jobj.toJSONString();
	}
저작자 표시
신고

BPI-M2+ mjpg-streamer

OS/Raspberry Pi 2017.02.26 04:49 Posted by 파란크리스마스

BPI-M2+ mjpg-streamer

출처 : 【BPI-M2+】之mjpg-streamer测试和体验- Bananap讨论区- 烽火社区 ...
MJPG-Streamer › Wiki › ubuntuusers.de
라즈베리파이에서 웹캠 mjpg-streamer 따라하기|작성자 서리
mjpg-streamer 웹캠 영상 스트리밍 | Mungrrr

관련 라이브러리 설치

$ sudo apt-get install libjpeg8-dev libv4l-dev subversion
$ sudo apt-get install imagemagick

mjpg-streamer 컴파일 및 설치

출처 : http://www.lavrsen.dk/svn/motion/tags/3.2.9/picture.c

$ svn co https://svn.code.sf.net/p/mjpg-streamer/code/mjpg-streamer mjpg-streamer
$ cd mjpg-streamer
$ make USE_LIBV4L2=true clean all
$ sudo make install
install --mode=755 mjpg_streamer /usr/local/bin
install --mode=644 input_ov5640.so input_uvc.so output_file.so output_udp.so output_http.so input_testpicture.so input_file.so /usr/local/lib/
install --mode=755 -d /usr/local/www
install --mode=644 -D www/* /usr/local/www

input_ov5640.so

input_ov5640.tar.gz

mjpg-streamer의 input_uvc.so는 YUV만 지원하고, BPI-M2+의 카메라인 OV5640는 YUV420코덱만 지원하기 때문에, mjpg-streamer에서 사용 할 수 없습니다.

제가 작업한 OV5640의 YUV420코덱을 지원하는 so 파일을 공개 합니다.
BPI-M2+의 카메라인 OV5640를 사용해서 mjpg-streamer로 스크리밍 하시려면 첨부한 파일을 사용하세요.

LD_LIBRARY_PATH 경로 추가

$ echo "export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:/usr/local/lib:/usr/local/lib/mjpg-streamer" | sudo tee -a /etc/profile

mjpg-streamer 실행

$ ./mjpg_streamer -i "./input_ov5640.so -y -n" -o "./output_http.so -w ./www"
MJPG Streamer Version: svn rev: 3:172M
 i: Using V4L2 device.: /dev/video0
 i: Desired Resolution: 640 x 480
 i: Frames Per Second.: 5
 i: Format............: YUV420
 i: JPEG Quality......: 80
 o: www-folder-path...: ./www/
 o: HTTP TCP port.....: 8080
 o: username:password.: disabled
 o: commands..........: enabled

서비스등록

$ sudo vi /etc/init.d/mjpg_streamer

mjpg_streamer 파일 내용

#!/bin/bash
# /etc/init.d/mjpg_streamer.sh
# v0.2 phillips321.co.uk
### BEGIN INIT INFO
# Provides:          mjpg_streamer.sh
# Required-Start:    $network
# Required-Stop:     $network
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: mjpg_streamer for webcam
# Description:       Streams /dev/video0 to http://IP/?action=stream
### END INIT INFO

#
export width=640
export height=480
export fps=10
export SHUTDOWN_WAIT=2
export QUALITY=75

if [ -n "$2" ]; then
  width=$2 
fi

if [ -n "$3" ]; then
  height=$3
fi

if [ -n "$4" ]; then
  fps=$4
fi

export LD_MJPG_STREAMER=/usr/local/lib

f_message(){
        echo "[+] $1"
}

mjpg_streamer_pid() {
  echo `ps aux | grep mjpg_streamer | grep -v grep | awk '{ print $2 }'`
}

start() {
  pid=$(mjpg_streamer_pid)
  if [ -n "$pid" ] 
  then
    echo "mjpg_streamer is already running (pid: $pid)"
  else
    # Start mjpg_streamer
		f_message "Starting mjpg_streamer"
		mjpg_streamer -b -i "$LD_MJPG_STREAMER/input_ov5640.so -r "$width"x"$height" -f $fps -q $QUALITY -y" -o "$LD_MJPG_STREAMER/output_http.so -p 8080 -w /usr/local/www"
		sleep 2
		f_message "mjpg_streamer started"
  fi

  return 0
}

stop() {
  pid=$(mjpg_streamer_pid)
  if [ -n "$pid" ]
  then
    f_message "Stopping mjpg_streamer..."
    kill -9 $pid

    let kwait=$SHUTDOWN_WAIT
    let count=0;
    until [ `ps -p $pid | grep -c $pid` = '0' ] || [ $count -gt $kwait ]
    do
      echo -n -e "\nwaiting for processes to exit";
      sleep 1
      let count=$count+1;
    done

    if [ $count -gt $kwait ]; then
      echo -n -e "\nkilling processes which didn't stop after $SHUTDOWN_WAIT seconds\n"
      kill -9 $pid
    fi
  else
    echo "mjpg_streamer is not running"
  fi
 
  return 0
}

# Carry out specific functions when asked to by the system
case "$1" in
        start)
                 start
                 ;;
        stop)
                 stop
                 ;;
        restart)
                 stop
                 sleep 2
                 start
                 ;;
        resolution)
                resolution=`ps axu | grep mjpg_streamer | grep -v grep | awk '{ print $16 }'`
                currfps=`ps axu | grep mjpg_streamer | grep -v grep | awk '{ print $18 }'`
                if [ -n "$resolution" ];
                then
                        echo "${resolution}"x"$currfps"
                else
                        echo "0x0x0"
                fi
                ;;
        status)
                pid=`ps -A | grep mjpg_streamer | grep -v "grep" | grep -v mjpg_streamer. | awk '{print $1}' | head -n 1`
                if [ -n "$pid" ];
                then
                        f_message "mjpg_streamer is running with pid ${pid}"
                        f_message "mjpg_streamer was started with the following command line"
                        cat /proc/${pid}/cmdline ; echo ""
                else
                        f_message "Could not find mjpg_streamer running"
                fi
                ;;
        *)
                f_message "Usage: $0 {start|stop|status|restart}"
                exit 1
                ;;
esac
exit 0

등록

$ sudo chmod u+x /etc/init.d/mjpg_streamer
$ sudo update-rc.d mjpg_streamer defaults

서비스 실행 및 실행 확인

$ sudo service mjpg_streamer start
$ sudo service mjpg_streamer status

● mjpg_streamer.service - LSB: mjpg_streamer for webcam
   Loaded: loaded (/etc/init.d/mjpg_streamer; bad; vendor preset: enabled)
   Active: active (running) since Sat 2017-03-11 23:04:13 CST; 15min ago
     Docs: man:systemd-sysv-generator(8)
  Process: 4201 ExecStart=/etc/init.d/mjpg_streamer start (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/mjpg_streamer.service
           └─4015 mjpg_streamer -b -i /usr/local/lib/input_ov5640.so -r 1280x720 -f 24 -q 45 -y -o /usr/local/lib/output_http.so -p 8080 -w /usr/local/www
 
Mar 11 23:04:11 bpi-iot-ros-ai mjpg_streamer[4201]: [+] Starting mjpg_streamer
Mar 11 23:04:11 bpi-iot-ros-ai mjpg_streamer[4201]: enabling daemon modeforked to background (4210)
Mar 11 23:04:11 bpi-iot-ros-ai mjpg_streamer[4210]: MJPG-streamer [4210]: MJPG Streamer Version: svn rev: 3:172M
Mar 11 23:04:11 bpi-iot-ros-ai mjpg_streamer[4210]: MJPG-streamer [4210]: Using V4L2 device.: /dev/video0
Mar 11 23:04:11 bpi-iot-ros-ai mjpg_streamer[4210]: MJPG-streamer [4210]: Desired Resolution: 640 x 480
Mar 11 23:04:11 bpi-iot-ros-ai mjpg_streamer[4210]: MJPG-streamer [4210]: Frames Per Second.: 10
Mar 11 23:04:11 bpi-iot-ros-ai mjpg_streamer[4210]: MJPG-streamer [4210]: Format............: YUV420
Mar 11 23:04:11 bpi-iot-ros-ai mjpg_streamer[4210]: MJPG-streamer [4210]: JPEG Quality......: 75
Mar 11 23:04:13 bpi-iot-ros-ai mjpg_streamer[4201]: [+] mjpg_streamer started
Mar 11 23:04:13 bpi-iot-ros-ai systemd[1]: Started LSB: mjpg_streamer for webcam.

실행

motion

출처 : mjpegtoyuv420p patch for Logitech Quickcam Pro

$ svn co http://www.lavrsen.dk/svn/motion/trunk motion
$ cd motion
$ ./configure
$ make
저작자 표시
신고

BPI-M2+ Camera(OV5640)

OS/Raspberry Pi 2017.02.26 04:27 Posted by 파란크리스마스

BPI-M2+ Camera(OV5640)

출처 : BPI-M2+ OV5640 camera linux driver is work fine
BPI-M2+ camera module

Camera 모듈 설치

$ sudo vi /etc/modules

내용 추가

ov5640
vfe_v4l2

Camera 설치 확인

$ dmesg | grep ov5640
[    8.330079] [VFE]Find sensor name is "ov5640", i2c address is 78, type is "YUV" !
[    8.330090] [VFE]Sub device register "ov5640" i2c_addr = 0x78 start!
[    8.535088] [VFE]Sub device register "ov5640" is OK!
$ ls /dev/video*
/dev/video0

Camera 정보

$ modinfo ov5640
filename:       /lib/modules/3.4.112-sun8i/kernel/drivers/media/video/sunxi-vfe/device/ov5640.ko
license:        GPL
description:    A low-level driver for ov5640 sensors
author:         raymonxiu
alias:          i2c:ov5640
depends:        cci,vfe_subdev
intree:         Y
vermagic:       3.4.112-sun8i SMP preempt mod_unload modversions ARMv7 p2v8 

Camera 캡쳐 프로그램 컴파일 및 실행

출처 : https://github.com/avafinger/ov5640

$ git clone https://github.com/avafinger/cap-v4l2
$ cd cap-v4l2
$ sudo ./install_deps.sh
$ ./build_script.sh
$ ./cap 1280 720 4 1 -999 -1 -1
저작자 표시
신고

SQL 학습

Database/SQL 2017.02.25 20:50 Posted by 파란크리스마스

Sample 데이터 다운로드

출처 : MySQL Sample Database - MySQL Tutorial

Sample 데이터 import

mysql -h localhost -P 5614 -u root -p < mysqlsampledatabase.sql

Select 문장

출처 : Using MySQL SELECT Statement to Query Data - MySQL Tutorial
SQL Syntax - TutorialsPoint

SELECT 컬럼명1, 컬럼명2
  FROM 테이블명

Select 예

select customerNumber, customerName
  from customers;

DISTINCT 예약어

출처 : SQL 자습서 - 1Keydata

SELECT 문장에 나열된 컬럼의 데이터가 중복행이 있으면 종복행을 제외하고 조회하는 예약어

SELECT DISTINCT 컬럼명1, 컬럼명2
  FROM 테이블명

Select 예

select DISTINCT city
  from customers;

-

-

저작자 표시
신고

Spring - Message Queue 구현

Programming/Java 2017.02.22 20:54 Posted by 파란크리스마스

Message Queue

출처 : 소프트웨어 개발자가 바라보는 세상 :: spring에서 ActiveMQ 접근 방법

메시지 객체

package com.iot.mq;

public class MQMessage {

	private String name;
	private String value;

	public void setName(String name) {
		this.name = name;
	}

	public String getName() {
		return name;
	}

	public void setValue(String value) {
		this.value = value;
	}

	public String getValue() {
		return value;
	}

	@Override
	public String toString() {
		return "MQMessage [name=" + name + ", value=" + value + "]";
	}
}

메시지 Sender

package com.iot.mq;

import org.springframework.jms.core.JmsTemplate;

public class MQMessageSender {

	private JmsTemplate jmsTemplate;

	public void setJmsTemplate(JmsTemplate jmsTemplate) {
		this.jmsTemplate = jmsTemplate;
	}

	public void send(final MQMessage msg) {
		jmsTemplate.convertAndSend(msg);
	}

}

메시지 Converter

package com.iot.mq;

import javax.jms.JMSException;
import javax.jms.MapMessage;
import javax.jms.Message;
import javax.jms.Session;

import org.springframework.jms.support.converter.MessageConversionException;
import org.springframework.jms.support.converter.MessageConverter;

public class MQMessageConverter implements MessageConverter {

	public MQMessageConverter() {

	}

	public Object fromMessage(Message message) throws JMSException, MessageConversionException {
		if (!(message instanceof MapMessage)) {
			throw new MessageConversionException("not MapMessage");
		}

		System.out.println("MsgConverterImpl.fromMessage");

		MapMessage mapMessage = (MapMessage) message;
		MQMessage msg = new MQMessage();
		msg.setName(mapMessage.getString("name"));
		msg.setValue(mapMessage.getString("value"));

		return msg;
	}

	public Message toMessage(Object object, Session session) throws JMSException, MessageConversionException {
		if (!(object instanceof MQMessage)) {
			throw new MessageConversionException("not Msg");
		}

		System.out.println("MsgConverterImpl.toMessage");

		MQMessage msg = (MQMessage) object;
		MapMessage mapMessage = session.createMapMessage();

		mapMessage.setString("name", msg.getName());
		mapMessage.setString("value", msg.getValue());

		return mapMessage;
	}
}

메시지 Driven POJO

package com.iot.mq;

public class MQMessageDrivenPOJO {
	
	public void handleMessage(MQMessage msg) {
		// handles the message
		System.out.println("handler called");
		System.out.println(msg);
	}
}

MQMessageController

package com.iot.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.iot.mq.MQMessage;
import com.iot.mq.MQMessageSender;

@Controller
public class MQMessageController {

	@Autowired
	private MQMessageSender mqSender;

	@ResponseBody
	@RequestMapping(value = "/mq.iot", method = RequestMethod.GET)
	public String login(
			@RequestParam("name") String name, 
			@RequestParam("value") String value, 
			ModelMap modelMap) throws Exception 
	{

		MQMessage msg = new MQMessage();
		msg.setName(name);
		msg.setValue(value);

		// 전송
		mqSender.send(msg);
		System.out.println(msg + " sent");

		return "MQ Send success";
	}

}

iot-mq.xml

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:amq="http://activemq.apache.org/schema/core" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="
	http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans-2.0.xsd 
	http://activemq.apache.org/schema/core 
	http://activemq.apache.org/schema/core/activemq-core.xsd">

	<!-- a pooling based JMS provider -->
	<bean id="connectionFactory" class="org.apache.activemq.ActiveMQConnectionFactory">
		<property name="brokerURL">
			<value>vm://localhost</value>
		</property>
	</bean>

	<bean id="jmsFactory" class="org.apache.activemq.pool.PooledConnectionFactory" destroy-method="stop">
		<property name="connectionFactory" ref="connectionFactory" />
	</bean>

	<bean id="queueDestination" class="org.apache.activemq.command.ActiveMQQueue">
		<constructor-arg value="com.iot.mq" />
	</bean>

	<bean id="msgConverter" class="com.iot.mq.MQMessageConverter" />
	<bean id="msgMdp" class="com.iot.mq.MQMessageDrivenPOJO" />

	<bean id="purePojoMdp" class="org.springframework.jms.listener.adapter.MessageListenerAdapter">
		<property name="delegate" ref="msgMdp" />
		<property name="defaultListenerMethod" value="handleMessage" />
		<property name="messageConverter" ref="msgConverter" />
	</bean>

	<bean class="org.springframework.jms.listener.SimpleMessageListenerContainer">
		<property name="connectionFactory" ref="jmsFactory" />
		<property name="destination" ref="queueDestination" />
		<property name="messageListener" ref="purePojoMdp" />
	</bean>

	<!-- Spring JMS Template -->
	<bean id="producerJmsTemplate" class="org.springframework.jms.core.JmsTemplate">
		<property name="connectionFactory" ref="jmsFactory" />
		<property name="defaultDestination" ref="queueDestination" />
		<property name="messageConverter" ref="msgConverter" />
	</bean>

	<bean id="mqSender" class="com.iot.mq.MQMessageSender">
		<property name="jmsTemplate" ref="producerJmsTemplate" />
	</bean>
</beans>

실행

실행결과

MsgConverterImpl.toMessage
MQMessage [name=a, value=a1] sent
MsgConverterImpl.fromMessage
handler called
MQMessage [name=a, value=a1]
저작자 표시
신고

Orange Pi PC 2

OS/Raspberry Pi 2017.02.17 22:28 Posted by 파란크리스마스

Orange pi 업그레이드

$ sudo apt-get update && apt-get upgrade
$ sudo shutdown -r now

SD 용량 확인(확장전)

$ df -l
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/mmcblk0p2   3536592 3448920         0 100% /
devtmpfs          492596       0    492596   0% /dev
tmpfs             501596       4    501592   1% /dev/shm
tmpfs             501596   13540    488056   3% /run
tmpfs               5120       4      5116   1% /run/lock
tmpfs             501596       0    501596   0% /sys/fs/cgroup
/dev/mmcblk0p1     51082   13062     38020  26% /boot
tmpfs             100320       4    100316   1% /run/user/0
tmpfs             100320       0    100320   0% /run/user/1002

파티션 늘리기

출처 : Orange PI resize SD-card root partition on Debian
Partitioning with fdisk

root@Orangepi:~# fdisk /dev/mmcblk0

Welcome to fdisk (util-linux 2.27.1).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.


Command (m for help): p
Disk /dev/mmcblk0: 14.9 GiB, 15931539456 bytes, 31116288 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x000ebed2

Device         Boot  Start      End  Sectors  Size Id Type
/dev/mmcblk0p1       40960   143359   102400   50M  c W95 FAT32 (LBA)
/dev/mmcblk0p2      143360 31116287 30972928 14.8G 83 Linux

Command (m for help): d
Partition number (1,2, default 2): 2

Partition 2 has been deleted.

Command (m for help): n
Partition type
   p   primary (1 primary, 0 extended, 3 free)
   e   extended (container for logical partitions)
Select (default p): p
Partition number (2-4, default 2): 2
First sector (2048-31116287, default 2048): 143360
Last sector, +sectors or +size{K,M,G,T,P} (143360-31116287, default 31116287): 

Created a new partition 2 of type 'Linux' and of size 14.8 GiB.

Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Re-reading the partition table failed.: Device or resource busy

The kernel still uses the old table. The new table will be used at the next reboot or after you run partprobe(8) or kpartx(8).

d - to delete partition
2 - second partition
n - create partition
p - primary partition type
2 - partition number
143360 - Same start as current 2th partition
enter - Default is last block
w - write partition table

리눅스 용량 확장

# resize2fs /dev/mmcblk0p2
resize2fs 1.42.13 (17-May-2015)
Filesystem at /dev/mmcblk0p2 is mounted on /; on-line resizing required
old_desc_blocks = 1, new_desc_blocks = 1
The filesystem on /dev/mmcblk0p2 is now 3871616 (4k) blocks long.

리눅스 용량 확인

# df -l
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/mmcblk0p2  15213236 3434452  11137696  24% /
devtmpfs          492596       0    492596   0% /dev
tmpfs             501596       0    501596   0% /dev/shm
tmpfs             501596    7092    494504   2% /run
tmpfs               5120       4      5116   1% /run/lock
tmpfs             501596       0    501596   0% /sys/fs/cgroup
/dev/mmcblk0p1     51082   13062     38020  26% /boot
tmpfs             100320       0    100320   0% /run/user/1002.

Using SSH

출처 : Login to the Orange Pi

사용자추가

$ sudo groupadd pi        
$ sudo useradd -g pi pi -m -s /bin/bash
$ sudo passwd pi   
Enter new UNIX password: [raspberry]
Retype new UNIX password: [raspberry] 
passwd: password updated successfully

sudo 권한 부여

# vi /etc/sudoers

내용 추가

pi      ALL=(ALL:ALL) ALL

Orange pi 정보 조회

# uname -a        
Linux Orangepi 3.10.65 #55 SMP PREEMPT Fri Nov 18 16:17:28 CST 2016 aarch64 aarch64 aarch64 GNU/Linux
# nmcli device
DEVICE   TYPE      STATE      CONNECTION 
eth0     ethernet  unmanaged  --         
gretap0  gretap    unmanaged  --         
ifb0     ifb       unmanaged  --         
ifb1     ifb       unmanaged  --         
gre0     iptunnel  unmanaged  --         
sit0     iptunnel  unmanaged  --         
tunl0    iptunnel  unmanaged  --         
lo       loopback  unmanaged  --         




이하 작성중...





USB Wifi

출처 : OrangePi PC 2上使用USB WIFI

$ git clone https://github.com/orangepi-xunlong/OrangePi_H5SDK.git
$ cd OrangePi_H5SDK
$ cp kernel/arch/arm64/configs/OrangePiH5_PC2_linux_defconfig kernel/.config

내용추가

# CONFIG_MAC80211 is not set
CONFIG_MAC80211=y
CONFIG_RTLWIFI=m
CONFIG_RTL8192CU=m

빌드

$ sudo ./build.sh

Orange pi 업그레이드

pi@Orangepi:~$ cd /usr/src/
pi@Orangepi:/usr/src$ sudo git clone https://github.com/jwrdegoede/rtl8189ES_linux/
[sudo] password for pi: 
Cloning into 'rtl8189ES_linux'...
remote: Counting objects: 818, done.
remote: Total 818 (delta 0), reused 0 (delta 0), pack-reused 818
Receiving objects: 100% (818/818), 2.75 MiB | 780.00 KiB/s, done.
Resolving deltas: 100% (437/437), done.
Checking connectivity... done.
pi@Orangepi:/usr/src$ cd rtl8189ES_linux
pi@Orangepi:/usr/src/rtl8189ES_linux$ git checkout 080f2aaf6bc8e08eeb3b51f0b8c377eae1ea7ed7
fatal: Unable to create '/usr/src/rtl8189ES_linux/.git/index.lock': Permission denied
pi@Orangepi:/usr/src/rtl8189ES_linux$

Orange pi - Wifi 드라이버 설치

출처 : How to properly compile device drivers for OrangePi?
Armbian missibg headers
pvaret/rtl8192cu-fixes
Orange PI PC Wireless Module (8192cu)
apt-get --reinstall install linux-headers-`uname -r` fails


$ cd /usr/src
$ sudo git clone https://github.com/pvaret/rtl8192cu-fixes
$ sudo make ARCH=armv7l CROSS_COMPILE= -C /lib/modules/3.10.65/build M=/usr/src/rtl8192cu-fixes modules
make: *** /lib/modules/3.10.65/build: No such file or directory.  Stop.


$ sudo apt-get update
$ sudo apt-get dist-upgrade
$ sudo apt-get install gcc build-essential
$ sudo apt-get install subversion
$ cd /usr/src
$ sudo svn checkout https://github.com/loboris/OrangePI-Kernel.git/trunk/linux-3.4
$ zcat /proc/config.gz > .config
$ make oldconfig





$ sudo apt-get install git linux-headers-generic build-essential dkms


Orange pi - Wifi 드라이버 설치

출처 : Install rtl8188etv/rtl8188eu driver on Orange-Pi
rebuild rtl8189es from GIT for OPI+
loboris/OrangePI-Kernel

$ sudo mkdir /usr/src/linux-kernel-$(uname -r)
$ cd /usr/src/linux-kernel-$(uname -r)
$ sudo git clone https://github.com/silentcreek/bananapi-kernel --depth 1


-

-

Armbian

출처 : Orange Pi – armbian

# apt-get update
# apt-get upgrade
# source /etc/armbian-release
# dpkg -r linux-u-boot-bananapi-${BRANCH} linux-$(lsb_release -cs)-root-${BRANCH}-bananapi
# apt-get -y install linux-u-boot-orangepi-${BRANCH} linux-$(lsb_release -cs)-root-${BRANCH}-orangepi
# ln -fs bin/orangepi.bin /boot/script.bin
# echo orangepi > /etc/hostname
# reboot

Kernel – armbian

출처 : Kernel – armbian

# echo "deb http://apt.armbian.com $(lsb_release -cs) main utils $(lsb_release -cs)-desktop" > /etc/apt/sources.list.d/armbian.list
# apt-key adv --keyserver keys.gnupg.net --recv-keys 0x93D6889F9F0E78D5
# apt-get update

Armbian build tools (igorpecovnik/lib)

출처 : Armbian build tools (igorpecovnik/lib)
Docs » Developer Guide » Building Armbian
Orange Pi PC - Armbian build

# apt-get -y install git
# git clone https://github.com/igorpecovnik/lib --depth 1
# cp lib/compile.sh .
# ./compile.sh


Orange Pi One Board Quick Start Guide with Armbian Debian based Linux Distribution


Orange Pi Camera

출처 : How to Use Orange Pi Camera in Linux (with Motion)

$ lsmod
Module                  Size  Used by
sunxi_ir_rx             8607  0
sunxi_keyboard          6962  0
ss                     34925  0
vfe_v4l2              779470  0
gc2035                 19147  0
vfe_io                 39108  2 vfe_v4l2,gc2035
videobuf2_dma_contig     9982  1 vfe_v4l2
videobuf2_memops        2691  1 videobuf2_dma_contig
videobuf2_core         31877  1 vfe_v4l2

$ dmesg
[   11.016560] [VFE]cci probe end cci_sel = 0!
[   11.022389] systemd[1]: Mounted Debug File System.
[   11.027676] [VFE]cci_init end
[   11.057297] systemd[1]: Started Remount Root and Kernel File Systems.
[   11.116467] systemd[1]: Started Create list of required static device nodes for the current kernel.
[   11.153733] systemd[1]: Started Nameserver information manager.
[   11.211515] systemd[1]: Reached target Network (Pre).
[   11.242083] systemd[1]: Starting Create Static Device Nodes in /dev...
[   11.283933] [VFE]Welcome to Video Front End driver
[   11.289701] [VFE]csi0 probe end!
[   11.293761] [VFE]csi_init end
[   11.297958] [ISP] isp platform_id = 3!
[   11.301463] systemd[1]: Starting udev Coldplug all Devices...
[   11.308437] [VFE]isp0 probe end!
[   11.312839] [VFE]sunxi_isp_platform_register end
[   11.322221] [VFE]mipi_init end
[   11.327704] [VFE]flash_init end
[   11.333422] [VFE]pdev->id = 0
[   11.336709] [VFE]dev->cci_sel = 0
[   11.343181] systemd[1]: Starting Load/Save Random Seed...
[   11.349636] [VFE]dev->csi_sel = 0
[   11.355996] [VFE]dev->mipi_sel = 0
[   11.360828] [VFE]dev->isp_sel = 0
[   11.364549] [VFE_WARN]fetch csi0_dev0_twi_id from device_tree failed
[   11.371634] [VFE_WARN]fetch csi0_dev0_iovdd from device_tree failed
[   11.378606] [VFE_WARN]fetch csi0_dev0_avdd from device_tree failed
[   11.379874] systemd[1]: Started Set console keymap.
[   11.391171] [VFE_WARN]fetch csi0_dev0_dvdd from device_tree failed
[   11.399505] [VFE_WARN]fetch csi0_dev0_afvdd from device_tree failed
[   11.409014] [VFE_WARN]fetch csi0_dev0_flash_en from device_tree failed
[   11.417687] systemd[1]: Started Load/Save Random Seed.
[   11.423755] [VFE_WARN]fetch csi0_dev0_flash_mode from device_tree failed
[   11.432635] [VFE_WARN]fetch csi0_dev0_flvdd from device_tree failed
[   11.442193] [VFE_WARN]fetch csi0_dev0_flvdd_vol from device_tree failed
[   11.449587] [VFE_WARN]fetch csi0_dev0_af_pwdn from device_tree failed
[   11.457381] [VFE]vfe_init end
[   11.465440] [VFE]probe_work_handle start!
[   11.469683] systemd[1]: Started Load Kernel Modules.
[   11.478053] [VFE]vfe_runtime_resume
[   11.494262] [VFE]..........................vfe clk open!.......................
[   11.502562] [VFE]v4l2 subdev register input_num = 0
[   11.508032] [VFE]vfe sensor detect start! input_num = 0
[   11.509449] systemd[1]: Started Create Static Device Nodes in /dev.
[   11.520743] [VFE]Find sensor name is "gc2035", i2c address is 78, type is "YUV" !
[   11.528982] [VFE]Sub device register "gc2035" i2c_addr = 0x78 start!
[   11.536002] [VFE]v4l2_device_register_subdev return 0
[   11.541547] [VFE]registered sensor subdev is OK!
[   11.548166] [VFE]Check sensor!
[   11.566077] [VFE]mclk on
[   11.575928] systemd[1]: Started Journal Service.
[   11.623105] [VFE CCI_0 ERR] Status error at addr_8bit = 78, wr_flag = 1, val = 3efa1cf0
[   11.632196] [VFE CCI_0 ERR] Status error at addr_8bit = 78, wr_flag = 1, val = 3efa00f0
[   11.642227] [VFE CCI_0 ERR] Status error at addr_8bit = 78, wr_flag = 1, val = 3efa00f0
[   11.653632] gc2035 sensor read retry=2
[   11.659085] [CSI_ERR][GC2035]sensor_read err at sensor_detect!
[   11.665513] [CSI_ERR][GC2035]chip found is not an target chip.
[   11.713421] [VFE]mclk off
[   11.740054] [VFE]vfe sensor subdev unregister!
[   11.745042] [VFE]Sub device register "gc2035" failed!
[   11.750592] [VFE_ERR]vfe sensor register check error at input_num = 0
[   11.757685] [VFE]V4L2 device registered as (null)
[   11.762915] [VFE]vfe_runtime_suspend
[   11.766841] [VFE]..........................vfe clk close!.......................
[   11.772987] [VFE]probe_work_handle end!


저작자 표시
신고

Arduino - 블루투스 모듈(HC-06)

OS/Arduino 2017.02.17 22:11 Posted by 파란크리스마스

블루투스 모듈(HC-06)

출처 : [아두이노] 블루투스 모듈(HC-06) 사용하기 (설정, 문자열 전송, LED ON/OFF) - 대네브

코드

#include <SoftwareSerial.h> //시리얼 통신 라이브러리 호출
 
int blueTx=2;   //Tx (보내는핀 설정)
int blueRx=3;   //Rx (받는핀 설정)
SoftwareSerial btSerial(blueTx, blueRx);  //시리얼 통신을 위한 객체선언
String myString=""; //받는 문자열
 
void setup() {
  Serial.begin(9600);   //시리얼모니터 
  btSerial.begin(9600); //블루투스 시리얼 개방
}
 
void loop() {
  while(btSerial.available())  //mySerial에 전송된 값이 있으면
  {
    char myChar = (char)btSerial.read();  //mySerial int 값을 char 형식으로 변환
    myString+=myChar;   //수신되는 문자를 myString에 모두 붙임 (1바이트씩 전송되는 것을 연결)
    delay(5);           //수신 문자열 끊김 방지
  }
  if(!myString.equals(""))  //myString 값이 있다면
  {
    Serial.println("input value: "+myString); //시리얼모니터에 myString값 출력
    myString="";  //myString 변수값 초기화
  }
}

앱 인벤터 - 블루투스 앱 만들기

출처 : 앱 인벤터 - 블루투스 앱 만들기

MIT App Inventor 2 사이트

화면구성

블럭

저작자 표시
신고

Arduino - 가스탐지(MQ-2)

OS/Arduino 2017.02.17 02:25 Posted by 파란크리스마스

가스탐지(MQ-2)

출처 : [아두이노] MQ-2 센서를 이용한 가스탐지 및 알람 - 대네브

사양

  • 출력값 0~1023
  • 작동전압 3.3v or 5v

코드

const int gasPin = A2; //가스센서 아웃을 아두이노 A2로 설정

void setup() {
  Serial.begin(9600); //시리얼포트 설정
}
 
void loop() {
  Serial.println(analogRead(gasPin)); //센서값을 시리얼모니터로 전송
  /*
  if (analogRead(gasPin) > 400) {   // 가스 검출 시(자신의 센서 감도에 알맞게 조절필요)
    tone(12,2000,1000); // 피에조 ON (주파수 2000으로 1초간 울리기)  
  }
  */ 
  delay(1000);
}
저작자 표시
신고

Arduino - 불꽃센서(SEN040129)

OS/Arduino 2017.02.17 02:14 Posted by 파란크리스마스

불꽃센서(SEN040129)

출처 : 아두이노 불꽃센서 모듈 / Arduino Flame Sensor - 메카솔루션

사양

감지 파장 760-1100nm
각도 60도
탐지거리 30-880mm
작동전압 3.3v or 5v

배선

OUT - A1
VCC - 5V
GND - GND

코드

void setup() {
  Serial.begin(9600);
}

void loop() {
  int val;
  val=analogRead(1);
  Serial.println(val,DEC);
  delay(100);
}


저작자 표시
신고

Arduino - 조도센서(SEN030101)

OS/Arduino 2017.02.17 01:44 Posted by 파란크리스마스

조도센서(SEN030101)

출처 : (SKU:SEN030101)亮度传感器 光敏电阻 光照传感器

배선

OUT - A0
VCC - 5V
GND - GND

코드

void setup() {
  Serial.begin(9600); // 打开串口,设置波特率为9600 bps
}

void loop() {
  int val;
  val=analogRead(0);   //传感器接于模拟口0
  Serial.println(val,DEC);//从串口发送数据并换行        
  delay(100);
}


저작자 표시
신고


 

티스토리 툴바