JDBCでのexecute、executeQueryとexecuteUpdateの違い
25544 ワード
1.executeQueryは、単一の結果セットを生成するために使用されます.(ResultSet)の文です.たとえばSELECT文です.SQL文を最も多く実行する方法が使用されます.この方法はSELECT文を実行するために使用されます.ほとんど最も多く使用されているSQL文です.ただし、クエリー文のみを実行し、実行後にクエリ結果を表すResultSetイメージを返します.2.executeUpdateはINSERT、UPDATEまたはDELETE文、およびSQL DDLを実行するために使用されます.(データ定義言語)CREATE TABLEやDROP TABLEなどの文.INSERT、UPDATEまたはDELETE文の効果は、表の0行または複数行の1列または複数列を変更することです.executeUpdateの戻り値は、影響を受ける行数を示す整数(int)です.(更新カウント).CREATE TABLEやDROP TABLEなどの行を操作しない文の場合、executeUpdateの戻り値は合計ゼロです.3.executeは任意のSQL文を実行するために使用でき、そのSQL文を実行してResultSetを返したかどうかを示すboolean値を返します.実行後の結果がResultSetの場合、trueを返します.そうでなければfalseを返します.ただし、SQL文を実行するとき面倒なことに、通常はexecuteメソッドを使用してSQL文を実行する必要はありませんが、executeQueryまたはexecuteUpdateを使用するのが適切ですが、SQL文のタイプが不明な場合はexecuteメソッドを使用してSQL文を実行するしかありません.
4.MySqlUtilツールクラス
//
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 queryFeature(String groupId, String userId) {
if (conn == null) {
return null;
}
ArrayList 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 queryFeatureByGroupId(String groupId) {
if (conn == null) {
return null;
}
ArrayList 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()
+ "
SQLState: " + e.getSQLState()
+ "
VendorError:" + 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 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();
}
}