name : svn
http://community.polarion.com/projects/subversive/download/eclipse/6.0/update-site/
2017년 8월 31일 목요일
채팅프로그램 구현 mqtt이용
채팅 프로그램 구현( MQTT 이용)
1.몽고DB에 채팅내용 저장
2. MariaDB에서 방정보, 로그인 정보 가져와서 사용
----------MongoDB-Context 환경설정---------------------
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mongo="http://www.springframework.org/schema/data/mongo"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/data/mongo http://www.springframework.org/schema/data/mongo/spring-mongo-1.8.xsd">
<!-- user:pw@dbname -->
<mongo:mongo-client host="211.110.165.201" port="27017" credentials="ds603:ds603@ds20170412">
<mongo:client-options connections-per-host="8" threads-allowed-to-block-for-connection-multiplier="4"
connect-timeout="1000" max-wait-time="1500" socket-keep-alive="true"
socket-timeout="1500" />
</mongo:mongo-client>
<bean id="mongoTemplate" class="org.springframework.data.mongodb.core.MongoTemplate">
<constructor-arg ref="mongo" />
<constructor-arg name="databaseName" value="ds20170412" />
</bean>
</beans>
-----------------------MQTTController-----------------------
@Controller
public class MQTTController {
@Autowired
private MQTTDAO mDAO;
@RequestMapping(value = "/mqtt", method = RequestMethod.GET)
public String mqtt(Model model, @RequestParam("room")String room_name,
HttpSession httpSession) {
Map<String,String> map = new HashMap<String, String>();
map.put("a", (String)httpSession.getAttribute("_id"));
map.put("b", room_name);
mDAO.addRoomMember(map);
return "mqtt";
}
@RequestMapping(value = "/room", method = RequestMethod.GET)
public String room(Model model, HttpSession httpSession) {
httpSession.setAttribute("_id", "jdh");
List<MyRoom> list = mDAO.getRoomList();
model.addAttribute("list", list);
return "room";
}
}
--------------MangodbController--------------------
@RestController
public class MongodbController {
@Autowired
private MongoTemplate mongoTemplate;
@Autowired
private MQTTDAO mDAO;
@RequestMapping(value = "/get_room_member", method = { RequestMethod.GET,
RequestMethod.POST })
public @ResponseBody List<MyRoomMember> get_room_member(
@RequestParam("room_name")String room_name){
List<MyRoomMember> list = mDAO.getRoomMember(room_name);
return list;
}
@RequestMapping(value = "/insert_mongodb", method = { RequestMethod.GET,
RequestMethod.POST })
public @ResponseBody Map<String, Integer> insertMongodb1(
@RequestParam("topic") String t,
@RequestParam("payload") String p) {
Map<String, Integer> map = new HashMap<String, Integer>();
try {
MyMessage msg = new MyMessage();
msg.setTopic(t);
msg.setPayload(p);
msg.setTime(System.currentTimeMillis());
//class603/아이디
String[] tmp = t.split("/");
mongoTemplate.save(msg, tmp[0]); // vo, collection이름
map.put("ret", 1); // 성공
} catch (Exception e) {
map.put("ret", 0); // 실패
}
return map;
}
@RequestMapping(value = "/select_mongodb", method = { RequestMethod.GET,
RequestMethod.POST })
public @ResponseBody List<MyMessage> selectMongodb(
@RequestParam("last_time") double last_time,
@RequestParam("room") String room) {
// import org.springframework.data.mongodb.core.query.Query;
Query query = new Query();
query.limit(50);
query.with(new Sort(Sort.Direction.DESC, "time"));
query.addCriteria(Criteria.where("time").lt(last_time));
// SELECT * FROM mqtt_hss WHERE time < 마지막시간
// ORDER BY time DESC LIMIT 50
List<MyMessage> list
= mongoTemplate.find(query, MyMessage.class, room);
return list;
}
@RequestMapping(value = "/select_mongodb1", method = { RequestMethod.GET,
RequestMethod.POST })
public @ResponseBody List<MyMessage> selectMongodb1(
@RequestParam("time") double t,
@RequestParam("room") String room) {
Query query = new Query();
query.with(new Sort(Sort.Direction.ASC, "time"));
query.addCriteria(Criteria.where("time").gt(t));
List<MyMessage> list
= mongoTemplate.find(query, MyMessage.class, room);
return list;
}
}
---------------------MQTTDAO---------------------
@Service
public class MQTTDAO {
@Autowired
private SqlSession sqlSession;
public List<MyRoom> getRoomList(){
return sqlSession.selectList("MQTT.getRoomList");
}
public int addRoomMember(Map<String,String> map){
return sqlSession.insert("MQTT.addRoomMember",map);
}
public List<MyRoomMember> getRoomMember(String room_name){
return sqlSession.selectList("MQTT.getRoomMember",room_name);
}
public int delRoomMember(MyRoomMember mem) {
return sqlSession.delete("MQTT.delRoomMember", mem);
}
}
---------------------------mqtt.XML----------------------------
<?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="MQTT">
<select id="getRoomList" resultType="com.ds.vo.MyRoom">
SELECT * FROM chat_room;
</select>
<insert id="addRoomMember" parameterType="java.util.Map">
INSERT INTO chat_member VALUES(0,#{a},#{b});
</insert>
<select id="getRoomMember" parameterType="String" resultType="com.ds.vo.MyRoomMember">
SELECT DISTINCT(mem_id) FROM chat_member WHERE room_name=#{str};
</select>
<delete id="delRoomMember" parameterType="com.ds.vo.MyRoomMember">
DELETE FROM chat_member WHERE mem_id=#{mem_id} AND room_name=#{room_name};
</delete>
</mapper>
---------------mqtt.jsp---------------------
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>채팅</title>
<script src="resources/js/jquery.js"></script>
<script src="resources/js/mqttws31.min.js"></script>
<script>
function formatTime (unixTimestamp) {
var dt = new Date(unixTimestamp);
var curr_date = dt.getDate();
var curr_month = dt.getMonth();
var curr_year = dt.getFullYear();
var hours = dt.getHours();
var minutes = dt.getMinutes();
var seconds = dt.getSeconds();
if (hours < 10)
hours = '0' + hours;
if (minutes < 10)
minutes = '0' + minutes;
if (seconds < 10)
seconds = '0' + seconds;
return curr_year+"년"+curr_month+"월"+curr_date+'일 '+hours + ":" +
minutes + ":" + seconds;
}
var curr_time = 0;
var client = new Paho.MQTT.Client('211.110.165.201', 1884, 'jdh');
client.onConnectionLost = lost;
client.onMessageArrived = recv;
client.connect({onSuccess:conn});
function conn(){
client.subscribe('${param.room}'+'/#');
var t = '${param.room}' +'/'+'${sessionScope._id}';
message = new Paho.MQTT.Message('2');
message.destinationName=t;
client.send(message);
}
function lost(){
alert('서버가 중지되었습니다.');
}
function recv(msg){
if(msg.payloadString == '1'){
$.get('select_mongodb1?time='+curr_time+'&room='+'${param.room}',
function(data){
var len = data.length;
for(var i=0;i<len;i++){
if(i == 0){
curr_time = data[i].time;
}
$('#div_out').prepend(
formatTime(data[i].time)+
" - "+data[i].time+" ] " + data[i].topic + ","
+data[i].payload +"<br />"
);
}
});
}
else if(msg.payloadString == '2'){
$.get('get_room_member?room_name='+'${param.room}',function(data){
var len = data.length;
$('#div_member').empty();
for(var i=0; i<len; i++){
$('#div_member').append(data[i].mem_id+'<br/>')
}
},'json');
}
}
function send(topic){
message = new Paho.MQTT.Message('1');
message.destinationName=topic;
client.send(message);
}
$(document).ready(function(){
var last_time = 9999999999999;
$(window).bind('beforeunload', function(){
var url = 'del_chat_member?mem_id='+'${sessionScope._id}'
+'&room_name='+'${param.room}';
$.get(url, function(data){
if(data.ret == 1){
var t = '${param.room}' + '/' +
'${sessionScope._id}';
message = new Paho.MQTT.Message('2');
message.destinationName=t;
client.send(message);
}
},'json');
});
setInterval(function(){
$('#send_msg').click();
}, 1000*60);
//1. 함수정의
getMessage = function(chk) {
$.get("select_mongodb?last_time="+last_time+'&room='+
'${param.room}', function(data){
var len = data.length;
for(var i=0;i<len;i++){
if(i==0 && chk ==1) {
curr_time = data[i].time;
}
last_time = data[i].time;
$('#div_out').append(
formatTime(data[i].time)+
" - "+data[i].time+" ] " + data[i].topic + ","
+data[i].payload +"<br />"
);
}
});
};
//2.최초 1 회 함수호출
getMessage(1);
$(window).scroll(function(){
if($(window).scrollTop() +
window.innerHeight== $(document).height()){
//3.스크롤이 아래쪽으로 이동하면 함수호출
getMessage(2);
}
});
$('#send_msg').click(function(){
var url ="insert_mongodb";
var t = '${param.room}' + '/' + '${sessionScope._id}';
var m = $('#txt_msg').val();
$.post(url,{topic:t, payload:m}, function(data){
if(data.ret == 1){
send(t);
}
});
});
});
</script>
</head>
<body>
<input type="text" id="txt_msg" placeholder="내용을 입력하세요" />
<input type="button" value="보내기" id="send_msg" />
<hr />
<div id="div_out" style="float:left;width:80%"></div>
<div id="div_member" style="float:left;border:1px solid #cccccc"></div>
</body>
</html>
---------------MyRoomMember.vo----------------------
public class MyRoomMember {
private int no = 0;
private String mem_id = null;
private String room_name = null;
Getter/setter
----------------MyRoom.vo---------------------------
public class MyRoom {
private String topic = null;
private int max = 0;
private String admin = null;
--getter/setter
레이지루크 설치, 설정(sql문 콘솔창에 출력 시켜줌)
//sts01 프로젝트 참고
pom.xml
<dependency>
<groupId>org.lazyluke</groupId>
<artifactId>log4jdbc-remix</artifactId>
<version>0.2.7</version>
</dependency>
mybatis-context.xml
// 위에있는 데이터베이스 소스의 아이디를 ref로 정해주면 데이터베이스가 실행될때 인터셉터하여 먼저 레이지루크를 실행하게 된다
<bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
<bean id="dataSource" class="net.sf.log4jdbc.Log4jdbcProxyDataSource">
<constructor-arg ref="dataSource1" />
<property name="logFormatter">
<bean class="net.sf.log4jdbc.tools.Log4JdbcCustomFormatter">
<property name="loggingType" value="MULTI_LINE" />
<property name="sqlPrefix" value="SQL : "/>
</bean>
</property>
</bean>
//한 후에 sqlSessionFactory안 datasource 프로퍼티안 ref에 방금추가한 Id= dataSource를 맞춰준다.
//한 후에 sqlSessionFactory안 datasource 프로퍼티안 ref에 방금추가한 Id= dataSource를 맞춰준다.
src/main/resources
log4j.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<!-- Appenders -->
<appender name="console" class="org.apache.log4j.ConsoleAppender">
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %5p [%c] %m%n" />
</layout>
</appender>
<appender name="console-infolog" class="org.apache.log4j.ConsoleAppender">
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%d %5p %m%n" />
</layout>
</appender>
<!-- Application Loggers -->
<logger name="first" additivity="false">
<level value="debug" />
<appender-ref ref="console"/>
</logger>
<!-- Query Loggers -->
<logger name="jdbc.sqlonly" additivity="false">
<level value="INFO"/>
<appender-ref ref="console-infolog"/>
</logger>
<logger name="jdbc.resultsettable" additivity="false">
<level value="INFO"/>
<appender-ref ref="console"/>
</logger>
<!-- Root Logger -->
<root>
<priority value="off"/>
<appender-ref ref="console" />
</root>
</log4j:configuration>
2017년 8월 30일 수요일
마이바티스 설정
mybatis 환경설정 구조도
파일명 : oracle.properties
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521/xe
username=system
password=class603
파일명 : mariadb.properties
driver=org.mariadb.jdbc.Driver
url=jdbc:mysql://211.110.165.201:3306/ds20170412
username=ds20170412
password=class603
파일명 : mybatis-config.xml
<?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>
<settings>
<setting name="cacheEnabled" value="true" />
<!--<setting name="lazyLoadingEnabled" value="true" /> -->
<setting name="aggressiveLazyLoading" value="true" />
<setting name="multipleResultSetsEnabled" value="true" />
<setting name="useColumnLabel" value="true" />
<!-- <setting name="autoMappingBehavior" value="false" /> -->
<setting name="useGeneratedKeys" value="true" />
<setting name="defaultExecutorType" value="REUSE" />
<setting name="defaultStatementTimeout" value="5000" />
</settings>
</configuration>
파일명 : mybatis-context.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
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-3.0.xsd">
<bean id="propertyPlaceholderConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<value>/WEB-INF/mybatis/mariadb.properties</value>
</property>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${username}" />
<property name="password" value="${password}" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath:com/ds/mapper/*.xml" />
<property name="configLocation" value="/WEB-INF/mybatis/mybatis-config.xml" />
<property name="transactionFactory">
<bean
class="org.apache.ibatis.transaction.managed.ManagedTransactionFactory" />
</property>
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSessionFactory" />
</bean>
</beans>
-----------------------------------------------------------------------------------------
mySQL 웹서버 사이트
http://211.110.165.201:8080/webdb/
mybatis-context.xml
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="org.mariadb.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://211.110.165.201:3306/ds20170412_12" />
<property name="username" value="ds20170412_12" />
<property name="password" value="class603" />
</bean>
또는 mariadb.properties
driver=org.mariadb.jdbc.Driver
url=jdbc:mysql://211.110.165.201:3306/ds20170412
username=ds20170412
password=class603
-----------------------------------------------------------------------------------------
mySQL 웹서버 사이트
http://211.110.165.201:8080/webdb/
mybatis-context.xml
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="org.mariadb.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://211.110.165.201:3306/ds20170412_12" />
<property name="username" value="ds20170412_12" />
<property name="password" value="class603" />
</bean>
또는 mariadb.properties
driver=org.mariadb.jdbc.Driver
url=jdbc:mysql://211.110.165.201:3306/ds20170412
username=ds20170412
password=class603
데이터베이스 설정
데이터베이스 설정
라이브러리 추가
<dependencies>밑에
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<!-- //마리아 DB 라이브러리 -->
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>1.1.7</version>
</dependency>
<!-- 오라클 -->
<repositories>
<repository>
<id>codelds</id>
<url>https://code.lds.org/nexus/content/groups/main-repo</url>
</repository>
</repositories>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<<< 오픈 되어 있는 오라클 데이터베이스 서버>>>>
비밀번호 class603
피드 구독하기:
글 (Atom)