TJETC Java实训04:Java购物商城连接SQLServer数据库


上一个的代码是模拟数据库的实现。第四天的工作是将模拟的数据库变成实际的微软SQLServer数据库。这里关于Java连接JDBC的教程和测试代码貌似在上个月就已经写了,参见Windows7下安装Microsoft SQL Server 2008配置eclipse通过JDBC连接SQl Server 2008数据库 ,说的是配置和测试。

OK,看完上面的两篇文章,数据库的连接就应该可以搞定了。这里只给出数据库有关的部分代码:

package com.chinasofti.etc.dao.jdbcimpl;

[java]
package com.chinasofti.etc.dao.jdbcimpl;

import java.sql.DatabaseMetaData;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
protected Connection conn;
protected PreparedStatement pstmt;
protected ResultSet rs;
protected int result;

static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("加载数据库引擎失败!");
}
}

/**
* 建立数据库连接
*
* @return
*/
public void getConn() {

String url = "jdbc:sqlserver://localhost:1433;dataBaseName=bookshop";
String user = "sa";
String password = "123456";

try {
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("数据库连接失败!");
}
}

/**
* 关闭数据库所有连接
*/
public void closeAll() {
// TODO Auto-generated method stub
try {
if (pstmt != null && !pstmt.isClosed()) {
pstmt.close();
}
if (rs != null && !rs.isClosed()) {
rs.close();
}
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 查询数据库
*/

public void doQuery(String sql, Object… params) {
// TODO Auto-generated method stub
try {
pstmt = conn.prepareStatement(sql);
if (pstmt != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
rs = pstmt.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**
* 操作数据库:增删改
*/
public void doOperate(String sql, Object… params) {
// TODO Auto-generated method stub
try {
pstmt = conn.prepareStatement(sql);
if (pstmt != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

boolean existTable(String tableName) {
DatabaseMetaData meta;
try {
meta = (DatabaseMetaData) conn.getMetaData();
ResultSet rs = meta.getTables(null, null, tableName, null);// tableName表名
if (rs.next()) {
return true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
}

[/java]

[java]
package com.chinasofti.etc.dao.jdbcimpl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.chinasofti.etc.dao.BookDao;
import com.chinasofti.etc.po.Book;

public class BookDaoImpl extends BaseDao implements BookDao {

@Override
public List<Book> findAllBooks() {
// TODO Auto-generated method stub
List<Book> books = new ArrayList<Book>();
getConn();
String sql = "select * from Books";
doQuery(sql);
try {
while (rs.next()) {
Book book = new Book();
book.setBookId(rs.getInt(1));
book.setBookIsbn(rs.getString(2));
book.setBookName(rs.getString(3));
book.setBookPrice(rs.getDouble(4));
book.setBookAuthor(rs.getString(5));
book.setBookPublisher(rs.getString(6));

books.add(book);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
closeAll();
return books;
}

@Override
public Book findBookByBookId(int bookId) {
// TODO Auto-generated method stub
Book book = new Book();
getConn();
String sql = "select * from books where bookId = ?";
doQuery(sql, bookId);
try {
if (rs.next()) {
book.setBookId(rs.getInt(1));
book.setBookIsbn(rs.getString(2));
book.setBookName(rs.getString(3));
book.setBookPrice(rs.getDouble(4));
book.setBookAuthor(rs.getString(5));
book.setBookPublisher(rs.getString(6));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
closeAll();
return book;
}

@Override
public int insertBook(Book book) {
// TODO Auto-generated method stub
getConn();

String sql = "insert into Books values(?,?,?,?,?)";
doOperate(sql, book.getBookIsbn(), book.getBookName(), book.getBookPrice(), book.getBookAuthor(), book.getBookPublisher());
System.out.println("–书籍添加成功–");

closeAll();
return result;
}

/**
* SQLServer数据库初始化:创建表,添加三行记录
*/
@Override
public void doInit() {
// TODO Auto-generated method stub
getConn();

String sql = null;
if (!existTable("Books")) {
// 初始化表Books
sql = "create table Books(bookId int primary key identity(1,1), bookIsbn nvarchar(100), bookName nvarchar(100), bookPrice int, bookAuthor nvarchar(100), bookPublisher nvarchar(100))";
doOperate(sql);
sql = "insert into Books values(‘1111’, ‘AAA’, 10, ‘AAuthor’, ‘APublisher’)";
doOperate(sql);
sql = "insert into Books values(‘2222’, ‘BBB’, 20, ‘BAuthor’, ‘BPublisher’)";
doOperate(sql);
sql = "insert into Books values(‘3333’, ‘CCC’, 30, ‘CAuthor’, ‘CPublisher’)";
doOperate(sql);
System.out.println("====初始化表Books成功===");

}
if (!existTable("Users")) {
// 初始化表Users
sql = "create table Users(userId int primary key identity(1,1), userName nvarchar(20), userPassword nvarchar(20))";
doOperate(sql);
sql = "insert into Users values(‘zhangsan’, ‘zhangsan’)";
doOperate(sql);
sql = "insert into Users values(‘lisi’, ‘lisi’)";
doOperate(sql);
sql = "insert into Users values(‘wangwu’, ‘wangwu’)";
doOperate(sql);
System.out.println("====初始化表Users成功===");
}

if (!existTable("Goods")) {
// 初始化表Goods
sql = "create table Goods(goodId int primary key identity(1,1), bookId int, bookNum int)";
doOperate(sql);
System.out.println("====初始化表Goods成功===");
}

closeAll();

}
}

[/java]

[java]
package com.chinasofti.etc.dao.jdbcimpl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.chinasofti.etc.biz.GoodBiz;
import com.chinasofti.etc.biz.seimpl.GoodBizImpl;
import com.chinasofti.etc.dao.GoodDao;
import com.chinasofti.etc.po.Good;

public class GoodDaoImpl extends BaseDao implements GoodDao {

boolean flag = true;

@Override
public List<Good> findAllGoods() {
// TODO Auto-generated method stub
List<Good> goods = new ArrayList<Good>();
getConn();
String sql = "select * from Goods";
doQuery(sql);
try {
while (rs.next()) {
Good good = new Good();
good.setBookId(rs.getInt(2));
good.setBookNum(rs.getInt(3));

goods.add(good);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
closeAll();
return goods;
}

@Override
public Good findGoodByBookIdNum(int bookId, int bookNum) {
// TODO Auto-generated method stub
Good good = new Good();
GoodBiz goodBiz = new GoodBizImpl();
getConn();
String sql = "select * from Goods where bookId = ?";
doQuery(sql, bookId);
try {
if (rs.next()) {
if (rs.getInt(2) == bookId) {
good = goodBiz.updateGoodByBookNum(bookId, rs.getInt(3) + bookNum);
flag = false;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

if (flag) {
good = goodBiz.addGoodByBookIdNum(bookId, bookNum);
}
closeAll();
return good;
}

@Override
public Good addGoodByBookIdNum(int bookId, int bookNum) {
// TODO Auto-generated method stub
getConn();
String sql = "insert into Goods values(?, ?)";
doOperate(sql, bookId, bookNum);

closeAll();
return null;
}

@Override
public Good updateGoodByBookNum(int bookId, int bookNum) {
// TODO Auto-generated method stub
getConn();
String sql = "update Goods set bookNum = ? where bookId = ?";
doOperate(sql, bookNum, bookId);

closeAll();
return null;
}

@Override
public Good deleteGoodList() {
// TODO Auto-generated method stub
getConn();

String sql = "delete from Goods";
doOperate(sql);
System.out.println("–购物车已清空–");

closeAll();
return null;
}

}

[/java]

[java]
package com.chinasofti.etc.dao.jdbcimpl;

import java.sql.SQLException;

import com.chinasofti.etc.dao.UserDao;
import com.chinasofti.etc.po.User;

public class UserDaoImpl extends BaseDao implements UserDao {

@Override
public User findUserByUserName(String userName) {
// TODO Auto-generated method stub
User user = null;
getConn();
String sql = "select * from Users where userName like ?";
doQuery(sql,userName);
try {
if (rs.next()) {
user = new User();
user.setUserId(rs.getInt(1));
user.setUserName(rs.getString(2));
user.setUserPassword(rs.getString(3));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

closeAll();
return user;
}

}

[/java]

发表评论