package com.yaoyw.prompt.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {
	private static DBUtil single;

	private DBUtil() throws ClassNotFoundException, InstantiationException,
			IllegalAccessException {
		//用公司類的話，就不用這兩句了
		//Class.forName("com.mysql.jdbc.Driver");
		//Class.forName("com.mysql.jdbc.Driver").newInstance();
	}

	public static DBUtil getInstance() throws ClassNotFoundException,
			InstantiationException, IllegalAccessException {
		if (single == null)
			single = new DBUtil();
		return single;
	}
	
	/**
	 * 輸入提示框的提示項內容的獲得
	 * 
	 * @param search
	 * @return 提示項的內容，字符串數組
	 * @throws SQLException
	 */
	public String[] getPrompt(String search) throws SQLException{
		/*
		 // company
		 DbStatement dbs = getDbStatement();
		 String ls_Sql = "Select 路線代碼 " + " From L$許可占路表2" + " Where 路線代碼 like '" + search + "% and rownum < 10";
		 dbs.setSql(ls_Sql);
		 Rows rows = dbs.executeDbQuery().getRows();
		 String[] prompts = new String[rows.size()];
		 int i = 0;
		 for(Row row : rows){
		 	prompts[i++] = row.get("路線代碼");
		 }
		 */
		
		
		String url="jdbc:mysql://localhost:3306/prompt?user=root&password=123456";
		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();
		//String query = "select road_code from t1 where road_code like '" + search + "%'";
		String query = "select road_code from t1 where road_code like '" + search + "%'";
		ResultSet rs=stmt.executeQuery(query);
		List<String> prompts = new ArrayList<String>();
		while(rs.next()){
			prompts.add(rs.getString(1));
		}
		rs.close();
		stmt.close();
		con.close();
		return prompts.toArray(new String[prompts.size()]);
		
		//return new String[]{"cc", "ca", "cb"};
		
	}
	
	/**
	 * 返回路線的起點樁號和止點樁號
	 * 
	 * @param search
	 * @return
	 * @throws SQLException 
	 */
	public String[] getFirstAndLast(String search) throws SQLException {
		/*
		//
		DbStatement dbs = getDbStatement();
		String ls_Sql = "Select 起點樁號,止點樁號" + " From L$許可占路表2" + " Where 路線代碼 = ?";
		dbs.setSql(ls_Sql);
		dbs.setString(1, search);
		Row row = dbs.executeDbQuery().getRow();
		String[] result = new String[2];
		result[0] = row.get("起點樁號");
		result[1] = row.get("止點樁號");
		return result;
		*/
		
		
		String url="jdbc:mysql://localhost:3306/prompt?user=root&password=123456";
		Connection con = DriverManager.getConnection(url);
		Statement stmt = con.createStatement();
		String query = "select first,end from t1 where road_code = '" + search + "'";
		ResultSet rs=stmt.executeQuery(query);
		String[] result = new String[2];
		if(rs.next()){
			result[0] = rs.getString(1);
			result[1] = rs.getString(2);
		}else{
			result[0] = "";
			result[1] = "";
		}
		rs.close();
		stmt.close();
		con.close();
		return result;
		/*
		if("cc".equals(search))
			return new String[]{"2","300"};
		else if("ca".equals(search))
			return new String[]{"301","600"};
		else
			return new String[]{"601","900"};
		*/
	}
}