廣告

2015年2月7日 星期六

[webservice][java] use spring lib to connect mysql x eclipse

上一篇 講到如何用struct2的 framework,
這一篇要講用spring 的lib 來連接mysql ，
會使用到資料庫基本的 新增刪改。

完整專案可於 github 下載，

專案結構如下:


專案所需lib














Install mysql if you don't have it.


看sql可以用打指令的方式或使用以下工具:


















配置web.xml(添加以下資訊)


  DB Connection
  jdbc/admin
  javax.sql.DataSource
  Container
 
 
  DB Connection
  jdbc/testdb
  javax.sql.DataSource
  Container


配置context.xml






index.jsp (view)
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
   pageEncoding="ISO-8859-1"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
   


Hello World


   

Hello World From Struts2



      

      
      
      
   



Hello World From Struts2 Extend



      

      
      
      
   



Hello World From Struts2 Map 



      

      
      
      
   



Insert people to testdb 



      

      id:
      name:
      school:
      age:
      
   



Update people to testdb 



      

      id:
      name:
      school:
      age:
      
   



Query people by name 



      

      
      
   



Query people by school 



      

      
      
   



Delete all people  



      

      
   



Delete people by school


ShowDbResult.jsp 
<%@ page contentType="text/html; charset=UTF-8" %>
<%@ taglib prefix="s" uri="/struts-tags" %>


Hello World


  

Select Records:

  
 

  
  id:
  
  name:
  
  school:
  
  age


配置struts.xml 


   
     
      
            /HelloWorld.jsp
      
      
       
            /HelloWorld.jsp
            /AccessDenied.jsp
         
        
            /HelloWorldMap.jsp
            /AccessDenied.jsp
         
          
            /ShowDbResult.jsp
            /AccessDenied.jsp
         
          
            /ShowDbResult.jsp
            /AccessDenied.jsp
         
          
            /ShowDbResult.jsp
            /AccessDenied.jsp
         
         
            /ShowDbResult.jsp
            /AccessDenied.jsp
         
          
            /ShowDbResult.jsp
            /AccessDenied.jsp
         
          
            /ShowDbResult.jsp
            /AccessDenied.jsp



新增vo(value object) class， PeopleVo.java
用來對應資料庫用 
package com.tutorial.vo;

import java.sql.Timestamp;

public class PeopleVo {
 private int id;
 //need to keep this arg,(spring lib need)
 //but you don't need to set it,
 //because this column is auto gen
 
 private String name;
 private String school;
 private int age;
 
 public int getId() {
  return id;
 }
 public void setId(int id) {
  this.id = id;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public String getSchool() {
  return school;
 }
 public void setSchool(String school) {
  this.school = school;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }

}


新增DAO(data access object) class，用來處理db connect
BaseDao.java and PeopleVo.java 
package com.tutorial.dao;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public abstract class BaseDao {
 public static final String DATA_SOURCE_ADMIN = "java:comp/env/jdbc/admin";
 public static final String DATA_SOURCE_TESTDB = "java:comp/env/jdbc/testdb";

 private static Map dsMap = new HashMap();
 private transient NamedParameterJdbcTemplate namedParameterJdbcTemplate = null;

 private NamedParameterJdbcTemplate getNamedParameterJdbcTemplate(
   DataSource datasource) {
  if (this.namedParameterJdbcTemplate == null) {
   this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
     datasource);
  }

  return this.namedParameterJdbcTemplate;
 }

 protected DataSource getDataSource(String dsName) {
  synchronized (dsMap) {
   Object obj = dsMap.get(dsName);

   if (obj == null) {
    try {
     final InitialContext initContext = new InitialContext();
     obj = initContext.lookup(dsName);
     dsMap.put(dsName, (javax.sql.DataSource) obj);
    } catch (NamingException e) {
     e.printStackTrace();
    }
   }

   return (javax.sql.DataSource) obj;
  }
 }

 /**
  * 查詢List
  * 
  * @param sql
  * @param bean
  * @param rowMapper
  * @return
  * @throws IOException
  * @throws DataAccessException
  */
 protected  List query(final String dsName, final String sql,
   final Object parameterbean, final RowMapper rowMapper) {
  try {
   return getNamedParameterJdbcTemplate(getDataSource(dsName)).query(
     sql, new BeanPropertySqlParameterSource(parameterbean),
     rowMapper);
  } catch (EmptyResultDataAccessException ex) {
   return null;
  }
 }

 /**
  * 更新資料
  * 
  * @param methodName
  * @param sql
  * @param parameterbean
  * @return
  */
 protected int update(final String dsName, final String sql,
   final Object parameterbean) {
  return getNamedParameterJdbcTemplate(getDataSource(dsName)).update(sql,
    new BeanPropertySqlParameterSource(parameterbean));
 }

