1、 在tomcat中配置数据源,配置路径是:E:\UCMSServer\tomcat\conf\server.xml,在如下位置添加:
数据源配置:
<Resource name="jdbc/website" type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:orcl" username="cmspro" password="cmspro" maxIdle="10" maxWait="10000" maxActive="350" removeAbandoned="true" removeAbandonedTimeout="180" logAbandoned="true" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" /> |
在tomcat\conf\Catalina\localhost\下编写wjdc.xml,内容如下:
<?xml version='1.0' encoding='utf-8'?> <Context displayName="wjdc" docBase="/wjdc" path="/wjdc" workDir="work/Catalina/localhost/wjdc"> <ResourceLink name="jdbc/website" global="jdbc/website" type="javax.sql.DataSource"/> </Context> |
如果想把项目不放置在tomcat中,需要做的操作是,可以类似:
<?xml version='1.0' encoding='utf-8'?> <Context displayName="wjdc" docBase="D:/UCMSServer/webapps/wjdc " path="/wjdc " workDir="work/Catalina/localhost/wjdc "> <ResourceLink name="jdbc/website" global="jdbc/website" type="javax.sql.DataSource"/> </Context> |
上面表示的是访问的工程是:D:/UCMSServer/webapps/wjdc,不是tomcat的webapps下的内容。
编写数据源类:
package com.ucap.survey.utils;
import java.sql.Connection;
import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource;
/** * JdbcUtils.java 数据源操作的工具类 * @attention * @author toto * @date 2017-3-30 * @note begin modify by 涂作权 2017-3-30 原始创建 */ public final class JdbcUtils { private static String dataSourceName = null;
/** * 获得数据库连接 */ public static Connection getConnection() { try { Context context = new InitialContext(); DataSource dataSource = (DataSource) context.lookup("java:comp/env/" + getDataSourceName()); return dataSource.getConnection(); } catch (Exception e) { e.printStackTrace(); } return null; }
public static String getDataSourceName() { if (dataSourceName == null) { dataSourceName = GetPropertyFromFileUtil.getProperty2("jdbc.properties", "dataSourceName").trim(); } return dataSourceName; } } |
数据库操作是(IOptionStatisticsDao),代码如下:
package com.ucap.survey.dao;
import java.util.List;
@SuppressWarnings("unchecked") public interface IOptionStatisticsDao {
/** * 通过题目id,获取选项信息列表 * @param opticId * @return * @attention方法的使用注意事项 * @author toto * @date 2017-3-24 * @note begin modify by 涂作权,邱鹏飞 2017-3-24 原始创建 */ public List findOptionsName(String opticId);
/** * 通过题目的id获取每道题目录的统计信息 * @param opticId :题目id * @return * @attention * * @author toto * @date 2017-3-24 * @note begin modify by 涂作权,邱鹏飞 2017-3-24 原始创建 */ public List findOptionVoteNum(String opticId);
/** * 获取当前题目的总的投票数量 * @param surveyId :问卷id * @return * @attention方法的使用注意事项 * @author toto * @date 2017-3-24 * @note begin modify by 涂作权,邱鹏飞 2017-3-24 原始创建 */ public Integer findVoteTotalNum(String surveyId);
} |
代码实现是(OptionStatisticsDaoImpl):
package com.ucap.survey.dao.impl;
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;
import com.ucap.survey.bean.OptionInfoBean; import com.ucap.survey.dao.IOptionStatisticsDao; import com.ucap.survey.exception.DaoException; import com.ucap.survey.utils.JdbcUtils;
/** * CommentDao.java 获得问卷的统计结果 * @attention * @author toto * @date 2017-3-24 * @note begin modify by 涂作权 2017-3-24 原始创建 */ public class OptionStatisticsDaoImpl implements IOptionStatisticsDao {
/** * 通过题目id,获取选项信息列表 * @param opticId * @return * @attention 方法的使用注意事项 * @author toto * @date 2017-3-24 * @note begin modify by 涂作权,邱鹏飞 2017-3-24 原始创建 */ public List<OptionInfoBean> findOptionsName(String opticId) { try { Connection conn = JdbcUtils.getConnection();
String sql = "select co.option_id optionId,co.option_content optionName " + "from CMS_OPTION co " + "where co.optic_id=? " + "and co.is_del = 0";
PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, opticId);
ResultSet rs = ps.executeQuery();
List<OptionInfoBean> list = new ArrayList<OptionInfoBean>(); while (rs.next()) { OptionInfoBean infoBean = new OptionInfoBean(); String optionId = rs.getString("optionId"); String optionName = rs.getString("optionName");
infoBean.setOptionId(optionId); infoBean.setOptionName(optionName); list.add(infoBean); }
rs.close(); ps.close(); conn.close();
return list; } catch (SQLException e) { throw new DaoException(e); } }
/** * 通过题目的id获取每道题目录的统计信息 * @param opticId :题目id * @return * @attention * * @author toto * @date 2017-3-24 * @note begin modify by 涂作权,邱鹏飞 2017-3-24 原始创建 */ public List<OptionInfoBean> findOptionVoteNum(String opticId) {
try { Connection conn = JdbcUtils.getConnection();
String sql = "select count(t.option_id) voteNum,t.option_id optionId from CMS_VOTERESULT t " + " where t.optic_id= ? " + " group by t.option_id";
PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, opticId);
ResultSet rs = ps.executeQuery();
List<OptionInfoBean> list = new ArrayList<OptionInfoBean>(); while (rs.next()) { OptionInfoBean infoBean = new OptionInfoBean(); String optionId = rs.getString("optionId"); Integer voteNum = rs.getInt("voteNum");
infoBean.setOptionId(optionId); infoBean.setVoteNum(voteNum); list.add(infoBean); }
rs.close(); ps.close(); conn.close();
return list; } catch (SQLException e) { throw new DaoException(e); } }
/** * 获取当前问卷投票次数 * @param surveyId :问卷id * @return * @attention 方法的使用注意事项 * @author toto * @date 2017-3-24 * @note begin modify by 涂作权,邱鹏飞 2017-3-24 原始创建 */ public Integer findVoteTotalNum(String surveyId) {
try { Connection conn = JdbcUtils.getConnection();
String sql = "select count(t.vote_survey_id) totalNum from CMS_VOTE_SURVEY t where t.survey_id= ?";
PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, surveyId);
ResultSet rs = ps.executeQuery();
Integer totalNum = 0; while (rs.next()) { totalNum = rs.getInt("totalNum"); break; }
rs.close(); ps.close(); conn.close();
return totalNum; } catch (SQLException e) { throw new DaoException(e); } } } |
jdbc.properties的内容如下:
##配置数据源名称,注意这里的内容和数据源配置的xml中配置的内容是一样的 dataSourceName=jdbc/website |
GetPropertyFromFileUtil的代码如下:
package com.ucap.survey.utils;
import java.io.IOException; import java.io.InputStream; import java.util.Enumeration; import java.util.LinkedHashMap; import java.util.Map; import java.util.Properties;
import com.ucap.survey.exception.GetPropertyFromFileException;
/** * @author <a href="mailto:1032610746@qq.com">涂作权</a> * * @version 1.2 2012-4-4 * @mobileshop2 */ @SuppressWarnings("unchecked") public final class GetPropertyFromFileUtil {
/** * <p>此方法用于获取指定文件中指定属性的整型值<p> */ public static int getProperty(String fileName,String property) { //返回DBType,并返回整型的数据 return Integer.parseInt(operate(fileName,property)); }
/** * <p>此方法用于获取指定文件中指定属性的String值<p> */ public static String getProperty2(String fileName,String property) { return operate(fileName,property); }
/** * @since version 1.2 * * @param fileName:表示要获得那个文件中的数据 * @param property:表示要获得的是那个文件的值 * @return String型的属性的值 */ public static String operate(String fileName,String property) { /* * 获得输入流 */ InputStream inputStream = GetPropertyFromFileUtil.class.getClassLoader().getResourceAsStream(fileName); Properties prop = new Properties();
try { prop.load(inputStream); } catch (Exception e) { throw new GetPropertyFromFileException(e); } finally { if (inputStream != null) { try { inputStream.close(); } catch (IOException e) { throw new GetPropertyFromFileException(e); } } inputStream = null; }
return prop.getProperty(property); }
/** * 获得属性文件中的所有参数的集合 */ @Deprecated public static Enumeration<Object> getProperties(String fileName) { /* * 获得输入流 */ InputStream inputStream = GetPropertyFromFileUtil.class.getClassLoader().getResourceAsStream(fileName); Properties prop = new Properties();
try { prop.load(inputStream); } catch (Exception e) { throw new GetPropertyFromFileException("GetPropertyUtilFromFileUtil prop.load步出错了!!"); } finally { if (inputStream != null) { try { inputStream.close(); } catch (IOException e) { throw new GetPropertyFromFileException("GetPropertyUtilFromFileUtil 关闭InputStream时出错了!"); } } inputStream = null; } return (Enumeration<Object>) prop.propertyNames(); }
/** * 通过文件名和实例对象获得所有的字段名称 * @param fileName * @param clazz * @return */ public static Map<String,String> getTableInfoFromFile(String fileName,Class clazz) { Map<String,String> tableFields = new LinkedHashMap<String,String>();
//获得表名称 String tableName = clazz.getSimpleName().toString(); Enumeration<Object> properties = getProperties(fileName); while (properties.hasMoreElements()) { //获得属性文件中的key值 String key = properties.nextElement().toString(); //如果key值是以表名称开头的,表示这些key对应的value全是这个表中的字段名称 if (key.startsWith(tableName) && !key.equals(tableName)) { String value = GetPropertyFromFileUtil.getProperty2(fileName, key); tableFields.put(key, value); } } return tableFields; }
public static String getTableName(String fileName,Class clazz) { //获得在配置文件中的表名名称对应的key String tableKey = clazz.getSimpleName().toString(); //获得tablememo.properties中的表名称对应key值之后,通过这个key值获得这个表名称 return getProperty2(fileName, tableKey); } } |