java學(xué)習(xí)筆記——使用JDBC，對數(shù)據(jù)庫進行增刪改查(方案一)【推薦】
[導(dǎo)讀]工具類BaseDao.javapackage?com.accp.jdbc;
import?java.sql.Connection;
import?java.sql.DriverManager;
im
工具類BaseDao.java
package?com.accp.jdbc;
import?java.sql.Connection;
import?java.sql.DriverManager;
import?java.sql.PreparedStatement;
import?java.sql.ResultSet;
import?java.sql.SQLException;
import?org.apache.log4j.Logger;
public?class?BaseDao?{
//?使用log4j記錄日志
private?static?Logger?logger?=?Logger.getLogger(BaseDao.class);
//?連接驅(qū)動
private?static?final?String?DRIVER?=?"com.microsoft.sqlserver.jdbc.SQLServerDriver";
//?連接路徑
private?static?final?String?URL?=?"jdbc:sqlserver://localhost:1433;databaseName=test";
//?用戶名
private?static?final?String?USERNAME?=?"sa";
//?密碼
private?static?final?String?PASSWORD?=?"sssaaa";
//靜態(tài)代碼塊
static?{
try?{
//?加載驅(qū)動
Class.forName(DRIVER);
}?catch?(ClassNotFoundException?e)?{
e.printStackTrace();
logger.error("加載驅(qū)動失敗",?e);
}
}
/*
?*?獲取數(shù)據(jù)庫連接
?*/
public?Connection?getConnection()?{
Connection?conn?=?null;
logger.debug("開始連接數(shù)據(jù)庫");
try{
//與數(shù)據(jù)庫建立連接
conn=DriverManager.getConnection(URL,?USERNAME,?PASSWORD);
}catch(SQLException?e){
e.printStackTrace();
logger.error("數(shù)據(jù)庫連接失??！",e);
}
logger.debug("數(shù)據(jù)庫連接成功");
return?conn;
}
/*
?*?關(guān)閉數(shù)據(jù)庫連接，注意關(guān)閉的順序
?*/
public?void?close(ResultSet?rs,?PreparedStatement?ps,?Connection?conn)?{
//注意：最后打開的最先關(guān)閉
if(rs!=null){
try{
rs.close();
rs=null;
}catch(SQLException?e){
e.printStackTrace();
logger.error("關(guān)閉ResultSet失敗",e);
}
}
if(ps!=null){
try{
ps.close();
ps=null;
}catch(SQLException?e){
e.printStackTrace();
logger.error("關(guān)閉PreparedStatement失敗",e);
}
}
if(conn!=null){
try{
conn.close();
conn=null;
}catch(SQLException?e){
e.printStackTrace();
logger.error("關(guān)閉Connection失敗",e);
}
}
}
/*
?*?查詢方法
?*?
?*?sql:?要執(zhí)行的sql語句
?*?handler：自定義接口
?*?obj：可變參數(shù)列表
?*/
publicT?query(String?sql,ResultSetHandlerhandler,Object...?obj){
Connection?conn=getConnection();?//獲得連接
PreparedStatement?ps=null;
ResultSet?rs=null;
try{
//創(chuàng)建PreparedStatement對象
ps=conn.prepareStatement(sql);
//為查詢語句設(shè)置參數(shù)
setParameter(ps,?obj);
//獲得ResultSet結(jié)果集
rs=ps.executeQuery();
//返回對象
return?handler.callback(rs);
}catch(SQLException?e){
e.printStackTrace();
logger.error("數(shù)據(jù)庫操作異常",e);
}finally{
//關(guān)閉連接
close(rs,ps,conn);
logger.debug("釋放資源成功");
}
return?null;
}
/*
?*?增加、修改、刪除,的方法
?*?
?*?obj:?可變參數(shù)列表
?*/
public?int?update(String?sql,Object...obj?){
Connection?conn=getConnection();?//獲得連接
PreparedStatement?ps=null;
int?rows=0;
try{
//創(chuàng)建PreparedStatement對象
ps=conn.prepareStatement(sql);
//為查詢語句設(shè)置參數(shù)
setParameter(ps,?obj);
//獲得受影響的行數(shù)
rows=ps.executeUpdate();
}catch(SQLException?e){
e.printStackTrace();
logger.error("數(shù)據(jù)庫操作異常",e);
}finally{
//關(guān)閉連接
close(null,ps,conn);
logger.debug("釋放資源成功");
}
return?rows;
}
/*
?*?為預(yù)編譯對象設(shè)置參數(shù)
?*/
public?void?setParameter(PreparedStatement?ps,Object...?obj)
throws?SQLException?{
if(obj!=null?&&?obj.length>0){
//循環(huán)設(shè)置參數(shù)
for?(int?i?=?0;?i?<?obj.length;?i++)?{
ps.setObject(i+1,?obj[i]);
}
}
}
}
//自定義接口
interface?ResultSetHandler{
public?T?callback(ResultSet?rs);
}
操作類，進行增刪改查 UserDao.java
package?com.accp.jdbc;
import?java.sql.Connection;
import?java.sql.PreparedStatement;
import?java.sql.ResultSet;
import?java.sql.SQLException;
import?java.sql.Statement;
import?java.util.ArrayList;
import?java.util.List;
import?com.accp.entity.User;
public?class?UserDao?extends?BaseDao?{
public?static?void?main(String[]?args)?{
UserDao?user=new?UserDao();
Listlist=new?ArrayList();
//查詢id<10的數(shù)據(jù)
list=user.queryUser();
//遍歷結(jié)果集
for(User?u:list){
System.out.println(u);
}
//查詢單條數(shù)據(jù)
System.out.println(user.queryUserById(10));
//更新數(shù)據(jù)
User?u=new?User();
u.setId(2);
u.setName("張龍");
u.setAge(22);
System.out.println("更新"+user.updateUser(u)+"條數(shù)據(jù)");
//刪除數(shù)據(jù)
System.out.println("刪除"+user.deleteUser(15)+"條數(shù)據(jù)");
//插入數(shù)據(jù)
User?u1=new?User();
u1.setName("張三");
u1.setAge(18);
user.insertUser(u1);
}
//查詢多條信息
public?ListqueryUser(){
String?sql="select?*?from?users?where?id<10";
//匿名實現(xiàn)自定義接口
ResultSetHandler<List>?handler=new?ResultSetHandler<List>(){
@Override
public?Listcallback(ResultSet?rs)?{
Listlist=new?ArrayList();?//用于存放結(jié)果的集合，User類型
User?user=null;
try?{
while(rs.next()){
user=new?User();?//實例化一個User對象
user.setId(rs.getInt("id"));
user.setAge(rs.getInt("age"));
user.setName(rs.getString("name"));
list.add(user);?//添加到list集合
}
}?catch?(SQLException?e)?{
e.printStackTrace();
}
return?list;
}
};
return?query(sql,handler);
}
//查詢單條數(shù)據(jù)
public?User?queryUserById(int?id){
String?sql="select?*?from?users?where?id=?";
//匿名實現(xiàn)自定義接口
ResultSetHandlerhandler=new?ResultSetHandler()?{
User?user=new?User();
@Override
public?User?callback(ResultSet?rs)?{
try?{
while(rs.next()){
user.setAge(rs.getInt("age"));
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
}
}?catch?(SQLException?e)?{
e.printStackTrace();
}
return?user;
}
};
return?query(sql,handler,id);
}
//更新數(shù)據(jù)
public?int?updateUser(User?user){
String?sql="update?users?set?name=?,age=??where?id=?";
return?update(sql,?user.getName(),user.getAge(),user.getId());
}
//刪除數(shù)據(jù)
public?int?deleteUser(int?id){
String?sql="delete?from?users?where?id=?";
return?update(sql,id);
}
//插入數(shù)據(jù)，并返回自動增長的的主鍵值
public?void?insertUser(User?user){
Connection?conn=getConnection();?//獲得連接
PreparedStatement?ps=null;
ResultSet?rs=null;
String?sql="insert?into?users?values(?,?)";
try{
//?創(chuàng)建能返回自動生成的主鍵的值的預(yù)編譯對象
ps=conn.prepareStatement(sql,?Statement.RETURN_GENERATED_KEYS);
ps.setString(1,user.getName());
ps.setInt(2,?user.getAge());
int?rows=ps.executeUpdate();
//?獲得自動增長的的主鍵值
rs=ps.getGeneratedKeys();
rs.next();
//獲得id
int?id=rs.getInt(1);
System.out.println("成功添加："+rows+"條數(shù)據(jù)，id是："+id);
}catch(SQLException?e){
e.printStackTrace();
}finally{
close(rs,ps,conn);
}
}
}
插入測試數(shù)據(jù) InsertTest.java
package?com.accp.jdbc;
import?java.sql.Connection;
import?java.sql.PreparedStatement;
import?java.sql.SQLException;
public?class?InsertTest?extends?BaseDao??{
public?static?void?main(String[]?args)?{
InsertTest?user=new?InsertTest();
user.insertUser();
}
public?void?insertUser()?{
String?sql?=?"insert?into?users?values(?,?)";
Connection?conn?=?getConnection();
PreparedStatement?ps?=?null;
try?{
//?禁止自動提交事務(wù)
conn.setAutoCommit(false);
//?創(chuàng)建能返回自動生成的主鍵的值的預(yù)編譯對象
ps?=?conn.prepareStatement(sql);
//開始時間的毫秒數(shù)
Long?start=System.currentTimeMillis();
for?(int?i?=?0;?i?<?10000;?i++)?{
ps.setString(1,?i+"");
ps.setInt(2,?22);
ps.addBatch();//?添加到批處理命令中
}
ps.executeBatch();//?執(zhí)行批處理
conn.commit();//?提交事務(wù)
//結(jié)束時間的毫秒數(shù)
Long?stop=System.currentTimeMillis();
//得到總耗時
Long?ms=stop-start;?
System.out.println("插入一萬記錄耗時："+ms+"毫秒");
}?catch?(SQLException?e)?{
e.printStackTrace();
//取消事務(wù)
try{
conn.rollback();
}catch(SQLException?ee){
ee.printStackTrace();
}
}?finally?{
//打開自動提交事務(wù)
try?{
conn.setAutoCommit(true);
}?catch?(SQLException?e)?{
e.printStackTrace();
}
close(null,?ps,?conn);
}
}
}
下載文檔
