UPDATE table1 AS a
SET col1=(SELECT col1 FROM table2 AS b WHERE a.col2 = b.col2)


'DB > MySQL' 카테고리의 다른 글

datetime 컬럼을 분단위로 group by 하기  (1) 2020.09.29
5.7 DB 파일로 복원  (0) 2020.06.04
외부 접근 허용  (0) 2019.10.28
Create table select, Insert select  (0) 2011.06.17
스키마내 테이블 수  (0) 2011.06.17
Tiles를 이용하게 되면 주소를 반환해야 할  getRequestURI() 가 layout를 담당하는 jsp의 경로만 반환하게 된다.
아래를 이용하면 정확한 uri를 받을 수 있다. 

request.getAttribute("javax.servlet.forward.request_uri")



Create table select

테이블을 생성하여 결과값을 입력하는 구문이다.


원형>

CREATE TABLE 생성할 테이블명 SELECT * FROM 가져올 테이블명


예>

CREATE TABLE test8.zipcode SELECT * FROM test5.zipcode



Insert select

이미 테이블이 만들어져 있는 경우 결과값만 입력하는 구문이다.


원형>

INSERT INTO 입력할 테이블명 SELECT 필드1, 필드2, 필드3 FROM test_source


예>

INSERT INTO copyTable SELECT field1, field2, field3 FROM orgTable



'DB > MySQL' 카테고리의 다른 글

datetime 컬럼을 분단위로 group by 하기  (1) 2020.09.29
5.7 DB 파일로 복원  (0) 2020.06.04
외부 접근 허용  (0) 2019.10.28
SELECT UPDATE  (0) 2011.06.30
스키마내 테이블 수  (0) 2011.06.17

원형>

SELECT count(*)

FROM information_schema.tables

WHERE table_schema = '스키마이름'


예>

SELECT count(*) AS number_of_tables

FROM information_schema.tables

WHERE table_schema = 'test5'


'DB > MySQL' 카테고리의 다른 글

datetime 컬럼을 분단위로 group by 하기  (1) 2020.09.29
5.7 DB 파일로 복원  (0) 2020.06.04
외부 접근 허용  (0) 2019.10.28
SELECT UPDATE  (0) 2011.06.30
Create table select, Insert select  (0) 2011.06.17

Ibatis cook book


  1. insert

1.1 insert

Java class

