安卓直连SQLSEVER数据库

当筵意气临九霄，星离雨散不终朝。这篇文章主要讲述安卓直连SQLSEVER数据库相关的知识，希望能为你提供帮助。
1、导入连接SQLSEVER的jar包:可以支持android的SQL驱动（如：jtds-1.2.7.jar）
【安卓直连SQLSEVER数据库】2、编写连接数据库的工具类

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static android.text.TextUtils.split;

//数据库得开启TCP/IP功能的1433端口
public class DBUtil {

//数据库
private static String IP = "192.168.1.666"; //类似的IP地址IP地址
private static String DBName = "XXX"; //数据库名
private static String USER = "XX";
private static String PWD = "XXX";

/**
* 创建数据库对象
*/
public static Connection getSQLConnection() {
Connection con = null;
try {
Class.forName("net.sourceforge.jtds.jdbc.Driver");
//加上 useunicode=true; characterEncoding=UTF-8 防止中文乱码
con = DriverManager.getConnection("jdbc:jtds:sqlserver://" + IP + ":1433/" + DBName + "; useunicode=true; characterEncoding=UTF-8", USER, PWD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}

/**
* 返回的List的[1]数据时List< Map>
* @param sql
* @return
* @throws
*/
//region 传入sql,返回转换成List(查询)
public static List Query(String sql) {
List result = new ArrayList();
ResultSet rs = null;
try {
Connection conn = getSQLConnection();

Statement stmt = conn.createStatement(); //
rs = stmt.executeQuery(sql);
result = convertList(rs);
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
String res = "查询数据异常" + e.getMessage();
e.printStackTrace();
result.add(res);
return result;
} catch (Exception e) {
String res = "无网络";
result.add(res);
return result;
}

return result;
}

//返回list,ResultSet转List< map>
public static List convertList(ResultSet rs) throws SQLException {
List all = new ArrayList();
List list = new ArrayList();
ResultSetMetaData md = rs.getMetaData(); //获取键名
int columnCount = md.getColumnCount(); //获取行的数量
String res = "ok";

all.add(res);
int coun = 0;
while (rs.next()) {
Map rowData = https://www.songbingjia.com/android/new HashMap(); //声明Map
for (int i = 1; i < = columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i)); //获取键名及值
}
list.add(rowData);
coun++;
}
if (coun < 1) {
all.set(0, "nodate");
}
all.add(list);

return all;
}
//endregion

/**
* 更新数据，新增，修改，删除
*/
//region 更新数据，新增，修改，删除返回int
public static int exesqlint(String sql){
int rs = 0;
try {

Connection conn = getSQLConnection();
Statement stmt = conn.createStatement(); //
rs = stmt.executeUpdate(sql);
stmt.close();
conn.close();
} catch (SQLException e) {
String res = "查询数据异常" + e.getMessage();
e.printStackTrace();
return 0;
} catch (Exception e) {
return 0;
}

return rs;
}
//endregion

//region 更新数据，新增，修改，删除返回LIST数据
public static List exesql(String sql) {
List result = new ArrayList();
int rs = 0;
try {
String ress = "";
Connection conn = getSQLConnection();

Statement stmt = conn.createStatement(); //
rs = stmt.executeUpdate(sql);
if (rs > 0) {
ress = "ok";
} else {
ress = "nodate";
}
result.add(ress);
stmt.close();
conn.close();
} catch (SQLException e) {
String res = "查询数据异常" + e.getMessage();
e.printStackTrace();
result.add(res);
return result;
} catch (Exception e) {
String res = "无网络";
result.add(res);
return result;
}
return result;
}

//endregion

/**
* 查询，有无该条数据
* @param sql
* @return
* @throws
*/
//region 查询，又多少条行数
public static int hasrows(String sql) {
int result = 0;

try {
Connection conn = getSQLConnection();

Statement stmt = conn.createStatement(); //
ResultSet ss =stmt.executeQuery(sql);
if (!ss.next()) {
result=0;
} else {
result=1;
}
ss.close();
stmt.close();
conn.close();
} catch (SQLException e) {
String res = "查询数据异常" + e.getMessage();
return -1;
} catch (Exception e) {
String res = "无网络";
return -1;

}
return result;
}
//endregion

//region 传入sql,返回转换成List(查询)
public static < T> List QueryT(String sql,T t) {
List result = new ArrayList();

ResultSet rs = null;
try {
Connection conn = getSQLConnection();

Statement stmt = conn.createStatement(); //
rs = stmt.executeQuery(sql);
result = util(t,rs);

rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
//String res = "查询数据异常" + e.getMessage();
//e.printStackTrace();
String res = "nodate";
result.add(res);
return result;
} catch (Exception e) {
String res = "无网络";
result.add(res);
return result;
}

return result;
}

/**
* ResultSet转List< T>
* @param t
* @param rs
* @return
* @throws
*/
public static < T> List util(T t, ResultSet rs) throws Exception {
// 创建一个对应的空的泛型集合
List< T> list = new ArrayList< T> ();
List ALL=new ArrayList();
// 反射出类类型（方便后续做操作）
Class c = t.getClass();
// 获得该类所有自己声明的字段，不问访问权限.所有。所有。所有
Field[] fs = c.getDeclaredFields();
int count=0;
// 大家熟悉的操作，不用多说
ALL.add("nodate");
int ros=rs.getRow();
if (rs != null) {
while (rs.next()) {
count++;
if(count==1){
ALL.set(0,"ok");
}
// 创建实例
t = (T) c.newInstance();
// 赋值
for (int i = 0; i < fs.length; i++) {
/*
* fs[i].getName()：获得字段名
*
* f:获得的字段信息
*/
Field f = t.getClass().getDeclaredField(fs[i].getName());
// 参数true 可跨越访问权限进行操作
f.setAccessible(true);
/*
* f.getType().getName()：获得字段类型的名字
*/
// 判断其类型进行赋值操作
if (f.getType().getName().equals(String.class.getName())) {
f.set(t, rs.getString(fs[i].getName()));
} else if (f.getType().getName().equals(int.class.getName())) {
f.set(t, rs.getInt(fs[i].getName()));
}
}

list.add(t);
}
}

ALL.add((list));
// 返回结果
return ALL;
}

//endregion
/**
* List< Map< String, Object> > 转List< T>
* @param list
* @param clazz
* @return
* @throws
*/

public static < T> List< T> castMapToBean(List< Map< String, Object> > list, Class< T> clazz) throws Exception {
if(list == null || list.size()==0) {
return null;
}
List< T> tList = new ArrayList< T> ();
// 获取类中声明的所有字段
Field[] fields = clazz.getDeclaredFields();

T t;
for(Map< String, Object> map : list) {
// 每次都先初始化一遍,然后再设置值
t = clazz.newInstance();
for(Field field : fields) {
// 把序列化的字段去除掉
if(!"serialVersionUID".equals(field.getName())){
// 由于Field都是私有属性，所有需要允许修改
field.setAccessible(true);

// 设置值, 类型要和vo对应好,不然会报类型转换错误
field.set(t, map.get(field.getName()));
}
}
tList.add(t);
}

return tList;
}

/**
* 返回的List的[1]数据时List< T>
* @param sql
* @return
* @throws
*/

//
/**
* 过滤非法字段
* @param str
* @return
* @throws
*/
public static boolean sql_inj(String str) {

String inj_str = "‘|and|exec|insert|select|delete|update|count|*|%|chr|mid|master|truncate|char|declare|; |or|-|+|,";

String inj_stra[] = split(inj_str, "|");

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

if (str.indexOf(inj_stra[i]) > = 0) {
return true;
}

}

return false;

}

