使用jtds将android连接到MSSQL db。jdbc驱动程序到特定端口

Question

关于如何将android应用程序连接到Windows机器上的MSSQL数据库的分步说明。

正在努力找到使用jtds从android连接到MSSQL数据库的正确解决方案。最糟糕的是，我不清楚问题出在数据库还是JTD上。jdbc驱动程序。

public class DBTestActivity extends Activity {         

  private static final String url = "jdbc:jtds:sqlserver://10.0.2.2:1433;instance=testdb;DatabaseName=androidtest";
  private static final String user = "test\'testuser";
  private static final String pass = "testlog";

  TextView tv;

 @Override
 protected void onCreate(Bundle savedInstanceState) {

  super.onCreate(savedInstanceState);
  setContentView(R.layout.dbconnect);

  final Button button = (Button) findViewById(R.id.connect);
  button.setOnClickListener(new View.OnClickListener() {
      public void onClick(View v) {
            Log.d("test3", "text4");
          // Perform action on click
          Connect();
          tv = (TextView) findViewById(R.id.db_text);
      }
  });

 }

  private class Connect extends AsyncTask<String, Void, String> {
    @Override
    protected String doInBackground(String... urls) {
      String response = "";

      try {

          Log.d("test5", "text5");
          Class.forName("net.sourceforge.jtds.jdbc.Driver");
          Log.d("test77", "text77");

          Connection con = DriverManager.getConnection(url, user, pass);

          Log.d("test12", "text12");
          String result = "Database connection success\n";
          Statement st = con.createStatement();
          ResultSet rs = st.executeQuery("select * from dbo.Test_Manufacturer");
          ResultSetMetaData rsmd = rs.getMetaData();*/

          while(rs.next()) {
            result += rsmd.getColumnName(1) + ": " + rs.getInt(1) + "\n";
            result += rsmd.getColumnName(2) + ": " + rs.getString(2) + "\n";
            result += rsmd.getColumnName(3) + ": " + rs.getString(3) + "\n";
          }
          tv.setText(result);
      }
      catch(Exception e) {
          e.printStackTrace();
         // tv.setText(e.toString());
      }
    Log.d("test20", "text212");

    return response;   

    }

   @Override
    protected void onPostExecute(String result) {
        Log.d("test2", "text2");
        tv.setText(result);
    }
  }

  public void Connect() {

      Log.d("test", "text1");
    Connect task = new Connect();
      task.execute();

    }

      }

有不同类型的错误：

• SQLException：服务器10.0.2.2没有名为
• 经济复苏
• 找不到方法组织。IETF。JGSS。GSSCONTEXT。INITSECCONTEXT

Answer 1

在互联网上进行了无数次研究后，我想出了一步一步的过程。

  In order to set the environment with MSSQL server database and create connection to the
  specific port, you need:


    1)create DB in SQL server management studio(http://www.youtube.com/watch?v=18wZWdMSb9c)
    2)port number which listens(e.g 1433, 25, 80) (http://jackal777.wordpress.com/2011/10/24/open-rdesktop-port-3389-in-firewall-via-command-line/)
    http://www.windowsnetworking.com/articles-tutorials/windows-2003/Using-Netsh-Windows-Firewall.html
    http://www.gfi.com/blog/scan-open-ports-in-windows-a-quick-guide/
    3)Configure SQL server to specific port
    4)SQL server management studio user name and password
    5)Downloaded and installed jTDS JDBC Driver

    configure SQL server to listen to 1433, description here
    http://support.webecs.com/kb/a868/how-do-i-configure-sql-server-express-to-allow-remote.aspx


     1)Click Start, Programs, Microsoft SQL Server 2005/2008/2012 and select SQL Server Configuration Manager.
     2)SQL Server Network Configuration
     3)Protocols for 'whatever the name of your instance' (could be Express or your name)
     4) TCP/I
     5) IP Addresses tab
     6) Scroll to IPALL
     7) Set to specific port, in this case it's 1433

    When you've done setting port in SQL management studio, go and check which ports are listening, go to command prompt write:  netstat -a

            NOTE:You might need to activate the port through cmd.exe(look in the web)


    plus - check Computer Management if the service 'SQL Server Browser' status is 'Started'
    (right click on Computer icon - Manage - Services and Applications - Services)

    Additionally you can add exception rule in the Advanced Firewall Settings.

    Sample code could be found here:

    Thanks guys, you really helped me!

    http://appinventor.blogspot.de/2011/07/android-mysql.html

    http://blog.althafkbacker.com/2013/10/android-and-microsoft-sql-ms-sql-server.html


    3) Download  and installed jTDS JDBC Driver

        the latest version:
        http://sourceforge.net/projects/jtds/files/
        previous version:
        http://www.findjar.com/jar/net.sourceforge.jtds/jars/jtds-1.2.jar.html 

        I've chosen previous version, because it worked straight away.

        When unpacked, then go to eclipse, select your project - right click - properties - libraries - Add External JARs - then - order and export - tick it




    My reworked code(needs cleaning though):

    1)quick solution for separate network thread, is this piece of code:

    StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
  StrictMode.setThreadPolicy(policy);

    Place it into onCreate();

    Credit to: http://stackoverflow.com/questions/19266553/android-caused-by-android-os-networkonmainthreadexception

    Although the best way is to use AsyncTask method.


    2)connect failed: ECONNREFUSED

    use 10.0.2.2 for localhost for the emulator runs (this case 10.0.2.2 : 1433)

    NOTE: if you are running on the device then you need to use your IPv4 address : 1433

    Credit to: http://stackoverflow.com/questions/18341652/connect-failed-econnrefused


    In order to properly function either remove Intent in2 = getIntent();  piece of code(from DBTestActivity) or add MainActivity

        //catches intent request from MainActivity or secondaryActivity
        Intent in2 = getIntent();
        String tv2 = in2.getExtras().getString("key");

    What code does is connects to DB, retrieves requested fields and then updates them.

