JAVA数据库操作

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);
}