jdbc連接數(shù)據(jù)庫办成，并封裝成數(shù)據(jù)庫工具類

import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.Vector;

import server.bean.Book;

public class DBTools {
    private static String driver;
    private static String url;
    private static String user;
    private static String password;
    private static InputStream is;
    private static Connection conn;

    static {
        Properties pt = new Properties();
        is = DBTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            pt.load(is);
            driver = pt.getProperty("driver");
            url = pt.getProperty("url");
            user = pt.getProperty("user");
            password = pt.getProperty("password");
            IOUtils.close(is);
        } catch (IOException e) {
            IOUtils.close(is);
            log("/n讀取數(shù)據(jù)庫配置文件失敗/n " + e);
        }
    }

    // log
    public static void log(String str) {
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        String time = df.format(System.currentTimeMillis());
        str = time + "  " + str;
        FileOutputStream fos = null;
        BufferedOutputStream bos = null;
        try {
            fos = new FileOutputStream("DBErroLog.txt");
            bos = new BufferedOutputStream(fos);
            byte[] b = str.getBytes();
            bos.write(b);
            bos.flush();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(fos, bos);
        }
    }

    // 獲取連接
    public static Connection getConnection() {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            return conn;
        } catch (ClassNotFoundException e) {
            log("加載驅(qū)動失敗搓逾，沒有找到類！！澎粟！\n" + e);
        } catch (SQLException e) {
            e.printStackTrace();
            log("數(shù)據(jù)庫連接失敗，請檢查ip地址，端口號,賬號及密碼是否有誤Ｈ岜啤！割岛！\n" + e);
        }
        return null;
    }

    // 登錄驗證 SELECT NAME,pwd,is_manager FROM usr WHERE NAME='tan' AND
    // pwd='123456';
    public static int loginCheck(String usr, String pwd) throws SQLException {
        String sql = " SELECT is_manager FROM usr WHERE name=? AND pwd=?;";
        PreparedStatement ps = getConnection().prepareStatement(sql);
        ps.setString(1, usr);
        ps.setString(2, pwd);
        ResultSet rs = ps.executeQuery();
        // 判斷賬號密碼是否正確
        if (rs.next()) {
            // 判斷是管理員還是學生
            if (rs.getBoolean(1)) {
                close();
                return 1;
            } else {
                close();
                return 0;
            }
        }
        close();
        return -1;
    }

    // 改密碼
    public static boolean changePwd(String usr, String pwd) {
        String sql = " UPDATE usr SET pwd=? WHERE NAME=?;";
        PreparedStatement ps;
        try {
            ps = getConnection().prepareStatement(sql);
            ps.setString(1, pwd);
            ps.setString(2, usr);
            int n = ps.executeUpdate();
            System.out.println(n);
            if (n > 0) {
                close();
                return true;
            }
        } catch (SQLException e) {
        }
        close();
        return false;
    }

    // 按id查詢書籍
    public static Vector<Book> selectByid(String book_id) throws SQLException {
        // SELECT book_id,book_name,book_info,book_total FROM book WHERE
        // book_id='1111';
        Vector<Book> books = null;
        String sql = "SELECT book_id,book_name,book_info,book_total,book_num FROM book WHERE book_id=?";
        PreparedStatement ps = getConnection().prepareStatement(sql);
        ps.setString(1, book_id);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            if (books == null) {
                books = new Vector<Book>();
            }
            Book book = new Book();
            String id = rs.getString(1);
            String name = rs.getString(2);
            String info = rs.getString(3);
            int total = rs.getInt(4);
            int num = rs.getInt(5);
            book.setBook_id(id);
            book.setBook_name(name);
            book.setBook_info(info);
            book.setBook_total(total);
            book.setBook_num(num);
            books.add(book);
        }
        close();
        return books;
    }

    // 按名字匹配書籍
    public static Vector<Book> selectByName(String book_name) throws SQLException {
        // SELECT book_id,book_name,book_info,book_total FROM book WHERE
        // book_name LIKE '%編程%';
        Vector<Book> books = null;
        String sql = " SELECT book_id,book_name,book_info,book_total,book_num FROM book WHERE book_name LIKE ?";
        PreparedStatement ps = getConnection().prepareStatement(sql);
        ps.setString(1, "%" + book_name + "%");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            if (books == null) {
                books = new Vector<Book>();
            }
            Book book = new Book();
            String id = rs.getString(1);
            String name = rs.getString(2);
            String info = rs.getString(3);
            int total = rs.getInt(4);
            int num = rs.getInt(5);
            book.setBook_id(id);
            book.setBook_name(name);
            book.setBook_info(info);
            book.setBook_total(total);
            book.setBook_num(num);
            books.add(book);
        }
        close();
        return books;
    }

    // 添加書籍
    public static boolean addBook(String bookId, String bookName, String bookInfo, int bookTotal, int bookNum) {
        String sql = "insert book values(?,?,?,?,?);";
        PreparedStatement ps;
        try {
            ps = getConnection().prepareStatement(sql);
            ps.setString(1, bookId);
            ps.setString(2, bookName);
            ps.setString(3, bookInfo);
            ps.setInt(4, bookTotal);
            ps.setInt(5, bookNum);
            int n = ps.executeUpdate();
            System.out.println(n);
            if (n > 0) {
                close();
                return true;
            }
        } catch (SQLException e) {
        }
        close();
        return false;
    }

    // 批量刪除書籍DELETE FROM book WHERE book_id='123';
    public static boolean deleteBooks(String[] array) {
        String sql = "DELETE FROM book WHERE book_id=?;";
        PreparedStatement ps;
        int n = 0;
        try {
            ps = getConnection().prepareStatement(sql);
            for (String string : array) {
                ps.setString(1, string);
                n += ps.executeUpdate();
            }
        } catch (SQLException e) {
        }
        if (n > 0) {
            return true;
        } else {
            return false;
        }
    }

    // 修改書籍UPDATE book SET
    // book_name='java編程',book_info='java編程思想',book_total='20',book_num='20'
    // WHERE book_id='23d3';
    public static boolean updateBook(Book book) {
        if (book == null) {
            return false;
        }
        String sql = "UPDATE book SET book_name=?,book_info=?,book_total=?,book_num=? WHERE book_id=?;";
        PreparedStatement ps;
        int n = 0;
        try {
            ps = getConnection().prepareStatement(sql);
            ps.setString(1, book.getBook_name());
            ps.setString(2, book.getBook_info());
            ps.setInt(3, book.getBook_total());
            ps.setInt(4, book.getBook_num());
            ps.setString(5, book.getBook_id());
            n = ps.executeUpdate();
        } catch (SQLException e) {
        }
        if (n > 0) {
            return true;
        } else {
            return false;
        }
    }

    // 關(guān)閉連接
    public static void close() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log("數(shù)據(jù)庫連接關(guān)閉失敗\n" + e);
            }
        }
    }

    public static void main(String[] args) throws SQLException {// 測試
        /*
         * String sql = "select * from usr;";
         * try {
         * PreparedStatement ps = getConnection().prepareStatement(sql);
         * ResultSet rs = ps.executeQuery();
         * while (rs.next()) {
         * System.out.println(rs.getString(1));
         * System.out.println(rs.getString(2));
         * }
         * } catch (SQLException e) {
         * 
         * e.printStackTrace();
         * }
         */

        // changePwd("tan", "123");
        // Vector<Book> books = selectByid("1111");
        // System.out.println(books.size());
        // Vector<Book> books1 = selectByName("編程");
        // System.out.println(books1.size());

        // addBook("123456789", "c語音", "c語言詳解", 10, 5);
        String[] str = { "233", "5" };
        System.out.println(deleteBooks(str));
    }
}