JDBC（七）JDBC开发规范

为了提高代码的复用性，需要遵循一定的规范。JDBC的开发一般遵循这样的规范：

DbUtil类中封装了哪些实用的方法呢？如图，常见的方法是：executeUpdate、executeQuery close方法：

例：

 

package com.wtyy.emp.util;

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

import com.wtyy.sqlite.DBFileUtil;

public class DbUtil {
	private static final String JDBC_DRIVER = "org.sqlite.JDBC";
	private static String JDBC_URL;
	private Connection conn = null;
	private PreparedStatement stmt = null;
	private ResultSet rs = null;

	public DbUtil(String dbname) {
		DbUtil.JDBC_URL = DBFileUtil.getDbUrl(dbname);
	}

	static {
		try {
			// 用来检查给定的类名存不存在
			Class.forName(JDBC_DRIVER);
		} catch (ClassNotFoundException e) {
			System.out.println("驱动不存在，请添加驱动包！");
		}
	}

	public Connection getConn() {
		try {
			if (conn == null || conn.isClosed()) {
				conn = DriverManager.getConnection(JDBC_URL);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public int executeUpdate(String sql, Object... params) {
		int rlt = -1;
		try {
			getConn();
			stmt = conn.prepareStatement(sql);
			putParams(params, stmt);
			rlt = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rlt;
	}

	public ResultSet executeQuery(String sql, Object... params) {
		try {
			// 2.获取连接
			getConn();
			stmt = conn.prepareStatement(sql);
			putParams(params, stmt);
			// 3.执行事务
			rs = stmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	public void close() {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void putParams(Object[] params, PreparedStatement stmt) throws SQLException {
		if (params != null) {
			for (int i = 1; i <= params.length; i++) {
				stmt.setObject(i, params[i - 1]);
			}
		}
	}

}

测试类：

 

 

package com;
import com.wtyy.sqlite.DBFileUtil;
import com.wtyy.emp.util.DbUtil;
public class DBUtilTest{ 


 public static void main(String args[]) 
   {
        DbUtil db = new DbUtil("employee");
        String sql = "insert into employee(empno,name,salary) values(?,?,?)";
	Object[] params = {"10003","Lilei",3000};
	int rlt = db.executeUpdate(sql, params);
	db.close();
        if(rlt>0){
         System.out.println("插入成功");
         }
   } 
}

 

例：

 

图书查询项目：

DBUtil不变：

（1.）实体类：

 

package com.wtyy.booklib.pojo;


public class BookInfo {

	private String bookno;

	private String title;

	private String year;

	private String author;

	private String category;

	private Double price;

	public BookInfo(){}

	public BookInfo(String bookno, String title, String year, String author, String category, Double price) {

		super();

		this.bookno = bookno;

		this.title = title;

		this.year = year;

		this.author = author;

		this.category = category;

		this.price = price;

	}


	public String getBookno() {

		return bookno;

	}


	public void setBookno(String bookno) {

		this.bookno = bookno;

	}


	public String getTitle() {

		return title;

	}


	public void setTitle(String title) {

		this.title = title;

	}


	public String getYear() {

		return year;

	}


	public void setYear(String year) {

		this.year = year;

	}


	public String getAuthor() {

		return author;

	}


	public void setAuthor(String author) {

		this.author = author;

	}


	public String getCategory() {

		return category;

	}


	public void setCategory(String category) {

		this.category = category;

	}


	public Double getPrice() {

		return price;

	}


	public void setPrice(Double price) {

		this.price = price;

	}

	@Override

	public String toString() {

		return bookno + "_" + title + "_" + year + "_" + author

				+ "_" + category + "_" + price;

	}


}

（2.）DAO：

 

 

package com.wtyy.booklib.dao;


import java.util.List;


import com.iflytek.booklib.pojo.BookInfo;

import com.iflytek.booklib.util.DbUtil;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

public class BookInfoDao {

	private DbUtil db = new DbUtil("bookstore");


	public int addBookInfo(BookInfo book) {

		String sql = "insert into bookstore values(?,?,?,?,?,?)";

		int i = db.executeUpdate(sql, book.getBookno(), book.getTitle(), book.getYear(), book.getAuthor(),

				book.getCategory(), book.getPrice());

		db.close();

		return i;

	}


	public int deleteBookInfoByNo(String bookNo) {

		String sql = "delete from bookstore where bookno = ?";

		int i = db.executeUpdate(sql, bookNo);

		db.close();

		return i;

	}


	public int updateBookInfo(BookInfo book) {

		String sql = "update bookstore set title = ?,year = ?,author = ?,category = ?,price = ? where bookno = ?";

		int i = db.executeUpdate(sql, book.getTitle(), book.getYear(), book.getAuthor(), book.getCategory(),

				book.getPrice(), book.getBookno());

		db.close();

		return i;

	}


	public List<BookInfo> queryBookInfos(String title) {

		String sql = "select * from bookstore where title like ?";

		ResultSet rs = db.executeQuery(sql, "%" + title + "%");

		List<BookInfo> list = new ArrayList<BookInfo>();

		try {

			while (rs.next()) {

				BookInfo bk = new BookInfo();

				bk.setBookno(rs.getString("bookno"));

				bk.setTitle(rs.getString("title"));

				bk.setYear(rs.getString("year"));

				bk.setAuthor(rs.getString("author"));

				bk.setCategory(rs.getString("category"));

				bk.setPrice(rs.getDouble("price"));

				list.add(bk);

			}

		} catch (SQLException e) {

			e.printStackTrace();

		}

		db.close();

		return list;

	}


	public BookInfo queryBookInfoByNo(String bookNo) {

		String sql = "select * from bookstore where bookno like ?";

		ResultSet rs = db.executeQuery(sql, "%" + bookNo + "%");

		BookInfo bk = new BookInfo();

		try {

			while (rs.next()) {

				bk.setBookno(rs.getString("bookno"));

				bk.setTitle(rs.getString("title"));

				bk.setYear(rs.getString("year"));

				bk.setAuthor(rs.getString("author"));

				bk.setCategory(rs.getString("category"));

				bk.setPrice(rs.getDouble("price"));

			}

		} catch (SQLException e) {

			e.printStackTrace();

		}

		db.close();

		return bk;

	}


}

（3.）测试类：

 

 

package com.wtyy.booklib.view;


import com.wtyy.booklib.dao.BookInfoDao;

import com.wtyy.booklib.pojo.BookInfo;

import java.util.List;

public class BookInfomanager {

	public static void main(String[] args) {

		BookInfoDao bookDao = new BookInfoDao();

		// TODO1

		BookInfo book = new BookInfo("10023", "西游记", "2001", "吴承恩", "文学", 65D);

		int i = bookDao.addBookInfo(book);

		System.out.println(i);

		// TODO2

		List<BookInfo> list  = bookDao.queryBookInfos("水浒");

		for (BookInfo e : list) {

			System.out.println(e);

		}


		// TODO3

		BookInfo book2 = new BookInfo("10023", "大话西游", "1999", "周星驰", "影视", 100.5);

		int i1 = bookDao.updateBookInfo(book2);

		System.out.println(i1);

		// TODO4

		String bookno = "10023";

		BookInfo book3 = null;

		book3 = bookDao.queryBookInfoByNo(bookno);

		System.out.println(book3);

		// TODO5

		String bookno1 = "100011";

		int i2 = bookDao.deleteBookInfoByNo(bookno1);

		System.out.println(i2);

	}

}