前言:在做具体操作时先需要把 jtds-1.2.jar 包放到libs目录当中(1.2是版本号,可用更新的版本)。
注意:
- 连接数据库需在子线程操作,不能在主线程;
- 连接目标数据库,需要和目标数据库安装的电脑在同一局域网;
- 如果已经在同一局域网还是连不上数据库,可检查一下数据库所在电脑的防火墙;
- 如果还是连不上,可检查“服务器属性”中的连接,“允许远程连接到此服务器”是否勾上;
- 服务器身份验证使用“SQL Server和Windows身份验证模式(S)”
下面栗子的表名为LoginUser,有UserName\UserPwd\OrderNumber 三列。
UserName | UserPwd | OrderNumber |
admin | 123 | 0 |
amberoot | 12345 | 1 |
haha | 123456 | 2 |
public class sql_test implements Runnable {
private String Tag = "sql_test";
@Override
public void run() {
String userName = "sa";//用户名
String password = "123456";//密码
Connection con = null;
try {
// 使用Class加载驱动程序
Class.forName("net.sourceforge.jtds.jdbc.Driver");
//连接数据库,库名为test
con = DriverManager.getConnection(
"jdbc:jtds:sqlserver://192.168.66.66:1433/test", userName,
password);
} catch (ClassNotFoundException e) {
System.out.println("加载驱动程序出错");
} catch (Exception e) {
System.out.println(e.getMessage());
}
try {
updateDataToSQL(con);
readSqlData(con);
} catch (java.sql.SQLException e) {
e.printStackTrace();
}
}
private void readSqlData(Connection con) throws java.sql.SQLException {
try {
/*SQL指令对大小不敏感*/
//查询表名为“LoginUser”的所有内容
String sql = "SELECT * FROM LoginUser";
//查询列UserName内容
String sql2 = "select UserName from LoginUser";
//查询 列UserName中不同的数据,即列数据中有相同的数据只显示一次
String sql3 = "SELECT DISTINCT UserName FROM LoginUser ";
//条件查询文本值-where
String sql4 = "SELECT * FROM LoginUser WHERE UserName='admin'";
//条件查询数值-where
String sql5 = "SELECT * FROM LoginUser WHERE OrderNumber>16";
//条件查询-and\or
String sql6 = "SELECT * FROM LoginUser WHERE UserName='admin' AND OrderNumber<16";
//按字母顺序显示用户名-order by
String sql7 = "SELECT UserName, UserPwd FROM MeetingList ORDER BY UserName";
//以逆字母顺序显示公司名称,并以数字顺序显示顺序号
String sql8 = "SELECT UserName, OrderNumber FROM MeetingList ORDER BY UserName DESC, OrderNumber ASC";
/**
* 更多SQL操作符:top\like\not\
*/
//选取表中的头两行-top
String sql14 = "SELECT TOP 2 * FROM LoginUser";
//选取表中UserName以'a'开头的用户名-like
String sql15 = "SELECT * FROM LoginUser WHERE UserName LIKE 'a%'";
//选取表中UserName不含有'ha'的用户名-not
String sql16 = "SELECT * FROM LoginUser WHERE UserName NOT LIKE '%ha%'";
//创建Statement,操作数据
Statement state = con.createStatement();
//读取数据:生成结果集ResultSet
ResultSet rs = state.executeQuery(sql);
while (rs.next()) {
//获取数据
String UserName = rs.getString("UserName");
String UserPwd = rs.getString("UserPwd");
int OrderNumber = rs.getInt("OrderNumber");
}
rs.close();
state.close();
} catch (SQLException e) {
Log.e(Tag,"读取数据库错误:"+e.getMessage());
} finally {
if (con != null)
try {
con.close(); //数据库的关闭
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private void updateDataToSQL(Connection con) throws java.sql.SQLException {
try {
/*SQL指令对大小不敏感(数据表中主键必须是唯一的,当前主键是UserName)*/
//插入数据-INSERT INTO
String sql9 = "INSERT INTO LoginUser VALUES ('amberoot', '12345', '1')";
//插入指定数据到表LoginUser
String sql10 = "INSERT INTO LoginUser (UserName, UserPwd) VALUES ('haha', '888888')";
//修改表中数据
String sql11 = "UPDATE LoginUser SET UserPwd = '666' WHERE UserName = 'haha' ";
//删除表中数据
String sql12 = "DELETE FROM LoginUser WHERE UserName = 'amber' ";
//删除表中所有数据
String sql13 = "DELETE * FROM LoginUser";
//创建Statement,操作数据
Statement state = con.createStatement();
//插入数据/
int insertSeccess = state.executeUpdate(sql10);
if (insertSeccess == 1) {
Log.i(Tag,"更新数据库成功");
}
state.close();
} catch (SQLException e) {
Log.e(Tag,"更新数据库错误"+e.getMessage());
}
// finally {
// if (con != null)
// try {
// con.close(); //数据库的关闭
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
}
}