public class Memo {

// DB 맵핑

SQLMapping mp = new SQLMapping();

// 입력할 메모 값이 담긴 프로퍼티

private Memo_VO memo;


public Boolean insertMemo() {

Integer rows = mp.insertMemo(memo);

if(rows == null) return false;

else return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public int insertMemo(Memo_VO memo) throws SQLException {

int rows = getSqlMap().update("insertMemo", memo);

return rows;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Memo" type="bean.Memo_VO"/>


<insert id="insertMemo" parameterClass="Memo">

INSERT INTO 1472s_memo ( userID, subject, content, wdate ) VALUE (#userID#, #subject#, #content#, now());

</insert>

</sqlMap>


1.2 insert - select last insert key

Java class

public class Memo {

// DB 맵핑

SQLMapping mp = new SQLMapping();

// 입력할 메모 값이 담겨있다.

private Memo_VO memo;


public Boolean insertMemo() {

Integer mmIdx = mp.insertMemo(memo); // 맵핑 호출 및 반환받음

if(mmIdx == null) return false;

else return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public int insertMemo(Memo_VO memo) throws SQLException {

int lastIdx = (Integer) getSqlMap().insert("insertMemo", memo);

return lastIdx;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Memo" type="bean.Memo_VO"/>


<insert id="insertMemo" parameterClass="Memo">

INSERT INTO 1472s_memo ( userID, subject, content, wdate ) VALUE (#userID#, #subject#, #content#, now());

<selectKey resultClass="int">

select LAST_INSERT_ID()

</selectKey>

</insert>

</sqlMap>



  1. Select

2.1 select - queryForObject

Java class

public class Memo {

// DB 맵핑

SQLMapping mp = new SQLMapping();


// 검색해올 메모의 관리번호

private int mmIdx;


// 검색해온 메모 값이 담긴 프로퍼티

private Memo_VO memo;


public Boolean selectMemo() {

memo = mp.selectMemo(mmIdx); // 불러오기

if(memo == null) return false;

else return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public Memo_VO selectMemo(int mmIdx) throws SQLException{

Memo_VO memo = (Memo_VO) sqlMap.queryForObject("selectMemo", mmIdx);

return memo;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Memo" type="bean.Memo_VO"/>


<select id="selectMemo" resultClass="Memo" parameterClass="int">

SELECT * FROM 1472s_memo WHERE mmIdx=#mmIdx#;

</select>

</sqlMap>


2.2 select - queryForList

Java class

public class Memo {

// DB 맵핑

SQLMapping mp = new SQLMapping();


// 검색해올 메모의 관리번호

private int mmIdx;


// 검색해온 메모 값이 담긴 프로퍼티

private ArrayList<Memo_VO> memoList;


public Boolean selectMemo() {

memoList = mp.selectMemoList(); // 불러오기

if(memoList == null) return false;

else return true;

}


// getter, setter

public void setMmIdx(int mmIdx) {

this.mmIdx = mmIdx;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public ArrayList<Memo_VO> selectMemoList(int mmIdx) throws SQLException{

ArrayList<Memo_VO> memoList = (ArrayList<Memo_VO>) sqlMap.queryForList("selectMemoList");

return memoList;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Memo" type="bean.Memo_VO"/>


<select id="selectMemoList" resultClass="Memo">

SELECT mmIdx, subject, substring(content, 1, 50) AS content, wdate FROM 1472s_memo;

</select>

</sqlMap>


2.3 select - queryForMap

Java class

public class Profile {

// DB 맵핑

SQLMapping mp = new SQLMapping();


public Boolean selectDiligenceDayList() throws SQLException{

Map<String, String> paramMap = new HashMap<String, String>();

paramMap.put("sDate", "2011-06-01");

paramMap.put("eDate", "2011-06-30");

HashMap<String, Member_DiligenceDay_VO> diligenceDayList = mp .selectDiligenceDayList(paramMap);

if(diligenceDayList == null) return false;

else return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public HashMap<String, Member_DiligenceDay_VO> selectDiligenceDayList(Map paramMap) throws SQLException{

HashMap<String, Member_DiligenceDay_VO> list = (HashMap<String, Member_DiligenceDay_VO>) sqlMap.queryForMap("selectDiligenceDayList", paramMap, "toDay");

return list;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Member_DiligenceDay" type="bean.Member_DiligenceDay_VO"/>


<select id="selectDiligenceDayList" resultClass="Member_DiligenceDay" parameterClass="java.util.Map">

SELECT * FROM 1472s_member_diligenceDay WHERE toDay BETWEEN #sDate# AND #eDate#

</select>

</sqlMap>


2.4 dynamic query

Java class

public class Profile {

// DB 맵핑

SQLMapping mp = new SQLMapping();


public Boolean selectDiligenceDayList() throws SQLException{

Map<String, String> paramMap = new HashMap<String, String>();

paramMap.put("sDate", "2011-06-01");

paramMap.put("eDate", "2011-06-30");

HashMap<String, Member_DiligenceDay_VO> diligenceDayList = mp .selectDiligenceDayList(paramMap);

if(diligenceDayList == null) return false;

else return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public HashMap<String, Member_DiligenceDay_VO> selectDiligenceDayList(Map paramMap) throws SQLException{

HashMap<String, Member_DiligenceDay_VO> list = (HashMap<String, Member_DiligenceDay_VO>) sqlMap.queryForMap("selectDiligenceDayList", paramMap, "toDay");

return list;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Member_DiligenceDay" type="bean.Member_DiligenceDay_VO"/>


<select id="selectDiligenceDayList" resultClass="Member_DiligenceDay" parameterClass="java.util.Map">

SELECT * FROM 1472s_member_diligenceDay WHERE toDay

<isNotNull property="eDate"> <!-- eDate라는 것이 Map 요소에 있다면 -->

BETWEEN #sDate# AND #eDate#

</isNotNull>

<isNull property="eDate"> <!-- eDate라는 것이 Map 요소에 없다면 -->

=#sDate#

</isNull>

</select>

</sqlMap>


2.5 paging

Java class

public class History {

// DB 매핑

private final Etc_SQLMapping mp = new Etc_SQLMapping();

// 목록

private ArrayList<History_VO> logList;


// 페이징처리

private final int recordCount=20; //한 화면에 보여질 레코드 갯수

private int currentPage; //현 페이지 넘버

private int totalCount;//전체 레코드 갯수

private int pageCount;//넘버링 되어질 페이지넘버

private int i;

private int CountNum;

private List<Integer> pageList; // jsp에서 출력된 페이지 번호 목록

private Boolean historyList() throws SQLException {

String eDate = this.eDate;

if(eDate != null) {

if(eDate.length() > 0 )

eDate = DateChange.date2tomorrow(eDate);

}


Map paramMap = new HashMap();

paramMap.put("hType", "인사");

paramMap.put("sDate", "2011-06-01");

paramMap.put("eDate", "2011-07-01");

paramMap.put("currentPage", getCurrentPage());



/***전체 레코드 갯수***/

totalCount = mp.selectLogListCount(paramMap);


/***totalCount나누기(페이지 넘버링 만들기)***/

pageCount = totalCount/recordCount;

/***만약 이라면 잔여물의 나머지가 있다면.. 올림을 해준다***/

if(totalCount%recordCount>0) pageCount++;


/***기준페이지번호 구하기***/

int start = (currentPage-1)*recordCount;


mp.selectLogList(paramMap, start, recordCount);

logList = log.getLogList();


CountNum=logList.size();

if(CountNum > 0){

pageList = new ArrayList<Integer>();

for(i=1; i<=pageCount; i++)

pageList.add(i);

}

return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public int selectLogHistoryCount(Map paramMap) throws SQLException {

int rows = (Integer) sqlMap.queryForObject("selectLogHistoryCount", paramMap);

return rows;

}


public ArrayList<History_VO> selectLogHistory(Map paramMap, int start, int recordCount) throws SQLException{

ArrayList<History_VO> list = (ArrayList<History_VO>) sqlMap.queryForList("selectLogHistory", paramMap, start, recordCount);

return list;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="logHistory">

<typeAlias alias="history" type="bean.History_VO"/>


<select id="selectLogHistoryCount" parameterClass="java.util.Map" resultClass="Integer">

SELECT count(no)

FROM 1472s_history

WHERE hType=#hType#

<isNotEmpty property="sDate" prepend="AND">

logDate BETWEEN #sDate# AND #eDate#

</isNotEmpty>

</select>


<select id="selectLogHistory" parameterClass="java.util.Map" resultClass="history">

SELECT no, logDate, hType, userID, userName, content, ip

FROM 1472s_history

WHERE hType=#hType#

<isNotEmpty property="sDate" prepend="AND">

logDate BETWEEN #sDate# AND #eDate#

</isNotEmpty>

ORDER BY no DESC

</select>

</sqlMap>



  1. update

Java class

public class Memo {

// DB 맵핑

SQLMapping mp = new SQLMapping();

// 수정할 메모

private Memo_VO memo;


public Boolean updateMemo() {

Integer rows = mp.updateMemo(memo);

if(rows == null) return false;

else return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public int updateMemo(Memo_VO memo) throws SQLException {

int rows = getSqlMap().update("updateMemo", memo);

return rows;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Memo" type="bean.Memo_VO"/>


<update id="updateMemo" parameterClass="Memo">

UPDATE 1472s_memo SET subject=#subject#, content=#content# WHERE mmIdx=#mmIdx#;

</update>

</sqlMap>



  1. delete

Java class

public class Memo {

// DB 맵핑

SQLMapping mp = new SQLMapping();


// 삭제할 메모의 관리번호

private int mmIdx;


public Boolean deleteMemo() {

Integer rows = mp.deleteMemo(mmIdx);

if(rows == null) return false;

else return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public int deleteMemo(int mmIdx) throws SQLException {

int rows = getSqlMap().delete("deleteMemo", mmIdx);

return rows;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Memo" type="bean.Memo_VO"/>


<delete id="deleteMemo" parameterClass="int">

DELETE FROM 1472s_memo WHERE mmIdx=#mmIdx#;

</delete>

</sqlMap>



  1. insert update

Java class

public class Memo {

// DB 맵핑

SQLMapping mp = new SQLMapping();

// 입력 또는 수정할 메모 값이 담긴 프로퍼티

private Memo_VO memo;


public Boolean insertUploadMemo() {

Integer rows = mp.insertUploadMemo(memo);

if(rows == null) return false;

else return true;

}

}

mapping

public class SQLMapping extends SQLManager {

SqlMapClient sqlMap = getSqlMap();

public int insertUploadMemo(Memo_VO memo) throws SQLException {

int rows = getSqlMap().update("insertUploadMemo", memo);

return rows;

}

}

Query xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sqlMap

PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"

"http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="Memo">

<typeAlias alias="Memo" type="bean.Memo_VO"/>


<statement id="insertUpdateMemo" parameterClass="Memo">

INSERT INTO 1472s_memo (wDate, userID, subject, content)

VALUES (#wDate#, #userID#, #subject#, #content#)

ON DUPLICATE KEY UPDATE

subject=#subject#, content=#content#

</statement>

</sqlMap>



  1. 기타

parameterClass에 ArrayList나 Map을 사용한 형태도 존재한다.

ArrayList는 []로 접근한다는 것 정도만 명시하겠다.


'Java' 카테고리의 다른 글

EL 함수 추가하기  (0) 2013.09.10
Apache POI 추가  (0) 2013.08.22
Spring 실행 쿼리문 콘솔 출력 - log4jdbc-remix (feat. 메이븐)  (0) 2013.08.08
Spring Interceptor 예외처리  (0) 2013.08.08
ojdbc14 추가  (0) 2013.06.24

+ Recent posts

티스토리툴바