---
Android連結Azure database的方法
===

###### tags: `AZure` `JDBC` `JTDS` `SQL`

#### jtds.jar 連線(code by Rafael)
- jtds-1.3.1.jar 導入專案中
[下載jar檔](https://www.dropbox.com/s/86o0i16wqhuq7ki/jtds-1.3.1.jar?dl=0)
- driver 引入

    Class.forName("net.sourceforge.jtds.jdbc.Driver");

- 連線方式

    static String v_server="***.database.windows.net";
    static String v_user="***";
    static String v_password="***";
    
    public static Connection Open(String v_db)
    {
        StrictMode.ThreadPolicy policy=new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);  //此段為必要否則無法連線
        String v_url = sql_server + "databaseName="+v_db+";"+sql_user+sql_other;
        Connection con= null;
        try{
            try {
                Class.forName(v_javaname);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            con=java.sql.DriverManager.getConnection(v_url);
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

- 單行指令

 public static List<Map<String,Object>> Load(String v_db, String v_sql) throws SQLException {
        ResultSet v_RSet=null;
        Statement stmt = null;
        Connection conn= null;
        List<Map<String,Object>> resultList=new ArrayList<Map<String,Object>>();
        Map<String,Object> row=null;
        try{
            conn=Open(v_db);
            stmt = conn.createStatement();
            v_RSet = stmt.executeQuery(v_sql);
            ResultSetMetaData metaData= v_RSet.getMetaData();
            Integer columnCount=metaData.getColumnCount();
            while (v_RSet.next())
            {
                row=new HashMap<String,Object>();
                for(int i=1;i<=columnCount;i++)
                {
                    row.put(metaData.getColumnName(i),v_RSet.getObject(i));
                }
                resultList.add(row);
            }
            v_RSet.close();
        }catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try
            {
                if (v_RSet!=null && !v_RSet.isClosed()){
                    v_RSet.close();
                }
            }
            catch (SQLException ex2){
                ex2.printStackTrace();
            }
            try
            {
                if (stmt!=null && !stmt.isClosed()){
                    stmt.close();
                }
            }
            catch (SQLException ex2){
                ex2.printStackTrace();
            }
            try
            {
                if (conn!=null && !conn.isClosed()){
                    conn.close();
                }
            }
            catch (SQLException ex1){
                ex1.printStackTrace();
            }
        }
        return resultList;

- 呼叫方式

       public void onClick(View view) {
            String text = "";
            String dbName = "PID";
            String sqlString = "SELECT * FROM Device";
            try{
                List<Map<String, Object>> result = SQL.Load(dbName, sqlString);
                if(result.size() > 0){
                    for(Map<String, Object> map : result){
                        text += map.get("DeviceSerialNo") + "_" + map.get("DevTypeId") + "_" + map.get("DeviceFwVersion") + "_" + map.get("DeviceMacAddress") + "\n";
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            textView.setText(text);
        }

===
==以下是錯誤的不用看了==

#### JDBC (using Java) -- 失敗的嘗試/SSL問題
##### error1: no JDBC driver
copy mssql-jdbc-7.2.2.jre8.jar into /libs

public class MainActivity extends AppCompatActivity {

    private static final String hostName = "***.database.windows.net";
    private static final String database = "PID";
    private static final String userName = "***";
    private static final String passwd = "***";
    private String url = String.format("jdbc:sqlserver://%s:1433;database=%s;user=%s;password=%s;encrypt=true;"
                + "hostNameInCertificate=*.database.windows.net;loginTimeout=30;", hostName, database, userName, passwd );
    private String azureUrl = String.format("Data Source=tcp:%s,1433;Inital Catalog=%s;User ID=%s;Password=%s", hostName, database, userName, passwd);
    Connection connection = null;protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        new Thread(new Runnable() {
            @Override
            public void run() {
                try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

                    connection = DriverManager.getConnection(url);

                    String selectSql = "SELECT * FROM Device";
                    Statement statement = connection.createStatement();
                    ResultSet resultSet = statement.executeQuery(selectSql);
                    while(resultSet.next()){
                        System.out.println(resultSet.getString(1));
                    }
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }).start();

    }