TJETC Java实训05:Java购物商城任意切换多种数据库


昨天的实例代码实现了java连接SQLServer数据库的功能,而今天要做的是在java代码里实现多个数据库,比如SQLServer数据库和MySQL数据库。通过仅仅修改一个外部的配置文件,就能自由的在不同数据库之间切换,想用哪个个数据库就用哪个数据库,而不用修改任何一行java代码。这种采用接口的方法真正实现了代码的高内聚,低耦合。可扩展性非常强。

关于MySQL数据库,配置就不多说,要知道用户名、密码,关键的一点是要启动MySQL服务,总体上MySQL的配置可以参照SQLServer的配置过程,当然,更简单。Java购物商场连接MySQL的实现代码如下,也是4个文件:

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

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.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("加载数据库引擎失败!");
}
}

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

String url = "jdbc:mysql://localhost:3306/bookshop?user=root&password=1&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false";

try {
conn = DriverManager.getConnection(url);
} 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.mysqlimpl;

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(null,?,?,?,?,?)";
doOperate(sql, book.getBookIsbn(), book.getBookName(), book.getBookPrice(), book.getBookAuthor(), book.getBookPublisher());
System.out.println("–书籍添加成功–");

closeAll();
return result;
}

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

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

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

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

closeAll();

}
}

[/java]

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

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(null, ?, ?)";
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.mysqlimpl;

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]

发表评论