1.连接数据库
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
1.1 直连
Connection con = null;
try {
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=bnuc2c";
//使用SQL驱动
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String username = "sa";
String password = "0000";
con = DriverManager.getConnection(url, username, password);
} catch(Exception e) {
e.printStackTrace();
throw e;
}
1.2连接池方式
private DataSource ds = null;
//生成连接池
try{
Context initCtx = new InitialContext();
//取得JNDI资源的根,需配置连接池
Context envCtx = (Context) initCtx.lookup("java:comp/env");
//取得已经定义好的JNDI资源
ds = (DataSource) envCtx.lookup("jdbc/c2c");
}catch(Exception e){
e.getStackTrace();
}
2.添加数据
2.1 PreparedStatement方式 安全较高
import java.sql.*;
Connection conn = null;
PreparedStatement stmt = null;
String sqlCommand = "insert into Shop(shopName,shopAddress,openDate,activityDays,goodsCount,bulletin,shopIntro," +
"ownerID,catID) values(?,?,getDate(),0,0,?,?,?,?)";
try {
conn = DBPool.getConnection();
stmt = conn.prepareStatement(sqlCommand);
stmt.setString(1,shopModel.getShopName());
stmt.setString(2,shopModel.getShopAddress());
stmt.setString(3,shopModel.getBulletin());
stmt.setString(4,shopModel.getShopIntro());
stmt.setInt(5,shopModel.getOwnerID());
stmt.setInt(6,shopModel.getCatID());
int nRow = stmt.executeUpdate();
conn.commit();
if(nRow > 0){
return ReturnResult.SUCCESS;
}else{
return ReturnResult.FAIL;
}
}catch(Exception e){
e.printStackTrace();
}finally{
DBPool.closeStatement(stmt);
DBPool.closeConnection(conn);
}
2.2 Statement方式
import java.sql.*;
Connection conn;
Statement stmt;
String sqlCommand = "insert into Order_Table values('" + this.createID() + "','" + username + "','" + productlist + "',0,"+ unitprice + "," + totalprices
+ ",getdate())";
try {
conn = DataBase.GetConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
int nRow = stmt.executeUpdate(sqlCommand);
conn.close();
stmt.close();
if(nRow > 0)
return ProductInfo.R_SUCESS;
else
return ProductInfo.R_FAIL;
}catch(Exception e){
System.out.println(e.getMessage());
}
3.更新数据
Connection conn = null;
PreparedStatement stmt = null;
String sqlCommand = "update Shop set shopName=?,shopAddress=?,bulletin=?,shopIntro=?,catID=? where shopID = ?";
try {
conn = DBPool.getConnection();
stmt = conn.prepareStatement(sqlCommand);
stmt.setString(1,shopModel.getShopName());
stmt.setString(2,shopModel.getShopAddress());
stmt.setString(3,shopModel.getBulletin());
stmt.setString(4,shopModel.getShopIntro());
stmt.setInt(5,shopModel.getCatID());
stmt.setInt(6,shopModel.getShopID());
int nRow = stmt.executeUpdate();
conn.commit();
if(nRow > 0){
return ReturnResult.SUCCESS;
}else{
return ReturnResult.FAIL;
}
}catch(Exception e){
e.printStackTrace();
}finally{
DBPool.closeStatement(stmt);
DBPool.closeConnection(conn);
}