 private NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
  if (this.namedParameterJdbcTemplate == null) {
   this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
     getDataSource(DATA_SOURCE_TESTDB));
  }

  return this.namedParameterJdbcTemplate;
 }

 protected int execute(final String sql, Map paramMap) {
  return getNamedParameterJdbcTemplate().update(sql, paramMap);
 }

 protected KeyHolder execute(final String sql,
   BeanPropertySqlParameterSource paramSource) {
  KeyHolder keyholder = new GeneratedKeyHolder();
  getNamedParameterJdbcTemplate().update(sql, paramSource, keyholder);
  return keyholder;
 }
}



package com.tutorial.impdao;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;

import com.tutorial.dao.BaseDao;
import com.tutorial.vo.PeopleVo;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class PeopleDao extends BaseDao {
 private static PeopleDao dao = new PeopleDao();

 public static PeopleDao getInstance() {
  return dao;
 }

 private PeopleDao() {
 }

 private static final String SQL_DELETE = "DELETE " + "FROM " + "testtable ";

 public void delete(final PeopleVo vo) {

  execute(SQL_DELETE, new BeanPropertySqlParameterSource(vo));
 }

 private static final String SQL_DELETE_BY_SCHOOL = "DELETE " + "FROM "
   + "testtable " + "WHERE " + "school = :school";

 public void deleteBySchool(final PeopleVo vo) {

  execute(SQL_DELETE_BY_SCHOOL, new BeanPropertySqlParameterSource(vo));
 }

 private static String SQL_Q_ALL = "SELECT * FROM " + "testtable";

 public List selectALL(final PeopleVo vo) {
  return this.query(DATA_SOURCE_TESTDB, SQL_Q_ALL.toString(), vo,
    BeanPropertyRowMapper.newInstance(PeopleVo.class));
 }

 private static String SQL_Q_Name = "SELECT * FROM " + "testtable"
   + " WHERE name = :name";

 public List selectByName(final PeopleVo vo) {
  return this.query(DATA_SOURCE_TESTDB, SQL_Q_Name.toString(), vo,
    BeanPropertyRowMapper.newInstance(PeopleVo.class));
 }

 private static String SQL_Q_SCHOOL = "SELECT * FROM " + "testtable"
   + " WHERE school = :school";

 public List selectBySchool(final PeopleVo vo) {
  return this.query(DATA_SOURCE_TESTDB, SQL_Q_SCHOOL.toString(), vo,
    BeanPropertyRowMapper.newInstance(PeopleVo.class));
 }

 private static final String SQL_INSERT = "INSERT INTO " + "testtable "
   + "VALUES(:id, :name, :school, :age) ";

 public void insert(final PeopleVo vo) {
  if (vo == null)
   throw new IllegalArgumentException("VO is null!!");

  execute(SQL_INSERT, new BeanPropertySqlParameterSource(vo));
 }

 private static final String SQL_UPDATE_BY_NAME = "UPDATE " + "testtable "
   + "SET school = :school, age = :age " + "WHERE name = :name ";

 public void update(final PeopleVo vo) {
  if (vo == null)
   throw new IllegalArgumentException("VO is null!!");
  System.out.print(vo);

  execute(SQL_UPDATE_BY_NAME, new BeanPropertySqlParameterSource(vo));
 }

}


對應struts的class & method ,HellowMySql.java 
package com.tutorialspoint.struts2;

import java.util.ArrayList;
import java.util.List;

import com.tutorial.impdao.PeopleDao;
import com.tutorial.vo.PeopleVo;

public class HellowMySql {
 public int id;
 public String name;
 public String school;
 public int age;
 public ArrayList daoL = new ArrayList();
 
 public String insert() throws Exception {
  PeopleDao dao = PeopleDao.getInstance();
  PeopleVo vo = new PeopleVo();
  //vo.setId(id);
  vo.setName(name);
  vo.setSchool(school);
  vo.setAge(age);
  System.out.println("insert to db: "+id+":"+name+":"+school+":"+age);
  dao.insert(vo);
  
  List daoDB = dao.selectALL(vo);
     for(int i = 0 ; i < daoDB.size() ; i++)
     {
      PeopleVo vod = new PeopleVo();
      vod.setId(daoDB.get(i).getId());  
      vod.setName(daoDB.get(i).getName()); 
      vod.setSchool(daoDB.get(i).getSchool()); 
      vod.setAge(daoDB.get(i).getAge()); 
      
      daoL.add(vod); 
      System.out.println("query from db:"+vod.getName());
     }
  return "success";
 }
 
