Android连接数据库sqlserver

 

Android studio 要先引入 jtds-1.2.7.jar 

 

帮助类

package com.example.util;

import android.util.Log;

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

public class SqlServerDBUtil {
    private static String driver = "net.sourceforge.jtds.jdbc.Driver";//sqlserver 驱动
    private static String url = "jdbc:jtds:sqlserver://118.124.8.100:3303;DatabaseName=databasename;useunicode=true;characterEncoding=UTF-8";//MYSQL数据库连接Url
    //;useunicode=true;characterEncoding=UTF-8
    private static String user = "root";//用户名
    private static String password = "root";//密码

    private static Connection getConn(){

        Connection connection = null;
        try{
            Class.forName(driver);// 动态加载类
            // 尝试建立到给定数据库URL的连接
            connection = DriverManager.getConnection(url ,user, password);
            Log.e("sqlDBUtils","加载驱动成功");
        }catch (Exception e){
            Log.e("sqlDBUtils","加载驱动失败");
            e.printStackTrace();
        }

        return connection;
    }

    public int logo(String sql){
        int num = 0;
        System.out.println(sql);
        Connection connection=getConn();
        try {
            if(connection!=null){
                Statement stmt = connection.createStatement();//
                if(stmt!=null){
                    // 执行sql查询语句并返回结果集
                    ResultSet result =stmt.executeQuery(sql);
                    if (!result.next()) {
                        num = 0;
                    } else {
                        num = 1;
                    }
                    connection.close();
                    stmt.close();
                    result.close();
                }else{
                    return num;
                }
            }else{
                return num;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        System.out.println(num);
        return num;
    }
    public static List select(String sql){
        HashMap<String, Object> map = new HashMap<>();
        List list=new ArrayList<>();
        // 根据数据库名称，建立连接
        Connection connection = getConn();
        try {
            // mysql简单的查询语句。这里是根据MD_CHARGER表的NAME字段来查询某条记录
            sql = "select * from clienter";
            if (connection != null){// connection不为null表示与数据库建立了连接
                Log.e("sqlDBUtils","成功");
                PreparedStatement ps = connection.prepareStatement(sql);
                if (ps != null){
                    // 设置上面的sql语句中的？的值为name
//                    ps.setString(1, name);
                    // 执行sql查询语句并返回结果集
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        Log.e("sqlDBUtils","列总数：" + count);
                        while (rs.next()){
                            System.out.println("sched_name:"+rs.getString("sched_name"));
                            System.out.println("lock_name:"+rs.getString("lock_name"));
                            list.add(rs.getString("sched_name")+"   " + rs.getString("lock_name"));
                            // 注意：下标是从1开始的
                            for (int i = 1;i <= count;i++){
                                String field = rs.getMetaData().getColumnName(i);
                                map.put(field, field);
                            }
                        }
                        connection.close();
                        ps.close();
                        return  list;
                    }else {
                        Log.e("sqlDBUtils","读取失败");
                        return null;
                    }
                }else {
                    Log.e("sqlDBUtils","查询失败");
                    return  null;
                }
            }else {
                Log.e("sqlDBUtils","连接失败");
                return  null;
            }
        }catch (Exception e){
            e.printStackTrace();
            Log.e("sqlDBUtils","异常：" + e.getMessage());
            return null;
        }
    }
}

 

 

因为Android连接数据需要重新开线程，不能在原线程中连接数据库

所以

 

在要连接的Activity中添加以下代码
//顶部消息
@SuppressLint("HandlerLeak")
    private Handler handler = new Handler(){
        @Override
        public void handleMessage(Message msg) {
            switch (msg.what){
                case 0x11:
                    //跳转页面
                    Intent intent = new Intent(LoginActivity.this, MainActivity.class);
                    startActivity(intent);
                    break;
                case 0x12:
                    Toast.makeText(LoginActivity.this, "账户密码错误，重新输入！", Toast.LENGTH_LONG).show();
                    break;
            }

        }
    };
// 创建一个线程来连接数据库并获取数据库中对应表的数据
  public void lianjie(){

      new Thread(new Runnable() {
        @Override
         public void run() {
           Message message = handler.obtainMessage();
           //调用连接数据库帮助类
           SqlServerDBUtil sqlServerDBUtil=new SqlServerDBUtil();
           //自定义查询语句sql，并接收返回值
           int logo = sqlServerDBUtil.logo("select * from operationer where bh="+username+" and password = "+userpwd);
            if(logo>0){
                  message.what = 0x11;//登录成功，表示有数据
               }else{
                  message.what = 0x12;
               }
                // 发消息通知主线程更新UI
                handler.sendMessage(message);
               }
        }).start();

    }