1.executeQuery
用于产生单个结果集（ResultSet）的语句，例如 SELECT 语句。 被使用最多的执行 SQL 语句的方法。这个方法被用来执行 SELECT 语句，它几乎是使用最多的 SQL 语句。但也只能执行查询语句，执行后返回代表查询结果的ResultSet对象。
2.executeUpdate
用于执行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句，例如 CREATE TABLE 和 DROP TABLE。INSERT、UPDATE 或 DELETE 语句的效果是修改表中零行或多行中的一列或多列。executeUpdate 的返回值是一个整数（int），指示受影响的行数（即更新计数）。对于 CREATE TABLE 或 DROP TABLE 等不操作行的语句，executeUpdate 的返回值总为零。
3.execute
可用于执行任何SQL语句，返回一个boolean值，表明执行该SQL语句是否返回了ResultSet。如果执行后第一个结果是ResultSet，则返回true，否则返回false。但它执行SQL语句时比较麻烦，通常我们没有必要使用execute方法来执行SQL语句，而是使用executeQuery或executeUpdate更适合，但如果在不清楚SQL语句的类型时则只能使用execute方法来执行该SQL语句了。
//加载驱动
Class.forName(driver);
//获取数据库连接
conn = DriverManager.getConnection(url, user, pass);
//使用Connection来创建一个Statment对象
stmt = conn.createStatement();
//执行SQL,返回boolean值表示是否包含ResultSet
boolean hasResultSet = stmt.execute(sql);
//如果执行后有ResultSet结果集
if (hasResultSet) {
//获取结果集
rs = stmt.getResultSet();
//ResultSetMetaData是用于分析结果集的元数据接口
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//迭代输出ResultSet对象
while (rs.next()) {//依次输出每列的值
for (int i = 0; i < columnCount; i++) {
System.out.print(rs.getString(i + 1) + "/t");
}
System.out.print("/n");
}
} else {
System.out.println("该SQL语句影响的记录有" + stmt.getUpdateCount() + "条");
}
4.MySqlUtil工具类
public class MySqlUtil {
public static PreparedStatement ps;
public static Connection conn;
//openConnection是连接数据库
public static Connection openConnection(String url, String user,
String password) {
try {
final String DRIVER_NAME = "com.mysql.jdbc.Driver";
Class.forName(DRIVER_NAME);
conn = DriverManager.getConnection(url, user, password);
} catch (ClassNotFoundException e) {
conn = null;
} catch (SQLException e) {
conn = null;
printLog(e);
}
return conn;
}
//查询数据库
public static UgHouseBean queryUgHouse(String sql) {
if (conn == null) {
return null;
}
Statement statement = null;
ResultSet result = null;
UgHouseBean houseBean = null;
try {
statement = conn.createStatement();
result = statement.executeQuery(sql);
if (result != null && result.first()) {
int idColumnIndex = result.findColumn("id");
int user_noColumnIndex = result.findColumn("face_user_no");
int stateColumnIndex = result.findColumn("state");
int face_stateColumnIndex = result.findColumn("face_state");
int user_idColumnInde = result.findColumn("user_id");
int pad_stateColumnIndex = result.findColumn("pad_state");
int door_stateColumnIndex = result.findColumn("door_state");
while (!result.isAfterLast()) {
houseBean = new UgHouseBean();
houseBean.setId(result.getString(idColumnIndex));
houseBean.setFace_user_no(result.getString(user_noColumnIndex));
houseBean.setState(result.getString(stateColumnIndex));
houseBean.setFace_state(result.getString(face_stateColumnIndex));
houseBean.setUser_id(result.getString(user_idColumnInde));
houseBean.setDoor_state(result.getString(door_stateColumnIndex));
houseBean.setPad_state(result.getString(pad_stateColumnIndex));
// Logger.i(houseBean.toString());
result.next();
}
}
return houseBean;
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
Logger.i("查询数据库失败");
} finally {
try {
if (result != null) {
result.close();
result = null;
}
if (statement != null) {
statement.close();
statement = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return houseBean;
}
//执行MySQL语句的函数
public static boolean execSQL(String sql) {
boolean execResult = false;
if (conn == null) {
return execResult;
}
Statement statement = null;
try {
statement = conn.createStatement();
if (statement != null) {
execResult = statement.execute(sql);
}
} catch (SQLException e) {
execResult = false;
e.printStackTrace();
printLog(e);
} finally {
try {
if (statement != null) {
statement.close();
statement = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return execResult;
}
public static boolean execSQLUpdate(String sql) {
boolean execResult = false;
if (conn == null) {
return execResult;
}
Statement statement = null;
try {
statement = conn.createStatement();
//该方法用于修改数据库内容的。
if (statement != null) {
int row = statement.executeUpdate(sql);
if (row > 0) {
execResult = true;
}
}
} catch (SQLException e) {
execResult = false;
e.printStackTrace();
printLog(e);
} finally {
try {
if (statement != null) {
statement.close();
statement = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return execResult;
}
/**
* 创建用户组
*
* @param group
* @return
*/
public static boolean addGroup(Group group) {
boolean execResult = false;
if (conn == null) {
return execResult;
}
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("insert into user_group (group_id,ctime,update_time) values (?,?,?)");
if (ps != null) {
ps.setString(1, group.getGroupId());
ps.setLong(2, System.currentTimeMillis());
ps.setLong(3, System.currentTimeMillis());
ps.executeUpdate();
execResult = true;
}
} catch (SQLException e) {
execResult = false;
e.printStackTrace();
printLog(e);
} finally {
try {
if (ps != null) {
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return execResult;
}
/**
* 添加用户
*
* @return
*/
public static boolean addUser(User user) {
boolean execResult = false;
if (conn == null) {
return execResult;
}
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("insert into ug_feature_user (user_id,face_user_no,user_info,group_id,ctime,update_time,door_id) values (?,?,?,?,?,?,?)");
if (ps != null) {
ps.setString(1, user.getUserId());
ps.setString(2, user.getFace_user_no());
ps.setString(3, user.getUserInfo());
ps.setString(4, user.getGroupId());
ps.setLong(5, System.currentTimeMillis());
ps.setLong(6, System.currentTimeMillis());
ps.setInt(7, user.getDoorId());
ps.executeUpdate();
for (Feature feature : user.getFeatureList()) {
execResult = addFeature(feature);
}
}
EventBus.getDefault().post("In:添加新用户-" + user.getUserId());
} catch (SQLException e) {
execResult = false;
e.printStackTrace();
printLog(e);
EventBus.getDefault().post("In:添加新用户失败-" + user.getUserId());
} finally {
try {
if (ps != null) {
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
EventBus.getDefault().post("In:添加新用户失败-" + user.getUserId());
}
}
return execResult;
}
/**
* 添加人脸特征
*
* @param feature
* @return
*/
public static boolean addFeature(Feature feature) {
boolean execResult = false;
if (conn == null) {
return execResult;
}
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("insert into ug_feature (face_token,group_id,user_id,feature,image_name,ctime,update_time) values (?,?,?,?,?,?,?)");
if (ps != null) {
ps.setString(1, feature.getFaceToken());
ps.setString(2, feature.getGroupId());
ps.setString(3, feature.getUserId());
ps.setBytes(4, feature.getFeature());
ps.setString(5, feature.getImageName());
ps.setLong(6, System.currentTimeMillis());
ps.setLong(7, System.currentTimeMillis());
ps.executeUpdate();
execResult = true;
}
} catch (SQLException e) {
execResult = false;
e.printStackTrace();
printLog(e);
} finally {
try {
if (ps != null) {
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return execResult;
}
/**
* 查询用户
*
* @param groupId
* @param userId
* @return
*/
public static User queryUser(String groupId, String userId) {
if (conn == null) {
return null;
}
PreparedStatement ps = null;
User user = null;
try {
ps = conn.prepareStatement("select * from ug_feature_user where group_id='" + groupId + "' and user_id='" + userId + "';");
if (ps != null) {
ResultSet rs = ps.executeQuery();
while (rs.next()) {
user = new User();
user.setDoorId(rs.getInt("door_id"));
user.setFace_user_no(rs.getString("face_user_no"));
user.setGroupId(rs.getString("group_id"));
user.setUserId(rs.getString("user_id"));
user.setUserInfo(rs.getString("user_info"));
user.setCtime(rs.getLong("ctime"));
user.setUpdateTime(rs.getLong("update_time"));
}
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
} finally {
try {
if (ps != null) {
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return user;
}
/**
* 获取指定用户的人脸特征
*
* @param groupId
* @param userId
* @return
*/
public static List<Feature> queryFeature(String groupId, String userId) {
if (conn == null) {
return null;
}
ArrayList<Feature> featureList = new ArrayList<>();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement("select * from ug_feature where group_id='" + groupId + "' and user_id='" + userId + "';");
if (ps != null) {
rs = ps.executeQuery();
while (rs.next()) {
Feature feature = new Feature();
feature.setGroupId(rs.getString("group_id"));
feature.setUserId(rs.getString("user_id"));
feature.setFaceToken(rs.getString("face_token"));
feature.setFeature(rs.getBytes("feature"));
feature.setImageName(rs.getString("image_name"));
feature.setCtime(rs.getLong("ctime"));
feature.setUpdateTime(rs.getLong("update_time"));
featureList.add(feature);
}
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (ps != null) {
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return featureList;
}
/**
* 查询指定组下面的所有人脸特征
*
* @param groupId
* @return
*/
public static List<Feature> queryFeatureByGroupId(String groupId) {
if (conn == null) {
return null;
}
ArrayList<Feature> featureList = new ArrayList<>();
PreparedStatement ps = null;
ResultSet rs = null;
User user = null;
try {
ps = conn.prepareStatement("select * from ug_feature where group_id='" + groupId + "';");
if (ps != null) {
rs = ps.executeQuery();
while (rs.next()) {
Feature feature = new Feature();
feature.setGroupId(rs.getString("group_id"));
feature.setUserId(rs.getString("user_id"));
feature.setFaceToken(rs.getString("face_token"));
feature.setFeature(rs.getBytes("feature"));
feature.setImageName(rs.getString("image_name"));
feature.setCtime(rs.getLong("ctime"));
feature.setUpdateTime(rs.getLong("update_time"));
featureList.add(feature);
}
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (ps != null) {
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return featureList;
}
/**
* 将指定路径的文件(比如：图片，word文档等)存储到数据库
*
* @param strFile 要存放到数据库的文件路径，如D:\\a.jpg
*/
public static void storeImg(String strFile) throws Exception {
int id = 0;
File file = new File(strFile);
FileInputStream fis = new FileInputStream(file);
try {
ps = conn.prepareStatement("insert "
+ "into PIC values (?,?,?)");
ps.setInt(1, id);
ps.setString(2, file.getName());
ps.setBinaryStream(3, fis, (int) file.length());
ps.executeUpdate();
System.out.println("file insert success ");
} catch (SQLException e) {
printLog(e);
e.printStackTrace();
} finally {
ps.close();
fis.close();
conn.close();
}
}
/**
* 将指定路径的文件(比如：图片，word文档等)存储到数据库
*
* @param faceByte 要存放到数据库的文件路径，如D:\\a.jpg
*/
public static void storeByteArray(byte[] faceByte) throws Exception {
int id = 0;
try {
ps = conn.prepareStatement("update "
+ "ug_face_data set face_data=? where id=1");
ps.setBytes(1, faceByte);
ps.executeUpdate();
System.out.println("file insert success ");
} catch (SQLException e) {
printLog(e);
e.printStackTrace();
} finally {
ps.close();
conn.close();
}
}
/**
* 将指定路径的文件(比如：图片，word文档等)存储到数据库
*
* @param faceByte 要存放到数据库的文件路径，如D:\\a.jpg
*/
public static void storeImageByteArray(byte[] faceByte) throws Exception {
int id = 0;
try {
ps = conn.prepareStatement("update "
+ "ug_face_data set img=? where id=1");
ps.setBytes(1, faceByte);
ps.executeUpdate();
System.out.println("file insert success ");
} catch (SQLException e) {
printLog(e);
e.printStackTrace();
} finally {
ps.close();
conn.close();
}
}
/**
* 读取数据库文件（.sql），并执行sql语句
*/
public static void executeAssetsSQL(Context mContext, String schemaName) {
BufferedReader in = null;
try {
in = new BufferedReader(new InputStreamReader(mContext.getAssets()
.open(schemaName + ".sql")));
System.out.println("路径:" + schemaName + ".sql");
String line;
String buffer = "";
while ((line = in.readLine()) != null) {
buffer += line;
if (line.trim().endsWith(";")) {
execSQL(buffer.replace(";", ""));
buffer = "";
}
}
} catch (IOException e) {
Logger.e("db-error", e.toString());
} finally {
try {
if (in != null)
in.close();
} catch (IOException e) {
Logger.e("db-error", e.toString());
}
}
}
/**
* 打印 sql 异常
*
* @param e
*/
private static void printLog(SQLException e) {
Logger.e("SQLException: " + e.getMessage()
+ "\nSQLState: " + e.getSQLState()
+ "\nVendorError:" + e.getErrorCode());
}
/**
* 获取用户信息
*
* @param groupId
* @param userId
* @return
*/
public static User getUserInfo(String groupId, String userId) {
if (TextUtils.isEmpty(groupId) || TextUtils.isEmpty(userId)) {
return null;
}
User user = queryUser(groupId, userId);
if (user != null) {
List<Feature> featureList = queryFeature(groupId, userId);
user.setFeatureList(featureList);
}
return user;
}
/* *//**
* 将存储在数据库中的文件(比如：图片，word文档等)读取到指定路径
*
* @param id 数据库里记录的id
*//*
public static void readImg(int id) throws Exception {
byte[] buffer = new byte[4096];
FileOutputStream outputImage = null;
InputStream is = null;
try {
ps = conn.prepareStatement("select face_data from ug_face_data where id =?");
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
rs.next();
File file = new File(FileUtils.getFilePath() + "/image/", "a.data");
if (!file.exists()) {
file.createNewFile();
}
outputImage = new FileOutputStream(file);
Blob blob = rs.getBlob("face_data"); //img为数据库存放图片字段名称
is = blob.getBinaryStream();
int size = 0;
while ((size = is.read(buffer)) != -1) {
outputImage.write(buffer, 0, size);
}
System.out.println("file read success ");
} catch (Exception e) {
e.printStackTrace();
} finally {
is.close();
outputImage.close();
ps.close();
conn.close();
}
}*/
/**
* 更新用户
*
* @param user
* @return
*/
public static boolean updateUser(User user) {
boolean execResult = false;
if (conn == null) {
return execResult;
}
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("update ug_feature_user set face_user_no='" + user.getFace_user_no() + "', door_id=" + user.getDoorId() + ", update_time='" + user.getUpdateTime() + "' where user_id='" + user.getUserId() + "';");
ps.executeUpdate();
for (Feature feature : user.getFeatureList()) {
updateFeature(feature);
}
execResult = true;
EventBus.getDefault().post("In:更新用户-" + user.getDoorId());
} catch (SQLException e) {
execResult = false;
e.printStackTrace();
printLog(e);
EventBus.getDefault().post("In:更新用户失败-" + user.getUserId());
} finally {
try {
if (ps != null) {
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
EventBus.getDefault().post("In:更新用户失败-" + user.getUserId());
}
}
return execResult;
}
/**
* 更新指定用户的人脸特征
*
* @return
*/
public static boolean updateFeature(Feature feature) {
boolean execResult = false;
if (conn == null) {
return execResult;
}
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("update ug_feature set face_token=?,group_id=?,feature=?,image_name=?,update_time=? where user_id=?");
if (ps != null) {
ps.setString(1, feature.getFaceToken());
ps.setString(2, feature.getGroupId());
ps.setBytes(3, feature.getFeature());
ps.setString(4, feature.getImageName());
ps.setLong(5, System.currentTimeMillis());
ps.setString(6, feature.getUserId());
ps.executeUpdate();
execResult = true;
} else {
execResult = false;
}
} catch (SQLException e) {
execResult = false;
e.printStackTrace();
printLog(e);
} finally {
try {
if (ps != null) {
ps.close();
ps = null;
}
} catch (SQLException e) {
e.printStackTrace();
printLog(e);
}
}
return execResult;
}
public void onInsert() {
new Thread(new Runnable() {
@Override
public void run() {
String sql = "insert into users values(15, 'xiaoming')";
MySqlUtil.execSQL(sql);
Logger.i("onInsert", "onInsert");
}
}).start();
}
public void onDelete() {
new Thread(new Runnable() {
@Override
public void run() {
String sql = "delete from users where name='hanmeimei'";
MySqlUtil.execSQL(sql);
Logger.i("onDelete", "onDelete");
}
}).start();
}
public void onUpdate() {
new Thread(new Runnable() {
@Override
public void run() {
String sql = "update users set name='lilei' where name='liyanzhen'";
MySqlUtil.execSQL(sql);
Logger.i("onUpdate", "onUpdate");
}
}).start();
}
public void onQuery() {
new Thread(new Runnable() {
@Override
public void run() {
MySqlUtil.execSQL("select * from users");
Logger.i("onQuery", "onQuery");
}
}).start();
}
}
523
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
