关于掌握JDBC基本操作的练习

掌握JDBC的基本操作。

要求

对book表中的数据进行增删改查；
book表：id int,bookName varchar(20),price int,description varchar(20)
Book类：id int，bookName String ，price int，description String
提示：创建数据库连接工具类DBUtil类。

以MySQL为例，打开MySQL，创建book表
在这里要设置MySQL的编码为utf-8，用alter database 数据库名 character set utf8;不然会出现Incorrect string value错误。

创建Book类

package entity;

public class Book {
	private int id;
	private String bookName;
	private int price;
	private String description;
	public Book(){}
	public Book(int id, String bookName, int price, String description) {
		super();
		this.id = id;
		this.bookName = bookName;
		this.price = price;
		this.description = description;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
}

创建DBUtil类，我将增删查改的方法也一起写进去了

package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import entity.Book;

//用于连接数据库，提供增删查改功能
public class DBUtil {
	private static String URL="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
	private static String uname="root";
	private static String pwd="123456";
	PreparedStatement pstmt=null;
	Connection connection=null;
	public DBUtil(){
		init();
	}
	private void init(){
		try{
			//加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//创建数据库连接
			this.connection=DriverManager.getConnection(URL,uname,pwd);
		}
		catch(ClassNotFoundException|SQLException e){
			e.printStackTrace();
		}
	}
	public void Add(Book book){//增
		String sql="insert into book(id,bookName,price,description) values(?,?,?,?)";
		try {
			//进行增
			pstmt=this.connection.prepareStatement(sql);
			pstmt=connection.prepareStatement(sql);
			pstmt.setInt(1, book.getId());
			pstmt.setString(2, book.getBookName());
			pstmt.setInt(3, book.getPrice());
			pstmt.setString(4, book.getDescription());
			int rs=pstmt.executeUpdate();
			//处理结果
			System.out.println(rs);
		}
		catch (Exception e) {
		e.printStackTrace();
		}
	}
	public void Remove(int BookId){
		String sql="delete from book where id=?";
		try {
			//进行删
			pstmt=this.connection.prepareStatement(sql);
			pstmt.setInt(1, BookId);
			int rs=pstmt.executeUpdate();
			System.out.println(rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public List<Book> Search(int BookId){
		List<Book> list=new ArrayList<Book>();
		Book book=new Book();
		String sql="select * from book where id=?";
		try {
			//进行查找
			pstmt=this.connection.prepareStatement(sql);
			pstmt.setInt(1, BookId);
			ResultSet rSet=pstmt.executeQuery();
			while(rSet.next()){
				book.setId(rSet.getInt("id"));
				book.setBookName(rSet.getString("BookName"));
				book.setPrice(rSet.getInt("price"));
				book.setDescription(rSet.getString("description"));
				list.add(book);
				System.out.println("编号为："+book.getId()+"，书名："+book.getBookName()+"，价格："+book.getPrice()+"，描述："+book.getDescription());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}

创建Test类用于测试

package dao;

import entity.Book;

public class Test {
	public static void main(String[] args) {
		DBUtil dbUtil=new DBUtil();
//		dbUtil.Remove(1);用来测试remove功能，在插入数据后可以去掉注释来测试
		dbUtil.Add(new Book(1,"软件工程导论",90,"软件工程的入门书"));
		System.out.print(dbUtil.Search(1));
	}
}