sqlserver数据库的JDBC

//封装的DBmanger 
public class DBmanger {
 
public static final String DRIVERNAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static final String URL = "jdbc:sqlserver://localhost:1433; DatabaseName=MySchool";
public static final String USERNAME = "sa";
public static final String PASSWORD = "950126";
 
//加载驱动
static {
try {
Class.forName(DRIVERNAME);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
 * 增删改的方法
 * @param sql
 * @param obj
 * @return
 */
public static boolean executeUpdates(String sql, Object[] obj){
boolean result = false;
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = getConnection();
stmt = conn.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
stmt.setObject(i+1, obj[i]);
}
int executeUpdate = stmt.executeUpdate();
if (executeUpdate>0) {
result = true;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(conn, stmt, null);
}
return result;
}
 
/**
 * 数据库连接方法
 * 
 * @return
 */
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
 
/**
 * 数据库关闭的方法
 * 
 * @param conn
 * @param stmt
 * @param rs
 */
public static void closeAll(Connection conn, Statement stmt, ResultSet rs) {
 
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
 
}
 
}

增删改查

/**
 * 添加菜品
 * @return
 */
public boolean insertDish(BusinessDetail bd){
String sql = "insert into BusinessDetail values (?,?,?,?)";
Object[] obj = {bd.getBD_dishName(),bd.getBD_dishInfo(),bd.getBD_dishprice(),bd.getBM_id()};
return DBManager.executeUpdate(sql, obj);
}
/**
 * 删除菜品
 * @param BD_id
 * @return
 */
public boolean deleteDish(int BD_id){
String sql = "delete from BusinessDetail where BD_id = ?";
Object[] obj = {BD_id};
return DBManager.executeUpdate(sql, obj);
}
/**
 * 修改菜品
 * @param bd
 * @return
 */
public boolean updateDish(BusinessDetail bd){
String sql ="update BusinessDetail set BD_dishName = ?,BD_dishInfo=?,BD_dishprice=? where BD_id = ?";
Object[] obj = {bd.getBD_dishName(),bd.getBD_dishInfo(),bd.getBD_dishprice(),bd.getBD_id()};
return DBManager.executeUpdate(sql, obj);
}
/**
 * 根据店家信息id查询所有的菜品
 * @return
 */
public List<BusinessDetail> selectAllDish(int BM_id){
List<BusinessDetail> list = new ArrayList<BusinessDetail>();
Connection conn = DBManager.getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = "select BD_id,BD_dishName,BD_dishInfo,BD_dishprice from BusinessDetail where BM_id = ?";
 
try {
stmt = conn.prepareStatement(sql);
stmt.setInt(1, BM_id);
rs = stmt.executeQuery();
while (rs.next()) {
BusinessDetail bd = new BusinessDetail();
bd.setBD_id(rs.getInt(1));
bd.setBD_dishName(rs.getString(2));
bd.setBD_dishInfo(rs.getString(3));
bd.setBD_dishprice(rs.getInt(4));
 
list.add(bd);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBManager.closeAll(conn, stmt, rs);
}
return list;
}

单元测试(需要导包)

    @Test
public void testAllDish(){
//BusinessDetail bd = new BusinessDetail();
//bd.setBD_dishName("薯片");
//bd.setBD_dishInfo("脆口塑像");
//bd.setBD_dishprice(10);
//bd.setBM_id(1);
//boolean insertDish = insertDish(bd);
//System.out.println(insertDish);
//boolean deleteDish = deleteDish(20);
//System.out.println(deleteDish);
List<BusinessDetail> list = selectAllDish(1);
//for (BusinessDetail businessDetail : list) {
//System.out.println(businessDetail.toString());
//}
//
}

存储过程

/**
 * 存储过程
 */
public static void main(String[] args) {
Connection conn = DBmanger.getConnection();
CallableStatement cstmt = null;
ResultSet rs = null;
Scanner scanner = new Scanner(System.in);
System.out.println("输入显示数量");
int size = scanner.nextInt();
System.out.println("输入页数");
int index = scanner.nextInt();
try {
cstmt = conn.prepareCall("{call proc_getPage(?,?)}");
cstmt.setInt(1, size);
cstmt.setInt(2, index);
boolean execute = cstmt.execute();//stmt.executeQuery()也可以
if (execute) {
rs = cstmt.getResultSet();
while (rs.next()) {
String result = String.format(
"编号: %d,姓名: %s,生日: %s,性别: %s,地址: %s,班级编号: %d",
rs.getInt(1), rs.getString(2), rs.getString(3),
rs.getString(4), rs.getString(5), rs.getInt(6));
System.out.println(result);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
 
}