Android 使用jtds遠程訪問數據庫

最近老師讓我用jtds這個jar包遠程訪問數據庫中的數據，雖然不難，但有幾個點還是要注意一下的。

1、jtds的jar包我使用的是1.2.7這個版本的，如果是Java工程使用的是1.3.1這個版本，Android工程不能使用1.3.1否則連接不上數據庫

2、遠程數據庫服務器的防火墻要關閉

?

下面是如何使用jtds的案例

Javabean有兩個字段一個是id，一個是stuName

package com.ht.jdts;

public class StuDomain {
?? ?private String id;
?? ?private String stuName;
?? ?public String getId() {
?? ??? ?return id;
?? ?}
?? ?public void setId(String id) {
?? ??? ?this.id = id;
?? ?}
?? ?public String getStuName() {
?? ??? ?return stuName;
?? ?}
?? ?public void setStuName(String stuName) {
?? ??? ?this.stuName = stuName;
?? ?}
}

?

訪問sqlserver的工具類

package com.example.jdtsproc;

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

public class SqlHelper {
?? ?private String drive = "net.sourceforge.jtds.jdbc.Driver";
?? ?private String connStr;
?? ?private String server;
?? ?private String dbName;
?? ?private String userName;
?? ?private String userPwd;
?? ?private Connection con;
?? ?private PreparedStatement pstm;
?? ?private ResultSet rs;
?? ?private Statement stmt;

?? ?public SqlHelper(String server, String dbName, String userName,
?? ??? ??? ?String userPwd) {
?? ??? ?this.server = server;
?? ??? ?this.dbName = dbName;//數據庫名
?? ??? ?this.connStr = "jdbc:jtds:sqlserver://" + this.server + ":1433/"
?? ??? ??? ??? ?+ this.dbName;

?? ??? ?this.userName = userName;
?? ??? ?this.userPwd = userPwd;

?? ?
?? ?}

?? ?public List<StuDomain> ExecuteQuery(String sql) {

?? ??? ?List<StuDomain> stuLists = new ArrayList<StuDomain>();
?? ??? ?
?? ??? ?try {
?? ??? ??? ?Class.forName(drive);
?? ??? ??? ?System.out.println("驅動加載成功");
?? ??? ??? ?con = DriverManager.getConnection(this.connStr, this.userName,
?? ??? ??? ??? ??? ?this.userPwd);
?? ??? ??? ?System.out.println("數據庫連接成功");
?? ??? ??? ?//pstm = con.prepareStatement(sql);
?? ??? ??? ?
?? ??? ??? ?stmt = con.createStatement();
?? ??? ??? ?
?? ??? ??? ?rs = stmt.executeQuery(sql);
?? ??? ??? ?
?? ??? ??? ?while (rs.next()) {

?? ??? ??? ??? ?StuDomain stuDomain=new StuDomain();
?? ??? ??? ??? ?stuDomain.setId(rs.getString(1));
?? ??? ??? ??? ?stuDomain.setStuName(rs.getString(2));
?? ??? ??? ??? ?
?? ??? ??? ??? ?stuLists.add(stuDomain);

?? ??? ??? ?}
?? ??? ??? ?System.out.println("stuLists.size():"+stuLists.size());

?? ??? ?} catch (Exception e) {
?? ??? ??? ?System.out.println("數據庫連接異常");
?? ??? ??? ?e.printStackTrace();
?? ??? ??? ?return null;
?? ??? ?} finally {
?? ??? ??? ?try {
?? ??? ??? ??? ?if(rs!=null){
?? ??? ??? ??? ??? ?rs.close();?? ??? ??? ?
?? ??? ??? ??? ?}
?? ??? ??? ??? ?if(stmt!=null){
?? ??? ??? ??? ??? ?stmt.close();
?? ??? ??? ??? ?}
?? ??? ??? ??? ?if(con!=null){
?? ??? ??? ??? ??? ?con.close();
?? ??? ??? ??? ?}

?? ??? ??? ?} catch (SQLException e) {
?? ??? ??? ??? ?// TODO Auto-generated catch block
?? ??? ??? ??? ?e.printStackTrace();
?? ??? ??? ?}
?? ??? ?}
?? ??? ?return stuLists;
?? ?}

}

?

MainActivity 并將數據顯示在ListView上

package com.example.jdtsproc;

import java.util.List;

import android.support.v7.app.ActionBarActivity;
import android.os.Bundle;
import android.os.Handler;

