1、測(cè)試數(shù)據(jù)庫(kù)
1.1 數(shù)據(jù)庫(kù)數(shù)據(jù)
1.2 數(shù)據(jù)庫(kù)結(jié)構(gòu)
1.3 數(shù)據(jù)庫(kù)腳本
DROP TABLE IF EXISTS `school`.`student`;
CREATE TABLE `school`.`student` (
`id` int(11) NOT NULL default '0',
`name` varchar(20) default NULL,
`sex` varchar(10) default NULL,
`age` int(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('201201', '張三', '男', '21');
INSERT INTO `student` VALUES ('201202', '李四', '男', '22');
INSERT INTO `student` VALUES ('201203', '王五', '女', '20');
INSERT INTO `student` VALUES ('201204', '趙六', '男', '21');
INSERT INTO `student` VALUES ('201205', '小紅', '女', '19');
INSERT INTO `student` VALUES ('201206', '小明', '男', '22');
2、DBHelper用法
2.1 增加【Add】
【示例代碼】
package com.hebut.util;
public class DBHelperApp {
/**
* 增加【Add】
*/
public static void main(String[] args) {
//第一種情況
String sql1="Insert Into student Values ('201208', '張三', '男', '21')";
System.out.println(DBHelper.executeNonQuery(sql1));
//第二種情況
String sql2="Insert Into student Values (?,?,?,?)";
Object[] obj = new Object[]{"201209","張三", "男", "21"};
System.out.println(DBHelper.executeNonQuery(sql2,obj));
}
}
【運(yùn)行結(jié)果】
1）控制臺(tái)
1
1
2）數(shù)據(jù)庫(kù)
2.2 刪除【Del】
【示例代碼】
package com.hebut.util;
public class DBHelperApp {
/**
* 刪除【Del】
*/
public static void main(String[] args) {
//第一種情況
String sql1="Delete From student Where id='201202'";
System.out.println(DBHelper.executeNonQuery(sql1));
//第二種情況
String sql2="Delete From student Where id=? And name=?";
Object[] obj = new Object[]{"201208","張三"};
System.out.println(DBHelper.executeNonQuery(sql2,obj));
}
}
【運(yùn)行結(jié)果】
1）控制臺(tái)
1
1
2）數(shù)據(jù)庫(kù)
從圖中可以看出id為"201202"和"201208"已經(jīng)刪除。
2.3 修改【Update】
【示例代碼】
package com.hebut.util;
public class DBHelperApp {
/**
* 修改【Update】
*/
public static void main(String[] args) {
//第一種情況
String sql1="Update student Set age='27' Where id='201207'";
System.out.println(DBHelper.executeNonQuery(sql1));
//第二種情況
String sql2="Update student Set name=? Where id=?";
Object[] obj = new Object[]{"張二","201201"};
System.out.println(DBHelper.executeNonQuery(sql2,obj));
}
}
【運(yùn)行結(jié)果】
1）控制臺(tái)
1
1
2）數(shù)據(jù)庫(kù)
備注：后面的操作都以上面數(shù)據(jù)為基準(zhǔn)。
2.4 查詢【Query】
【示例代碼】
package com.hebut.util;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBHelperApp {
/**
* 查詢【Query】
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
//第一種情況
String sql1="Select * From student";
//第一步：查詢
ResultSet rs1 = DBHelper.executeQuery(sql1);
//第二步：輸出
while(rs1.next()){
System.out.println("姓名："+rs1.getString(2));
}
//第三步：關(guān)閉
DBHelper.free(rs1);
//第二種情況
String sql2="Select * From student Where name=?";
Object[] obj = new Object[]{"張三"};
//第一步：查詢
ResultSet rs2 = DBHelper.executeQuery(sql2,obj);
//第二步：輸出
while(rs2.next()){
System.out.println("學(xué)號(hào)："+rs2.getString(1));
}
//第三步：關(guān)閉
DBHelper.free(rs2);
}
}
【運(yùn)行結(jié)果】
姓名：張二
姓名：王五
姓名：趙六
姓名：小紅
姓名：小明
姓名：張三
姓名：張三
學(xué)號(hào)：201207
學(xué)號(hào)：201209
2.5 獲取查詢行數(shù)
【示例代碼】
package com.hebut.util;
public class DBHelperApp {
/**
* 獲取查詢行數(shù)
*/
public static void main(String[] args) {
//第一種情況
String sql1="Select * From student";
System.out.println(DBHelper.getCount(sql1));
//第二種情況
String sql2="Select * From student Where name=?";
Object[] obj = new Object[]{"張三"};
System.out.println(DBHelper.getCount(sql2,obj));
}
}
【運(yùn)行結(jié)果】
7
2
2.6 判斷記錄存在
【示例代碼】
package com.hebut.util;
public class DBHelperApp {
/**
* 判斷記錄存在
*/
public static void main(String[] args) {
//第一種情況
String sql1="Select * From student";
System.out.println(DBHelper.isExist(sql1));
//第二種情況
String sql2="Select * From student Where name=?";
Object[] obj = new Object[]{"張五"};
System.out.println(DBHelper.isExist(sql2,obj));
}
}
【運(yùn)行結(jié)果】
true
false
3、DBHelper代碼
package com.hebut.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class DBHelper {
// 此方法為獲取數(shù)據(jù)庫(kù)連接
public static Connection getConnection() {
Connection conn = null;
try {
String driver = "com.mysql.jdbc.Driver"; // 數(shù)據(jù)庫(kù)驅(qū)動(dòng)
String url = "jdbc:MySQL://127.0.0.1:3306/school";// 數(shù)據(jù)庫(kù)
String user = "root"; // 用戶名
String password = "hadoop"; // 密碼
Class.forName(driver); // 加載數(shù)據(jù)庫(kù)驅(qū)動(dòng)
if (null == conn) {
conn = DriverManager.getConnection(url, user, password);
}
} catch (ClassNotFoundException e) {
System.out.println("Sorry,can't find the Driver!");
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 增刪改【Add、Del、Update】
*
* @param sql
* @return int
*/
public static int executeNonQuery(String sql) {
int result = 0;
Connection conn = null;
Statement stmt = null;
try {
conn = getConnection();
stmt = conn.createStatement();
result = stmt.executeUpdate(sql);
} catch (SQLException err) {
err.printStackTrace();
free(null, stmt, conn);
} finally {
free(null, stmt, conn);
}
return result;
}
/**
* 增刪改【Add、Delete、Update】
*
* @param sql
* @param obj
* @return int
*/
public static int executeNonQuery(String sql, Object... obj) {
int result = 0;
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
pstmt.setObject(i + 1, obj[i]);
}
result = pstmt.executeUpdate();
} catch (SQLException err) {
err.printStackTrace();
free(null, pstmt, conn);
} finally {
free(null, pstmt, conn);
}
return result;
}
/**
* 查【Query】
*
* @param sql
* @return ResultSet
*/
public static ResultSet executeQuery(String sql) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException err) {
err.printStackTrace();
free(rs, stmt, conn);
}
return rs;
}
/**
* 查【Query】
*
* @param sql
* @param obj
* @return ResultSet
*/
public static ResultSet executeQuery(String sql, Object... obj) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
pstmt.setObject(i + 1, obj[i]);
}
rs = pstmt.executeQuery();
} catch (SQLException err) {
err.printStackTrace();
free(rs, pstmt, conn);
}
return rs;
}
/**
* 判斷記錄是否存在
*
* @param sql
* @return Boolean
*/
public static Boolean isExist(String sql) {
ResultSet rs = null;
try {
rs = executeQuery(sql);
rs.last();
int count = rs.getRow();
if (count > 0) {
return true;
} else {
return false;
}
} catch (SQLException err) {
err.printStackTrace();
free(rs);
return false;
} finally {
free(rs);
}
}
/**
* 判斷記錄是否存在
*
* @param sql
* @return Boolean
*/
public static Boolean isExist(String sql, Object... obj) {
ResultSet rs = null;
try {
rs = executeQuery(sql, obj);
rs.last();
int count = rs.getRow();
if (count > 0) {
return true;
} else {
return false;
}
} catch (SQLException err) {
err.printStackTrace();
free(rs);
return false;
} finally {
free(rs);
}
}
/**
* 獲取查詢記錄的總行數(shù)
*
* @param sql
* @return int
*/
public static int getCount(String sql) {
int result = 0;
ResultSet rs = null;
try {
rs = executeQuery(sql);
rs.last();
result = rs.getRow();
} catch (SQLException err) {
free(rs);
err.printStackTrace();
} finally {
free(rs);
}
return result;
}
/**
* 獲取查詢記錄的總行數(shù)
*
* @param sql
* @param obj
* @return int
*/
public static int getCount(String sql, Object... obj) {
int result = 0;
ResultSet rs = null;
try {
rs = executeQuery(sql, obj);
rs.last();
result = rs.getRow();
} catch (SQLException err) {
err.printStackTrace();
} finally {
free(rs);
}
return result;
}
/**
* 釋放【ResultSet】資源
*
* @param rs
*/
public static void free(ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException err) {
err.printStackTrace();
}
}
/**
* 釋放【Statement】資源
*
* @param st
*/
public static void free(Statement st) {
try {
if (st != null) {
st.close();
}
} catch (SQLException err) {
err.printStackTrace();
}
}
/**
* 釋放【Connection】資源
*
* @param conn
*/
public static void free(Connection conn) {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException err) {
err.printStackTrace();
}
}
/**
* 釋放所有數(shù)據(jù)資源
*
* @param rs
* @param st
* @param conn
*/
public static void free(ResultSet rs, Statement st, Connection conn) {
free(rs);
free(st);
free(conn);
}
}
|
