當(dāng)批量查詢及修改數(shù)據(jù)是，需要通過與編譯方式對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作渤愁，即通過perparedstatement對(duì)象進(jìn)行操作缰揪。
當(dāng)與編譯是SQL語句編寫方式如下：
select id,name,age from tablename whereid =?;
通過站位符代替具體內(nèi)容測(cè)試代碼如下：
//查詢函數(shù)
public void PreSelect(userInfo user) throws SQLException{
DBUtil data = new DBUtil();
Connection DBconn = (Connection) data.getConnection();
System.out.println(DBconn);
String SQL = "select id, username, email from users where id >?";
try{
PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(SQL);
prep.setInt(1,user.getId());
System.out.println(prep);
ResultSet res = prep.executeQuery();
while(res.next()){
int id = res.getInt(1);
String username = res.getString(2);
System.out.println("查詢內(nèi)容為： "+id+" "+username);
}
}catch (SQLException e){
e.printStackTrace();
}
DBconn.close();
}
通過定義一個(gè)傳輸類可自定義傳輸參數(shù)眨业。
使用相同方法添加一條數(shù)據(jù)如下：
public void PreAdd() throws SQLException{
try{
DBUtil data = new DBUtil();
Connection DBconn = (Connection) data.getConnection();
//添加語句INSERT INTO `test`.`users` (`id`, `username`, `email`) VALUES ('10', 'add', 'add@test');
String SQLadd = "INSERT INTO `test`.`users` (`id`, `username`, `email`) VALUES (?, ?, ?)";
PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(SQLadd);
prep.setInt(1,11);
prep.setString(2, "addr");
prep.setString(3, "addr@test");
prep.executeLargeUpdate();
System.out.println(prep);
}catch (SQLException e){
e.printStackTrace();
}
}
public void PreAdd(userInfo user) throws SQLException{
try{
DBUtil data = new DBUtil();
Connection DBconn = (Connection) data.getConnection();
//添加語句INSERT INTO `test`.`users` (`id`, `username`, `email`) VALUES ('10', 'add', 'add@test');
String SQLadd = "INSERT INTO `test`.`users` (`id`, `username`, `email`) VALUES (?, ?, ?)";
PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(SQLadd);
prep.setInt(1,user.getId());
prep.setString(2, user.getUsername());
prep.setString(3, user.getEmail());
prep.executeLargeUpdate();
System.out.println(prep);
}catch (SQLException e){
e.printStackTrace();
}
}
數(shù)據(jù)刪除方式如下
//刪除函數(shù)
public void PreDel(userInfo user) throws SQLException{
try{
DBUtil data = new DBUtil();
Connection DBconn = (Connection) data.getConnection();
//刪除語句DELETE FROM `test`.`users` WHERE `id`='5';
String SQL = "DELETE FROM `test`.`users` WHERE `id`=?";
PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(SQL);
prep.setInt(1, user.getId());
prep.executeUpdate();
}catch ( SQLException e){
e.printStackTrace();
}
}
'''
修改函數(shù)如下：
//修改函數(shù)
public void PreMod(userInfo user) throws SQLException{
try{
DBUtil data = new DBUtil();
Connection DBconn = (Connection) data.getConnection();
System.out.println("premod"+DBconn);
//修改語句UPDATE test.users SET username='9', email='9' WHERE id='6';
String sql = "UPDATE test.users SET username=?, email=? WHERE id=?";
PreparedStatement prep = (PreparedStatement) DBconn.prepareStatement(sql);
prep.setString(1, user.getUsername());
prep.setString(2, user.getEmail());
prep.setInt(3, user.getId());
System.out.println("修改語句"+prep);
prep.executeUpdate();
System.out.println(prep);
}catch (SQLException e){
e.printStackTrace();
}
}
整體測(cè)試函數(shù)如下：
//運(yùn)行測(cè)試函數(shù)
@Test
public void runtest() throws SQLException{
userInfo use = new userInfo();
use.setId(5);
use.setUsername("sheng");
use.setEmail("sheng@124.com");
//添加函數(shù)測(cè)試
//PreAdd(use);
//刪除測(cè)試函數(shù)
//PreDel(use);
//查詢函數(shù)測(cè)試
PreMod(use);
PreSelect(use);
//
}