public static Object mapToObject(Map< String, Object> map, Class< ?> beanClass) throws Exception {
if (map == null)
return null;
Object obj = beanClass.newInstance();

Field[] fields = obj.getClass().getDeclaredFields();
for (Field field : fields) {
int mod = field.getModifiers();
if(Modifier.isStatic(mod) || Modifier.isFinal(mod)){
continue;
}

field.setAccessible(true);
field.set(obj, map.get(field.getName()));
}
return obj;
}
}

3、根据上个工具类，构造专属自己专属工具类（。。。。。。）

public class SqlDto {

privateString sql;

public SqlDto() {
}

public SqlDto(String sql) {
this.sql = sql;
}

public String getSql() {
return sql;
}

public void setSql(String sql) {
this.sql = sql;
}

@Override
public String toString() {
return "SqlDto{" +
"sql=‘" + sql + ‘‘‘ +
‘}‘;
}
}

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbHelper {

//region 常规操作数据库
//批量执行sql语句，都执行成功，返回ok，存在失败语句，返回no
public static Stringlistsql(List< SqlDto> sql) {

String res = "";
int count = 0;
String tip = "";
for (int i = 0; i < sql.size(); i++) {

List ret = DBUtil.exesql(sql.get(i).getSql());
String dd = ret.get(0).toString();
String status = dd;

List< Production> p = new ArrayList< Production> ();

switch (dd) {
case "ok":
count++;
break;
case "nodate"://更新sql语句失败
break;
case "无网络"://脱机操作
//脱机操作
break;
default://其他异常
break;
}

}

if (count > 0) {

res = "ok";
} else {

res = "no";
}

returnres ;
}

//单独执行sql语句执行成功返回ok ，执行失败，返回no
publicstatic String ExcuteSql(String sql) {

String res = "";

int i = DBUtil.exesqlint(sql);

if (i > 0) {
//插入成功
res = "ok";

} else {
res = "no";
}

return res;

}

// 入参数sql + 需要查询的字段构造一个对象
//返回list< 查询对象> 集合
public static< T> List QueryData(String sql, T t) {

List list = DBUtil.QueryT(sql, t);
String dd = list.get(0).toString();
String status = dd;
String tip = "";
List< T> t2 = new ArrayList< T> ();
switch (dd) {
case "ok":
t2 = (List< T> ) list.get(1);
break;
case "nodate"://没有获取到数据
break;
case "无网络"://脱机操作
break;
default://其他异常

}
return t2;
}
//endregion

//region 调用数据库中的存储过程
/**
* 存储过程返回的是一个行数据，构造一个接收对象，有无该条数据
* @param tm,worker,inputer,T
* @return List< T>
* @throws
*/
public static List< 接收对象> 存储过程名字(传入的参数列表)
{

List< 接收对象> list = new ArrayList< > ();
try {

Connection conn =DBUtil.getSQLConnection();
String call = "{call SQLSEVER存储过程( 有几个参数，几个问号）}";
CallableStatement callStatement = conn.prepareCall(call);
callStatement.setString("参数一的名字", 参数一的值);
callStatement.setString("参数二的名字", 参数二的值);

ResultSet set = callStatement.executeQuery();
/*callStatement.get
ResultSet set=callStatement.getResultSet(); */

//List< Map>
List list1 = new ArrayList();
list1 = DBUtil.convertList(set);

Map< String,Object> map = new HashMap< > ();

if(list1.get(0).toString().equals("ok")){
ArrayList list2 = (ArrayList) list1.get(1);
for (int i = 0 ; i < list2.size() ; i ++)
{
map =(Map< String,Object> )list2.get(i);
对象实例 = new 对象();

实例.set参数名(map.get("参数名").toString())；
。。。。。。
list.add(p);
}
}

callStatement.close();
conn.close();

} catch (SQLException e) {
String res = "执行数据异常" + e.getMessage();
return list;
} catch (Exception e) {
e.printStackTrace();
}
return list;

}
//endregion

//获取远程服务器时间
public static Date getSystemTime(){
Map< String,Object> map = new HashMap< > ();
String sql = "select GETDATE() as datetime";
List list = DBUtil.Query(sql);
String time = "";
if(list.size() > 0){
map =(Map< String,Object> )list.get(0);
time = map.get("datetime").toString();
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = new Date();
try {
date = sdf.parse(time);
} catch (ParseException e) {
e.printStackTrace();
}

returndate;

}

//获取远程服务器小时
public static int getRemoteSystemHour(){

Hour hour = new Hour();
String sql = "SELECTDateName(hour,GetDate()) as hour";
List< Hour> list =DbHelper.QueryData(sql,hour);
String h = "";
if(list.size() > 0){
h = list.get(0).getHour();
}
int hour2 = Integer.parseInt(h);

return hour2;

}

}

4：实例调用
（1）批量调用（增、删、改SQL）

List< SqlDto> sql_list = new ArrayList< > ();
for (int i = 0; i < arr1.size(); i++) {
String sql = arr1.get(i).toString();
SqlDto sqlDto = new SqlDto();
sqlDto.setSql(sql);
sql_list.add(sqlDto);
}
其中数组arr1中存储的是sql语句的集合