注：还有一个未包含的主要活动代码。下面是连接到DB的活动代码。

  public class DBTestActivity extends Activity {


ListView Lista;
SimpleAdapter AD;


 @Override
 protected void onCreate(Bundle savedInstanceState) {

  super.onCreate(savedInstanceState);
  setContentView(R.layout.dbconnect);
  //handles database requests on different thread
  StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
  StrictMode.setThreadPolicy(policy);

    Lista = (ListView) findViewById(R.id.list_output);

    //catches intent request from MainActivity or secondaryActivity
            Intent in2 = getIntent();
            String tv2 = in2.getExtras().getString("key");
            TextView theFact = (TextView) findViewById(R.id.db_barcode);
            String shareFact = tv2.toString();
            theFact.setText(shareFact);

  final Button button = (Button) findViewById(R.id.connect);
  button.setOnClickListener(new View.OnClickListener() {
      public void onClick(View v) {
            Log.d("test1", "1");
            //Perform action on click
            TextView deFact = (TextView) findViewById(R.id.db_barcode);
            String shareFact123 = deFact.getText().toString();
            //TextView deFact2 = (TextView) findViewById(R.id.db_text2);
           EditText deFact2 = (EditText) findViewById(R.id.db_task);
            //String shareFact2 = shareFact123.toString();
            deFact2.setText(shareFact123);
            //deFact2.setText(shareFact2);


            connect();  
      }
  });


  final Button button12 = (Button) findViewById(R.id.retrieve);
  button12.setOnClickListener(new View.OnClickListener() {
      public void onClick(View v) {
            Log.d("test2", "2");
            //Perform action on click
            //empty variables for storing the parameters of save()

            EditText deFact244 = (EditText) findViewById(R.id.db_task3);
            String shareFact1233 = deFact244.getText().toString();
            Log.d("retrtieve button", shareFact1233);
           // deFact244.setText(shareFact1233);

            String jjj1 = "";
            String jj2 = "";
            String j3 = "";
            String retrtieve = shareFact1233;
            save(jj2,jjj1,j3,retrtieve);
      }
  });


 }//onCreate(Bundle savedInstanceState)


      public void connect() {  
      try {  


          TextView theFactbarcode = (TextView) findViewById(R.id.db_barcode);
          String shareFactbarcode = theFactbarcode.getText().toString();
          String dbvariable = shareFactbarcode;
          Log.d("test3.0", dbvariable);
          Log.d("test3", "3");
          Class.forName("net.sourceforge.jtds.jdbc.Driver");  
          Log.d("test4", "4");
          Connection con = DriverManager.getConnection("jdbc:jtds:sqlserver://10.0.2.2:1433/androidtest","usernameMSSQL","passwordMSSQL");
          Log.d("test5", "5");
          Statement st = con.createStatement();  
          ResultSet rs = st.executeQuery("select * from dbo.nameAndroid where barcode='"+dbvariable+"'");  
          Log.d("test5.1", "5.1");
      while(rs.next()) {  

            Log.v("test6", rs.getString(2)); 
            String res =  rs.getString(2);
            TextView deFact22 = (TextView) findViewById(R.id.db_text2);
            String shaF = rs.getString(2);
            deFact22.setText(shaF);

            //was responsible for showing the name in edittext field
            //TextView deF = (TextView) findViewById(R.id.db_text2);
            //String shF = deF.getText().toString();
            //TextView deFact2 = (TextView) findViewById(R.id.db_text2);
          //  EditText deF2 = (EditText) findViewById(R.id.db_task);
            //String shareFact2 = shareFact123.toString();
          //  String defdef= deF2.getText().toString();
         //   deF2.setText(shF);

           // Log.v("test7", defdef );      
          //  String kk =defdef; 
            Log.v("test8", res ); 

            List<Map<String, String>> data = null;
            data = new ArrayList<Map<String,String>>();

            Map<String, String> datanum = new HashMap<String, String>();
            datanum.put("A", rs.getString("name"));
            datanum.put("B", rs.getString("id"));
            datanum.put("C", rs.getString("barcode"));
            data.add(datanum);

            //String name = datanum.put("A", rs.getString("name"));

            String barcod = datanum.put("C", rs.getString("barcode"));
            Log.v("test9", barcod);

            String jj = datanum.put("B", rs.getString("id"));
            int foo = Integer.parseInt(jj);

            for(int i = 0;i<jj.length();i++){

                Log.v("test10",jj);
                System.out.println(foo);

                TextView deF29 = (TextView) findViewById(R.id.db_text);
                String defdefT= jj.toString();
                deF29.setText(defdefT);

            }//end of for loop


                Log.v("test11", "11" ); 

             //   if(kk.toString()=="Android"){

            //        Log.v("DB111", "test" ); 

            String retr = null;
                    //    }             
             //   else{
                    save(res,jj,barcod,retr);//call function save
             //   } 


            /*String[] from = {"A","B","C"};
            int[] views = {R.id.db_text,R.id.db_text2,R.id.db_text3}; //Adapter
            AD = new SimpleAdapter(this, data, R.layout.dbconnect, from, views);
            Lista.setAdapter(AD);   */

      }//end of while loop  
     } 
      catch (Exception e){
         e.printStackTrace();
     }  
  }//end of connect()  

     public void save(String res,String jj,String barcod, String retrtievesave) {  

                /*TextView deFact22 = (TextView) findViewById(R.id.db_text);
                String shareFact1234 = deFact22.getText().toString();
                EditText deFact223 = (EditText) findViewById(R.id.db_task2);
                deFact223.setText(shareFact1234);
                String iddb = deFact223.toString();   */


                TextView deFact33 = (TextView) findViewById(R.id.db_text2);
                String shareFact1233 = deFact33.getText().toString();
                EditText deFact244 = (EditText) findViewById(R.id.db_task3);
                deFact244.setText(shareFact1233);

               Log.v("test12.0", retrtievesave ); 
          Log.v("test12", "save" );      
          Log.v("test13", res ); 
          Log.v("test13.1", jj );
          Log.v("test13.2", barcod );

          TextView deF299 = (TextView) findViewById(R.id.db_text);
          //String shareFact2 = shareFact123.toString();
          String defdefT9= deF299.getText().toString();
          Log.v("test14", defdefT9 );
          // deF299.setText(defdefT9);


          TextView nameDB = (TextView) findViewById(R.id.db_text2);
          //String shareFact2 = shareFact123.toString();
          String nameOneString= nameDB.getText().toString();
          Log.v("test14.1", nameOneString );



          TextView barcodeDB = (TextView) findViewById(R.id.db_barcode);
          //String shareFact2 = shareFact123.toString();
          String barcodeDBString= barcodeDB.getText().toString();
          Log.v("test14.2", barcodeDBString );


          String jjj = res;
          String jjj4 = jj;

          SharedPreferences prefs = getApplicationContext().getSharedPreferences("prefs", Context.MODE_PRIVATE);
          Editor prefsEditor = prefs .edit();
          prefsEditor.putString("myString", jjj4);
          prefsEditor.commit();
          //call the stored string
          String str= prefs.getString("myString", "");
          Log.v("test15", str ); 


          EditText edited = (EditText) findViewById(R.id.db_task);
          String defdef= edited.getText().toString();

          Log.v("test16", defdef ); 
          String dbvariable2 = defdef;
          Log.v("test17", dbvariable2 ); 
          Log.d("test18", "18");

          EditText deFact2445 = (EditText) findViewById(R.id.db_task3);
            String namedb = deFact2445.getText().toString();
              Log.v("test18.0",namedb ); 


          try{
              Class.forName("net.sourceforge.jtds.jdbc.Driver");  
              Log.d("test19", "19");
              Connection con2 = DriverManager.getConnection("jdbc:jtds:sqlserver://10.0.2.2:1433/androidtest","usernameMSSQL","passwordMSSQL");
              Log.d("test20", "20");
              Statement st2 = con2.createStatement();  
              int rs2 = st2.executeUpdate("UPDATE [androidtest].[dbo].[nameAndroid] set barcode='"+dbvariable2+"'"+", name='"+retrtievesave+"'" +" where id='"+defdefT9+"'");  
              String quer= "UPDATE [androidtest].[dbo].[nameAndroid] set barcode='"+dbvariable2+"'"+", name='"+retrtievesave+"'" +" where id='"+defdefT9+"'";
              System.out.println(quer);
              Log.d("test21", "21");


              EditText deFact24456 = (EditText) findViewById(R.id.db_task3);
              deFact24456.setText(retrtievesave);

            }
            catch (Exception e)
            {
                 e.printStackTrace();
            }

    }//end of save() function  

}