 public String QueryByName() throws Exception {
  PeopleDao dao = PeopleDao.getInstance();
  PeopleVo vo = new PeopleVo();
  vo.setName(name);
  System.out.println("QueryByName :"+name);
  
  List daoDB = dao.selectByName(vo);
     for(int i = 0 ; i < daoDB.size() ; i++)
     {
      PeopleVo vod = new PeopleVo();
      vod.setId(daoDB.get(i).getId());  
      vod.setName(daoDB.get(i).getName()); 
      vod.setSchool(daoDB.get(i).getSchool()); 
      vod.setAge(daoDB.get(i).getAge()); 
      
      daoL.add(vod); 
      System.out.println("query from db:"+vod.getName());
     }
  return "success";
 }
 
 
 public String QueryBySchool() throws Exception {
  PeopleDao dao = PeopleDao.getInstance();
  PeopleVo vo = new PeopleVo();
  vo.setSchool(school);
  System.out.println("QueryBySchool: "+school);
  
  List daoDB = dao.selectBySchool(vo);
     for(int i = 0 ; i < daoDB.size() ; i++)
     {
      PeopleVo vod = new PeopleVo();
      vod.setId(daoDB.get(i).getId());  
      vod.setName(daoDB.get(i).getName()); 
      vod.setSchool(daoDB.get(i).getSchool()); 
      vod.setAge(daoDB.get(i).getAge()); 
      
      daoL.add(vod); 
      System.out.println("query from db:"+vod.getName());
     }
  return "success";
 }
 
 public String UpdateByName() throws Exception {
  PeopleDao dao = PeopleDao.getInstance();
  PeopleVo vo = new PeopleVo();
  vo.setName(name);
  vo.setSchool(school);
  vo.setAge(age);
  System.out.println("UpdateByName: "+name);
  
  System.out.println("before UpdateByName: "+name);
  
  List daoDB = dao.selectByName(vo);
     for(int i = 0 ; i < daoDB.size() ; i++)
     {
      PeopleVo vod = new PeopleVo();
      vod.setId(daoDB.get(i).getId());  
      vod.setName(daoDB.get(i).getName()); 
      vod.setSchool(daoDB.get(i).getSchool()); 
      vod.setAge(daoDB.get(i).getAge()); 
      
      daoL.add(vod); 
     }
  
  dao.update(vo);
  
  List daoDBa = dao.selectByName(vo);
     for(int i = 0 ; i < daoDB.size() ; i++)
     {
      PeopleVo vod = new PeopleVo();
      vod.setId(daoDBa.get(i).getId());  
      vod.setName(daoDBa.get(i).getName()); 
      vod.setSchool(daoDBa.get(i).getSchool()); 
      vod.setAge(daoDBa.get(i).getAge()); 
      
      daoL.add(vod); 
     }
  return "success";
 }
 
 public String DeleteAll() throws Exception {
  PeopleDao dao = PeopleDao.getInstance();
  PeopleVo vo = new PeopleVo();
  System.out.println("DeleteAll");
  
  dao.delete(vo);
  
  List daoDB = dao.selectALL(vo);
     for(int i = 0 ; i < daoDB.size() ; i++)
     {
      PeopleVo vod = new PeopleVo();
      vod.setId(daoDB.get(i).getId());  
      vod.setName(daoDB.get(i).getName()); 
      vod.setSchool(daoDB.get(i).getSchool()); 
      vod.setAge(daoDB.get(i).getAge()); 
      
      daoL.add(vod); 
      System.out.println("query from db:"+vod.getName());
     }
  return "success";
 }
 
 public String DeleteBySchool() throws Exception {
  PeopleDao dao = PeopleDao.getInstance();
  PeopleVo vo = new PeopleVo();
  System.out.println("DeleteBySchool :"+ school);
  vo.setSchool(school);
  dao.deleteBySchool(vo);
  
  List daoDB = dao.selectALL(vo);
     for(int i = 0 ; i < daoDB.size() ; i++)
     {
      PeopleVo vod = new PeopleVo();
      vod.setId(daoDB.get(i).getId());  
      vod.setName(daoDB.get(i).getName()); 
      vod.setSchool(daoDB.get(i).getSchool()); 
      vod.setAge(daoDB.get(i).getAge()); 
      
      daoL.add(vod); 
      System.out.println("query from db:"+vod.getName());
     }
  return "success";
 }
 
 
 public ArrayList getDaoList() {  
     return daoL;  
 }  
 public void setDaoList(ArrayList daoL) {  
     this.daoL = daoL;  
 } 

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }
 public String getName() {
  System.out.println("call getName");
  return name;
 }

 public void setName(String name) {
  System.out.println("call setName");
  this.name = name;
 }
 public String getSchool() {
  return school;
 }

 public void setSchool(String school) {
  this.school = school;
 }

 public int getAge() {
  return age;
 }

 public void setAge(int age) {
  this.age = age;
 }


}


主畫面:















結果畫面:


於
標籤： ,

1 則留言:

  1. 匿名2015年2月11日 晚上8:15

    db 如果 配置不了
    http://my.oschina.net/artshell/blog/199669

    回覆刪除

訂閱： 張貼留言 (Atom)