mysql+配置文件使用方式

配置文件properties.properties

driver=com.mysql.jdbc.Driver

url=jdbc\:mysql\://localhost\:3306/test?useUnicode\=true&characterEncoding\=UTF-8&zeroDateTimeBehavior\=convertToNull
username=root
password=root

创建并连接mysql DBConnection.java

package util;

import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;

public class DBConnection {

	public static Connection getConn() {
		String driver = ConfigProperties.getValue("driver");
	    String url = ConfigProperties.getValue("url");
	    String username = ConfigProperties.getValue("username");
	    String password = ConfigProperties.getValue("password");
	    Connection conn = null;
	    try {
	        Class.forName(driver); 
	        conn = (Connection) DriverManager.getConnection(url, username, password);
	    } catch (ClassNotFoundException e) {
	        e.printStackTrace();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return conn;
	}
}

dao层使用方法

package util;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class UpdataResourceDB {

	/**
	 * 更新状态 cms_status
	 * @param string
	 */
	public void updateStatus(String sql) {
		// TODO Auto-generated method stub
		try {
			Connection conn = DBConnection.getConn();
			PreparedStatement Prepst = null;
			ResultSet rs = null;
			Prepst = (PreparedStatement) conn.prepareStatement(sql);
			Prepst.executeUpdate();
			Prepst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 通过关联查询给出resource表中的id 
	 * @return
	 */
	public String getResourceId(String id) {
		System.out.println("id------>"+id);
		String resourceId = null;
		Connection conn = DBConnection.getConn();
		PreparedStatement Prepst = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer(
				"SELECT a.id "
						+ "FROM ott_vod_resource a,ott_vod_content b, ott_vod_content_resource c "
						+ "WHERE b.id = c.content_id AND c.resource_id =a.id AND b.id =");
		sb.append(id + ";");
		try {
			Prepst = (PreparedStatement) conn.prepareStatement(sb.toString());
			rs = Prepst.executeQuery();
			 while(rs.next()){
				resourceId = rs.getString("id");
			}
			Prepst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return resourceId;
	}
	
	public List getResourceList() {
		String id = "";
		String title = "";
		String filename = "";
		List<Map> list = new ArrayList<Map>();
		Connection conn = DBConnection.getConn();
		PreparedStatement Prepst = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer("SELECT o.id,o.title,o.filename  FROM ott_vod_resource o ;");
		try {
			Prepst = (PreparedStatement) conn.prepareStatement(sb.toString());
			rs = Prepst.executeQuery();
			 while(rs.next()){
				 Map<String,Object> map = new HashMap<String,Object>();
				map.put("id", rs.getString("id")) ;
				map.put("title", rs.getString("title")) ;
				map.put("filename", rs.getString("filename")) ;
				list.add(map);
			}
			Prepst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 更新状态 cms_status
	 */
	public void updateOfferId(String resource_id, String movie_id) throws SQLException {
		Connection conn = DBConnection.getConn();
		PreparedStatement Prepst = null;
		ResultSet rs = null;
		StringBuffer sb = null;
		try{
			sb = new StringBuffer(" UPDATE ott_vod_resource SET offer_id ='");
			sb.append(movie_id+"' WHERE id = ");
			sb.append(resource_id+";");
			Prepst = (PreparedStatement) conn.prepareStatement(sb.toString());
			Prepst.executeUpdate();
			Prepst.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/**
	 * 更新状态OfferUriId_TermUriId
	 */
	public void updateOfferUriId_TermUriIdByOfferId(String offerId, String offerUriId, String termUriId, int code, String reason) throws SQLException {
		System.out.println("UpdataResourceDB.updateOfferUriId_TermUriIdByOfferId()>>>>>>>>>>>>>>>>>start>>>>>>>>>>>>>>>>>>>>");
		Connection conn = DBConnection.getConn();
		PreparedStatement Prepst = null;
		ResultSet rs = null;
		StringBuffer sb = null;
		try{
			if(code == 0){
				sb = new StringBuffer(" UPDATE ott_vod_resource SET path ='");
				sb.append(offerUriId+"', guid = '");
				sb.append(termUriId+"', adi_description = '");
				sb.append(reason+"', cms_status='已获取播放链接'");
				sb.append(", adi_time = NOW()");
				sb.append(", adi_status = 2");
				sb.append(" WHERE offer_id ='"+offerId+"';");
			}else{
				sb = new StringBuffer(
						"UPDATE ott_vod_resource SET cms_status = '注入失败', adi_time = NOW(), adi_status = 3 , adi_description='"+reason+"("+code+")"+"' WHERE offer_id='");
				sb.append(offerId+"';");
			}
			
			Prepst = (PreparedStatement) conn.prepareStatement(sb.toString());
			System.out.println("调用注入接口后山东有线返回的信息更新sql******************************:"+sb.toString());
			Prepst.executeUpdate();
			Prepst.close();
			conn.close();
		}catch(Exception e){
			System.out.println("调用注入接口后山东有线返回的信息更新失败");
			e.printStackTrace();
		}
		System.out.println("UpdataResourceDB.updateOfferUriId_TermUriIdByOfferId()<<<<<<<<<<<<<end<<<<<<<<<<<<<<<<<<");
	}
	
	/**
	 * 更新状态Package
	 */
	public void updatePackage(String offer_uri_id, String package_program_description) throws SQLException {
		Connection conn = DBConnection.getConn();
		PreparedStatement Prepst = null;
		ResultSet rs = null;
		StringBuffer sb = null;
		try{
			sb = new StringBuffer(" UPDATE ott_vod_resource SET package_program_description ='");
			sb.append(package_program_description+"' WHERE path = '");
			sb.append(offer_uri_id+"';");
			Prepst = (PreparedStatement) conn.prepareStatement(sb.toString());
			Prepst.executeUpdate();
			Prepst.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	/**
	 * 删除节目后更新状态
	 */
	public void updateOffer_delete(String resource_id, String adi_description, int deleteOffer) throws SQLException {
		Connection conn = DBConnection.getConn();
		PreparedStatement Prepst = null;
		ResultSet rs = null;
		StringBuffer sb = null;
		try{
			if(deleteOffer == 0){//删除节目成功
				sb = new StringBuffer(" UPDATE ott_vod_resource SET path ='', cms_status = '已删除', package_program_description ='', adi_description ='");
				sb.append(adi_description+"' WHERE id = ");
				sb.append(resource_id+";");
			}else{//删除节目失败
				sb = new StringBuffer(" UPDATE ott_vod_resource SET adi_description ='");
				sb.append(adi_description+"' WHERE id = ");
				sb.append(resource_id+";");
			}
			System.out.println("删除节目更新数据sql****************************"+sb.toString());
			Prepst = (PreparedStatement) conn.prepareStatement(sb.toString());
			Prepst.executeUpdate();
			Prepst.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	/**
	 * 通过关联查询给出series表电视剧父集中的id 20170220dalian
	 * @return
	 */
	public Map<String,String> getSeriesId(String title) {
		Map<String,String> map = new HashMap<String,String>();
		String seriesId = null;
		Connection conn = DBConnection.getConn();
		PreparedStatement Prepst = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer(
				"SELECT t1.id ,t1.guid ,t1.title ,t1.episode_total FROM ott_vod_series t1,ott_vod_content_series t2,ott_vod_content t3 "
						+ "WHERE  t1.`id` = t2.`series_id` AND t2.`content_id` = t3.`id` AND t3.`id`= "
						+"(SELECT t6.`id` FROM ott_vod_resource t4,ott_vod_content_resource t5,ott_vod_content t6 "
						+ "WHERE t4.`id` = t5.`resource_id` AND t5.`content_id` = t6.`id` AND t6.title='");
		sb.append(title + "');");
		System.out.println("芒果根据title获取剧集父id：************************"+sb.toString());
		try {
			Prepst = (PreparedStatement) conn.prepareStatement(sb.toString());
			rs = Prepst.executeQuery();
			 while(rs.next()){
				map.put("id",  rs.getString("id"));
				map.put("guid", rs.getString("guid"));
				map.put("series_title", rs.getString("title"));
				map.put("total", rs.getString("episode_total"));
			}
			Prepst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}
	/**
	 * 给出具体的集数
	 * @param string
	 * @return
	 */
	public  String getChapter(String content_id) {
		String chapter = null;
		try{
			StringBuffer sb = new StringBuffer("select number from ott_vod_content_series where content_id="+content_id+";");
			Connection conn = DBConnection.getConn();
			PreparedStatement Prepst = null;
			ResultSet rs = null;
			Prepst = (PreparedStatement) conn.prepareStatement(sb.toString());
			rs = Prepst.executeQuery();
			while(rs.next()){
				chapter=rs.getString("number");
				}
			Prepst.close();
		    conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		return chapter;
	}
	/**
	 * 获取综艺类型的集数序号
	 * @return
	 */
	public String getVerNum(String contentId,String serNum){
		String num = serNum;
		List<String> numList = new ArrayList<String>();
		
		try{
			//StringBuffer sb = new StringBuffer("select b.number from ott_vod_content ovc left join ott_vod_content_series ovcs on ovc.id = ovcs.content_id");
			String sql = "select b.number from ott_vod_content ovc left join ott_vod_content_series ovcs on ovc.id = ovcs.content_id " +
					"right join ott_vod_content_series b on ovcs.series_id = b.series_id" +
					" where ovc.id="+contentId+" order by b.number ";
			Connection conn = DBConnection.getConn();
			PreparedStatement Prepst = null;
			ResultSet rs = null;
			Prepst = (PreparedStatement) conn.prepareStatement(sql);
			rs = Prepst.executeQuery();
			while(rs.next()){
				numList.add(rs.getString(1));
				//chapter=rs.getString("number");
			}
			Prepst.close();
		    conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		
			for(int i=0;i<numList.size();i++){
				String rnum = numList.get(i);
				if(rnum.equals(serNum)){
					num = (i+1)+"";
					break;
				}
			}
		return num;
	}
}