import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.TextView;


public class MainActivity extends ActionBarActivity {

??? private Button btn_connect;
?? ?private ListView lv_lists;
?? ?
?? ?private List<StuDomain> stuLists;

?? ?private SqlHelper sh=new SqlHelper("xxx.xx.xxx.xx", "MyDb1", "sa", "sa");//第一個參數是訪問的遠程數據庫服務器的ip
?? ?private Handler mHandler=new Handler(){
?? ??? ?public void handleMessage(android.os.Message msg) {
?? ??? ??? ?lv_lists.setAdapter(new StuAdapter());
?? ??? ?};
?? ?};
?? ?@Override
??? protected void onCreate(Bundle savedInstanceState) {
??????? super.onCreate(savedInstanceState);
??????? setContentView(R.layout.activity_main);
?????? ?
??????? initView();
?????? ?
??????? initData();
?????? ?
??? }

?? ?private void initData() {
?? ??? ?btn_connect.setOnClickListener(new OnClickListener() {
?? ??? ??? ?
?? ??? ??? ?@Override
?? ??? ??? ?public void onClick(View v) {
?? ??? ??? ??? ?new Thread(){
?? ??? ??? ??? ??? ?
?? ??? ??? ?

?? ??? ??? ??? ??? ?public void run() {
?? ??? ??? ??? ??? ??? ?String sql="select *from stu";
?? ??? ??? ??? ??? ??? ?stuLists = sh.ExecuteQuery(sql);
?? ??? ??? ??? ??? ??? ?
?? ??? ??? ??? ??? ??? ?mHandler.sendEmptyMessage(0);//發送消息
?? ??? ??? ??? ??? ??? ?//lv_lists.setAdapter(new StuAdapter());
?? ??? ??? ??? ??? ?};
?? ??? ??? ??? ?}.start();
?? ??? ??? ?}
?? ??? ?});
?? ?}

?? ?private void initView() {
?? ??? ?//找到我們所需要的
?? ??? ?btn_connect = (Button) findViewById(R.id.btn_connect);
?? ??? ?lv_lists = (ListView) findViewById(R.id.lv_lists);
?? ?}

?? ?class StuAdapter extends BaseAdapter{

?? ??? ?@Override
?? ??? ?public int getCount() {

?? ??? ??? ?return stuLists.size();
?? ??? ?}

?? ??? ?@Override
?? ??? ?public StuDomain getItem(int position) {

?? ??? ??? ?return stuLists.get(position);
?? ??? ?}

?? ??? ?@Override
?? ??? ?public long getItemId(int position) {
?? ??? ??? ?
?? ??? ??? ?return position;
?? ??? ?}

?? ??? ?@Override
?? ??? ?public View getView(int position, View convertView, ViewGroup parent) {
?? ??? ??? ?StuViewHolder stuHolder;
?? ??? ??? ?if(convertView==null){
?? ??? ??? ??? ?convertView=View.inflate(getApplicationContext(), R.layout.list_main, null);
?? ??? ??? ??? ?stuHolder=new StuViewHolder();
?? ??? ??? ??? ?stuHolder.tv_id=(TextView) convertView.findViewById(R.id.tv_id);
?? ??? ??? ??? ?stuHolder.tv_stuname=(TextView) convertView.findViewById(R.id.tv_stuname);
?? ??? ??? ??? ?stuHolder.iv_photos=(ImageView) convertView.findViewById(R.id.iv_photos);
?? ??? ??? ?}else{
?? ??? ??? ??? ?stuHolder=(StuViewHolder) convertView.getTag();
?? ??? ??? ?}
?? ??? ??? ?stuHolder.tv_id.setText(getItem(position).getId());
?? ??? ??? ?stuHolder.tv_stuname.setText(getItem(position).getStuName());
?? ??? ??? ?stuHolder.iv_photos.setImageResource(R.drawable.photopager);
?? ??? ??? ?return convertView;
?? ??? ?}
?? ??? ?
?? ?}
?? ?
?? ?class StuViewHolder{
?? ??? ?public TextView tv_id;
?? ??? ?public TextView tv_stuname;
?? ??? ?public ImageView iv_photos;
?? ?}
?
}

?

最后奉獻jtds的兩個jar包??? 永久鏈接：https://pan.baidu.com/s/1YBW1I_ddSKe7vcIouDXKrg 密碼：0v95